Select for update

May be useful when we need to implement concurrent updates.

select *
from table_name
where condition
for update;

Select for update skip locked

May be useful when we need to implement concurrent updates but we don't want to wait.

select *
from table_name
where condition
for update
skip locked;

Standby mode

• Hot standby: a replica accepts requests from clients.
• Warm standby: a replica does NOT accept requests from clients.

Show which queries use the most resources

select total_exec_time,
	     mean_exec_time as avg_ms,
	     calls,
	     query
from pg_stat_statements
order by mean_exec_time desc
limit 10;

Show table sizes

select relname as relation,
       pg_size_pretty (
         pg_total_relation_size (c .oid)
       ) as total_size
from pg_class c
left join pg_namespace n ON (n.oid = c.relnamespace)
where nspname not in (
        'pg_catalog',
        'information_schema'
      )
  and c.relkind <> 'i'
  and nspname !~ '^pg_toast'
  order by pg_total_relation_size (c.oid) desc