The Data Tier Under Load: PostgreSQL, Connection Pooling, Redis and pgvector

At 9:02 on the morning of a flash sale, in an illustrative scene, the engineer on call sees 940 of 1,000 database connections in use and checkout slowing past three seconds. Someone suggests moving the database to the next size up before the second wave arrives.

That would be a guess. Most of those 940 connections are idle, held by PHP processes between requests, and database CPU sits near 35 percent. The real queue is eleven workers waiting on writes to the same few rows. The connection count is a symptom, and resizing treats the symptom.

I have seen the real version of this. In a platform I prepared for a scheduled spike of about 1,000 users acting in the same minute, the managed database (16 GB of RAM, 4 vCPU, roughly 1,000 allowed connections) was never the bottleneck. A single serial worker was. This article covers the data tier the way I plan it now: what to measure, how to pool, what replicas can and cannot do, how to give Redis or Valkey a clean job, and how to add AI search without putting vector queries on your transactional database.

This is part 3 of the series "Engineering for High Volume". Part 1 covers the edge and web tier and part 2 covers queues and workers. Here we go one layer down, to the data.

Size by write throughput, not by connection count

A database page that says "1,000 max connections" tells you how many clients may connect, not how much work the server can do. Four vCPUs can run only a handful of queries at the same instant. Everything else waits, whether it is connected or not.

So measure the things that actually saturate a relational database: write throughput (commits per second and WAL volume), CPU, disk latency and lock waits. Connection count matters only as a symptom of something else, such as slow queries holding connections open.

My rule is simple: do not upgrade the database before a load test proves it is saturated. In the setup above, the fix was to split the web and worker nodes and run about 12 worker processes. The database handled the extra load easily. Past roughly 20 workers the jobs only queued on database writes, and that, not connections, is the real ceiling to watch. These numbers come from one setup, not a universal benchmark, so run your own test.

Pool your connections

PHP opens many short connections: every request, and every queue job, may connect and disconnect. PostgreSQL starts a separate process for each connection, so a thousand clients can use a lot of memory before they run a single useful query. A pooler such as PgBouncer sits between the application and the database. Hundreds of client connections share a few dozen real server connections.

An illustrative calculation. Two web nodes with 60 PHP-FPM children each can hold 120 connections, 12 worker processes add 12, and the scheduler plus a few admin sessions add about 8. That is roughly 140 connections, or 140 PostgreSQL processes competing for 4 vCPU. Put PgBouncer in transaction mode with a pool of 20 server connections and the same 140 clients share 20. If an average transaction holds its connection for 5 ms, 20 connections can carry at most 20 ÷ 0.005 = 4,000 transactions per second. The CPU will cap you well below that, and that is the point: the pool makes the real limit visible instead of hiding it under connection churn.

PgBouncer has three pool modes, and the choice decides what your application may do.

Pool mode	A server connection is held for	Best for	Watch out for
Session	The whole client connection	Long-lived listeners, tools that need session state	Saves the least; idle clients still hold a server connection
Transaction	One transaction	Web requests and queue jobs; the usual choice for high volume	Anything that relies on session state breaks
Statement	One statement	Simple, autocommit-only workloads	Multi-statement transactions are not allowed

For a web application under load, transaction mode gives the biggest win. It also has caveats you should know before you switch.

What breaks in transaction mode

Because the next transaction from the same client may land on a different server connection, state that lives in the session is unreliable:

• Session-level settings. A plain SET does not carry over; SET LOCAL inside a transaction does.
• Advisory locks and LISTEN/NOTIFY held at session level. Use a direct connection or session mode for those.
• Temporary tables and WITH HOLD cursors that outlive a transaction.
• Prepared statements. Older PgBouncer versions could not support them in transaction mode. Since version 1.21 PgBouncer can track them if you set its max_prepared_statements option above zero. Check your version and your driver rather than assuming.

