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