Bruce McAlister wrote:
Hi Heikki,
Thanks for the reply.
The RAID array was implemented due to a projected growth pattern which
incorporate all 18 of our databases. The sizings I mentioned only refer to 1
of those databases, which, is also the most heavily used database :)
If I understand you correctly, we could in essence change our maintenance
routine to the follwing:
[1] Cluster on most used index
[2] Perform a vacuum analyze on the table
If I read your post correctly, this will regenerate the index that the
cluster is performed on (1 of 3) and also re-generate the table in the
sequence of that index?
That's right. In fact, even cluster probably doesn't make much
difference in your case. Since the table fits in memory anyway, the
physical order of it doesn't matter much.
I believe you would be fine just turning autovacuum on, and not doing
any manual maintenance.
If that is the case, why would anyone use the vacuum full approach if they
could use the cluster command on a table/database that will regen these
files for you. It almost seems like the vacuum full approach would, or
could, be obsoleted by the cluster command, especially if the timings in
their respective runs are that different (in our case the vacuum full took
15 minutes in our worst case, and the cluster command took under 1 second
for the same table and scenario).
In fact, getting rid of vacuum full, or changing it to work like
cluster, has been proposed in the past. The use case really is pretty
narrow; cluster is a lot faster if there's a lot of unused space in the
table, and if there's not, vacuum full isn't going to do much so there's
not much point running it in the first place. The reason it exists is
largely historical, there hasn't been a pressing reason to remove it either.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
