The Art of PostgreSQL (Dimitri Fontaine)（Z-Library）

【One-Line Pitch】 A practical, developer-focused guide to PostgreSQL that shows how to push business logic into SQL, treat queries as first-class code, and dramatically reduce application code by mastering the full power of the world's most advanced open-source database. Ideal for application developers, backend engineers, and data-savvy programmers who want to move beyond basic CRUD operations. 【Book Arc】 - **Opening (~0%–6%)**: The book opens with a manifesto for SQL-centric development, arguing that most developers use only a fraction of PostgreSQL's capabilities. It sets up the core philosophy: treat SQL as code with versioning, testing, and deployment, and demonstrates early on how replacing hundreds of lines of application code with a single efficient query is both possible and desirable. - **Early (~6%–19%)**: Introduces the fundamentals of writing SQL queries as an application developer, covering psql basics, query structure, and the concept of business logic embedded in SQL. This section includes practical examples like calendar generation with `generate_series`, JSON querying with `jsonb`, and the first look at stored procedures as a data access API. - **Early (~19%–28%)**: Dives into the developer workflow around SQL: using psql as a powerful REPL, implementing regression testing with pgTap, and establishing a comprehensive indexing strategy. This is where the book shifts from "writing queries" to "engineering with SQL," covering the six main index types (B-tree, Hash, GiST, SP-GiST, GIN, BRIN) and when to use each. - **Middle (~28%–38%)**: Explores schemaless design with JSONB and hstore, then systematically breaks down the SELECT statement into its core components. The focus here is on PostgreSQL's rich processing functions (like `extract()`, `to_char()`, and `CASE`) that let you do data transformation directly in the database rather than in application code. - **Middle (~38%–47%)**: Covers advanced query patterns: ORDER BY with complex expressions, GROUP BY with CUBE for multi-dimensional aggregation, UNION/EXCEPT for set operations, and a deep dive into window functions. This section demonstrates the analytical power that becomes available when you stop treating SQL as a simple data-fetching language. - **Late (~47%+)**: The book concludes with an in-depth look at understanding relations and joins from a mathematical perspective, setting up the reader for advanced topics like query optimization and extension development that are covered in the final chapters. 【Key Takeaways】 - **SQL is a programming language, not a query tool** (Early): The book's central thesis is that SQL deserves the same engineering rigor as any other language—versioning, testing, code review, and deployment. This mindset shift alone can transform how you build database-backed applications. - **Business logic belongs in the database** (Early): Every clause in a query (SELECT, FROM, WHERE, ORDER BY) encodes business decisions. By moving this logic into SQL, you reduce application code, improve correctness, and eliminate the "race conditions and Heisenbugs" that come from implementing relational checks in application code. - **psql is the professional's REPL** (Early): The command-line tool psql is not a fallback for those without a GUI—it's the preferred interface for experts. Its support for variables, decorated literals, and scripting makes it ideal for both exploration and production work. - **Testing SQL is non-negotiable** (Early): pgTap provides a framework for writing unit tests directly in SQL, covering both result sets and schema integrity. This makes regression testing for database code as straightforward as it is for application code. - **Indexing is a strategy, not an afterthought** (Early): PostgreSQL offers six index types (B-tree, Hash, GiST, SP-GiST, GIN, BRIN), each designed for specific use cases. Understanding when to use each—not just defaulting to B-tree—is critical for query performance. - **Window functions changed SQL forever** (Middle): The ability to process peer rows through a frame—seeing multiple values at once to compute a single output—is described as a watershed moment in SQL history. Mastering window functions unlocks analytical queries that would otherwise require complex application code. - **PostgreSQL's extensibility is its superpower** (Early): The ability to add new data types, operators, and indexing support from "userland code" means you can enhance SQL itself. The JSONB `@>` operator with GIN index support is a prime example of this extensibility in action. - **Set operations are underused tools** (Middle): UNION, EXCEPT, and their variants allow for elegant solutions to complex data problems, like finding drivers who scored points in one race but not the next. Parenthesizing branches improves readability and clarifies where ORDER BY applies. 【Reading Tips】 - **Skim the preface and introduction** (~0%–6%) if you're already convinced SQL is powerful; the real value starts with the practical query examples in Part 3. - **Deep-read the indexing chapter** (Early ~28%)—this is where performance knowledge lives, and it's worth understanding each index type even if you don't memorize all the details. - **Pay special attention to the window functions section** (Middle ~47%)—this is described as a "before and after" moment in SQL capability, and the frame concept is the key to unlocking it. - **Work through the examples in psql yourself**—the book is built around hands-on learning, and the REPL is the intended environment for experimentation. - **Treat the stored procedures section as a bridge** (Early ~19%)—it connects simple queries to the more advanced data access patterns that follow, so don't skip it even if you're tempted to jump ahead. 【Coverage Limits】 The excerpts cover roughly the first half of the book (through ~47%), focusing on query writing, testing, indexing, and window functions. Later chapters on extensions, advanced relations, and performance tuning are mentioned in the table of contents but not covered in detail in this guide.