I am currently hosting a ~10GB database in amazon RDS running postgresql. I have 100GB of space allocated using General Purpose SSDs which means that I get 300IOPS to be split between read and writes.
At steady state, my database receives around 8.3 update statements per second which will update a timestamp on up to 100 rows each, and usually above 95 each. There are 6 columns in this table, each of which is indexed as they are searched quite often for other reasons. There should be around 830 row updates per second, and I am seeing around 800-900 write IOPS using the aws monitoring utilities.
However, each of these write operation is very small, and the total bandwidth being used is ~15kB. A write operation can be up to 8kB for postgres(correct me if Im wrong), so this means that is not nearly the amount of bandwidth that I have available, and as such I am being severely bottlenecked by the IOPS allocated to me.
High precision is not necessary for these updates, so I was wondering if there was some way to combine many of the updates together such that fewer IOPS are consumed? I've tried turning synchronous_commit off as well as playing around with commit_delay and commit_siblings to no avail.
So I am wondering if anyone has suggestions to make this possible? What would be the ideal values of commit_delay(higher/lower) and commit_siblings(higher/lower) to remedy this situation if that is the solution. Otherwise is there anything else I can do?
Let me know if you need any more info, thanks! I am very new, so feel free to correct me on anything.