Re: Best Practices for Managing Schema Changes Dynamically with libpq

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
adrian.klaver@aklaver.com