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
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 |