Introduction to PostgreSQL for the data professional (Ryan Booz, Grant Fritchey)（Z-Library）
SQL
Adoption and use of PostgreSQL is growing all the time. From mom-and-pop shops to large enterprises, more data is being managed by PostgreSQL. In turn, this means that more data professionals need to learn PostgreSQL even when they have experience with other databases. While the documentation around PostgreSQL is detailed and technically rich, finding a simple, clear path to learning what it is, what it does, and how to use it can be challenging. This book seeks to help with that challenge. We set about giving you all the basics of how to get started within PostgreSQL. From getting your first instance, or cluster, up and running, to backups, to server configurations, we cover all the basics. You'll learn about processing languages, monitoring, indexes and more. Every chapter provides fundamental knowledge and guidance so that you can more easily get started working with PostgreSQL. Getting started with PostgreSQL doesn't have to be a challenge and this book will help.
87
Views
0
Downloads
0.00
Total Donations
Registered users can read the full content for free
Register as a Gaohf Library member to read the complete e-book online for free and enjoy a better reading experience.
Page
1
(This page has no text content)
Page
2
Introduction to PostgreSQL for the Data Professional First Edition By Ryan Booz & Grant Fritchey Published by Red Gate Books 2024
Page
3
Copyright © 2024 by Grant Fritchey and Ryan Booz Title: Introduction to PostgreSQL for the data professional. Authors: Grant Fritchey and Ryan Booz Technical Reviewer: Robert Treat Copy Editor: Louis Davidson Edition: Preview Edition Publication Year: 2024 All rights reserved. No part of this publication may be reproduced, distributed, or transmitted in any form or by any means, including photocopying, recording, or other electronic or mechanical methods, without the prior written permission of the publisher, except in the case of brief quotations embodied in critical reviews and certain other non-commercial uses permitted by copyright law. For permission requests, write to the publisher, addressed “Attention: Permissions Coordinator,” at the address below. Publisher: Red Gate Books Cavendish House, Cambridge Business Park Cambridge, CB4 0XB United Kingdom ISBN: 978-1-3999-9678-5 Library of Congress Cataloging-in-Publication Data Introduction to PostgreSQL for the data professional. / Grant Fritchey and Ryan Booz. Includes bibliographical references and index. ISBN 978-1-3999-9678-5 1. PostgreSQL 2. Database Management 3. SQL Printed in United States of America
Page
4
i Table of Contents About the Authors ............................................................................................................................ x About the Tech Editor ..................................................................................................................... xi Forward ............................................................................................................................................ xii Preface ............................................................................................................................................ xiv Chapter 1: An Introduction to PostgreSQL .................................................................................... 1 What’s in a Name ............................................................................................................................ 2 How It All Began .............................................................................................................................. 3 Going Open-Source ..................................................................................................................... 4 PostgreSQL Governance ............................................................................................................ 5 Commitfest ...................................................................................................................................... 7 Why PostgreSQL and Why Now ................................................................................................... 10 Conclusion..................................................................................................................................... 11 Chapter 2: PostgreSQL Basics and Differences ......................................................................... 13 Feature Comparison ..................................................................................................................... 13 Extensibility ................................................................................................................................ 14 Built-in Job Scheduler................................................................................................................ 15 Query Hints ................................................................................................................................ 16 Query Plan Cache ..................................................................................................................... 16 Execution Plan Viewer............................................................................................................... 17 Terminology Differences ............................................................................................................... 18 Cluster vs. Instance ................................................................................................................... 18 Role vs User .............................................................................................................................. 18 Tuple vs. Row ............................................................................................................................ 19 Literal Object Qualifiers ............................................................................................................. 19 COPY vs. BULK INSERT .......................................................................................................... 20 TOAST ....................................................................................................................................... 20 SQL Differences ............................................................................................................................ 20
Page
5
ii Data Types ................................................................................................................................ 21 Conclusion .................................................................................................................................... 30 Chapter 3: Installing PostgreSQL ................................................................................................. 31 Minimum Hardware ...................................................................................................................... 31 Where To Get the Bits .................................................................................................................. 32 Linux Install ................................................................................................................................... 34 Windows Install ............................................................................................................................. 36 Containers .................................................................................................................................... 43 Conclusion .................................................................................................................................... 44 Chapter 4: PostgreSQL Tools ....................................................................................................... 45 psql ............................................................................................................................................... 45 Installing psql ............................................................................................................................ 46 Connecting to PostgreSQL from psql ....................................................................................... 48 Using psql ................................................................................................................................. 48 pgAdmin ........................................................................................................................................ 51 Installing pgAdmin ..................................................................................................................... 52 Using pgAdmin .......................................................................................................................... 54 DBeaver ........................................................................................................................................ 59 Installing DBeaver ..................................................................................................................... 59 Using DBeaver .......................................................................................................................... 60 Azure Data Studio ........................................................................................................................ 65 Installing Azure Data Studio...................................................................................................... 66 Working With Azure Data Studio .............................................................................................. 68 Conclusion .................................................................................................................................... 72 Chapter 5: Sever Configuration .................................................................................................... 73 Memory Settings ........................................................................................................................... 73 Modify Configuration Settings in PostgreSQL .......................................................................... 75 Caching Data Pages ..................................................................................................................... 75 Query Memory .............................................................................................................................. 76
Page
6
iii Number of Connections ................................................................................................................ 78 Ongoing Maintenance and Backup/Restores ............................................................................... 78 Query Plan Settings ...................................................................................................................... 80 Random Page Cost ................................................................................................................... 80 Effective Cache Size ................................................................................................................. 81 Just-In-Time Compilation........................................................................................................... 82 Conclusion..................................................................................................................................... 82 Chapter 6: Roles and Privileges .................................................................................................... 83 Host-based Authentication ............................................................................................................ 83 Cluster, Databases, and Roles ..................................................................................................... 86 Roles.......................................................................................................................................... 87 Users and Groups ..................................................................................................................... 87 Role Attributes ........................................................................................................................... 88 The Superuser Role .................................................................................................................. 90 Role Privileges .............................................................................................................................. 91 GRANT and REVOKE ............................................................................................................... 93 The PUBLIC Role ...................................................................................................................... 97 Object Ownership ...................................................................................................................... 98 Conclusion ............................................................................................................................... 105 Chapter 7: Creating Databases ................................................................................................... 107 What Is a Database ..................................................................................................................... 107 Creating a Database ................................................................................................................... 109 Modifying a Database ................................................................................................................. 113 Removing a Database ................................................................................................................. 116 Database Templates ................................................................................................................... 117 Tablespace .................................................................................................................................. 120 Conclusion................................................................................................................................... 122 Chapter 8: Extensions .................................................................................................................. 123 The History of Extensions ........................................................................................................... 123
Page
7
iv What Are Extensions? ................................................................................................................ 124 Available Extensions .................................................................................................................. 126 Installing Extensions ............................................................................................................... 128 Updating an Extension ............................................................................................................ 129 Dropping an Extension ............................................................................................................ 130 Making Extensions Available for Use ......................................................................................... 130 Extension Registries ............................................................................................................... 131 Pre-made Docker Containers ................................................................................................. 132 Linux Package Managers ....................................................................................................... 133 Cloud-Hosted Databases ........................................................................................................ 134 Two Words of Caution ................................................................................................................ 134 Extensions Across Environments ........................................................................................... 135 Extension Backup and Restore .............................................................................................. 135 Extensions to Try ........................................................................................................................ 136 pg_stat_statements ................................................................................................................. 136 postgis ..................................................................................................................................... 137 pg_hint_plan ........................................................................................................................... 137 pg_cron ................................................................................................................................... 138 postgres_fdw ........................................................................................................................... 139 pg_partman ............................................................................................................................. 140 pg_trgm ................................................................................................................................... 141 hypopg .................................................................................................................................... 142 Vector and AI Extensions ........................................................................................................... 142 pgvector .................................................................................................................................. 143 pgai ......................................................................................................................................... 143 azure_ai .................................................................................................................................. 144 Conclusion .................................................................................................................................. 144 Chapter 9: Core Object Types in PostgreSQL .......................................................................... 145 SQL Examples ............................................................................................................................ 145
Page
8
v Object Ownership ........................................................................................................................ 146 Core Object Types ...................................................................................................................... 146 Databases ............................................................................................................................... 146 Schema ................................................................................................................................... 147 Tables ...................................................................................................................................... 147 Sequences ............................................................................................................................... 152 Indexes .................................................................................................................................... 153 Functions ................................................................................................................................. 158 Procedures .............................................................................................................................. 159 View ......................................................................................................................................... 160 Materialized View .................................................................................................................... 161 Foreign Table .......................................................................................................................... 161 Trigger ..................................................................................................................................... 162 Types ....................................................................................................................................... 162 Domains .................................................................................................................................. 163 Conclusion................................................................................................................................... 163 Chapter 10: Introducing PL/pgSQL ............................................................................................. 165 Purpose of a Procedural Language ............................................................................................ 166 Basic PL/pgSQL Syntax .............................................................................................................. 166 Blocks ...................................................................................................................................... 167 Variables .................................................................................................................................. 168 Error Handling ......................................................................................................................... 169 Procedural Language .................................................................................................................. 171 IF/THEN ................................................................................................................................... 172 CASE ....................................................................................................................................... 173 Loops ....................................................................................................................................... 174 Creating Procedural Objects ....................................................................................................... 179 Functions ................................................................................................................................. 179 Procedures .............................................................................................................................. 183
Page
9
vi Cursors.................................................................................................................................... 186 Conclusion .................................................................................................................................. 189 Chapter 11: Query Tuning and Indexes ..................................................................................... 191 EXPLAIN ..................................................................................................................................... 191 Estimated Query Plans ........................................................................................................... 192 EXPLAIN Options ................................................................................................................... 193 Adding run-time statistics ........................................................................................................ 193 BUFFERS ............................................................................................................................... 195 Query Cost and Actual Time ................................................................................................... 198 Primary Node Types ................................................................................................................... 199 Scan Nodes ............................................................................................................................ 199 Join Nodes .............................................................................................................................. 200 Other Nodes ............................................................................................................................ 201 Additional Areas to Troubleshoot ............................................................................................... 201 First things first – work_mem .................................................................................................. 202 External Sort Disk ................................................................................................................... 202 Indexes ....................................................................................................................................... 205 B-Tree Deduplication .............................................................................................................. 206 Functional Indexes .................................................................................................................. 208 Partial Indexes ........................................................................................................................ 209 Composite Indexes ................................................................................................................. 210 Covering Indexes .................................................................................................................... 213 Logging Query Plans .................................................................................................................. 214 Auto Explain ............................................................................................................................ 214 Reading Execution Plans from the Log .................................................................................. 217 More Resources ......................................................................................................................... 217 Chapter 12: Backup and Restore................................................................................................ 219 Restore Strategy ......................................................................................................................... 220 Database Backups ..................................................................................................................... 221
Page
10
vii SQL Dump ............................................................................................................................... 222 File System Backup ................................................................................................................. 225 Base Backups .......................................................................................................................... 225 Comparing Backup Mechanisms ............................................................................................. 227 Restoring a Database ................................................................................................................. 228 Restoring to a Point in Time ........................................................................................................ 231 WAL Archiving ......................................................................................................................... 232 Recovery To a Point In Time ................................................................................................... 233 Conclusion................................................................................................................................... 235 Chapter 13: MVCC, VACUUM, and ANALYZE ............................................................................ 237 MVCC in PostgreSQL ................................................................................................................. 238 Row Versions .............................................................................................................................. 239 Row Versions in Practice ............................................................................................................ 240 Row Visibility ........................................................................................................................... 241 Transaction ID Space .............................................................................................................. 242 The Vacuum Process .................................................................................................................. 243 VACUUM and autovacuum ..................................................................................................... 243 Dead Tuples and Table Bloat .................................................................................................. 245 Freezing Live Tuples ............................................................................................................... 247 ANALYZE and autoanalyze ........................................................................................................ 247 Configuration and Maintenance Tasks ....................................................................................... 249 maintenance_work_mem ........................................................................................................ 249 Reindexing ............................................................................................................................... 250 Fill Factor ................................................................................................................................. 251 Heap-Only Tuples .................................................................................................................... 252 Conclusion................................................................................................................................... 254 Chapter 14: Replication and HA .................................................................................................. 255 Cloud Hosted Databases ............................................................................................................ 255 Replication................................................................................................................................... 256
Page
11
viii Physical Replication ................................................................................................................ 256 Logical Replication .................................................................................................................. 258 Replication Slots ..................................................................................................................... 258 Setting Up Streaming Replication............................................................................................... 260 Setting Up Logical Replication.................................................................................................... 267 Conclusion .................................................................................................................................. 275 Chapter 15: Monitoring PostgreSQL .......................................................................................... 277 Knowledge Drives Decisions ...................................................................................................... 278 Collecting Server Information ..................................................................................................... 279 Error Logs ............................................................................................................................... 279 Cumulative Statistics System.................................................................................................. 283 Query Performance Metrics ........................................................................................................ 290 Query Metrics at Execution ..................................................................................................... 290 Monitoring Query Metrics ........................................................................................................ 292 Conclusion .............................................................................................................................. 295 Chapter 16: PostgreSQL on the Cloud ...................................................................................... 297 Why the Cloud? .......................................................................................................................... 298 Why Platform as a Service (PaaS)? ........................................................................................... 299 PaaS Services ............................................................................................................................ 300 AWS ........................................................................................................................................ 300 Azure Database for PostgreSQL ............................................................................................ 303 Google Cloud Platform ............................................................................................................ 305 Conclusion .................................................................................................................................. 308 Chapter 17: Where To Go For More Learning ........................................................................... 309 PostgreSQL Documentation ....................................................................................................... 310 Books on PostgreSQL ................................................................................................................ 313 The Art of PostgreSQL ............................................................................................................ 313 Database Administration: The Complete Guide to DBA Practices and Procedures .............. 313 PostgreSQL Query Optimization: The Ultimate Guide to Building Efficient Queries .............. 314
Page
12
ix PostgreSQL Events ..................................................................................................................... 314 User Groups and Meetups ...................................................................................................... 315 Local Events ............................................................................................................................ 316 International Events ................................................................................................................. 317 Online Resources ........................................................................................................................ 319 Aggregations ........................................................................................................................... 319 Podcasts .................................................................................................................................. 320 Blogs ........................................................................................................................................ 321 Webinars ................................................................................................................................. 321 Conclusion................................................................................................................................... 322 Index ............................................................................................................................................ 325
Page
13
x About the Authors Ryan Booz Ryan is an Advocate at Redgate focusing on PostgreSQL. Ryan has been working as a PostgreSQL advocate, developer, DBA and product manager for more than 22 years, primarily working with time-series data on PostgreSQL and the Microsoft Data Platform. Ryan is a long-time DBA, starting with MySQL and Postgres in the late 90s. He spent more than 15 years working with SQL Server before returning to PostgreSQL full-time in 2018. He's at the top of his game when he's learning something new about the data platform or teaching others about the technology he loves. Grant Fritchey Grant Fritchey is a Data Platform MVP and AWS Community Builder with over 30 years’ experience in IT, including time spent in support and development. Grant works with multiple data platforms including PostgreSQL and SQL Server, as well as multiple cloud platforms. He has also developed in Python, C#, and Java. Grant writes books for Apress and Redgate. Grant presents at conferences and user groups, large and small, all over the world. He joined Redgate Software as a product advocate in January 2011.
Page
14
xi About the Tech Editor Robert Treat Robert Treat is a seasoned database professional with nearly three decades of experience working on mission critical, data intensive systems. A passionate open-source advocate and contributor, he is probably best known for his work with Postgres, where he has been recognized as a Major Contributor. His career has traversed a diverse set of organizations including DoorDash, Etsy, Amazon, National Geographic, and WebMD, where he has worked in both leadership and practitioner roles, honing his expertise in databases management and operations. As an independent community advocate, he dedicates his time to fostering open-source initiatives, speaking on a wide variety of topics including open source, devops, and scalable web operations, with a goal of empowering others and driving innovation and operability within the field.
Page
15
xii Foreword It is with great pleasure that I introduce this comprehensive guide to PostgreSQL, authored by two very respected figures in the database community, Grant Fritchey and Ryan Booz. Together, they have produced many blog posts and books about databases for years and years. As you read this book, this is very evident, making it a valuable resource. I have read this book as closely as I have any other book because I served as the copy editor for most of the book. I have known one of the authors for over 15 years, and the other for 2 now, but I have learned so much about PostgreSQL (and SQL Server and other topics) from them both over these years. Grant Fritchey, known for his extensive work with SQL Server, has over the past few years, added PostgreSQL to his skill set and brings a wealth of knowledge and practical insights about both to this topic. His experience is not just theoretical; it is grounded in real-world applications and challenges, making his contributions both relevant and actionable. Grant has been a part of the database community for many years now and has always been a wonderful teacher and all-around nice person to deal with. I met Ryan Booz through working with him at Redgate and he has taught me a lot about how PostgreSQL works (and as a SQL Server expert myself, there are some very interesting differences that aren’t always as obvious as you might expect, which is a part of why this book was written!) Everything I have said about how great it is to work with Grant goes exactly the same for Ryan. Ryan has been instrumental in educating the community through his regular online seminar series, "PostgreSQL 101," and his numerous articles on Simple-Talk.com. His passion for PostgreSQL and his ability to break down complex concepts into understandable terms have made him a trusted voice in the community. This book is a testament to their hard work and dedication. It provides a very nice introduction to PostgreSQL and includes hints throughout on how it is similar and different from other RDBMS, covering everything from installation and configuration to advanced performance tuning and optimization. Whether you are new to PostgreSQL or
Page
16
xiii looking to deepen your understanding, this book offers valuable insights and practical advice that will help you succeed. I will go so far as to say that this book was one of the easiest I have ever had the pleasure to edit, and that includes all my own books as well. I am confident that readers will find this book to be an essential addition to their technical library and am proud to admit that here in this foreword. Louis Davidson (Simple Talk Editor)
Page
17
xiv Preface There is no denying that PostgreSQL is growing in popularity. You can look at the history of its growth on the DB-Engines Ranking web site if there’s any doubt. As more and more organizations begin to manage some, or all, of their data in PostgreSQL, a growing number of people are going to have to know how PostgreSQL works. We’re writing this book for you. Whether you’re a Database Administrator (DBA) who has to learn how to maintain a whole new data platform, a developer looking for new and better ways to manage information persistence, or even someone fresh in the IT field looking to expand your skill set, this book is for you. However, that said, we do assume a certain amount of knowledge of databases in general. We have comparisons to other data platforms, frequently Microsoft SQL Server, but others as well, to help establish context. So, this book is more a “beginners in PostgreSQL” rather than a “beginners in databases” in general style book. Your authors have a very large amount of accumulated knowledge of databases, database management, and database development. We tried to add as much of that into the book as we can. There’s guidance for best use of PostgreSQL in a lot of the chapters, not simply descriptions of how things work. We know, based on our own blunders and learning curve, that why you’re doing something matters as much as how. So, we put that into the book as well. Since this is very much an introductory book for PostgreSQL, the best way to get the maximum value from the book is to follow the flow of the book. This is especially true because we use code and structures introduced in earlier chapters, later in the book, so skipping around could cause confusion. However, most chapters stand on their own, for the most part. While it is certainly possible to skip around, you may hit snags when you run the sample code. Speaking of sample code, you’ll generally see it looking like this:
Page
18
xv Listing X-1. This is sample code SELECT cola FROM sometable; The code will be introduced in some manner, you’ll see it in a different format, and you’ll see the Listing caption with a chapter and the number of the listing within the chapter. This makes it possible to refer back to code within a chapter. You will also see code that’s shown in-line with the text, just like sometable here. You’ll note that this has a different font, to help set it apart from the rest of the text. Speaking of Notes, and Warnings, you’ll see this throughout the book: NOTE/WARNING/CAUTION: These are points that we don’t want to get lost in the text because they’re important. This way, you can spot them easily. We broke the chapters down into sections that will have headings to let you know what they’re about. Hopefully this helps navigation as you read the book. We are both excited to share this book with you. There are several reasons for this. While writing a book is somewhat difficult, and very time consuming, when you’re done, there’s a real sense of accomplishment. Also, both of us have run into issues while learning PostgreSQL and thought to ourselves, if only someone had pointed this out for me. Well, we’ve tried to do that throughout the book in order to help you on your journey. Finally, and most important, we both really enjoy being able to help others. We enjoy it even more when it’s helping others get going on the PostgreSQL data platform, because we’ve had a lot of fun exploring this space. Our goal is to help you on that journey too. Thanks for reading.
Page
19
(This page has no text content)
Page
20
1 1 An Introduction to PostgreSQL PostgreSQL is unique among other popular databases both in its foundations and in the way that it is developed and maintained. For users primarily experienced with commercial relational databases, this can sometimes be a point of intrigue… and confusion. PostgreSQL was, has been, and will continue to be a true open-source database run by the community. This means that no one person, group of people, or organization owns PostgreSQL. Anybody can use it without paying a license, and anybody can derive new work and other databases with the code as they see fit. (Spoiler alert: hundreds of companies and organizations have created database variants using the PostgreSQL source code over the years.) If you are new to PostgreSQL and are planning to use it for upcoming projects, it’s useful that you understand some of the background that makes PostgreSQL what it is today. This history gives you background on how the project works, how the community has evolved, and what you can expect for support and feature updates in the future. Plus, it’s just an interesting project that many of us have come to rely on and it’s fascinating to understand the differences from what you know today with whatever RDBMS you’re using.
The above is a preview of the first 20 pages. Register to read the complete e-book.
AI Reading Assistant
Whole-book reading guide from stratified index samples; jump to passages in the text
AI guide
# 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.
##
Passage locations
Page 3
ertain other non-commercial uses permitted by copyright law. For permission requests, write to the publisher, addressed “Attention: Permissions Coordinator,”...
View in text
Page 4
............ 45 psql ...........................................................................................................................................
View in text
Page 7
................................................................ 132 Linux Package Managers ....................................................................
View in text
Page 4
.... 167 Variables .................................................................................................................................. 168 Err...
View in text
Recommended for You
{{#thumbnailUrl}}
{{/thumbnailUrl}}
{{^thumbnailUrl}}
{{/thumbnailUrl}}
Loading recommended books...
Failed to load, please try again later
Tip the Site
Scan the WeChat Pay or Alipay code to tip. No login required.
WeChat Pay
Alipay