Re: Deleting large amount of data. - Mailing list pgsql-admin

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?

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
);