I am designing a database for a system that has to handle lots of writes operations (99%+ are updates, sometimes inserts) but at all times it has to provide fast reads. At the moment selects take a lot of time to complete.
There are around 20 processes (each on its own server) that communicate with the database server at the same time. Each process has to update rows and in order to avoid multiple processes updating the same row in parallel, we use a particular column
queued which indicates if a row has already been queued by one process. In more detail:
A process requests data from the database with a query like this:
WITH selected AS (SELECT username FROM profiles WHERE queued = false FOR UPDATE SKIP LOCKED LIMIT %(n)s) UPDATE profiles SET queued = true WHERE username IN (SELECT username FROM selected) RETURNING username;
After it has finishing updating each profile we run the following query
INSERT INTO profiles ( ... ) values ( ... ) ON CONFLICT (username) DO UPDATE SET ... ;
Presently we have around 1.7-1.9M updates per day. As mentioned, selects are really slow when all these processes are updating rows. The database server has 4 cores and 25GB of RAM and runs PostgreSQL 9.6 with the following parameters:
max_connections = 100 shared_buffers = 2560MB effective_cache_size = 7680MB maintenance_work_mem = 640MB checkpoint_completion_target = 0.9 wal_buffers = 16MB default_statistics_target = 100 random_page_cost = 1.1 effective_io_concurrency = 200 work_mem = 13107kB min_wal_size = 2GB max_wal_size = 4GB max_worker_processes = 4 max_parallel_workers_per_gather = 2
With all these updates I guess I'll have to increase the autovacuum frequency, is that right? I am ok penalizing a bit the updates if in turn that guarantees fast reads.