Select / configure database for high throughput, reliable sequential write throughput, sacrificing latency

Question

I am working on real-time applications with the following features:

• Hundreds of clients will insert lines / documents at the same time, each time inserting a line every couple of seconds.
• Mostly append-only; almost all lines / documents once inserted never change.
• The client should only see success when the data has been flushed to disk, and after that the consistency of "your record" should be maintained.
• Customers are ready to wait about seconds to confirm - long enough to search and burn multiple discs.
• There is too much data in RAM (excluding options like Redis). But lines written a long time ago are rarely available, so they do not need to be remembered.
• Ideally, these records should not block reading.
• A key store is excellent, but there must be at least one reliable auto-increment index.

In other words (and tl; dr), clients can tolerate latency, but they need a lot of reliable write bandwidth — more bandwidth than “one record is one disk operation”.

I anticipate a database that will be implemented something like this: accept (theoretically limited by the number of file descriptors) the number of TCP connections, buffer these entries in memory, write them to disk as often as possible (along with automatic increment index updates), and respond only to these TCP connections when the associated disk write operation is completed. Or it can be as simple as a lazy-writing database that posts a message stating that it has written to disk (clients wait for a lazy response and then wait for the write message to report success).

I think that with such high latent tolerance this does not require too much. And I would suggest that others have such a problem as financial companies that cannot afford to lose data, but can afford to postpone a response to any one client.

Are there any database solutions tested in battle, such as Postgres, CouchDB / Couchbase, or MongoDB, that support these modes of operation?

Answer 1

PostgreSQL should be well suited to this workload; almost everything you specified is well within its usual set of functions. Pg is compatible with ACID, supports group commit to reduce synchronization overhead, writers do not block readers, and it uses the OS for caching, so it will naturally only save hot data in memory.

"Customers are ready to wait about seconds to confirm - long enough for multiple attempts to read and write."

If you are considering PostgreSQL, your application is ideally suited for a really big commit_delay , which will help a lot with write throughput. You cannot use synchronous_commit = off because you need to confirm the commit before replying, but you can just hold it in the queue for a few seconds to save the cost of synchronization.

If you use Pg for this type of work, you will need to configure a breakpoint to make sure breakpoints do not stop I / O. Make sure the bgwriter aggressively writes dirty buffers. Make sure autovaccum works often - you are not deleting from tables, but indexes still need maintenance, as well as table statistics.

If you expect a lot of data, and your queries usually have a temporary element, consider splitting a table into pieces (say) 1 month into 1 year, consolidating everything older than 12 months into tables divided by year. Pg has only limited built-in separation (it is hacked along with inheritance and restriction exceptions), so you need to do it manually / script with triggers, but it does the job.

See:

• commit_delay configuration option
• High Performance PostgreSQL 9.0
• PostgreSQL server setup - PostgreSQL wiki page
• control point configuration settings
• WAL configuration
• resource consumption configuration settings
• table splitting