Re: Deleting large amount of data.

On Tue, 27 Aug 2002, Vilson farias wrote:

>
>
> Thanks for ask my post, Mr. Szabo.
>
>    Thanks for worry about my performance problems, but they are natural for
> my database, once my hardware isn't a Ferrari and my table has more than 2
> milion of touples and it's related with a lot of other very high populated
> tables. I realized I can keep no longer than 6 mounths of data in these
> tables, so I would like to remove older data every night. I didn't write any
> code yet and I can't start testing the best way in my databases, so I'm
> posting here to get some advices. I'm worried about performance problems
> that could raise when a start removing 12000 touples in a single transaction
> once I don't know how PostgreSQL works internally. Can a single transaction
> deal with so many data without problem or overloading transaction buffers
> (or something like that) or do I need to split deletion process in smaller
> sets of data?

12000 rows shouldn't be a big deal.  I assume you're adding about the same
number per day basically, so it's a rotating window.

The things to be sure of are that the column you're deleting by is indexed
(using the definition below this means dt_inicial or dt_final has to be
part of the constraint).  You should check with explain to make sure it's
using the index (if it says it's not, you should analyze the table and try
again).  Also, if you're running 7.2, you should probably do a vacuum
afterwards (without full) and after say a couple of weeks of this, if
you notice your disk space is increasing, there are some configuration
options which can help (look for the free space map options in the
configuration file).

>
> My main table wich has more than 2milion of touples :
> CREATE TABLE cham_chamada (
>        cod_bxs              integer NOT NULL,
>        chave                integer NOT NULL,
>        identificacao        integer NOT NULL,
>        dt_inicial           timestamp  NOT NULL,
>        indicadora           integer,
>        cod_categoria        integer,
>        identidadea          varchar(20),
>        dt_final             timestamp ,
>        juntor               integer,
>        indicadorb           integer,
>        identidadeb          varchar(20),
>        flg_chamada          char(1),
>        flg_liberacao        char(1),
>        temp_total           integer,
>        ddd                  smallint,
>        cod_liberjuntor      integer,
>        cod_resultcham       integer,
>        cod_fds              integer,
>        cifra_origem         integer,
>        csp                  char(2),
>        conta                integer,
>        ramal                varchar(20),
>        ultimo_ramal         varchar(20),
>        CONSTRAINT XPKcham_chamada PRIMARY KEY (dt_inicial, cod_bxs, chave,
> identificacao)
>
> )
> ;
>
> CREATE INDEX XIE1cham_chamada ON cham_chamada
> (
>        dt_final                       ,
>        identidadea
> );
>
> CREATE INDEX XIE2cham_chamada ON cham_chamada
> (
>        dt_final                       ,
>        flg_chamada
> );