PostgreSQL Performance Tuning: Complete Developer Guide

PostgreSQL can perform extremely well, but it does not become fast by accident.

Most performance problems come from a few repeat issues:

• missing or low-value indexes
• inefficient query patterns
• poor schema design
• too many connections
• bad cache behavior
• vacuum and bloat problems
• or tuning the wrong layer before measuring the real bottleneck

That is why good PostgreSQL tuning is not about memorizing a list of settings. It is about understanding how PostgreSQL spends work on:

• finding rows
• joining rows
• sorting and aggregating rows
• maintaining visibility information
• caching hot data
• and coordinating concurrent sessions safely

This guide is written for developers who want practical tuning steps they can apply in real systems.

The Most Important PostgreSQL Performance Rule

Before touching settings, remember this:

The fastest PostgreSQL server is usually the one doing less unnecessary work.

That means:

• scanning fewer rows
• joining fewer rows
• sorting less data
• maintaining fewer wasteful indexes
• keeping bloated tables under control
• and avoiding more connections than the workload truly needs

A lot of PostgreSQL tuning goes wrong because teams start with global configuration instead of fixing expensive queries and schema patterns first.

So the best tuning order is usually:

1. measure the slow workload
2. inspect the plan
3. fix query shape and indexing
4. review schema and data distribution
5. then tune runtime settings if the workload still needs it

1. Start With Measurement, Not Guessing

Before changing anything, identify what is actually slow.

Useful things to measure:

• slow queries by total time
• slow queries by frequency
• lock waits
• temp file usage
• buffer hit patterns
• autovacuum activity
• bloat growth
• connection count and session state
• replication lag if replicas are involved

The key question is not:

• “What setting should we increase?”

It is:

• “Where is PostgreSQL spending time and why?”

A query can feel slow for very different reasons:

• bad index choice
• wrong join strategy
• stale statistics
• large sort spill
• too many round trips
• lock contention
• or plain old bad schema design

If you skip measurement, tuning becomes superstition.

2. Use EXPLAIN ANALYZE Early

One of the most important PostgreSQL performance tools is EXPLAIN ANALYZE.

It shows:

• the execution plan PostgreSQL chose
• estimated rows versus actual rows
• timing per step
• join methods
• sort behavior
• and whether the real runtime matched planner expectations

A basic example:

EXPLAIN ANALYZE
SELECT o.id, o.created_at
FROM orders o
WHERE o.customer_id = 42
ORDER BY o.created_at DESC
LIMIT 20;

This helps answer questions like:

• did PostgreSQL use an index?
• did it scan too many rows?
• was a sort needed?
• was the estimate badly wrong?
• did the query spend time in a join, filter, or sort step?

A lot of tuning starts with discovering that the real expensive part is not what the team assumed.

3. Fix Row Estimate Problems First

Many bad plans happen because PostgreSQL misestimates how many rows a step will return.

When row estimates are wrong, PostgreSQL may choose:

• a nested loop when a hash join would be better
• a hash join when the real data shape does not fit
• an index scan that touches too many rows
• or a sequential scan when a better index path exists

Common causes of poor estimates:

• stale statistics
• skewed data
• correlation between columns
• predicates on expressions
• highly uneven tenant or customer distributions
• complex boolean filters

If the plan shows large gaps between:

• estimated rows
• and actual rows

that is often one of the first things to investigate.

Sometimes the answer is:

• better statistics
• better query structure
• or better indexes aligned with the real filter pattern

4. Index for Real Access Patterns

Indexes are usually the biggest lever developers control directly.

But good indexing is not:

• “index every filtered column”

Good indexing is:

• “index the shapes of queries the application actually runs”

A useful index should usually match:

• filter columns
• sort order
• join keys
• and sometimes columns returned frequently enough to support index-only scans

For example, this query:

SELECT id, created_at, status
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

often benefits from an index like:

CREATE INDEX idx_orders_customer_created_at
ON orders (customer_id, created_at DESC);

That index matches:

• the filter on customer_id
• and the order on created_at DESC

That is much better than indexing customer_id and created_at separately and hoping PostgreSQL assembles the perfect path.

5. Understand the Main PostgreSQL Index Types

