Postgres
Changing a column type in Postgres often forces a full table rewrite while holding an ACCESS EXCLUSIVE lock. Some type changes avoid the rewrite entirely, others can be staged across multiple migrations to keep production traffic flowing.
Postgres
Postgres compresses large column values transparently using a system called TOAST. Since Postgres 14 the default algorithm can be switched per column from the classic PGLZ to LZ4, and the choice changes both compression ratio and CPU cost in ways most teams never tune.
Postgres
SQL NULL is not a value. It is the absence of a value, and the difference produces a class of bugs that survives code review, type checks, and unit tests because the SQL standard explicitly defines NULL to behave the way that hides them.
Postgres
Silent data corruption is one of the rare failure modes that punishes good operational hygiene. Regular backups happily encode the corruption, replication propagates it to standbys, and the team discovers the problem when a restore reveals data that was already wrong months ago.
Postgres
Postgres exposes a family of reset functions for the cumulative statistics views, and using them carelessly destroys the historical context that makes the views useful. The discipline of when to reset and what to capture first is one of the smaller operational habits that compounds over years.
Postgres
Postgres has supported jsonb since 9.4, but the SQL standard's jsonpath language added in Postgres 12 changed what you can ask of JSON columns. The operators are unfamiliar and the language is its own small grammar, but the capability is real.
Postgres
Replication slots are one of the most operationally dangerous Postgres features because their failure mode is silent disk fill. The view exposes the data to monitor, but the alerts require different thresholds for streaming and logical slots.
Postgres
The default EXPLAIN output is built for humans reading one plan at a time. The JSON format is built for tools, scripts, and dashboards that need to read many plans and compare them programmatically.
Postgres
Foreign key checks normally run at the statement level. Deferred constraints let them run at commit instead, which is the right tool for circular references and bulk imports that produce intermediate states the canonical immediate-check semantics reject.
Postgres
WAL archiving is the foundation of point-in-time recovery, and the most common failure mode is silent: archive_command fails, pg_wal fills up, and nobody notices until the cluster refuses writes. pg_stat_archiver catches the failure before it compounds.
Postgres
Most teams configure TLS on their Postgres connection and then never look at it again. pg_stat_ssl is the per-connection view that tells you which connections are actually encrypted and which TLS version and cipher they negotiated. The diagnostic queries and the configuration knobs that matter.
Postgres
PL/pgSQL functions are easy to write and hard to profile. pg_stat_user_functions exposes per-function call counts and total time, but only for functions you mark with track_functions. The configuration discipline, the diagnostic queries, and the wrap-and-measure pattern for functions that wrap