Optimizing PostgreSQL Performance With Indexing Strategies

What You’ll Need

• Hetzner VPS or DigitalOcean instance with PostgreSQL 14 or higher installed
• n8n Cloud or self-hosted n8n for automated database alert triggers
• Namecheap for configured domain endpoints if exposing database management webhooks

Table of Contents

• Understanding Query Execution and Identifying Slow Queries
• Choosing the Right Index Type: B-Tree, GIN, and BRIN
• Advanced Strategies: Composite, Partial, and Expression Indexes
• Monitoring and Maintaining Index Health
• Getting Started

Understanding Query Execution and Identifying Slow Queries

When your application starts dropping requests or consuming excessive CPU on your server instance, the root cause is almost always an unindexed full table scan. Before creating indexes blindly, you need to understand how PostgreSQL processes your SQL statements.

To profile queries on your server, such as a cloud server hosted on Hetzner VPS, you must enable the pg_stat_statements module. This extension records execution statistics of all SQL statements executed by the server.

Add the following configuration lines to your postgresql.conf file:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000

After modifying the configuration file, restart PostgreSQL and enable the extension inside your target database:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Now you can inspect the top queries ranked by total execution time using this SQL query:

SELECT 
    query, 
    calls, 
    total_exec_time, 
    mean_exec_time, 
    rows 
FROM 
    pg_stat_statements 
ORDER BY 
    total_exec_time DESC 
LIMIT 5;

Once you identify a offending query, use EXPLAIN ANALYZE to read its execution plan. Let us set up a practical schema representing an e-commerce orders ledger:

