Book Image

PostgreSQL 10 High Performance - Third Edition

By : Enrico Pirozzi
Book Image

PostgreSQL 10 High Performance - Third Edition

By: Enrico Pirozzi

Overview of this book

PostgreSQL database servers have a common set of problems that they encounter as their usage gets heavier and requirements get more demanding. Peek into the future of your PostgreSQL 10 database's problems today. Know the warning signs to look for and how to avoid the most common issues before they even happen. Surprisingly, most PostgreSQL database applications evolve in the same way—choose the right hardware, tune the operating system and server memory use, optimize queries against the database and CPUs with the right indexes, and monitor every layer, from hardware to queries, using tools from inside and outside PostgreSQL. Also, using monitoring insight, PostgreSQL database applications continuously rework the design and configuration. On reaching the limits of a single server, they break things up; connection pooling, caching, partitioning, replication, and parallel queries can all help handle increasing database workloads. By the end of this book, you will have all the knowledge you need to design, run, and manage your PostgreSQL solution while ensuring high performance and high availability

Related Content you might be interested in

Current Title:

Small book image
PostgreSQL 10 High Performance - Third Edition
Enrico Pirozzi
Apr, 2018 | 508 pages
Small book image
PostgreSQL 16 Administration Cookbook
Boris Mejias | Simon Riggs | Gianni Ciolli | Vibhor Kumar | Dimitrios Angelakos
Dec, 2023 | 636 pages
Small book image
PostgreSQL 11 Administration Cookbook
Simon Riggs | Gianni Ciolli | Sudheer Kumar Meesala
May, 2019 | 600 pages
Small book image
PostgreSQL 14 Administration Cookbook
Simon Riggs | Gianni Ciolli
Mar, 2022 | 608 pages
Table of Contents (18 chapters)
Preface
Preface
Who this book is for
What this book covers
To get the most out of this book
Get in touch
Free Chapter
1
PostgreSQL Versions
PostgreSQL Versions
Performance of historical PostgreSQL releases
Choosing a version to deploy
Upgrading to a newer major version
Upgrades to PostgreSQL 8.3+ from earlier ones
PostgreSQL or another database?
PostgreSQL tools
PostgreSQL contrib
Finding contrib modules on your system
Installing a contrib module from source
Using a contrib module
pgFoundry
PGXN
Additional PostgreSQL-related software
PostgreSQL application scaling life cycle
Performance tuning as a practice
Summary
2
Database Hardware
Database Hardware
Balancing hardware spending
Reliable controller and disk setup
Summary
3
Database Hardware Benchmarking
Database Hardware Benchmarking
CPU and memory benchmarking
Physical disk performance
Disk benchmarking tools
Sample disk results
Summary
4
Disk Setup
Disk Setup
Maximum filesystem sizes
Filesystem crash recovery
Linux filesystems
Solaris and FreeBSD filesystems
Disk layout for PostgreSQL
Summary
5
Memory for Database Caching
Memory for Database Caching
Memory units in postgresql.conf
Increasing Unix shared memory parameters for larger buffer sizes
Crash recovery and the buffer cache
Database buffer cache versus operating system cache
Analyzing buffer cache contents
Summary
6
Server Configuration Tuning
Server Configuration Tuning
Interacting with the live configuration
Summary
7
Routine Maintenance
Routine Maintenance
Transaction visibility with multiversion concurrency control
Vacuum
Index bloat
Detailed data and index page monitoring
Monitoring query logs
Summary
8
Database Benchmarking
Database Benchmarking
pgbench default tests
Graphing results with pgbench-tools
Sample pgbench test results
Sources of bad results and variation
pgbench custom tests
Transaction Processing Performance Council benchmarks
Summary
9
Database Indexing
Database Indexing
Indexing example walkthrough
Index creation and maintenance
Index types
Advanced index use
Summary
10
Query Optimization
Query Optimization
Sample data sets
EXPLAIN basics
Query plan node structure
Explain analysis tools
Assembling row sets
Processing nodes
Joins
Statistics
Other query-planning parameters
Executing other statement types
Improving queries
SQL limitations
Summary
11
Database Activity and Statistics
Database Activity and Statistics
Statistics views
Cumulative and live views
Table statistics
Index statistics
Database-wide totals
Connections and activity
Locks
Disk usage
Buffer, background writer, and checkpoint activity
Summary
12
Monitoring and Trending
Monitoring and Trending
UNIX monitoring tools
Windows monitoring tools
Trending software
Summary
13
Pooling and Caching
Pooling and Caching
Connection pooling
Summary
14
Scaling with Replication
Scaling with Replication
Hot Standby
Replication queue managers
Synchronous replication
Logical replication
Special application requirements
Other interesting replication projects
Replication solution comparison
Summary
15
Partitioning Data
Partitioning Data
Table range partitioning
PostgreSQL 10 – declarative partitioning – the built-in partitioning
Horizontal partitioning with PL/Proxy
Summary
16
Avoiding Common Problems
Avoiding Common Problems
Bulk loading
Backup
Common performance issues
Foreign data wrapper
The amcheck module
pgAdmin
Performance-related features by version
Summary
17
Other Books You May Enjoy
Other Books You May Enjoy
Leave a review - let other readers know what you think

PGXN

Another site where it is possible to find many PostgreSQL-related projects is PGXN. PGXN is more recent than pgFoundry and it is possible to find recent extensions there.

The PostgreSQL Extension Network (PGXN) is a central distribution system for open source PostgreSQL extension libraries. It consists of four basic parts:

  • PGXN Manager: An upload and distribution infrastructure for extension developers
  • PGXN API: A centralized index and API of distribution metadata
  • PGXN Search: This site is for searching extensions and perusing their documentation
  • PGXN Client: A command-line client for downloading, testing, and installing extensions

The difference between pgFoundry and PGXN is that pgFoundry is about project management and PGXN is about distribution and exposure.

Previous Section
End of Section 13
Next Section