pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: brianb-pgsql@edsamail.com
To: Poul L. Christiansen <plc@faroenet.fo>
Cc: Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Continuous inserts...
Date: Fri, 18 Aug 2000 01:41:20 GMT
Message-ID: <20000818014120.2572.qmail@mail01.edsamail.com.ph> (raw)
In-Reply-To: <399BFB98.67D58A7D@faroenet.fo>
References: <4.3.2.7.0.20000817162006.00af3db0@192.168.0.1>
	<399BFB98.67D58A7D@faroenet.fo>


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 <brianb@edsamail.com>
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



view thread (15+ messages)  latest in thread

Message-ID: <20000818014120.2572.qmail@mail01.edsamail.com.ph>
Permalink:  ../20000818014120.2572.qmail@mail01.edsamail.com.ph/
Also on:    postgresql.org/message-id/20000818014120.2572.qmail@mail01.edsamail.com.ph

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: brianb-pgsql@edsamail.com, plc@faroenet.fo, Joerg.Hessdoerfer@sea-gmbh.com
  Subject: Re: Continuous inserts...
  In-Reply-To: <20000818014120.2572.qmail@mail01.edsamail.com.ph>
  References: <4.3.2.7.0.20000817162006.00af3db0@192.168.0.1> <399BFB98.67D58A7D@faroenet.fo> <20000818014120.2572.qmail@mail01.edsamail.com.ph>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox