Useful PostgresQL SQL Queries

Database maintenance related queries

Find Total Database Size

SELECT pg_size_pretty(pg_database_size('pp'));

Find Individual Table and Index Sizes

SELECT
   relname  as table_name,
   pg_size_pretty(pg_total_relation_size(relid)) As "Total Size",
   pg_size_pretty(pg_indexes_size(relid)) as "Index Size",
   pg_size_pretty(pg_relation_size(relid)) as "Actual Size"
   FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Check Current Active Database Queries

SELECT pid, now()-pg_stat_activity.query_start AS duration, query, state FROM pg_stat_activity WHERE (now()-pg_stat_activity.query_start) > interval '5 seconds';

Terminate/kill a Postgres process by its id

select pg_terminate_backend(18837);

where 18837 is an example id.

Domain Specific Queries

Find a book by ESTC

select id from pp_book where estc  = 'R42222' and is_eebo_book = false;

where R42222 is an example ESTC identifier.

Find all characters on a particular page of a given book UUID

SELECT c.id, c.character_class_id, p.sequence as page, p.side as side, l.sequence as line, c.sequence as character FROM pp_character c INNER JOIN pp_line l ON c.line_id = l.id INNER JOIN pp_page p ON l.page_id = p.id INNER JOIN pp_characterrun cr ON cr.id = c.created_by_run_id INNER JOIN pp_book b ON b.is_eebo_book = false AND b.id = cr.book_id WHERE b.id = 'ceb2aa59-aa2c-461d-a411-76ec72b746d1' AND p.sequence = 283;

where 283 is an example page number and ceb2aa59-aa2c-461d-a411-76ec72b746d1 is an example of book UUID.

Find all characters created by a character_run UUID and belonging to a particular character_class

select ppp.sequence, ppc.id from pp_character ppc INNER JOIN pp_line ppl ON ppl.id = ppc.line_id INNER JOIN pp_page ppp ON ppp.id = ppl.page_id WHERE ppc.created_by_run_id = 'fd706856-5b79-41ea-895c-e5f4776cb02f' and ppc.character_class_id = 'R_uc' ORDER BY ppp.sequence;

where fd706856-5b79-41ea-895c-e5f4776cb02f is an example character_run UUID and R_uc is an example character_class i.e. upper-case R.

Find all characters, along with page number and line numbers for given book UUID - slow query

SELECT c.*, c.id, c.character_class_id, p.sequence as page, p.side as side, l.sequence as line, c.sequence as character FROM pp_character c INNER JOIN pp_line l ON c.line_id = l.id INNER JOIN pp_page p ON l.page_id = p.id INNER JOIN pp_characterrun cr ON cr.id = c.created_by_run_id INNER JOIN pp_book b ON b.is_eebo_book = false AND b.id = cr.book_id WHERE b.id = 'ceb2aa59-aa2c-461d-a411-76ec72b746d1';