CODEWITHSUNDEEP
postgresql
p_piorkowski
p_piorkowski

Reputation: 127

Slow access after many deletion in Postgres

I have a question. I have database in which one table is cleared and filled up with data again once a day. After many operations like this access to this table is much slower.

I cleared this table with command DELETE FROM table_name and then COPY INTO table_name... - I didn't use VACCUM. Now I have dropped table_name and create it again -> performance is again on high level.

Now instead of DELETE FROM ... I use TRUNCATE table_name and I'm wondering if it will work better.

Maybe problem is with index (this one which is created during creating table)?

I'm using PostgreSQL 8.1.23.

Upvotes: 0

Views: 280

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324751

8.1 is old, unsupported, has tons of limitations in vacuum, and should be upgraded to a modern version as soon as practical. It isn't just an in-place upgrade, you must do a dump and reload and you need to read the documentatation - both the upgrade docs and the upgrade notes section of the release notes for the major versions 8.2, 8.3, 8.4, 9.0, 9.1 and 9.2.

Using TRUNCATE instead of DELETE should indeed help. You should also DROP the index before loading the table and re-CREATE it afterwards. See the manual.

In a vaguely modern version you should also do the TRUNCATE and COPY in the same transaction, as this allows PostgreSQL to perform several useful optimisations.

Upvotes: 3

Related Questions