I tried to switch a system that was using PostgreSQL 7.1.3 under Red Hat Linux
7.2 (PIII 800MHz, 768MB RAM) to PostgreSQL 7.2.3. The problem is that VACUUM
FULL ANALYZE in v7.2.3 is not working as VACUUM ANALYZE did in v7.1.3 (I
thought that they did the same thing).
Currently we have a table that has approximately 30 rows that are being
updated very frequently by a process that keeps several persistent
connections to the database. Each row is updated every 15 seconds (max.). The
description of this table is the following:
Table "merchant_client"
Attribute | Type | Modifier
---------------------+--------------------------+----------
merchant_id | integer | not null
last_access_time | timestamp with time zone |
broker_ip_address | character varying(15) |
version | character varying(20) | not null
broker_port | integer |
merchant_ip_address | character varying(15) |
merchant_port | integer |
connection_type | character varying(20) | not null
polling_frequency | integer |
Index: merchant_client_pkey
Constraints: ("version" = '1.0'::"varchar")
((connection_type = 'Pull'::"varchar") OR (connection_type =
'Push'::"varchar"))
The problem is that Postgres seems to be making a logical delete for each row
that is updated (i.e. it seems to be doing an INSERT followed by a DELETE),
so a lot of cruft is created on the database. We need to avoid this because
there are other processes that are permanently querying this table (normally
needing a sequential scan over the table) and the performance of the queries
gradually decreases with each update. We run a VACUUM ANALYZE every 30
minutes on this table and with v7.1.3 the system performs acceptably. With
v7.2 (we tried v7.2.1, v7.2.2 and v7.2.3) if we run a VACUUM FULL ANALYZE on
the table almost none of the deleted rows are really vacuumed until the
clients disconnect. When using the VERBOSE modifier, I noticed that sometimes
some of the rows were effectively removed, but these we normally very few
(less than 10%). We even tried a VACUUM FULL FREEZE ANALYZE without success.
Does anybody know if there is a way to have the v7.1.3 behaviour back? Is
there anything we can do to force the removal of deleted rows from a table in
v7.2?
Thanks.
--
Juan Jose Comellas
(juanjo@comellas.com.ar)