The practical answer is two connection paths: the pooled one for the application, and a direct one for migrations, schema tools and anything that listens. The PgBouncer configuration reference lists every option, including pool sizes and timeouts.

Replicas: what they fix and what must stay on the primary

A read replica is a copy that follows the primary with a small delay. It is good for work that can tolerate being slightly behind: reports, exports, search listings, dashboards and analytics queries that would otherwise compete with checkout.

It is not a general speed-up. Replication lag is normal, and under heavy writes it grows. Keep these on the primary:

• Anything that reads right after it writes, such as showing an order page after checkout.
• Stock reservation, payments, authentication and anything that decides money or access.
• Any query inside a transaction that also writes.

In practice this becomes a routing rule short enough to write on one page.

Indexes and N+1 queries: the cheapest capacity you can buy

Before you add hardware, find the queries that burn the most time. PostgreSQL's pg_stat_statements extension ranks queries by total time, and EXPLAIN with the ANALYZE and BUFFERS options shows whether a query reads a few pages or a million.

Two problems cause most of the waste:

• Missing or wrong indexes. A filter and sort on columns with no matching index turns a millisecond lookup into a full table scan. Add composite indexes that match your real filters and ordering.
• N+1 queries. A list page that runs one query for the list and one more for each row. Fifty rows means fifty-one round trips. Eager-load the relations you display.

Redis and Valkey: one server, several jobs

A short note on names. In March 2024 Redis moved to source-available licences, and the Linux Foundation launched Valkey, a fork of Redis 7.2.4 that keeps the permissive BSD licence. Valkey speaks the same protocol, so most applications and managed services treat the two as interchangeable. Everything below applies to both.

The common mistake is to put cache, sessions, API tokens and queues into one keyspace. Then one flush, or one memory squeeze, damages all of them. Give each role its own logical database or its own instance, and its own key prefix. A cache can be emptied safely. A queue cannot.

Role	What loss means	Eviction policy	Rule
Cache	Slower for a moment; data is rebuilt	allkeys-lru	Everything has a TTL; evicting is fine
Sessions and API tokens	People are logged out	volatile-ttl or volatile-lru	Size it so eviction never happens
Queues	Lost jobs	noeviction	Writes fail loudly instead of dropping work; alert on memory

The Redis eviction documentation describes each policy. Two points matter most. With allkeys-lru, Redis may remove any key to stay under its memory limit, which is exactly what you want for a cache and exactly what you never want for a queue. With noeviction, a full server refuses new writes rather than deleting data, so a queue stays correct and you find out through an error and an alert.

Never FLUSHDB

Ban FLUSHDB and FLUSHALL in your runbooks and in the application. When the cache needs clearing, delete by prefix. That way a cache flush can never remove queued jobs, even by accident at the worst possible moment.

Right-size it

In that same platform the managed Valkey instance was provisioned at 16 GB and used about 62 MB. Look at real memory use under a realistic load, add generous headroom for the queue during a burst, and size down to that.

pgvector for AI search, in its own database

If your store or ERP has AI search, recommendations or an assistant that retrieves documents, you are storing embeddings: long lists of numbers that represent meaning. The pgvector extension adds a vector type and nearest-neighbour search to PostgreSQL, so you do not need a separate vector product to start.

My design rule is to run it in a separate PostgreSQL database with its own connection and its own migration path. The latest setup I built used pgvector 0.8 on PostgreSQL 18, which was released in September 2025. Vector queries are heavy on CPU and memory, and index builds are heavier still. In a separate database the AI workload cannot slow checkout, and you can size, back up and upgrade it on its own schedule.

Generate embeddings in workers

Creating an embedding means calling a model, which is slow and can fail. Do it from a queue job triggered after the database commit, not in the web request. Make the job idempotent: store a hash of the source text and skip the work when nothing changed, and keep the model version with each row so you can re-embed in the background later. This is the same worker discipline from part 2.

HNSW or IVFFlat

pgvector offers two approximate index types, and both trade a little accuracy for a lot of speed.

	HNSW	IVFFlat
