Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Interesting idea. There are two levels of caching but AFAIK Pg doesn't separate index and heap blocks in shared_buffers. The two levels are the shared_buffers cache and the underlying operating system disk cache. I'm not aware of any way to tell Pg to avoid caching certain data, and while there are facilities like the posix_fadvise call with POSIX_FADV_NOREUSE flag to offer caching hints to the OS I'm not sure Pg can use them for the purpose you want. –  Craig Ringer Mar 27 '13 at 11:56
add comment

1 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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.