Vacuum DB in Postgres Vs similar concept in other RDBMS

[Harpreet Dhaliwal]

Hi,

I was wondering if Vacuum DB concept in Postgres is really novel and there's
no concept like this in other RDBMS like oracle or sql server.
If at all other RDBMS have such a concept implemented, how good or bad it is
as compared to postgres's vacuum db concept.

Any type of pointers would be highly appreciated.

Thanks,
~Harpreet

[Alexander Staubo]

Post by Harpreet Dhaliwal
I was wondering if Vacuum DB concept in Postgres is really novel and there's
no concept like this in other RDBMS like oracle or sql server.
If at all other RDBMS have such a concept implemented, how good or bad it is
as compared to postgres's vacuum db concept.

As we told you the last time you asked
(http://archives.postgresql.org/pgsql-general/2007-05/msg00074.php),
the concept of vacuuming is not unique to PostgreSQL by far. It is an
inherent facet of MVCC.

Other databases that implement MVCC, or implement an MVCC-like system
that requires garbage collection, include Oracle, SQLite, Firebird and
its parent project InterBase. Wikipedia has a decent article on MVCC:

http://en.wikipedia.org/wiki/Multiversion_concurrency_control

Alexander.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

[André Volpato]

Hello,

Is there any way to remove the results of certain query, from the memory
cache ?
I´m doing some performance tests, and I need the planner to make his
work every time I run the statements, without changing them.

Running vmstat, I can se the memory cache grows, and the planner do not
'forget' the results of any query until the cache reach 2 Gb (total box
RAM) , or the server is rebooted.
--
[]´s,

André Volpato
ECOM Tecnologia Ltda
***@ecomtecnologia.com.br
(41) 3014 2322



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

[Alvaro Herrera]

Post by André Volpato
Hello,
Is there any way to remove the results of certain query, from the memory
cache ?
I´m doing some performance tests, and I need the planner to make his
work every time I run the statements, without changing them.
Running vmstat, I can se the memory cache grows, and the planner do not
'forget' the results of any query until the cache reach 2 Gb (total box
RAM) , or the server is rebooted.

Stop postmaster, unmount the filesystem, mount, restart postmaster. The
problem is not only Postgres' own cache, but the kernel cache as well,
which is why you need the unmount step.

Maybe remounting is good enough, but I'm not sure
mount -o remount /where/lies/data

[Dann Corbit]

In SQL*Server it is called "UPDATE STATISTICS"

http://msdn2.microsoft.com/en-us/library/ms187348.aspx



Oracle tuning is a lot more fiddly:

http://searchoracle.techtarget.com/originalContent/0,289142,sid41_gci121
3646,00.html





________________________________

From: pgsql-general-***@postgresql.org
[mailto:pgsql-general-***@postgresql.org] On Behalf Of Harpreet
Dhaliwal
Sent: Wednesday, May 23, 2007 10:49 AM
To: Postgres General
Subject: [GENERAL] Vacuum DB in Postgres Vs similar concept in other
RDBMS



Hi,

I was wondering if Vacuum DB concept in Postgres is really novel and
there's no concept like this in other RDBMS like oracle or sql server.
If at all other RDBMS have such a concept implemented, how good or bad
it is as compared to postgres's vacuum db concept.

Any type of pointers would be highly appreciated.

Thanks,
~Harpreet

[Alexander Staubo]

Post by Dann Corbit
In SQL*Server it is called "UPDATE STATISTICS"
http://msdn2.microsoft.com/en-us/library/ms187348.aspx

No -- MS SQL Server's "update statistics" is the equivalent of
"analyze", not "vacuum."

Alexander.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Harpreet Dhaliwal]

I was just wondering if Vacuum Db in postgresql is somehow superior to the
ones that we have in other RDBMS.

Post by Alexander Staubo

Post by Dann Corbit
In SQL*Server it is called "UPDATE STATISTICS"
http://msdn2.microsoft.com/en-us/library/ms187348.aspx

No -- MS SQL Server's "update statistics" is the equivalent of
"analyze", not "vacuum."
Alexander.

[Chris Browne]

Post by Harpreet Dhaliwal
I was just wondering if Vacuum Db in postgresql is somehow superior
to the ones that we have in other RDBMS.

The thing that is more akin to VACUUM, in Oracle's case, is the
rollback segment. In Oracle, Rollback segments are areas in your
database which are used to temporarily save the previous values when
some updates are going on.

In the case of Oracle, if a transaction rolls back, it has to go and
do some work to clean up after the dead transaction.

This is not *exactly* like PostgreSQL's notion of vacuuming, but
that's the nearest equivalent that Oracle has.

The Oracle InnoDB product also has the notion of rollback segments; if
you use InnoDB tables with MySQL, the rollback functionality has much
the same behaviour as Oracle.

Note that in the case of PostgreSQL, the MVCC behaviour (which
requires VACUUMing) has the merit that COMMIT and ROLLBACK both have
near-zero costs; in either case, the cost is merely to mark the
transaction as either committed or failed. Data doesn't have to be
touched at time of COMMIT/ROLLBACK; any costs that need to be paid are
deferred to VACUUM time.
--
select 'cbbrowne' || '@' || 'acm.org';
http://www3.sympatico.ca/cbbrowne/postgresql.html
"For be a man's intellectual superiority what it will, it can never
assume the practical, available supremacy over other men, without the
aid of some sort of external arts and entrenchments, always, in
themselves, more or less paltry and base. This it is, that forever
keeps God's true princes of the Empire from the world's hustings; and
leaves the highest honors that this air can give, to those men who
become famous more through their infinite inferiority to the choice
hidden handful of the Divine Inert, than through their undoubted
superiority over the dead level of the mass." --Moby Dick, Ch 33

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[Ron Johnson]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Post by Chris Browne

Post by Harpreet Dhaliwal
I was just wondering if Vacuum Db in postgresql is somehow superior
to the ones that we have in other RDBMS.

The thing that is more akin to VACUUM, in Oracle's case, is the
rollback segment. In Oracle, Rollback segments are areas in your
database which are used to temporarily save the previous values when
some updates are going on.
In the case of Oracle, if a transaction rolls back, it has to go and
do some work to clean up after the dead transaction.
This is not *exactly* like PostgreSQL's notion of vacuuming, but
that's the nearest equivalent that Oracle has.

That's the only other way to do it, no?

(Rdb/VMS has dynamically-created [made when a process attaches to
the db] Recovery Unit Journal files that store the record before-
images.)

Post by Chris Browne
The Oracle InnoDB product also has the notion of rollback segments; if
you use InnoDB tables with MySQL, the rollback functionality has much
the same behaviour as Oracle.
Note that in the case of PostgreSQL, the MVCC behaviour (which
requires VACUUMing) has the merit that COMMIT and ROLLBACK both have
near-zero costs; in either case, the cost is merely to mark the
transaction as either committed or failed. Data doesn't have to be
touched at time of COMMIT/ROLLBACK; any costs that need to be paid are
deferred to VACUUM time.

So it's not "near-zero cost", it's "deferred cost".

- --
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGVN1mS9HxQb37XmcRAsNbAJ9hgkDpUQGVR1yxb2WrpP/m3U36eQCghv7d
9FWyD8TbSOxXiaa0e8lK5/4=
=W63C
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Tom Lane]

Post by Ron Johnson

Post by Harpreet Dhaliwal
I was just wondering if Vacuum Db in postgresql is somehow superior
to the ones that we have in other RDBMS.

So it's not "near-zero cost", it's "deferred cost".

Exactly. VACUUM sucks (ahem) in all ways but one: it pushes the
maintenance costs associated with MVCC out of the foreground query code
paths and into an asynchronous cleanup task. AFAIK we are the only DBMS
that does it that way. Personally I believe it's a fundamentally
superior approach --- because when you are under peak load you can defer
the cleanup work --- but you do need to pay attention to make sure that
the async cleanup isn't postponed too long. We're still fooling around
with autovacuum and related tuning issues to make it work painlessly...

regards, tom lane

---------------------------(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

[Robert Treat]

Post by Ron Johnson

Post by Chris Browne

Post by Harpreet Dhaliwal
I was just wondering if Vacuum Db in postgresql is somehow superior
to the ones that we have in other RDBMS.

The thing that is more akin to VACUUM, in Oracle's case, is the
rollback segment. In Oracle, Rollback segments are areas in your
database which are used to temporarily save the previous values when
some updates are going on.
In the case of Oracle, if a transaction rolls back, it has to go and
do some work to clean up after the dead transaction.
This is not *exactly* like PostgreSQL's notion of vacuuming, but
that's the nearest equivalent that Oracle has.

That's the only other way to do it, no?

You can also take care of the maintenence part both inline (as opposed to a
seperate segment) and at commit time (rather than delay for a vacuum). See
the current HOT patch for a similar implementation to this idea.