No, a primary key is not strictly necessary to replicate inserts only using PostgreSQL built-in logical replication, provided you configure the publication with the
FOR TABLE clause and ensure the table has a unique index or constraint that can serve as a row identifier. However, relying on implicit row identification via physical TID (tuple ID) is highly discouraged for production systems because it leads to data corruption during updates or deletes if the underlying storage layout changes. For insert-only scenarios, logical replication works without a primary key if the publisher does not attempt to update or delete rows, but explicit uniqueness constraints are still recommended to maintain data integrity and prevent duplicate entries from causing conflicts in edge cases.
Understanding Logical Replication Requirements
PostgreSQL's logical replication operates at the row level by decoding Write-Ahead Log (WAL) records into logical changes. Unlike streaming replication, which replicates entire database clusters, logical replication allows you to select specific tables and operations. When you create a publication, you define what data is sent to subscribers. The core challenge arises when the subscriber needs to identify which row corresponds to a change. In standard replication involving updates or deletes, PostgreSQL requires a unique identifier to locate the exact row to modify. This is typically a primary key or a unique index.
For insert-only replication, the subscriber never needs to look up an existing row; it simply appends new data. Therefore, the strict requirement for a primary key is relaxed. You can publish a table without a primary key if you are confident that no updates or deletes will ever occur on that table. However, this approach carries significant risks. If you later add an update operation to the publication, the replication will fail because there is no unique key to identify the row being updated. Additionally, without a primary key, PostgreSQL may use the internal tuple ID (TID) as a fallback, which is not stable across reindexes or vacuum operations.
Configuring Insert-Only Publications
To set up logical replication for a table without a primary key, you must explicitly define the publication to include only the insert operation. This prevents any ambiguity regarding how rows are identified. You start by creating a publication on the publisher node using the
PUBLICATION command. You specify the table name and restrict the operations to
INSERT.
CREATE PUBLICATION pub_insert_only
FOR TABLE my_table
WITH (publish = 'insert');
This configuration tells PostgreSQL to send only INSERT events for
my_table to the subscribers. Since no UPDATE or DELETE events are generated, the subscriber does not need to resolve row identities based on keys. It simply receives the data payload and executes the insert statement. This method is effective for logging tables, audit trails, or staging areas where data is appended sequentially and never modified.
Setting Up the Subscriber Side
On the subscriber side, you must create a subscription that connects to the publisher. The subscriber table must exist before the subscription is created, and its schema must match the published table. If the table lacks a primary key on the publisher, it should also lack one on the subscriber to avoid schema mismatch errors during the initial sync.
CREATE SUBSCRIPTION sub_insert_only
CONNECTION 'host=publisher_host dbname=mydb user=replicator password=secret'
PUBLICATION pub_insert_only;
When the subscription is created, PostgreSQL performs an initial data copy. For insert-only publications, this copy process is straightforward because it only involves reading existing rows and inserting them into the subscriber. There is no complex conflict resolution required because no concurrent modifications are expected. Once the initial sync completes, the subscriber begins receiving real-time INSERT events.
Risks of Omitting Primary Keys
While technically feasible, omitting primary keys in logical replication introduces several operational risks. First, if you need to switch to full replication (including updates and deletes), you must first add a primary key or unique index to the table. This requires stopping the replication, altering the table structure, and resyncing the data, which can cause downtime. Second, without a primary key, you lose the ability to easily detect and handle duplicate inserts. If network issues cause duplicate messages, the subscriber might insert duplicate rows, leading to data inconsistency. Third, monitoring and debugging become more difficult. Without unique identifiers, it is hard to trace specific rows through the replication pipeline or diagnose why certain data is missing or duplicated.
For these reasons, even in insert-only scenarios, it is best practice to define a surrogate primary key or a natural unique key on the table. This ensures that your replication setup is robust, scalable, and easier to manage in the long term. If you are dealing with high-volume append-only logs, consider using partitioned tables with unique constraints on partition keys to balance performance and integrity.
Alternative Approaches for High-Volume Inserts
If your use case involves massive volumes of inserts where primary keys are impractical due to performance overhead, consider using asynchronous batch replication tools like pglogical or Londiste. These tools offer more flexibility in handling tables without strict unique constraints. Alternatively, you can implement application-level deduplication logic before sending data to the subscriber. This shifts the responsibility of data integrity from the database layer to the application layer, allowing you to bypass the limitations of logical replication while maintaining control over duplicate records.
Frequently Asked Questions
Can I add a primary key to a table already being replicated?
Yes, but you must stop the subscription first. Alter the table to add the primary key, then restart the subscription. Be aware that adding a primary key triggers a reindexing process which can be resource-intensive on large tables. Ensure the new key is unique to avoid conflicts during the resync phase.
What happens if I try to update a row in an insert-only publication?
The update will fail on the subscriber because the publication does not include UPDATE events. If you attempt to enable updates on such a table, PostgreSQL will throw an error indicating that the table lacks a suitable row identifier. You must add a primary key or unique index before enabling update replication.
Is logical replication slower than streaming replication?
Logical replication generally has higher CPU and I/O overhead because it decodes WAL records into logical changes. Streaming replication is faster as it copies raw page images. However, logical replication offers greater flexibility in filtering data and selecting specific tables, making it preferable for selective replication needs despite the performance cost.
How do I monitor logical replication lag?
Use the
pg_stat_replication view on the publisher and
pg_stat_subscription on the subscriber. These views provide metrics on delay, bytes received, and last applied transaction timestamp. Regularly monitoring these metrics helps identify bottlenecks in the replication pipeline before they impact data freshness.
Can I replicate a table with multiple columns as a composite key?
Yes, logical replication supports composite keys as row identifiers. Define a unique constraint on the combination of columns, and PostgreSQL will use this constraint to identify rows for updates and deletes. This is often more efficient than a single-column primary key for tables where natural keys consist of multiple attributes.