We've a very large table with more than 2.2 billion rows at present on Postgres 12.5. The total size of the table (including index) stands at 500 GB. There is one query that we need to do in order to find a set of valid rows from the data set and do updates on them. The query looks something like this:
select id, col4 from table where col1=$1 and col2=$2 and col3='f' and col4>0 order by col5 limit 10
To serve this query, there is an index on the table
ON (col1, col2, col5) and the query uses this index. So far so good. The problem arises when the database needs to do a lot of disk seeks when there's a miss from buffer. This leads to the queries waiting on
Up until now we were using a 16 vCPU and 128 GB machine with io1 storage type with a provisioned IOPS of 20000 for hosting (it's hosted on AWS RDS). We started with a provisioned IOPS of about 3000 and kept increasing it with hopes that with an aggressive autovacuum and data localization it will stabilize at some value. The autovacuum is configured in such a way that it runs every couple of days on this table. We recently faced an issue where the read IOPS started hitting 20000 and the application got too slow. We upgraded to a larger machine with exactly double the size since we could no longer provision IOPS more than 20000 on the prior machine.
On the larger machine we're observing that even the read IOPS has now fallen to ~5000 and the machine now consumes overall IOPS of around 6000 at peak times and the query time has halved precisely. This certainly has to do with the higher
shared_buffer now available for postgres to keep the hot referenced rows in cache, we're assuming.
The problem is that the machine which we're now using is running at ~5% CPU load and there's 184 GB of RAM still unused. All in all, this machine is heavily underutilized. We want to be using the smaller machine by doing any changes in parameters so that this query can run under some tolerable latency limit. We've tried multiple memory tuning in the previous machine so as to fully utilize the RAM. But increasing
shared_buffer to more than 40% of RAM always led to queries getting extremely slow and we always had to revert it back to the previous value.
Sharing a few Postgres db parameters (currently on the bigger machine):
effective_cache_size: 130GB shared_buffers: 66GB work_mem: 4MB maintenance_work_mem: 8.5GB
P.S.: The data growth is about 30 million entries per day so this is going to go worse. The database went live for the production use exactly 3 months back. We also want suggestions on building a sustainable solution. Due to the nature of the application, we can't partition the table unless it's 6 months old or more. Sharding would be our last resort but we want to exhaust all our options before moving to this solution.
Edit: Attaching the query plans (1st when there's no data in buffer and the second is the immediate subsequent query hit). The performance looks more than acceptable since we're on a larger machine but this was taking more than 1 second on the smaller one.
No, we don't need to update a billion rows at a time. We only need to update a few. It's very difficult to tell the number of rows affected during update but it will not be more than 20 for a particular transaction. I can give an idea what we're trying to achieve here. It's like a bag with a capacity
C which we're trying to fill with
col4 values only taking entries which are valid (
col4 > 0). We're looking at 10 entries at a time from the database and if need be there might be a subsequent same query on the database to fetch the next valid entries. In this process, only
col4 is updated which can either be set to zero since it's been consumed or a number lower than it's current value.
Looking for any thoughts or suggestions. Thanks in advance.