Re: Continuous inserts...

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(at)edsamail(dot)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