PostgreSQL Performance in Production: Advanced Indexing, EXPLAIN ANALYZE, and Avoiding Table Bloat

Introduction: The 30-Second Query That Brought Down Our Checkout

During a major sales event, our PostgreSQL database CPU spiked to 100%, and the checkout API started timing out. The culprit wasn't traffic volume; it was a single query fetching "recent orders" for a dashboard. It was performing a Sequential Scan on a 50-million-row table because the developer had wrapped the indexed created_at column in a DATE() function, rendering the B-Tree index useless. The query took 30 seconds, queued up hundreds of connections, and brought the entire application to a halt.

PostgreSQL is an incredibly powerful database, but it is not magic. It relies on statistics, well-designed indexes, and properly written SQL to make good decisions. This guide moves beyond basic "CREATE INDEX" tutorials. We will cover the production-tested patterns, execution plan analysis, and maintenance strategies we use to keep our PostgreSQL databases fast, stable, and scalable under heavy load.

The Real Cost of Indexes: Read vs. Write Trade-offs

The most common beginner mistake is adding an index to every column mentioned in a WHERE clause. While indexes speed up reads, they are not free. Every INSERT, UPDATE, or DELETE must also update every associated index.

Furthermore, indexes consume disk space and contribute to table bloat due to PostgreSQL's Multi-Version Concurrency Control (MVCC). When a row is updated, the old version is marked as dead, but the index still points to it until VACUUM cleans it up. Over-indexing a high-write table can lead to write starvation and massive storage waste.

Mastering EXPLAIN (ANALYZE, BUFFERS)

EXPLAIN shows the planner's estimated plan. EXPLAIN ANALYZE actually executes the query and shows the real time and row counts. But to truly understand performance, you must add BUFFERS.

              EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT customer_id, total_amount
FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 100;

            

How to read the output: Look for actual time=0.050..1.200 rows=100 loops=1. If rows is vastly different from the planner's rows= estimate, your table statistics are outdated (run ANALYZE). The Buffers: shared hit=50 read=20 tells you exactly how many data pages were fetched from RAM (hit) vs. disk (read). Your goal is to minimize "read".

The Leftmost Prefix Rule in Composite Indexes

A composite index on (customer_id, status) is essentially two indexes in one: it can be used to search by customer_id alone, or by customer_id AND status. However, it cannot be used to search by status alone.

              -- GOOD: Uses the index (Leftmost column is present)
SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending';

-- GOOD: Uses the index (Leftmost column is present)
SELECT * FROM orders WHERE customer_id = 123;

-- BAD: Ignored index, performs Sequential Scan (Leftmost column missing)
SELECT * FROM orders WHERE status = 'pending';

            

Always order the columns in a composite index from the most selective (highest cardinality, like user_id) to the least selective (like status), based on how your queries actually filter the data.

Covering Indexes: The Secret to "Index Only Scans"

Normally, an index lookup finds the row's location (CTID), and then PostgreSQL must fetch the actual data page from the table heap. This is an "Index Scan". If you only need a few specific columns, you can avoid the heap fetch entirely using a Covering Index with the INCLUDE clause.

              -- Creates an index on customer_id, but also stores total_amount and created_at
-- in the index leaf nodes, without making them part of the search key.
CREATE INDEX idx_orders_customer_covering
ON orders(customer_id)
INCLUDE (total_amount, created_at);

-- This query will result in an "Index Only Scan", reading ONLY from the index.
SELECT customer_id, total_amount, created_at
FROM orders
WHERE customer_id = 123;

            

Caveat: For an Index Only Scan to actually happen, the table must be well-vacuumed so PostgreSQL's visibility map confirms the data pages are visible to all transactions.

Partial Indexes: Shrinking Index Size by 90%

If you only ever query a small subset of a table, don't index the whole thing. Partial indexes store only the rows that match a WHERE condition, making them smaller, faster to update, and quicker to scan.

              -- Instead of indexing all 50 million orders, index only the 50,000 pending ones.
CREATE INDEX idx_orders_pending
ON orders(created_at)
WHERE status = 'pending' AND processed_at IS NULL;

            

Beyond B-Tree: GIN, GiST, and BRIN Indexes

B-Tree is the default and handles 90% of use cases. But for specific data types, specialized indexes are exponentially faster.

              -- For a 500-million row log table sorted by time, a BRIN index is
