PostgreSQL Migration Pitfalls: From Rough Practices to Stable Management

Common Misconceptions

Increase connection pool size as soon as concurrency rises.

Raise the database's max_connections when connections are scarce.

Wrap entire business flows in a single transaction for "safety".

Deploy code that relies on temporary tables, session variables, or connection‑level state.

Launch to production as long as SQL runs correctly and returns expected results.

Add indexes whenever a query feels slow.

These habits may pass unnoticed during low traffic but cause connection spikes, CPU saturation, lock contention, transaction buildup, and response‑time jitter under peak load.

1. Active Connections Are Not Throughput

PostgreSQL creates one backend process per client connection, consuming memory, CPU scheduling, context switches, and lock resources. While adding connections can improve concurrency when the system is under‑utilized, beyond a certain point the bottleneck shifts to CPU, I/O, locks, and execution‑plan costs, leading to higher connection counts, busier CPUs, longer response times, and unchanged or reduced TPS.

In micro‑service environments, the total number of connections equals service_replicas × pool_max. For example, 20 replicas with a pool max of 30 can generate up to 600 connections, creating a "connection flood" at the database.

2. Connection Pool Size Is Not a Safety Net

A healthy pool should have a maximum size, allow queuing when full, fail fast after a threshold, return connections promptly, and expose metrics for active, idle, waiting, and timed‑out connections.

Moderate queuing is beneficial: it indicates that back‑pressure is applied at the application layer, preventing the database from becoming a bottleneck. Over‑sized pools overwhelm the database, while undersized pools cause excessive client waiting.

3. max_connections Is Not a Default Fix

When connection errors or performance jitter appear, the instinct is to raise max_connections. However, increasing this limit only permits more connections; it does not improve processing capacity. If the root cause is an inflated pool, slow SQL, long‑running transactions, lock contention, or poor indexing, raising the limit merely postpones the problem.

Typical degradation chain:

Connection saturation → increased max_connections → CPU saturation → lock waiting → I/O jitter → higher response time → overall instability.

Reasonable troubleshooting order:

Check whether the application reuses connections correctly.

Verify that the connection pool is not oversized.

Determine if active connections are truly high or mostly idle.

Inspect for long‑running transactions and idle in transaction sessions.

Identify high‑frequency slow SQL or resource‑heavy queries.

Finally, evaluate database parameters such as max_connections.

4. Clear Transaction Boundaries Improve Stability

Encapsulating an entire business flow in a single transaction (e.g., BEGIN; … COMMIT;) can lock resources for a long time, especially when the transaction includes RPC calls, HTTP requests, or message publishing. Long transactions increase lock duration, hinder vacuum, cause table and index bloat, and make troubleshooting harder.

Best practice: keep transactions limited to the minimal set of database operations required for consistency; move external calls and heavy computation outside the transaction.

5. idle in transaction Is Not Just Idle

An idle in transaction session holds a transaction open without executing SQL, retaining locks and preventing vacuum, which can lead to data version accumulation and increased storage usage.

Typical scenarios:

Opening a transaction, then calling an external service.

Complex business logic inside a transaction method.

ORM automatically starts a transaction but the application does not commit promptly.

Exception paths that miss a ROLLBACK.

Connections checked out from the pool and not returned for a long time.

State comparison:

active : executing SQL – normal.

idle : no transaction – safe.

idle in transaction : appears idle but the transaction is still open – high risk.

6. SQL Must Be Evaluated for Resource Cost

Running SQL successfully in a test environment does not guarantee efficiency in production. Developers should examine execution plans for excessive data scans, large row‑count mismatches, suboptimal join order, expensive sorting/aggregation/pagination, temporary file generation, and cumulative cost of high‑frequency queries.

Even queries that are not the slowest individually can dominate resource consumption if they run very frequently.

7. Indexes Need Balanced Cost‑Benefit

Adding indexes indiscriminately can improve some queries but incurs write‑time overhead, storage consumption, and may not be used by the optimizer. Poor selectivity, duplicate or similar indexes, and increased write latency are common pitfalls.

Reasonable index evaluation dimensions include query predicates, join conditions, sort fields, pagination strategy, column selectivity, data distribution, query frequency, and write cost.

Conclusion

Most PostgreSQL performance issues stem from legacy MySQL habits—oversized pools, large transactions, blind index addition, and treating "SQL runs" as sufficient validation. Stable PostgreSQL systems require disciplined connection management, precise transaction boundaries, careful SQL cost analysis, and judicious indexing rather than merely raising configuration limits.