PostgreSQL - Commands

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 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;