When database tables grow past a few million rows, query times that used to take milliseconds start climbing. You notice it first in your API response times. Then your background jobs take longer to finish. Eventually, the database CPU hits 100%, and everything grinds to a halt, often exacerbated by contention from database locks at the row, page, or table level.
Throwing larger instance sizes at the problem only buys you time. True optimization requires changing how PostgreSQL interacts with the disk, how it plans queries, and how it structured the data.
Reading the Map with EXPLAIN (ANALYZE, BUFFERS)
You cannot optimize a query if you do not know what the database engine is actually doing. The default EXPLAIN command only shows the query planner's estimate. It guesses the cost based on internal statistics, which are often out of date.
To see what actually happened during execution, use EXPLAIN (ANALYZE, BUFFERS).
EXPLAIN (ANALYZE, BUFFERS)
SELECT email, signup_date
FROM users
WHERE status = 'active' AND signup_date > '2026-01-01';The output of this command shows the physical execution path. The BUFFERS option adds critical data points:
- Shared Hit: The data blocks read directly from the PostgreSQL shared buffers (RAM).
- Shared Read: The data blocks read from the operating system cache or directly from disk because they were not in RAM.
- Shared Written: The blocks written to disk during the query.
Every block in PostgreSQL is 8kB by default. If your query shows shared read=10000, it pulled roughly 80MB of data from disk. Disk access is orders of magnitude slower than RAM. Your primary goal in query optimization is minimizing shared read counts and maximizing shared hit counts.
Look out for sequential scans on large tables. A Seq Scan means PostgreSQL is reading the entire table file from disk, row by row. If the planner chooses a sequential scan on a table with 50 million rows, the disk I/O will choke other operations.
Moving Beyond Basic Indexes
Most developers know they need to index foreign keys and columns used in WHERE clauses. But default B-Tree indexes on large tables become massive, consuming gigabytes of RAM and slowing down write operations. To mitigate this, teams often employ multitenant database index tuning strategies to optimize query paths.
Partial Indexes for Specific States
If you query a subset of your data frequently, do not index the whole table. A partial index contains only the rows that match a specific filter condition.
Suppose you have an orders table with 100 million rows, but your background workers only scan for orders that are in a pending state.
CREATE INDEX idx_orders_pending
ON orders (created_at)
WHERE status = 'pending';This index is tiny. It only grows when new pending orders are created, and rows are removed from the index as soon as their status changes to completed. It fits entirely in RAM, making index lookups incredibly fast.
Covering Indexes and Index-Only Scans
When PostgreSQL finds a matching row in an index, it still has to fetch the actual row data from the table heap to get the requested columns. This double lookup adds disk read overhead.
You can avoid this using a covering index with the INCLUDE clause. This appends non-key columns to the leaf nodes of the B-Tree index.
CREATE INDEX idx_users_email_include_created
ON users (email)
INCLUDE (created_at);If you execute a query that looks up a user by email and only selects the created_at timestamp, PostgreSQL performs an Index-Only Scan. It returns the data directly from the index without reading the table heap at all.
- This query triggers an Index-Only Scan
SELECT email, created_at
FROM users
WHERE email = 'user@example.com';Keep in mind that the visibility map must be clean for Index-Only Scans to work efficiently. If the table has frequent updates and autovacuum has not run recently, PostgreSQL will still have to check the heap to verify row visibility.
Partitioning Giant Tables
When a single table grows past 100GB, B-Tree indexes become too large to fit in memory. At this scale, comparing PostgreSQL partitioning vs partial indexing is essential to determine the best strategy for keeping queries fast.
Partitioning splits one logical table into smaller physical tables under the hood. The query planner uses partition pruning to ignore physical tables that do not match the query filters.
Range partitioning by date is the most common pattern for time-series or transactional data.
CREATE TABLE payments (
id uuid NOT NULL,
amount numeric(10,2),
payment_date timestamp with time zone NOT NULL
) PARTITION BY RANGE (payment_date);You then create child tables for specific ranges:
CREATE TABLE payments_y2026m01 PARTITION OF payments
FOR VALUES FROM ('2026-01-01 00:00:00+00') TO ('2026-02-01 00:00:00+00');
CREATE TABLE payments_y2026m02 PARTITION OF payments
FOR VALUES FROM ('2026-02-01 00:00:00+00') TO ('2026-03-01 00:00:00+00');When you query this table, always include the partition key in your filter:
SELECT sum(amount)
FROM payments
WHERE payment_date >= '2026-01-15' AND payment_date < '2026-01-20';The planner immediately discards all other partitions and only queries payments_y2026m01. This keeps the scanned dataset small and allows the index on the partition to fit comfortably in memory.
Avoid creating too many partitions. Having thousands of partitions causes the query planner to spend more time analyzing metadata than executing the query. A good rule of thumb is keeping the total active partition count under a few hundred.
Tuning Autovacuum and Statistics
PostgreSQL relies on the cost-based optimizer to choose the fastest execution path. The optimizer relies on statistics collected by the ANALYZE process. If these statistics are stale, the planner might choose a sequential scan when an index scan would be faster.
Adjusting Statistics Targets
By default, PostgreSQL samples 100 rows per column to build statistics. For large tables with highly skewed data distributions, this sample size is too small.
You can increase the statistics limit for specific columns:
ALTER TABLE users ALTER COLUMN status SET STATISTICS 500;
ANALYZE users;This tells the database to build a more detailed histogram for the status column, helping the planner make better decisions on complex queries.
Aggressive Autovacuum Configurations
Autovacuum cleans up dead row versions (dead tuples) left behind by updates and deletes. It also updates table statistics. By default, autovacuum is tuned conservatively to avoid CPU spikes on small databases. On large tables, the default settings are not aggressive enough.
The default configuration triggers a vacuum when 20% of the table rows change. On a table with 50 million rows, that requires 10 million updates or deletes before autovacuum kicks in. By that time, table bloat is massive, and query performance has degraded.
Adjust these parameters for your largest tables:
ALTER TABLE large_transaction_table SET (
autovacuum_vacuum_scale_factor = 0.05,
autovacuum_vacuum_threshold = 10000,
autovacuum_analyze_scale_factor = 0.02,
autovacuum_analyze_threshold = 5000
);This configuration triggers a vacuum when 5% of the rows plus 10,000 rows change, and updates statistics when 2% of the rows change. It keeps the table clean and stats fresh without waiting for massive write volumes to accumulate.
Rewriting Common Query Anti-Patterns
Sometimes the database engine cannot optimize a query because of how it is written. Small changes in SQL syntax can yield massive performance gains.
Replacing OR with UNION
The query planner often struggles to use indexes when a query contains an OR clause matching different columns.
- Slow: Often forces a sequential scan
SELECT id, email, username
FROM users
WHERE email = 'test@example.com' OR username = 'testuser';The planner might decide that checking two different indexes and combining the results is too expensive, falling back to a full table scan. You can rewrite this using UNION:
- Fast: Uses separate index lookups for each branch
SELECT id, email, username
FROM users
WHERE email = 'test@example.com'
UNION
SELECT id, email, username
FROM users
WHERE username = 'testuser';Each branch of the UNION is planned independently. PostgreSQL will use the index on email for the first query, the index on username for the second query, and then merge the results.
Preventing CTE Materialization Issues
In older versions of PostgreSQL, Common Table Expressions (CTEs) acted as optimization barriers. The database materialized the CTE output to a temporary structure in memory, preventing the optimizer from pushing outer WHERE clauses down into the CTE.
While PostgreSQL 12 and later attempts to inline CTEs automatically, complex queries can still trigger materialization. You can force inlining using the NOT MATERIALIZED hint.
WITH regional_sales AS NOT MATERIALIZED (
SELECT id, region, amount
FROM sales
WHERE year = 2026
)
SELECT *
FROM regional_sales
WHERE region = 'north';This tells the planner to treat the CTE like a subquery, allowing it to push the region = 'north' filter down and use an index on both year and region.
Avoiding COUNT(*) on Large Tables
Running a SELECT count(*) on a table with tens of millions of rows requires PostgreSQL to scan every single row version to verify its visibility. This is a slow, I/O-intensive process.
If you only need an estimate for a dashboard or pagination UI, query the system catalog tables instead.
SELECT reltuples::bigint AS estimate
FROM pg_class
WHERE relname = 'large_transaction_table';
