Preface
PostgreSQL has become an increasingly viable database platform to serve as storage for applications, from classic corporate database use to the latest web apps. But getting the best performance from it is not an easy subject to learn. You need just the right combination of thumb rule to get started, solid monitoring, and maintenance to keep your system running as well as suggestions for troubleshooting, and add-on tools to add the features the core database doesn't try to handle on its own. This book is an update of the book PostgreSQL 9.6 High Performance.
Who this book is for
This book is aimed at intermediate to advanced database administrators who are using, or planning to use, PostgreSQL. Portions will also interest systems administrators looking to build or monitor a PostgreSQL installation, as well as developers interested in advanced database internals that impact application design.
What this book covers
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.
To get the most out of this book
In order for this book to be useful, you need at least access to a PostgreSQL client that is allowed to execute queries on a server. Ideally, you'll also be the server administrator. Full client and server packages for PostgreSQL are available for most popular operating systems at http://www.postgresql.org/download/. All the examples here are executed on Command Prompt, usually running the psql program. This makes them applicable to most platforms. It's straightforward to do many of these operations using a GUI tool for PostgreSQL instead, such as the pgAdmin program. Some scripts that are written in the bash scripting language are provided.
Download the example code files
You can download the example code files for this book from your account at www.packtpub.com. If you purchased this book elsewhere, you can visit www.packtpub.com/support and register to have the files emailed directly to you.
You can download the code files by following these steps:
- Log in or register at www.packtpub.com.
- Select the
SUPPORTtab. - Click on
Code Downloads & Errata. - Enter the name of the book in the
Searchbox and follow the onscreen instructions.
Once the file is downloaded, please make sure that you unzip or extract the folder using the latest version of:
- WinRAR/7-Zip for Windows
- Zipeg/iZip/UnRarX for Mac
- 7-Zip/PeaZip for Linux
The code bundle for the book is also hosted on GitHub at https://github.com/PacktPublishing/PostgreSQL-10-High-Performance. In case there's an update to the code, it will be updated on the existing GitHub repository.
We also have other code bundles from our rich catalog of books and videos available at https://github.com/PacktPublishing/. Check them out!
Download the color images
We also provide a PDF file that has color images of the screenshots/diagrams used in this book. You can download it here: http://www.packtpub.com/sites/default/files/downloads/PostgreSQL10HighPerformance_ColorImages.pdf.
Conventions used
There are a number of text conventions used throughout this book.
CodeInText: Indicates code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles. Here is an example: "The sync here is to try and flush all the data to the disk before we just blow away the caches."
A block of code is set as follows:
for each outer row:
for each inner row:
if join condition is true:
output combined rowAny command-line input or output is written as follows:
EXPLAIN ANALYZE SELECT * FROM orders WHERE customerid IN (SELECT customerid FROM customers WHERE customerid=1000 OR customerid=2000);Bold: Indicates a new term, an important word, or words that you see onscreen. For example, words in menus or dialog boxes appear in the text like this. Here is an example: "HD Tune includes a Random Access test that gives its results in terms of the standard IOPS figure, at various block sizes."
Note
Warnings or important notes appear like this.
Note
Tips and tricks appear like this.
Get in touch
Feedback from our readers is always welcome.
General feedback: Email [email protected] and mention the book title in the subject of your message. If you have questions about any aspect of this book, please email us at [email protected].
Errata: Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you have found a mistake in this book, we would be grateful if you would report this to us. Please visit www.packtpub.com/submit-errata, selecting your book, clicking on the Errata Submission Form link, and entering the details.
Piracy: If you come across any illegal copies of our works in any form on the Internet, we would be grateful if you would provide us with the location address or website name. Please contact us at [email protected] with a link to the material.
If you are interested in becoming an author: If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, please visit authors.packtpub.com.
Reviews
Please leave a review. Once you have read and used this book, why not leave a review on the site that you purchased it from? Potential readers can then see and use your unbiased opinion to make purchase decisions, we at Packt can understand what you think about our products, and our authors can see your feedback on their book. Thank you!
For more information about Packt, please visit packtpub.com.
