Postgres The First Experience (Pavel Luzanov, Egor Rogov, Igor Levshin)（Z-Library）

# Postgres: The First Experience — Reading Guide ## 【One-Line Pitch】 A practical, beginner-friendly introduction to PostgreSQL that takes you from installation through writing real SQL queries, with a focus on hands-on learning using a demo airline database. Ideal for developers, students, and IT professionals who want to get productive with the world's most advanced open-source database quickly. ## 【Book Arc】 - **Opening (~0%–10%)**: Introduces PostgreSQL's history from the POSTGRES project at UC Berkeley, explains its open-source development model, release cycles, and core features like ACID compliance, MVCC, and cross-platform support. Sets the stage for why PostgreSQL matters. - **Early (~10%–19%)**: Covers what's new in PostgreSQL 15 (MERGE command, NULLS DISTINCT, logical replication improvements, backup enhancements) and walks through installation on Linux and Windows, including locale setup and initial server configuration. - **Early (~19%–32%)**: Hands-on SQL fundamentals using a test database—creating tables, inserting data, writing SELECT queries with WHERE clauses, using aggregate functions with GROUP BY and HAVING, and understanding transactions through a practical example involving groups and monitors. - **Middle (~39%–48%)**: Introduces the demo airline database (bookings), explains its schema (airports, flights, boarding passes), and presents real-world query problems—finding delayed flights, computing flight durations, and using recursive CTEs for route planning. - **Late (~48%–end)**: Covers connecting PostgreSQL to applications, minimal server configuration, using pgAdmin, advanced features (full-text search, JSON, foreign data wrappers), education/certification paths, and staying current with PostgreSQL releases. ## 【Key Takeaways】 - **PostgreSQL is the most feature-rich open-source database** (Early): Born from Michael Stonebraker's POSTGRES project at UC Berkeley in 1985, it has evolved through community-driven development where patches are discussed on the pgsql-hackers mailing list and must pass code review before inclusion. - **The release cycle is predictable and community-governed** (Early): Major versions ship roughly annually (now single-numbered, e.g., PostgreSQL 15 in October 2022), with quarterly minor updates for bug fixes. Code stabilization happens about six months before release, and beta versions precede release candidates. - **PostgreSQL 15 brings meaningful SQL improvements** (Early): The MERGE command finally arrived after a failed attempt in version 11, NULLS [NOT] DISTINCT gives control over NULL handling in constraints, and logical replication now supports row/column filtering and schema-wide replication. - **Transactions are the backbone of data integrity** (Early): The book demonstrates atomicity, consistency, isolation, and durability (ACID) through a practical example—creating a group and assigning a monitor must happen atomically, and PostgreSQL's MVCC allows parallel reads/writes without blocking. - **The demo airline database is a powerful learning tool** (Middle): Available in small, medium, and large sizes (from 280 MB to 2.6 GB), it provides realistic flight data for practicing everything from basic SELECT queries to complex recursive CTEs for route finding. - **Recursive queries solve real routing problems** (Middle): The book shows how WITH RECURSIVE with CYCLE detection (new in PostgreSQL 14) finds shortest routes between airports, using hop arrays and window functions to prevent infinite loops. - **PostgreSQL extends beyond basic SQL** (Late): Full-text search, JSON/JSONB support, and foreign data wrappers make it suitable for modern applications requiring semi-structured data and integration with external systems. ## 【Reading Tips】 - **Skim the history and "What's New" chapters** (first ~15%): These are useful for context but not essential for hands-on work. Focus on the release cycle explanation and PostgreSQL 15 highlights if you're upgrading from an older version. - **Deep-read the installation and SQL fundamentals sections** (~19%–32%): Follow along with your own PostgreSQL instance. The psql commands (starting with backslash) are terminal-specific—if using pgAdmin, skip those and find GUI equivalents. - **Work through the demo database problems** (~39%–48%): Download the small demo database (21 MB) and attempt each query problem before reading the solution. The recursive CTE examples are challenging but rewarding—take time to understand the CYCLE clause and window functions. - **Pay attention to transaction examples**: The group/monitor example perfectly illustrates why transactions matter. Recreate it yourself and experiment with ROLLBACK to see atomicity in action. - **Skip or skim application integration and pgAdmin sections** if you're already comfortable with database connections and GUI tools—they're straightforward and better learned by doing. ## 【Coverage Limits】 The excerpts cover the book's first half thoroughly (history, installation, SQL basics, demo database problems) but provide limited detail on later sections like application integration, advanced features (full-text search, JSON, foreign data wrappers), and certification paths. The guide's coverage reflects this distribution. ##