Introduction
============

The PostgreSQL server default settings are designed to accomodate a
broad variety of general use cases. To run the port database
effectively requires changing some of these settings. Failure to do so
will result in diminished performance and, probably, errors that
prevent the proper use of the port database.

We have found the following changes useful when running the port
database in large SiLK installations. It is not, however, a substitute
for knowledge of PostgreSQL database administration, and your
installation may require adjustments unique to your environment. If
you have not administered a PostgreSQL database before, you should
familiarize yourself with the online documentation,[1] especially the
Server Configuration section.[2]



Constraint Exclusion
====================

In order to allow PostgreSQL to make proper query optimizations based
on the data partitioning scheme the portal uses, enable the
"constraint_exclusion" option.  To do this, edit the postgresql.conf
file underneath the PostgreSQL data directory.  Find the line:

------------------------------------------------------------------->8---
#constraint_exclusion = off
------------------------------------------------------------------->8---

underneath the QUERY TUNING section, and change it to:

------------------------------------------------------------------->8---
constraint_exclusion = on
------------------------------------------------------------------->8---

When constraint exclusion is used, PostgreSQL will no longer look at
partitions that are not relevant to the query at hand--when it is off,
it will look at these partitions even if they cannot possibly contain
any relevant data.  This parameter is set to off by default because it
has drawbacks in certain rare situations, but none of those situations
apply to the port database (and the performance benefits in a
partitioned table are significant.)

More information on constraint exclusion is available in the
PostgreSQL documentation at [2] (especially Section 17.6.4) and [3]
(especially Section 5.9.4.)



Locks Per Transaction
=====================

The port database partitions its data into subtables bounded by time,
so a single query is likely to acquire many locks. The default
max_locks_per_transaction value of 64, then, will probably be
insufficient for running the port database. The server will likely
tell you that this need to be changed with an error like "out of
shared memory HINT: You may need to increase
max_locks_per_transaction."

To do this, find the line in the postgresql.conf file that reads:

------------------------------------------------------------------->8---
#max_locks_per_transaction = 64         # min 10
------------------------------------------------------------------->8---

and change it to a larger number. (How much larger depends on how much
historical data you plan to keep in the database. 1024 is not an
unreasonable place to start, if you plan to keep a month or more of
data.)



Shared Memory
=============

Postgres uses shared memory to store locks, connection information,
prepared transactions and other resources. In order to accomodate the
increased number of locks the Port database requires, you will
probably have to adjust the shared memory allocation on your system.

The Postgres server will not start if it cannot allocate the shared
memory it requires to run as configured. If, after adjusting
max_locks_per_transaction, you find that the server won't start, you
probably need to adjust your shared memory.

Shared memory management is system-specific. You can find more
information on how Postgres uses shared memory and how to adjust
shared memory options for particular systems at [4].




Notes
=====

[1] http://www.postgresql.org/docs/8.2/static/index.html
[2] http://www.postgresql.org/docs/8.2/static/runtime-config.html
[3] http://www.postgresql.org/docs/8.2/static/ddl-partitioning.html
[4] http://www.postgresql.org/docs/8.1/static/kernel-resources.html