Received: from dv.kp.dlr.de (dv.kp.dlr.de [129.247.97.1]) by hub.org (8.10.1/8.10.1) with ESMTP id e7HFURm13722 for ; Thu, 17 Aug 2000 11:30:27 -0400 (EDT) Received: from sea-gw.sea.com (yyy.kp.dlr.de [129.247.113.223]) by dv.kp.dlr.de (8.10.1/8.10.1) with ESMTP id e7HFUPU63860 for ; Thu, 17 Aug 2000 17:30:25 +0200 Received: from joebox.sea-gmbh.com (joebox.sea.com [192.168.0.20]) by sea-gw.sea.com (8.7.4/8.7.1) with ESMTP id RAA14238 for ; Thu, 17 Aug 2000 17:30:24 +0200 Message-Id: <4.3.2.7.0.20000817172909.00af93e0@192.168.0.1> X-Sender: joe@192.168.0.1 X-Mailer: QUALCOMM Windows Eudora Version 4.3.2 Date: Thu, 17 Aug 2000 17:30:24 +0200 To: pgsql-sql@postgresql.org From: Joerg Hessdoerfer Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Archive-Number: 200008/161 Hi! At 15:50 17.08.00 +0100, you wrote: > Isn't easier to reduce the table every day and make a daily vacuum which only > lasts a few seconds? Well, sounds simple, but I still have some headaches here: a) Full Data must be available a month or so (OK, that could be done by COPYing the insert table into an offline table) b) I would prefer near-to zero downtime - else the client (a near real-time app) would have to do things like determine server status, buffering etc. pp., und would get considerably more complex. Joerg Hessdoerfer wrote: >> Hi! >> >> I have an application, where I have to insert data into a table at several >> rows per second, 24 hours a day, 365 days a year. >> >> After some period (a week, maybe a month) the data will be reducted to some >> degree and deleted from the table. >> >> As far as I understood, I would have to use VACUUM to really free the table >> from deleted rows - but VACUUM (esp. on a table with several million rows) >> takes some time and prevents me from inserting new data. >> >> Now, I thought I could just rename the table, inserting into a temp table, and >> switch the tables back after VACUUMing. Ideally, this should work unnoticed >> (and thus without prog. effort) on the client (inserter) side. >> >> Question: would it work to use a transaction to perform the rename? >> >> i.e.: continuous insert into table 'main' from client. >> >> From somewhere else, execute: >> >> begin; >> alter table main rename to vac_main; >> create table main (...); >> end; >> >> would the inserter notice this? Read: would ALL inserts AT ANY TIME succeed? >> >> I know, I could simulate such functionality in the client (inserter). But it >> seems more elegant this way... +------**** Science & Engineering Applications GmbH ****------+ | | | Joerg Hessdoerfer | | Leading SW developer Phone: +49 (0)2203-962211 | | S.E.A GmbH Fax: -962212 | | D-51147 Koeln Internet: joerg.hessdoerfer@sea-gmbh.com | | http://www.sea-gmbh.com | +---------------------------------------------------------------+