Speed and recall	Usually the better trade-off	Good, depends on how many lists you probe
Build time and memory	Slower to build, uses more memory	Faster to build, uses less memory
Needs data first	No, can start on an empty table	Yes, build it after loading representative data
Typical use	Default for most live search	Very large, rarely changing sets where build cost matters

For most commerce and ERP workloads I start with HNSW. The pgvector project documentation covers the tuning options for both.

Filtering is where search quietly breaks

Real queries are never "nearest to this text" alone. They are "nearest, in stock, in this store, in this language". With an approximate index the filter is applied after the index is scanned. If a filter matches 10% of rows and the default hnsw.ef_search is 40, you get about four matching rows on average, even when you asked for ten.

pgvector 0.8.0, released in late 2024, added iterative index scans for exactly this problem. When too few rows survive the filter, the scan continues until it has enough or reaches a limit.

Setting	What it does
hnsw.iterative_scan	Turns on iterative scans for HNSW: strict_order keeps exact distance order, relaxed_order allows slight disorder for better recall
hnsw.max_scan_tuples	Upper limit on how many index entries an iterative scan may visit
ivfflat.iterative_scan	The same idea for IVFFlat, using relaxed ordering
ivfflat.max_probes	Upper limit on lists probed during an iterative scan

Keep object storage next to the compute

Files are state too. Product images, invoices and exports belong in S3-compatible object storage, not on a web node's disk, or you cannot run two nodes behind a load balancer.

Place the bucket in the same region as your servers. In one load test a bucket in a different region added roughly 50 to 80 ms to every upload of a generated file. Streaming files to and from storage, rather than downloading them to local disk first, also cut one job's peak memory from about 288 MB to about 128 MB.

A data tier checklist before the event

1. Run a load test at the target rate and record database CPU, disk latency, lock waits and commits per second. Only then decide whether to resize.
2. Put PgBouncer in transaction mode in front of the application, and keep a direct connection for migrations and listeners.
3. Check your PgBouncer version and your driver for prepared statement support.
4. Rank queries with pg_stat_statements, fix the top ones, and remove N+1 patterns on your busiest pages.
5. List which reads may use a replica, and confirm that checkout, stock and payments stay on the primary.
6. Split Redis or Valkey by role with separate prefixes, set an eviction policy per role, and ban FLUSHDB.
7. Alert on queue memory so noeviction never turns into silent failure.
8. Run AI search in its own PostgreSQL database, test filtered queries, and enable iterative scans if recall drops.
9. Confirm that object storage sits in the same region as your compute.

Back to 9:02. With the pooler in transaction mode, the 940 client connections map onto 20 server connections, and the dashboard stops showing a frightening number. The sales report runs on the replica, the cache and the queue live in separate keyspaces, and the on-call engineer asks "what is saturated?" instead of "what size is next?". The load test already gave the answer: writes, with the database at a comfortable margin.

Once the data tier is tuned you still need to see what it is doing on event day. Part 4 covers that: logs, metrics and traces you can act on.

Key takeaways

• Size the database by write throughput and measured saturation, not by the connection limit, and never upgrade before a load test shows it is needed.
• Use PgBouncer in transaction mode for the application, and know what it breaks: session state, session-level locks, LISTEN/NOTIFY and old prepared-statement setups.
• Replicas serve lag-tolerant reads; anything that reads after a write, or touches money and stock, stays on the primary.
• Give cache, sessions and queues separate Redis or Valkey databases and prefixes, with allkeys-lru for cache and noeviction for queues, and never flush the whole database.
• Keep pgvector in its own PostgreSQL database, create embeddings in workers, prefer HNSW to start, and use iterative scans for filtered search.
• Right-size cache memory and keep object storage in the same region as compute.

Anichur Rahaman is a software architect and the creator of StoreConsole. He designs commerce and ERP systems for growing businesses, with a focus on event-driven architecture, data integrity and self-hosted operations.