Received: from faroenet.fo ([212.55.59.140]) by hub.org (8.10.1/8.10.1) with SMTP id e7HEoJm94478 for ; Thu, 17 Aug 2000 10:50:23 -0400 (EDT) Received: (qmail 3423 invoked by uid 7719); 17 Aug 2000 15:54:09 -0000 Received: from unknown (HELO faroenet.fo) (192.168.1.21) by 192.168.1.1 with SMTP; 17 Aug 2000 15:54:09 -0000 Message-ID: <399BFB98.67D58A7D@faroenet.fo> Date: Thu, 17 Aug 2000 15:50:00 +0100 From: "Poul L. Christiansen" X-Mailer: Mozilla 4.71 [en] (WinNT; U) X-Accept-Language: en,pdf MIME-Version: 1.0 To: Joerg Hessdoerfer CC: pgsql-sql@postgresql.org Subject: Re: Continuous inserts... References: <4.3.2.7.0.20000817162006.00af3db0@192.168.0.1> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200008/159 Isn't easier to reduce the table every day and make a daily vacuum which only lasts a few seconds? 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... > > Greetings, > Joerg > +------**** 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 | > +---------------------------------------------------------------+