Re: VACUUM FULL not working with persistent connections in - Mailing list pgsql-general

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

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