DevOps & Cloud

PostgreSQL Optimization Guide: Indexing, Query Analysis & Performance

13 min readMarch 16, 2026
PostgreSQL optimizasyonPostgreSQL indekslemePostgreSQL performansEXPLAIN ANALYZEPostgreSQL rehberiVeritabanı optimizasyonuPostgreSQL .NETConnection poolingPostgreSQL TürkiyeB-tree indexGIN indexSorgu optimizasyonu

PostgreSQL stands out among open-source relational databases for its performance, reliability, and feature richness. However, without proper configuration and query optimization, serious performance issues can arise with large datasets. In this article, I will cover PostgreSQL indexing strategies, query analysis with EXPLAIN ANALYZE, and query optimization techniques for production environments in detail.

In my projects, I have repeatedly experienced reducing queries that took seconds down to millisecond-level response times by optimizing PostgreSQL queries. Proper index design and query plan analysis are the cornerstones of database performance.

Indexing Strategies

Core Index Types

PostgreSQL offers a wide variety of index types, each with different use cases:

  • B-Tree: Default index type. Ideal for equality and range queries
  • Hash: For equality queries only. Can be slightly faster than B-Tree
  • GIN (Generalized Inverted Index): For JSONB, full-text search, and array queries
  • GiST (Generalized Search Tree): For geometric data, full-text search, and range types
  • BRIN (Block Range Index): For large, naturally ordered tables (e.g., time-series data)
sql
-- B-Tree index: Most common usage
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- Composite index: Multiple columns
CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at DESC);

-- Partial index: Only rows matching a specific condition
CREATE INDEX idx_orders_active ON orders (status, created_at)
WHERE status = 'active';

-- Covering index: Additional columns for index-only scans
CREATE INDEX idx_orders_covering ON orders (customer_id)
INCLUDE (total_amount, status);

-- GIN index: For JSONB fields
CREATE INDEX idx_products_metadata ON products USING gin (metadata jsonb_path_ops);

-- BRIN index: For time-series data
CREATE INDEX idx_logs_created_at ON application_logs USING brin (created_at)
WITH (pages_per_range = 32);

-- Expression index: For function-based queries
CREATE INDEX idx_users_email_lower ON users (lower(email));

Index Selection Criteria

To choose the right index, answer these questions:

  • Which columns does the query access with WHERE clauses?
  • Which columns are used in JOIN operations?
  • Which columns do ORDER BY and GROUP BY apply to?
  • What is the row count and data distribution in the table?

Query Analysis with EXPLAIN ANALYZE

Reading the Query Plan

EXPLAIN ANALYZE shows the detailed plan of how PostgreSQL executes a query. It is the most effective way to diagnose performance issues.

sql
-- Slow query example
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total_amount, c.name, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= '2025-01-01'
  AND o.status = 'completed'
  AND o.total_amount > 100
ORDER BY o.created_at DESC
LIMIT 50;

/*
Example output (unoptimized):

Limit  (cost=15234.56..15234.69 rows=50 width=72) (actual time=1523.456..1523.489 rows=50 loops=1)
  Buffers: shared hit=2341 read=8923
  ->  Sort  (cost=15234.56..15298.23 rows=25468 width=72) (actual time=1523.454..1523.478 rows=50 loops=1)
        Sort Key: o.created_at DESC
        Sort Method: top-N heapsort  Memory: 32kB
        ->  Hash Join  (cost=1234.56..14567.89 rows=25468 width=72) (actual time=45.123..1498.234 rows=25468 loops=1)
              Hash Cond: (o.customer_id = c.id)
              ->  Seq Scan on orders o  (cost=0.00..12345.67 rows=25468 width=48) (actual time=0.015..1423.567 rows=25468 loops=1)
                    Filter: ((created_at >= '2025-01-01') AND (status = 'completed') AND (total_amount > 100))
                    Rows Removed by Filter: 974532
                    Buffers: shared hit=1234 read=8923
Planning Time: 0.234 ms
Execution Time: 1523.678 ms
*/

-- After adding an index:
CREATE INDEX idx_orders_status_date ON orders (status, created_at DESC)
INCLUDE (total_amount, customer_id)
WHERE status = 'completed';

-- Optimized plan for the same query:
/*
Limit  (cost=234.56..234.69 rows=50 width=72) (actual time=2.345..2.378 rows=50 loops=1)
  Buffers: shared hit=156
  ->  Nested Loop  (cost=234.56..1234.89 rows=12734 width=72) (actual time=2.343..2.371 rows=50 loops=1)
        ->  Index Only Scan using idx_orders_status_date on orders o
              (cost=0.42..567.89 rows=12734 width=48) (actual time=0.034..0.156 rows=50 loops=1)
              Index Cond: ((status = 'completed') AND (created_at >= '2025-01-01'))
              Filter: (total_amount > 100)
              Heap Fetches: 0
        ->  Index Scan using customers_pkey on customers c
              (cost=0.29..0.31 rows=1 width=28) (actual time=0.003..0.003 rows=1 loops=50)
              Index Cond: (id = o.customer_id)
Planning Time: 0.312 ms
Execution Time: 2.456 ms
*/

Key Metrics to Watch

Focus on these metrics in the EXPLAIN ANALYZE output:

  • Seq Scan: Sequential scans on large tables indicate performance issues
  • Rows Removed by Filter: High values suggest missing indexes
  • Buffers read: Number of blocks read from disk; high values mean data is not cached
  • actual time: Real execution time; compare with cost estimates
  • loops: Number of nested loops; high values warrant reviewing the join strategy

