Postgres

Here are some interesting postgres queries.

List tables

To list all tables you can use the following command.

select * from pg_tables where schemaname = 'public';

Relation Sizes

To get a list of the size of the relations in the database you can use the following command.

SELECT table_name, pg_size_pretty(pg_relation_size(quote_ident(table_name))) AS size
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY pg_relation_size(quote_ident(table_name)) desc;
            

Table Sizes

To get a list of the sizes of the tables in the database you can use the following command.

SELECT table_name, pg_size_pretty(pg_table_size(quote_ident(table_name))) AS size
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY pg_table_size(quote_ident(table_name)) desc;
            

List indexes

To list all of the indexes you can use the following command.

SELECT indexname FROM pg_indexes WHERE schemaname = 'public';

Index Sizes

To get a list of the sizes of the indexes in the database you can use the following command.

SELECT indexname, pg_size_pretty(pg_relation_size(quote_ident(indexname)))
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(quote_ident(indexname)) desc;