2

Greetings,

I am working on a piece of software that logs entries (and related tagging) in a PostgreSQL database for storage and retrieval. We never update any data once it has been inserted; we might remove it when the entry gets too old, but this is done at most once a day. Stored entries can be retrieved by users.

The insertion of new entries can happen rather fast and regularly, thus the database will commonly hold several millions elements.

The tables used are pretty simple : one table for ids, raw content and insertion date; and one table storing tags and their values associated to an id. User search mostly concern tags values, so SELECTs usually consist of JOIN queries on ids on the two tables.

To sum it up :

  • 2 tables
  • Lots of INSERT
  • no UPDATE
  • some DELETE, once a day at most
  • some user-generated SELECT with JOIN
  • huge data set

What would an optimal server configuration (software and hardware, I assume for example that RAID10 could help) be for my PostgreSQL server, given these requirements ? By optimal, I mean one that allows SELECT queries taking a reasonably little amount of time.

I can provide more information about the current setup (like tables, indexes ...) if needed.

0

1 Answer 1

1

Change some configuration settings in postgresql.conf: Writing: increase the wal-settings Reading: increase shared_buffers and set work_mem to an amount the planner can do its magic.

There several settings, you have to check and test.

RAID 10 is great for writing, reading will be fine as well. A good RAID-card with a lot of RAM and battery backup, will help a lot.

And, RAM, RAM and RAM. You can't have too much.

2
  • Thanks for the answer, Frank. I have followed guidelines from the [PostgreSQL wiki][1]. They basically cover what you advised. Changing these settings already led to a dramatic speedup in queries. Any other fine tunings I should be aware of ? [1]: wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server "Server Tuning"
    – user39530
    Apr 2, 2010 at 8:54
  • auto_vacuum can play a role, it has to analyze your data and clean up deleted data. The correct indexes will help the SELECTs (and maybe the DELETE's), inserting using prepared statements or COPY can give a hugh performance boost on the inserts. There are many options and it all depends on you usage patern. So, start testing! Apr 2, 2010 at 11:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .