Master PostgreSQL performance tuning with indexing strategies, query optimization, connection pooling, partitioning, and monitoring. Learn to handle millions of rows efficiently.
# PostgreSQL Performance Optimization Guide
Optimize PostgreSQL for high-performance applications handling millions of rows with proper indexing, query optimization, and database tuning.
## Indexing Strategies
### Index Types and Usage
```sql
-- B-tree index (default, best for equality and range queries)
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_created ON orders(created_at DESC);
-- Partial index (smaller, faster for filtered queries)
CREATE INDEX idx_active_users ON users(email)
WHERE status = 'active';
CREATE INDEX idx_recent_orders ON orders(user_id, created_at)
WHERE created_at > NOW() - INTERVAL '30 days';
-- Composite index (order matters!)
CREATE INDEX idx_orders_user_status ON orders(user_id, status, created_at DESC);
-- Covering index (includes all needed columns)
CREATE INDEX idx_products_search ON products(category_id, price)
INCLUDE (name, description);
-- GIN index for JSONB and arrays
CREATE INDEX idx_products_metadata ON products USING GIN(metadata);
CREATE INDEX idx_posts_tags ON posts USING GIN(tags);
-- GiST index for geometric and full-text search
CREATE INDEX idx_locations_coords ON locations USING GIST(coordinates);
CREATE INDEX idx_articles_search ON articles USING GIN(to_tsvector('english', title || ' ' || content));
-- BRIN index (block range, excellent for time-series data)
CREATE INDEX idx_events_timestamp ON events USING BRIN(timestamp);
```
### Index Analysis
```sql
-- Find unused indexes
SELECT
schemaname || '.' || relname AS table,
indexrelname AS index,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS size,
idx_scan AS scans
FROM pg_stat_user_indexes i
JOIN pg_index USING (indexrelid)
WHERE idx_scan < 50
AND NOT indisunique
ORDER BY pg_relation_size(i.indexrelid) DESC;
-- Find missing indexes
SELECT
relname AS table,
seq_scan,
seq_tup_read,
idx_scan,
n_live_tup AS estimated_rows
FROM pg_stat_user_tables
WHERE seq_scan > 0
AND n_live_tup > 10000
ORDER BY seq_tup_read DESC
LIMIT 20;
```
## Query Optimization
### EXPLAIN ANALYZE
```sql
-- Analyze query execution
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id
ORDER BY order_count DESC
LIMIT 100;
-- Key metrics to watch:
-- - Seq Scan on large tables (needs index)
-- - High "rows removed by filter" (needs partial index)
-- - Nested Loop with high row counts (consider hash join)
-- - Sort with high memory (increase work_mem)
```
### Common Query Patterns
```sql
-- Pagination with keyset (faster than OFFSET)
SELECT * FROM posts
WHERE (created_at, id) < ($last_created_at, $last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- Efficient COUNT for large tables
SELECT reltuples::bigint AS estimate
FROM pg_class
WHERE relname = 'orders';
-- Or use COUNT with covering index
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- Batch updates (avoid locking entire table)
WITH batch AS (
SELECT id FROM orders
WHERE status = 'processing'
AND updated_at < NOW() - INTERVAL '1 hour'
LIMIT 1000
FOR UPDATE SKIP LOCKED
)
UPDATE orders SET status = 'failed'
WHERE id IN (SELECT id FROM batch);
-- Upsert with conflict handling
INSERT INTO user_stats (user_id, views, updated_at)
VALUES ($1, 1, NOW())
ON CONFLICT (user_id) DO UPDATE SET
views = user_stats.views + 1,
updated_at = NOW();
```
## Connection Pooling
### PgBouncer Configuration
```ini
; pgbouncer.ini
[databases]
myapp = host=localhost port=5432 dbname=myapp
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
; Pool settings
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
; Connection limits
max_db_connections = 50
max_user_connections = 50
; Timeouts
server_idle_timeout = 600
client_idle_timeout = 0
query_timeout = 30
```
### Prisma with Connection Pooling
```typescript
// lib/db.ts
import { PrismaClient } from "@prisma/client";
const globalForPrisma = globalThis as unknown as {
prisma: PrismaClient | undefined;
};
export const prisma = globalForPrisma.prisma ?? new PrismaClient({
datasources: {
db: {
url: process.env.DATABASE_URL + "?connection_limit=10&pool_timeout=30",
},
},
log: process.env.NODE_ENV === "development"
? ["query", "error", "warn"]
: ["error"],
});
if (process.env.NODE_ENV !== "production") {
globalForPrisma.prisma = prisma;
}
```
## Table Partitioning
### Range Partitioning for Time-Series
```sql
-- Create partitioned table
CREATE TABLE events (
id BIGSERIAL,
event_type VARCHAR(50),
payload JSONB,
created_at TIMESTAMPTZ NOT NULL,
PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);
-- Create monthly partitions
CREATE TABLE events_2024_01 PARTITION OF events
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE events_2024_02 PARTITION OF events
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
-- Auto-create partitions with pg_partman
CREATE EXTENSION pg_partman;
SELECT partman.create_parent(
p_parent_table := 'public.events',
p_control := 'created_at',
p_type := 'native',
p_interval := 'monthly',
p_premake := 3
);
```
## Performance Tuning
### postgresql.conf Settings
```ini
# Memory (adjust based on available RAM)
shared_buffers = 4GB # 25% of RAM
effective_cache_size = 12GB # 75% of RAM
work_mem = 256MB # Per operation memory
maintenance_work_mem = 1GB # For VACUUM, CREATE INDEX
# Parallelism
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4
# Write Ahead Log
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB
min_wal_size = 1GB
# Query Planner
random_page_cost = 1.1 # For SSD storage
effective_io_concurrency = 200 # For SSD storage
default_statistics_target = 100
```
## Monitoring Queries
```sql
-- Active queries and locks
SELECT pid, age(clock_timestamp(), query_start), usename, query, state
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;
-- Table bloat estimation
SELECT
schemaname, tablename,
pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as total_size,
n_dead_tup,
n_live_tup,
round(n_dead_tup * 100.0 / nullif(n_live_tup, 0), 2) as dead_ratio
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;
```
This PostgreSQL optimization guide covers indexing, query tuning, connection pooling, partitioning, and monitoring for production-scale databases.This postgresql prompt is ideal for developers working on:
By using this prompt, you can save hours of manual coding and ensure best practices are followed from the start. It's particularly valuable for teams looking to maintain consistency across their postgresql implementations.
Yes! All prompts on Antigravity AI Directory are free to use for both personal and commercial projects. No attribution required, though it's always appreciated.
This prompt works excellently with Claude, ChatGPT, Cursor, GitHub Copilot, and other modern AI coding assistants. For best results, use models with large context windows.
You can modify the prompt by adding specific requirements, constraints, or preferences. For postgresql projects, consider mentioning your framework version, coding style, and any specific libraries you're using.