Cheatsheet - Postgres Admin

A few notes and reminders on commands to help adminster a postgres db.

betting web development

June 1, 2025

Introduction

To help my fading memory...

This post will contain a few random squeels to help in the administration of postgres dbs. These have been picked up from stack overflow. I will add to these as and when I come across notes that I have dotted around various apps, text files and readme's.

Where there are variables (table names or column names etc. These are indicated in [square brackets].

To get the size of the entire database in MB


                select pg_database_size('fearnought_db')/1024/1024;

Get the size of all tables in order


                
                select
                table_name,
                pg_size_pretty(pg_total_relation_size(quote_ident(table_name))),
                pg_total_relation_size(quote_ident(table_name))
                from information_schema.tables
                where table_schema = 'public'
                order by 3 desc;

Get db block size


                
                SELECT current_setting('block_size');

Get size of specified row

When concerned about table siez (num columns)


                
               
                SELECT pg_column_size([TABLE].*) FROM [TABLE] 

                WHERE id = [row id]

Get number of columns in a table


                
                SELECT COUNT(*) 

                FROM INFORMATION_SCHEMA.COLUMNS 

                WHERE table_catalog = '[DB NAME]' -- the database 

                AND table_name = '[TABLE NAME]'

Get average row size


                
               SELECT AVG(pg_column_size([TABLE NAME].*)) FROM [TABLE NAME];

Get MAX row size


                
               SELECT MAX(pg_column_size([TABLE NAME].*)) FROM [TABLE NAME];