Sql
-
Fast row-count estimates without COUNT(*)
pg_class.reltuples for when you need an answer in milliseconds.
-
Postgres 12 changed my CTEs and nobody told me
In Postgres 12 the optimization fence around CTEs came down. For us, that was mostly bad.
-
Find foreign-key violations before a migration
A query that tells you exactly which child rows will fail when you add the FK.
-
TIL: SQLite has RETURNING now
SQLite 3.35 added RETURNING, so you can get the inserted row back in one round-trip.
-
Gaps-and-islands SQL for compressing event streaks
A window-function pattern that collapses consecutive states into runs.
-
Cancel long-idle Postgres transactions
A query plus a loop to find idle-in-transaction sessions and kindly end them.
-
Recursive CTE to walk a parent-child tree in SQLite
The WITH RECURSIVE pattern for a self-joining table, with depth and path.
-
Postgres advisory locks for cross-process deduplication
A cheap mutex across N service replicas that does not need Redis.
-
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.
-
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.