Hacker News new | ask | show | jobs
by saddist0 779 days ago
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.
2 comments
da_chicken 778 days ago
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.

link
watt 779 days ago
Could you also mention what is the appropriate way to go about it?
link
keeperofdakeys 779 days ago
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);".
link
kroolik 778 days ago
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

link