Re: postgre vs MySQL

Steve Crawford escribió:
> Alvaro Herrera wrote:

>> 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.
>
> More interesting. I may have a broken mental-model. I *thought* that
> CLUSTER acquired exclusive locks and that acquisition of the exclusive
> lock would imply that there couldn't be any transactions accessing that
> table. Where is my misunderstanding?

In that a transaction may have started _before_ the CLUSTER, yet not
grabbed any locks on the table yet. CLUSTER completes, releases the
lock, and your old transaction visits the table only to find that the
tuples it needs are no longer there. (In fact IIRC what happens is that
the transaction finds the table empty).

>> Of course, the main problem with CLUSTER is that it needs about 2x the
>> disk space of table + indexes.
>>
> Again checking my mental model. My understanding is that CLUSTER
> basically recreates the tables and indexes and then swaps the new ones
> in place of the originals. So ~2x is true for typical tables. But for
> tables badly bloated by multiple bulk updates or bad vacuum practices
> CLUSTER should require far less than 2x.

Yeah, that would seem to be correct -- you only need space enough for
live tuples.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support