Leanpub Header

Skip to main content

The PostgreSQL Administrator's Handbook

Architecture, Operations, and Performance Tuning for Production Systems

This book is 100% completeLast updated on 2026-07-13

Master PostgreSQL administration with a practical guide to deploying, securing, optimizing, and operating production databases. Covering PostgreSQL 17 and 18, this book combines real-world examples, hands-on labs, and proven best practices for reliable, high-performance systems.

Minimum price

$19.00

$29.00

You pay

Author earns

$

Also available for 1 book credit with a Reader Membership

PDF
EPUB
122
Pages
About

About

About the Book

This is a complete guide to administering PostgreSQL in production environments. Whether you are deploying your first database server or managing a fleet of high-availability clusters, this book walks you through everything from architecture internals and installation to security hardening, backup and recovery strategies, replication setups, performance tuning, and automated operations. It covers PostgreSQL 17 and 18 (the latest stable releases as of mid-2026) with practical examples, real-world configurations, hands-on labs, and production-tested best practices drawn from the official documentation and community expertise.

Bundles

Bundles that include this book

Author

About the Author

Steve Publications

Steve is a technology professional with more than 20 years of experience in software development, server infrastructure, cybersecurity, vulnerability research and reverse engineering. Throughout his career, he has designed, secured, analyzed and tested complex software and infrastructure, with a particular focus on understanding how systems fail and how they can be made more secure.

Outside of work, Steve enjoys sharing knowledge with the technology community. He collaborates with researchers, industry experts and technology professionals to write practical books covering software development, cybersecurity, cloud computing, networking, DevOps, artificial intelligence and enterprise technologies. His books focus on practical learning through clear explanations, real-world examples and hands-on exercises. With more than two decades of industry experience, his goal is to help IT professionals, students and technology enthusiasts build useful skills and stay current in a rapidly changing industry.

We believe readers deserve to know how our books are created. Most of our authors are not native English speakers, so we use AI to help translate, proofread manuscripts, fix grammar, improve sentence structure and make technical explanations easier to read. AI is used as an editing tool only. It does not replace the research, technical knowledge or hands-on experience behind our books. Some of our authors also prefer to remain anonymous for privacy or professional reasons. In those cases, we publish their work under a different name. The author's name may be different, but the quality of the content and our review process remain the same.

Every book is written, reviewed and maintained by experienced technology professionals, with contributions from our private technical community of more than 400 engineers and researchers from Ukraine, Belarus and Russia. We spend far more time validating technical accuracy and keeping our content up to date than generating text. We are always interested in working with experienced professionals who have deep expertise in a particular technology or domain. If you would like to publish a book with us or help review an existing manuscript, we'd love to hear from you. Send us a message describing your area of expertise. We are especially interested in niche technologies, specialized skills and emerging topics that are underrepresented in existing technical literature.

If you look through the contents of our books, you'll see practical examples, detailed explanations and material that is regularly updated. Our goal is to publish books that professionals can actually rely on, not low-effort AI-generated content. If you ever feel that one of our books does not meet that standard, Leanpub offers a 60-day money-back guarantee. Feel free to request a refund if you are not satisfied with your purchase.

Contents

Table of Contents

Architecture, Operations, and Performance Tuning for Production Systems

Introduction: Why PostgreSQL Administration Matters

Chapter 1: Understanding PostgreSQL Architecture

  1. The Client-Server Model and Process Architecture
  2. Shared Memory and Background Processes
  3. Multiversion Concurrency Control (MVCC) Explained
  4. Write-Ahead Logging and the WAL Pipeline
  5. Storage Layout: Tables, Indexes, and Catalogs
  6. Chapter 1 Summary

Chapter 2: Installation and Initial Configuration

  1. Choosing an Installation Method: Packages vs Source vs Containers
  2. Initializing the Database Cluster with initdb
  3. Understanding postgresql.conf and Its Include Chain
  4. Configuring pg_hba.conf for Access Control
  5. Setting Up PostgreSQL as a System Service
  6. Hands-on Lab: Install and Configure PostgreSQL
  7. Chapter 2 Summary

Chapter 3: Authentication, Security, and Role Management

  1. Authentication Methods: trust, peer, md5, scram-sha-256, and Certificate-Based
  2. Designing a Secure pg_hba.conf Strategy
  3. Setting Up SSL/TLS for Encrypted Connections
  4. Roles, Privileges, and the Principle of Least Privilege
  5. Row-Level Security and Data Encryption at Rest
  6. Hands-on Lab: Security Configuration
  7. Chapter 3 Summary

Chapter 4: Configuration Deep Dive: Memory, WAL, and Performance Parameters

  1. Memory Configuration: shared_buffers, work_mem, and effective_cache_size
  2. WAL and Checkpoint Tuning for Write Workloads
  3. Connection Management and max_connections
  4. Autovacuum Parameters and When to Override Defaults
  5. Query Planner Settings and Cost Parameters
  6. Hands-on Lab: Configuration Tuning
  7. Chapter 4 Summary

Chapter 5: Backup Strategies and Point-in-Time Recovery

  1. Backup Strategy Fundamentals: RPO, RTO, and Method Selection
  2. Logical Backups with pg_dump and pg_restore
  3. Physical Backups with pg_basebackup
  4. Continuous Archiving and Point-in-Time Recovery (PITR)
  5. Testing Restores: The Backup That Isn’t Tested Is Not a Backup
  6. Hands-on Lab: Backup and Restore
  7. Chapter 5 Summary

