-
Book Overview & Buying
-
Table Of Contents
PostgreSQL 9.0 High Performance
PostgreSQL 9.0 High Performance
Overview of this book
PostgreSQL database servers have a common set of problems they encounter as their usage gets heavier and requirements more demanding. You could spend years discovering solutions to them all, step by step as you encounter them. Or you can just look in here.All successful database applications are destined to eventually run into issues scaling up their performance. Peek into the future of your PostgreSQL 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, with the right indexes. Monitor every layer, from hardware to queries, using some tools that are inside PostgreSQL and others that are external.
Using monitoring insight, continuously rework the design and configuration. On reaching the limits of a single server, break things up; connection pooling, caching, partitioning, and replication can all help handle increasing database workloads.
The path to a high performance database system isn't always easy. But it doesn't have to be mysterious with the right guide.
Table of Contents (120 chapters)
Preface
What you need for this book
Who this book is for
Conventions
Reader feedback
Customer support
Chapter 1. PostgreSQL Versions
Performance of historical PostgreSQL releases
PostgreSQL or another database?
PostgreSQL tools
PostgreSQL application scaling lifecycle
Performance tuning as a practice
Summary
Chapter 2. Database Hardware
Balancing hardware spending
Reliable controller and disk setup
Summary
Chapter 3. Database Hardware Benchmarking
CPU and memory benchmarking
Physical disk performance
Disk benchmarking tools
Sample disk results
Summary
Chapter 4. Disk Setup
Maximum filesystem sizes
Filesystem crash recovery
Linux filesystems
Solaris and FreeBSD filesystems
Windows filesystems
Disk layout for PostgreSQL
Summary
Chapter 5. Memory for Database Caching
Inspecting the database cache
Crash recovery and the buffer cache
Database buffer cache versus operating system cache
Analyzing buffer cache contents
Summary
Chapter 6. Server Configuration Tuning
Interacting with the live configuration
Server-wide settings
Per-client settings
New server tuning
Dedicated server guidelines
Shared server guidelines
pgtune
Summary
Chapter 7. Routine Maintenance
Transaction visibility with multiversion concurrency control
Vacuum
Autoanalyze
Index bloat
Detailed data and index page monitoring
Monitoring query logs
Summary
Chapter 8. Database Benchmarking
pgbench default tests
Running pgbench manually
Graphing results with pgbench-tools
Sample pgbench test results
Sources for bad results and variation
pgbench custom tests
Transaction Processing Performance Council benchmarks
Summary
Chapter 9. Database Indexing
Indexing example walkthrough
Index creation and maintenance
Index types
Advanced index use
Summary
Chapter 10. 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
Chapter 11. 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
Chapter 12. Monitoring and Trending
UNIX monitoring tools
Windows monitoring tools
Trending software
Summary
Chapter 13. Pooling and Caching
Connection pooling
Database caching
Summary
Chapter 14. Scaling with Replication
Hot Standby
Replication queue managers
Special application requirements
Other interesting replication projects
Summary
Chapter 15. Partitioning Data
Table range partitioning
Horizontal partitioning with PL/Proxy
Summary
Chapter 16. Avoiding Common Problems
Bulk loading
Common performance issues
Profiling the database
Performance related features by version
Summary
