Incidentally,
this is also true for most "commerical" databases. The space of
delete rows is not returned to OS automatically.
while shutting down the database is not necessary but trying to
return space back to OS does have a significant performance penalty and is
not recommeneded on productions systems (at least in oracle and SQL Server).
Tarun
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On
Behalf Of Gurpreet Singh Sachdeva
Sent: Saturday, October 18, 2003 2:10 AM
To: The Linux-Delhi mailing list; The Linux-Delhi mailing list
Subject: RE: [ilugd] Postgres vs oracle
Correct!
To be more precise:
The standard form of VACUUM is best used with the goal of maintaining a
fairly level steady-state usage of disk space. The standard form finds old
tuples and makes their space available for re-use within the table, but it
does not try very hard to shorten the table file and return disk space to
the operating system. If you need to return disk space to the operating
system you can use VACUUM FULL --- but what's the point of releasing disk
space that will only have to be allocated again soon? Moderately frequent
standard VACUUMs are a better approach than infrequent VACUUM FULLs for
maintaining heavily-updated tables.
Recommended practice for most sites is to schedule a database-wide VACUUM
once a day at a lowusage time of day, supplemented by more frequent
vacuuming of heavily-updated tables if necessary. (If you have multiple
databases in an installation, don't forget to vacuum each one; the vacuumdb
script may be helpful.) Use plain VACUUM, not VACUUM FULL, for routine
vacuuming for space recovery.
VACUUM FULL is recommended for cases where you know you have deleted the
majority of tuples in a table, so that the steady-state size of the table
can be shrunk substantially with VACUUM FULL's more aggressive approach.
If you have a table whose contents are deleted completely every so often,
consider doing it with TRUNCATE rather than using DELETE followed by VACUUM.
Gurpreet Singh Sachdeva
-----Original Message-----
From: Raj Mathur [mailto:[EMAIL PROTECTED]
Sent: Thu 10/16/2003 5:38 PM
To: The Linux-Delhi mailing list
Cc:
Subject: Re: [ilugd] Postgres vs oracle
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
>>>>> "Ambar" == Ambar Roy <[EMAIL PROTECTED]> writes:
>> > Surely NO...so we would certainly be interested to know
some
>> of your >
Ambar> "Biggest Internet Sites running on open source".
>> Geocrawler use Postgres.
Ambar> Intresting thing about Geocrawler & AudioGalaxy using
Ambar> postgres was that both of these sites seemed to go
offline
Ambar> for daily maintainence during the middle of the day here
in
Ambar> India. Both the sites ran Postgresql, and were probably
Ambar> running a daily database cleanup. IMHO this is what has
Ambar> kept me away from considering postgresql for serious
Ambar> databases. Has this issue of regular database
maintainence
Ambar> been solved by the recent versions of postgresql?
PostgreSQL (PgSQL) has two types of ``cleanup'': vacuum and vacuum
full. The first (plain vacuum) merely marked unused (deleted) disk
clusters as free but does not compact the physical database. The
second (vacuum full) does all that and also physically compacts the
database on disk.
Using vacuum full will lock up your database, no queries or
transactions will be possible while it is running. However most
databases achieve a sort of `steady state' (roughly the same number
of
records being added and deleted regularly) and plain vacuum suffices
for that. Transactions are possible during vacuum, and most
installations will prefer to use that periodically over vacuum full.
It's only if your database sizes vary wildly over the course of time
that you'll need to use vacuum full (and consequently bring the
system
down for maintenance).
The above is from my understanding of PgSQL, would appreciate
clarifications in case I've missed anything out.
Ambar> Another intresting thing about postgresql is that while
the
Ambar> web hosting control panels on Linux used to only support
Ambar> MySQL, cPanel & Plesk now have support for Postgresql. To
Ambar> me this is a good sign. Now even smaller sites can start
Ambar> using Postgresql.
Regards,
- -- Raju
- --
Raj Mathur [EMAIL PROTECTED]
http://kandalaya.org/
GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F
It is the mind that moves
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (GNU/Linux)
Comment: Processed by Mailcrypt 3.5.6 and Gnu Privacy Guard
<http://www.gnupg.org/>
iD8DBQE/joo+yWjQ78xo0X8RAh/9AJ92FPCIhsOSYWwlOrgI610XI6IjtQCbBOCv
apkVPv4S87Ot14c7+YDzGwo=
=RF8Y
-----END PGP SIGNATURE-----
_______________________________________________
ilugd mailing list
[EMAIL PROTECTED]
http://frodo.hserus.net/mailman/listinfo/ilugd
_______________________________________________
ilugd mailing list
[EMAIL PROTECTED]
http://frodo.hserus.net/mailman/listinfo/ilugd
