Anurag’s Read

Migrating billions of records: moving our active DNS database while it's in use.

Shows a live Postgres migration with custom change capture, a rollback path and a 2 second write pause. Reuse the plan for audit and cutover.
Score
9.0
Verdict
Must read
Blog and date
Cloudflare,
Original length and level
18 min, advanced
Topics
postgres, migration, dns, change data capture
Read the original

Context

Cloudflare is the authoritative DNS provider for about 14.5% of websites. DNS data used about 40% of storage in cfdb, the main Postgres cluster, which also fed the Quicksilver key-value store. The DNS Records API runs a database query for each request, and load from other services slowed it.

In late 2022 the DNS team and 25 other teams agreed to move DNS records to their own cluster, dnsdb.

Architecture

First, the team hid the database behind an internal gRPC DNS Records API, which more than 20 services in five languages now use. Record-limit checks and audit logic moved from SQL functions into application code. A trigger on the cf_rec table writes every insert, update and delete into a log table on cfdb.

A bulk copy first moves existing rows by ID range, using psql COPY piped from one database to the other. Then the change transfer loop reads batches from the log table, sends them to dnsdb and applies them there. A small control table says which database is active and whether writes are locked. The API polls it.

At cutover the API holds write requests briefly while the last changes drain, then it points to dnsdb.

Mechanisms

M1

Change capture

The trigger copies the new row for inserts and updates. For deletes it stores only the primary key. Extra columns hold a change sequence ID, an action code (I, U or D), a timestamp and the user. The two tables had no triggers or foreign keys of their own, so adding a trigger caused no cascading effects.

M2

Bulk copy

The copy works in ID ranges, for example one million IDs per batch. It pipes COPY output straight into COPY input, so no intermediate file exists. The team compares the row count of each batch on both sides to detect loss. A bash loop checks for a pause file and an end file, so operators can stop the copy safely.

M3

Change transfer and apply

One transaction on cfdb selects a batch, records it in an audit table, deletes it from the log and returns it. A second transaction on dnsdb loads the batch into a staging table and keeps only the last action for each record ID. It then deletes matching rows and inserts the new versions. Last, it stores the batch in an apply audit table. The four phases (fetch, transfer, apply, count compare) run as separate steps.

M4

Write lock and cutover

The cutover cut the loop interval from 3 seconds to 0.5 seconds. The team locked writes through the control table and made cfdb read-only. After the last changes drained, writes were enabled on dnsdb and the control table was updated. A foreign table in cfdb pointed at the moved data, so code that still used cfdb kept working.

Design decisions

No.ChoseInstead ofWhy
D1Build a custom trigger-based change capture and transfer servicepgLogical, and a queue-based systempgLogical raised speed and audit concerns and could not run both ways. Queues handle one change at a time, which hurt speed goals.
D2Make the migration bidirectional with writes allowed in one database at a timeRollback stayed possible after cutover, and the team could prove that no data was lost.
D3Put all record access behind one gRPC API before the moveLet each service keep direct database accessOne access point gave central control of rate limits, audit and caching, and it made the final switch a single change.

Key numbers

  • cf_rec held 1.7 billion rows and 1.5 TB with indexes.
  • cf_archived_rec held about 4.3 billion insert-only rows.
  • Daily cf_rec activity was 3 to 5 million inserts, about 1 million updates and 3 to 5 million deletes.
  • The change loop ran every 3 seconds for several weeks before cutover.
  • Writes were blocked for no more than 2 seconds.
  • The largest zone held about 4 million records, and most zones held fewer than 100.
  • After the move, CPU on both clusters stayed under 10%.

Failures and limits

  • The team missed three rarely used services in its first scan of database clients.
  • The foreign table let the team fix them by renaming a table, so a full rollback was not needed.
  • After cutover, a latency spike and a backlog of requests lasted several minutes.
  • The post does not give the production batch size or the write cost of the trigger.

Lessons you can use

  • Put a service API in front of a shared database before you try to move it.
  • Keep the copy running for weeks and compare counts, so you can prove correctness before cutover.
  • Design for rollback and for a short write pause, because that is easier than zero downtime.
  • Keep a compatibility path such as a foreign table, because you will find clients you missed.

Glossary

CDCChange data capture records each change to a table so another system can replay it.
QuicksilverCloudflare's distributed key-value store that sends configuration to its data centers.
Foreign tableA Postgres table that reads data stored in another database.
pgLogicalA Postgres extension for logical replication between databases.