1. Same database, same instance

If both tables are in the same database, you rarely need anything fancy:

Database triggers — a trigger on INSERT/UPDATE/DELETE on table A writes/updates the corresponding row in table B, in the same transaction. This is instant and consistent, but triggers can get hard to maintain as logic grows, and they add latency to every write.
Materialized views (Postgres, etc.) — good for read-optimized derived tables, but usually refreshed on a schedule, not truly real-time unless you use incrementally-updated views.

Use this when the tables are tightly coupled and you want strong consistency (no chance of them drifting apart).

2. Same database engine, different databases/services — Change Data Capture (CDC)

This is the most common production pattern once services are separated. Instead of the application manually writing to both places (which is fragile — a failure after write #1 but before write #2 leaves things out of sync), you:

Read the database's transaction/write-ahead log (WAL) and stream every change out as an event.
Tools: Debezium (most popular, works with Postgres, MySQL, MongoDB, SQL Server, etc.), or cloud-native options like AWS DMS, Postgres logical replication, MySQL binlog replication.
Changes get published to a stream (commonly Kafka, but Debezium also supports simpler sinks like Kinesis, Pulsar, or even Postgres-to-Postgres direct outbound connectors).
A consumer applies those changes to the target table.

This is the gold standard for real-time sync across services because:

It doesn't touch your application code — it reads the DB log directly, so nothing gets missed even if the app crashes mid-write.
It preserves ordering per row/table.
It decouples "source of truth" from "things that need a copy of the data."
3. Application-level sync (dual writes / outbox pattern)

If CDC tooling is overkill for your scale, a common lighter-weight pattern is the transactional outbox:

When you write to table A, in the same transaction you also write an "event" row to an outbox table.
A separate worker polls the outbox table (or uses CDC just on that one small table) and pushes the event to wherever it needs to go (another table, another service, a queue).
This avoids the classic "dual write" bug where you update table A successfully, then the write to table B fails, and now they're silently out of sync.

Avoid doing dual writes directly in application code without an outbox — it's the most common source of "why did these tables drift apart" bugs in production.

4. Message queue for downstream consumers

Once changes are captured (via CDC or outbox), they typically flow through a broker:

Kafka — for high throughput, replay-ability, and multiple consumers.
RabbitMQ / SQS — simpler, fine for lower volume or single-consumer setups.

Each downstream table/service just consumes the stream and applies changes idempotently (using the row's primary key + a version/timestamp so replayed or out-of-order events don't corrupt data).

5. Handling conflicts & ordering
   Ordering: Partition your stream by the row's primary key (Kafka does this well) so all changes for a given row are processed in order by the same consumer.
   Conflicts: Use a last-write-wins strategy based on a timestamp/version column, or — if both sides can write independently — a proper conflict-resolution strategy (vector clocks, CRDTs) if you truly need multi-master writes. Most real-time sync setups avoid multi-master and keep one clear source of truth to sidestep this entirely.
   Idempotency: Always design consumers to safely re-apply the same event twice (e.g., UPSERT instead of INSERT), since at-least-once delivery is the norm with queues/CDC.