How to safely TRUNCATE and re-populate a table in PostgreSQL?

Question

One of the tables in my db has to be updated daily. A web server actively queries this table every 5 seconds. Now I can't simply UPDATE the table rows because of some constraints, so I need to clear all the rows and repopulate the table. How can I safely do this without affecting the functioning of web server? The repopulation is done by an other web service isolated from the web server. I am using Spring framework.

The table has approx. 170k rows and 5 columns.

Answer 1

Truncate and re-populate the table in a single transaction. The truncate isn't visible to concurrent readers, who continue to see the old data. Correction per @AlexanderEmelianov and the docs:

TRUNCATE is not MVCC-safe. After truncation, the table will appear empty to concurrent transactions, if they are using a snapshot taken before the truncation occurred. See Section 13.5 for more details.

so after the TRUNCATEing txn commits , concurrent txns started before the TRUNCATE will see the table as empty.

---

Any transaction attempting to write to the table after it's truncated will wait until the transaction doing the truncate either commits or rolls back.

BEGIN;

TRUNCATE TABLE my_table;

INSERT INTO my_table (blah) VALUES (blah), (blah), (blah);

COMMIT;

You can COPY instead of INSERT too. Anything in a normal transaction.

Even better, there's an optimisation in PostgreSQL that makes populating a table after a truncate faster than if you do it otherwise.