Postgresql – Postgres caching and bytea

cachepostgresql

I have a DB which, among other things, stores images (as bytea, if that's interesting). It also stores users' metadata, activity logs, etc., but images currently take 500MB out of the total 600MB data.

I'm a complete newbie, but if I understand correctly PG has (at least) 2 pools of buffers, one for indexes and one for "heap" (data).

I imagine that my images 'pollute' the heap cache, making virtually nothing else cached (the cache obviously is not large enough to accommodate all the images).

Can I stop the image blocks only (I don't mind the image indexes being cached) from being cached?

Best Answer

I think the first question is how much RAM the system has. If you have enough that the database fits comfortably in RAM then this is not a major problem. Basically the OS disk cache is very fast, and if the db is sitting there effectively, then you don't have anything to worry about. If you are on a system with, say, 4GB RAM and not a whole lot else running, I wouldn't worry.

The problem happens when this is not true, and then the shared buffers try to hold the most frequently used information. I would expect that caching would be ok there too. What you have to remember is that the PostgreSQL cache is slower than the OS cache because it provides features aimed at keeping frequently used data in the cache.

Of course all of the above depends highly on your query patterns.