PostgreSQL 9.6 High Performance

Enhance the performance of your PostgreSQL system with this handy guide while avoiding common pitfalls that can slow it down.
Preview in Mapt

PostgreSQL 9.6 High Performance

Ibrar Ahmed, Gregory Smith

5 customer reviews
Enhance the performance of your PostgreSQL system with this handy guide while avoiding common pitfalls that can slow it down.
Mapt Subscription
FREE
$29.99/m after trial
eBook
$28.00
RRP $39.99
Save 29%
Print + eBook
$49.99
RRP $49.99
What do I get with a Mapt Pro subscription?
  • Unlimited access to all Packt’s 5,000+ eBooks and Videos
  • Early Access content, Progress Tracking, and Assessments
  • 1 Free eBook or Video to download and keep every month after trial
What do I get with an eBook?
  • Download this book in EPUB, PDF, MOBI formats
  • DRM FREE - read and interact with your content when you want, where you want, and how you want
  • Access this title in the Mapt reader
What do I get with Print & eBook?
  • Get a paperback copy of the book delivered to you
  • Download this book in EPUB, PDF, MOBI formats
  • DRM FREE - read and interact with your content when you want, where you want, and how you want
  • Access this title in the Mapt reader
What do I get with a Video?
  • Download this Video course in MP4 format
  • DRM FREE - read and interact with your content when you want, where you want, and how you want
  • Access this title in the Mapt reader
$0.00
$28.00
$49.99
$29.99 p/m after trial
RRP $39.99
RRP $49.99
Subscription
eBook
Print + eBook
Start 14 Day Trial

Frequently bought together


PostgreSQL 9.6 High Performance Book Cover
PostgreSQL 9.6 High Performance
$ 39.99
$ 28.00
PostgreSQL Administration Cookbook, 9.5/9.6 Edition Book Cover
PostgreSQL Administration Cookbook, 9.5/9.6 Edition
$ 47.99
$ 33.60
Buy 2 for $35.00
Save $52.98
Add to Cart

Book Details

ISBN 139781784392970
Paperback508 pages

Book Description

Database administrators and developers spend years learning techniques to configure their PostgreSQL database servers for optimal performance, mostly when they encounter performance issues. Scalability and high availability of the database solution is equally important these days. This book will show you how to configure new database installations and optimize existing database server installations using PostgreSQL 9.6.

You will start with the basic concepts of database performance, because all successful database applications are destined to eventually run into issues when scaling up their performance. You will not only learn to optimize your database and queries for optimal performance, but also detect the real performance bottlenecks using PostgreSQL tools and some external tools. Next, you will learn how to benchmark your hardware and tune your operating system. Optimize your queries against the database with the help of right indexes, and monitor every layer, ranging from hardware to queries. Moving on, you will see how connection pooling, caching, partitioning, and replication will help you handle increasing database workloads.

Achieving high database performance is not easy, but you can learn it by using the right guide—PostgreSQL 9.6 High Performance.

Table of Contents

Chapter 1: PostgreSQL Versions
Performance of historical PostgreSQL releases
PostgreSQL or another database?
PostgreSQL Tools
PostgreSQL application scaling life cycle
Performance tuning as a practice
Summary
Chapter 2: Database Hardware
Balancing hardware spending
Reliable controller and disk setup
Write-back caches
Performance impact of write-through caching
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
Disk layout for PostgreSQL
Summary
Chapter 5: Memory for Database Caching
Memory units in the postgresql.conf file
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
Chapter 6: Server Configuration Tuning
Interacting with the live configuration
Summary
Chapter 7: Routine Maintenance
Transaction visibility with multiversion concurrency control
Vacuum
Autoanalyze
Index bloat
Dump and restore
Vacuuming the database/table
CLUSTER
Reindexing
Detailed data and index page monitoring
Monitoring query logs
Summary
Chapter 8: Database Benchmarking
pgbench default tests
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
count(*)
Visibility map
Advanced index use
Summary
Chapter 10: Query Optimization
Sample datasets
EXPLAIN basics
Query plan node structure
Assembling row sets
Processing nodes
CTE scan
Copy command
Joins
Collecting 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
Summary
Chapter 12: Monitoring and Trending
UNIX monitoring tools
Windows monitoring tools
Trending software
Summary
Chapter 13: Pooling and Caching
Connection pooling
Summary
Chapter 14: Scaling with Replication
Hot standby
Replication queue managers
Other interesting replication projects
Replications solution comparison
Summary
Chapter 15: Partitioning Data
Table inheritance
dellstore2 database
Partitioning in PostgreSQL
Range partitioning
Determine a key field to partition over
Sizing the partitions
Redirecting INSERT statements to the partitions
Empty partition query plans
Date change update trigger
Live migration of a partitioned table
Partitioned queries
Creating new partitions
Partitioning advantages
Common partitioning mistakes
Horizontal partitioning with PL/Proxy
Summary
Chapter 16: Database Profiling
Profiling using gprof
Debugging using Valgrind
Visual Studio
Profiling using DTrace
Summary
Chapter 17: Avoiding Common Problems
Bulk loading
Common performance issues
Summary
Chapter 18: Performance Features by Release
Aggressive PostgreSQL version upgrades
Performance features in version 8.1
Performance features in version 8.2
Performance features in version 8.3
Performance features in version 8.4
Performance features in version 9.0
Performance features in version 9.1
Performance features in version 9.2
Performance features in version 9.3
Performance features in version 9.4
Performance features in version 9.5
Performance features in version 9.6
Summary