Most application workloads rely heavily on B-tree indexes. These are usually the right choice for:

• equality filters
• range filters
• sorting
• joins
• ORDER BY
• and many common lookup patterns

But PostgreSQL also has specialized index types.

B-tree

Best for:

• =
• <, <=, >, >=
• ORDER BY
• most foreign key and OLTP patterns

GIN

Best for:

• full-text search
• arrays
• JSONB containment-style lookups
• document-like search patterns

GiST

Useful for:

• geometric data
• ranges
• nearest-neighbor and other specialized operator classes

BRIN

Useful for:

• very large tables where values are naturally ordered on disk
• append-heavy time-series or event tables
• large datasets where a tiny summary index is acceptable

A lot of performance mistakes come from using the wrong index type or expecting B-tree to solve workloads it was not meant for.

6. Avoid Redundant and Low-Value Indexes

Adding indexes can speed up reads, but every extra index also adds cost.

Indexes increase:

• insert cost
• update cost
• delete cost
• storage usage
• vacuum work
• and planning complexity

Redundant indexes are especially common.

Examples:

• indexing both (customer_id) and (customer_id, created_at) when the second already covers the first workload
• adding many near-duplicate indexes for slightly different query variations
• indexing very low-selectivity columns without a real filter pattern that benefits

A healthy tuning pass often removes indexes, not just adds them.

A good rule is: every index should justify its existence with a meaningful workload.

7. Use Partial Indexes for Focused Workloads

Partial indexes are excellent when the application repeatedly queries a narrow subset of rows.

Examples:

• unpaid invoices
• active users
• non-deleted rows
• open tickets
• published posts

Example:

CREATE INDEX idx_invoices_unpaid_due_date
ON invoices (due_date)
WHERE paid = false;

This can be much better than indexing the whole table if most invoices are already paid and the application mostly looks for unpaid ones.

Partial indexes help by:

• reducing index size
• reducing write overhead
• improving cache efficiency
• and aligning the index with the real workload

But they only help if the query predicate matches the partial condition closely enough for PostgreSQL to use the index.

8. Use Multicolumn Indexes Deliberately

Multicolumn indexes are powerful, but column order matters.

If you create:

CREATE INDEX idx_orders_status_created
ON orders (status, created_at DESC);

that index is most useful for queries starting from status.

It is not equally good for every combination of the two columns.

When choosing multicolumn indexes, think about:

• which filters are most selective
• which filters appear together
• whether sorting matters
• and how the leading column affects reuse

Many PostgreSQL performance problems come from indexes that contain the right columns in the wrong order.

9. Covering Indexes and Index-Only Scans Can Help Hot Paths

Sometimes PostgreSQL can answer a query almost entirely from the index without visiting the table heap for each row.

This is called an index-only scan.

That can help for hot read paths, especially when:

• the query returns a small set of narrow columns
• visibility information is favorable
• and the index includes the needed data

In PostgreSQL, you can sometimes help this with INCLUDE columns.

Example:

CREATE INDEX idx_orders_customer_created_cover
ON orders (customer_id, created_at DESC)
INCLUDE (status, total_amount);

This may help a query that filters and sorts on the indexed keys but also needs status and total_amount.

Not every workload benefits, but for hot list endpoints it can be useful.

10. Query Shape Matters More Than Most Settings

The same data can be fast or slow depending on how the query is written.

Common costly patterns:

• selecting more columns than needed
• joining too early before filtering
• using functions that block index use
• unbounded result sets
• deep OFFSET pagination
• correlated subqueries where a better join exists
• repeating expensive lookups row by row in application code

For example, this is often more expensive than needed:

SELECT *
FROM orders
WHERE DATE(created_at) = DATE(NOW());

because wrapping the column in a function can make index use harder.

A better pattern is usually:

SELECT id, created_at, status
FROM orders
WHERE created_at >= CURRENT_DATE
  AND created_at < CURRENT_DATE + INTERVAL '1 day';

This preserves a range predicate that aligns better with ordinary indexes.

11. Learn the Main Join Strategies

PostgreSQL commonly uses:

• nested loop joins
• hash joins
• merge joins

