Replicate PostgreSQL tables without primary keys

You can replicate a PostgreSQL table without adding a database primary-key constraint. Configure PostgreSQL to include the complete previous row in change events, then tell Artie which existing unique index to use to identify each row.

What you’ll need

A PostgreSQL table with no primary key
An existing unique index, or columns that you can make unique, to identify each row
Permission to alter the source table’s replica identity

Choose columns that uniquely identify every row. Artie uses the selected columns to merge updates and deletes in the destination. Do not use columns with duplicate values.

Configure the table

Create or identify a unique index

Artie requires the override columns to match a unique index on the source table. If you want to turn an existing column into an actual unique constraint, first run this check. It must return no rows:

-- Find duplicate values before adding the constraint.
SELECT external_id, count(*) AS duplicate_count
FROM public.orders
WHERE external_id IS NOT NULL
GROUP BY external_id
HAVING count(*) > 1;

Then run the following outside a transaction block. The index build does not block inserts, updates, or deletes. The final ALTER TABLE is brief and fails after five seconds rather than waiting on a conflicting lock.

-- Fail quickly instead of waiting on a conflicting lock.
SET lock_timeout = '5s';

-- Build the backing unique index without blocking normal writes.
CREATE UNIQUE INDEX CONCURRENTLY orders_external_id_key
ON public.orders (external_id);

-- Convert the index into an actual unique constraint.
ALTER TABLE public.orders
ADD CONSTRAINT orders_external_id_unique
UNIQUE USING INDEX orders_external_id_key;

For a composite identifier, create one unique index containing all of the columns you will select in Artie.

Set the table replica identity to FULL

Run the following as the table owner or another role with permission to alter the table:

ALTER TABLE public.orders REPLICA IDENTITY FULL;

REPLICA IDENTITY FULL makes PostgreSQL include the full previous row in update and delete events, which lets Artie use the override columns when the table has no primary key.

This setting can increase WAL volume for updates and deletes because PostgreSQL records the full old row. Monitor replication-slot usage after enabling it.

Add the table and set the primary key override in Artie

In the Artie dashboard, open your pipeline, then go to Edit > Tables. Add or select the table, open Table settings, and choose the Advanced settings tab.Enable Override primary keys and select the column or columns from the unique index. For the example above, select external_id.Keep the selected columns included in replication. Artie uses them as the table’s merge key in the destination.

Save, deploy, and verify

Save the table settings and deploy the pipeline. Verify an insert, update, and delete on the source table are reflected in the destination.

When to add a database primary key instead

Use a real PostgreSQL primary key when you control the schema and can add one safely. A primary key is the simplest long-term choice. Use the override workflow when you cannot change the table’s primary-key constraint but it already has a stable unique identifier.

Capacity pricingEnable capacity pricing for BigQuery to avoid having unexpected BigQuery costs.

⌘I

​What you’ll need

​Configure the table

​When to add a database primary key instead

What you’ll need

Configure the table

When to add a database primary key instead