Postgres
-
pgbench: my first run against a new postgres
The complete pgbench init-and-run flow on a fresh postgres, with enough context to not look up the flags next time.
-
Postgres: find long-running queries and who is blocking whom
A view of pg_stat_activity that tells me the query and its blockers in one shot.
-
Postgres query to find bloated indexes
A quick-and-dirty bloat estimate so I can decide which indexes to REINDEX tonight.
-
Notes: pgvector HNSW m parameter rule of thumb
Turns out 'just pick m=16' is a surprisingly reasonable default, and I've been overthinking it.
-
TIL: temp_buffers is a thing and default is tiny
When your query spills to disk, it's using temp_buffers. The default is 8MB, which is small.
-
TIL: EXPLAIN (ANALYZE, BUFFERS) shows cache hit ratio
The buffers flag tells you how much of your query was served from cache vs disk.
-
TIL: pg_stat_statements_reset can take a userid
You can reset stats for just one role, instead of losing all history.
-
TIL: psql's \gexec runs the output of your query as SQL
Turns out psql has a dynamic-SQL feature built in, and I've been writing shell loops for no reason.
-
TIL: generate_series is the most underrated Postgres function
TIL you can use generate_series for date ranges, backfills, test data, and calendar tables — all without a helper.