public inbox for [email protected]
help / color / mirror / Atom feedBest Practices for Managing Schema Changes Dynamically with libpq
4+ messages / 4 participants
[nested] [flat]
* Best Practices for Managing Schema Changes Dynamically with libpq
@ 2024-12-03 17:43 Sasmit Utkarsh <[email protected]>
2024-12-03 18:10 ` Re: Best Practices for Managing Schema Changes Dynamically with libpq Ron Johnson <[email protected]>
2024-12-03 20:50 ` Re: Best Practices for Managing Schema Changes Dynamically with libpq Adrian Klaver <[email protected]>
2024-12-06 02:06 ` Re: Best Practices for Managing Schema Changes Dynamically with libpq Erik Wienhold <[email protected]>
0 siblings, 3 replies; 4+ messages in thread
From: Sasmit Utkarsh @ 2024-12-03 17:43 UTC (permalink / raw)
To: pgsql-general; [email protected]
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?
How can we efficiently handle table additions/updates while keeping the
application and database in sync dynamically?
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Best Practices for Managing Schema Changes Dynamically with libpq
2024-12-03 17:43 Best Practices for Managing Schema Changes Dynamically with libpq Sasmit Utkarsh <[email protected]>
@ 2024-12-03 18:10 ` Ron Johnson <[email protected]>
2 siblings, 0 replies; 4+ messages in thread
From: Ron Johnson @ 2024-12-03 18:10 UTC (permalink / raw)
To: pgsql-general
On Tue, Dec 3, 2024 at 12:44 PM Sasmit Utkarsh <[email protected]>
wrote:
[snip]
> How can we efficiently handle table additions/updates while keeping the
> application and database in sync dynamically?
>
Enumerate all relevant column names in SELECT and INSERT statements. That
way, the application still works when you add columns or alter(*) data
types. Dropping columns will still break your app.
*Altering to from a numeric type to a text type might still kill your app,
if it can't convert the string into your app's int or float type..
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Best Practices for Managing Schema Changes Dynamically with libpq
2024-12-03 17:43 Best Practices for Managing Schema Changes Dynamically with libpq Sasmit Utkarsh <[email protected]>
@ 2024-12-03 20:50 ` Adrian Klaver <[email protected]>
2 siblings, 0 replies; 4+ messages in thread
From: Adrian Klaver @ 2024-12-03 20:50 UTC (permalink / raw)
To: Sasmit Utkarsh <[email protected]>; pgsql-general; [email protected]
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]
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Best Practices for Managing Schema Changes Dynamically with libpq
2024-12-03 17:43 Best Practices for Managing Schema Changes Dynamically with libpq Sasmit Utkarsh <[email protected]>
@ 2024-12-06 02:06 ` Erik Wienhold <[email protected]>
2 siblings, 0 replies; 4+ messages in thread
From: Erik Wienhold @ 2024-12-06 02:06 UTC (permalink / raw)
To: Sasmit Utkarsh <[email protected]>; +Cc: pgsql-general; [email protected]
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-12-06 02:06 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-12-03 17:43 Best Practices for Managing Schema Changes Dynamically with libpq Sasmit Utkarsh <[email protected]>
2024-12-03 18:10 ` Ron Johnson <[email protected]>
2024-12-03 20:50 ` Adrian Klaver <[email protected]>
2024-12-06 02:06 ` Erik Wienhold <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox