Thread: DELETE or TRUNCATE?
Hi! I have a process that replaces the contents of a table. The canonical data store is somewhere else. At the moment, the importlooks like this: CREATE TEMPORARY TABLE markets_import( LIKE x INCLUDING ALL ); COPY markets_import FROM STDIN; ... \. -- COPY a bunch of other tables BEGIN; TRUNCATE markets; INSERT INTO markets SELECT * FROM markets_import; -- do the other tables here as well COMMIT; VACUUM ANALYZE markets; Sometimes, the import process will block on the TRUNCATE because some other query already holds a ShareLock on the marketstable, because it's doing a long report. I'm guessing TRUNCATE prevents the use of MVCC, and DELETE would be betterin this case? Especially since I'm doing a VACUUM ANALYZE at the end anyway. Thanks! François
Attachment
Hi,
Yes,DELETE would be better this case.
The TRUNCATE operation required AccessExclusiveLock on Table before perform TRUNCATE operation.
So,if you the table size is bing,it is batter to do ANALYZE <Table> after report and VACUUM <table> non-peak(less business) hours.
Regards,
Chiru
On Thu, May 16, 2013 at 7:52 PM, François Beausoleil <francois@teksol.info> wrote:
Hi!
I have a process that replaces the contents of a table. The canonical data store is somewhere else. At the moment, the import looks like this:
CREATE TEMPORARY TABLE markets_import( LIKE x INCLUDING ALL );
COPY markets_import FROM STDIN;
...
\.
-- COPY a bunch of other tables
BEGIN;
TRUNCATE markets;
INSERT INTO markets SELECT * FROM markets_import;
-- do the other tables here as well
COMMIT;
VACUUM ANALYZE markets;
Sometimes, the import process will block on the TRUNCATE because some other query already holds a ShareLock on the markets table, because it's doing a long report. I'm guessing TRUNCATE prevents the use of MVCC, and DELETE would be better in this case? Especially since I'm doing a VACUUM ANALYZE at the end anyway.
Thanks!
François