Free Sample
+ Collection

PostgreSQL 9.0 High Performance

Starting
Gregory Smith

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.
$29.99
$45.00
RRP $29.99
RRP $49.99
eBook
Print + eBook

Want this title & more?

$21.99 p/month

Subscribe to PacktLib

Enjoy full and instant access to over 2000 books and videos – you’ll find everything you need to stay ahead of the curve and make sure you can always get the job done.

Book Details

ISBN 139781849510301
Paperback468 pages

About This Book

<ul> <li>Learn the right techniques to obtain optimal PostgreSQL database performance, from initial design to routine maintenance</li> <li>Discover the techniques used to scale successful database installations</li> <li>Avoid the common pitfalls that can slow your system down</li> <li>Filled with advice about what you should be doing; how to build experimental databases to explore performance topics, and then move what you've learned into a production database environment</li> <li>Covers versions 8.1 through 9.0</li> </ul> <p>&nbsp;</p>

Who This Book Is For

<p>This book is aimed at intermediate to advanced database administrators 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.</p>

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

<ul> <li>Learn best practices culled from years of work scaling PostgreSQL installations to handle demanding applications</li> <li>Uncover what makes hardware good or bad for high-performance database applications</li> <li>Investigate making informed speed and reliability trade-offs</li> <li>Tweak your operating system for best database performance</li> <li>Benchmark your whole system from hardware to application</li> <li>Learn how the server parameters that impact performance work using real examples</li> <li>Understand how the query optimizer makes its decisions, and what to do when it makes the wrong ones</li> <li>Monitor what happens on your server, both in and outside of the database</li> <li>Find the best add-on tools that extend the core PostgreSQL database</li> <li>Discover how to architect replicated systems using the latest features added to PostgreSQL 9.0</li> </ul> <p>&nbsp;</p>

In Detail

<p>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.<br /><br />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.<br /><br />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.</p> <p>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.</p> <p>The path to a high performance database system isn't always easy. But it doesn't have to be mysterious with the right guide.</p>

Authors

Read More