Database Reference
In-Depth Information
To know more about MVCC, check out
http://www.postgresql.org/docs/
current/static/mvcc-intro.html
.
Dump and restore
In the case of bloating, the simplest way of prevention is to back up the table
utilizing
pg_dump
, drop the table, and reload the data into the initial table.
This is an expensive operation and sometimes seems too restrictive.
VACUUM
Vacuuming the table using the
VACUUM
command is another solution that can be
used to ix the bloat. The
VACUUM
command reshufles the rows to ensure that the
page is as full as possible, but database ile shrinking only happens when there are
100 percent empty pages at the end of the ile. This is the only case where
VACUUM
is
useful to reduce the bloat. Its syntax is as follows:
VACUUM table_name
The following example shows the usage of
VACUUM
on the
item
table:
warehouse_db=#
VACUUM
item;
The other way of using
VACUUM
is as follows:
warehouse_db=# VACUUM
FULL
item;
CLUSTER
As we discussed previously, rewriting and reordering of rows can ix the issue that
can be indirectly achieved using dump/restore, but this is an expensive operation.
The other way to do this is the
CLUSTER
command, which is used to physically
reorder rows based on the index. The
CLUSTER
command is used to create a whole
initial copy of the table and the old copy of the data is dropped. The
CLUSTER
command requires enough space, virtually twice the disk space, to hold the initial
organized copy of the data. Its syntax is as follows:
CLUSTER table_name USING index_name
