PostgreSQL is the world's most advanced open-source relational database, and the one we recommend by default at Alternetica for the vast majority of projects. But having PostgreSQL does not guarantee having performance. We have audited dozens of applications in LATAM with 5–10 second response times that, after optimizations, dropped to milliseconds. These are the 10 techniques that have the most impact.
1. Use EXPLAIN ANALYZE before optimizing
Before any optimization, you need to understand what is actually happening. EXPLAIN ANALYZE executes the query and shows the execution plan with real timing.
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as total_orders
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.active = true
GROUP BY u.id, u.name
ORDER BY total_orders DESC
LIMIT 20;
The most important lines to look for:
- Seq Scan: Sequential scan, a signal that an index is missing
- Hash Join vs Nested Loop: Hash Join is generally better for large tables
- cost=X..Y rows=Z: The planner's estimate
- actual time=X..Y rows=Z: The real time
If the estimated "rows" differs greatly from the real "rows," the statistics are outdated. Run ANALYZE table to update them.
2. Correct indexes for frequent queries
The right index can turn a 30-second Seq Scan into a 1ms Index Scan.
-- Frequent query: find orders by user and status
SELECT * FROM orders
WHERE user_id = 123 AND status = 'pending'
ORDER BY created_at DESC;
-- Composite index in the correct order (most selective column first)
CREATE INDEX idx_orders_user_status_date
ON orders (user_id, status, created_at DESC);
Golden rule: The order of columns in the index matters. Columns that appear in equality conditions go first; those for ranges or sorting go last.
3. Covering indexes
A covering index includes all the columns a query needs, eliminating the need to access the main table.
-- Without covering index: PostgreSQL does Index Scan + Heap Fetch
SELECT name, email FROM users WHERE company_id = 5;
-- With covering index: Index-Only Scan
CREATE INDEX idx_users_company_covering
ON users (company_id) INCLUDE (name, email);
The performance difference can be 3–10x on large tables because it avoids additional disk accesses.
4. Partial indexes for data subsets
If you frequently query only a fraction of the data, a partial index is more efficient.
-- 95% of queries are on active orders
-- Create index only for that subset
CREATE INDEX idx_active_orders_date
ON orders (created_at DESC)
WHERE status IN ('pending', 'processing');
-- This index is much smaller than one over the whole table
-- and faster for frequent queries
5. Eliminate the N+1 problem with JOINs or subqueries
The N+1 problem is the silent killer of performance: you load 100 records and then make 100 additional queries to get related information.
-- BAD: N+1 in the application
-- First: SELECT * FROM users WHERE company_id = 5 (1 query)
-- Then: SELECT * FROM orders WHERE user_id = ? x100 (100 queries)
-- GOOD: Everything in one query with JOIN
SELECT
u.id,
u.name,
u.email,
COUNT(o.id) FILTER (WHERE o.status = 'completed') AS completed_orders,
SUM(o.total) FILTER (WHERE o.status = 'completed') AS total_sales
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.company_id = 5
GROUP BY u.id, u.name, u.email;
In ORMs like Prisma or Sequelize, look for include/eager loading options to avoid N+1.
6. Connection pooling with PgBouncer
Each PostgreSQL connection consumes memory (typically 5–10MB). If you have 100 application instances each opening 10 connections, that is 1,000 connections and potentially 10GB of RAM just for connections.
PgBouncer acts as a proxy, maintaining a real connection pool to PostgreSQL and multiplexing application connections.
# Basic pgbouncer.ini
[databases]
my_app = host=localhost dbname=my_app
[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
With pool_mode = transaction, a real connection is shared between multiple application connections, sharing the pool across transactions. For most web applications, 20–30 real connections can serve hundreds of application connections.
7. Partitioning large tables
For tables that grow constantly (logs, events, transactions), partitioning allows queries to access only relevant partitions.
-- Events table partitioned by month
CREATE TABLE events (
id BIGSERIAL,
user_id INT NOT NULL,
type VARCHAR(100) NOT NULL,
data JSONB,
created_at TIMESTAMP NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);
-- Create partitions (automatable with pg_partman)
CREATE TABLE events_2025_01
PARTITION OF events
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE events_2025_02
PARTITION OF events
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
-- A query that filters by date only scans the relevant partition
SELECT * FROM events
WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01'
AND user_id = 456;
8. JSONB correctly indexed
JSONB is one of PostgreSQL's most powerful features, but without proper indexes it can become a bottleneck.
-- GIN index for searches within JSONB
CREATE INDEX idx_metadata_gin ON products USING GIN (metadata);
-- Specific index for a frequently queried field
CREATE INDEX idx_metadata_category
ON products ((metadata->>'category'));
-- Efficient query
SELECT name, price
FROM products
WHERE metadata->>'category' = 'electronics'
AND metadata @> '{"active": true}';
Avoid using JSONB as a substitute for typed columns for data you will always need to filter. The overhead of JSON vs. native types is real for high-cardinality columns.
9. VACUUM and AUTOVACUUM strategy
PostgreSQL uses MVCC (Multi-Version Concurrency Control): when you update a record, the old record is not immediately removed. VACUUM cleans up those dead records and prevents "table bloat."
-- Check the autovacuum status on your tables
SELECT
schemaname,
tablename,
n_dead_tup,
n_live_tup,
last_autovacuum,
last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;
-- For tables with heavy write activity, tune autovacuum
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.01, -- vacuum at 1% dead rows
autovacuum_analyze_scale_factor = 0.005
);
A table with millions of dead records has degraded performance even with perfect indexes.
10. pg_stat_statements for continuous monitoring
pg_stat_statements records statistics for all executed queries. It is the most valuable tool for identifying problematic queries in production.
-- Enable the extension (requires PostgreSQL restart)
-- In postgresql.conf: shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- The 10 queries that consume the most total time
SELECT
round(mean_exec_time::numeric, 2) AS avg_ms,
calls,
round(total_exec_time::numeric, 2) AS total_ms,
substring(query, 1, 100) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
This query will show you exactly where the real time is in your production database. Surprises are frequent.
Complementary tools
- pganalyze: Continuous monitoring with performance regression alerts
- PgHero: Visual dashboard for PostgreSQL statistics
- pg_partman: Partitioning automation
- pgBadger: PostgreSQL log analysis
Conclusion: performance is a process, not a task
Optimizing PostgreSQL is not something done once. Queries change, data volume grows, access patterns evolve. What matters is having the observability instruments (pg_stat_statements, slow query logging) to detect problems before they affect users.
If your application has database performance problems you have not been able to resolve, at Alternetica we perform PostgreSQL performance audits. Contact us and we will analyze your specific case.