Re: Logical Replication - Should Destination Table Columns Be Defined With Default Value

> On Nov 9, 2021, at 1:24 PM, Michael Lewis  wrote:
> 
> Is there any advantage to not defining the default on the replica? If it is not a static value and the publishing database will trigger row updates, I could see waiting to set the default until after the table re-write is done, but otherwise there doesn't seem to be any benefit to skipping column defaults on subscribers.

That's a schema design decision.  I can't really make recommendations in the abstract.  As a general rule, I would expect that the DDL executed on the publisher (such as the ALTER TABLE..ADD COLUMN..DEFAULT..) would be the same as that executed on the subscriber, unless there is a particular reason to want different behavior on the subscriber.

Wanting different behavior is not unreasonable.  For example, if the subscriber exists merely to archive data from the publisher, the subscriber might not bother creating indexes over that data, even if such indexes exist on the publisher. 

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company