public inbox for [email protected]  

help / color / mirror / Atom feed

From: Ed Sabol <[email protected]>
To: Gambhir Singh <[email protected]>
Cc: [email protected]
Subject: Re: Adding New Column with default value.
Date: Mon, 28 Apr 2025 21:13:22 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAHOGQfVmfJ5rpxfMSqJjTZGqN5ss-hdsCdTHsrxHAi2mUwWwmw@mail.gmail.com>
References: <CAHOGQfVmfJ5rpxfMSqJjTZGqN5ss-hdsCdTHsrxHAi2mUwWwmw@mail.gmail.com>

On Apr 28, 2025, at 1:24 PM, Gambhir Singh <[email protected]> wrote:
> Row Count - 50 Billion

I've never dealt with a table that huge personally, but my concern would be that ALTER TABLE will lock the table for a very long time. Is this in a production environment with active usage of this table? Just SELECTs or are we talking UPDATEs and INSERTs as well? If so, you might need to do something more complicated than just ALTER TABLE.

If you have enough disk space in the storage area for this database to have two identical copies of this 50 billion row table (with indexes!), you could make a copy of the table and either ALTER that copy or add the new column at the same time as making the copy and then, in a single transaction, rename the two tables to swap them. If you do it this way, the new table will replace the old table seamlessly without interrupting usage of the table. Somewhere in there, you might need to re-sync the two tables to make sure any rows that got inserted or updated while you were making the copy are incorporated into the new version of the table as well.

Just some initial thoughts on how I would accomplish this and things I would consider when deciding how to do it.

Good luck,
Ed

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Adding New Column with default value.
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox