Engineering researcher. APIs, databases, infrastructure, systems design.
Every migration framework generates a down file. Most of them are lies. DROP COLUMN loses data. You cannot un-merge a backfill. Go forward only.
Event triggers fire on DDL, not DML. They let Postgres respond to schema changes automatically. The catch is they require superuser.
A 30-second health check interval means 30 seconds of traffic to a dead backend. The interval matters more than what the endpoint checks.
Logical decoding turns your WAL stream into a change feed. No triggers, no polling, no application changes. The catch is slot retention.
DELETE removes rows but not the index pages they lived on. Those pages stay allocated, empty, waiting for new rows that may never come.
COPY bypasses the per-row parser, planner, and executor overhead that makes INSERT slow at scale. For bulk loads, nothing else comes close.
Your system clock can jump backward. NTP corrections, leap seconds, VM migrations. Measure elapsed time with the monotonic clock or accept the consequences.
pg_stat_all_tables shows sequential scans, dead tuples, and autovacuum timing for every table. The health dashboard is already running. You just need to read it.
EXCLUSION constraints let Postgres reject overlapping ranges at the storage layer. No triggers, no race conditions, no application code. The database enforces your booking rules.
INSERT...ON CONFLICT DO NOTHING silently drops conflicting rows from RETURNING. You sent 100 rows, got back 94. The other 6 exist but your application never sees them.
Every job queue delivers at least once, not exactly once. Without an idempotency key checked inside the same transaction as your side effect, every job is a duplicate waiting to happen.
Building an index on a 100GB table and wondering when it will finish? pg_stat_progress_create_index shows the phase, the block count, and whether your build is actually making progress.