top of page
Волнистый абстрактный фон

PostgreSQL: database statistics

The window presents a variety of statistics on the operation of cluster databasesPostgreSQL

Database stats

Presented pg_stat_database system view information

PostgreSQL database statistics

Database statistics

SELECT db.datid,
 FROM pg_stat_database db 
   JOIN pg_stat_database_conflicts slave 
      ON db.datid=slave.datid
where db.datname=$$DBNAME


Viewing the system table pg_config of a PostgreSQL database

PostgreSQL view pg_config

select * from pg_config


Viewing the pg_file_settings system table  PostgreSQL databases

PostgreSQL view pg_file_settings

select f.sourcefile,, 
 from pg_file_settings f

Language support

List of programming languages supported by PostgreSQL cluster

PostgreSQL view language_support

select l.lanname,
 from pg_language l 
   left join pg_authid a 
      on l.lanowner=a.oid

Percentage before freezing

The page displays information about the performance of the PostgreSQL cluster's autovacuum processes. A percentage in the "Percentage before freezing" field close to zero indicates that the autovacuum does not have time to clean up the tables and there is a high probability of a situation in which it will be necessary to switch the database to single-user mode and perform vacuum full .

With a large number of tables with a low percentage, it is necessary to analyze the values of the auto vacuum parameters in order to change them to more aggressive values.

PostgreSQL show freeze info

with rel_frozen as (
   select c.oid::regclass as table_name,
          (select e.nspname 
             from pg_catalog.pg_namespace e 
             where oid = c.relnamespace) as schema,
           greatest(age(c.relfrozenxid), age(t.relfrozenxid)) as age,
           greatest(age(c.relfrozenxid), age(t.relfrozenxid))*100./
               (pow(2,31)-1) as perc_wrpa
     from pg_class c 
        left join pg_class t on c.reltoastrelid = t.oid
     where c.relkind in ('r','m')
select table_name::text "table", 
      100 - round(perc_wrpa::numeric,2) "froz" 
 from rel_frozen order by 3

bottom of page