1

i'am using this query to determite hit/read value for my databases

SELECT 
  datname, 
  CASE 
    WHEN blks_read = 0 THEN 0 
    ELSE blks_hit / blks_read 
  END AS ratio 
FROM 
  pg_stat_database;
  
 select * from pg_stat_database;

i have results, but the first column datname is NULL, and it has worst ratio

user: postgres

datname     |ratio  |
------------+-------+
NULL        |9984265|
template1   |      0|
template0   |      0|
postgres    |      9|
db1         |   2078|
db2         |    660|

what does the null mean here?

Drilla
  • 109
  • 8

1 Answers1

2

According to the documentation, pg_stat_databases contains a record for objects that are shared with multiple databases:

It has a datid = 0 ..

OID of this database, or 0 for objects belonging to a shared relation

.. and has no datname:

Name of this database, or NULL for the shared objects.

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
  • 2
    You were 20 seconds faster;) Your link is correct, but your quote refers to OID insted of datname. – Steeeve Aug 04 '21 at 10:40
  • @Steeeve 20 sec. means a lot in SO :-D The quote is the description of the column `datid` in the link I provided, as referred to `datid`. But you're right, it might be confusing. I just edited my answer. Thanks for the hint – Jim Jones Aug 04 '21 at 10:48
  • so as i can see, this might be an internal tables of postgres. A i right? – Drilla Aug 04 '21 at 14:30
  • @Drilla exactly, this is a postgres internal view. cheers – Jim Jones Aug 04 '21 at 14:32
  • @JimJones is there any way to reduce ratio for it? – Drilla Aug 04 '21 at 14:35
  • @Drilla possibly yes, but this would need a much better description of your cluster... and you'd need to ask another question with more details. – Jim Jones Aug 04 '21 at 14:37