Can we simulate Oracle Flashback with pg_export_snapshot()?
Hello,
Just had an idea and could use some feedback. If we start a transaction,
leave it idle, and use pg_export_snapshot() to get its snapshot_id MVCC
will hold all the tuples as of that transaction's start and any other
transaction can see the state of the database as of that time using SET
TRANSACTION SNAPSHOT snapshot_id?
http://www.postgresql.org/docs/devel/static/functions-admin.html#FUNCTIONS-SNAPSHOT-SYNCHRONIZATION
I'm thinking of setting up automation to ~every half hour open a
transaction as such, close any that have been open over an hour, and store
the snapshot_id. However, I don't have a busy system that I can test it on.
Of course this would cause some extra bloat because those tuples cannot get
autovacuumed until the transaction closes but that is also the case in
Oracle. Is there anything else I am missing or a reason that this would not
be possible?
Thanks!
*Will J. Dunn*
*willjdunn.com <http://willjdunn.com>*
William Dunn wrote:
Just had an idea and could use some feedback. If we start a transaction, leave it idle, and use
pg_export_snapshot() to get its snapshot_id MVCC will hold all the tuples as of that transaction's
start and any other transaction can see the state of the database as of that time using SET
TRANSACTION SNAPSHOT snapshot_id?http://www.postgresql.org/docs/devel/static/functions-admin.html#FUNCTIONS-SNAPSHOT-SYNCHRONIZATION
I'm thinking of setting up automation to ~every half hour open a transaction as such, close any that
have been open over an hour, and store the snapshot_id. However, I don't have a busy system that I can
test it on.Of course this would cause some extra bloat because those tuples cannot get autovacuumed until the
transaction closes but that is also the case in Oracle. Is there anything else I am missing or a
reason that this would not be possible?
Oracle does not have that issue because modifications cannot bloat the table (the bloat is in
what is called the "UNDO tablespace").
What you suggest would allow you to look at the data as they were at specific times (of the snapshots).
But the price on a busy system where data are modified would be considerable; your tables might
become quite bloated, and you could not use HOT updates.
If you want to look into the past, wouldn't it be much more useful to have a standby server
that is lagging behind? There is an option for that (recovery_min_apply_delay) from
PostgreSQL 9.4 on.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hello Laurenz,
Thanks for your feedback. Actually when I said that the same overhead
occurs in Oracle I was referring to bloat in the UNDO logs, which similar
to PostgreSQL dead tuples has some impact on size/performance and is the
majority of the trade-off considered when deciding to implement Flashback.
Thank you for point out HOT Updates! I had not read about that before as it
doesn't seem to appear in the official documentation. I found info on it in
a readme in the source code (
http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/heap/README.HOT;hb=HEAD)
and hope to read through it soon.
In terms of benefit over a lagging replica Flashback has the benefit of
being transparent to the user (the user can query over the same database
connection, etc), it does not incur the full cost of having a replica...
*Will J. Dunn*
*willjdunn.com <http://willjdunn.com>*
On Fri, May 22, 2015 at 4:50 AM, Albe Laurenz <laurenz.albe@wien.gv.at>
wrote:
Show quoted text
William Dunn wrote:
Just had an idea and could use some feedback. If we start a transaction,
leave it idle, and use
pg_export_snapshot() to get its snapshot_id MVCC will hold all the
tuples as of that transaction's
start and any other transaction can see the state of the database as of
that time using SET
TRANSACTION SNAPSHOT snapshot_id?
http://www.postgresql.org/docs/devel/static/functions-admin.html#FUNCTIONS-SNAPSHOT-SYNCHRONIZATION
I'm thinking of setting up automation to ~every half hour open a
transaction as such, close any that
have been open over an hour, and store the snapshot_id. However, I don't
have a busy system that I can
test it on.
Of course this would cause some extra bloat because those tuples cannot
get autovacuumed until the
transaction closes but that is also the case in Oracle. Is there
anything else I am missing or a
reason that this would not be possible?
Oracle does not have that issue because modifications cannot bloat the
table (the bloat is in
what is called the "UNDO tablespace").What you suggest would allow you to look at the data as they were at
specific times (of the snapshots).
But the price on a busy system where data are modified would be
considerable; your tables might
become quite bloated, and you could not use HOT updates.If you want to look into the past, wouldn't it be much more useful to have
a standby server
that is lagging behind? There is an option for that
(recovery_min_apply_delay) from
PostgreSQL 9.4 on.Yours,
Laurenz Albe
William Dunn wrote:
In terms of benefit over a lagging replica Flashback has the benefit of being transparent to the user
(the user can query over the same database connection, etc), it does not incur the full cost of having
a replica...
Yes, Flashback (in all ist forms) is something that PostgreSQL doesn't offer.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general