Part 3: A Smaller, Cheaper, Highly Available Database in 26 Minutes, and Why AI Got Its Own PostgreSQL

At 22:33 on the night of 5 October, we pointed both load balancers at an empty tag. Anyone who opened the site saw the maintenance page. Behind it, every student, answer and result was being copied to a new database, and the window we had agreed with the business was 26 minutes.

Nobody had complained that the database was slow. The problem was its shape. It was one large MySQL node with no standby, so the day that machine failed would have been the day the whole platform failed. It was also bigger, and more expensive, than the work needed.

This article is about the data layer of NovaCommerce, the platform that sells courses and runs timed online exams. It covers the 26-minute move, the client we forgot, how we split reads and writes, why AI got its own PostgreSQL, and what the real exam evening of 7 October said about all of it. It follows Part 2, where the application layer learned to scale.

This is part 3 of the five-part case study "From One Server to Exam-Day Ready". NovaCommerce is a fictional name; the architecture, numbers and mistakes are real.

The wrong kind of big

The old database was one Managed MySQL "Advanced" node with 8 vCPU and 32 GB of RAM. It was big the way a single enormous bridge is big. Impressive, and the only way across.

Early in the project we wrote down what High Availability had to mean for us. One line said: the database survives a node failure. One node cannot meet that line at any size. Size and availability are different questions, and the old setup had answered only the first.

The data was about 11 GB. So we moved to Managed MySQL "Standard" 8.4 with 4 vCPU and 16 GB per node, and two nodes: a primary, and a standby that takes over if the primary fails. On the new cluster the buffer pool is 7 GB, max connections is 1,601 and storage autoscale is on. Status: Implemented, 5 October.

	Before	After
Plan	Advanced, single node	Standard 8.4, two nodes (primary + standby)
Size	8 vCPU / 32 GB	4 vCPU / 16 GB per node
If a node fails	The database is down	The standby takes over
Monthly cost	Higher	Lower; primary + standby is about $389 at list price

Cheaper and highly available at the same time is rare, so we took it. Here is the whole data layer as it looks today. The rest of the article walks through it.

The 26-minute cutover, step by step

The window was planned and agreed with the business. This is the order we followed on 5 October.

1. 22:33. Both load balancers pointed at an empty tag. Students saw the maintenance page.
2. Queues drained and the scheduler stopped, so no job could write in the middle of the copy.
3. Zero writes on the old database for 30 seconds. That was the gate: the copy started only after the old side had gone quiet.
4. Copy with mysqldump in 4 parallel streams. It took 10 minutes.
5. Count check: 341 tables and 18,727,244 rows matched exactly between old and new.
6. The application was tested against the new database. Then a new golden snapshot, a rollout of both pools and a fresh worker.
7. 22:59. Load balancers back, 26 minutes after we started.

How we proved the copy was right

Matching row counts is a weak test, because two tables can hold the same number of rows and different content. So we checked more than counts, and we kept checking after the site was back.

Check	Result
Tables and rows, old vs new	341 tables, 18,727,244 rows, exact match
Content of every table	Full-content checksums on all tables, after the window
Biggest results table	Compared column by column: 2.76 million rows identical
Rights of the app user	CREATE TABLE refused, as it should be
First 4 minutes live	About 2,900 requests, 0 5xx, 110 notification jobs, 0 failed

The client we forgot

At 23:24, 25 minutes after the site came back, we found something we had not planned for. A legacy admin panel, running on a separate old server, was still writing to the old database.

We had switched off everything we remembered: the web nodes, the worker, the scheduler. We had not switched off a thing we had forgotten existed. Think of moving house. The post office forwards your mail, but a friend who still has the old address keeps posting letters there.

By then 48 rows had landed in the old database. We merged them into the new one with a conditional upsert: insert the row, or update it only when the old copy is the newer one. 44 rows were merged. For 4 rows the new database already held a newer version, and we kept those. Then we pointed the legacy panel at the new database and changed the old cluster's app password, so that nothing could write to it again. Status: Implemented.

