lukeasrodgers

269 tunable settings, these 20 are most helpful

max_connections

• set to useful peak
• above 200, look at conn pooler
• consider superuser_reserved_connections

shared_buffers - shared RAM

• amt of memory pg takes for itself
• generally, avail ram ÷ 4

lukeasrodgers

databases

pg

• dropping not null constraint on table with 800k rows, approx 35 columns: 32ms (8 core / 16 G RAM mbp)
• counting table with 5.1M records: 42s (on heroku)
• adding index on column, all values NULL: 42s (on heroku)
• adding index concurrently on updated_at column, about 17M rows, aptible postgres ~800 IOPS: 455.8086s
• adding index concurrently on 3 cols, item_type, event_type, updated_at, ~70M rows: 1.5hours
• removing index concurrently, one index, table about 20M rows: 80s
• adding index concurrently on 2 cols, banking_system_id (string, non-fk), platform (string), ~30M rows, about 3k IOPS: ~35 mins
• adding index (employee_id, date, amount) non-concurrently on 60M row table, rds db.t3.large: about 1.5 mins

lukeasrodgers

# TYPE /a in vim


select(0x1, 0x7FFF5DD1C850, 0x7FFF5DD1C7D0, 0x7FFF5DD1C750, 0x7FFF5DD1C8D8)		 = 0 0
lseek(0x3, 0x2000, 0x0)		 = 8192 0
dtrace: error on enabled probe ID 2132 (ID 320: syscall::write:return): invalid kernel access in action #12 at DIF offset 92
select(0x1, 0x7FFF5DD1C720, 0x7FFF5DD1C6A0, 0x7FFF5DD1C620, 0x7FFF5DD1C7A8)		 = 0 0
fsync(0x3, 0x7FFF5DD1C720, 0x7FFF5DD1C6A0)		 = 0 0
select(0x1, 0x7FFF5DD1C770, 0x7FFF5DD1C6F0, 0x7FFF5DD1C670, 0x7FFF5DD1C7F8)		 = 0 0
select(0x1, 0x7FFF5DD1C850, 0x7FFF5DD1C7D0, 0x7FFF5DD1C750, 0x0)		 = 1 0

lukeasrodgers

alert();

lukeasrodgers

/* quickly/hackily create randomish string data with md5 */

DELIMITER $$
DROP PROCEDURE IF EXISTS user_promos;

CREATE PROCEDURE user_promos()
BEGIN
  DECLARE i INT DEFAULT 1;

  WHILE i < 5000 DO

lukeasrodgers

Keybase proof

I hereby claim:

• I am lukeasrodgers on github.
• I am lukeasrodgers (https://keybase.io/lukeasrodgers) on keybase.
• I have a public key whose fingerprint is 9E9C 2A25 69EA CF84 DF8D 6B8A 0CA0 92B0 E2F9 8F01

To claim this, I am signing this object:

lukeasrodgers

http://bonesmoses.org/2016/07/15/pg-phriday-a-postgres-persepctive-on-mongodb/
CREATE TABLE sensor_log (
  id            SERIAL NOT NULL PRIMARY KEY,
  location      VARCHAR NOT NULL,
  reading       BIGINT NOT NULL,
  reading_date  TIMESTAMP NOT NULL
);
 
\timing
 

lukeasrodgers

# useful if k is a very large list, and you don't want to do it all in one request
# note that consistency will not be guaranteed here (i.e. list can change during iteration)
def scan_list(k, range=1000, &block)
  i = 0
  result = []
  while l = HLR.lrange(k, i, i+range)
    break if l.size == 0
    if block_given?
      yield l
    else

lukeasrodgers

config

• ensure config is loaded/tested in correct env

arithmetic/math

• check for divide by zero
• is integer division/rounding accounted for?
• what happens if inputs are very small/very large? or negative?
• what happens if results are negative (even though this "should never happen")
• integer overlow (in languages where matters)
• catastrohpic cancellation and other floating point concerns

lukeasrodgers

# List users by average and maximum session length.
SELECT person, max(client.runtime_ms), avg(client.runtime_ms)
FROM item_occurrence
GROUP BY 1
ORDER BY 2 DESC

# List active date ranges for each deploy.
SELECT client.javascript.code_version, min(timestamp), max(timestamp)
FROM item_occurrence
GROUP BY 1