What this book covers
Chapter 1, PostgreSQL Versions introduces how PostgreSQL performance has improved in the most recent versions of the databases. It makes a case for using the most recent version feasible, in contrast to the common presumption that newer versions of any software are buggier and slower than their predecessors.
Chapter 2, Database Hardware discusses how the main components in server hardware, including processors, memory, and disks, need to be carefully selected for reliable database storage and a balanced budget. In particular, accidentally using volatile write-back caching in disk controllers and drives can easily introduce database corruption.
Chapter 3, Database Hardware Benchmarking moves on to quantifying the different performance aspects of database hardware. Just how fast is the memory and raw drives in your system? Does performance scale properly as more drives are added?
Chapter 4, Disk Setup looks at popular filesystem choices and suggests the trade-offs of various ways to layout your database on disk. Some common, effective filesystem tuning tweaks are also discussed.
Chapter 5, Memory for Database Caching digs into how the database is stored on disk, in memory, and how the checkpoint process serves to reconcile the two safely. It also suggests how you can actually look at the data being cached by the database, to confirm whether what's being stored in memory matches what you'd expect to be there.
Chapter 6, Server Configuration Tuning covers the most important settings in the postgresql.conf file, what they mean, and how you should set them. And the settings you can cause trouble by changing are pointed out, too.
Chapter 7, Routine Maintenance starts by explaining how PostgreSQL determines what rows are visible to which clients. The way visibility information is stored requires a cleanup process named VACUUM to reuse leftover space properly. Common issues and general tuning suggestions for it and the always running autovacuum are covered. Finally, there's a look at adjusting the amount of data logged by the database, and using a query log analyzer on the result to help find query bottlenecks.
Chapter 8, Database Benchmarking investigates how to get useful benchmark results from the built-in pgbench testing program included with PostgreSQL.
Chapter 9, Database Indexing introduces indexes in terms of how they can reduce the amount of data blocks read to answer a query. That approach allows for thoroughly investigating common questions like why a query is using a sequential scan instead of an index in a robust way.
Chapter 10, Query Optimization is a guided tour of the PostgreSQL optimizer, exposed by showing the way sample queries are executed differently based on what they are asking for and how the database parameters are set.
Chapter 11, Database Activity and Statistics looks at the statistics collected inside the database, and which of them are useful to find problems. The views that let you watch query activity and locking behavior are also explored.
Chapter 12, Monitoring and Trending starts with how to use basic operating system monitoring tools to determine what the database is doing. Then it moves onto suggestions for trending software that can be used to graph this information over time.
Chapter 13, Pooling and Caching explains the difficulties you can encounter when large numbers of connections are made to the database at once. Two types of software packages are suggested to help: connection poolers, to better queue incoming requests, and caches that can answer user requests without connecting to the database.
Chapter 14, Scaling with Replication covers approaches for handling heavier system loads by replicating the data across multiple nodes, typically a set of read-only nodes synchronized to a single writeable master.
Chapter 15, Partitioning Data explores how data might be partitioned into subsets usefully, such that queries can execute against a smaller portion of the database. Approaches discussed include the standard single node database table partitioning, and using PL/Proxy with its associated toolset to build sharded databases across multiple nodes.
Chapter 16, Avoiding Common Problems discusses parts of PostgreSQL that regularly seem to frustrate newcomers to the database. Bulk loading, counting records, and foreign key handling are examples. This chapter ends with a detailed review of what performance related features changed between each version of PostgreSQL from 8.1 to 9.0. Sometimes, the best way to avoid a common problem is to upgrade to version where it doesn't happen anymore.
