Re: Best Practices for Managing Schema Changes Dynamically with libpq

On 2024-12-03 18:43 +0100, Sasmit Utkarsh wrote:
> I am working on a project that uses libpq along with C language to interact
> with PostgreSQL, and we face challenges with managing schema changes
> dynamically in production while avoiding downtime. Specifically, we need
> guidance on handling table structure changes/additions without tightly
> coupling these changes to application updates.
>
> *Current Approach:*
> Schema changes are deployed first, followed by application updates to align
> with the new structure.
>
> *Challenges:*
> Ensuring application stability during the transitional phase when the
> schema and code are not fully in sync.
> Handling table structure changes (e.g., adding new columns) dynamically
> without requiring immediate code changes.

What you're looking for is the "Expand and Contract" pattern[1][2]. The
transitional phase between expand and contract has to support both old
and new code until the old code is migrated as well. How you keep the
schema compatible with the old code for some time depends on the kind of
schema changes. Some use cases from the top of my head:

1) expand: add unconstrained columns
transition: adapt code to use new columns
contract: add constraints

2) expand: rename tables/columns
transition: add (updatable) views that expose the old names until the
code is adapted to the new names
contract: drop views

3) expand: add columns with constraints
transition: backfill new columns with triggers
contract: drop triggers

[1] https://www.tim-wellhausen.de/papers/ExpandAndContract/ExpandAndContract.html
[2] https://martinfowler.com/articles/evodb.html#everything_refactoring

--
Erik