truncate in transaction blocks read access

From: Craig James <craig_james(at)emolecules(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: truncate in transaction blocks read access
Date: 2009-11-30 18:50:17
Message-ID: 4B1413E9.3000905@emolecules.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I have a million-row table (two text columns of ~25 characters each plus two integers, one of which is PK) that is replaced every week. Since I'm doing it on a live system, it's run inside a transaction. This is the only time the table is modified; all other access is read-only.

I wanted to use "truncate table" for efficiency, to avoid vacuum and index bloat, etc. But when I do "truncate" inside a transaction, all clients are blocked from read until the entire transaction is complete. If I switch to "delete from ...", it's slower, but other clients can continue to use the old data until the transaction commits.

The only work-around I've thought of is to create a brand new table, populate it and index it, then start a transaction that drops the old table and renames the new one.

Any thoughts?

Thanks,
Craig

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Joshua D. Drake 2009-11-30 19:31:43 Re: truncate in transaction blocks read access
Previous Message Denis Lussier 2009-11-30 17:12:21 Re: Server Freezing