PostgreSQL High Performance Cookbook

PostgreSQL High Performance Cookbook

by Chitij Chauhan, Dinesh Kumar
Released March 2017
Publisher(s): Packt Publishing
ISBN: 9781785284335

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Get to know effective ways to improve PostgreSQL’s performance and master query optimization, and database monitoring.

About This Book

  • Perform essential database tasks such as benchmarking the database and optimizing the server’s memory usage

  • Learn ways to improve query performance and optimize the PostgreSQL server

  • Explore a wide range of high availability and replication mechanisms to build robust, highly available, scalable, and fault-tolerant PostgreSQL databases

    • Who This Book Is For

    If you are a developer or administrator with limited PostgreSQL knowledge and want to develop your skills with this great open source database, then this book is ideal for you. Learning how to enhance the database performance is always an exciting topic to everyone, and this book will show you enough ways to enhance the database performance.

    What You Will Learn

  • Build replication strategies for homogeneous and heterogeneous databases

  • Test and build a powerful machine with multiple bench marking techniques

  • Get to know a few SQL injection techniques

  • Find out how to manage the replication using multiple tools

  • Benchmark the database server using multiple strategies

  • Work with the query processing algorithms and their internal behaviors

  • Build a proper plan to upgrade or migrate to PostgreSQL from other databases

  • See the essential database load balancing techniques and the various partitioning approaches PostgreSQL provides

  • Learn memory optimization techniques and database server configurations

    • In Detail

    PostgreSQL is one of the most powerful and easy to use database management systems. It has strong support from the community and is being actively developed with a new release every year. PostgreSQL supports the most advanced features included in SQL standards. It also provides NoSQL capabilities and very rich data types and extensions. All of this makes PostgreSQL a very attractive solution in software systems.

    If you run a database, you want it to perform well and you want to be able to secure it. As the world’s most advanced open source database, PostgreSQL has unique built-in ways to achieve these goals. This book will show you a multitude of ways to enhance your database’s performance and give you insights into measuring and optimizing a PostgreSQL database to achieve better performance.

    This book is your one-stop guide to elevate your PostgreSQL knowledge to the next level. First, you’ll get familiarized with essential developer/administrator concepts such as load balancing, connection pooling, and distributing connections to multiple nodes. Next, you will explore memory optimization techniques before exploring the security controls offered by PostgreSQL. Then, you will move on to the essential database/server monitoring and replication strategies with PostgreSQL. Finally, you will learn about query processing algorithms.

    Style and approach

    This comprehensive guide is packed with practical administration tasks. Each topic is explained using examples and a step-by-step approach.

    Table of contents

    1. PostgreSQL High Performance Cookbook
      1. PostgreSQL High Performance Cookbook
      2. Credits
      3. About the Authors
      4. About the Reviewers
      5. www.PacktPub.com
        1. Why subscribe?
      6. Customer Feedback
      7. Preface
        1. What this book covers
        2. What you need for this book
        3. Who this book is for
        4. Sections
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        5. Conventions
        6. Reader feedback
        7. Customer support
          1. Downloading the example code
          2. Errata
          3. Piracy
          4. Questions
      8. 1. Database Benchmarking
        1. Introduction
        2. CPU benchmarking
          1. Getting ready
          2. How to do it...
            1. Phoronix
            2. sysbench
          3. How it works...
            1. Phoronix
            2. sysbench
        3. Memory benchmarking
          1. Getting ready
          2. How to do it...
            1. Phoronix
            2. tmpfs
              1. Write test
              2. Read test
          3. How it works...
        4. Disk benchmarking
          1. Getting ready
          2. How to do it...
            1. Phoronix
            2. bonnie++
          3. How it works...
            1. bonnie++
        5. Performing a seek rate test
          1. Getting ready
          2. How to do it...
          3. How it works...
        6. Working with the fsync commit rate
          1. Getting ready
          2. How to do it...
          3. How it works...
        7. Checking IOPS
          1. Getting ready
          2. How to do it...
            1. Sequential mixed read and write
            2. Random mixed read and write
          3. How it works...
        8. Storage sizing
          1. Getting ready
          2. How to do it...
          3. How it works...
        9. Discussing RAID levels
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. RAID 0
            2. RAID 1
            3. RAID 5
            4. RAID 6
            5. RAID 10
        10. Configuring pgbench
          1. Getting ready
          2. How to do it...
          3. How it works...
        11. Running read/write pgbench test cases
          1. Getting ready
          2. How to do it...
            1. Read-only
            2. Write-only
          3. How it works...
      9. 2. Server Configuration and Control
        1. Introduction
        2. Starting the server manually
          1. Getting ready
          2. How to do it...
          3. How it works...
        3. Stopping the server quickly
          1. Getting ready
          2. How to do it...
          3. How it works...
        4. Stopping the server in an emergency
          1. How to do it...
          2. How it works...
        5. Reloading server configuration
          1. Getting ready
          2. How to do it...
          3. How it works...
        6. Restarting the database server quickly
          1. How to do it...
          2. How it works...
        7. Tuning connection-related parameters
          1. How to do it...
          2. How it works...
        8. Tuning query-related parameters
          1. How to do it...
          2. How it works...
        9. Tuning logging-related parameters
          1. How to do it...
          2. How it works...
      10. 3. Device Optimization
        1. Introduction
        2. Understanding memory units in PostgreSQL
          1. Getting ready
          2. How to do it...
            1. shared_buffers
            2. temp_buffers
            3. work_mem
            4. maintenance_work_mem
            5. wal_buffers
            6. max_stack_depth
            7. effective_cache_size
          3. How it works...
        3. Handling Linux/Unix memory parameters
          1. Getting ready
          2. How to do it...
            1. kernel.shmmax
            2. kernel.shmall
            3. kernel.shmmni
            4. vm.swappiness
            5. vm.overcommit_memory
            6. vm.overcommit_ratio
            7. vm.dirty_background_ratio
            8. vm.dirty_ratio
          3. How it works...
        4. CPU scheduling parameters
          1. Getting ready
          2. How to do it...
            1. kernel.sched_autogroup_enabled
            2. kernel.sched_min_granularity_ns
            3. kernel.sched_latency_ns
            4. kernel.sched_wakeup_granularity_ns
            5. kernel.sched_migration_cost_ns
          3. How it works...
        5. Disk tuning parameters
          1. Getting ready
          2. How to do it...
            1. CFQ
            2. noop
            3. Deadline
          3. How it works...
        6. Identifying checkpoint overhead
          1. Getting ready
          2. How to do it...
          3. How it works...
        7. Analyzing buffer cache contents
          1. Getting ready
          2. How to do it...
          3. How it works...
      11. 4. Monitoring Server Performance
        1. Introduction
        2. Monitoring CPU usage
          1. Getting ready
          2. How to do it...
          3. How it works...
        3. Monitoring paging and swapping
          1. Getting ready
          2. How to do it...
          3. How it works...
        4. Tracking CPU consuming processes
          1. Getting ready
          2. How to do it...
          3. How it works...
        5. Monitoring CPU load
          1. How to do it...
          2. How it works...
        6. Identifying CPU bottlenecks
          1. How to do it...
          2. How it works...
        7. Identifying disk I/O bottlenecks
          1. How to do it...
          2. How it works...
        8. Monitoring system load
          1. How to do it...
          2. How it works...
        9. Tracking historical CPU usage
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        10. Tracking historical memory usage
          1. Getting ready
          2. How to do it...
          3. How it works...
        11. Monitoring disk space
          1. How to do it...
          2. How it works...
        12. Monitoring network status
          1. Getting ready
          2. How to do it...
          3. How it works...
      12. 5. Connection Pooling and Database Partitioning
        1. Introduction
        2. Installing pgpool-II
          1. Getting Ready
          2. How to do it...
          3. How it works...
        3. Configuring pgpool and testing the setup
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        4. Installing PgBouncer
          1. Getting ready
          2. How to do it...
          3. How it works...
        5. Connection pooling using PgBouncer
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        6. Managing PgBouncer
          1. Getting ready
          2. How to do it...
          3. How it works...
        7. Implementing partitioning
          1. Getting ready
          2. How to do it...
          3. How it works...
        8. Managing partitions
          1. Getting ready
          2. How to do it...
          3. How it works...
        9. Installing PL/Proxy
          1. How to do it...
          2. How it works...
        10. Partitioning with PL/Proxy
          1. Getting ready
          2. How to do it...
          3. How it works...
      13. 6. High Availability and Replication
        1. Introduction
        2. Setting up hot streaming replication
          1. Getting ready
          2. How to do it...
          3. How it works...
        3. Replication using Slony
          1. Getting ready
          2. How to do it...
          3. How it works...
        4. Replication using Londiste
          1. Getting ready
          2. How to do it...
          3. How it works...
        5. Replication using Bucardo
          1. Getting ready
          2. How to do it...
          3. How it works...
        6. Replication using DRBD
          1. Getting ready
          2. How to do it...
          3. How it works...
        7. Setting up a Postgres-XL cluster
          1. Getting ready
          2. How to do it...
      14. 7. Working with Third-Party Replication Management Utilities
        1. Introduction
        2. Setting up Barman
          1. Getting ready
          2. How to do it...
          3. How it works...
        3. Backup and recovery using Barman
          1. Getting ready
          2. How to do it...
          3. How it works...
        4. Setting up OmniPITR
          1. Getting ready
          2. How to do it...
          3. How it works...
        5. WAL management with OmniPITR
          1. Getting ready
          2. How to do it...
          3. How it works...
        6. Setting up repmgr
          1. How to do it...
          2. How it works...
        7. Using repmgr to create replica
          1. How to do it...
          2. How it works...
        8. Setting up walctl
          1. How to do it...
          2. How it works...
        9. Using walctl to create replica
          1. How to do it...
          2. How it works...
      15. 8. Database Monitoring and Performance
        1. Introduction
        2. Checking active sessions
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        3. Finding out what the users are currently running
          1. Getting ready
          2. How to do it...
          3. How it works...
        4. Finding blocked sessions
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Transactional locks
            2. Table level locks
            3. Prepared transaction locks
            4. Usage of SKIP LOCKED
        5. Dealing with deadlocks
          1. Getting ready
          2. How to do it...
            1. Using FOR UPDATE
            2. Advisory locks
        6. Table access statistics
          1. Getting ready
          2. How to do it...
            1. pg_stat_user_tables
            2. pg_statio_user_tables
          3. How it works...
        7. Logging slow statements
          1. Getting ready
          2. How to do it...
          3. How it works...
        8. Determining disk usage
          1. Getting ready
          2. How to do it...
            1. Column size
            2. Relation size
            3. Database size
            4. Tablespace size
          3. How it works...
        9. Preventing page corruption
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        10. Routine reindexing
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        11. Generating planner statistics
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. null_frac
            2. avg_width
            3. n_distinct
            4. most_common_vals
            5. most_common_freqs
            6. histogram_bounds
            7. correlation
            8. most_common_elems
            9. most_common_elem_freqs
        12. Tuning with background writer statistics
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. checkpoints_timed
            2. checkpoints_req
            3. checkpoint_write_time
            4. checkpoint_sync_time
            5. buffers_checkpoint
            6. buffers_clean
            7. max_written_clean
            8. buffers_backend
            9. buffers_backend_fsync
            10. buffers_alloc
            11. stats_reset
          4. There's more...
      16. 9. Vacuum Internals
        1. Introduction
        2. Dealing with bloating tables and indexes
          1. Getting ready
          2. How to do it...
          3. How it works...
        3. Vacuum and autovacuum
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. xmin
            2. xmax
            3. Autovacuum
        4. Freezing and transaction ID wraparound
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. Fixing transaction ID wraparound
            2. Preventing transaction ID wraparound
        5. Monitoring vacuum progress
          1. Getting ready
          2. How to do it...
          3. How it works...
        6. Control bloat using transaction age
          1. Getting ready
          2. How to do it...
          3. How it works...
      17. 10. Data Migration from Other Databases to PostgreSQL and Upgrading the PostgreSQL Cluster
        1. Introduction
        2. Using pg_dump to upgrade data
          1. Getting ready
          2. How to do it...
          3. How it works...
        3. Using the pg_upgrade utility for version upgrade
          1. Getting ready
          2. How to do it...
          3. How it works...
        4. Replicating data from other databases to PostgreSQL using Goldengate
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
      18. 11. Query Optimization
        1. Introduction
        2. Using sample data sets
          1. Getting ready
          2. How to do it…
          3. How it works…
        3. Timing overhead
          1. Getting ready
          2. How to do it…
          3. How it works…
        4. Studying hot and cold cache behavior
          1. Getting ready
          2. How to do it…
            1. Cold cache
            2. Hot cache
          3. How it works…
        5. Clearing the cache
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
        6. Query plan node structure
          1. Getting ready
          2. How to do it…
          3. How it works…
        7. Generating an explain plan
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
        8. Computing basic cost
          1. Getting ready
          2. How to do it…
          3. How it works…
            1. Sequential scan
            2. Index scan
            3. Phase I
            4. Phase II
        9. Running sequential scans
          1. Getting ready
          2. How to do it…
          3. How it works…
        10. Running bitmap heap and index scan
          1. Getting ready
          2. How to do it…
          3. How it works…
        11. Aggregate and hash aggregate
          1. Getting ready
          2. How to do it…
            1. Aggregate
            2. Hash aggregate
          3. How it works…
            1. Aggregate
            2. State transition functions
            3. Hash aggregates
        12. Running CTE scan
          1. Getting ready
          2. How to do it…
          3. How it works…
            1. CTE scan
            2. Recursive union
        13. Nesting loops
          1. Getting ready
          2. How to do it…
          3. How it works…
            1. Phase I
            2. Phase II
        14. Working with hash and merge join
          1. Getting ready
          2. How to do it…
            1. Hash join
            2. Merge join
          3. How it works…
            1. Hash join
              1. Phase I
              2. Phase II
            2. Merge join
              1. Phase I
              2. Phase II
        15. Grouping
          1. Getting ready
          2. How to do it…
          3. How it works…
        16. Working with set operations
          1. Getting ready
          2. How to do it…
          3. How it works…
            1. INTERSECT
            2. EXCEPT
            3. UNION
        17. Working on semi and anti joins
          1. Getting ready
          2. How to do it…
          3. How it works…
      19. 12. Database Indexing
        1. Introduction
        2. Measuring query and index block statistics
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. idx_scan
            2. idx_tup_read
            3. idx_tup_fetch
            4. idx_blks_read
            5. idx_blks_hit
        3. Index lookup
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. Index scan
            2. Bitmap heap scan
            3. Index only scan
        4. Comparing indexed scans and sequential scans
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. Sequential scan
            2. Index scan
            3. Index only scan
            4. Bitmap heap scan
          4. There's more...
        5. Clustering against an index
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        6. Concurrent indexes
          1. Getting ready
          2. How to do it...
          3. How it works...
        7. Combined indexes
          1. Getting ready
          2. How to do it...
          3. How it works...
        8. Partial indexes
          1. Getting ready
          2. How to do it...
          3. How it works...
        9. Finding unused indexes
          1. Getting ready
          2. How to do it...
          3. How it works...
        10. Forcing a query to use an index
          1. Getting ready
          2. How to do it...
          3. How it works...
        11. Detecting a missing index
          1. Getting ready
          2. How to do it...
          3. How it works...

    Product information

    • Title: PostgreSQL High Performance Cookbook
    • Author(s): Chitij Chauhan, Dinesh Kumar
    • Release date: March 2017
    • Publisher(s): Packt Publishing
    • ISBN: 9781785284335