Each can be correct depending on data shape.

Nested loop

Good when:

• one side is small
• the inner side has efficient index lookups
• result counts stay low

Bad when:

• the outer side is much larger than estimated
• the inner lookup repeats many expensive operations

Hash join

Good when:

• building a hash table on one side is affordable
• equality joins dominate
• both sides are large enough that repeated index lookups would be wasteful

Bad when:

• memory is insufficient and the hash spills badly
• row estimates are wrong

Merge join

Good when:

• both sides are already sorted or can be sorted efficiently
• large ordered sets are being joined

Bad when:

• the required sorting cost outweighs the benefit

Developers do not need to force join types manually most of the time. But they should understand why a plan chose one and whether the estimates behind it make sense.

12. Watch for Sort and Hash Spills

Sorts and hash operations can become expensive when they spill to disk.

This often shows up when:

• ORDER BY
• GROUP BY
• DISTINCT
• hash joins
• hash aggregation

need more memory than the query is allowed to use comfortably.

This is where work_mem matters. But the mistake is treating work_mem as a global “make queries faster” knob.

It is not per server. It is roughly per operation. A single complex query can use several such operations. Many concurrent sessions can do this at once.

So the right approach is:

• identify spills
• understand which queries are causing them
• improve indexes or query shape first
• and then raise memory carefully if the workload justifies it

Blindly increasing work_mem can create more problems than it solves.

13. Caching Matters, but Not the Way Many Developers Think

PostgreSQL performance depends heavily on caching:

• shared buffers inside PostgreSQL
• OS page cache outside PostgreSQL
• application-level caching above the database

A slow query is not always slow because PostgreSQL lacks memory. It may be slow because it touches too much data, sorts too much, or uses the wrong access path.

Still, caching matters because hot data staying in memory helps:

• index reads
• repeated table access
• hot OLTP workloads
• dashboard queries
• frequently used lookup tables

The important thing is to remember that caching helps repeated access. It does not fix fundamentally wasteful queries.

14. shared_buffers Is Important, but Not Magical

shared_buffers controls PostgreSQL’s shared cache. It matters, but it is not the first place developers should jump.

A reasonable setting helps PostgreSQL keep hot pages accessible. But if the workload is already inefficient, larger shared buffers will not transform it.

Typical guidance is to treat it as a balanced cache setting, not an emergency performance switch. Overcommitting it can also pressure the rest of the system, because PostgreSQL still benefits from OS caching too.

In real tuning work, developers usually get more from:

• better indexes
• better query shapes
• better pooling
• and less wasted data access

before obsessing over buffer sizing.

15. Connection Count Can Quietly Hurt Performance

Too many PostgreSQL connections are a common hidden problem.

Each active backend has overhead:

• memory
• process scheduling
• locking participation
• temporary work potential
• and coordination cost

A database with hundreds of mostly idle or poorly managed connections can behave worse than one with a smaller number of efficiently pooled connections.

That is why connection pooling matters.

For many applications, PgBouncer is one of the most useful PostgreSQL performance tools. It helps by:

• reducing backend churn
• limiting active connections
• smoothing spikes
• and preventing the app tier from overwhelming the database

More connections do not automatically mean more throughput. Often they mean more contention and more wasted resources.

16. Pool Size Should Match Real Work, Not Panic Defaults

A common anti-pattern is:

• traffic increases
• the team raises max_connections
• everything gets worse

That happens because the real problem may have been:

• slow queries
• poor transaction hygiene
• bad application concurrency patterns
• or bursty request behavior

A better strategy is usually:

• pool aggressively
• keep transactions short
• reduce idle-in-transaction sessions
• and keep true concurrent DB work at a sane level

The goal is not maximum connections. The goal is maximum useful work completed efficiently.

17. Keep Transactions Short and Clean

Long transactions are bad for performance in several ways.

They can:

• hold locks longer
• block cleanup of dead rows
• contribute to bloat
• increase replication lag in some cases
• and make concurrency worse

Common causes:

• application code opens a transaction too early
• user interactions happen while a transaction is still open
• background jobs process too much work in one transaction
• idle-in-transaction sessions are left hanging

A good practice is:

