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

About the Reviewers

Baji Shaik is a database administrator and developer. He is a co-author of PostgreSQL Development Essentials and has tech-reviewed Troubleshooting PostgreSQL by Packt Publishing. He is currently working as a database consultant at OpenSCG. He has an engineering degree in telecommunications, and had started his career as a C# and Java developer. Baji started working with databases in 2011, and over the years he has worked with Oracle, PostgreSQL, and Greenplum. His background spans a length and breadth of expertise and experience in SQL/NoSQL database technologies. He has good knowledge of automation, orchestration, and DevOps in a cloud environment. He likes to watch movies, read books, and write technical blogs. He also loves to spend time with family. Baji is a certified PostgreSQL professional.

Feng Tan is from China. His nickname is Francs. He was a PostgreSQL DBA at SkyMobi (NASDAQ: MOBI) for more than 5 years, where he was maintaining more than 100 PostgreSQL instances. He gave presentations at the China PostgreSQL conference on topics such as Oracle VS PostgreSQL and PostgreSQL 9.4 new features.

Feng Tan likes to share PostgreSQL technology in his blog at http://francs3.blog.163.com/. He is also one of the translators of PostgreSQL 9 Administration Cookbook Chinese Edition.

Currently, he serves as the open source database administrator at China Mobile Group Zhejiang Co. Ltd.

Previous Section
Next Chapter