PostgreSQL High Performance Cookbook

Get to know effective ways to improve PostgreSQL’s performance and master query optimization, and database monitoring.

PostgreSQL High Performance Cookbook

This ebook is included in a Mapt subscription
Chitij Chauhan, Dinesh Kumar

Get to know effective ways to improve PostgreSQL’s performance and master query optimization, and database monitoring.
$0.00
$22.00
$54.99
$29.99p/m after trial
RRP $43.99
RRP $54.99
Subscription
eBook
Print + eBook
Start 30 Day Trial
Subscribe and access every Packt eBook & Video.
 
  • 5,000+ eBooks & Videos
  • 50+ New titles a month
  • 1 Free eBook/Video to keep every month
Start Free Trial
 
Preview in Mapt

Book Details

ISBN 139781785284335
Paperback360 pages

Book Description

PostgreSQL is one of the most powerful and easy to use database management systems. It has strong support from the community and is being actively developed with a new release every year. PostgreSQL supports the most advanced features included in SQL standards. It also provides NoSQL capabilities and very rich data types and extensions. All of this makes PostgreSQL a very attractive solution in software systems.

If you run a database, you want it to perform well and you want to be able to secure it. As the world’s most advanced open source database, PostgreSQL has unique built-in ways to achieve these goals. This book will show you a multitude of ways to enhance your database’s performance and give you insights into measuring and optimizing a PostgreSQL database to achieve better performance.

This book is your one-stop guide to elevate your PostgreSQL knowledge to the next level. First, you’ll get familiarized with essential developer/administrator concepts such as load balancing, connection pooling, and distributing connections to multiple nodes. Next, you will explore memory optimization techniques before exploring the security controls offered by PostgreSQL. Then, you will move on to the essential database/server monitoring and replication strategies with PostgreSQL. Finally, you will learn about query processing algorithms.

Table of Contents

Chapter 1: Database Benchmarking
Introduction
CPU benchmarking
Memory benchmarking
Disk benchmarking
Performing a seek rate test
Working with the fsync commit rate
Checking IOPS
Storage sizing
Discussing RAID levels
Configuring pgbench
Running read/write pgbench test cases
Chapter 2: Server Configuration and Control
Introduction
Starting the server manually
Stopping the server quickly
Stopping the server in an emergency
Reloading server configuration
Restarting the database server quickly
Tuning connection-related parameters
Tuning query-related parameters
Tuning logging-related parameters
Chapter 3: Device Optimization
Introduction
Understanding memory units in PostgreSQL
Handling Linux/Unix memory parameters
CPU scheduling parameters
Disk tuning parameters
Identifying checkpoint overhead
Analyzing buffer cache contents
Chapter 4: Monitoring Server Performance
Introduction
Monitoring CPU usage
Monitoring paging and swapping
Tracking CPU consuming processes
Monitoring CPU load
Identifying CPU bottlenecks
Identifying disk I/O bottlenecks
Monitoring system load
Tracking historical CPU usage
Tracking historical memory usage
Monitoring disk space
Monitoring network status
Chapter 5: Connection Pooling and Database Partitioning
Introduction
Installing pgpool-II
Configuring pgpool and testing the setup
Installing PgBouncer
Connection pooling using PgBouncer
Managing PgBouncer
Implementing partitioning
Managing partitions
Installing PL/Proxy
Partitioning with PL/Proxy
Chapter 6: High Availability and Replication
Introduction
Setting up hot streaming replication
Replication using Slony
Replication using Londiste
Replication using Bucardo
Replication using DRBD
Setting up a Postgres-XL cluster
Chapter 7: Working with Third-Party Replication Management Utilities
Introduction
Setting up Barman
Backup and recovery using Barman
Setting up OmniPITR
WAL management with OmniPITR
Setting up repmgr
Using repmgr to create replica
Setting up walctl
Using walctl to create replica
Chapter 8: Database Monitoring and Performance
Introduction
Checking active sessions
Finding out what the users are currently running
Finding blocked sessions
Dealing with deadlocks
Table access statistics
Logging slow statements
Determining disk usage
Preventing page corruption
Routine reindexing
Generating planner statistics
Tuning with background writer statistics
Chapter 9: Vacuum Internals
Introduction
Dealing with bloating tables and indexes
Vacuum and autovacuum
Freezing and transaction ID wraparound
Monitoring vacuum progress
Control bloat using transaction age
Chapter 10: Data Migration from Other Databases to PostgreSQL and Upgrading the PostgreSQL Cluster
Introduction
Using pg_dump to upgrade data
Using the pg_upgrade utility for version upgrade
Replicating data from other databases to PostgreSQL using Goldengate
Chapter 11: Query Optimization
Introduction
Using sample data sets
Timing overhead
Studying hot and cold cache behavior
Clearing the cache
Query plan node structure
Generating an explain plan
Computing basic cost
Running sequential scans
Running bitmap heap and index scan
Aggregate and hash aggregate
Running CTE scan
Nesting loops
Working with hash and merge join
Grouping
Working with set operations
Working on semi and anti joins
Chapter 12: Database Indexing
Introduction
Measuring query and index block statistics
Index lookup
Comparing indexed scans and sequential scans
Clustering against an index
Concurrent indexes
Combined indexes
Partial indexes
Finding unused indexes
Forcing a query to use an index
Detecting a missing index

