Re: Massive table (500M rows) update nightmare

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



If it is possible to lock this audit table exclusively (may be during
off peak hours) I would look into
- create new_audit_table as select col1, col2, col3 ... col9,
'new_col_value' from old_audit_table;
- create all indexes
- drop old_audit_table
- rename new_audit_table to old_audit_table

That is probably the fasted method you can do, even if you have to join
the "new_col_value" from an extra helper-table with the correspondig id.
Remeber, databases are born to join.


This has all been done before - the production team was crippled while they waited for this and the SECOND the table was available again, they jumped on it - even though it meant recreating the bare minimum of the indexes.

You could also try to just update the whole table in one go, it is
probably faster than you expect.

Possibly, but with such a large table you have no idea of the progress, you cannot interrupt it without rolling back everything. Worse, you have applications stalling and users wanting to know what is going on - is the OS and the DB/MVCC trashing while it does internal maintenance? Have you reached some sort of deadlock condition that you can't see because the server status is not helpful with so many uncommitted pending updates?

And of course, there is the file bloat.

--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux