postgres
The information_schema views are slow and incomplete. The underlying pg_class and pg_attribute catalogs are fast, exhaustive, and the right tool when you need to know what the database actually knows about itself, not what the SQL standard says it should expose.
postgres
Replication slots solve a real problem and create a worse one. The mechanism keeps WAL files around as long as a consumer might need them, which is correct behavior, but a consumer that stops consuming becomes a slow-motion disk-fill catastrophe that the default monitoring catches only after th
postgres
LISTEN/NOTIFY is one of the more underused Postgres features and reaching for Redis or Kafka before checking what the database offers is a common mistake at small scale. But NOTIFY has structural limits that show up under load, and knowing them in advance saves both the architectural detour and
postgres
Most teams that use triggers reach for FOR EACH ROW by default, often without considering that FOR EACH STATEMENT exists. The two variants run different numbers of times, see different data, and pay dramatically different performance costs. Picking the wrong one is one of the more expensive tri
postgres
Most teams model date ranges as two columns: start_date and end_date. Postgres has had a dedicated range type since 9.2 that encodes both endpoints, inclusivity, and emptiness as a single typed value with operators for overlap, containment, and adjacency. The two-column pattern works, but it pu
postgres
GROUP BY answers one question. GROUPING SETS, ROLLUP, and CUBE answer several at once in a single pass over the data. Most teams reach for UNION ALL of multiple GROUP BY queries when one of these extensions would have done the work with one table scan and clearer intent.
postgres
Declarative partitioning in Postgres only pays off when the planner can skip partitions a query does not need. Plan-time pruning is automatic; execution-time pruning is recent and easy to miss. Knowing which form fires for which queries is the difference between a fast partitioned table and a s
postgres
Foreign Data Wrappers let Postgres query external data sources—other Postgres instances, MySQL, CSV files, REST APIs—as if they were local tables. The feature is powerful and easy to misuse, with performance characteristics that surprise teams expecting normal table behavior.
postgres
LATERAL is the SQL feature that lets a subquery reference columns from earlier in the FROM clause. It collapses several common loop-like patterns into single queries and removes a whole class of N+1 problems that ORMs and application code usually solve by iteration.
postgres
effective_cache_size does not allocate any memory. It is a hint to the planner about how much OS page cache it can assume, and getting it wrong systematically biases the planner toward sequential scans on tables that index scans would handle better.
postgres
Standby queries get cancelled because they conflict with WAL replay, and the only honest way to diagnose the problem is pg_stat_database_conflicts. The view exposes five conflict types and the trade-off matrix between query stability and replication lag.
postgres
synchronous_commit is one of the most consequential Postgres settings most teams never touch. It controls when a transaction is considered durably committed, and the default is correct for most workloads but wrong for some.