Re: VACUUM FULL not working with persistent connections in

7.2 VACUUM FULL is the same as VACUUM on 7.1.X. They should behaving
the same.

---------------------------------------------------------------------------

Juan Jose Comellas wrote:
> 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(at)comellas(dot)com(dot)ar)
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073