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