PostgreSQL – shared buffers expiration

postgresql

I noticed a performance issue with my SQL queries. The same query executed for the first time took ~7 seconds, consecutive executions took around 50 ms.

I tried the same query another day and once again, first one took ~7 seconds and next ones 50-60 ms.

I used EXPLAIN then:

First run:

main=> EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(*) FROM txes INNER JOIN accounts ON txes.account_id = accounts.id WHERE accounts.user_id = 135736;

                                                                                                 QUERY PLAN                                                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 Aggregate  (cost=8564.89..8564.90 rows=1 width=8) (actual time=7854.024..7854.024 rows=1 loops=1)
   Buffers: shared hit=3636 read=2052
   ->  Nested Loop  (cost=0.98..8562.64 rows=899 width=0) (actual time=3.901..7852.603 rows=9650 loops=1)
         Buffers: shared hit=3636 read=2052
         ->  Index Scan using index_user_accounts_on_user_id on user_accounts  (cost=0.42..6.03 rows=5 width=4) (actual time=0.016..0.087 rows=31 loops=1)
               Index Cond: (user_id = 135736)
               Buffers: shared hit=29
         ->  Index Only Scan using index_money_transactions_on_user_account_id_and_transaction_on on money_transactions  (cost=0.56..1694.89 rows=1643 width=4) (actual time=1.162..253.254 rows=311 loops=31)
               Index Cond: (user_account_id = user_accounts.id)
               Heap Fetches: 9650
               Buffers: shared hit=3607 read=2052
 Planning time: 0.217 ms
 Execution time: 7854.055 ms
(13 rows)

Next run:

main=> EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(*) FROM txes INNER JOIN accounts ON txes.account_id = accounts.id WHERE accounts.user_id = 135736;

                                                                                                 QUERY PLAN                                                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=8564.89..8564.90 rows=1 width=8) (actual time=14.739..14.739 rows=1 loops=1)
   Buffers: shared hit=5688
   ->  Nested Loop  (cost=0.98..8562.64 rows=899 width=0) (actual time=0.068..14.146 rows=9650 loops=1)
         Buffers: shared hit=5688
         ->  Index Scan using index_accounts_on_user_id on accounts  (cost=0.42..6.03 rows=5 width=4) (actual time=0.037..0.077 rows=31 loops=1)
               Index Cond: (user_id = 135736)
               Buffers: shared hit=29
         ->  Index Only Scan using index_txes_on_account_id_and_tx_on on txes  (cost=0.56..1694.89 rows=1643 width=4) (actual time=0.010..0.424 rows=311 loops=31)
               Index Cond: (account_id = accounts.id)
               Heap Fetches: 9650
               Buffers: shared hit=5659
 Planning time: 1.060 ms
 Execution time: 14.805 ms
(13 rows)

So it seems that shared buffers do not contain all the data. And as far as I understand shared buffers represent RAM memory.

So my question is how long the data stays in shared buffers, until the machine is rebooted or is there some kind of validity/expiration period?

Total RAM on machine is 32GB. It is designed to run both web application and database. 12 GB are dedicated for DB.

  1. shared_buffers = 3000MB
  2. effective_cache_size = 9000MB

Best Answer

Data never expires from shared buffers merely due to the passage of time. It will stay in shared buffers until it gets evicted to make room for some other data, until the PostgreSQL server is restarted, or until the object the data belongs to gets dropped (or truncated). If the data is getting evicted, it is probably because other activity in the database needs to work with a different part of the data and needs to make room for it.

The amount of effective_cache_size which is more than shared_buffers is not dedicated to the DB in any formal sense. It is an estimate only, it is not reserved or set aside. That memory is subject to the OS kernel's eviction algorithm, not PostgreSQL's.

Related Question