PostgresSQL-30-ways-to-optimize-4

PostgreSQL is one of the most powerful open-source databases, but out-of-the-box settings are not optimized for production workloads. True performance comes from design + query + configuration + operations working together.

This article explains 30 practical optimization techniques, grouped logically and explained in depth.

🔹 CATEGORY 1: DATABASE & SCHEMA DESIGN (Foundation)

1️⃣ Choose Correct Data Types

Using wrong data types wastes memory and CPU.

Bad

age VARCHAR(10)

Good

age SMALLINT

✔ Smaller data types = less I/O
✔ Faster index scans

2️⃣ Avoid Overusing TEXT and VARCHAR

Use fixed-size types where possible.

Use:

Only use TEXT when length is truly unknown.

3️⃣ Normalize First, Denormalize When Needed

Example:

4️⃣ Use Proper Primary Keys

Always define a PRIMARY KEY.

ALTER TABLE users ADD PRIMARY KEY (id);

Benefits:

5️⃣ Use UUID Carefully

UUIDs are random → bad for index locality.

Better options:

🔹 CATEGORY 2: INDEXING STRATEGY (Most Important)

6️⃣ Create Indexes Based on Queries (Not Columns)

Do not blindly index every column.

Index what appears in:

7️⃣ Use Composite Indexes Correctly

Order matters.

CREATE INDEX idx_orders_user_date
ON orders(user_id, created_at);

✔ Works for:

❌ Not for created_at alone

8️⃣ Avoid Over-Indexing

Each index:

Rule:

More reads → more indexes
More writes → fewer indexes

9️⃣ Use Partial Indexes

Index only required rows.

CREATE INDEX idx_active_users
ON users(last_login)
WHERE is_active = true;

✔ Smaller index
✔ Faster scans

🔟 Use Covering Indexes (INCLUDE)

Avoid table lookups.

CREATE INDEX idx_orders_cover
ON orders(user_id)
INCLUDE (total_amount, status);

Result: Index-only scans

🔹 CATEGORY 3: QUERY OPTIMIZATION

1️⃣1️⃣ Always Use EXPLAIN ANALYZE

Never guess performance.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 10;

Look for:

1️⃣2️⃣ Avoid SELECT *

Fetch only required columns.

Bad

SELECT * FROM users;

Good

SELECT id, name FROM users;

✔ Less memory
✔ Faster execution

1️⃣3️⃣ Replace Subqueries with Joins

Subqueries can execute repeatedly.

Bad

SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users);

Good

SELECT o.*
FROM orders o
JOIN users u ON o.user_id = u.id;

1️⃣4️⃣ Use EXISTS Instead of IN (Large Data)

EXISTS stops after first match.

SELECT *
FROM orders o
WHERE EXISTS (
  SELECT 1 FROM users u WHERE u.id = o.user_id
);

1️⃣5️⃣ Avoid Functions in WHERE Clause

Functions prevent index usage.

Bad

WHERE LOWER(email) = '[email protected]'

Good

WHERE email = '[email protected]'

Or use functional index.

🔹 CATEGORY 4: VACUUM, ANALYZE & BLOAT

1️⃣6️⃣ Understand MVCC & Dead Tuples

PostgreSQL doesn’t overwrite rows—it creates new versions.

Result:

1️⃣7️⃣ Configure Autovacuum Properly

Default settings are conservative.

Tune:

autovacuum_vacuum_scale_factor
autovacuum_analyze_scale_factor

Critical for:

1️⃣8️⃣ Use Manual VACUUM When Needed

VACUUM (VERBOSE, ANALYZE) orders;

Use during:

1️⃣9️⃣ Use VACUUM FULL Carefully

Reclaims disk space but:

Use only during maintenance windows.

🔹 CATEGORY 5: CONFIGURATION & MEMORY TUNING

2️⃣0️⃣ Tune shared_buffers

Controls memory used for caching.

Rule of thumb:

shared_buffers = 8GB

2️⃣1️⃣ Tune work_mem

Used for sorting and joins.

work_mem = 64MB

Too low → disk spill
Too high → memory exhaustion

2️⃣2️⃣ Tune effective_cache_size

Helps query planner.

effective_cache_size = 24GB

Set to:

🔹 CATEGORY 6: PARTITIONING & DATA MANAGEMENT

2️⃣3️⃣ Use Table Partitioning for Large Tables

Best for:

PARTITION BY RANGE (created_at);

Benefits:

2️⃣4️⃣ Archive Old Data

Do not keep years of unused data in hot tables.

Move to:

2️⃣5️⃣ Use Materialized Views

Precompute heavy queries.

CREATE MATERIALIZED VIEW sales_summary AS ...

Refresh periodically.

🔹 CATEGORY 7: CONNECTIONS & CONCURRENCY

2️⃣6️⃣ Use Connection Pooling (PgBouncer)

PostgreSQL doesn’t scale well with many connections.

✔ PgBouncer
✔ PgPool-II

2️⃣7️⃣ Reduce Long-Running Transactions

They:

Avoid:

2️⃣8️⃣ Use Proper Isolation Levels

Default is usually enough.

Avoid:

SERIALIZABLE

unless truly required.

🔹 CATEGORY 8: MONITORING & OPERATIONS

2️⃣9️⃣ Monitor Slow Queries

Enable:

log_min_duration_statement = 500ms

Use tools:

3️⃣0️⃣ Regularly Review Index & Query Usage

Periodically check:

Optimization is continuous, not one-time.

✅ Final Thoughts

PostgreSQL optimization is not just tuning parameters. It is a holistic process involving:

✔ Schema design
✔ Query writing
✔ Index strategy
✔ Memory tuning
✔ Maintenance
✔ Monitoring

Great PostgreSQL performance is designed, not fixed later.