public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: Sasmit Utkarsh <[email protected]>
To: pgsql-general <[email protected]>
To: [email protected]
Subject: Re: Best Practices for Managing Schema Changes Dynamically with libpq
Date: Tue, 3 Dec 2024 12:50:54 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAM-5MT2gc+=tm5ErdoEyHDef3NzNL2UbAkrykdca9=1VJZi4fg@mail.gmail.com>
References: <CAM-5MT2gc+=tm5ErdoEyHDef3NzNL2UbAkrykdca9=1VJZi4fg@mail.gmail.com>

On 12/3/24 09:43, Sasmit Utkarsh wrote:
> Dear PostgreSQL Community Team,
> 
> 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.
> 
> *Questions:*
> Are there recommended best practices for managing such schema changes 
> with libpq?

I use Sqitch(https://sqitch.org/). You have to squint but it is libpq, 
of a sort, as it uses psql to do its changes.

> How can we efficiently handle table additions/updates while keeping the 
> application and database in sync dynamically?

There is way too many variations that enter into the above to give a 
complete concrete answer in anything less then a short book.

My general rule for this is to create a map of the process in outline 
form. Personally I still think better on paper and I pull out a legal 
pad and pencil and write out a work flow that goes from where I am to 
where I want to be. This starts with the 10000 foot view that I then 
drill down in to get the specific actions. I use a pencil as the drill 
down process often uncovers flaws in the 10000 foot view. This by the 
way was a method my 7th grade math teacher taught the class back way 
back when.

> 
> I would appreciate any guidance, patterns, or examples that can help us 
> implement a robust solution.
> 
> Thank you for your time and support!
> 
> Regards,
> Sasmit Utkarsh
> +91-7674022625

-- 
Adrian Klaver
[email protected]

view thread (4+ messages)  latest in thread

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