Introduction to PostgreSQL for the data professional (Ryan Booz, Grant Fritchey)（Z-Library）

# Introduction to PostgreSQL for the Data Professional ## 【One-Line Pitch】 A practical, beginner-friendly guide for data professionals coming from other database systems who need to get productive with PostgreSQL quickly—covering everything from installation and configuration to query tuning and procedural programming. If you know databases but not PostgreSQL, this book gives you the fastest on-ramp. ## 【Book Arc】 - **Opening (~0%–9%)**: Introduces PostgreSQL's history, open-source governance model, and why it has become a mainstream choice for organizations of all sizes. Sets up the book's promise: a clear path through PostgreSQL basics for experienced data professionals. - **Early (~9%–17%)**: Compares PostgreSQL with other database systems, highlighting key differences in terminology (cluster vs. instance, role vs. user, tuple vs. row) and architectural concepts like extensibility, the built-in job scheduler, and query plan caching. Covers the essential client tools—psql, pgAdmin, DBeaver, and Azure Data Studio—with installation and usage guidance. - **Early (~22%–30%)**: Moves into server configuration fundamentals: memory settings, caching data pages, query memory allocation, connection limits, and query plan parameters like random page cost and effective cache size. Then covers security and object management—roles, privileges, GRANT/REVOKE, host-based authentication, database creation/modification/removal, templates, and tablespaces. - **Middle (~35%–43%)**: Explores PostgreSQL's extension ecosystem, explaining what extensions are, how to install/update/drop them, and where to find them (registries, Docker containers, package managers, cloud offerings). Profiles notable extensions including pg_stat_statements, PostGIS, pg_cron, postgres_fdw, pg_partman, pg_trgm, and hypopg. - **Middle (~43%–52%)**: Introduces database objects beyond tables—functions, procedures, views, materialized views, foreign tables, triggers, types, and domains—then dives into PL/pgSQL, PostgreSQL's procedural language, covering blocks, variables, error handling, conditionals (IF/THEN, CASE), loops, functions, procedures, and cursors. - **Middle (~52%–end)**: Focuses on query tuning with EXPLAIN—reading estimated query plans, adding runtime statistics with BUFFERS, understanding cost and actual time, and recognizing scan nodes, join nodes, and other plan elements. Includes troubleshooting guidance around work_mem and external sort disk usage, plus index fundamentals. ## 【Key Takeaways】 - **PostgreSQL is not "another SQL database"—it has its own mental model** (Early): Terms like cluster, role, and tuple map imperfectly to concepts from other databases, and understanding these differences early prevents confusion throughout your work. The book explicitly addresses these terminology gaps. - **Extensibility is PostgreSQL's superpower** (Early): Unlike many commercial databases, PostgreSQL is designed to be extended at its core—via extensions, custom types, and procedural languages—which is why features like PostGIS and pg_cron exist as add-ons rather than built-ins. - **Server configuration is mostly about memory and connections** (Early): The book walks through the key settings—shared buffers for caching data pages, work_mem for query operations, and connection limits—giving you a practical starting point for tuning without overwhelming you with the full parameter catalog. - **Roles and privileges follow a distinct model** (Early): PostgreSQL separates the concepts of roles, users, and groups, with a superuser role that bypasses most checks. GRANT and REVOKE, the PUBLIC role, and object ownership form the core of its security model. - **Extensions are first-class citizens, not afterthoughts** (Middle): The book shows a practical workflow—installing, updating, dropping, and making extensions available—plus a curated list of extensions worth trying, from monitoring (pg_stat_statements) to geospatial (PostGIS) to partitioning (pg_partman). - **PL/pgSQL is the gateway to server-side logic** (Middle): With blocks, variables, error handling, conditionals, loops, functions, procedures, and cursors, PL/pgSQL lets you move business logic into the database—a significant shift for professionals coming from application-centric development. - **EXPLAIN is the primary query-tuning tool** (Middle): Reading execution plans—scan nodes, join nodes, cost estimates, and actual runtime statistics—is the core skill for diagnosing slow queries. The book also flags work_mem as a common culprit when sorts spill to disk. ## 【Reading Tips】 - **Skim Chapter 1's history and governance sections** (~0%–9%) unless you're curious about PostgreSQL's origins; the practical value is in the "Why PostgreSQL and Why Now" discussion, which frames the rest of the book. - **Deep-read Chapters 2 and 6** (~9% and ~26%) for terminology and security—these are the concepts most likely to trip up professionals coming from other databases. The role/privilege model especially rewards careful study. - **Use Chapter 5 as a reference, not a cover-to-cover read** (~22%): Memory and query plan settings are important, but you'll likely return to specific parameters as you encounter performance issues rather than needing them all upfront. - **Treat Chapter 11 as a hands-on workshop** (~52%): To get value from the EXPLAIN material, you need to run it against your own queries. Read with a PostgreSQL instance open and practice on real workloads. - **The extension chapter (Chapter 8) is a treasure map** (~35%): Even if you don't need every extension, skimming the "Extensions to Try" list will show you what's possible and save you from reinventing wheels later. ## 【Coverage Limits】 The excerpts cover roughly the first half of the book's table of contents; later chapters on advanced indexing, backup/recovery, and monitoring are not represented in this guide. The book is a preview edition (2024), so some content may evolve in the final release. ##