New feature request: FlashBack Query
in [PgSql]
|
Prev: Piggybacking vacuum I/O
Next: pg_proc without oid?
From: "Joshua D. Drake" on 17 Feb 2007 22:21 Chad Wagner wrote: > On 2/17/07, elein <elein(a)varlena.com> wrote: >> >> For other recent time travel ideas see: >> http://www.varlena.com/GeneralBits/122.php >> Time travel is not cheap, though. >> > > > I am sure this topic has probably been beaten to death in the past, but has > anyone talked about the advantages of Oracle's MVCC model versus > PostgreSQL's MVCC model? Oracle achieves multiversioning by using > rollback/undo segments, where PostgreSQL appears to place (essentially) the > undo in the same space as the table. My understanding is that the main difference is that rollbacks are inexpensive for us, but expensive for Oracle. Talk to an Oracle DBA about their Rollback logs :0. However, they don't have vacuum, we do. Joshua D. Drake > > If I were to guess this is probably a major thing to change. Clearly there > are advantages to both, with Oracle essentially the space consumed by a > modified row is immediately available for reuse and generally there is > little row migration assuming there is enough space on the block so you > should be able to avoid updates to the index and the bloating that seems to > go along with vacuuming. > > Is there any previous discussions that folks could point out here? > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
From: "Chad Wagner" on 17 Feb 2007 22:49 On 2/17/07, Joshua D. Drake <jd(a)commandprompt.com> wrote: > > My understanding is that the main difference is that rollbacks are > inexpensive for us, but expensive for Oracle. Talk to an Oracle DBA > about their Rollback logs :0. Yes, I have seen cases where undo segments are thrashed. Generally it works well, and I agree it likely much cheaper on PostgreSQL as you would expect fewer scattered reads because the old version is inline with the rest of the data. But if I recall undo segments are cached in Oracle, usually where I see problems is where the DBA is completely incompetent and has undersized the buffer cache. Oracle does direct reads (afaik) -- so undersizing the buffer cache can be brutal. A very common mistake with Oracle was undersizing the buffer cache and oversizing the shared pool (when the shared plans are stored), and with 9i and later they tried to have the management tools suggest the ideal values or have it automatically managed by the database. Probably a step in the right direction, but I know they still have bumps to iron it. :) 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. The disadvantages is likely overhead to perform the "rollback" and possibly more scattered reads. 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).
From: Tom Lane on 18 Feb 2007 00:32 "Chad Wagner" <chad.wagner(a)gmail.com> writes: > I am sure this topic has probably been beaten to death in the past, but has > anyone talked about the advantages of Oracle's MVCC model versus > PostgreSQL's MVCC model? Yes, we've been all through that. We like ours. See the archives. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
From: Warren Turkal on 18 Feb 2007 01:46 On Saturday 17 February 2007 07:49, RPK wrote: > PostgreSQL, already a mature database, needs to have more options for > recovery as compared to proprietary databases. I just worked with Oracle's > FlashBack query feature in Oracle 9i and FlashBack Table feature in 10g. > > Future versions of PostgreSQL must have similar features which enable users > to bring Table(s) and/or Database(s) to a desired Time Stamp. Check out my proposal[1] for Temporal extensions. Ultimately, creating valid time and transaction time tables would be possible through my proposal. Please check it out. [1]http://archives.postgresql.org/pgsql-hackers/2007-02/msg00540.php wt -- Warren Turkal (w00t) ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
From: "Andreas 'ads' Scherbaum" on 19 Feb 2007 04:53
Hello, On Sat, 17 Feb 2007 06:49:42 -0800 (PST) RPK <rohitprakash123(a)indiatimes.com> wrote: > PostgreSQL, already a mature database, needs to have more options for > recovery as compared to proprietary databases. I just worked with Oracle's > FlashBack query feature in Oracle 9i and FlashBack Table feature in 10g. > > Future versions of PostgreSQL must have similar features which enable users > to bring Table(s) and/or Database(s) to a desired Time Stamp. There is a pgfoundry project which tries to achieve this: http://pgfoundry.org/projects/tablelog/ Kind regards -- Andreas 'ads' Scherbaum German PostgreSQL Usergroup: http://www.pgug.de ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings |