Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec> wrote:
> Tomeh, Husam <HTomeh(at)facorelogic(dot)com> wrote:
>> We have a huge table with hundred million records. We need to
>> increase the size of an existing column with varchar type, and it
>> has been running for more than 12 hours.
>> 3) Some folks referred to directly updating Postgres internal
>> tables (pg_attribute) which takes seconds to make the column
>> change happen. How safe if this and would potentially cause any
>> corruption?
>>
>
> no, that's insane
Not really; but it is something to approach with great caution.
With this approach you don't need to drop or rebuild the index, and
it's all done, as you say, in a matter of seconds.
Thoughts:
* Don't over-generalize the technique. Going from a varchar(n) to
a varchar(larger-n) is safe. Most changes aren't.
* Test, test, test. Copy your schema to a test database. Look at
the pg_attribute row. Use ALTER TABLE to make the change. Then
look at it again. Restore the schema to the starting point and try
it with a direct update as a database superuser. Write a query to
SELECT the row which will be updated using table name and column
name (since oid might not match between your copy and the real
database), then modify the SELECT to get to your UPDATE. Confirm
that it made exactly the right change to the right row. If you can
arrange a copy of the complete database, or some reasonable test
facsimile, test there; then make sure your application works as
expected.
* Have a good backup. Confirm that it can actually be restored;
otherwise you'll be doing this trapeze act without a net.
We've done this successfully with large production databases, but
we've been very careful. If you're not, you could corrupt your
database.
Insane, no. If it doesn't make you nervous enough to take great
care -- well, *that* would be insane.
-Kevin