Overview

The requirement sounded simple. One local PostgreSQL server. A second local PostgreSQL server as a backup. Keep them in sync — inserts and updates only. No deletes. The source was the live system. The backup was a historical record.

I built three different solutions before I found one that actually worked.

This is the story of what broke each one and what I ended up with.

---

The First Approach: Timestamp-Based Polling

The obvious starting point. Every record in the source database has an updated_at timestamp. Track the last sync time, query everything updated since then, replicate it to the backup server.

Simple. Clean. Understood by any engineer in thirty seconds.

SELECT * FROM orders
WHERE updated_at > $last_sync_time
ORDER BY updated_at ASC;

Fetch the rows, generate upsert queries using Prisma, run them on the backup server. Done.

It worked for the first few tables.

Then I hit the first edge case.

Problem 1: Dependency Order

Parent-child relationships. A user record has to exist before an order record that references it can be inserted. If both were created between two sync cycles, the order of insertion matters.

Sorting by updated_at gives you time order but not dependency order. A child created one millisecond after its parent comes right after it in the result set — fine. But a child updated after its parent gets processed after the parent, which is also fine, until you have a scenario where the parent record was not updated at all and does not appear in the current sync window.

The child arrives at the backup server, looks for its parent, the parent does not exist. Foreign key violation. Sync fails.

The fix was dependency graph resolution. Build the foreign key graph for all tables in scope, topologically sort them, process in dependency order regardless of timestamp. Parents always before children.

This worked for trees. It did not work for cycles.

Problem 2: Cyclic Foreign Keys

Two tables with a foreign key relationship in both directions. Table A references Table B. Table B references Table A.

Topological sort fails on a cycle. There is no valid insertion order that satisfies both constraints simultaneously.

The standard approaches to this problem are:

Option 1: Insert with nulls, then update. Insert the row with the cyclic foreign key set to null, satisfying the constraint temporarily. Then run a second pass to update the foreign key to its correct value once the referenced row exists.

Option 2: Disable constraints, load, re-enable. Temporarily disable foreign key checks on the backup server, insert all rows, then re-enable. Risky — if any referential integrity violation exists in the source data, it gets silently propagated.

I went with option 1. Two-pass insertion for any table pair involved in a cycle. First pass inserts with null foreign keys. Second pass updates them. Dependency ordering handles the rest.

The Prisma query layer made this harder than it needed to be. Prisma translates from TypeScript to SQL but adds its own abstraction over update operations. Generating the correct two-pass merge queries through Prisma required careful handling of which fields to include in the first pass versus the second.

The system was becoming complex. It worked, but it was fragile. Every new table added to the sync scope required analysis of its foreign key relationships. Every schema change was a potential sync breakage.

Problem 3: The Updated-At Timestamp Bug

A subtle one. The timestamp-based approach tracks changes by updated_at. This works when records are updated. It fails silently when the ordering of changes interacts with the sync window in unexpected ways.

The case that broke it: a parent record is created and a child record is created in the same transaction. Both have identical updated_at timestamps. The sync query orders by updated_at — ties are broken arbitrarily. Sometimes the child arrives before the parent. Foreign key violation.

Millisecond-level timestamps are not a reliable ordering mechanism for dependent records created in the same transaction.

At this point I stepped back and rethought the approach.

---

The Second Approach: WAL Replication

PostgreSQL’s Write-Ahead Log is designed for exactly this kind of problem. Every write to the database — insert, update, delete — is recorded in the WAL before being applied. Standby servers replay the WAL to stay in sync with the primary.

The appeal: no polling, no timestamp logic, no dependency ordering code. Postgres handles all of it. Replication is ordered, complete, and reliable.

The problem: WAL replication replicates everything, including deletes. My requirement was explicit — deletes on the source should not propagate to the backup. The backup is a historical record. Once a row exists there, it stays.

You cannot selectively exclude delete operations from standard WAL replication. The WAL stream is a complete record of all operations. Filtering it requires intercepting the stream and dropping delete events before they reach the replica — which means building custom logical replication tooling.

I also had a second concern. The source and backup servers were on different local networks. WAL streaming replication expects a persistent low-latency connection between primary and standby. The network between these two servers was not that reliable. WAL replication would queue changes during disconnection and catch up when the connection restored — but the operational complexity of managing replication slots, monitoring replication lag, and handling slot bloat during extended disconnections was more than the project warranted.

WAL was the right answer for a different set of requirements. It was not the right answer for this one.

---

The Third Approach: A Log Table

The solution I should have built first, if I had known to look for it.

Instead of querying updated_at to find changed records, I created a log table that records every insert and update operation explicitly.

CREATE TABLE sync_log (
  id SERIAL PRIMARY KEY,
  table_name TEXT NOT NULL,
  record_id TEXT NOT NULL,
  operation TEXT NOT NULL, -- 'INSERT' or 'UPDATE'
  occurred_at TIMESTAMPTZ DEFAULT NOW()
);

A PostgreSQL trigger on every source table writes to sync_log on insert and update. Delete operations are not intercepted — the trigger only fires on INSERT and UPDATE.

CREATE OR REPLACE FUNCTION log_sync_event()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO sync_log (table_name, record_id, operation)
  VALUES (TG_TABLE_NAME, NEW.id::TEXT, TG_OP);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER sync_logger
AFTER INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION log_sync_event();

When the sync job runs, it reads the log table to find which records changed since the last sync, fetches those records from the source tables by ID, and replicates them to the backup.

-- Read pending sync events
SELECT DISTINCT table_name, record_id
FROM sync_log
WHERE occurred_at > $last_sync_time;

-- After sync completes, clear the log
DELETE FROM sync_log WHERE occurred_at <= $sync_completed_at;

Why This Solves the Problems

Dependency ordering: The log records operations in exact execution order. The trigger fires after each row is written, so the log preserves the actual insertion sequence. Parent records appear in the log before child records that reference them, because the database enforced that order when they were inserted.

Cyclic foreign keys: Because operations are logged in execution order, the two-pass insertion problem largely disappears. The log shows you exactly when each record was written. Replaying in log order means the database state at the backup mirrors the exact sequence of writes at the source.

Where cycles still require care: if two records in a cyclic relationship are inserted in the same transaction, the log order within that transaction may not be deterministic. A small amount of two-pass handling remains for intra-transaction cycles, but this is a much narrower problem than the general cyclic foreign key case.

No deletes propagated: The trigger simply does not intercept DELETE operations. Deletes on the source have no effect on the log table and therefore no effect on the backup.

Reliable ordering: The log is a sequence of events with timestamps at sub-millisecond precision, not a polling query that relies on updated_at. Even if two records are written in the same millisecond, they appear as separate log entries in write order.

The Tradeoffs

Every write to a source table now triggers a write to the log table. This is additional write overhead on the source database. For high-throughput tables, this is a cost worth measuring.

The log table grows between sync cycles. If the sync job fails or is delayed, the log accumulates. A monitoring job that alerts on log table growth above a threshold is worth adding.

The log table approach also means the sync job must handle the case where a record appears multiple times in the log — updated twice between syncs. The correct behavior is to fetch the current state of the record from the source and upsert it to the backup, ignoring intermediate states. The log is used to identify which records changed, not to replay every intermediate state.

---

What I Learned

The first version of a sync service always looks simpler than it is. Timestamp polling is intuitive. It is also insufficient for any schema with foreign key relationships and concurrent writes.

WAL is the right answer for a lot of problems, but not all of them. When your requirements include selective operation filtering or intermittent connectivity, WAL replication’s operational overhead and all-or-nothing streaming model work against you.

A log table is a well-understood pattern that solves real problems. It is essentially a manual Change Data Capture implementation. CDC tools like Debezium do something similar at the infrastructure level. Building it yourself gives you full control over what gets captured and what does not.

Cyclic foreign keys are a known hard problem in data replication. The standard solutions — null-first insertion, constraint disabling, two-pass updates — all work, each with their own tradeoffs. Knowing these patterns before you hit them in production is worth the reading time.

The sync service is running in production. It handles inserts and updates reliably, preserves deletion history on the backup, and survives the cyclic foreign key relationships in the schema without special-casing them at query time.

The code is straightforward. The path to get there was not.