postgres
Every index you add costs write throughput. pg_stat_user_indexes is the view that tells you which of those costs you are paying without getting any read-side benefit. Most production databases have several indexes that have not been read in months.
postgres
pg_dump backs up one database. pg_dumpall backs up the whole cluster: every database plus roles, tablespaces, and cluster-level settings. The difference matters in disasters where the cluster itself, not just the data, needs to be reconstructed.
postgres
CREATE INDEX takes an ACCESS EXCLUSIVE lock for the duration of the build. On large tables that means minutes to hours of blocked writes. CREATE INDEX CONCURRENTLY trades roughly double the build time for non-blocking behavior, and the trade is almost always worth it in production.
postgres
ALTER SYSTEM is the SQL interface to Postgres configuration. It writes to postgresql.auto.conf, takes effect after a reload, and survives restarts. The mechanics are simple; the operational discipline around it is what matters.
postgres
SECURITY DEFINER functions let a function run with the privileges of its owner rather than its caller. This is deliberate privilege escalation, useful for tightly-controlled operations and dangerous when written carelessly. The mechanics are simple; the failure modes are not.
postgres
Prepared statements skip parsing and planning on repeated execution. They are also the source of one of the most surprising performance regressions in production Postgres: the generic-plan trap that kicks in after five executions and can make subsequent queries 100x slower.
postgres
Most ALTER TABLE statements take an ACCESS EXCLUSIVE lock that blocks every read and write until the operation completes. Knowing which forms are safe and which ones rewrite the entire table is the difference between a five-second deploy and a two-hour outage.
postgres
The pg_settings view exposes every parameter Postgres knows about along with its current value, default, source, and whether changing it requires a restart. Most teams find out the hard way which parameters were silently overridden.
postgres
WAL archiving is the foundation of point-in-time recovery in Postgres, and the silent-failure mode of archive_command misconfiguration is one of the most expensive operational mistakes a team can make. pg_stat_archiver exposes the counters that tell you whether your backups can actually be r...
postgres
WAL is the load-bearing primitive behind Postgres durability and replication. pg_stat_wal, added in Postgres 14, finally exposes how much WAL your workload generates and why. Reading the view changes how you think about write amplification, full-page writes, and replica bandwidth.
postgres
The view that tells you whether your logical replication subscriber is actually keeping up. Reading pg_stat_subscription is half the diagnostic story that pg_stat_replication on the primary cannot tell you alone.
postgres
The system view that lets you read the same statistics the planner reads. Reading pg_stats is one of the most underused diagnostics for understanding why a query plan looks the way it does.