Archiveopteryx and PostgreSQL

Archiveopteryx 3.x works with Postgres version 8.1.0 or later. Versions 8.3.0 or later provide much better performance — opening a large mailbox can easily be tens of times faster, and we've found tasks that are a thousand times faster.

Configuration

Archiveopteryx assumes that the PostgreSQL server accepts connections to localhost:5432 by default (which is true since Postgres 8.0). The use of Unix domain sockets, which are supported for backwards compatibility, is strongly discouraged. (While using Unix sockets and running in secure mode, we cannot open new database connections after we chroot.)

Archiveopteryx prefers to authenticate using a username and password. It supports ident authentication for compatibility with some older default Postgres configurations, but its use is discouraged.

You may need to add (something like) the following line near the top of pg_hba.conf to allow Archiveopteryx to authenticate with a password (if Postgres refuses ident authentication):

host archiveopteryx aox 127.0.0.1/32 md5

In archiveopteryx.conf, the db-* variables control how Archiveopteryx tries to connect to Postgres.

The Postgres server hould be local to the Archiveopteryx server; Archiveopteryx uses unencrypted database connections. If a distant Postgres server has to be used, we recommend establishing a VPN.

Performance tuning

It is essential that the database be frequently VACUUMed. We strongly recommend enabling autovacuum, or at least adding an entry to the postgres superuser's crontab that runs "vacuumdb -q -z archiveopteryx" (or "vacuumdb -q -a -z") every fifteen minutes.

In postgresql.conf, it is essential to set the values of shared_buffers, max_fsm_pages, effective_cache_size, and checkpoint_segments based on the amount of memory your server has, as described in this checklist.

We are still testing Archiveopteryx with large archives, and will update this page with more specific advice. In the meantime, there are links to some general tuning guidelines below.

If you would like to discuss a performance problem, please write to the mailstore-users list.

Version-specific configuration advice

Under 8.1, it is advisable to increase the default_statistics_target to 100 or so (from the default value of 10). This helps the planner to pick an index scan over a sequential scan in some commonly-used queries on the header_fields table. This may be a good idea in newer versions too.

Please note that with 8.2 and newer, overestimating the value of effective_cache_size (which used to be a good idea in 8.1 and earlier) is no longer recommended. A more realistic setting can improve performance.

Upgrades

Archiveopteryx does not require any special care when upgrading PostgreSQL. Archiveopteryx does change some queries depending on the PostgreSQL version, but it handles that by autodetecting the server version at startup time.

Archiveopteryx upgrades may involve schema changes. The aox tool does all that with with a single (large) transaction:

aox upgrade schema

The -n switch causes aox to report on the upgrade and not commit it.

If you have problems or questions, please write to info@aox.org.

Relevant links

About this page

Last modified: 2010-11-19
Location: aox.org/postgresql/