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
deletedfrom 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.:
continuousinsert into table 'main' from client.>>>> From somewhere else, execute:>>>> begin;>> alter table main rename
tovac_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 |
+---------------------------------------------------------------+