PostgreSQL Mistakes and How to Avoid Them (Jimmy Angelakos)（Z-Library）

# PostgreSQL Mistakes and How to Avoid Them ## 【One-Line Pitch】 A practical, mistake-driven guide for anyone running PostgreSQL in production—DBAs, developers, and architects—that turns common errors into mental models for better database design, query writing, and administration. ## 【Book Arc】 - **Opening (~0%–10%)**: Establishes why PostgreSQL matters and why studying mistakes is a powerful learning method. Introduces the book's structure (mental models + example mistakes), the Frogge Emporium sample database, and categorizes typical mistakes: expectations from other databases, misunderstanding PostgreSQL, misreading documentation, SQL Standard relics, and ignoring best practices. - **Early (~10%–32%)**: Dives into bad SQL usage—the heart of the book. Covers negative predicates (NOT IN vs. NOT EXISTS), CTE pitfalls, quoted identifiers and case sensitivity, integer division traps, and querying indexed columns with expressions that defeat index usage. Each mistake comes with a concrete fix and EXPLAIN-based evidence. - **Middle (~32%–48%)**: Moves into data type misuse and tooling. Discusses ON CONFLICT behavior with NULLs, introduces SQLFluff as a linter for catching SQL errors, critiques AI-generated SQL (showing why it can be subtly wrong), and explores timestamp/time zone mistakes—particularly the dangers of TIMESTAMP WITHOUT TIME ZONE for cross-timezone arithmetic. - **Late (~48%–70%)**: Continues with improper data type usage, including CHARACTER(n)/BPCHAR padding issues. The pattern shifts from query-level mistakes to schema-level decisions that have long-term consequences. - **Ending (~70%–100%)**: (Excerpts do not cover this section in detail) Presumably covers administration, high availability, performance, security, upgrades, and migrations—plus appendices with the Frogge Emporium schema and a cheat sheet for best practices. ## 【Key Takeaways】 - **Mistakes are mental models in disguise** (Early): Each chapter pairs a common PostgreSQL error with a reusable mental model, making the book more than a bug list—it's a framework for thinking about database design and query optimization. - **NOT IN can silently return NULLs** (Early): When a subquery returns NULL values, NOT IN yields no rows at all—counterintuitive and dangerous. Use NOT EXISTS (anti-join) for predictable results. - **Quoted identifiers create permanent friction** (Early): Mixed-case quoted column names force you to quote them forever, causing errors and usability problems. Use aliases in queries for pretty report names instead of polluting your schema. - **Expressions on indexed columns kill index usage** (Early): Wrapping an indexed column in a function or casting it to a different type makes Postgres skip the index entirely—you pay the storage/write cost without the read benefit. Match the index's data type exactly. - **Integer division truncates silently** (Early): Dividing two INTEGERs gives an INTEGER result, losing fractional precision. Cast to NUMERIC explicitly when you need percentages or decimals. - **AI-generated SQL needs human scrutiny** (Middle): LLM-written queries can look polished but make false assumptions about schema, quoting, and execution order. Always verify with EXPLAIN and check against actual table definitions. - **TIMESTAMP WITHOUT TIME ZONE is a trap for cross-timezone math** (Middle): Naive timestamps make arithmetic meaningless when inputs come from different time zones. Standardize on a single timezone (e.g., Europe/London) or use TIMESTAMPTZ. - **Linters catch what eyes miss** (Middle): SQLFluff provides free, mechanical code review—catching formatting and syntax issues before they reach production. It's not a replacement for understanding, but a valuable safety net. ## 【Reading Tips】 - **Skim the EXPLAIN output** (Early): The book uses EXPLAIN plans extensively to prove why certain queries are slow or fast. You don't need to parse every line—focus on "Index Scan" vs. "Seq Scan" and the cost numbers to grasp the performance lesson. - **Deep-read Chapter 2 (Bad SQL usage)**: This is the densest, most actionable chapter. The NOT IN, quoted identifiers, and index-expression mistakes are the ones you'll encounter most in real codebases. - **Practice with the Frogge Emporium database**: Appendix A provides a sample schema. Reproduce the mistakes and fixes yourself—the book's examples are designed for hands-on learning. - **Watch for the "Lingo" boxes**: These define key terms (predicate, anti-join) inline. If you're new to relational algebra, these are your anchors. - **Use the cheat sheet (Appendix B)**: After reading, keep it handy as a quick reference for avoiding the most common mistakes in daily work. ## 【Coverage Limits】 This guide covers the opening through the middle sections (roughly 0–48% of the book), focusing on SQL mistakes and data type issues. The later chapters on administration, high availability, security, and migrations are not covered in the available excerpts. ##