https://postgres.ai/assets/js/b6a33c94.057aa8dd.js

1"use strict";(self.webpackChunkdatabase_lab_docs=self.webpackChunkdatabase_lab_docs||[]).push([[17094],{24946(e,t,s){s.r(t),s.d(t,{assets:()=>c,contentTitle:()=>r,default:()=>h,frontMatter:()=>o,metadata:()=>n,toc:()=>l});const n=JSON.parse('{"id":"postgres-howtos/advanced-topics/misc/how-to-work-with-metadata","title":"How to work with metadata","description":"","source":"@site/docs/postgres-howtos/advanced-topics/misc/how-to-work-with-metadata.md","sourceDirName":"postgres-howtos/advanced-topics/misc","slug":"/postgres-howtos/advanced-topics/misc/how-to-work-with-metadata","permalink":"/docs/postgres-howtos/advanced-topics/misc/how-to-work-with-metadata","draft":false,"unlisted":false,"editUrl":"https://gitlab.com/postgres-ai/docs/-/edit/master/docs/postgres-howtos/advanced-topics/misc/how-to-work-with-metadata.md","tags":[{"inline":true,"label":"beginner","permalink":"/docs/tags/beginner"},{"inline":true,"label":"metadata","permalink":"/docs/tags/metadata"},{"inline":true,"label":"system-catalogs","permalink":"/docs/tags/system-catalogs"},{"inline":true,"label":"oid","permalink":"/docs/tags/oid"},{"inline":true,"label":"regclass","permalink":"/docs/tags/regclass"},{"inline":true,"label":"administration","permalink":"/docs/tags/administration"}],"version":"current","frontMatter":{"title":"How to work with metadata","sidebar_label":"work with metadata","description":"","keywords":["postgresql","work","with","metadata","beginner"],"tags":["beginner","metadata","system-catalogs","oid","regclass","administration"],"difficulty":"beginner","estimated_time":"5 min"},"sidebar":"baseSidebar","previous":{"title":"help others","permalink":"/docs/postgres-howtos/advanced-topics/misc/how-to-help-others"},"next":{"title":"compile PG on Ubuntu 22.04","permalink":"/docs/postgres-howtos/advanced-topics/misc/how-to-compile-postgres-on-ubuntu-22.04"}}');var a=s(74848),i=s(28453);const o={title:"How to work with metadata",sidebar_label:"work with metadata",description:"",keywords:["postgresql","work","with","metadata","beginner"],tags:["beginner","metadata","system-catalogs","oid","regclass","administration"],difficulty:"beginner",estimated_time:"5 min"},r=void 0,c={},l=[{value:"::oid, ::regclass",id:"oid-regclass",level:2},{value:"? and ECHO_HIDDEN",id:"-and-echo_hidden",level:2},{value:"Performance",id:"performance",level:2},{value:"INFORMATION_SCHEMA",id:"information_schema",level:2},{value:"pg_stat_activity is not a table",id:"pg_stat_activity-is-not-a-table",level:2}];function d(e){const t={a:"a",code:"code",h2:"h2",li:"li",ol:"ol",p:"p",pre:"pre",ul:"ul",...(0,i.R)(),...e.components};return(0,a.jsxs)(a.Fragment,{children:[(0,a.jsx)(t.p,{children:"When working with metadata \u2013 data about data \u2013 in Postgres, these reference docs are worth using:"}),"\n",(0,a.jsxs)(t.ul,{children:["\n",(0,a.jsx)(t.li,{children:(0,a.jsx)(t.a,{href:"https://postgresql.org/docs/current/catalogs.html",children:"System Catalogs"})}),"\n",(0,a.jsx)(t.li,{children:(0,a.jsx)(t.a,{href:"https://postgresql.org/docs/current/views.html",children:"System Views"})}),"\n",(0,a.jsx)(t.li,{children:(0,a.jsx)(t.a,{href:"https://postgresql.org/docs/current/monitoring-stats.html",children:"The Cumulative Statistics System"})}),"\n",(0,a.jsx)(t.li,{children:(0,a.jsx)(t.a,{href:"https://postgresql.org/docs/current/information-schema.html",children:"The Information Schema"})}),"\n"]}),"\n",(0,a.jsx)(t.p,{children:"It's unnecessary to repeat the material from the docs here. Instead, let's focus on some tricks and principles that make\nyour work more efficient. We'll cover these topics:"}),"\n",(0,a.jsxs)(t.ul,{children:["\n",(0,a.jsxs)(t.li,{children:[(0,a.jsx)(t.code,{children:"::oid"}),", ",(0,a.jsx)(t.code,{children:"::regclass"})]}),"\n",(0,a.jsxs)(t.li,{children:[(0,a.jsx)(t.code,{children:"\\?"})," and ",(0,a.jsx)(t.code,{children:"ECHO_HIDDEN"})]}),"\n",(0,a.jsx)(t.li,{children:"Performance"}),"\n",(0,a.jsx)(t.li,{children:(0,a.jsx)(t.code,{children:"INFORMATION_SCHEMA"})}),"\n",(0,a.jsxs)(t.li,{children:[(0,a.jsx)(t.code,{children:"pg_stat_activity"})," is not a table"]}),"\n"]}),"\n",(0,a.jsx)(t.h2,{id:"oid-regclass",children:"::oid, ::regclass"}),"\n",(0,a.jsxs)(t.p,{children:['In Postgres terminology, tables, indexes, views, materialized views are all called "relations". The metadata about them\ncan be seen in various ways, but the "central" place is the\n',(0,a.jsx)(t.a,{href:"https://postgresql.org/docs/current/catalog-pg-class.html",children:"pg_class system catalog"}),". In other words, this is a table\nthat stores\ninformation about all tables, indexes, and so on. It has two keys:"]}),"\n",(0,a.jsxs)(t.ul,{children:["\n",(0,a.jsxs)(t.li,{children:["PK: ",(0,a.jsx)(t.code,{children:"oid"})," - a number (",(0,a.jsx)(t.a,{href:"https://postgresql.org/docs/current/datatype-oid.html",children:"OID, object identifier"}),")"]}),"\n",(0,a.jsxs)(t.li,{children:["UK: a pair of columns ",(0,a.jsx)(t.code,{children:"(relname, relnamespace)"}),", relation name and OID of the schema."]}),"\n"]}),"\n",(0,a.jsxs)(t.p,{children:["A trick to remember: OID can be quickly converted to relation name, and vice versa, using type conversion to ",(0,a.jsx)(t.code,{children:"oid"})," and\n",(0,a.jsx)(t.code,{children:"regclass"})," datatypes."]}),"\n",(0,a.jsxs)(t.p,{children:["Simple examples for a table named ",(0,a.jsx)(t.code,{children:"t1"}),":"]}),"\n",(0,a.jsx)(t.pre,{children:(0,a.jsx)(t.code,{children:"nik=# select 't1'::regclass;\n regclass\n----------\n t1\n(1 row)\n\nnik=# select 't1'::regclass::oid;\n  oid\n-------\n 74298\n(1 row)\n\nnik=# select 74298::regclass;\n regclass\n----------\n t1\n(1 row)\n"})}),"\n",(0,a.jsxs)(t.p,{children:["So, there is no need to do ",(0,a.jsx)(t.code,{children:"select oid from pg_class where relname = ..."})," \u2013 just memorize ",(0,a.jsx)(t.code,{children:"::regclass"})," and ",(0,a.jsx)(t.code,{children:"::oid"}),"."]}),"\n",(0,a.jsx)(t.h2,{id:"-and-echo_hidden",children:"? and ECHO_HIDDEN"}),"\n",(0,a.jsxs)(t.p,{children:[(0,a.jsx)(t.code,{children:"psql"}),"'s ",(0,a.jsx)(t.code,{children:"\\?"})," command is crucial \u2013 this is how you can find descriptions for all c

1ommands. For example:"]}),"\n",(0,a.jsx)(t.pre,{children:(0,a.jsx)(t.code,{children:"\\d[S+]                 list tables, views, and sequences\n"})}),"\n",(0,a.jsxs)(t.p,{children:['The "describing" commands produce some SQL implicitly \u2013 and it can be helpful to "spy" on them. For that, we first need\nto turn on ',(0,a.jsx)(t.code,{children:"ECHO_HIDDEN"}),":"]}),"\n",(0,a.jsx)(t.pre,{children:(0,a.jsx)(t.code,{children:"nik=# \\set ECHO_HIDDEN on\n"})}),"\n",(0,a.jsxs)(t.p,{children:["\u2013 or just use the option ",(0,a.jsx)(t.code,{children:"-E"})," when starting ",(0,a.jsx)(t.code,{children:"psql"}),". And then we can start spying:"]}),"\n",(0,a.jsx)(t.pre,{children:(0,a.jsx)(t.code,{children:"nik=# \\d t1\n/********* QUERY **********/\nSELECT c.oid,\n n.nspname,\n c.relname\nFROM pg_catalog.pg_class c\n    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\nWHERE c.relname OPERATOR(pg_catalog.~) '^(t1)$' COLLATE pg_catalog.default\n AND pg_catalog.pg_table_is_visible(c.oid)\nORDER BY 2, 3;\n/**************************/\n\n[... + more queries to get info about \"t1\" ...]\n"})}),"\n",(0,a.jsx)(t.p,{children:"Examining these queries can assist in building various tooling to work with metadata."}),"\n",(0,a.jsx)(t.h2,{id:"performance",children:"Performance"}),"\n",(0,a.jsx)(t.p,{children:"In some cases, metadata queries can be heavy, slow. Here's what to do if it's so:"}),"\n",(0,a.jsxs)(t.ol,{children:["\n",(0,a.jsxs)(t.li,{children:["\n",(0,a.jsx)(t.p,{children:"Consider caching to reduce the frequency and necessity of metadata queries."}),"\n"]}),"\n",(0,a.jsxs)(t.li,{children:["\n",(0,a.jsxs)(t.p,{children:["Check for catalog bloat. For example ",(0,a.jsx)(t.code,{children:"pg_class"})," can be bloated due to frequent DDL, use of temp tables, etc. In this\ncase, unfortunately, a ",(0,a.jsx)(t.code,{children:"VACUUM FULL"})," is needed (",(0,a.jsx)(t.code,{children:"pg_repack"})," cannot repack system catalogs). If you need it, don't\nforget the golden rule of zero-downtime DDLs in Postgres \u2013\n",(0,a.jsx)(t.a,{href:"https://postgres.ai/blog/20210923-zero-downtime-postgres-schema-migrations-lock-timeout-and-retries",children:"use low lock_t

1imeout and retries"}),"."]}),"\n"]}),"\n"]}),"\n",(0,a.jsx)(t.h2,{id:"information_schema",children:"INFORMATION_SCHEMA"}),"\n",(0,a.jsxs)(t.p,{children:['System catalogs and views are "native" ways to query table and index metadata \u2013 not standard, though. The standard way\nis called ',(0,a.jsx)(t.code,{children:"INFORMATION_SCHEMA"})," and Postgres supports it following the SQL\nstandard: ",(0,a.jsx)(t.a,{href:"https://postgresql.org/docs/current/information-schema.html",children:"Docs"}),". What to use:"]}),"\n",(0,a.jsxs)(t.ul,{children:["\n",(0,a.jsx)(t.li,{children:"Use the information schema for simple, cross-database compatible metadata queries."}),"\n",(0,a.jsx)(t.li,{children:"Use native system catalogs for more complex, Postgres-specific queries or when you need detailed internal information."}),"\n"]}),"\n",(0,a.jsx)(t.h2,{id:"pg_stat_activity-is-not-a-table",children:"pg_stat_activity is not a table"}),"\n",(0,a.jsx)(t.p,{children:"It's essential to remember that when querying metadata, you might deal with something that doesn't behave as a normal\ntable even if it looks so."}),"\n",(0,a.jsxs)(t.p,{children:["For instance, when you read records from ",(0,a.jsx)(t.code,{children:"pg_stat_activity"}),", you're not dealing with a consistent snapshot of table\ndata: reading the first and, theoretically, the last rows are produced at different moments of time, and you might see\nthe queries which were not running simultaneously."]}),"\n",(0,a.jsxs)(t.p,{children:["This phenomenon also explains why ",(0,a.jsx)(t.code,{children:"select now() - query_start from pg_stat_activity;"})," might give you negative values:\nthe function ",(0,a.jsx)(t.code,{children:"now()"})," is executed at the beginning of the transaction, once, and doesn't change its value inside the\ntransaction, no matter how many times you call it."]}),"\n",(0,a.jsxs)(t.p,{children:["To get precise time intervals, use ",(0,a.jsx)(t.code,{children:"clock_timestamp()"})," instead\n(",(0,a.jsx)(t.code,{children:"select clock_timestamp() - query_start from pg_stat_activity;"}),")."]})]})}function h(e={}){const{wrapper:t}={...(0,i.R)(),...e.components};return t?(0,a.jsx)(t,{...e,children:(0,a.jsx)(d,{...e})}):d(e)}},28453(e,t,s){s.d(t,{R:()=>o,x:()=>r});var n=s(96540);const a={},i=n.createContext(a);function o(e){const t=n.useContext(i);return n.useMemo(function(){return"function"==typeof e?e(t):{...t,...e}},[t,e])}function r(e){let t;return t=e.disableParentContext?"function"==typeof e.components?e.components(a):e.components||a:o(e.components),n.createElement(i.Provider,{value:t},e.children)}}}]);