PostgreSQL tips

Here are some tips to manipulate or inspect postgresql

To know the size of your table

# SELECT relname as table_name, pg_size_pretty(pg_total_relation_size(relid)) as total_size, pg_size_pretty(pg_relation_size(relid)) as table_size, pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as index_size FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

And for a specific schema, just add the condition

WHERE schemaname = 'warehouse'

Expanded display

With MySQL, ending the request by \G pretty display the result, in Postgres, you’ll have to active it using \x

# \x
Expanded display is on.

Use the same command to inactive it

Compare 2 tables

SELECT <table_name> will return all the fields of the table. Then to compare fields by fields :

SELECT table_1, table_2
FROM table_1
FULL OUTER JOIN table_2 USING (id)
WHERE table_1 <> table_2;

Get which request lock a table

SELECT                                                                                                                                    c.relname AS locked_table,                                                                                                                        a.pid AS blocked_pid,                                                                                                                             a.query AS blocked_query,                                                                                                                         l.mode AS lock_mode                                                                                                                           FROM                                                                                                                                                  pg_locks l                                                                                                                                    JOIN                                                                                                                                                  pg_class c ON c.oid = l.relation                                                                                                              JOIN                                                                                                                                                  pg_stat_activity a ON a.pid = l.pid                                                                                                           WHERE                                                                                                                                                 NOT l.granted                                                                                                                                     AND c.relkind = 'r';