I'm having difficulty finding 'lay' explanations of how indexes are cached in PostgreSQL, so I'd like a reality check on any or all of these assumptions:

  1. PostgreSQL indexes, like rows, live on disk but may be cached.
  2. An index may be entirely in the cache or not at all.
  3. Whether it is cached or not depends on how often it is used (as defined by the query planner).
  4. For this reason most 'sensible' indexes are going to be in the cache all the time.
  5. The indexes live in the same cache (the buffer cache?) as rows, and therefore cache space used by an index is not available to rows.

My motivation for understanding this follows on from another question I asked where it was suggested that partial indexes can be used on tables where a majority of the data will never be accessed.

Before undertaking this, I'd like to be clear that employing a partial index yields two advantages:

  1. We reduce the size of the index in the cache, freeing up more space for rows themselves in the cache.
  2. We reduce the size of the B-Tree, resulting in a faster query response.
  • 4
    Using a partial index is not only useful when a large part of data will be rarely accessed but also when certain values are very common. When a value is very common the planner will use a table scan anyway instead of the index so including the value in the index serves no purpose.
    – Eelke
    Commented Oct 13, 2012 at 14:07

2 Answers 2


Playing a bit with pg_buffercache, I could get answers to some of your questions.

  1. This is quite obvious, but the results for (5) also show that answer is YES
  2. I am yet to set up a good example for this, for now it is more yes than no :) (See my edit below, the answer is NO.)
  3. Since the planner is who decides whether to use an index or not, we can say YES, it decides caching (but this is more complicated)
  4. The exact details of caching could be derived from the source code, I couldn't find too much on this topic, except this one (see the author's answer, too). However, I'm pretty sure that this again is far more complicated than a simple yes or no. (Again, from my edit you can get some idea - since the cache size is limited, those 'sensible' indexes compete for available space. If they are too many, they will kick each other from cache - so the answer is rather NO.)
  5. As a simple query with pg_buffercache shows, the answer is a definitive YES. It is worth to note that temporary table data don't get cached here.


I've found Jeremiah Peschka's terrific article about table and index storage. With information from there, I could answer (2) as well. I set up a small test, so you can check these yourself.

-- we will need two extensions
CREATE EXTENSION pg_buffercache;

-- a very simple test table
CREATE TABLE index_cache_test (
      id serial
    , blah text

-- I am a bit megalomaniac here, but I will use this for other purposes as well
INSERT INTO index_cache_test
SELECT i, i::text || 'a'
FROM generate_series(1, 1000000) a(i);

-- let's create the index to be cached
CREATE INDEX idx_cache_test ON index_cache_test (id);

-- now we can have a look at what is cached
SELECT c.relname,count(*) AS buffers
    pg_class c 
    INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode 
    INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database())
GROUP BY c.relname

             relname              | buffers
 index_cache_test                 |    2747
 pg_statistic_relid_att_inh_index |       4
 pg_operator_oprname_l_r_n_index  |       4
... (others are all pg_something, which are not interesting now)

-- this shows that the whole table is cached and our index is not in use yet

-- now we can check which row is where in our index
-- in the ctid column, the first number shows the page, so 
-- all rows starting with the same number are stored in the same page
SELECT * FROM bt_page_items('idx_cache_test', 1);

 itemoffset |  ctid   | itemlen | nulls | vars |          data
          1 | (1,164) |      16 | f     | f    | 6f 01 00 00 00 00 00 00
          2 | (0,1)   |      16 | f     | f    | 01 00 00 00 00 00 00 00
          3 | (0,2)   |      16 | f     | f    | 02 00 00 00 00 00 00 00
          4 | (0,3)   |      16 | f     | f    | 03 00 00 00 00 00 00 00
          5 | (0,4)   |      16 | f     | f    | 04 00 00 00 00 00 00 00
          6 | (0,5)   |      16 | f     | f    | 05 00 00 00 00 00 00 00
         64 | (0,63)  |      16 | f     | f    | 3f 00 00 00 00 00 00 00
         65 | (0,64)  |      16 | f     | f    | 40 00 00 00 00 00 00 00

-- with the information obtained, we can write a query which is supposed to
-- touch only a single page of the index
    SELECT id 
    FROM index_cache_test 

 Index Scan using idx_test_cache on index_cache_test  (cost=0.00..8.54 rows=9 width=4) (actual time=0.031..0.042 rows=11 loops=1)
   Index Cond: ((id >= 10) AND (id <= 20))
   Buffers: shared hit=4
 Total runtime: 0.094 ms
(4 rows)

-- let's have a look at the cache again (the query remains the same as above)
             relname              | buffers
 index_cache_test                 |    2747
 idx_test_cache                   |       4

-- and compare it to a bigger index scan:
    FROM index_cache_test 
    WHERE id <= 20000 ORDER BY id

 Index Scan using idx_test_cache on index_cache_test  (cost=0.00..666.43 rows=19490 width=4) (actual time=0.072..19.921 rows=20000 loops=1)
   Index Cond: (id <= 20000)
   Buffers: shared hit=4 read=162
 Total runtime: 24.967 ms
(4 rows)

-- this already shows that something was in the cache and further pages were read from disk
-- but to be sure, a final glance at cache contents:

             relname              | buffers
 index_cache_test                 |    2691
 idx_test_cache                   |      58

-- note that some of the table pages are disappeared
-- but, more importantly, a bigger part of our index is now cached

All in all, this shows that indexes and tables can be cached page by page, therefore the answer for (2) is NO.

And a final one to illustrate temporary tables being non-cached here:

SELECT * FROM index_cache_test ORDER BY id FETCH FIRST 20000 ROWS ONLY;


-- checking the buffer cache now shows no sign of the temp table
  • link for answer 4 is down
    – cdalxndr
    Commented Aug 3, 2021 at 19:16
  • 1
    @cdalxndr thanks for the notice, I re-linked the doc at its present location. Commented Aug 25, 2021 at 12:09

Index pages are fetched when a query decides they will be useful to cut down on the amount of table data needed to answer a query. Only the blocks of the index navigated to accomplish that are read in. Yes, they go into the same shared_buffers pool where table data is stored. Both are also backed by the operating system cache as a second layer of caching.

You can easily have 0.1% of an index in memory or 100% of it. The idea that most "'sensible' indexes are going to be in the cache all the time" falls down hard when you have queries that only touch a subset of a table. A common example is if you have time oriented data. Often those are commonly navigating the recent end of the table, rarely vising old history. There you might find all of the index blocks needed to navigate to and around the recent end in memory, while very few needed to navigate the earlier records are there.

The complicated parts of the implementation aren't how blocks get into the buffer cache. It's the rules about when they leave. My Inside the PostgreSQL Buffer Cache talk and the sample queries included there can help you understand what's going on there, and see what is really accumulating on a production server. It can be surprising. There's a lot more on all these topics in my PostgreSQL 9.0 High Performance book too.

Partial indexes can be helpful because they reduce the size of the index, and therefore are both quicker to navigate and leave more RAM for caching other things. If your navigation of the index is such that the parts you touch are always in RAM, anyway, that might not buy a real improvement though.

  • I think there is an error in the presentation. On slide 3 it says "Each table gets a subdirectory". I think that should be "Each database gets a subdirectory", right?
    – user1822
    Commented Aug 25, 2021 at 13:05
  • Update link to the talk slides: cgi.cse.unsw.edu.au/~cs9315/21T1/readings/GregSmith2008.pdf Commented Apr 21, 2022 at 0:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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