Chapter 1, PostgreSQL Versions, introduces how PostgreSQL performance has improved in the most recent versions of the database. 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 are 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 lay out your database on disk. Some common and effective filesystem tuning tweaks are also discussed.
Chapter 5, Memory for Database Caching, digs into how the database is stored on disk and 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 that 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. The settings that may cause you some trouble if you change them are pointed out too.
Chapter 7, Routine Maintenance, starts by explaining how PostgreSQL determines which 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 ever-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 number of data blocks read to answer a query. That approach allows for thoroughly investigating common questions such as why a query is using a sequential scan instead of an index in a robust way. In this chapter, we present new PostgreSQL 10 index types.
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. This chapter also covers the new way to perform searches in multi-core mode.
Chapter 11, Database Activity and Statistics, looks at the statistics collected inside the database, and which of them are useful for finding 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 a large number 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 which 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 writable master. This chapter also covers the new logical replication method.
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. The approaches discussed include the standard single-node database table partitioning and the new declarative partition method.
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 some examples. This chapter ends with a detailed review of which performance-related features have changed between each version of PostgreSQL from 8.1 to 10.3. Sometimes, the best way to avoid a common problem is to upgrade to a version in which it doesn't happen anymore.
