0

I was asked to create a server application that needs to receive and store relatively simply messages (about 7 fields, uints, strings and a timestamp), but a lot of them and at a high frequency. The messages fly in at an average rate of about 100 messages per second, with peaks going up to about 400 per second. The database will mainly consist of one large archive table and some reference tables with station names etc.

As I do not have experience with Postgres, I'm having a hard time defining the required hardware for the use case, is there some rule of thumb or some lookup table that you know of? Of course, bigger and faster is always "better", but I would like to get a feeling for what will do the job with a good performance and reserve without going directly to the top of the shelf.

Here are some more details:

  • The data need to be accessible for at least 5 years before it is acceptable to export and remove them from the DB
  • the data need to be filtered by time ranges and some additional fields
  • Search and filtering results should be reasonably fast (from a GUI user's point of view who knows nothing about the amount of data), so maybe some milliseconds or seconds
  • From what I already know, new messages do not need to be written to the DB instantly, it is ok to collect them in intervals of 1 to 2 seconds and bulk put them into the database
  • 99% of the time, nobody will look at the data, so there's usually just the server application pumping messages into the archive, from time to time a user starts a client application to look at some values, so there are very few concurrent connections to the DB

Here's a message definition from my current server application written in Go:

type Message struct {
    Sender     string   `json:"sender"`
    MessageID  int      `json:"message_id"`
    Text       string   `json:"text"`
    Priority   int      `json:"priority"`
    Parameters []string `json:"parameters"`
}

MessageID references a Messages table, for the Parameters string array I would like to use a Postgres string array field.

What do you think is a good hardware platform in terms of CPU & cores, disk space, memory? Thanks in advance for your estimation.

3
  • 1
    Any reasonably sized modern server should be able to handle 400 inserts per second (even my Laptop can handle tat). You definitely want to use SSDs (or NVMe disks) in order to make sure the read performance. Something like 32 CPUs is probably enough, RAM isn't really the most important thing for a "mostly-write" system, especially if you have fast disks. Feb 12 '20 at 9:31
  • 1
    For the table size estimation, have a look here: dba.stackexchange.com/questions/25617 Feb 12 '20 at 9:33
  • Good to know, thanks. Interestingly, I could not find any hardware examples from existing projects. This would be extremely helpful. Some kind of overview with given scenarios and the hardware used for it. So for now I'll probably have to start with some machine and test with simulated messages.
    – Rob
    Feb 12 '20 at 12:23
1

How many connections are there going to be pumping in the data? Is every insert naturally its own transaction or are they naturally bundled up somehow?

From what I already know, new messages do not need to be written to the DB instantly, it is ok to collect them in intervals of 1 to 2 seconds and bulk put them into the database

Where are they going to be stored for 1 to 2 seconds? What happens if the system crashes/restarts and those 1 to 2 seconds get forgotten about, is that acceptable? If a small amount of data loss is acceptable on rare occasions, then simply setting synchronous_commit to off is probably a better solution then coming up with some complicated way to re-arrange small transactions so they are bundled into larger transactions.

All the above is mostly to answer the question of how fast does the fdatasync on your storage need to be.

the data need to be filtered by time ranges and some additional fields

How wide will the queries time ranges be? If they are narrow, then you can have an index just on the timestamp. Then read every row in the time range and apply the additional field filters to each row. This would make index maintenance pretty efficient, as the rows should be inserted roughly in timestamp order, meaning the index leaf pages that need to be updated will have just recently been used and so still in memory. If you need more indexes than this, then index maintenance will likely become your bottleneck, especially once the total size of your indexes becomes a substantial fraction of your RAM.

Also, will the time range in those queries be mostly for recent times, or for any time in the last 5 years?

The data need to be accessible for at least 5 years before it is acceptable to export and remove them from the DB

Do you envision this working by removing data from 5 years + 1 hour ago, every hour? Or just archive and drop an entire year at a time once a year? If the latter, then partitioning by year would be a good idea. But at 3 billion records per year, you might want to partition by month instead.

I think it is hard to do much capacity planning without knowing which indexes will need to exist.

I don't think a large number of CPUs will help you. Spend the money on fast storage, or maybe RAM, instead.

1
  • Thanks for sharing your thoughts. You're right, using the built-in async commit functionality is probably better than some DIY bundling. The time range intervals of interest will most likely be in the range of some minutes to several hours. Data from at least the last 3 days should be fast, older data will be looked at rather rarely and may be slower to query. The old data export/removal is thought to work like a ring buffer so that you can always look back 5 years. I think of running a clean-up job once a week.
    – Rob
    Feb 13 '20 at 7:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.