The password change matters as much as the merge. A forgotten client then fails loudly instead of quietly filling a database nobody reads. The old cluster stayed frozen for 48 hours as our way back, and was deleted after that.

The lesson is a checklist item, not a story:

• Before a cutover, list every external client of the database. Read the old cluster's trusted sources, and ask for each entry: who owns this?
• Switch every client in the same window as the application.
• After the switch, change the old password, so a client you missed fails at once.
• Keep the old cluster frozen for a while as a rollback.

Reads from the standby: the read/write split

Why not a read replica

The textbook answer to "reads are heavy" is a read-only replica node. We considered it. On this platform a read-only node must be at least as large as the primary, so it would cost about as much again. Meanwhile we already pay for a standby that sits idle, waiting for a failure.

So we implemented something cheaper: the standby is also a reader, reached through its replica hostname. Reads are shared between the standby and the primary, and each one covers for the other. Same hardware, no new line on the bill.

Option	Extra cost	Status	Note
Everything on the primary	None	Baseline	Simplest, but the primary does every read
Dedicated read-only replica node	About as much as the primary again	Considered	Must be at least as large as the primary
Standby shares reads with the primary	None	Implemented, 6 October	Reads may lag a little; sticky reads cover that

The rules, in plain words

We switched the split on at 00:13 on 6 October. In sketch form, the Laravel database configuration says this:

read   hosts = [standby, primary]
write  host  = primary
sticky = true
connect timeout = 2 s

• Writes go to the primary. Always.
• Reads go to the standby, and the primary is listed as a second read host. If one does not answer, the other does.
• Sticky. After a write, the same request keeps reading from the primary, so a student sees their own answer. A small middleware keeps the next request on the primary too. Think of writing a note on a whiteboard: you go back to the whiteboard to read it, not to a photocopy that may still be printing.
• A 2-second connect timeout. A slow standby falls back to the primary quickly, and no student waits on it.
• The worker never uses the standby. It stays on the primary, because jobs need fresh data.

Does it really split?

We checked on a live node instead of trusting the config. We opened 8 fresh connections, twice. The first run split 6 and 2 between the two servers, the second split 4 and 4. The primary reported read_only=0 and the standby read_only=1, so both were being used, and we knew which was which.

One gotcha

Production loads classes from an authoritative class map. A brand-new class, such as a new middleware, that is not in the map does not exist as far as the application is concerned, and the request returns a 500. Add it to the class map as part of the deploy.

Fixed size, narrow rights, access by tag

Weekly resizing: considered. One idea was to resize the database up before exam days and down afterwards. The business chose a fixed 4 vCPU / 16 GB instead. The exam evening numbers below back that choice.

Least privilege: implemented. The app's database user has SELECT, INSERT, UPDATE and DELETE, and no DDL. It can change rows, which it must, but it cannot create, alter or drop a table. We tested it during the cutover: CREATE TABLE was refused.

Access by tag: implemented. Every database admits the backend tag, the worker and the office and jump addresses. A new autoscaled backend node connects with no manual step.

That leaves one piece of arithmetic. Every backend node runs 80 php-fpm workers, and each one may hold a MySQL connection. The total must stay under the 1,601 allowed.

Backend nodes	php-fpm workers per node	Worst case connections	MySQL limit
2	80	160	1,601
4	80	320	1,601
6	80	480	1,601
10 (pool maximum)	80	800	1,601

Even at the pool maximum we use about half the limit, which leaves room for the worker and for admin sessions.

Valkey, in the data layer

One managed Valkey, with a standby, holds the cache, the sessions and the queues, and it runs with the noeviction policy. Part 4 goes deeper. Here is only what belongs to the data layer.

On 6 October at 02:28, a backend load test returned 2,854 application 500s. Every one was "Operation timed out" while connecting to Valkey, with a 5-second connect timeout. Each request opened a fresh TLS connection, which cost about 5.4 ms of CPU per request for Valkey, against about 3.3 ms for MySQL. At 4 GB, Valkey could not accept connections fast enough.

Implemented: we resized it in place to 8 GB with 2 nodes. Data kept, host unchanged. The retest ran from 25 up to 500 requests/s, starting on 2 backend nodes with the pool scaling out: 0 application errors, 0 backend 5xx. On exam evenings it uses about 12.5% of its memory. It costs more money, and it removed a real failure.

Why AI got its own PostgreSQL

NovaCommerce has AI study features: adaptive learning, weakness analysis and predicted questions. They work on embeddings, which are long lists of numbers that describe the meaning of a question. Finding the most similar ones is called vector similarity search.

We keep those embeddings in a separate Managed PostgreSQL 17 with the pgvector extension, at 2 vCPU and 4 GB. Not in MySQL. Three reasons:

• A different query shape. Vector search means big vectors, approximate-nearest-neighbour indexes and CPU-heavy queries. Exam traffic is short reads and writes.
• No competition with exam writes. A heavy similarity query must never take CPU from a student saving an answer. Two servers means two budgets.
• The right tool. MySQL has no first-class vector index for this job.

The downsize we reverted

The PostgreSQL node is small, and we tried to make it smaller: 1 vCPU and 2 GB. Tested, then reverted. We put it back to 2 vCPU and 4 GB because of the writes that arrive during exams. A small saving was not worth a node that struggles in the one hour that matters. Size for the exam hour, not for the quiet day.

For the generic version of this advice, such as choosing an index type and keeping filtered vector search accurate, see our field guide The Data Tier Under Load. This case study sticks to what we did.

Backups and recovery

A standby protects against a dead machine. It does not protect against a mistake: if someone deletes a table, the standby deletes it a moment later. That is what backups are for. We use both, and they answer different questions.

What goes wrong	What protects us	Status
A MySQL node fails	The standby takes over (provider failover)	Implemented
Data is lost or damaged	Daily backups of the managed databases (provider)	Implemented
The cutover goes wrong	Old cluster kept frozen for 48 hours, then deleted	Implemented
A web node is lost or a rollout fails	Golden snapshots of the web nodes; the last 3 good images kept	Implemented
The worker is lost	One snapshot of the worker; it is still a single point of failure	Snapshot implemented, standby worker planned

The database was not the bottleneck

We did not move the database because it was slow. We moved it for availability and cost. The measurements decided what not to spend on: on the real exam evening of 7 October, with two exams back to back, the database was the quiet part of the system.

Measure, 7 October exam evening	Value
MySQL CPU, max / average	24.5% / 13.4%
MySQL running queries, max	5
MySQL lock waits	0
Valkey memory	12.5%
Backend peak	About 69 requests/s
Backend node CPU, 2-minute average, max	47% (one node touched 86% for a minute)

The bottleneck was backend CPU per node, and the database had about 6x headroom. Our capacity model says MySQL becomes the limit near 450 requests/s, against a peak of 69 that evening. That model comes from one multiple-choice exam. Written exams with PDF uploads load the worker much more and must be measured separately.

In money terms, the three data stores cost about $689 a month at list price: MySQL about $389, Valkey about $240 and PostgreSQL $60. That is about 70% of the roughly $990 monthly bill with the backend pool at 2 nodes, which is why we grow a store only after measuring it.

What we learned

• One big node is not high availability. Availability needs a second node that can take over, and a smaller pair can beat one giant machine on price.
• A cutover needs a gate before the copy (zero writes), counts during it, and checksums after it.
• List every client of the old database before the cutover, then change the old password afterwards so a missed client fails loudly.
• Use the standby as a reader before you buy a replica. Add sticky reads and a short connect timeout, and keep jobs on the primary.
• Give the app user the rights it needs and nothing more. No DDL.
• Give AI its own PostgreSQL, and size it for the exam hour.
• Measure before you spend. The database had 6x headroom; the work was in the backend.

The databases are now calm, so the next questions are about everything that runs in the background: the single worker, the queues, the logs and the monitoring. Part 4 covers those: Valkey, workers, logging and reliability.