truncate in transaction blocks read access - Mailing list pgsql-performance

From Craig James
Subject truncate in transaction blocks read access
Date
Msg-id 4B1413E9.3000905@emolecules.com
Whole thread Raw
Responses Re: truncate in transaction blocks read access  ("Joshua D. Drake" <jd@commandprompt.com>)
Re: truncate in transaction blocks read access  ("Joshua D. Drake" <jd@commandprompt.com>)
List 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
replacedevery week.  Since I'm doing it on a live system, it's run inside a transaction.  This is the only time the
tableis 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
atransaction, 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
thatdrops the old table and renames the new one. 

Any thoughts?

Thanks,
Craig


pgsql-performance by date:

Previous
From: Waldomiro
Date:
Subject: Server Freezing
Next
From: "Joshua D. Drake"
Date:
Subject: Re: truncate in transaction blocks read access