Re: postgre vs MySQL

Alvaro Herrera wrote:
>
>
> 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.
Interesting historical note, but fortunately largely irrelevant these days.

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

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

Cheers,
Steve