Question posted in 
I have a problem with a postgresql database which slow down when some requests are done on old rarely used data.
This database contains data on appointments, messages, etc. Everything is dated, and requests are mostly for current and future times. Data about old appointments and messages is rarely used, but still needed for accounting and history.
On normal times, the server is very responsive. The web app show a 40ms response time with 15k requests per minute, 80ms on high traffic days (20k requests per minute).
The database is about 120GB in size.
The debian server memory is fulled used, roughly 2GB by postgresql and 29GB in disk cache.
Disk I/O usage show only disk writes, nearly no disk read.
However, if I make a request on old data, for example statistics on appointments made 2 years ago, the server show massive disk read spike (as expected), but meanwhile every other request is slowed down. The web app show a 250ms+ response time for the duration of the request.
The cpu usage doesn’t really increase while this is happening, staying at 40-60% usage.
This lag spikes are happening multiple times per day, and are really annoying, even if not critical. Anyone have any idea on how to reduce or eliminate this problem?
2
Answers
That is normal. As long as you don’t have enough memory to cache the whole database, rarely used data will not be cached and have to be read from disk. This is naturally slower.
To improve that, there are two options:
get enough RAM to cache the whole database
get faster storage
This can be solved programmaticlly.As you mentioned, your data includes hot data and cold data, whcih is divided by dates.So just divide hot data and cold data into two tables.
You can migrate cold data to a archived table automaticlly.And then every request should specify time range to search.For example, if the request requested current and future times, then access hot data table.