Date: Fri, 6 Dec 2024 03:06:03 +0100
From: Erik Wienhold <ewie@ewie.name>
To: Sasmit Utkarsh <utkarshsasmit@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>, 
	pgsql-general@lists.postgresql.org
Subject: Re: Best Practices for Managing Schema Changes Dynamically with libpq
Message-ID: <3a73e644-1707-4640-a271-c5c8300f61ec@ewie.name>
References: <CAM-5MT2gc+=tm5ErdoEyHDef3NzNL2UbAkrykdca9=1VJZi4fg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <CAM-5MT2gc+=tm5ErdoEyHDef3NzNL2UbAkrykdca9=1VJZi4fg@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/3a73e644-1707-4640-a271-c5c8300f61ec%40ewie.name>
Precedence: bulk

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