Book Image

PostgreSQL High Performance Cookbook

By : Chitij Chauhan, Dinesh Kumar
Book Image

PostgreSQL High Performance Cookbook

By: Chitij Chauhan, Dinesh Kumar

Overview of this book

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.

Related Content you might be interested in

Current Title:

Small book image
PostgreSQL High Performance Cookbook
Chitij Chauhan | Dinesh Kumar
Mar, 2017 | 360 pages
No titles found
Table of Contents (19 chapters)
PostgreSQL High Performance Cookbook
PostgreSQL High Performance Cookbook
Credits
Credits
About the Authors
About the Authors
About the Reviewers
About the Reviewers
www.PacktPub.com
www.PacktPub.com
Customer Feedback
Customer Feedback
Preface
Preface
Free Chapter
1
Database Benchmarking
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
2
Server Configuration and Control
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
3
Device Optimization
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
4
Monitoring Server Performance
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
5
Connection Pooling and Database Partitioning
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
6
High Availability and Replication
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
7
Working with Third-Party Replication Management Utilities
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
8
Database Monitoring and Performance
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
9
Vacuum Internals
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
10
Data Migration from Other Databases to PostgreSQL and Upgrading the PostgreSQL Cluster
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
11
Query Optimization
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
12
Database Indexing
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

Measuring query and index block statistics

In this recipe, we will be discussing how to measure the index statistics, using various catalog views.

Getting ready

PostgreSQL offers a few catalog views and extensions, which are enough to study the index usage statistics. The catalog views are pg_stat_user_indexes and pg_statio_user_indexes. These give the index usage statistics, and the extension pgstattuple provides insight into the details of the index by reading its physical files.

How to do it...

  1. Let's get a sample non-primary key index to measure its statistics, as follows:

            benchmarksql=# SELECT indexrelid::regclass FROM
        pg_index WHERE indisprimary IS FALSE AND
        indrelid::regclass::text='bmsql_item' LIMIT 
        1; 
        indexrelid  
        ------------ 
        pric_idx 
        (1 row)

  2. Let's reset the statistics using the pg_stat_reset function, as follows:

            benchmarksql=# SELECT pg_stat_reset(); 
        pg_stat_reset  
        --------------- 
   
 ...
Previous Section
End of Section 3
Next Section