Received: from mail01.edsamail.com.ph (mail01.edsamail.com.ph [210.16.71.2]) by hub.org (8.10.1/8.10.1) with SMTP id e7I1fhm99626 for ; Thu, 17 Aug 2000 21:41:44 -0400 (EDT) Received: (qmail 2576 invoked by uid 508); 18 Aug 2000 01:41:20 -0000 Message-ID: <20000818014120.2572.qmail@mail01.edsamail.com.ph> References: <4.3.2.7.0.20000817162006.00af3db0@192.168.0.1> <399BFB98.67D58A7D@faroenet.fo> In-Reply-To: <399BFB98.67D58A7D@faroenet.fo> From: brianb-pgsql@edsamail.com To: "Poul L. Christiansen" Cc: Joerg Hessdoerfer , pgsql-sql@postgresql.org Subject: Re: Continuous inserts... Date: Fri, 18 Aug 2000 01:41:20 GMT Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Archive-Number: 200008/167 Poul L. Christiansen writes: > Isn't easier to reduce the table every day and make a daily vacuum which only > lasts a few seconds? I doubt that it would last just a few seconds. From my experience, VACUUM on large tables can sap your I/O subsystem, slowing down overall performance for everyone else. Joerg, if this is a logging-type application, you may want to consider creating new tables periodically, e.g. rawdata_YYYY_MM or rawdata_WEEKNO and put a little more logic into your app to correctly name the table to perform the INSERT on. The rawdata_YYYY_MM tables should be created in advance, of course. You can then safely post-process last month's data, insert results into a much smaller postprocess_YYYY_MM table, then archive or drop rawdata_YYYY_MM altogether. Perhaps my suggestions are coloured by my experiences w/ 6.5, but this seems to be the safest way to do it without losing data. Alternately, you could log data to flat files, post-process and then INSERT into Postgres. Brian  > 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? > > -- Brian Baquiran http://www.baquiran.com/ AIM: bbaquiran Work: +63(2)7182222 Home: +63(2) 9227123 I'm smarter than average. Therefore, average, to me, seems kind of stupid. People weren't purposely being stupid. It just came naturally. -- Bruce "Tog" Toganazzini