Best way to update the content of large lookups tables In our application we have a about 20 tables that contain lookup information (read only data that the application doesn't modify).

Nightly we'd like to update this information by deleting all the content and running a script that run a series of inserts and updates. This script could be a couple of million lines long.

I was thinking to run this within a transaction. 

What behaviour should I expect from the database? Will the tables be updated at the end of the transaction without downtime?

Is there something I should be aware of when running large transactions?

The database I'm using is Postgres 9.3

Thanks.
Assuming that the script contains only INSERTs, UPDATEs and DELETEs to the target table(s), and that the app never attempts row locks (SELECT ... FOR UPDATE/SHARE) or DML on those tables, then the script shouldn't affect the app except for the increased load on the DB server.

When the script commits, the changes will instantly become visible to new statements (if the transaction is in READ COMMITTED isolation) or transactions (if they're in SERIALIZABLE isolation).

However...


  Nightly we'd like to update this information by deleting all the content and running a script that run a series of inserts and updates. This script could be a couple of million lines long.


You should use COPY for this instead of a script full of inserts and updates, if possible.

Just:


DELETE FROM mytable;
VACUUM mytable;
COPY mytable FROM 'inputfile.csv' WITH (FORMAT CSV)


or similar.

It will be a lot faster. Because of the next point, you might want to load several smaller files though.


  Is there something I should be aware of when running large transactions?


Long running data modifying transactions prevent VACUUM from cleaning up rows in other tables that they might be able to "see". This can create quite a lot of table bloat.

If you use READ COMMITTED isolation and stick to individual statements that don't take an extremely long time you won't have a problem, because the snapshot that controls which rows the transaction can see gets updated after each statement.

It's mostly a concern if you're using cursors, working in SERIALIZABLE isolation, or running extremely long-running individual data modifying statements.