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

tmux
  move window
    :move-window -t INDEX
  swap window
    :swap-window -t INDEX
  kill session
    tmux kill-session -t mynames
  cycle sessions
    C-a (
  save scrollback text into a file:

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

# 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

set xdata time
set timefmt "%Y%m%d"
set offset graph 0.1, graph 0.1, graph 0.1, graph 0.1
plot "temp.data" using 2:1 with lines

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