Last updated: 17 May 2026
PostgreSQL performance tuning focuses on index strategy, autovacuum configuration, work_mem allocation, effective_cache_size settings and connection pooling via PgBouncer. A well-tuned PostgreSQL instance reduces query times by 50-80% compared to default configuration. The most common UK deployment mistake is running PostgreSQL on default settings with no connection pooler on cloud instances.
PostgreSQL and MySQL are both capable relational databases, but they make different architectural tradeoffs that affect performance characteristics for different workloads. Understanding these differences helps UK businesses choose the right database and apply the correct tuning approach.
| Characteristic | PostgreSQL | MySQL (InnoDB) |
|---|---|---|
| Index types available | B-tree, Hash, GIN, GiST, SP-GiST, BRIN, Bloom | B-tree, Full-text, Spatial (R-tree) |
| Parallel query execution | Yes -- configurable per query | Limited (MySQL 8.0+) |
| MVCC implementation | Row-level MVCC; requires VACUUM for cleanup | Undo log-based MVCC; automatic cleanup |
| Connection model | Process-per-connection (higher memory per connection) | Thread-per-connection (lower memory per connection) |
| JSON support | JSONB with full indexing (GIN) | JSON type, limited indexing |
| Full-text search | Built-in tsvector with GIN/GiST indexes | FULLTEXT index; less configurable |
| Partial/filtered indexes | Yes (WHERE clause on CREATE INDEX) | No (MySQL 8 adds functional indexes only) |
| Suitable for | Complex queries, JSON workloads, analytics, multi-version apps | High-throughput OLTP, simple queries at scale, WordPress/Drupal |
PostgreSQL's process-per-connection model is the primary reason connection pooling is more critical for PostgreSQL than MySQL. Each PostgreSQL connection spawns a new OS process consuming approximately 5-10MB of memory. At 100 connections, that is 500MB-1GB consumed purely by connection overhead. PgBouncer is not optional for any PostgreSQL deployment receiving more than 20 concurrent application connections -- it is a required component of a production setup.
MySQL's thread model has lower per-connection overhead, which is why MySQL deployments often run without a dedicated connection pooler (though ProxySQL is still beneficial for high-concurrency applications). PostgreSQL's advantage is its significantly richer index type repertoire and more powerful query planner, which delivers better performance for complex queries at the cost of requiring more careful operational configuration.
PostgreSQL supports seven index types. Choosing the right index type for each column's data type and query pattern is a PostgreSQL-specific skill that MySQL DBAs must learn when migrating to PostgreSQL.
B-tree (default):
-- B-tree: default when no index type specified
-- Use for: equality (=), range (<, >, BETWEEN), sorting (ORDER BY), LIKE 'prefix%'
CREATE INDEX idx_orders_customer ON orders (customer_id);
-- Multi-column B-tree for composite queries
CREATE INDEX idx_orders_composite ON orders (customer_id, order_date, status);
-- Covering B-tree index (INCLUDE adds non-key columns)
CREATE INDEX idx_orders_covering
ON orders (customer_id, order_date)
INCLUDE (total_amount, status);
-- Queries selecting only customer_id, order_date, total_amount, status
-- can be answered entirely from this index (index-only scan)
Hash:
-- Hash: faster than B-tree for equality-only comparisons
-- Use for: exact equality (=) only; no range, no ORDER BY benefit
-- Appropriate when column is ONLY ever used in equality comparisons
CREATE INDEX idx_sessions_token ON sessions USING HASH (session_token);
-- Note: Hash indexes are WAL-logged since PostgreSQL 10 (safe for replication)
-- Before PostgreSQL 10, Hash indexes were not crash-safe
GIN (Generalised Inverted Index):
-- GIN: for composite values where you search for elements within
-- Use for: JSONB (contains @>), arrays (&&, @>), full-text search (@@)
-- GIN is slower to build and update than B-tree, faster for element searches
-- Full-text search with GIN
CREATE INDEX idx_articles_fts ON articles
USING GIN (to_tsvector('english', title || ' ' || body));
-- Query using the GIN full-text index
SELECT title FROM articles
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'database & performance');
-- JSONB search with GIN
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
-- Query: find products where metadata contains specific key-value
SELECT id, name FROM products
WHERE metadata @> '{"category": "electronics"}'::jsonb;
-- Array search with GIN
CREATE INDEX idx_posts_tags ON posts USING GIN (tags); -- tags is text[]
SELECT id, title FROM posts WHERE tags @> ARRAY['postgresql', 'performance'];
GiST (Generalised Search Tree):
-- GiST: for geometric/geographic data and custom operator classes
-- Use for: PostGIS geographic queries, range types, geometric shapes
-- GiST supports NEAREST NEIGHBOUR searches; GIN does not
-- Geographic distance queries with PostGIS
CREATE INDEX idx_locations_geo ON locations USING GIST (coordinates);
SELECT name FROM locations
WHERE ST_DWithin(coordinates, ST_MakePoint(-0.1278, 51.5074)::geography, 5000);
-- Finds all locations within 5km of central London
-- Range type overlap with GiST
CREATE INDEX idx_bookings_period ON bookings USING GIST (booking_period);
SELECT * FROM bookings WHERE booking_period && '[2026-06-01, 2026-06-07]'::daterange;
BRIN (Block Range Index):
-- BRIN: tiny index for naturally ordered large tables
-- Use for: timestamp/date columns on append-only or insert-ordered tables
-- Size: 100-1000x smaller than B-tree; effective when data is physically ordered
-- NOT suitable for randomly ordered data (low correlation)
CREATE INDEX idx_events_created_brin ON events
USING BRIN (created_at) WITH (pages_per_range = 128);
-- Check correlation before choosing BRIN:
SELECT tablename, attname, correlation
FROM pg_stats
WHERE tablename = 'events' AND attname = 'created_at';
-- correlation close to 1.0 = data is physically ordered = BRIN is effective
-- correlation close to 0.0 = data is disordered = use B-tree instead
Partial indexes:
-- Partial index: smaller, faster index covering only a subset of rows
-- Use when queries consistently filter on a specific condition
-- Only index pending orders (most processed orders never queried by status)
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';
-- The query that uses this index MUST include the WHERE clause condition
SELECT * FROM orders WHERE status = 'pending' AND created_at > NOW() - INTERVAL '7 days';
-- Partial unique index: enforce uniqueness only for non-null values
CREATE UNIQUE INDEX idx_users_email_active ON users (email)
WHERE deleted_at IS NULL; -- Soft-deleted users can share emails
EXPLAIN ANALYZE is PostgreSQL's query plan inspector. It shows the execution plan PostgreSQL chose for a query, including every operation performed, the estimated vs actual row counts, and the time spent at each step. Understanding EXPLAIN ANALYZE output is the core skill for PostgreSQL performance debugging.
Basic usage:
-- EXPLAIN: shows estimated plan without executing the query
EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;
-- EXPLAIN ANALYZE: executes the query and shows actual timing and row counts
-- WARNING: for DELETE/UPDATE/INSERT, wrap in a transaction and roll back
BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE status = 'cancelled';
ROLLBACK;
-- EXPLAIN (ANALYZE, BUFFERS): shows buffer usage (cache hits vs disk reads)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.order_id, c.customer_name, o.total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date >= '2026-01-01'
ORDER BY o.order_date DESC
LIMIT 50;
Reading the output -- key nodes and what they mean:
-- Example EXPLAIN ANALYZE output:
Sort (cost=12543.21..12568.21 rows=10000 width=48)
(actual time=245.123..246.890 rows=50 loops=1)
Sort Key: o.order_date DESC
Sort Method: top-N heapsort Memory: 28kB
-> Hash Join (cost=1250.00..12168.21 rows=10000 width=48)
(actual time=15.234..240.123 rows=8750 loops=1)
Hash Cond: (o.customer_id = c.id)
Buffers: shared hit=2340 read=890
-> Index Scan using idx_orders_date on orders o
(cost=0.43..9500.00 rows=10000 width=32)
(actual time=0.087..180.234 rows=8750 loops=1)
Index Cond: (order_date >= '2026-01-01')
Buffers: shared hit=1240 read=890
-> Hash (cost=750.00..750.00 rows=40000 width=16)
(actual time=12.345..12.345 rows=40000 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 2048kB
-> Seq Scan on customers c
(cost=0..750.00 rows=40000 width=16)
(actual time=0.012..8.234 rows=40000 loops=1)
Buffers: shared hit=1100
Planning Time: 2.345 ms
Execution Time: 247.123 ms
-- Key things to read:
-- cost=X..Y: estimated startup..total cost (arbitrary units)
-- actual time=X..Y: actual milliseconds startup..total (loops=N means run N times)
-- rows=N: actual rows returned at this step
-- Buffers: shared hit=N read=N
-- hit = served from shared_buffers (memory)
-- read = fetched from disk (slow)
-- High read count relative to hit = buffer pool undersized for this query
-- Seq Scan on customers: full table scan on 40,000 row customers table
-- For this query it may be fine (small table, used for hash join)
-- Seq Scan on a large table in a WHERE clause is the red flag
Identifying the problem nodes:
loops=50000, the inner part of the loop executed 50,000 times. This is the EXPLAIN signature of an N+1 pattern or a missing join index.ANALYZE table_name to refresh statistics. Stale statistics cause the query planner to choose poor execution plans.shared_buffers, or this is a cold query on rarely-accessed data.Visualising EXPLAIN output:
For complex multi-table queries, text EXPLAIN output is difficult to read. Use explain.dalibo.com (free, paste EXPLAIN JSON output) for a visual tree representation that highlights the most expensive nodes. Run with EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) and paste the output into the tool.
In a PostgreSQL performance audit of a London SaaS company running a subscription analytics platform, the reporting query generating weekly subscriber cohort analysis was taking 4.2 seconds. The table contained 2.1 million event rows with a created_at timestamp column. The table had a B-tree index on created_at, but the correlation between physical row order and created_at values was 0.98 -- the data was almost perfectly physically ordered because rows were only ever appended (events are immutable once recorded).
EXPLAIN ANALYZE showed the B-tree index was being used, but with high Buffers: read counts. The B-tree index on 2.1 million rows occupied 45MB on disk. We replaced it with a BRIN index using pages_per_range = 64. The BRIN index occupied 48KB -- 1,000x smaller. Query time dropped from 4.2 seconds to 0.08 seconds -- a 98% improvement. The query planner was now using the BRIN index to identify which 8-kilobyte table pages could possibly contain the target date range and reading only those pages, rather than navigating the B-tree and then doing random I/O across the table.
This result is only achievable because the data was physically ordered by insert time. On a table where rows are updated frequently (changing created_at values, or with high delete rates creating holes), the BRIN index would be far less effective. The correlation check -- SELECT attname, correlation FROM pg_stats WHERE tablename = 'events' -- is a 2-second check that determines whether BRIN is applicable before spending any time testing it.
A second consistent finding: UK SaaS companies running on AWS RDS PostgreSQL frequently have work_mem set to its default of 4MB. At 4MB, any sort or hash join operation that requires more than 4MB of memory spills to disk. EXPLAIN ANALYZE output shows this as Sort Method: external merge Disk: 128MB -- meaning the sort wrote 128MB to temporary files because work_mem was too small to hold the sort in memory. Increasing work_mem to 32-64MB converts disk sorts to in-memory sorts, reducing query time for complex analytical queries by 60-90%.
For a systematic approach to PostgreSQL and other database performance issues, see our complete database performance tuning guide.
PostgreSQL uses Multi-Version Concurrency Control (MVCC) to handle concurrent transactions. When a row is updated or deleted, PostgreSQL does not overwrite or remove it immediately -- it marks the old version as dead and keeps it in the table until a VACUUM operation cleans it up. This dead tuple accumulation is called table bloat, and it directly degrades query performance by forcing PostgreSQL to read and skip dead rows during scans.
Autovacuum is PostgreSQL's background process that automatically removes dead tuples. The default autovacuum settings are calibrated for small databases and low-write workloads. High-write production databases need more aggressive autovacuum settings to prevent bloat from accumulating.
Checking for table bloat:
-- Find tables with highest dead tuple counts
SELECT
schemaname,
relname AS table_name,
n_dead_tup,
n_live_tup,
ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 1)
AS dead_pct,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 20;
-- Tables with dead_pct > 10% need more frequent vacuuming
-- Tables where last_autovacuum is NULL may have autovacuum disabled
Default autovacuum thresholds and why they fail high-write tables:
Default autovacuum triggers when: dead tuples exceed autovacuum_vacuum_threshold (50) + (autovacuum_vacuum_scale_factor (0.2) * total rows). For a table with 1 million rows, this means autovacuum triggers after 200,050 dead tuples accumulate. At 1,000 writes per second, a high-write table can accumulate 200,000 dead tuples in 3-4 minutes. Default autovacuum fires too infrequently and runs too gently to keep up.
Per-table autovacuum overrides (the correct approach):
-- Apply aggressive autovacuum settings to a high-write table
-- without changing global settings (which could overwhelm low-write tables)
ALTER TABLE high_write_events SET (
autovacuum_vacuum_scale_factor = 0.01, -- Trigger at 1% dead tuples (vs 20%)
autovacuum_vacuum_threshold = 100, -- Minimum dead tuples before trigger
autovacuum_analyze_scale_factor = 0.005, -- Update statistics at 0.5% change
autovacuum_vacuum_cost_delay = 2, -- 2ms delay between cost limit resets
-- (default 2ms; lower = faster vacuum)
autovacuum_vacuum_cost_limit = 400 -- Cost limit per round (default 200)
);
-- For extreme high-write tables (order lines, event logs, audit trails)
ALTER TABLE audit_log SET (
autovacuum_vacuum_scale_factor = 0.005,
autovacuum_vacuum_threshold = 50,
autovacuum_vacuum_cost_delay = 0,
autovacuum_vacuum_cost_limit = 800
);
-- Verify per-table autovacuum settings
SELECT relname, reloptions
FROM pg_class
WHERE relname IN ('high_write_events', 'audit_log');
Global autovacuum settings (postgresql.conf) for UK production databases:
# Increase number of autovacuum worker processes (default 3)
autovacuum_max_workers = 6
# Reduce naptime for faster response to bloat (default 1 minute)
autovacuum_naptime = 15s
# Increase cost limit to allow faster vacuuming (default 200)
autovacuum_vacuum_cost_limit = 400
# Reduce cost delay for SSD storage (default 2ms -- designed for HDD)
autovacuum_vacuum_cost_delay = 2ms
# Force vacuum of frozen transactions on large tables
autovacuum_freeze_max_age = 200000000 # Default; rarely needs changing
PgBouncer is the standard connection pooler for PostgreSQL. It reduces the number of real PostgreSQL processes (each consuming 5-10MB of memory) while supporting a much larger number of application connections. On a server with 16GB of RAM, running 200 direct PostgreSQL connections consumes 1-2GB purely on connection overhead; with PgBouncer in transaction mode, the same application traffic can be served by 20-30 real PostgreSQL connections.
PgBouncer installation on Ubuntu/Debian:
sudo apt-get install pgbouncer
# Configuration file location:
# /etc/pgbouncer/pgbouncer.ini
# User authentication file:
# /etc/pgbouncer/userlist.txt
# Generate password hash for userlist.txt
# Using md5 method (PostgreSQL legacy):
echo -n "passwordusername" | md5sum
# Prefix result with md5:
# userlist.txt entry: "username" "md5"
# Using SCRAM-SHA-256 (PostgreSQL 14+ recommended):
# Copy hash directly from pg_shadow:
psql -c "SELECT usename, passwd FROM pg_shadow WHERE usename = 'myapp';"
pgbouncer.ini configuration for UK production:
[databases]
; Database alias = real connection parameters
myapp = host=127.0.0.1 port=5432 dbname=myapp_production
; Read replica routing (optional)
myapp_read = host=10.0.0.2 port=5432 dbname=myapp_production
[pgbouncer]
; Network settings
listen_port = 6432
listen_addr = 127.0.0.1 ; Only listen on loopback; application on same server
; listen_addr = 0.0.0.0 ; If PgBouncer is on a separate proxy server
; Authentication
auth_type = scram-sha-256 ; Use scram-sha-256 for PostgreSQL 14+
auth_file = /etc/pgbouncer/userlist.txt
; Pooling mode
; session: 1 server connection per client session (max compatibility)
; transaction: 1 server connection per transaction (recommended for most apps)
; statement: 1 server connection per SQL statement (only for autocommit apps)
pool_mode = transaction
; Connection limits
max_client_conn = 1000 ; Maximum application connections to PgBouncer
default_pool_size = 25 ; Real PostgreSQL connections per database/user pair
min_pool_size = 5 ; Minimum connections kept warm
reserve_pool_size = 5 ; Extra connections for emergency overflow
reserve_pool_timeout = 3.0 ; Wait time before using reserve pool
; Connection lifecycle
server_idle_timeout = 600 ; Close idle server connections after 10 minutes
client_idle_timeout = 600 ; Close idle client connections after 10 minutes
server_lifetime = 3600 ; Recycle server connections after 1 hour
server_connect_timeout = 5 ; Fail connection attempt after 5 seconds
; Logging
log_connections = 0 ; Avoid log spam in high-traffic environments
log_disconnections = 0
stats_period = 60 ; Log statistics every 60 seconds
; Admin access
admin_users = pgbouncer_admin
stats_users = monitoring_user
Application connection strings with PgBouncer:
# Application connects to PgBouncer on port 6432, not PostgreSQL directly
# Django settings.py
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql',
'HOST': '127.0.0.1',
'PORT': '6432', # PgBouncer port, not 5432
'NAME': 'myapp',
'USER': 'myapp_user',
'PASSWORD': 'password',
'CONN_MAX_AGE': 0, # Important: set to 0 with transaction-mode PgBouncer
}
}
# Laravel .env
DB_HOST=127.0.0.1
DB_PORT=6432 # PgBouncer port
DB_DATABASE=myapp
DB_USERNAME=myapp_user
DB_PASSWORD=password
# Note: Disable server-side prepared statements when using transaction-mode PgBouncer
# These are incompatible with transaction-mode pooling
# In Django: use DISABLE_SERVER_SIDE_CURSORS = True
# In Rails: use prepared_statements: false in database.yml
Monitoring PgBouncer:
-- Connect to PgBouncer admin console
psql -p 6432 -U pgbouncer_admin pgbouncer
-- Show pool statistics
SHOW POOLS;
-- cl_active: clients with active server connections
-- cl_waiting: clients waiting for a free connection
-- sv_active: server connections in use
-- sv_idle: server connections idle in pool
-- If cl_waiting is consistently > 0, increase default_pool_size
-- Show client connections
SHOW CLIENTS;
-- Show overall statistics
SHOW STATS;
-- total_query_count: queries processed
-- avg_query_time: average query duration in microseconds
-- Reload config without restart
RELOAD;
For the full picture of PostgreSQL tuning in context with MySQL and MSSQL, see our database performance tuning guide.
The four most impactful PostgreSQL configuration settings are: shared_buffers (set to 25% of server RAM), work_mem (set to 32-64MB for OLTP, higher for analytics), effective_cache_size (set to 75% of server RAM, used by the query planner), and random_page_cost (set to 1.1 for SSD storage, versus the default 4.0 which assumes HDD). These four settings control memory allocation and storage cost assumptions. Correct values reduce query times by 30-60% on a default PostgreSQL installation on modern hardware.
Use GIN for searches within composite values: full-text search (tsvector), JSONB containment queries (@>), and array overlap (&&). GIN is faster to search than GiST for these patterns. Use GiST for spatial and geographic queries (PostGIS), range type overlap, and when you need nearest-neighbour search capability (which GIN does not support). GiST also handles full-text search if you need distance ranking (similarity scoring), whereas GIN does not support GiST's nearest-neighbour operators. For standard full-text search on large text columns, GIN is the correct choice.
Table bloat occurs when dead tuples (old versions of updated or deleted rows) accumulate faster than autovacuum removes them. A bloated table is physically larger than the live data it contains -- PostgreSQL must read and skip dead rows during table scans. A table with 1 million live rows and 3 million dead rows requires reading 4 million row slots to complete a sequential scan. Indexes also bloat over time, consuming more pages and slowing index scans. Correctly configured autovacuum prevents bloat from accumulating on high-write tables.
PostgreSQL creates a new OS process for each connection, consuming 5-10MB of memory per connection. On a 16GB server, 200 direct connections consume 1-2GB purely on connection overhead before any queries run. PgBouncer in transaction mode multiplexes 1,000+ application connections through 25-50 real PostgreSQL connections, reducing memory overhead by 20-40x. Additionally, establishing a new PostgreSQL connection takes 10-50ms. PgBouncer reuses existing connections, eliminating this overhead for every request. For any Python, Ruby, PHP or Node.js application generating more than 20 concurrent database requests, PgBouncer is not optional -- it is a required component.
In EXPLAIN ANALYZE output, look for Seq Scan on table_name (filter: ...) with large actual rows and Rows Removed by Filter counts. If a node shows Seq Scan on a table of more than 10,000 rows, with a Filter clause matching your WHERE condition, and the query is slow, a missing index is almost certainly the cause. Check the actual rows output -- if rows removed by filter is 10x higher than rows returned, you are scanning the entire table to find a small fraction of matching rows. Add a B-tree index on the filtered column(s) and re-run EXPLAIN ANALYZE to confirm the plan changes to an Index Scan.
PostgreSQL performance tuning for UK businesses rests on five specific foundations: deploy PgBouncer in transaction mode from day one (not as an afterthought); set shared_buffers to 25% of RAM and work_mem to 32-64MB; choose the correct index type for each column's query pattern (GIN for JSONB and full-text, BRIN for time-ordered append-only tables, B-tree for everything else); configure per-table autovacuum settings for high-write tables rather than relying on global defaults; and use EXPLAIN ANALYZE with BUFFERS to diagnose slow queries before adding indexes. A PostgreSQL database configured with these five principles on appropriate hardware outperforms a default-configured PostgreSQL database of equal specification by 50-80% on typical UK SaaS and e-commerce workloads.
Softomate Solutions provides PostgreSQL performance tuning from Stanmore, London. Contact us for a database assessment.
Written by the Softomate Solutions technical team, database and software specialists based in Stanmore, London.
More from the blog
Let us help
Talk to our London-based team about how we can build the AI software, automation, or bespoke development tailored to your needs.
Softomate Solutions Limited
Deen Dayal Yadav
Online