• begin transactions as late as possible
• do the necessary DB work
• commit as soon as possible

Transaction hygiene is one of the most overlooked PostgreSQL performance topics.

18. Vacuum and Autovacuum Are Core Performance Features

PostgreSQL uses MVCC, which means old row versions must be cleaned up over time.

That is where VACUUM and autovacuum matter.

When they fall behind, you get:

• table bloat
• index bloat
• stale statistics
• slower scans
• worse cache efficiency
• and degrading write performance

Autovacuum is not housekeeping you can ignore. It is part of the performance model.

If a busy table updates frequently, autovacuum behavior matters directly to latency and storage health.

When developers complain that PostgreSQL “got slower over time,” bloat and vacuum health are often part of the answer.

19. HOT Updates Can Reduce Write Cost

PostgreSQL can sometimes perform HOT updates when updated row versions do not force index changes.

That can reduce index churn and improve write efficiency.

This is influenced by:

• which columns are updated
• whether indexed columns are changing
• and how much free space remains on pages

That is why settings and design ideas like:

• fillfactor
• keeping hot update paths off indexed columns when possible
• and reasonable page free space

can matter for heavy write workloads.

Most developers do not need to obsess over HOT updates immediately, but on high-write tables they can be very relevant.

20. Fillfactor Can Help Write-Heavy Tables

fillfactor controls how full pages are allowed to become. Leaving some free space can help when rows are updated often, because it creates room for new row versions on the same page.

This can improve:

• update locality
• HOT update chances
• and sometimes write performance

It is not a universal setting. For many mostly read-only tables, the default is fine. But for heavily updated tables, it can be worth considering.

This is a good example of workload-specific tuning rather than one-size-fits-all tuning.

21. Schema Design Has Huge Performance Consequences

A lot of PostgreSQL performance is decided long before any EXPLAIN ANALYZE.

Schema choices affect:

• join complexity
• index strategy
• data growth
• row width
• write amplification
• cache efficiency
• and archival or retention cost

Important schema topics include:

• normalization versus denormalization
• data types
• partitioning
• natural versus surrogate keys
• multi-tenant design
• and how wide rows become over time

A schema that fights the workload is hard to save with runtime tuning.

22. Use the Right Data Types

Data types affect:

• storage footprint
• index size
• CPU cost
• row width
• and comparison behavior

Common mistakes include:

• using TEXT everywhere without need
• overly wide numeric types for small values
• unnecessary JSONB for heavily relational data
• inconsistent key types across joined tables

Smaller, more appropriate types often improve:

• storage efficiency
• cache behavior
• index density
• and scan performance

This does not mean prematurely micro-optimizing every column. It means modeling data deliberately instead of defaulting to the broadest type every time.

23. Normalize by Default, Denormalize Deliberately

Normalized schemas usually help:

• data integrity
• maintainability
• smaller duplication footprint
• and cleaner writes

But some read paths benefit from selective denormalization:

• cached counts
• snapshot fields
• precomputed totals
• reporting tables
• materialized views

The important thing is to denormalize because a real workload justifies it, not because joins feel scary in theory.

PostgreSQL is very capable of relational work when indexes and query patterns are good. Denormalization is useful, but it should be intentional.

24. Partitioning Helps Some Big-Table Workloads, Not Every Workload

Partitioning is often useful for:

• time-series data
• event tables
• retention-heavy tables
• large append-only histories
• and data where pruning by partition key is common

It can help with:

• maintenance
• retention
• archival
• and some query patterns through partition pruning

But it also adds complexity.

Partitioning is not a universal answer to large tables. It works best when the workload consistently filters on the partition key or when lifecycle management is a big concern.

25. Pagination Strategy Matters More Than People Expect

Deep OFFSET pagination can become expensive because PostgreSQL still has to walk through skipped rows.

For large result sets or user-facing feeds, keyset pagination is often more efficient.

Example offset style:

SELECT id, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

This gets more expensive as the offset grows.

A keyset approach can continue from a known last row instead, which usually scales much better for deep traversal.

Pagination is not just a UI decision. It is a database access pattern.

26. JSONB Is Powerful, but It Is Not Free

JSONB is excellent when:

• the data is semi-structured
• the shape changes often
• the query flexibility matters
• document-style access is real

But it is not automatically the best choice for:

• heavily relational data
• frequently joined structured entities
• or hot transactional paths where exact columns are queried constantly

Bad use of JSONB can hurt:

• indexing clarity
• query simplicity
• update costs
• and planner predictability

Use JSONB where it fits. Do not use it as an excuse to avoid schema design.

27. Avoid N+1 Query Patterns in the Application Layer

Sometimes PostgreSQL is not the real problem. The application is asking for data inefficiently.

Classic example:

• load 100 parent rows
• then run 100 child queries one by one

This creates unnecessary round trips and repeated work. Often the better answer is:

• a proper join
• a batched lookup
• or a prefetch strategy

No amount of database tuning fully compensates for an application doing 101 queries where one or two would do.

28. Read Replicas Can Help, but Only for the Right Problems

Read replicas can help when the bottleneck is:

• read scale
• analytical offloading
• reporting isolation
• or reducing load on the primary for read-heavy workloads

But they do not fix:

• bad queries
• poor indexing
• bad write paths
• lock issues on the primary
• or schema problems

They also add:

• replication lag concerns
• consistency considerations
• and operational complexity

Use replicas when they solve a measured workload issue, not as a first reflex.

29. Monitoring Is Part of Tuning, Not a Separate Topic

You cannot keep PostgreSQL fast without watching:

• slow queries
• CPU and memory behavior
• lock waits
• buffer and cache patterns
• autovacuum health
• bloat growth
• temp file usage
• replication lag
• and connection pressure

Performance tuning is not a one-time fix. It is an ongoing feedback loop.

The healthiest systems make it easy to notice:

• which queries got worse
• which tables are growing fastest
• which indexes are unused
• which vacuum tasks are falling behind
• and when workload shape changes

30. A Practical PostgreSQL Tuning Workflow

A good developer-friendly tuning workflow looks like this:

Step 1

Identify the slow query or overloaded workload.

Step 2

Run EXPLAIN ANALYZE and inspect:

• row estimates
• scans
• joins
• sorts
• spills
• timing hotspots

Step 3

Fix the query pattern:

• reduce selected columns
• improve predicates
• avoid deep OFFSET if needed
• simplify joins or aggregations

Step 4

Add or revise indexes based on the real access path.

Step 5

Check schema issues:

• bad data types
• unnecessary duplication
• missing tenant-aware design
• bad archival pattern
• overly wide rows

Step 6

Review operational factors:

• autovacuum
• bloat
• connection count
• transaction hygiene
• pooling

Step 7

Only then tune runtime settings if the workload still justifies it.

This order avoids a lot of wasted effort.

Common PostgreSQL Performance Bottlenecks

Missing composite indexes

The app filters and sorts on a pattern, but the index only covers part of it.

Stale statistics

PostgreSQL chooses a bad plan because it misunderstands row counts.

Too many connections

The database spends too much energy coordinating sessions instead of doing useful work.

Long transactions

Cleanup gets blocked and concurrency suffers.

Table and index bloat

Reads and writes get slower as structures grow inefficiently.

Over-indexing

Write cost rises and maintenance becomes heavier without enough read benefit.

Deep OFFSET pagination

The server throws away more and more work as the page depth grows.

Poor application query behavior

N+1 lookups and inefficient batch handling waste DB capacity.

Developer Checklist

Use this quick checklist when investigating a PostgreSQL slowdown:

• Is the slow path one query or a workload pattern?
• Did you run EXPLAIN ANALYZE?
• Are estimated rows far from actual rows?
• Does the query scan far more rows than it returns?
• Is there a better composite or partial index?
• Is the query doing a big sort or hash spill?
• Are there too many connections?
• Are transactions staying open too long?
• Is autovacuum healthy on the hot tables?
• Are the table or indexes bloated?
• Is the schema fighting the workload?
• Is the app making too many round trips?

Appendix A — Cheat Sheet

Find expensive queries with pg_stat_statements

SELECT
  query,
  calls,
  total_exec_time,
  mean_exec_time,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Find tables with many sequential scans

