Postgres eBooks by pganalyze: DB Optimization, Indexing, and more.

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: