Vacuum utility

Mladen Gogala

unread,

Nov 2, 2009, 11:07:01 PM11/2/09

to

I was wondering where does Postgres put the undo information. Oracle has
a special place called "undo tablespace" where the copies of old data are
kept, for the sake of transaction consistency. Postgres doesn't appear to
have such a special place, it keeps the copies of the old data in the
table itself, having to "vacuum" the transactional tables every now and
then, in order to save space.
One more thing is quite confusing: in Postgres, a tablespace is a
directory. That is why there is no "DBA_FREE_SPACE", one can simply use
df -h and du -h. Also, I have another question. Here are files in the main
directory:

root@nyclapwxp2622:/var/lib/postgresql/8.3/main/base# ls
1 11510 11511 16387 16389 pgsql_tmp

What are these numbers? They're not database OID's:

If those numbers are not OIDs, what are they?
--
http://mgogala.freehostia.com

Thomas Kellerer

unread,

Nov 3, 2009, 3:01:47 AM11/3/09

to

Mladen Gogala, 03.11.2009 05:07:

> Also, I have another question. Here are files in the main
> directory:
>
> root@nyclapwxp2622:/var/lib/postgresql/8.3/main/base# ls
> 1 11510 11511 16387 16389 pgsql_tmp
>
>
> What are these numbers? They're not database OID's:
>
> mgogala=# select datname,datdba from pg_database;
> datname | datdba
> -----------+--------
> template1 | 10
> template0 | 10
> postgres | 10
> mgogala | 16386
> scott | 16388
> (5 rows)
>
> If those numbers are not OIDs, what are they?

http://www.postgresql.org/docs/8.4/static/storage-file-layout.html

Laurenz Albe

unread,

Nov 3, 2009, 7:58:37 AM11/3/09

to

Mladen Gogala wrote:
> I was wondering where does Postgres put the undo information. Oracle has
> a special place called "undo tablespace" where the copies of old data are
> kept, for the sake of transaction consistency. Postgres doesn't appear to
> have such a special place, it keeps the copies of the old data in the
> table itself, having to "vacuum" the transactional tables every now and
> then, in order to save space.

There is no direct correspondence between Oracle and PostgreSQL in this
case. PostgreSQL doesn't even touch the table when you COMMIT or
ROLLBACK. All that is done is that the transaction is marked as "failed"
or "succeeded". This information is tracked in the files in the "pg_clog"
directory.

> One more thing is quite confusing: in Postgres, a tablespace is a
> directory. That is why there is no "DBA_FREE_SPACE", one can simply use
> df -h and du -h. Also, I have another question. Here are files in the main
> directory:
>
> root@nyclapwxp2622:/var/lib/postgresql/8.3/main/base# ls
> 1 11510 11511 16387 16389 pgsql_tmp
>
>
> What are these numbers? They're not database OID's:
>
> mgogala=# select datname,datdba from pg_database;
> datname | datdba
> -----------+--------
> template1 | 10
> template0 | 10
> postgres | 10
> mgogala | 16386
> scott | 16388
> (5 rows)

What you select are the OIDs of the database owners, see

http://www.postgresql.org/docs/8.4/static/catalog-pg-database.html

Try:

SELECT oid, datname FROM pg_database;

OID is a special column, these days only used in catalog tables:
http://www.postgresql.org/docs/8.4/static/ddl-system-columns.html

Yours,
Laurenz Albe