SELECT
  relname,
  seq_scan,
  idx_scan,
  n_live_tup
FROM pg_stat_user_tables
ORDER BY seq_scan DESC
LIMIT 20;

Find unused indexes

SELECT
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname, indexrelname;

Check dead tuples

SELECT
  relname,
  n_live_tup,
  n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Check active sessions

SELECT
  state,
  count(*)
FROM pg_stat_activity
GROUP BY state
ORDER BY count(*) DESC;

Find blocking locks

SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks
  ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks
  ON blocked_locks.locktype = blocking_locks.locktype
 AND blocked_locks.database IS NOT DISTINCT FROM blocking_locks.database
 AND blocked_locks.relation IS NOT DISTINCT FROM blocking_locks.relation
 AND blocked_locks.page IS NOT DISTINCT FROM blocking_locks.page
 AND blocked_locks.tuple IS NOT DISTINCT FROM blocking_locks.tuple
 AND blocked_locks.virtualxid IS NOT DISTINCT FROM blocking_locks.virtualxid
 AND blocked_locks.transactionid IS NOT DISTINCT FROM blocking_locks.transactionid
 AND blocked_locks.classid IS NOT DISTINCT FROM blocking_locks.classid
 AND blocked_locks.objid IS NOT DISTINCT FROM blocking_locks.objid
 AND blocked_locks.objsubid IS NOT DISTINCT FROM blocking_locks.objsubid
 AND blocked_locks.pid <> blocking_locks.pid
JOIN pg_stat_activity blocking
  ON blocking.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted
  AND blocking_locks.granted;

Appendix B — Top-Up Index (1–200)

