Feedback-ID: i76984098:Fastmail
Message-ID: <fce25846-c678-4237-b902-18de50ce96d7@aklaver.com>
Date: Tue, 3 Dec 2024 12:50:54 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Best Practices for Managing Schema Changes Dynamically with libpq
To: Sasmit Utkarsh <utkarshsasmit@gmail.com>,
 pgsql-general <pgsql-general@postgresql.org>,
 pgsql-general@lists.postgresql.org
References: <CAM-5MT2gc+=tm5ErdoEyHDef3NzNL2UbAkrykdca9=1VJZi4fg@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAM-5MT2gc+=tm5ErdoEyHDef3NzNL2UbAkrykdca9=1VJZi4fg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/fce25846-c678-4237-b902-18de50ce96d7%40aklaver.com>
Precedence: bulk

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