PostgreSQL - Commands

Command	Description
\d table	Describe table (columns, types, constraints)
\d+ table	Detailed describe (+ storage, comments)
\dt	List tables
\dt+ schema.*	List tables in schema with sizes
\di table	List indexes on table
\dv	List views
\ds	List sequences
\df	List functions
\dn	List schemas
\du	List roles/users
\l	List all databases
\sf func	Show function DDL (CREATE FUNCTION)
\sv view	Show view DDL (CREATE VIEW)
\x	Toggle expanded/vertical output
\timing	Toggle query execution timing
\conninfo	Show current connection info
\e	Open query in external editor

psql meta commands

Command	Description
\d table	Describe table (columns, types, constraints)
\d+ table	Detailed describe (+ storage, comments)
\dt	List tables
\dt+ schema.*	List tables in schema with sizes
\di table	List indexes on table
\dv	List views
\ds	List sequences
\df	List functions
\dn	List schemas
\du	List roles/users
\l	List all databases
\sf func	Show function DDL (CREATE FUNCTION)
\sv view	Show view DDL (CREATE VIEW)
\x	Toggle expanded/vertical output
\timing	Toggle query execution timing
\conninfo	Show current connection info
\e	Open query in external editor

Query metadata

List tablename along with it's Indexes:

SELECT CONCAT(n.nspname,'.', c.relname) AS table,

i.relname AS index_name FROM pg_class c

JOIN pg_index x ON c.oid = x.indrelid

JOIN pg_class i ON i.oid = x.indexrelid LEFT JOIN pg_namespace n ON n.oid = c.relnamespace

WHERE c.relkind = ANY (ARRAY['r', 't']) AND c.relname like '<table_name>';

Table related

To get the table size:

select pg_size_pretty(pg_relation_size('table_name'));.

To check the table bloat:

select (pgstattuple('table_name')).* as x; --there is a better way than this and use below one

select (x).* from (pgstattuple('table_name')) as x;.

Extensions

List tablename along with it's Indexes:

select * from pg_extension; --List all the PostgreSQL extensions that are available to install on the system. These are extensions that are present on the Server's extension directory.

select * from pg_available_extensions; --Lists only the extensions that are currently installed in your current database.

Performance Tuning

To see work memory:
SHOW work_mem;

work_mem is a PostgreSQL configuration setting that determines the amount of memory to be used for internal sort operations and hash tables before writing to temporary disk files. It is set per operation, not per query, meaning if a query has multiple sorts or joins, each one can use up to work_mem.

To see shared_buffers:
SHOW shared_buffers;

shared_buffers determines how much memory PostgreSQL allocates for caching data blocks in memory, essentially acting as PostgreSQL's internal cache before going to the operating system cache or disk.

It's one of the most important settings for PostgreSQL performance tuning

To see shared_buffers:
SHOW shared_buffers;

shared_buffers determines how much memory PostgreSQL allocates for caching data blocks in memory, essentially acting as PostgreSQL's internal cache before going to the operating system cache or disk.

It's one of the most important settings for PostgreSQL performance tuning.

To list the blocking apps

SELECT pid, pg_blocking_pids(pid), wait_event, wait_event_type, substr(query, 1, 100) as query

FROM pg_stat_activity

WHERE backend_type = 'client backend' AND wait_event_type = 'Lock';

To list the long running apps

SELECT pid, now() - pg_stat_activity.query_start AS duration

FROM pg_stat_activity

WHERE (now() - pg_stat_activity.query_start) > interval '15 minutes';

To list the lock details

SELECT pid, usename, datname, locktype, relation::regclass, mode, granted, query

FROM pg_locks

JOIN pg_stat_activity USING (pid)

WHERE NOT granted;

To print privileges/roles on table: update "schemaname" and  "tablename"

WITH table_privs AS (

    SELECT

        grantor,

        grantee,

        string_agg(privilege_type, ', ' ORDER BY privilege_type) AS table_privileges,

        bool_or(is_grantable = 'YES') AS has_grant_option

    FROM information_schema.table_privileges

    WHERE table_schema = 'schemaname'

      AND table_name = 'tablename'

    GROUP BY grantor, grantee

),

schema_access AS (

    SELECT

        r.rolname AS grantee,

        has_schema_privilege(r.rolname, 'schemaname', 'USAGE') AS schema_usage,

        has_schema_privilege(r.rolname, 'schemaname', 'CREATE') AS schema_create

    FROM pg_roles r

    WHERE r.rolname IN (SELECT grantee FROM table_privs)

),

role_members AS (

    SELECT

        r.rolname AS grantee,

        string_agg(g.rolname, ', ') AS member_of_roles

    FROM pg_roles r

    LEFT JOIN pg_auth_members m ON m.member = r.oid

    LEFT JOIN pg_roles g ON g.oid = m.roleid

    WHERE r.rolname IN (SELECT grantee FROM table_privs)

    GROUP BY r.rolname

)

SELECT

    t.grantor,

    t.grantee,

    t.table_privileges,

    t.has_grant_option,

    s.schema_usage,

    s.schema_create,

    r.member_of_roles

FROM table_privs t

LEFT JOIN schema_access s ON s.grantee = t.grantee

LEFT JOIN role_members r ON r.grantee = t.grantee

ORDER BY t.grantee, t.grantor;

To print Partition details of table

SELECT

    n.nspname AS schema,

    t.relname AS table_name,

    CASE

        WHEN t.relkind = 'p' THEN 'Parent (Partitioned)'

        WHEN t.relkind = 'r' THEN 'Partition'

    END AS table_type,

    pg_get_expr(t.relpartbound, t.oid) AS partition_range,

    c.reltuples::bigint AS estimated_rows,

    t.relpages AS pages,

    pg_size_pretty(pg_total_relation_size(t.oid)) AS total_size,

    pg_size_pretty(pg_relation_size(t.oid)) AS table_size,

    pg_size_pretty(pg_indexes_size(t.oid)) AS index_size,

    s.n_live_tup AS live_rows,

    s.n_dead_tup AS dead_rows,

    CASE

        WHEN s.n_live_tup > 0

        THEN round(100.0 * s.n_dead_tup / s.n_live_tup, 2)

        ELSE 0

    END AS bloat_pct,

    s.seq_scan,

    s.idx_scan,

    s.last_vacuum,

    s.last_autovacuum,

    s.last_analyze,

    s.last_autoanalyze

FROM

    pg_class t

JOIN

    pg_namespace n ON n.oid = t.relnamespace

LEFT JOIN

    pg_stat_user_tables s ON s.relid = t.oid

WHERE

    n.nspname = 'schemaname'

    AND (t.relname = 'tablename' OR t.relname LIKE 'tablename%')

    AND t.relkind IN ('p', 'r')  -- p = partitioned parent, r = regular table/partition

ORDER BY

    t.relname;

To analyze table level statistics:
SELECT relname, seq_scan, idx_scan, n_tup_ins, n_tup_upd, n_tup_del FROM pg_stat_user_tables ORDER BY seq_scan DESC LIMIT 10;

• Tables with very high seq_scan(full table scan) but low idx_scan could be missing indexes.
• High update/delete counts could suggest frequent data changes, which may impact bloat and vacuuming.

To see shared_buffers:
SHOW shared_buffers;

shared_buffers determines how much memory PostgreSQL allocates for caching data blocks in memory, essentially acting as PostgreSQL's internal cache before going to the operating system cache or disk.

It's one of the most important settings for PostgreSQL performance tuning.

Replication related

Identify Orphaned Replication slots
select * from pg_replication_slots;