-- a fraction of the size of a B-Tree and incredibly fast for time-range queries.
CREATE INDEX idx_logs_created_at_brin
ON application_logs USING BRIN(created_at);

            

Pagination: The OFFSET Trap and the Keyset Solution

OFFSET 1000000 LIMIT 50 is a performance killer. PostgreSQL must fetch and discard 1,000,050 rows before returning the 50 you want. As the offset grows, the query gets linearly slower.

The production solution is Keyset Pagination (or the Seek Method). Instead of skipping rows, you tell the database exactly where to start using the last seen value.

              -- BAD: OFFSET pagination (Gets slower as page number increases)
SELECT id, name, created_at
FROM users
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 1000000;

-- GOOD: Keyset pagination (Consistently fast, uses index efficiently)
-- Assume the last item on the previous page had created_at = '2026-07-01' and id = 54321
SELECT id, name, created_at
FROM users
WHERE (created_at, id) < ('2026-07-01', 54321)
ORDER BY created_at DESC, id DESC
LIMIT 50;

            

Tuning Autovacuum for High-Churn Tables

PostgreSQL's autovacuum daemon is responsible for reclaiming storage from dead tuples and updating statistics. The default settings are tuned for a generic workload, but high-traffic tables (like orders or session_logs) will outgrow them, leading to severe table bloat and outdated statistics (which causes the query planner to choose Sequential Scans).

              -- For a high-churn table, lower the scale factor and threshold
-- so vacuum runs more frequently and in smaller, faster batches.
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.05,  -- Vacuum when 5% of rows are dead (default is 20%)
    autovacuum_vacuum_threshold = 1000,     -- Minimum dead rows before triggering (default is 50)
    autovacuum_analyze_scale_factor = 0.02  -- Analyze statistics more frequently
);

            

Finding the Real Bottlenecks with pg_stat_statements

Don't guess which queries are slow. Enable the pg_stat_statements extension to track execution statistics for all SQL statements.

              -- Find the top 5 queries consuming the most total time
SELECT
    substring(query, 1, 100) AS short_query,
    calls,
    round(total_exec_time::numeric, 2) AS total_time_ms,
    round(mean_exec_time::numeric, 2) AS avg_time_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

            

ORM Pitfalls: Entity Framework Core & PostgreSQL

ORMs are productivity tools, but they can generate disastrous SQL if used blindly. Common EF Core pitfalls in PostgreSQL include:

• ●

  The N+1 Query Problem: Iterating over a collection and accessing a navigation property, triggering hundreds of individual SELECT statements. Fix: Use .Include() or .Select() projections.

• ●

  Client-Side Evaluation: Using a C# method in a .Where() clause that PostgreSQL cannot translate. EF Core will pull the entire table into application memory and filter it there. Fix: Check EF Core logs for "client evaluation" warnings.

• ●

  Missing AsNoTracking: For read-only dashboards, always use .AsNoTracking(). Otherwise, EF Core wastes massive CPU and memory building change-tracking snapshots for every row.

Common PostgreSQL Performance Mistakes

• ●

  ❌ Applying functions to indexed columns: WHERE LOWER(email) = 'test@x.com' ignores a standard B-Tree index. Solution: Create a functional index: CREATE INDEX idx_users_lower_email ON users(LOWER(email)).

• ●

  ❌ Implicit type casting: Querying a VARCHAR column with an INTEGER literal (e.g., WHERE phone_number = 12345) forces PostgreSQL to cast the entire column, invalidating the index. Always match data types.

• ●

  ❌ Using SELECT *: Prevents Index Only Scans, wastes network bandwidth, and increases application memory usage.

• ●

  ❌ Over-indexing write-heavy tables: Every index slows down INSERT/UPDATE operations and increases bloat.

• ●

  ❌ Ignoring correlation: If a table's physical disk order matches the index order (high correlation), an Index Scan is very fast. If it's random, the planner may correctly choose a Sequential Scan.

"Adding an index is not a substitute for writing efficient SQL. The best optimization is often rewriting the query, not adding more database structures."

Frequently Asked Questions

Conclusion

Ready to optimize your data layer? Explore our deep dives into [Advanced PostgreSQL Connection Pooling with PgBouncer], [Scaling Time-Series Data in PostgreSQL], and [Avoiding N+1 Queries in Entity Framework Core] to complete your backend performance toolkit.