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(dot)klaver(at)aklaver(dot)com