1

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:

  1. 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;
    
  2. 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.

  • 1
    For tables with a high UPDATE count, it is usually also a good idea to lower fillfactor (e.g. to 60 or 70) – a_horse_with_no_name Jun 25 '18 at 12:09
  • @a_horse_with_no_name Interesting, I didn't know about that. Thanks for pointing it out. – rubik Jun 25 '18 at 12:54
  • 2
    This is a rather small machine for what you are asking it to do. Somewhat more importantly what disks are you using? – Dave Cramer Jun 25 '18 at 13:57
  • 1
    What is the nature of the slow selects? Single row selects on a primary key? Or is it the selects that are part of the update which are slow? – jjanes Jun 25 '18 at 22:52
  • 1
    It would really help to see a specific query, with an explain (analyze, buffers). Pick the worst one. Or the simplest one which is noticeably bad. Also, run it twice in a row and see if the second run as much faster. Otherwise all we can do is give generic advice like "buy better hardware" or "vacuum more aggressively". Also, if you stop doing updates, do the selects stop being slow? What if you set up streaming hot standby and run the queries there? – jjanes Jun 26 '18 at 13:08

Your Answer

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

Browse other questions tagged or ask your own question.