Re: postgre vs MySQL

Steve Crawford <scrawford@pinpointresearch.com> writes:
> Alvaro Herrera wrote:
>> 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.

Another point to keep in mind is that creation of a new btree index
(and, soon, a new hash index) involves a temporary sort file that's
roughly the size of the index.  So the peak transient space demand is
size of compacted table + size of compacted indexes + size of largest
index, more or less.  (I suppose it'd depend on the order in which
the indexes get rebuilt.)

            regards, tom lane