public inbox for [email protected]  

help / color / mirror / Atom feed

From: Erik Wienhold <[email protected]>
To: Sasmit Utkarsh <[email protected]>
Cc: pgsql-general <[email protected]>
Cc: [email protected]
Subject: Re: Best Practices for Managing Schema Changes Dynamically with libpq
Date: Fri, 6 Dec 2024 03:06:03 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAM-5MT2gc+=tm5ErdoEyHDef3NzNL2UbAkrykdca9=1VJZi4fg@mail.gmail.com>
References: <CAM-5MT2gc+=tm5ErdoEyHDef3NzNL2UbAkrykdca9=1VJZi4fg@mail.gmail.com>

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

view thread (4+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Best Practices for Managing Schema Changes Dynamically with libpq
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox