PostgreSQL 9.0 High Performance

If you’re an intermediate to advanced database administrator, this book is the shortcut to optimizing and troubleshooting your PostgreSQL database. With a balanced mix of theory and practice, it will quickly hone your expertise.

PostgreSQL 9.0 High Performance

Gregory SmithOctober 2010

1 customer reviews

If you’re an intermediate to advanced database administrator, this book is the shortcut to optimizing and troubleshooting your PostgreSQL database. With a balanced mix of theory and practice, it will quickly hone your expertise.

Book Details

ISBN 139781849510301

Paperback468 pages

Book Description

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

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

What You Will Learn

Learn best practices culled from years of work scaling PostgreSQL installations to handle demanding applications

Uncover what makes hardware good or bad for high-performance database applications

Investigate making informed speed and reliability trade-offs

Tweak your operating system for best database performance

Benchmark your whole system from hardware to application

Learn how the server parameters that impact performance work using real examples

Understand how the query optimizer makes its decisions, and what to do when it makes the wrong ones

Monitor what happens on your server, both in and outside of the database

Find the best add-on tools that extend the core PostgreSQL database

Discover how to architect replicated systems using the latest features added to PostgreSQL 9.0

Authors

Gregory Smith

Gregory Smith is a Chief PostgreSQL Evangelist in Charleston, South Carolina, for Crunchy Data Solutions, Inc. He's been providing database advice to clients in industries such as manufacturing, finance, and web development for 20 years.

Writing about PostgreSQL represents his second foray into teaching database performance tuning. He wrote a small free e-book titled, Progress Performance FAQ in 1995, covering the basics of how to make the Progress 4GL and its associated database run faster. In 2001, he converted exclusively to using PostgreSQL 7.0 for projects, and has been watching the complexity of problems the database is capable of solving increase with every release ever since.

Greg has contributed feature additions to every PostgreSQL version since 8.3. He's also the creator of a growing set of add-on tools for the database, currently including pgtune, pgbench-tools, peg, and 2warm.

Alerts & Offers

Series & Level

We understand your time is important. Uniquely amongst the major publishers, we seek to develop and publish the broadest range of learning and information products on each technology. Every Packt product delivers a specific learning pathway, broadly defined by the Series type. This structured approach enables you to select the pathway which best suits your knowledge level, learning style and task objectives.

Learning

As a new user, these step-by-step tutorial guides will give you all the practical skills necessary to become competent and efficient.

Beginner's Guide

Friendly, informal tutorials that provide a practical introduction using examples, activities, and challenges.

Essentials

Fast paced, concentrated introductions showing the quickest way to put the tool to work in the real world.

Cookbook

A collection of practical self-contained recipes that all users of the technology will find useful for building more powerful and reliable systems.

Blueprints

Guides you through the most common types of project you'll encounter, giving you end-to-end guidance on how to build your specific solution quickly and reliably.

Mastering

Take your skills to the next level with advanced tutorials that will give you confidence to master the tool's most powerful features.

Starting

Accessible to readers adopting the topic, these titles get you into the tool or technology so that you can become an effective user.

Progressing

Building on core skills you already have, these titles share solutions and expertise so you become a highly productive power user.