What You Will Learn

  • Build replication strategies for homogeneous and heterogeneous databases
  • Test and build a powerful machine with multiple bench marking techniques
  • Get to know a few SQL injection techniques
  • Find out how to manage the replication using multiple tools
  • Benchmark the database server using multiple strategies
  • Work with the query processing algorithms and their internal behaviors
  • Build a proper plan to upgrade or migrate to PostgreSQL from other databases
  • See the essential database load balancing techniques and the various partitioning approaches PostgreSQL provides
  • Learn memory optimization techniques and database server configurations

Authors

Table of Contents

Chapter 1: Database Benchmarking
Introduction
CPU benchmarking
Memory benchmarking
Disk benchmarking
Performing a seek rate test
Working with the fsync commit rate
Checking IOPS
Storage sizing
Discussing RAID levels
Configuring pgbench
Running read/write pgbench test cases
Chapter 2: Server Configuration and Control
Introduction
Starting the server manually
Stopping the server quickly
Stopping the server in an emergency
Reloading server configuration
Restarting the database server quickly
Tuning connection-related parameters
Tuning query-related parameters
Tuning logging-related parameters
Chapter 3: Device Optimization
Introduction
Understanding memory units in PostgreSQL
Handling Linux/Unix memory parameters
CPU scheduling parameters
Disk tuning parameters
Identifying checkpoint overhead
Analyzing buffer cache contents
Chapter 4: Monitoring Server Performance
Introduction
Monitoring CPU usage
Monitoring paging and swapping
Tracking CPU consuming processes
Monitoring CPU load
Identifying CPU bottlenecks
Identifying disk I/O bottlenecks
Monitoring system load
Tracking historical CPU usage
Tracking historical memory usage
Monitoring disk space
Monitoring network status
Chapter 5: Connection Pooling and Database Partitioning
Introduction
Installing pgpool-II
Configuring pgpool and testing the setup
Installing PgBouncer
Connection pooling using PgBouncer
Managing PgBouncer
Implementing partitioning
Managing partitions
Installing PL/Proxy
Partitioning with PL/Proxy
Chapter 6: High Availability and Replication
Introduction
Setting up hot streaming replication
Replication using Slony
Replication using Londiste
Replication using Bucardo
Replication using DRBD
Setting up a Postgres-XL cluster
Chapter 7: Working with Third-Party Replication Management Utilities
Introduction
Setting up Barman
Backup and recovery using Barman
Setting up OmniPITR
WAL management with OmniPITR
Setting up repmgr
Using repmgr to create replica
Setting up walctl
Using walctl to create replica
Chapter 8: Database Monitoring and Performance
Introduction
Checking active sessions
Finding out what the users are currently running
Finding blocked sessions
Dealing with deadlocks
Table access statistics
Logging slow statements
Determining disk usage
Preventing page corruption
Routine reindexing
Generating planner statistics
Tuning with background writer statistics
Chapter 9: Vacuum Internals
Introduction
Dealing with bloating tables and indexes
Vacuum and autovacuum
Freezing and transaction ID wraparound
Monitoring vacuum progress
Control bloat using transaction age
Chapter 10: Data Migration from Other Databases to PostgreSQL and Upgrading the PostgreSQL Cluster
Introduction
Using pg_dump to upgrade data
Using the pg_upgrade utility for version upgrade
Replicating data from other databases to PostgreSQL using Goldengate
Chapter 11: Query Optimization
Introduction
Using sample data sets
Timing overhead
Studying hot and cold cache behavior
Clearing the cache
Query plan node structure
Generating an explain plan
Computing basic cost
Running sequential scans
Running bitmap heap and index scan
Aggregate and hash aggregate
Running CTE scan
Nesting loops
Working with hash and merge join
Grouping
Working with set operations
Working on semi and anti joins
Chapter 12: Database Indexing
Introduction
Measuring query and index block statistics
Index lookup
Comparing indexed scans and sequential scans
Clustering against an index
Concurrent indexes
Combined indexes
Partial indexes
Finding unused indexes
Forcing a query to use an index
Detecting a missing index

Book Details

ISBN 139781785284335
Paperback360 pages
Read More

Read More Reviews