Appendix B. Cheat sheet

This cheat sheet distills the most common PostgreSQL pitfalls and the practices that prevent them. It urges readers to align design choices, query habits, and operational routines with their actual needs, to favor clarity and correctness over convenience, and to plan proactively rather than react under pressure. The overarching themes are to choose appropriate data types and features, write predictable and efficient SQL, tune and observe the system continuously, and rely on proven tools and processes for safety, performance, and reliability.

On the SQL and schema front, it cautions against constructs with surprising semantics (for example, NOT IN with NULLs or BETWEEN’s inclusive bounds), reminds that integer division truncates, and that count on a nullable column skips NULLs. It warns that quoting identifiers creates case sensitivity and that selecting or fetching more than necessary harms performance. Use CTEs to improve readability and let the optimizer help, and be wary of expressions that prevent index use. Prefer clear designs over nullable columns in composite unique keys; choose TIMESTAMPTZ over timestamp without time zone; avoid MONEY; favor identity columns over SERIAL; prefer TEXT to fixed-length character types; use JSONB instead of XML; and think carefully before choosing UUIDs over integer keys. For tables and indexes, avoid inheritance in favor of foreign keys and triggers, use partitioning judiciously (with care around multi-key strategies), and match index types to data and queries to unlock orders-of-magnitude speedups. Treat SQL_ASCII as a non-choice, avoid RULEs in favor of TRIGGERs, and don’t mix document patterns with relational access if it complicates or slows queries.

Operational guidance centers on performance, safety, and discipline. Defaults are conservative, so tune memory and concurrency with workload in mind; too many sessions, idle connections, idle-in-transaction, or excessive transaction rates can throttle performance, block work, or even risk XID wraparound. Autovacuum is essential; use EXPLAIN to understand slow plans; index for your WHERE clauses but prune unneeded indexes carefully, especially in replicated environments. Administratively, watch disk usage, separate and rotate logs, monitor continuously, and retain long-term stats; keep PostgreSQL, the OS, and libraries up to date. Secure access and privileges tightly: avoid blanket listen_addresses, trust in pg_hba, superuser-owned databases, unsafe SECURITY DEFINER usage, and an unguarded search_path. For availability, backups beat snapshots; combine base backups with point-in-time recovery, automate and test restores, and use proven HA tooling rather than DIY failover. For upgrades and migrations, read all relevant release notes, test with realistic data and workloads, and handle encodings, booleans, and type differences explicitly. Above all, be proactive: review code and usage patterns, document and automate, and focus first on the biggest pain points to build robust, reliable systems.

FAQ