PostgreSQL tips and tricks

Re:PostgreSQL tips and tricks by ilikepi on Sunday May 29, 2011 @05:24PM (#2597640)

While I agree with the second tip ("Replace a live table with DROP TABLE/ALTER TABLE") for ad-hoc stuff, its big disadvantage is that it requires knowledge of the indexes on the table. If you have one or more scripts to maintain that use this method on a particular table, and the indexes are changed in an unrelated update to your table structure, you have to make sure those changes are also reflected in your scripts.

Oracle allowed you to disable the indexes on a table (something like 'ALTER INDEX foo DISABLE'), which allowed them to become stale when data in the table was modified. You could do this right before a big import operation for example, and you could then rebuild (a different 'ALTER INDEX' syntax) them when the operation completed.

PostgreSQL doesn't appear to have an equivalent at the moment.

edit: wording

[ Parent | on HN ]

Re:PostgreSQL tips and tricks by avar on Sunday May 29, 2011 @05:26PM (#2597645)

Doesn't PostgreSQL support something like:

    RENAME TABLE current TO current_old,
                 current_new TO current;

Which is the common pattern of atomically swapping out tables in MySQL with zero downtime.

[ Parent | on HN ]

Re:PostgreSQL tips and tricks by lentil on Sunday May 29, 2011 @06:19PM (#2597767)

Almost all DDL is transactional in PostgreSQL, so the usual equivalent to the above would be:

  begin;
  alter table current rename to current_old;
  alter table current_new rename to current;
  commit;

Transaction-safe DDL is a very handy thing indeed.

[ Parent | on HN ]