I'm running a database with a few tables with large row counts (millions) but which are fairly small in data size. All the tables are append-only, so no deletes or updates ever happening, around 250 rows are added per minute. 99% of the time queries take less than 5ms. However, occasionally queries get terminated by Postgres for taking more than 200ms (my configured statement timeout).
I'm using default settings apart from
I'm running PostgreSQL 9.6.3 on AWS RDS on a t2.medium machine, with multi-az and connecting to it from AWS Lambda (Node.js 6.10) from within the same private VPC.
CPU stays under 7% at all times, disk read latency is less than 2 ms, number of concurrent connections rarely goes above 1. Average queries per second is quite consistent at around 20.
We've also tried turning
off, but the same timeouts continued to happen.
Could this be an issue with the auto-vacuum process slowing queries even though I've got no updates or deletes?
Update: Having removed all writes to the database, we still see the same issue. So, no inserts, no updates, no deletes, just ~30 selects per second causes timeouts.