44 messages in org.postgresql.pgsql-hackersRe: New feature request: FlashBack Query| From | Sent On | Attachments |
|---|---|---|
| RPK | Feb 17, 2007 6:49 am | |
| Joshua D. Drake | Feb 17, 2007 7:50 am | |
| Tom Lane | Feb 17, 2007 8:48 am | |
| elein | Feb 17, 2007 4:06 pm | |
| Chad Wagner | Feb 17, 2007 4:43 pm | |
| Joshua D. Drake | Feb 17, 2007 7:21 pm | |
| Chad Wagner | Feb 17, 2007 7:49 pm | |
| Tom Lane | Feb 17, 2007 9:31 pm | |
| Warren Turkal | Feb 17, 2007 10:46 pm | |
| Hannu Krosing | Feb 18, 2007 1:45 pm | |
| Joshua D. Drake | Feb 18, 2007 2:27 pm | |
| Andreas 'ads' Scherbaum | Feb 19, 2007 1:53 am | |
| Hannu Krosing | Feb 19, 2007 4:36 am | |
| Florian G. Pflug | Feb 19, 2007 6:13 am | |
| Alvaro Herrera | Feb 19, 2007 6:27 am | |
| Zeugswetter Andreas ADI SD | Feb 19, 2007 6:32 am | |
| Zeugswetter Andreas ADI SD | Feb 19, 2007 6:38 am | |
| Florian G. Pflug | Feb 19, 2007 6:59 am | |
| Gregory Stark | Feb 19, 2007 7:18 am | |
| tom...@tuxteam.de | Feb 19, 2007 8:18 am | |
| August Zajonc | Feb 19, 2007 10:09 am | |
| Florian G. Pflug | Feb 19, 2007 11:30 am | |
| August Zajonc | Feb 19, 2007 12:00 pm | |
| Tom Lane | Feb 19, 2007 7:39 pm | |
| Jonah H. Harris | Feb 19, 2007 8:53 pm | |
| Gregory Stark | Feb 20, 2007 12:58 am | |
| RPK | Feb 20, 2007 3:25 am | |
| Andrew Dunstan | Feb 20, 2007 4:42 am | |
| Jonah H. Harris | Feb 20, 2007 7:19 am | |
| Rod Taylor | Feb 20, 2007 7:42 am | |
| Hannu Krosing | Feb 20, 2007 8:02 am | |
| Gregory Stark | Feb 20, 2007 8:28 am | |
| August Zajonc | Feb 20, 2007 8:39 am | |
| RPK | Feb 20, 2007 10:27 am | |
| Tom Lane | Feb 20, 2007 10:40 am | |
| Theo Schlossnagle | Feb 20, 2007 10:45 am | |
| Jonah H. Harris | Feb 20, 2007 11:48 am | |
| Jonah H. Harris | Feb 20, 2007 12:03 pm | |
| August Zajonc | Feb 20, 2007 7:30 pm | |
| Csaba Nagy | Feb 21, 2007 1:17 am | |
| Florian G. Pflug | Feb 21, 2007 6:01 am | |
| Alvaro Herrera | Feb 21, 2007 6:13 am | |
| Florian G. Pflug | Feb 21, 2007 7:08 am | |
| August Zajonc | Mar 1, 2007 9:48 am |
| Subject: | Re: New feature request: FlashBack Query | |
|---|---|---|
| From: | Hannu Krosing (han...@skype.net) | |
| Date: | Feb 18, 2007 1:45:11 pm | |
| List: | org.postgresql.pgsql-hackers | |
Ühel kenal päeval, L, 2007-02-17 kell 22:49, kirjutas Chad Wagner:
However, they don't have vacuum, we do.
Right, and I think that is more or less because Oracle doesn't need it. Vacuum's main purpose (correct me if I am wrong) is to recover/mark rows that are no longer used, and Oracle essentially reuses the space immediately.
Obviously with Oracle if you bloat out a table and delete a ton of rows then you have to rebuild the table, but that is more or less the same problem that PostgreSQL has and where vacuum full comes into play.
The only benefit with the Oracle model is that you can achieve flashback, which is a very rarely used feature in my book.
We can have flashbacks up to the last vacuum. It is just not exposed. Don't vacuum, and you have the whole history. (Actually you can't go for more than 2G transactions, or you get trx id rollover).
To get a flashback query, you "just" have to construct a snapshot from that time and you are done. We don't store transaction times anywere, so the flashback has to be by transaction id, but there is very little extra work involved. We just don't have syntax for saying "SELECT ... AS SEEN BY TRANSACTION XXX"
AFAIK, Oracles flashbacks also can go as far back as there are rollback segments.
Postgres' original design prescribed, that VACUUM would not delete dead tuples, but just move them to history tables on cheap(er) WORM storage. Doing that would have very little overhead (except writing the old tuples) and would not need any fundamental changes to how we do things currently.
The disadvantages is likely overhead to perform the "rollback" and possibly more scattered reads.
I've also heard reports, that doing concurrent data loading and big analysis queries is a royal pain in Oracle.
I can say that I have used it, and it has come in handy, but hardly worth it. The benefit with the PostgreSQL model is the likelihood of the old rows being inline with the rest of the table data, potentially reducing scattered reads. The disadvantage is vacuuming, it seems to be often overlooked -- possibly solved by defaulting autovacuum to on? (seems to be the way Oracle is heading, defaulting statistics collection to on and other management features).
-- ---------------- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia
Skype me: callto:hkrosing Get Skype for free: http://www.skype.com