Skip to content

PostgreSQL

References

Tools
  • pgx_scripts - A collection of useful little scripts for database analysis and administration
    • bloat - queries to estimate bloat in tables and indexes
  • pg_repack is a PostgreSQL extension which lets you remove bloat from tables and indexes, and optionally restore the physical order of clustered indexes.

1. Export to CSV with \copy

References
myquery.sql
\COPY (SELECT abc FROM tbl_name WHERE <query>) TO 'sample-result.csv' CSV header;

➜ psql -af myquery.sql

2. Terminating long running connections

If you need to terminate a long running query or a connection e.g. because it is in IDLE in transaction admins can run:

select user_management.terminate_backend(<pid>);

Or to terminate all application connections:

select user_management.terminate_backend(pid)
  from pg_stat_activity
 where usename = 'your_application_user';

or use pg_terminate_backend ( pid integer ) → boolean sends SIGTERM signal to backend processes identified by process ID 1

3. Get all ID columns in PSQL

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
  AND column_name LIKE '%id';
Example 
      column_name      |     data_type
-----------------------+-------------------
id                    | integer
...
some_id               | uuid
id                    | text
notification_id       | uuid
(21 rows)

4. List and order tables by size

see SO: PostgreSQL: Get table size

This shows you the size of all tables in the schema pg_catalog:

SELECT table_name,
       PG_SIZE_PRETTY(PG_TOTAL_RELATION_SIZE(QUOTE_IDENT(table_name))),
       PG_TOTAL_RELATION_SIZE(QUOTE_IDENT(table_name))
FROM information_schema.tables
WHERE table_schema = 'pg_catalog'
ORDER BY 3 DESC;
Example
table_name pg_size_pretty pg_total_relation_size
pg_proc 1320 kB 1351680
pg_attribute 856 kB 876544
pg_rewrite 776 kB 794624

This shows you the size of all tables in all schemas:

SELECT table_schema,
       table_name,
       PG_RELATION_SIZE('"' || table_schema || '"."' || table_name || '"')
FROM information_schema.tables
ORDER BY 3 DESC;
Example
table_schema table_name pg_relation_size
pg_catalog pg_proc 884736
public content_production_briefings 688128
pg_catalog pg_attribute 573440
pg_catalog pg_collation 114688
information_schema sql_features 65536
pg_catalog pg_amop 57344

  1. Terminates the session whose backend process has the specified process ID. This is also allowed if the calling role is a member of the role whose backend is being terminated or the calling role has been granted pg_signal_backend, however only superusers can terminate superuser backends (see Server Signaling Functions) ↩