PacktLib: PostgreSQL 9.0 High Performance

PostgreSQL 9.0 High Performance

Credits

About the Author

About the Reviewers

Preface

PostgreSQL Versions

Performance of historical PostgreSQL releases

PostgreSQL or another database?

PostgreSQL tools

PostgreSQL application scaling lifecycle

Performance tuning as a practice

Summary

Database Hardware

Balancing hardware spending

Reliable controller and disk setup

Summary

Database Hardware Benchmarking

CPU and memory benchmarking

Physical disk performance

Disk benchmarking tools

Sample disk results

Summary

Disk Setup

Maximum filesystem sizes

Filesystem crash recovery

Linux filesystems

Solaris and FreeBSD filesystems

Windows filesystems

Disk layout for PostgreSQL

Summary

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

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

Routine Maintenance

Transaction visibility with multiversion concurrency control

Vacuum

Autoanalyze

Index bloat

Detailed data and index page monitoring

Monitoring query logs

Summary

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

Database Indexing

Indexing example walkthrough

Index creation and maintenance

Index types

Advanced index use

Summary

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

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

Monitoring and Trending

UNIX monitoring tools

Windows monitoring tools

Trending software

Summary

Pooling and Caching

Connection pooling

Database caching

Summary

Scaling with Replication

Hot Standby

Replication queue managers

Special application requirements

Other interesting replication projects

Summary

Partitioning Data

Table range partitioning

Horizontal partitioning with PL/Proxy

Summary

Avoiding Common Problems

Bulk loading

Common performance issues

Profiling the database

Performance related features by version

Summary