Tuning Autovacuum for best Postgres performance

We walk you through the most important concepts of why and when Postgres has to vacuum, why that matters, and when it’s best to tune the default settings that control autovacuum scheduling, vacuum overhead, and more. We‘ll provide actionable tuning tips you can use in your specific setup.

Here is what you will learn in this book:

  • What is table and index bloat?
  • What are Transaction IDs and freezing?
  • Performance benefits of frequent vacuuming: All-visible pages
  • The anatomy of a vacuum
  • Tuning when autovacuum is scheduled, and how fast its running
  • Reducing the impact of autovacuum during business hours
  • How to avoid blocked vacuums due to long-running transactions

Advanced Database Programming with Rails and Postgres

We explain key skills you'll want to make yourself more familiar with when it comes to working with Postgres and Rails. We walk through real-life examples of real business needs, translating first into SQL, and then into Rails code. A backend developer‘s career will consist in most likely hundreds of similar requests!

Here is what you will learn in this book:

  • Using Active Record and subqueries in Rails
  • Using views and materialized views
  • Creating custom Postgres data types (domain types and composite types)

Best Practices for Optimizing Postgres Query Performance

Over the last 10 years, we’ve learned a lot on how to optimize Postgres performance. Here, we share how to get a 3x performance improvement on your Postgres database and 500x reduced data loaded from disk.

Here is what you will learn in this book:

  • Database Performance = Application Performance
  • Missing indexes are the #1 database performance problem
  • How to figure out what’s going on in your database(s)
  • Gathering EXPLAIN plans automatically using auto_explain
  • Determining missing indexes based on EXPLAIN plans

Effective Indexing in Postgres

In this ebook, we discuss how to create the best indexes for your queries and provide a deep dive into index types, operators, data types and more. Indexing can make a significant difference for the performance of your application’s query workload.

Here is what you will learn in this book:

  • How Postgres uses indexes
  • Data types, operators and index types
  • B-tree indexes, Hash indexes, BRIN indexes, and Generic Index Types (GIN, GIST, SP-GIST)
  • How to create the best indexes for your queries

Finding the root cause of slow Postgres queries using EXPLAIN

Learn how to use EXPLAIN to understand problematic query plans, and how to enable auto_explain to automatically collect EXPLAIN plans for slow queries.

Here is what you will learn in this book:

  • How you can use EXPLAIN to find common patterns of problematic plans
  • How you then can improve these query plans that match these patterns
  • How you can use auto_explain to automatically start collecting EXPLAIN plans so you don't have to

Efficient Search in Rails with Postgres

Speed up a search query from seconds to milliseconds and learn about exact matches, similarity matches with trigrams, partial matches with ILIKE, and natural language full-text search.

Here is what you will learn in this book:

  • How to optimize an Exact Match Query
  • Partial / Case Insensitive Matches and using indexes with LIKE and ILIKE
  • Similarity Matches and Optimizing Trigram Search
  • Natural Language Matches with Full Text Search
  • Optimizing Full Text Search

The Most Important Events to Monitor in Your Postgres Logs

Learn about the Top 6 Postgres log events for improving query performance, reducing I/O spikes, deep diving into performance issues, handling secret data and PII contained in Postgres logs, and preventing downtime.

The events we will look in detail are:

  • Transaction ID (TXID) Wraparound
  • Data Corruption
  • Checkpoints
  • Temporary Files
  • Lock Notices & Deadlocks
  • EXPLAIN plans through auto_explain


Hundreds Of Companies Monitor Their Production PostgreSQL Databases With pganalyze

Atlassian
DoorDash
Moody's
fuboTv
Salsify
CounterPath
Ipsy

Robin Fernandes
pganalyze is essential to making our Postgres databases run faster, and makes sure end-users have the best experience possible.
- Robin Fernandes, Software Development Manager, Atlassian