postgres system dictionary slowniki pg_catalog - ghdrako/doc_snipets GitHub Wiki

• https://www.postgresql.org/docs/current/catalogs.html
• https://www.postgresql.org/docs/current/catalogs-overview.html
• https://pgpedia.info/version-charts/system-catalogue-tables.html
• https://pgpedia.info/p/pg_init_privs.html - lista slownikow/tabel systemowych
• https://github.com/ghdrako/doc_snipets/wiki/postgres-system-catalog

Note: In PostgreSQL, A new database is initialized with a pre-defined set of schemas and those are: public: A default Schema; Tables will get created in public schema by default if the schema detail is not provided during the table creation.

• information_schema:Views in information_schema are based on pg_catalog.INFORMATION_SCHEMA data dictionary is SQL standard and doesn't change between postgres versions. Contains information about the database objects defined in the current database. An information schema, named information_schema, contains information about tables, columns, users, and other aspects of the database. Because the information schema does not contain information specific to the database management software, the system catalog should be checked for more detailed information.And pg_catalog is much faster!!!! views in the information schema often join many tables from the system catalogs to meet a strictly standardized format - why are slow
• pg_catalog: Stores the metadata information about the database and cluster. Updated when ANALYZE or VACUM are performed. Catalogs in pg_catalog are typically prefixed with 'pg_' in example pg_database.
• pg_toast: Hold TOAST storage for large tables.

User information is created in the global area of the database cluster and is common to all databases in the cluster. Settings, such as whether each user is allowed to connect and whether operations on individual objects are allowed, can be set on each database. However, CREATE USER is a user creation for a database cluster, not an operation on individual database.

Name	Description
pg_stat_all_tables	Analiza aktywności na wszystkich tabelach. Sprawdzanie liczby operacji seq_scan, idx_scan, n_tup_ins, n_tup_upd, n_tup_del itd.Monitorowanie, które tabele są najczęściej używane.
pg_stat_user_tables	statystyki dotyczące tylko tabel utworzonych przez użytkownika (pomija systemowe)
pg_stat_sys_tables	statystyki dotyczące tylko tabel systemowych PostgreSQL (np. pg_class, pg_attribute, itp.)
pg_statio_user_tables	Zawiera dane dotyczące I/O (wejścia/wyjścia dysku) dla tabel użytkownika:Pozwala sprawdzić, ile danych zostało załadowanych z dysku, a ile z cache (bufferów).Analiza wydajności dyskowej – np. które tabele powodują najwięcej odczytów z dysku (heap_blks_read) vs z pamięci (heap_blks_hit).
pg_statio_user_indexes	Zawiera statystyki I/O dla indeksów użytkownikaUmożliwia ocenę, które indeksy są najczęściej używane.Pomaga zidentyfikować nieużywane indeksy (które można usunąć).Może wskazać indeksy, które powodują dużo odczytów z dysku – potencjalnie do optymalizacji.Przydatne przy diagnozowaniu problemów z wydajnością.
pg_stat_database	globalne statystyki dla każdej bazy danych w klastrze PostgreSQL.

--Pokazuje 10 tabel z największą liczbą sekwencyjnych skanów.
SELECT schemaname, relname, seq_scan, idx_scan, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_all_tables
ORDER BY seq_scan DESC
LIMIT 10;
-- Tabele z największą liczbą "martwych" (dead) rekordów – potencjalni kandydaci do VACUUM.
SELECT relname, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
--Aktywność na tabelach systemowych – przydatne dla zaawansowanych analiz.
SELECT relname, seq_scan, n_tup_ins, n_tup_upd
FROM pg_stat_sys_tables
ORDER BY seq_scan DESC;
--Tabele z najgorszym stosunkiem cache hitów – mogą obciążać dysk.
SELECT relname,
       heap_blks_read,
       heap_blks_hit,
       ROUND(heap_blks_hit::numeric / (heap_blks_hit + heap_blks_read + 1) * 100, 2) AS cache_hit_ratio
FROM pg_statio_user_tables
ORDER BY cache_hit_ratio ASC
LIMIT 10;

