Database Reference
In-Depth Information
Use table partitioning and constraint exclusion
If you have a huge table, and a query select only a subset of that table, then you can partition
that table and use constraint exclusion so that PostgreSQL knows which partitions it needs to
access for a specific query.
Table partitioning is still not directly supported in PostgreSQL 9.0, but PostgreSQL has
the basic capabilities in place to define it yourself. Unfortunately, it needs much longer
explanation then we have space here. You can check out the official documentation on
partitioning at the following URL:
http://www.postgresql.org/docs/9.0/interactive/ddl-partitioning.html
There is a full chapter on table partitioning in another Packt topic "High
Performance PostgreSQL 9.0" which goes well beyond what is covered
by the standard PostgreSQL documentation.
In case of many updates set fillfactor on table
If you often update only some table and can arrange so that you don't change any indexed
fields, then setting
fillfactor
to a lower value than the default of 100 for that a table
enables PostgreSQL to use HOT updates, which can be an order of magnitude faster than
ordinary updates. HOT updates not only avoid creating new index entries but also can perform
a fast mini-vacuum inside the page to make room for new rows.
ALTER TABLE t1 SET (fillfactor = 70);
Tells PostgreSQL to fill only 70% of each page in table
t1
when doing inserts, so that 30% is
left for use by in-page (HOT) updates.
Rewriting the schema—a more radical approach
In some occasions, it may make sense to rewrite the database schema, and provide an old
view for unchanged queries using views, triggers, rules, and functions.
One such case occurs when refactoring the database, and you want old queries to keep
running while changes are made.
Another is an external application that is unusable with the provided schema, but can be
made to perform OK with a different distribution of data between tables.
