Re: postgre vs MySQL

Steve Crawford escribió:

>> Currently postgresql's "vacuum full" also locks the affected tables.
>> Does 8.3 vacuum full effectively make a copy of the entire table? How
>> much extra space will the various vacuums use while vacuuming?
>>
> As to 8.3 and how it handles vacuum-full internally, I can't say for
> certain without reading the notes. I suspect it is an in-place operation
> similar to prior versions.

VACUUM FULL is in 8.3 pretty much the same that's on previous versions.
You are advised to stay away from it, though, as much as possible.

> But if you are running PG 7.0 or later (maybe even earlier, I didn't
> look) and have enough disk space to replicate the table, you can use
> CLUSTER to do a copy-to-new file and delete old file operation. It does
> still lock the table and it does require that the table have an index
> based on which it will physically reorder the table but it is usually
> _way_ faster and you get fresh indexes as a bonus.

You can use CLUSTER reliably only from 7.2 upwards. (Or was it 7.3? I
forget). In earlier versions it would lose information about other
indexes (i.e. those not being clustered on), foreign keys, inheritance,
etc; in other words pretty much a disaster except for the simplest of
tables. Also, it is MVCC-safe only from 8.3 upwards; on older versions
it (incorrectly) deletes dead tuples that are still visible to old
transactions.

Of course, the main problem with CLUSTER is that it needs about 2x the
disk space of table + indexes.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.