Query Performance Monitoring with pg_stat_statements

The pg_stat_statements extension collects statistics for all queries in the database. It is indispensable for identifying the slowest queries, most frequently called queries, and most resource-consuming queries:

sql
-- Enable the extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- List the 10 slowest queries (by total time)
SELECT
    calls,
    round(total_exec_time::numeric, 2) AS total_time_ms,
    round(mean_exec_time::numeric, 2) AS avg_time_ms,
    round(stddev_exec_time::numeric, 2) AS stddev_ms,
    rows,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Most frequently called queries
SELECT
    calls,
    round(mean_exec_time::numeric, 2) AS avg_time_ms,
    query
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

-- Reset statistics (do this periodically)
SELECT pg_stat_statements_reset();

By reviewing this data regularly, you can detect performance regressions early and determine your optimization priorities.

The N+1 Query Problem and Solutions

The N+1 problem is one of the most common performance issues in applications using ORMs. The main query runs once, and if an additional query is fired for each row in the result, a total of N+1 queries are executed:

sql
-- N+1 problem: Querying customer separately for each order
-- Query 1: SELECT * FROM orders WHERE status = 'active';
-- Query 2..N+1: SELECT * FROM customers WHERE id = ? (for each order)

-- Solution: Resolve in a single query with JOIN
SELECT o.*, c.name, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'active';

-- Alternative solution: Batch querying with IN clause
SELECT * FROM customers
WHERE id IN (
    SELECT DISTINCT customer_id FROM orders WHERE status = 'active'
);

In ORMs like EF Core, using .Include() for eager loading solves the N+1 problem at the code level. However, monitoring repeating query patterns with pg_stat_statements on the database side is equally important.

Table Partitioning

Physically partitioning large tables can significantly improve query performance. PostgreSQL supports both range and list partitioning:

sql
-- Range partitioning: Date-based partitioning
CREATE TABLE orders (
    id BIGSERIAL,
    customer_id INT NOT NULL,
    total_amount DECIMAL(10,2),
    status VARCHAR(20),
    created_at TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE orders_2025_01 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE orders_2025_02 PARTITION OF orders
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

CREATE TABLE orders_2025_03 PARTITION OF orders
    FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

-- Default partition (for undefined ranges)
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

-- Create indexes for each partition (automatically inherited)
CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_status ON orders (status, created_at DESC);

With partitioning, date-filtered queries only scan the relevant partition, returning fast results even on tables with millions of rows. However, partitioning has overhead and provides no benefit on small tables.

Connection Pooling

Connection pooling is critically important in production environments. PgBouncer is the most widely used connection pooler for PostgreSQL.

ini
; pgbouncer.ini
[databases]
myapp = host=localhost port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

; Pool settings
pool_mode = transaction
default_pool_size = 25
min_pool_size = 5
max_client_conn = 200
reserve_pool_size = 5

; Timeout settings
server_idle_timeout = 600
client_idle_timeout = 0
query_timeout = 30

VACUUM and ANALYZE in Detail

Due to PostgreSQL's MVCC (Multi-Version Concurrency Control) architecture, regular VACUUM operations are necessary. Understanding how VACUUM works forms the foundation of your database maintenance strategy:

  • VACUUM: Cleans up dead rows and reclaims space, but does not return space to the operating system
  • VACUUM FULL: Completely rewrites the table and returns space to the OS (requires table lock)
  • VACUUM ANALYZE: VACUUM + statistics update (critical for the planner)
  • autovacuum: Configure automatic vacuum; never disable it
  • REINDEX: Rebuilds bloated indexes
sql
-- Customize autovacuum settings per table
ALTER TABLE orders SET (
    autovacuum_vacuum_threshold = 1000,
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_threshold = 500,
    autovacuum_analyze_scale_factor = 0.02
);

-- Check table bloat
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
    n_dead_tup,
    n_live_tup,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    last_vacuum,
    last_autovacuum,
    last_analyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Aggressively configuring autovacuum parameters on tables with high update rates prevents table bloat and preserves query performance.

Practical Tips and Summary

Keep these points in mind for PostgreSQL optimization:

  1. EXPLAIN ANALYZE habit: Analyze every new query before deploying to production
  2. Index maintenance: Identify and remove unused indexes with pg_stat_user_indexes
  3. Statistics updates: Run ANALYZE regularly so the planner makes correct decisions
  4. work_mem setting: Allocate sufficient memory for sort and hash operations in complex queries
  5. Slow query log: Record slow queries with log_min_duration_statement and review them regularly

When properly optimized, PostgreSQL can deliver millisecond-level responses even on tables with millions of rows. The key is understanding data access patterns and building an index strategy that matches them.

Related Articles

Entity Framework Core: The Modern Way to Handle Databases

Manage database operations with Entity Framework Core. Code-first approach, migrations, and performance optimization.

.NET Performance Optimization: Profiling and Best Practices

Optimize .NET application performance. Profiling tools, memory management, and async patterns.

Advanced EF Core: Migrations, Performance, and Raw SQL

Master advanced Entity Framework Core features. Migration strategies, performance optimization, interceptors, and raw SQL.

Have a Flutter Project?

I build high-performance Flutter applications for iOS, Android, and web.

Get in Touch