1. Start with EXPLAIN ANALYZE, not config changes.
2. Compare estimated rows versus actual rows.
3. Fix bad query shape before raising memory settings.
4. Index the real filter and sort pattern together.
5. Remove indexes that do not support real workloads.
6. Prefer composite indexes over many disconnected single-column guesses.
7. Use partial indexes for predictable hot subsets.
8. Check for sort spills before changing work_mem.
9. Keep transactions short.
10. Avoid idle-in-transaction sessions.
11. Use PgBouncer when connection counts grow.
12. Do not treat max_connections as a throughput knob.
13. Monitor autovacuum on hot tables.
14. Watch dead tuples and table bloat.
15. Watch index bloat too.
16. Review unused indexes regularly.
17. Avoid SELECT * on hot paths.
18. Fetch only the columns you need.
19. Do not wrap indexed columns in unnecessary functions.
20. Use range predicates that preserve index usefulness.
21. Prefer keyset pagination for large feeds.
22. Be careful with deep OFFSET pagination.
23. Choose data types intentionally.
24. Keep joined key types consistent.
25. Use foreign keys, but index the referencing side where needed.
26. Review row width on hot tables.
27. Separate hot and cold data when it helps.
28. Partition only when the workload truly fits it.
29. Do not use partitioning as a first response to every slow table.
30. Use BRIN for the right very large append-like workloads.
31. Use GIN for the right JSONB and text-search patterns.
32. Do not force JSONB where relational columns are better.
33. Check whether the application is doing N+1 queries.
34. Batch lookups where possible.
35. Recheck slow endpoints after feature launches.
36. Query plans change as data changes.
37. A plan that was fine at 100k rows may be bad at 100M.
38. Keep staging data realistic when testing performance.
39. Measure temp file growth.
40. Look for repeated sorts in hot plans.
41. Watch for nested loops over unexpectedly large outer sets.
42. Check whether a hash join is spilling badly.
43. Use ANALYZE healthily through autovacuum or manual maintenance when needed.
44. Treat stale stats as a real production problem.
45. Measure before and after every major tuning change.
46. Do not tune blind.
47. Keep an eye on lock waits, not just CPU.
48. Blocking can look like slowness.
49. Be careful with large bulk updates.
50. Heavy writes can create vacuum pressure later.
51. Use fillfactor thoughtfully on high-update tables.
52. HOT-friendly design can matter for write-heavy systems.
53. Denormalize selectively, not emotionally.
54. Normalize by default unless read pressure proves otherwise.
55. Use materialized views or projections for repeated heavy reporting if justified.
56. Keep tenant filters explicit in multi-tenant systems.
57. Align indexes with tenant-aware access patterns.
58. Use tenant-scoped uniqueness where appropriate.
59. Do not confuse global uniqueness with business uniqueness.
60. Watch large analytical queries on OLTP primaries.
61. Consider replicas for read offloading only when they solve a real problem.
62. Replicas do not fix bad SQL.
63. Avoid huge IN lists if a better join or temp relation pattern exists.
64. Investigate planner behavior on skewed data.
65. Heavily uneven customers or tenants can distort plan quality.
66. Watch cache hit behavior, but do not worship one metric.
67. A high cache hit rate does not prove the workload is healthy.
68. Use covering indexes selectively for hot read paths.
69. Check whether index-only scans are realistic on the target table.
70. Remember visibility rules affect index-only scans.
71. Keep maintenance windows realistic.
72. Vacuum debt compounds over time.
73. Prefer many good decisions over one heroic setting change.
74. Tune the workload, not your ego.
75. Revisit old indexes after major product changes.
76. A once-helpful index may become dead weight.
77. Beware of OR conditions that obscure index paths.
78. Rewrite predicates when a clearer shape helps the planner.
79. Do not assume the ORM generated the best SQL.
80. Inspect it.
81. Beware of hidden cartesian growth in joins.
82. Verify join predicates carefully.
83. Distinguish latency problems from throughput problems.
84. Distinguish query slowness from connection saturation.
85. Distinguish lock contention from CPU saturation.
86. Distinguish planner mistakes from storage pressure.
87. Keep query patterns stable where possible.
88. Repeatedly changing shapes can reduce plan predictability.
89. Use prepared statements thoughtfully with pooling strategy in mind.
90. Know how your pooler interacts with session features.
91. Watch transaction duration in background workers.
92. Background jobs can silently become your worst DB citizens.
93. Large deletes are not free.
94. Retention by partition drop is often cleaner than retention by mass delete.
95. Consider archival design before tables become enormous.
96. Track table growth trends.
97. Track index growth trends.
98. Track autovacuum lag trends.
99. Track slow-query regressions over time.
100. The best tuning culture is continuous, not reactive.
101. Keep SQL readable enough to optimize.
102. Complex SQL is harder to reason about under pressure.
103. Name indexes consistently.
104. Document why special-purpose indexes exist.
105. Re-evaluate partial indexes if query predicates drift.
106. Predicate mismatch can make a good partial index useless.
107. Check composite index order carefully.
108. Leading columns matter.
109. Match indexes to ORDER BY when pagination is hot.
110. Avoid sorting large sets unnecessarily.
111. Push filters earlier when possible.
112. Aggregate after narrowing the working set.
113. Watch for wide joins returning columns the API never uses.
114. Reduce tuple width where practical.
115. Use COUNT(*) carefully on huge tables in hot paths.
116. Cached counters may be justified in the right workload.
117. Historical snapshot fields are sometimes correct denormalization.
118. Distinguish operational queries from reporting queries.
119. They often deserve different paths.
120. Put expensive admin reporting on a different path if needed.
121. Check temp table usage in ETL-style workflows.
122. Session patterns can create hidden memory pressure.
123. Keep session state assumptions clear when using poolers.
124. Analyze the top total-time queries, not only the single slowest query.
125. High-frequency medium-slow queries often dominate total load.
126. Mean execution time can hide tail problems.
127. Look at worst-case behavior too.
128. Watch p95 and p99 response paths at the app layer.
129. Database timing and endpoint timing should be correlated.
130. Sometimes the DB is blamed for app-layer waste.
131. Use server-side limits intentionally.
132. Unbounded endpoints become future incidents.
133. Know which tables are write-hot.
134. Know which tables are read-hot.
135. Know which indexes are write-expensive.
136. A small schema review can prevent a large performance project later.
137. Rehearse large migrations on realistic data.
138. Query plans can change after schema changes.
139. Validate major index changes with measurement.
140. More indexes are not automatically safer.
141. Tune with the future data size in mind.
142. A query that is acceptable today may fail the next order of magnitude.
143. Watch WAL generation during heavy write operations.
144. Heavy churn affects replicas and storage too.
145. Use application caching where it meaningfully removes duplicate reads.
146. But do not cache your way around broken query design forever.
147. Keep retry logic sane around lock and timeout scenarios.
148. Bad retry storms can magnify DB pressure.
149. Tune statement timeouts intentionally.
150. Some endpoints should fail fast instead of dragging the system down.
151. Consider lock timeouts for safer concurrency behavior.
152. Deadlocks are correctness issues, but they affect perceived performance too.
153. Keep update order consistent in critical workflows.
154. Review hot tables for unnecessary triggers.
155. Hidden trigger work can distort perceived query cost.
156. Review generated columns and expressions if they are on hot write paths.
157. Materialize only what the workload justifies.
158. Separate convenience fields from authoritative fields clearly.
159. Use schema changes to support performance deliberately, not randomly.
160. Backfill carefully on huge tables.
161. Big backfills can create vacuum and WAL debt.
162. Monitor during heavy maintenance, not just after.
163. Performance incidents often emerge mid-operation.
164. Do not forget the operating system layer.
165. Disk latency still matters.
166. Network latency still matters for chatty applications.
167. CPU saturation still matters for bad query mixes.
168. Memory pressure still matters for concurrency-heavy workloads.
169. PostgreSQL tuning is full-stack thinking.
170. The database is part of a system, not a magic box.
171. Prefer one clear improvement at a time over many simultaneous changes.
172. Otherwise you will not know what helped.
173. Keep rollback plans for major tuning changes.
174. Especially for index removals and pool changes.
175. Understand the workload before changing planner-related settings.
176. Global planner toggles are rarely the first answer.
177. Avoid forcing plans unless you are solving a very specific issue.
178. The fix is usually data shape, stats, or indexing.
179. Use multicolumn stats if correlation is hurting row estimates and the workload justifies it.
180. Revisit skewed predicates after data growth.
181. Review batch job schedules if they collide with user traffic.
182. Throughput problems are often scheduling problems too.
183. Treat migrations as performance events on large tables.
184. Schema changes can be latency events.
185. Plan them with the same discipline as code releases.
186. Observe PostgreSQL after deploys, not just during incidents.
187. Slow drift is still a performance bug.
188. Make slow-query review part of regular engineering work.
189. Teach developers to read plans early.
190. Performance is easier to keep than to recover.
191. Keep docs for your key tables and access patterns.
192. The best index strategy is easier to maintain when it is shared knowledge.
193. Be suspicious of miracle tuning advice with no workload context.
194. PostgreSQL performance is workload-specific.
195. The right answer for one app can be wrong for another.
196. Keep your tuning honest with measurements.
197. Keep your schema honest with real access patterns.
198. Keep your operations honest with monitoring.
199. Keep your application honest with efficient SQL generation.
200. PostgreSQL flies when the whole stack stops wasting work.

