Jaime Casanova <jcasanov@xxxxxxxxxxxxxxxxxxx> wrote: > Tomeh, Husam <HTomeh@xxxxxxxxxxxxxxx> 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 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
