Is `ALTER TABLE mytable REPLICA IDENTITY FULL;` a safe operation for a high volume postgres database?

user_8755064 0
Viewed 5022

I am working with debezium and a Postgres database. The database contains several tables with TOAST (The Oversized-Attribute Storage Technique) columns. To handle unchanged Postgres TOAST column values, one approach is to set the REPLICA IDENTITY of a table to FULL. However, I am not sure if running ALTER TABLE mytable REPLICA IDENTITY FULL could cause downtime on the database or it will be a safe operation. The current value for REPLICA IDENTITY is DEFAULT.

postgresql-9.6 debezium alter-table postgresql
1 Answers

Changing the replica identity will not cause down time, but it requires a short ACCESS EXCLUSIVE lock on the table, which can be a problem if you have long running transactions that involve the table.

However, I don't see the point of the exercise. This has nothing to do with TOAST; the only effect is that the replicated UPDATE and DELETE statements will have all columns in the WHERE condition rather than just the primary key, which will increase the WAL volume and be bad for performance.

REPLICA IDENTITY FULL is mostly a crutch for tables without a primary key, which is to be avoided anyway.

user_64643080
Related