From: | "Vilson farias" <vilson(dot)farias(at)digitro(dot)com(dot)br> |
---|---|
To: | <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Deleting large amount of data. |
Date: | 2002-08-27 12:36:24 |
Message-ID: | 016e01c24dc6$5bf743f0$98a0a8c0@dgtac |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
);
From | Date | Subject | |
---|---|---|---|
Next Message | Elielson Fontanezi | 2002-08-27 13:03:37 | RES: [GENERAL] MSAcess databasse type X PostgreSQL database type |
Previous Message | Joe Conway | 2002-08-27 00:59:46 | Re: Preserving datatypes in dblink. |