CREATE TABLE user_orders (
    order_id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    status VARCHAR(32) NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO user_orders (user_id, status, total_amount, created_at)
SELECT 
    (random() * 100000)::BIGINT,
    (ARRAY['pending', 'completed', 'cancelled', 'refunded'])[floor(random() * 4 + 1)],
    (random() * 500)::NUMERIC(10,2),
    NOW() - (random() * interval '365 days')
FROM generate_series(1, 1000000);

Running an unindexed filter query forces PostgreSQL to inspect every single page on the disk:

EXPLAIN ANALYZE 
SELECT * FROM user_orders 
WHERE user_id = 42105 AND status = 'completed';

The output confirms a Sequential Scan over one million rows:

Seq Scan on user_orders  (cost=0.00..20834.00 rows=25 width=49) (actual time=0.082..48.312 rows=31 loops=1)
  Filter: ((user_id = 42105) AND ((status)::text = 'completed'::text))
  Rows Removed by Filter: 999969
Planning Time: 0.115 ms
Execution Time: 48.341 ms

💡 Fast-Track Your Project: Don’t want to configure this yourself? I build custom n8n pipelines and bots. Message me with code SYS3-HUGO.

Choosing the Right Index Type: B-Tree, GIN, and BRIN

PostgreSQL offers multiple index access methods. Selecting the wrong index type can inflate your disk usage while offering zero performance benefits.

B-Tree Indexes

B-Tree is the default index type in PostgreSQL. It works exceptionally well for equality operations, range queries (<, <=, >, >=), and sorting operations (ORDER BY).

CREATE INDEX idx_user_orders_user_id ON user_orders (user_id);

Re-running the previous query using EXPLAIN ANALYZE illustrates the structural shift in processing:

EXPLAIN ANALYZE 
SELECT * FROM user_orders 
WHERE user_id = 42105 AND status = 'completed';

Bitmap Heap Scan on user_orders  (cost=4.57..122.31 rows=25 width=49) (actual time=0.038..0.052 rows=31 loops=1)
  Recheck Cond: (user_id = 42105)
  Filter: ((status)::text = 'completed'::text)
  ->  Bitmap Index Scan on idx_user_orders_user_id  (cost=0.00..4.56 rows=32 width=0) (actual time=0.022..0.022 rows=32 loops=1)
        Index Cond: (user_id = 42105)
Planning Time: 0.182 ms
Execution Time: 0.071 ms

Execution time dropped from 48.341 milliseconds to 0.071 milliseconds, representing an over 600x speed improvement.

GIN Indexes (Generalized Inverted Index)

When working with semi-structured data like JSONB documents, array columns, or full-text search vectors, standard B-Tree indexes fail. Generalized Inverted Indexes (GIN) map individual element values directly to row pointers.

Let us add a JSONB metadata column to demonstrate GIN index performance:

ALTER TABLE user_orders ADD COLUMN payload JSONB;

UPDATE user_orders 
SET payload = jsonb_build_object(
    'device', (ARRAY['ios', 'android', 'desktop'])[floor(random() * 3 + 1)],
    'ip_address', '192.168.1.' || floor(random() * 255)::text,
    'tags', jsonb_build_array('promo', 'mobile_app')
)
WHERE order_id % 2 = 0;

UPDATE user_orders 
SET payload = jsonb_build_object(
    'device', 'desktop',
    'ip_address', '10.0.0.1',
    'tags', jsonb_build_array('web_checkout')
)
WHERE order_id % 2 = 1;

Searching within JSONB without a GIN index requires parsing the JSON document for every row in the sequence:

CREATE INDEX idx_user_orders_payload_gin ON user_orders USING GIN (payload);

Now, containment queries using the @> operator leverage the index seamlessly:

EXPLAIN ANALYZE 
SELECT count(*) FROM user_orders 
WHERE payload @> '{"device": "android"}';

BRIN Indexes (Block Range Index)

For extremely large datasets where values correlate naturally with physical storage order (such as append-only log events or timestamped telemetry data), B-Tree indexes become too large to fit into RAM.

A Block Range Index (BRIN) stores summary metadata (minimum and maximum value) for contiguous ranges of physical disk pages. A BRIN index takes up a tiny fraction of the disk space required by a standard B-Tree index.

CREATE INDEX idx_user_orders_created_at_brin ON user_orders USING BRIN (created_at) WITH (pages_per_range = 128);

While a B-Tree index on a timestamp column for 100 million rows might consume several gigabytes, a BRIN index often consumes less than a single megabyte while maintaining high scan rates.

Advanced Strategies: Composite, Partial, and Expression Indexes

Composite Indexes and the Leftmost Prefix Rule

When queries filter by multiple conditions simultaneously, a single multi-column index yields better performance than merging separate single-column indexes.

CREATE INDEX idx_orders_user_status_created ON user_orders (user_id, status, created_at DESC);

The column order inside a composite index matters fundamentally:

1. Columns queried with equality operators (=) must come first.
2. Columns evaluated with range filters or used in ORDER BY clauses must come last.

This index services queries searching by user_id, queries searching by user_id and status, or queries ordering results by created_at for a specific user. It will not service a query that filters solely on status without specifying user_id.

Partial Indexes

Indexing every row in a table is wasteful if you frequently query a tiny subset of records. Partial indexes include a WHERE clause during index creation.

Consider an authentication system where millions of revoked tokens accumulate, but active authorization checks only query non-revoked session records. For detailed architectural details on managing user authentication states safely, read our guide on Implementing JWT Token Refresh Strategies Self-Hosted.

We can recreate that optimization directly inside PostgreSQL:

CREATE TABLE user_sessions (
    session_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id BIGINT NOT NULL,
    refresh_token TEXT NOT NULL,
    is_revoked BOOLEAN NOT NULL DEFAULT false,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL
);

INSERT INTO user_sessions (user_id, refresh_token, is_revoked, expires_at)
SELECT 
    (random() * 50000)::BIGINT,
    md5(random()::text),
    (random() > 0.05),
    NOW() + interval '7 days'
FROM generate_series(1, 500000);

CREATE INDEX idx_active_sessions ON user_sessions (refresh_token) 
WHERE is_revoked = false;

Because 95% of rows in user_sessions have is_revoked = true, our partial index ignores them entirely. The index size remains tiny, keeping write overhead for revoked session operations near zero.

Expression Indexes

If your query applies functions to table columns inside WHERE clauses, standard indexes are bypassed entirely:

SELECT * FROM user_orders WHERE LOWER(status) = 'completed';

PostgreSQL cannot use idx_user_orders_user_id here. To resolve this, create an index directly on the computed expression:

CREATE INDEX idx_orders_lower_status ON user_orders (LOWER(status));

Monitoring and Maintaining Index Health

Indexes carry a write penalty. Every time an INSERT, UPDATE, or DELETE executes, PostgreSQL must update all affected indexes. Accumulating unused or bloated indexes degrades write performance and consumes memory.

Identifying Unused Indexes

Run this administrative query to identify indexes that waste disk space without serving incoming queries:

SELECT 
    schemaname, 
    relname AS table_name, 
    indexrelname AS index_name, 
    idx_scan AS number_of_scans, 
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM 
    pg_stat_user_indexes 
WHERE 
    idx_scan = 0 
    AND idx_expression_predicate IS NULL
    AND indexrelname NOT LIKE '%_pkey'
ORDER BY 
    pg_relation_size(indexrelid) DESC;

If an index reports zero scans over weeks of runtime, safely remove it using DROP INDEX CONCURRENTLY.

Rebuilding Bloated Indexes

High-volume update operations cause index fragmentation (bloat), where index pages retain empty slots that cannot be reclaimed naturally. To defragment indexes without locking out live application reads and writes, perform a concurrent rebuild:

REINDEX INDEX CONCURRENTLY idx_orders_user_status_created;

To automate database cleanup routines safely in production background workers, consider setting up a dedicated scheduling architecture using Python. Read our step-by-step tutorial on Scheduling Python Jobs with APScheduler and Redis to build resilient background index maintenance tasks.

Furthermore, if your server detects sudden query performance degradation, you can send instant alerts directly to your mobile phone. Check out our guide on Building a Self Hosted WhatsApp Bot to set up custom PostgreSQL health notification channels.

Getting Started

To implement these index optimization techniques on your infrastructure, ensure your server is correctly configured and provisioned:

1. Provision a high-performance database instance on Hetzner VPS or DigitalOcean.
2. Enable pg_stat_statements to track slow-running queries across your environment.
3. Replace inefficient sequence scans with target-specific B-Tree, GIN, or Partial indexes.
4. Set up operational webhooks using n8n Cloud to monitor slow queries continuously.

Outsource Your Automation

Don’t have time? I build production n8n workflows, WhatsApp bots, and fully automated YouTube Shorts pipelines. Hire me on Fiverr, mention SYS3-HUGO for priority. Or DM at chasebot.online.