What You Will Learn

  • Learn the best practices to configure your PostgreSQL 9.6 database for optimal performance
  • Write optimal queries and techniques to detect performance issue in queries
  • Fine tune the performance of your queries using benchmarking and indexing techniques
  • Ensure high performance and a highly available database using the scaling and replication techniques
  • Discover how to make informed speed and reliability trade-offs
  • Handle increasing database workloads without any hassle
  • Use monitoring insights to continuously rework the design and configuration for best performance

Authors

Table of Contents

Chapter 1: PostgreSQL Versions
Performance of historical PostgreSQL releases
PostgreSQL or another database?
PostgreSQL Tools
PostgreSQL application scaling life cycle
Performance tuning as a practice
Summary
Chapter 2: Database Hardware
Balancing hardware spending
Reliable controller and disk setup
Write-back caches
Performance impact of write-through caching
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
Disk layout for PostgreSQL
Summary
Chapter 5: Memory for Database Caching
Memory units in the postgresql.conf file
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
Chapter 6: Server Configuration Tuning
Interacting with the live configuration
Summary
Chapter 7: Routine Maintenance
Transaction visibility with multiversion concurrency control
Vacuum
Autoanalyze
Index bloat
Dump and restore
Vacuuming the database/table
CLUSTER
Reindexing
Detailed data and index page monitoring
Monitoring query logs
Summary
Chapter 8: Database Benchmarking
pgbench default tests
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
count(*)
Visibility map
Advanced index use
Summary
Chapter 10: Query Optimization
Sample datasets
EXPLAIN basics
Query plan node structure
Assembling row sets
Processing nodes
CTE scan
Copy command
Joins
Collecting 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
Summary
Chapter 12: Monitoring and Trending
UNIX monitoring tools
Windows monitoring tools
Trending software
Summary
Chapter 13: Pooling and Caching
Connection pooling
Summary
Chapter 14: Scaling with Replication
Hot standby
Replication queue managers
Other interesting replication projects
Replications solution comparison
Summary
Chapter 15: Partitioning Data
Table inheritance
dellstore2 database
Partitioning in PostgreSQL
Range partitioning
Determine a key field to partition over
Sizing the partitions
Redirecting INSERT statements to the partitions
Empty partition query plans
Date change update trigger
Live migration of a partitioned table
Partitioned queries
Creating new partitions
Partitioning advantages
Common partitioning mistakes
Horizontal partitioning with PL/Proxy
Summary
Chapter 16: Database Profiling
Profiling using gprof
Debugging using Valgrind
Visual Studio
Profiling using DTrace
Summary
Chapter 17: Avoiding Common Problems
Bulk loading
Common performance issues
Summary
Chapter 18: Performance Features by Release
Aggressive PostgreSQL version upgrades
Performance features in version 8.1
Performance features in version 8.2
Performance features in version 8.3
Performance features in version 8.4
Performance features in version 9.0
Performance features in version 9.1
Performance features in version 9.2
Performance features in version 9.3
Performance features in version 9.4
Performance features in version 9.5
Performance features in version 9.6
Summary

Book Details

ISBN 139781784392970
Paperback508 pages
Read More
From 5 reviews

Read More Reviews

Recommended for You

PostgreSQL Administration Cookbook, 9.5/9.6 Edition Book Cover
PostgreSQL Administration Cookbook, 9.5/9.6 Edition
$ 47.99
$ 33.60
PostgreSQL High Availability Cookbook - Second Edition Book Cover
PostgreSQL High Availability Cookbook - Second Edition
$ 43.99
$ 30.80
PostgreSQL High Performance Cookbook Book Cover
PostgreSQL High Performance Cookbook
$ 43.99
$ 30.80
Python GUI Programming Cookbook - Second Edition Book Cover
Python GUI Programming Cookbook - Second Edition
$ 39.99
$ 28.00
PostgreSQL 9 Administration Cookbook - Second Edition Book Cover
PostgreSQL 9 Administration Cookbook - Second Edition
$ 47.99
$ 33.60
AWS Administration Cookbook Book Cover
AWS Administration Cookbook
$ 35.99
$ 25.20