Hacker News Reader

Comment by saddist0 | Hacker News Reader

Another common mistake I have seen: duplicating tables without the indexes.

This is not how it works, period.

    CREATE TABLE <abcv2> SELECT * FROM <abc> WHERE <>

People do it all the time, either to create a backup table, or deleting data in bulk, etc.

View on HN

If I'm creating a backup table -- as in, I'm going to do some complex nebulous operation that might screw up in ways I don't immediately foresee -- then I don't care at all about indexes or constraints. I want an immediately present copy of the data that I plan to never need but is there so I don't have to restore from DB backup + WAL. Creating the indexes is a waste of both server time and disk space.

If something goes pear-shaped or I really need them, I can create those indexes later.

Could you also mention what is the appropriate way to go about it?

Either put machinery in your schema migration tool to create indexes as a separate step, so they are easy to re-apply. This makes keeping indexes in sync between production and staging a lot easier. Or you can use "CREATE TABLE_B (LIKE TABLE_A WITH INDEXES);".

Make sure to create indices AFTER the data is in. Its way faster to create indices from data than create them empty and insert data.

Also make sure to set maintenance_work_mem high as it helps with index creation

Reply on news.ycombinator.com