Postgres' logical replication - adapt db-sync tables when no primary key is defined

[CodiePP]

OS Your OS: Linux

Versions The db-sync version (eg cardano-db-sync --version): PostgreSQL version: 13.2.0.1

Build/Install Method The method you use to build or install cardano-db-sync: using nix

Run method The method you used to run cardano-db-sync (eg Nix/Docker/systemd/none): shell script

Additional context PostgreSQL logical replication: https://www.postgresql.org/docs/current/logical-replication.html

Problem Report PostgreSQL now supports logical replication between database instances. This is much finegrained and better tunable than the byte-by-byte replication. This requires that all tables have a primary key defined, as this is the identity in comparing equivalence between copies of a table. As table "public.reward" does not have a defined primary key, one has to define the whole row as identity:

ALTER TABLE public.reward REPLICA IDENTITY FULL;

Could this be added to the schema, please?

[kderme]

Is this necessarily added in the default schema, or can it be added in custom schemas for instances that use replication? A new schema file can be added eg at schema/migration-3-9998-20240419.sql with the command above.

We may end up adding it by default, I'm just checking the options we have.

[kderme]

Alternatively an autoincremental primary key could be added in a similar way. This is not so trivial though, as it may affect the queries that db-sync performs.

For some context, this id was removed because it wasn't necessary to db-sync, while it requires multiple GB of disk space.

[CodiePP]

I would not add back the primary key as this would unnecessary increase the size of the table. The db already contains quite a number of indexes that are huge. I think it would not hurt to add this small change in the schema. It is only a hint to the replication system.