FAQ

What is the first thing to check when PostgreSQL gets slow?

Start with the slow query itself. Use EXPLAIN ANALYZE, check indexes, row estimates, sort and hash spills, and whether the query is scanning far more rows than it returns.

Does adding more indexes always make PostgreSQL faster?

No. Indexes can speed up reads, but they also add write overhead, consume storage, and increase maintenance work. Good indexing is selective and workload-driven.

How important is autovacuum for PostgreSQL performance?

It is critical. Poor autovacuum behavior leads to table bloat, index bloat, stale planner statistics, and slower reads and writes over time.

Should developers care about connection pooling?

Yes. Too many active PostgreSQL connections can waste memory and hurt performance. Connection pooling with PgBouncer often improves stability and throughput.

Conclusion

PostgreSQL performance tuning works best when developers stop treating it like a bag of mystery settings.

The biggest wins usually come from:

• better query shape
• better index design
• healthier transaction behavior
• good connection pooling
• strong vacuum health
• and schema choices that fit the real workload

That is why the most effective tuning mindset is simple:

• measure first
• inspect the plan
• reduce wasted work
• and only then tune broader runtime settings when the workload actually calls for it

If you follow that approach consistently, PostgreSQL becomes much easier to scale, much easier to reason about, and much less likely to surprise you in production.