You need to feed a separate reporting database with just three tables out of a 400-table production database. The reporting database must also hold its own locally-created summary tables and extra indexes tuned for analysts. Would you use physical or logical replication, and what does that choice commit you to?
answer
- Physical = whole cluster, read-only, identical layout → fails all three needs
- Logical = publication/subscription, chosen tables, writable target
- Cost: row identity, manual DDL, conflicts, slot retention
- Extra indexes on target = extra apply cost per row
- Subset copy is never a promotable failover target
basics
~20 sLogical replication. Physical copies the entire cluster byte-for-byte and keeps the target read-only, so neither selective tables nor local tables and extra indexes are possible. Logical costs you: manual DDL coordination, row identity on the three tables, conflict handling, and slot/log retention on the source.
solid answer
~1 min**Logical**, because two requirements rule physical out outright: - **Selectivity.** A physical standby is a byte-for-byte clone of the whole cluster; you cannot replicate three tables out of 400. - **A writable target.** The physical standby is read-only while in recovery, so local summary tables and analyst-tuned indexes are impossible. A logical subscriber is a normal writable database that happens to receive some rows. What that commits you to: - **Row identity** on the three source tables — a primary key or designated unique index — or updates and deletes cannot be applied. - **Manual DDL coordination.** Schema changes do not propagate; adding a column must be applied on the subscriber first (or in a compatible order) or apply breaks. - **Conflict management.** The target is writable, so a local write colliding with a replicated one stalls the subscription until resolved. - **Retention risk.** The source keeps change log on the subscriber's behalf while it is behind or disconnected; a dead subscriber can grow the source's disk until it fills. - **Not a failover target.** It holds a subset, so it can never be promoted to replace production. I would also confirm the reporting write rate is within logical apply throughput, since per-row apply is far costlier than physical replay.
go deeper
Choose logical and justify it with selectivity and a writable target; knowing those two facts is enough at this level.
Add the obligations — primary keys for identity, manual DDL ordering, and the fact that the target's extra indexes cost apply time.
Emphasise the operational hazards: subscription stalls from conflicts or schema drift, and retained-log growth on the source when the subscriber is down.
Position it in the estate: logical for the reporting feed, a separate physical standby for HA, plus explicit ownership of DDL choreography, permissions on replicated tables, and a re-seed threshold.
## Reading the requirements Three constraints in the scenario decide it before any performance argument: 1. **Three tables out of 400** — a scope constraint. 2. **Local summary tables on the target** — a writability constraint. 3. **Extra indexes tuned for analysts** — a divergent-schema constraint. Physical replication fails all three by construction. It replays storage-level change records onto a byte-identical copy of the source's files, so its scope is the entire cluster, its target must stay read-only to keep those files consistent with the incoming records, and its physical layout — indexes included — is exactly the source's. There is no configuration that relaxes any of this; the mechanism does not admit it. Logical replication satisfies all three by construction. It decodes the source's log into row-level change events for a chosen set of tables and applies them as ordinary DML on the target. The target is a normal, fully writable database that happens to receive some rows from elsewhere; it may hold anything else it likes, indexed however it likes. ## The publication/subscription model The usual shape is: the source defines a **publication** — a named set of tables (and often which operation types, sometimes a row filter or column list) — and the target defines a **subscription** that connects, requests an **initial snapshot** of the published tables, and then streams ongoing changes from the point that snapshot was taken. That two-phase behaviour matters operationally: the initial copy of a large table can take a long time, and while it runs, changes accumulate on the source that must be retained and then applied afterwards. ## What you sign up for **Row identity.** To apply an UPDATE or DELETE the subscriber must find the row the event refers to. The stream carries an identifying value set — by default the primary key. A table without one either cannot replicate those operations at all, or must be configured to log the entire old row for matching, which forces the subscriber to search without a key. Before choosing logical, confirm the three tables have primary keys; if not, that is work item number one. **Manual DDL coordination.** Schema changes are not replicated. Adding a nullable column on the source is harmless until the first row event carrying it arrives at a subscriber that lacks it, at which point apply fails and the subscription stalls. The discipline is: additive changes on the subscriber first, then the source; removals in the reverse order; and destructive changes coordinated with a pause. This is a permanent process obligation, not a setup step, and it is the most common cause of logical-replication incidents. **Conflicts.** Because the subscriber is writable, nothing stops an analyst — or a well-meaning ETL job — from inserting a row whose key later arrives from the source. The typical outcome is a unique-violation that halts apply for the whole subscription until someone intervenes. Guard against it with permissions: the replicated tables should not be writable by anyone on the target except the replication role. **Retention on the source.** While the subscriber is behind or disconnected, the source must retain the change log the subscriber has not yet consumed — usually tracked by a replication slot or equivalent. That is a feature (no data loss over a brief outage) and a hazard (a subscriber that is switched off for a week can fill the source's disk and take production down). Monitor retained-log size as a first-class metric, and have a documented threshold at which you drop the subscription and re-seed rather than let the source run out of space. **Throughput.** Physical replay writes pages; logical apply executes real DML with index maintenance, constraint checks and its own logging. Reporting targets often make this worse by carrying *more* indexes than the source, since every extra index is extra work on every applied row. Sanity-check the source's write rate against that: three tables receiving modest OLTP traffic is fine; three tables receiving a nightly bulk rewrite of millions of rows may not be. **It is not a failover target.** A subset copy can never be promoted to replace production. If the estate also needs high availability, that is a separate physical standby. Running both is completely normal, and this scenario is a textbook example of why. ## The shape of the answer A strong answer picks logical immediately, cites *which* requirement kills physical (selectivity plus writability, not vague flexibility), and then volunteers the obligations — identity, DDL discipline, conflict prevention, retention monitoring — because those are what a team actually gets paged about. Mentioning that the reporting database still needs its own backup strategy, and is not covered by production's HA, rounds it out.
- What breaks first if a developer adds a NOT NULL column to a published table on the source and forgets the subscriber?The subscriber keeps working until the first row event that includes the new column arrives, then apply fails because the target table has no such column, and the whole subscription stalls at that position. Lag then grows and the source begins retaining change log on the subscriber's behalf. The fix is to add compatible columns on the subscriber first, which is why additive-on-target-first is the standard ordering rule.
- How do you stop the reporting database's own users from breaking replication?Restrict write privileges on the replicated tables to the replication role alone, so analysts and local jobs cannot insert or update rows that would later collide with an incoming event. Local summary tables live in a separate schema that users can write freely. This turns a class of subscription-halting conflicts into an ordinary permission error at the moment of the mistake.
saying these in an interview costs you the question
- Proposing a physical standby and 'just ignoring' the tables you don't need
- Believing a physical standby can be made writable while still replicating
- Forgetting that the source retains change log for a disconnected subscriber and can fill its disk
- Assuming schema changes flow to the subscriber automatically
- Treating the reporting subscriber as a disaster-recovery copy of production