-- Indeksy z najgorszym wykorzystaniem cache – do przemyślenia, czy są potrzebne / efektywne.
SELECT relname AS table_name,
       indexrelname AS index_name,
       idx_blks_read,
       idx_blks_hit,
       ROUND(idx_blks_hit::numeric / (idx_blks_hit + idx_blks_read + 1) * 100, 2) AS index_cache_hit_ratio
FROM pg_statio_user_indexes
ORDER BY index_cache_hit_ratio ASC
LIMIT 10;

-- Statystyki transakcji i odczytów dla każdej bazy – pozwala porównać ich aktywność.
SELECT datname, xact_commit, xact_rollback, blks_read, blks_hit,
       ROUND(blks_hit::numeric / (blks_hit + blks_read + 1) * 100, 2) AS cache_hit_ratio
FROM pg_stat_database
ORDER BY xact_commit DESC;

List databases

select oid as database_id,
       datname as database_name,
       datallowconn as allow_connect,
       datconnlimit as connection_limit
from pg_database
order by oid;

Namespace

SELECT * FROM pg_catalog.pg_namespace ORDER BY nspname;

Database size

SELECT sum(pg_database_size(datname)) from pg_database;

List tables

SELECT * FROM pg_catalog.pg_tables;
SELECT 'alter table '||tablename||' rename to bak_'||tablename||';' FROM pg_catalog.pg_tables where schemaname = 'ebk' and tablename like 'imp%' order by tablename ;
SELECT 'drop table if exists '||tablename||' cascade;' FROM pg_catalog.pg_tables where schemaname = 'ebk' and tablename like 'bak_imp%' order by tablename ;
SELECT 'truncate table '||tablename||';' FROM pg_catalog.pg_tables where schemaname = 'aci_tmp_db' and tablename like 'e%' order by tablename ;

List tables without PK

select tab.table_schema,
       tab.table_name
from information_schema.tables tab
left join information_schema.table_constraints tco 
          on tab.table_schema = tco.table_schema
          and tab.table_name = tco.table_name 
          and tco.constraint_type = 'PRIMARY KEY'
where tab.table_type = 'BASE TABLE'
      and tab.table_schema not in ('pg_catalog', 'information_schema')
      and tco.constraint_name is null
order by table_schema,
         table_name;

Columny

SELECT *
  FROM information_schema.columns
 WHERE table_schema = 'your_schema'
   AND table_name   = 'your_table'
     ;

Information schema is slow

SELECT attrelid::regclass AS tbl
     , attname            AS col
     , atttypid::regtype  AS datatype
       -- more attributes?
FROM   pg_attribute
WHERE  attrelid = 'myschema.mytable'::regclass  -- table name optionally schema-qualified
AND    attnum > 0
AND    NOT attisdropped
ORDER  BY attnum;

SELECT json_object_keys(to_json(json_populate_record(NULL::schema_name.table_name, '{}'::JSON)))

Estimate rows in table

CREATE OR REPLACE FUNCTION estimated_row_count(text)
RETURNS bigint
LANGUAGE sql
AS $$
SELECT (CASE WHEN reltuples > 0 THEN
               pg_relation_size($1)*reltuples/(8192*relpages)
             ELSE 0
             END)::bigint
FROM pg_class
WHERE oid = $1::regclass;
$$;

select estimated_row_count('myschema.mytable');

SSL

select datname, usename, ssl, client_addr from pg_stat_ssl

List installed extensions

SELECT * FROM pg_extension;

List users

SELECT usename AS role_name,
  CASE 
     WHEN usesuper AND usecreatedb THEN 
	   CAST('superuser, create database' AS pg_catalog.text)
     WHEN usesuper THEN 
	    CAST('superuser' AS pg_catalog.text)
     WHEN usecreatedb THEN 
	    CAST('create database' AS pg_catalog.text)
     ELSE 
	    CAST('' AS pg_catalog.text)
  END role_attributes
FROM pg_catalog.pg_user
ORDER BY role_name desc;

List session

select pid as process_id, 
       usename as username, 
       datname as database_name, 
       client_addr as client_address, 
       application_name,
       backend_start,
       state,
       state_change
from pg_stat_activity;