On Tue, May 23, 2017 at 1:49 PM, Sven R. Kunze <srkunze@mail.de> wrote:
On 23.05.2017 22:14, Jarek wrote:
I have pool of clients (~30) inserting to database about 50 records per second (in total from all clients) and small numer (<10) clients querying database for those records once per 10s. Other queries are rare and irregular. The biggest table has ~ 100mln records (older records are purged nightly). Database size is ~13GB. I near future I'm expecting ~150 clients and 250 inserts per second and more clients querying database. Server is handling also apache with simple web application written in python. For the same price, I can get 8C/3.2GHz or 14C/2.6GHz. Which one will be better ?
I would start by trying a few things on your existing equipment.
If your inserts are coming from individual connections, say, via the web-app in a connect-insert-disconnect fashion then pooling can be a huge win. Connection overhead is a bigger factor than you might imagine and I've seen as much as a 10x improvement in small queries when pooling was added.
If the every-10-second queries are running on the recently inserted data then partitioning by time range could substantially improve the speed of inserts, queries and purging. It's pretty easy to do, now, with pg_partman or similar but built-in auto-partitioning is coming in version 10.
Fast commit to disk is a win - think SSD or RAID with BBU cache and with a relatively modest 13GB database you should be able to spec enough RAM to keep everything in memory.
