Timo Sirainen wrote:
I wasted some time yesterday and today implementing a SQL storage plugin. It seems to be working, but:
- Saving new messages is done in a regular INSERT statement, which is bad. PostgreSQL has at least this COPY TO command which could be used instead.
depending on the code's structure, you can wrap bundles of 100 or so inserts in transactions, which will speed things up rather noticeably. also watch for connection handling, opening a connection is really expensive so pooling of some sort is good (maybe look at SQL Relay, it's database agnostic to a fair degree and could form part of a bridge to database independence.) this is also likely something that would benefit from smart disk layout, e.g. putting transaction logs (/var/lib/pgsql/data/pg_xlog) on a separate spindle (or raid 1 mirror), maybe separating message store from other infrastructure using table spaces, etc. etc. in other words, there are a lot of DBA type issues in getting it to perform well. so for installs, you'll want a guide for DBAs and some flexibility to manage the database setup. richard (not a real PostgreSQL DBA but i play one at the day job)
