PostgreSQL is powerful enough to handle demanding production workloads, but large-scale applications require more than simply installing PostgreSQL and increasing server resources.
As data volume, traffic, concurrent users, and query complexity increase, database performance can become a major factor in application reliability.
A typical large-scale architecture may look like:
Users
↓
Application Servers
↓
Connection Pool
↓
PostgreSQL
├── Primary
├── Read Replicas
└── Backup / Recovery
Optimizing PostgreSQL involves improving several layers:
- SQL queries
- Indexes
- Database schema
- Connection pooling
- Transactions
- Memory
- Storage
- Parallelism
- Replication
- Partitioning
- Monitoring
- Maintenance
- Application architecture
In this guide, we’ll explore practical PostgreSQL optimization techniques for large-scale applications.
Why PostgreSQL Optimization Matters
A small application may work perfectly with:
10,000 rows
100 users
Simple queries
But a large application might have:
Millions or billions of rows
Thousands of concurrent users
Complex queries
High write volume
Heavy reporting workloads
Multiple application servers
At this scale, inefficient database operations can cause:
Slow queries
↓
Higher CPU usage
↓
Connection saturation
↓
Increased response times
↓
Application timeouts
The goal of PostgreSQL optimization is not simply to make individual queries faster.
You want the entire database system to remain predictable and reliable as workload increases.
1. Start by Measuring Performance
The first rule of database optimization is:
Measure before changing.
Don’t immediately increase memory, add indexes, or modify PostgreSQL configuration.
First identify the actual bottleneck.
Look at:
- Slow queries
- CPU usage
- Memory usage
- Disk I/O
- Connection count
- Lock contention
- Cache hit ratio
- Replication lag
- Table and index growth
- Transaction duration
Optimization should be driven by real workload data.
2. Use EXPLAIN
PostgreSQL provides EXPLAIN to show how it plans to execute a query.
For example:
EXPLAIN
SELECT *
FROM users
WHERE email = 'oliver@example.com';
For actual execution information:
EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email = 'oliver@example.com';
You may see operations such as:
Seq Scan
Index Scan
Bitmap Heap Scan
Nested Loop
Hash Join
Merge Join
Understanding the execution plan is one of the most important PostgreSQL performance skills.
3. Understand Sequential Scans
A sequential scan means PostgreSQL reads rows from a table to find matching records.
For example:
SELECT *
FROM users
WHERE email = 'oliver@example.com';
If PostgreSQL performs:
Seq Scan on users
it may need to inspect many rows.
A sequential scan isn’t automatically bad.
For a small table, scanning the whole table can be faster than using an index.
The important question is:
Is the chosen execution plan appropriate for the size and workload?
4. Create Appropriate Indexes
Indexes are one of the most important PostgreSQL optimization tools.
Suppose you frequently search by email:
SELECT *
FROM users
WHERE email = 'oliver@example.com';
You can create:
CREATE INDEX idx_users_email
ON users(email);
PostgreSQL may then use an index-based access method.
5. Don’t Index Every Column
Indexes improve certain reads but come with costs.
Every index:
- Consumes disk space
- Requires maintenance
- Makes some writes more expensive
- Must be updated when indexed data changes
Avoid creating indexes on every column simply because indexes are useful.
Instead, create indexes based on actual query patterns.
6. Composite Indexes
Suppose your application frequently executes:
SELECT *
FROM orders
WHERE user_id = 100
AND status = 'completed';
A composite index may be useful:
CREATE INDEX idx_orders_user_status
ON orders(user_id, status);
Column order matters.
For an index:
(user_id, status)
queries filtering by user_id can generally benefit from the leading part of the index.
You should design composite indexes based on the application’s most important query patterns.
7. Partial Indexes
A partial index contains only rows matching a condition.
Suppose you frequently query active users:
SELECT *
FROM users
WHERE status = 'active';
You could create:
CREATE INDEX idx_active_users
ON users(id)
WHERE status = 'active';
This can be useful when the indexed subset is significantly smaller than the complete table.
8. Expression Indexes
PostgreSQL allows indexes on expressions.
For example:
CREATE INDEX idx_users_lower_email
ON users(LOWER(email));
Now a query such as:
SELECT *
FROM users
WHERE LOWER(email) = 'oliver@example.com';
can potentially use the expression index.
Expression indexes are useful when queries consistently apply the same expression.
9. Avoid Functions That Prevent Useful Index Usage
Suppose you have:
SELECT *
FROM users
WHERE LOWER(email) = 'oliver@example.com';
Without an appropriate expression index, PostgreSQL may not be able to use a normal index on email effectively for that expression.
Instead, you can either adjust the query/data model or create:
CREATE INDEX idx_users_lower_email
ON users(LOWER(email));
Always verify with EXPLAIN.
10. Avoid SELECT *
Instead of:
SELECT *
FROM users;
select only what you need:
SELECT
id,
name,
email
FROM users;
This can reduce:
- Data transferred
- Memory usage
- Network traffic
- Application-side processing
It also makes queries more explicit.
11. Limit the Number of Rows
If you only need a small number of records, don’t retrieve thousands.
Instead of:
SELECT *
FROM orders
ORDER BY created_at DESC;
use:
SELECT
id,
total,
created_at
FROM orders
ORDER BY created_at DESC
LIMIT 50;
This is especially important for API endpoints.
12. Use Efficient Pagination
A common approach is:
SELECT *
FROM users
ORDER BY id
LIMIT 50
OFFSET 50000;
As the offset becomes large, PostgreSQL may need to process and discard many rows.
For large datasets, keyset pagination can often be more efficient.
For example:
SELECT *
FROM users
WHERE id > $1
ORDER BY id
LIMIT 50;
Here $1 is the last ID from the previous page.
This works particularly well when the pagination column is indexed.
13. Optimize ORDER BY
Sorting large datasets can be expensive.
For example:
SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 50;
If this is a frequent query, an index that supports the access pattern may help:
CREATE INDEX idx_orders_created_at
ON orders(created_at DESC);
Whether PostgreSQL uses it depends on the query and cost estimates.
Always verify with EXPLAIN.
14. Optimize JOINs
Large applications often require queries across multiple tables.
For example:
SELECT
u.name,
o.total
FROM users u
JOIN orders o
ON u.id = o.user_id;
Make sure important join columns are appropriately indexed.
For example:
CREATE INDEX idx_orders_user_id
ON orders(user_id);
Primary keys are already indexed, but foreign-key columns aren’t automatically indexed just because they reference a primary key.
15. Avoid N+1 Queries
The N+1 query problem occurs when an application makes one query to retrieve a collection and then makes another query for each individual item.
For example:
1 query → Get 1,000 users
Then:
1 query → Orders for user 1
1 query → Orders for user 2
...
1 query → Orders for user 1,000
That can produce:
1,001 queries
Instead, consider:
- JOINs
- Batch queries
- Aggregations
- Carefully designed APIs
- Data loaders
- Appropriate caching
Reducing unnecessary database round trips can have a significant impact on application performance.
16. Use Connection Pooling
Opening a new PostgreSQL connection for every request is expensive.
Large applications should use connection pooling.
Conceptually:
Application
↓
Connection Pool
┌──┼──┐
↓ ↓ ↓
C1 C2 C3
└──┼──┘
↓
PostgreSQL
The pool reuses existing database connections.
Popular application frameworks and PostgreSQL clients provide connection-pooling capabilities.
17. Don’t Make the Connection Pool Too Large
A common misconception is:
More connections = more performance.
That’s not necessarily true.
Suppose you have:
20 application servers
and each creates:
100 database connections
You could potentially have:
20 × 100 = 2,000 connections
That may overwhelm PostgreSQL and cause excessive resource contention.
Pool size should be designed around:
- Query workload
- PostgreSQL capacity
- Number of application instances
- CPU
- Memory
- Connection limits
18. Use an External Pooler When Appropriate
For very large deployments, an external connection pooler such as PgBouncer can be useful.
A simplified architecture:
Application Servers
↓
PgBouncer
↓
PostgreSQL
This can help manage large numbers of application-side connections and reduce connection overhead.
The correct pooling mode and configuration depend on application behavior, especially around transactions and session state.
19. Keep Transactions Short
Long-running transactions can cause several problems.
They may:
- Hold locks
- Prevent cleanup of old row versions
- Increase table bloat
- Increase contention
- Affect vacuum progress
Prefer transactions that perform only the work that needs to be atomic.
Avoid:
BEGIN
↓
Slow external API call
↓
User interaction
↓
Large computation
↓
Database update
↓
COMMIT
Instead, keep the database transaction focused.
20. Use Transactions Correctly
For related operations:
BEGIN;
INSERT INTO orders (...);
INSERT INTO order_items (...);
UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = 10;
COMMIT;
If something fails:
ROLLBACK;
Transactions protect data consistency.
But they should not remain open longer than necessary.
21. Understand MVCC
PostgreSQL uses MVCC, or Multi-Version Concurrency Control.
Instead of overwriting a row immediately, PostgreSQL maintains row versions that allow transactions to work concurrently.
Conceptually:
Old Row Version
↓
New Row Version
MVCC helps PostgreSQL support concurrent reads and writes efficiently.
However, old row versions eventually need to be cleaned up.
That’s one reason vacuuming is important.
22. Understand VACUUM
When rows are updated or deleted, PostgreSQL can leave behind dead row versions.
VACUUM helps reclaim space for reuse and maintains database health.
PostgreSQL includes automatic vacuuming through autovacuum.
You can inspect vacuum-related statistics using system views such as:
SELECT
relname,
n_live_tup,
n_dead_tup,
last_autovacuum
FROM pg_stat_user_tables;
Autovacuum is critical for healthy PostgreSQL installations.
23. Don’t Disable Autovacuum Without a Strong Reason
Autovacuum helps with:
- Dead tuple cleanup
- Transaction ID maintenance
- Table health
- Statistics maintenance
Disabling it globally can cause serious long-term problems.
If a specific table needs different behavior, tune that table rather than blindly disabling autovacuum everywhere.
24. Analyze Tables
PostgreSQL’s query planner depends on statistics.
ANALYZE updates statistics used by the planner.
You can run:
ANALYZE users;
PostgreSQL’s autovacuum system also performs automatic analyze operations under appropriate conditions.
Accurate statistics help PostgreSQL make better decisions about:
- Index usage
- Join strategies
- Join order
- Scan methods
25. Keep Statistics Up to Date
If the planner has inaccurate statistics, it may estimate row counts incorrectly.
For example:
Estimated rows: 100
Actual rows: 1,000,000
This can lead to a poor query plan.
When diagnosing performance problems, compare estimated and actual rows in:
EXPLAIN ANALYZE
Large differences can be a sign that statistics or data distribution need attention.
26. Tune work_mem Carefully
work_mem controls the amount of memory available for certain operations before PostgreSQL may use temporary disk files.
Operations can include:
- Sorts
- Hash operations
- Some query execution steps
A larger value isn’t always better.
Importantly, work_mem can be consumed by multiple operations within a query and across concurrent sessions.
Setting it excessively high can cause memory pressure.
Tune it based on workload and concurrency rather than choosing a huge global value.
27. Configure shared_buffers Carefully
shared_buffers controls memory PostgreSQL allocates for its shared buffer cache.
It is an important configuration parameter.
However, optimal values depend on:
- Available RAM
- Operating system
- Workload
- PostgreSQL version
- Other memory-consuming processes
Don’t blindly copy a configuration from another server.
Measure and tune based on your environment.
28. Use SSD Storage
Storage performance has a major impact on database workloads.
For large-scale applications, fast SSD or NVMe storage can significantly improve:
- Random reads
- Writes
- WAL operations
- Temporary files
- Checkpoints
Storage configuration should match the workload.
A database performing heavy random I/O needs different capacity planning from a mostly analytical workload.
29. Separate Database Workloads
Not every query has the same resource requirements.
You may have:
Transactional Queries
↓
Primary
Reporting Queries
↓
Read Replica
This prevents some heavy analytical queries from competing directly with transactional workloads.
For very large systems, dedicated analytics infrastructure may be more appropriate than running everything on the transactional database.
30. Use Read Replicas
Read replicas can distribute read workloads.
A simple architecture:
Application
/ \
↓ ↓
Writes Reads
↓ ↓
Primary Read Replicas
/ \
↓ ↓
Replica 1 Replica 2
Replicas can help with:
- Read scaling
- Reporting
- Disaster recovery
- Availability
But remember that asynchronous replicas can lag behind the primary.
31. Understand Replication Lag
If a user writes:
Create Order
↓
Primary
and immediately reads from a replica:
Get Order
↓
Replica
the new order might not yet be visible.
This is a consistency issue caused by replication lag.
Critical read-after-write operations may need to be routed to the primary.
32. Use Partitioning for Very Large Tables
If a table becomes extremely large, partitioning can help manage it.
For example, suppose you have:
events
with billions of rows.
You could partition by date:
events
├── events_2026_01
├── events_2026_02
├── events_2026_03
└── events_2026_04
PostgreSQL supports several partitioning strategies.
Common options include:
- Range partitioning
- List partitioning
- Hash partitioning
33. Range Partitioning Example
For event data:
CREATE TABLE events (
id BIGINT,
event_time TIMESTAMPTZ NOT NULL,
payload JSONB
) PARTITION BY RANGE (event_time);
Then:
CREATE TABLE events_2026_01
PARTITION OF events
FOR VALUES FROM ('2026-01-01')
TO ('2026-02-01');
Additional partitions can be created for subsequent periods.
Partitioning can make management and some queries more efficient, but it also adds complexity.
Don’t partition tables simply because they are large; use it when the workload and maintenance pattern justify it.
34. Use Partition Pruning
When a query filters on the partition key, PostgreSQL may be able to avoid scanning irrelevant partitions.
For example:
SELECT *
FROM events
WHERE event_time >= '2026-01-01'
AND event_time < '2026-02-01';
If the table is partitioned by event_time, PostgreSQL can potentially scan only the relevant partition.
This is called partition pruning.
35. Choose the Right Data Types
Data types affect storage and performance.
For example, don’t use:
TEXT
for everything simply because it is convenient.
Use appropriate types:
INTEGER / BIGINT
BOOLEAN
DATE
TIMESTAMPTZ
NUMERIC
UUID
JSONB
Choose based on the data and application requirements.
Smaller, appropriate types can reduce storage and memory usage.
36. Use TIMESTAMPTZ for Absolute Times
For application events such as:
created_at
updated_at
payment_time
login_time
TIMESTAMPTZ is often a good choice when you need to represent an absolute instant.
For example:
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
Keep timezone handling consistent across your application and database.
37. Use Constraints for Data Integrity
Constraints aren’t just about correctness.
They also provide a clear database model.
For example:
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
age INTEGER CHECK (age >= 18)
);
Here PostgreSQL guarantees:
id → Unique
email → Unique + Not Null
age → Must be >= 18
Data integrity should be enforced at the database level where appropriate, not only in application code.
38. Use Foreign Keys Carefully
Foreign keys protect relationships:
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL
REFERENCES users(id)
);
For large workloads, consider indexing frequently used foreign-key columns:
CREATE INDEX idx_orders_user_id
ON orders(user_id);
This can help joins and operations involving the relationship.
39. Optimize JSONB Usage
PostgreSQL’s JSONB is powerful, but don’t use it as a replacement for every relational column.
For frequently queried structured data, normal columns may be more appropriate.
For example:
Bad design:
user_id, name, email all inside JSONB
Better:
user_id
name
email
metadata JSONB
Use JSONB for flexible or semi-structured attributes where it makes sense.
40. Index JSONB Carefully
If you frequently query JSONB data, PostgreSQL supports specialized indexes such as GIN.
For example:
CREATE INDEX idx_products_metadata
ON products
USING GIN (metadata);
This can be useful for certain JSONB containment and related queries.
Don’t automatically add a GIN index to every JSONB column.
Measure the workload and query patterns first.
41. Use Full-Text Search When Appropriate
If your application needs text search, avoid repeatedly using:
WHERE description ILIKE '%postgresql%'
on a huge table without considering a suitable search strategy.
PostgreSQL provides full-text search capabilities.
For example, you can use:
to_tsvector()
and:
to_tsquery()
with appropriate indexing.
For very advanced search requirements, a dedicated search engine may be more appropriate.
42. Avoid Unnecessary Database Round Trips
Consider this application flow:
Request
↓
Query 1
↓
Query 2
↓
Query 3
↓
Query 4
↓
Response
Every database round trip has overhead.
Where appropriate, combine operations into efficient SQL queries, use transactions, batch operations, or redesign the data-access layer.
However, don’t combine unrelated operations into one giant query just for the sake of reducing query count.
Maintainability still matters.
43. Use Bulk Operations
If you’re inserting thousands of records, avoid making thousands of individual requests when a bulk operation is appropriate.
Instead of:
INSERT
INSERT
INSERT
INSERT
...
consider:
INSERT INTO users (name, email)
VALUES
('Oliver', 'oliver@example.com'),
('Sophia', 'sophia@example.com'),
('Ethan', 'ethan@example.com');
For very large ingestion workloads, PostgreSQL’s COPY mechanism can be significantly more efficient than individual INSERT statements.
44. Use Prepared Statements Appropriately
Parameterized queries provide safety and are important for preventing SQL injection.
For frequently executed statements, prepared statements can also be useful.
For example:
PREPARE find_user AS
SELECT *
FROM users
WHERE id = $1;
Application drivers can also provide prepared-statement functionality.
Don’t assume every query needs to be manually prepared; use the mechanisms provided by your PostgreSQL driver and workload.
45. Avoid SQL Injection
Never build SQL using untrusted user input.
Avoid:
const query = `
SELECT *
FROM users
WHERE email = '${email}'
`;
Use parameters instead:
const result = await pool.query(
`
SELECT *
FROM users
WHERE email = $1
`,[email]
);
This is essential for application security.
46. Monitor Active Queries
PostgreSQL provides useful system views such as:
SELECT
pid,
usename,
state,
query,
query_start
FROM pg_stat_activity;
This can help identify:
- Long-running queries
- Idle connections
- Active sessions
- Potentially problematic workloads
Be careful when exposing database monitoring information and ensure appropriate permissions.
47. Find Long-Running Queries
You can calculate query duration:
SELECT
pid,
now() - query_start AS duration,
query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;
Long-running queries aren’t automatically bad, but unexpected long-running queries deserve investigation.
48. Monitor Locks
Lock contention can significantly affect performance.
PostgreSQL provides system views for examining locks and sessions.
A simplified investigation can involve:
SELECT *
FROM pg_locks;
You may need to combine lock information with pg_stat_activity to understand which sessions are waiting and which are blocking others.
49. Monitor Database Size
As applications grow, storage management becomes increasingly important.
You can check database size:
SELECT
pg_size_pretty(
pg_database_size(current_database())
);
For table sizes:
SELECT
relname,
pg_size_pretty(
pg_total_relation_size(relid)
) AS size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;
This can help identify tables consuming significant storage.
50. Monitor Index Size
Indexes can also become large.
For example:
SELECT
indexrelname,
pg_size_pretty(
pg_relation_size(indexrelid)
) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;
This can help identify large indexes that may deserve investigation.
51. Identify Unused Indexes
PostgreSQL provides statistics that can help identify indexes with low usage.
For example:
SELECT
schemaname,
relname,
indexrelname,
idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan;
A low idx_scan count doesn’t automatically mean an index should be removed.
An index may be important for infrequent but critical queries.
Evaluate carefully before dropping indexes.
52. Use Query Statistics
The pg_stat_statements extension is extremely useful for query performance analysis.
It can help track information such as:
- Query execution count
- Total execution time
- Average execution time
- Rows returned
- Resource consumption
This allows you to identify expensive queries across the workload rather than optimizing queries based only on isolated examples.
53. Optimize the Most Expensive Queries First
Suppose your application has:
Query A → 5 ms
Query B → 8 ms
Query C → 2 seconds
Query D → 6 ms
Optimizing Query A from:
5 ms → 3 ms
is less important than fixing Query C:
2 seconds → 100 ms
Focus optimization efforts where they provide the largest real-world benefit.
54. Be Careful With COUNT(*)
Counting very large datasets can require significant work.
For example:
SELECT COUNT(*)
FROM orders;
If your application performs this frequently on a huge table, consider whether exact counts are actually necessary.
For dashboards, approximate counts, cached counts, or precomputed aggregates may sometimes be more appropriate.
Don’t sacrifice correctness when exact counts are required.
55. Use Caching Strategically
Not every request needs to hit PostgreSQL.
For frequently requested data, application-level caching can reduce database load.
For example:
Application
↓
Cache
↙ ↘
Hit Miss
↓ ↓
Data PostgreSQL
Caching technologies such as Redis can be useful for appropriate workloads.
However, caching introduces:
- Invalidation complexity
- Memory usage
- Consistency considerations
Cache data that is expensive to retrieve and safe to cache.
56. Cache Carefully
Suppose product information changes occasionally but is requested thousands of times per minute.
Caching may be useful.
But don’t blindly cache rapidly changing transactional data where stale values could cause incorrect behavior.
A useful question is:
Can this data safely be slightly stale?
If yes, caching may be a good candidate.
57. Separate Read and Write Workloads
At large scale, you may need different database paths.
For example:
Application
/ \
/ \
Write Path Read Path
↓ ↓
Primary Replicas
This is commonly called read/write splitting.
But the application needs to understand replica consistency and failover behavior.
58. Use Replication for High Availability
A production PostgreSQL architecture may include:
Primary
↓
Standby
↓
Failover
Replication helps reduce downtime and can support disaster recovery.
But replication alone isn’t enough.
You also need:
Monitoring
Failover Procedures
Backups
Recovery Testing
Application Routing
59. Don’t Treat Replicas as Backups
If data is accidentally deleted:
DELETE FROM users;
the deletion can also be replicated.
Therefore:
Replication ≠ Backup
Maintain independent backups and test recovery regularly.
60. Use Partitioning for Data Lifecycle Management
Partitioning can make it easier to manage old data.
For example:
events
├── 2024
├── 2025
└── 2026
Older partitions can potentially be archived or detached according to your retention strategy.
This can be much more efficient than deleting enormous numbers of old rows from one massive table.
61. Archive Old Data
Not every record needs to remain in the primary transactional workload forever.
A large application might use:
Hot Data
↓
PostgreSQL
Old Data
↓
Archive Storage
For example:
Current orders
→ Primary database
Historical analytics
→ Data warehouse / archive
Data retention policies can significantly reduce database growth.
62. Avoid Massive Deletes
Deleting millions of rows in a single transaction can create:
- Large WAL volume
- Long locks
- Large transaction overhead
- Significant vacuum work
- Replication pressure
Instead, when appropriate, delete data in controlled batches.
For example:
DELETE FROM events
WHERE event_time < NOW() - INTERVAL '2 years'
LIMIT ...
PostgreSQL does not support LIMIT directly on a plain DELETE in the same way some databases do, so batch deletion typically requires a CTE, key-based selection, or another controlled strategy.
For partitioned time-series data, dropping or detaching an old partition can be far more efficient than deleting individual rows.
63. Avoid Unnecessary Columns
Wide tables increase the amount of data PostgreSQL may need to process.
For example:
users
-------------------------
id
name
email
address
profile
preferences
large_metadata
...
If a query only needs:
SELECT id, name
FROM users;
don’t unnecessarily load large columns.
Good schema and query design can reduce I/O.
64. Use Covering Indexes Carefully
PostgreSQL supports indexes with included columns.
For example:
CREATE INDEX idx_users_email
ON users(email)
INCLUDE (name);
This can sometimes allow PostgreSQL to satisfy a query from the index without fetching the table row.
For example:
SELECT name
FROM users
WHERE email = 'oliver@example.com';
This can be useful for frequently executed queries.
However, included columns increase index size, so use them selectively.
65. Optimize Query Joins
For complex queries:
SELECT ...
FROM users u
JOIN orders o
ON u.id = o.user_id
JOIN payments p
ON o.id = p.order_id;
inspect:
EXPLAIN ANALYZE
Look for:
- Large sequential scans
- Unexpected join strategies
- Incorrect row estimates
- Large intermediate result sets
- Expensive sorts
- Hash operations spilling to disk
Don’t optimize JOIN syntax blindly.
Optimize the actual execution plan.
66. Avoid Returning Huge Result Sets
An API endpoint such as:
GET /api/users
shouldn’t return millions of rows.
Use:
Pagination
Filtering
Field selection
Limits
For example:
GET /api/users?page=1&limit=50
This protects both PostgreSQL and the application server.
67. Optimize API Queries Together With PostgreSQL
Database performance isn’t isolated from application design.
Consider:
Poor API
↓
Fetch 100,000 records
↓
Node.js filters them
↓
Return 50 records
A better approach is often:
API
↓
PostgreSQL
↓
Filter
↓
Sort
↓
Limit
↓
Return 50 records
Push appropriate filtering and aggregation into PostgreSQL instead of transferring unnecessary data to the application.
68. Use Appropriate Isolation Levels
PostgreSQL supports transaction isolation levels.
Common levels include:
Read Committed
Repeatable Read
Serializable
The default is typically Read Committed.
Higher isolation can provide stronger guarantees but may increase contention or transaction retries.
Choose the isolation level based on the application’s correctness requirements.
69. Avoid Unnecessary Serializable Transactions
Serializable transactions provide strong consistency guarantees, but they can experience serialization failures under contention.
Don’t use the strongest isolation level simply because it sounds safer.
Use the minimum level that correctly satisfies the business requirements.
If using Serializable transactions, applications need to be prepared to retry transactions when serialization failures occur.
70. Monitor Checkpoints and WAL
PostgreSQL uses WAL for durability and recovery.
Heavy write workloads can generate substantial WAL.
Monitor:
WAL generation
WAL retention
Checkpoint behavior
Replication lag
Disk usage
Excessive checkpoint pressure or WAL-related bottlenecks can affect performance.
Configuration should be tuned based on actual workload rather than copied from generic examples.
71. Use COPY for Large Imports
For large data ingestion, PostgreSQL’s COPY command is usually much more efficient than sending individual INSERT statements.
For example:
COPY users(name, email)
FROM '/path/users.csv'
WITH (FORMAT csv, HEADER true);
When loading data from an application, PostgreSQL client libraries can expose COPY functionality as well.
Always consider security and file-access permissions when using server-side COPY paths.
72. Avoid Unnecessary ORM Queries
ORMs such as Hibernate can simplify application development, but poorly designed ORM usage can produce inefficient SQL.
For example:
Entity
↓
Lazy relationship
↓
Another query
↓
Another relationship
↓
Another query
Monitor generated SQL.
For complex workloads, use:
- Projections
- Fetch joins
- Batch fetching
- Explicit queries
- Native SQL where appropriate
Use the abstraction where it helps, but understand the SQL underneath it.
73. Use Read-Only Transactions Where Appropriate
Some frameworks allow read-only transaction hints.
For example, in Spring:
@Transactional(readOnly = true)
public List<User> getUsers() {
return repository.findAll();
}
Read-only semantics can communicate intent to the persistence layer and may help avoid unnecessary application-side work.
They should not be treated as a magic PostgreSQL performance switch.
74. Control Connection Timeouts
Large-scale applications should have sensible connection and query timeout strategies.
A request shouldn’t wait indefinitely for a database connection or query.
Consider:
Connection timeout
Query timeout
HTTP request timeout
Transaction timeout
Timeouts should be coordinated so that the application can fail predictably under database pressure.
75. Use Statement Timeouts
PostgreSQL supports statement timeouts.
For example:
SET statement_timeout = '5s';
This can prevent unexpectedly expensive queries from running indefinitely.
Use timeouts carefully.
A timeout should protect the system, not hide queries that need proper optimization.
76. Use Lock Timeouts
PostgreSQL also supports lock timeouts.
For example:
SET lock_timeout = '2s';
This can prevent a transaction from waiting indefinitely for a lock.
Again, the correct value depends on the application’s workload.
77. Monitor Disk Space
Running out of disk space can be catastrophic for PostgreSQL.
Monitor:
Database size
WAL growth
Table growth
Index growth
Temporary files
Filesystem capacity
Set alerts well before the disk reaches critical capacity.
78. Monitor Replication Slots
Replication slots can cause WAL to accumulate if consumers stop advancing.
Monitor slot state and retained WAL.
An inactive slot that retains a large amount of WAL should be investigated immediately.
79. Keep PostgreSQL Updated
Use supported PostgreSQL releases and apply appropriate security and maintenance updates.
Newer PostgreSQL versions can provide:
- Performance improvements
- Bug fixes
- Security fixes
- Better planner behavior
- New database capabilities
Before upgrading production, test application compatibility and extension compatibility.
80. Choose Hardware Based on Workload
Database performance depends heavily on hardware.
Consider:
CPU
RAM
Storage
IOPS
Network
A write-heavy workload may need fast durable storage.
A read-heavy workload may benefit significantly from enough memory to keep frequently accessed data cached.
Don’t optimize hardware before understanding the workload.
81. Scale Vertically First
When a PostgreSQL server becomes overloaded, increasing:
CPU
RAM
Storage performance
can often be simpler than immediately introducing a distributed architecture.
Vertical scaling is usually easier operationally.
But eventually, workloads may benefit from:
Read replicas
Partitioning
Caching
Workload separation
Archiving
Sharding or distributed systems
Choose complexity only when the workload justifies it.
82. Use Caching Before Overcomplicating the Database
If the same relatively static query runs thousands of times per second:
GET Product
GET Product
GET Product
...
a cache may reduce database load dramatically.
A common architecture is:
Client
↓
Application
↓
Cache
↓
PostgreSQL
Use caching for suitable data and design invalidation carefully.
83. Use Database-Level Constraints
Application-level validation is useful, but the database should also protect important invariants.
For example:
email TEXT UNIQUE NOT NULL
and:
quantity INTEGER CHECK (quantity >= 0)
This prevents invalid data even if another application or script writes to the database.
84. Avoid Over-Normalization and Under-Normalization
Normalization reduces duplication and improves consistency.
But at scale, some read-heavy workloads may benefit from carefully chosen denormalization.
For example:
Normalized
users
orders
order_items
products
might require several JOINs.
A carefully designed summary table or materialized view can sometimes improve reporting performance.
Don’t denormalize prematurely.
85. Materialized Views
For expensive analytical queries, a materialized view can store query results.
For example:
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
date_trunc('month', created_at) AS month,
SUM(total) AS revenue
FROM orders
GROUP BY 1;
Refresh it when appropriate:
REFRESH MATERIALIZED VIEW monthly_sales;
Materialized views are useful when the underlying data doesn’t need to be reflected immediately in every query.
86. Use Background Jobs for Heavy Work
Don’t perform expensive database operations inside a latency-sensitive HTTP request when they don’t need to happen synchronously.
Instead:
HTTP Request
↓
Create Job
↓
Queue
↓
Worker
↓
PostgreSQL
This keeps user-facing requests fast and allows heavy work to be processed asynchronously.
87. Large-Scale Architecture Example
A mature PostgreSQL architecture might look like:
Users
↓
Load Balancer
↓
Application Servers
/ | \
/ | \
↓ ↓ ↓
Cache API Layer Workers
│ │
└────┬─────┘
↓
DB Pooling
↓
┌───────┴───────┐
↓ ↓
PostgreSQL Read Replicas
Primary / \
│ ↓ ↓
└────────── Replica 1 Replica 2
│
↓
Backups / WAL
The exact design depends on the application’s workload and availability requirements.
88. PostgreSQL Optimization Checklist
Before considering a large-scale PostgreSQL deployment optimized, review:
☐ Slow queries identified
☐ EXPLAIN ANALYZE used
☐ Important indexes created
☐ Unnecessary indexes reviewed
☐ Composite indexes reviewed
☐ Pagination implemented
☐ N+1 queries eliminated
☐ Connection pooling configured
☐ Pool size tuned
☐ Transactions kept short
☐ Autovacuum healthy
☐ Statistics current
☐ Disk usage monitored
☐ WAL monitored
☐ Replication monitored
☐ Backups tested
☐ Long-running queries monitored
☐ Lock contention monitored
☐ Query statistics collected
☐ Large tables evaluated for partitioning
☐ Data retention strategy defined
☐ Caching considered
☐ API result sizes controlled
☐ Security controls implemented
Common PostgreSQL Optimization Mistakes
Adding Indexes Without Measuring
More indexes don’t automatically mean better performance.
Increasing Memory Blindly
Large memory settings can create problems under high concurrency.
Making Connection Pools Huge
Too many connections can cause resource contention.
Ignoring Autovacuum
Poor vacuum behavior can eventually cause serious performance and storage problems.
Using SELECT *
This can transfer unnecessary data.
Ignoring Replication Lag
Read replicas aren’t always immediately consistent with the primary.
Using OFFSET for Huge Pagination
Keyset pagination can often be a better fit for large datasets.
Running Heavy Reports on the Primary
Consider replicas or separate analytics infrastructure.
Treating Replication as Backup
Replication does not protect against accidental changes in the same way independent backups do.
Optimizing Without EXPLAIN ANALYZE
Always understand what PostgreSQL is actually doing.
Practical Optimization Strategy
A good optimization process looks like this:
1. Identify the problem
↓
2. Measure workload
↓
3. Find expensive queries
↓
4. Run EXPLAIN ANALYZE
↓
5. Check indexes and statistics
↓
6. Optimize SQL/schema
↓
7. Test the change
↓
8. Measure again
↓
9. Monitor in production
Don’t make multiple unrelated changes at once.
If you change five things simultaneously, it becomes difficult to determine which change actually helped.
PostgreSQL Optimization Cheat Sheet
| Area | Optimization |
|---|---|
| Queries | Use EXPLAIN ANALYZE |
| Indexes | Index important query paths |
| Pagination | Prefer keyset for very large datasets |
| Connections | Use pooling |
| Transactions | Keep them short |
| Vacuum | Keep autovacuum healthy |
| Statistics | Keep planner statistics current |
| Storage | Use fast SSD/NVMe where appropriate |
| Reads | Use replicas when appropriate |
| Large tables | Consider partitioning |
| Caching | Cache suitable frequently accessed data |
| Reporting | Isolate heavy analytical workloads |
| Monitoring | Track queries, locks, WAL, disk, and replication |
| Security | Parameterize queries and restrict permissions |
| Recovery | Maintain tested backups and recovery procedures |
Final Thoughts
Optimizing PostgreSQL for large-scale applications isn’t about finding one magic configuration value.
It’s a combination of good database design, efficient SQL, appropriate indexes, connection management, monitoring, and architecture.
The optimization process can be summarized as:
Efficient Queries
+
Good Indexes
+
Healthy Schema
+
Connection Pooling
+
Vacuum & Statistics
+
Fast Storage
+
Replication
+
Caching
+
Monitoring
+
Reliable Backups
Start with the fundamentals:
EXPLAIN ANALYZE
Indexes
Query optimization
Connection pooling
Transactions
Autovacuum
Then move toward larger architectural improvements:
Read Replicas
Partitioning
Caching
Workload Isolation
Data Archiving
High Availability
Most importantly, measure before optimizing.
A query that looks inefficient may actually be the fastest option for a particular dataset, while a query that looks simple may become extremely expensive at scale.
The best PostgreSQL architecture is not the most complicated one. It is the simplest architecture that reliably handles your actual workload, growth, availability requirements, and performance targets.
