[collectd] RFC: Writing data to PostgreSQL

[Sebastian Harl]

Hi there,

I'd like to announce a new feature of the "postgresql" plugin and I'd
like to ask for feedback, comments, flames, etc. in order to further
tune and improve it before actually releasing it.

I've implemented generic support for writing data to PostgreSQL. This is
done by letting the user specify an arbitrary SQL statement to be used
for storing a value-list in PostgreSQL. Usually, this should be done by
creating custom helper functions (using PL/SQL) for that purpose.

This approach has two benefits: for one, a user may chose whatever
database layout best suits her needs. Also, it is very easy to
experiment with different approaches on how to structure the data in a
database without the need to modify the plugin. This can be done in SQL,
which (hopefully) is the language that people working with PostgreSQL
databases like most ;-) If it happens to turn out that some approach is
rather superior, it may still be re-implemented in a specific plugin in
later versions (in case that improves performance or usability).

The code is available at Github for now:
<https://github.com/tokkee/collectd/tree/sh/postgresql-writer>

I've included an example database setup (table layout and partitioning
and helper functions). See contrib/postgresql/collectd_insert.sql.
However, I'd like people to think and experiment on their own ;-)

I suppose that, currently, this won't work with large amounts of data
being processed by one collectd instance. I've got a couple of random
ideas floating around on how to possibly improve that situation but I
haven't tested any of that yet, so I won't talk about it ;-) Anyway,
some feedback about how this performs or some hints on how to tune
PostgreSQL in order to let it process large amounts of write workloads
would be much appreciated. Imho, decreasing data safety a bit (like
allowing some data to be lost in case of a crash) would be perfectly
fine for this kind of setup if that bursts performance.

Cheers,
Sebastian

-- 
Sebastian "tokkee" Harl +++ GnuPG-ID: 0x8501C7FC +++ http://tokkee.org/

Those who would give up Essential Liberty to purchase a little Temporary
Safety, deserve neither Liberty nor Safety.         -- Benjamin Franklin

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 197 bytes
Desc: Digital signature
URL: <http://mailman.verplant.org/pipermail/collectd/attachments/20120819/6293fd9b/attachment.pgp>