Chapter 6: Replication and Data Distribution

  1. Streaming Replication: Architecture and Configuration
  2. Replication Slots and WAL Retention
  3. Synchronous Replication: Commit Modes and Trade-offs
  4. Logical Replication for Selective Data Distribution
  5. Monitoring Replication Lag and Failover Readiness
  6. Hands-on Lab: Set Up Streaming Replication
  7. Chapter 6 Summary

Chapter 7: High Availability and Failover

  1. The High Availability Problem in PostgreSQL
  2. Automated Failover with Patroni and Distributed Stores
  3. Connection Pooling with PgBouncer
  4. Load Balancing and Virtual IPs with HAProxy and Keepalived
  5. Testing Failover: Chaos Engineering for PostgreSQL
  6. Hands-on Lab: Set Up Patroni HA Cluster
  7. Chapter 7 Summary

Chapter 8: Indexing Strategies and Query Optimization

  1. Understanding Query Plans with EXPLAIN and EXPLAIN ANALYZE
  2. B-Tree Indexes: The Workhorse of PostgreSQL
  3. Specialized Index Types: Hash, GiST, GIN, and BRIN
  4. Partial, Unique, and Covering Index Strategies
  5. Common Query Anti-Patterns and How to Fix Them
  6. Hands-on Lab: Query Optimization with EXPLAIN
  7. Chapter 8 Summary

Chapter 9: Performance Tuning in Production

  1. Characterizing Your Workload: Read-Heavy vs Write-Heavy
  2. Hardware Considerations: CPU, RAM, Storage, and Network
  3. Monitoring with pg_stat_activity, pg_stat_statements, and pg_stat_progress
  4. Connection Pooling for High-Concurrency Applications
  5. I/O Optimization and Asynchronous I/O (PostgreSQL 18+)
  6. Hands-on Lab: Performance Monitoring
  7. Chapter 9 Summary

Chapter 10: Maintenance, Vacuuming, and Table Health

  1. The Autovacuum Daemon: Mechanics and Tuning
  2. Transaction ID and Multixact Wraparound Prevention
  3. Detecting and Managing Table and Index Bloat
  4. Statistics Collection: ANALYZE and Beyond
  5. Routine Maintenance Tasks and Scheduling
  6. Hands-on Lab: Maintenance and Vacuum Tuning
  7. Chapter 10 Summary

Chapter 11: Partitioning, Extensions, and Advanced Features

  1. Declarative Partitioning: Range, List, and Hash Strategies
  2. Partition Maintenance: ATTACH, DETACH, and Automated Management
  3. Essential Built-in Extensions: pg_stat_statements, pg_cron, pg_trgm
  4. Specialized Extensions: pgvector, TimescaleDB, PostGIS, Citus
  5. New Features in PostgreSQL 18: uuidv7(), Virtual Generated Columns, and More
  6. Hands-on Lab: Partitioning and Extensions
  7. Chapter 11 Summary

Chapter 12: Automation, Cloud Deployments, and Upgrades

  1. Automating PostgreSQL Administration with Ansible and Terraform
  2. Managed PostgreSQL: AWS RDS, Azure Database for PostgreSQL, GCP Cloud SQL
  3. Minor Version Upgrades: The Simple Path
  4. Major Version Upgrades: pg_upgrade and Logical Replication Migration
  5. Troubleshooting Common Production Issues
  6. Hands-on Lab: Cloud Deployment and Upgrade
  7. Chapter 12 Summary

Conclusion

References

Get the free sample chapters

Click the buttons to get the free sample in PDF or EPUB, or read the sample online here

The Leanpub 60 Day 100% Happiness Guarantee

Within 60 days of purchase you can get a 100% refund on any Leanpub purchase, in two clicks.

See full terms...

Earn $8 on a $10 Purchase, and $16 on a $20 Purchase

We pay 80% royalties on purchases of $7.99 or more, and 80% royalties minus a 50 cent flat fee on purchases between $0.99 and $7.98. You earn $8 on a $10 sale, and $16 on a $20 sale. So, if we sell 5000 non-refunded copies of your book for $20, you'll earn $80,000.

(Yes, some authors have already earned much more than that on Leanpub.)

In fact, authors have earned over $15 million writing, publishing and selling on Leanpub.

Learn more about writing on Leanpub

Free Updates. DRM Free.

If you buy a Leanpub book, you get free updates for as long as the author updates the book! Many authors use Leanpub to publish their books in-progress, while they are writing them. All readers get free updates, regardless of when they bought the book or how much they paid (including free).

Most Leanpub books are available in PDF (for computers) and EPUB (for phones, tablets and Kindle). The formats that a book includes are shown at the top right corner of this page.

Finally, Leanpub books don't have any DRM copy-protection nonsense, so you can easily read them on any supported device.

Learn more about Leanpub's ebook formats and where to read them

Write and Publish on Leanpub

You can use Leanpub to easily write, publish and sell in-progress and completed ebooks and online courses!

Leanpub is a powerful platform for serious authors, combining a simple, elegant writing and publishing workflow with a store focused on selling in-progress ebooks.

Leanpub is a magical typewriter for authors: just write in plain text, and to publish your ebook, just click a button. (Or, if you are producing your ebook your own way, you can even upload your own PDF and/or EPUB files and then publish with one click!) It really is that easy.

Learn more about writing on Leanpub