PostgreSQL

Link

https://www.postgresql.org/

Resources

• pgRouting

  pgRouting extends the PostGIS / PostgreSQL geospatial database to provide geospatial routing functionality.

• pg_arcgis

  Reach out to ArcGIS web service from PostgreSQL or Greenplum

• pg_osm

  Reach out to OpenStreetMap web service from PostgreSQL or Greenplum

• Installing PostgreSQL 12 on Raspbian Buster using Docker
• psycopg

  Psycopg is the most popular PostgreSQL adapter for the Python programming language. Its core is a complete implementation of the Python DB API 2.0 specifications. Several extensions allow access to many of the features offered by PostgreSQL.

• EDB - commercial support for PostgreSQL

  Software, services, and support for teams who need to do more and go faster with PostgreSQL.

• Lock monitoring, LWLocks and the log_lock_waits setting

FAQ

Which query is blocking which other?

SELECT blocked_locks.pid         AS blocked_pid,
       blocked_activity.usename  AS blocked_user,
       blocking_locks.pid        AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query    AS blocked_statement,
       blocking_activity.query   AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
         JOIN
     pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
         JOIN
     pg_catalog.pg_locks blocking_locks
     ON blocking_locks.locktype = blocked_locks.locktype
         AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
         AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
         AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
         AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
         AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
         AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
         AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
         AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
         AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
         AND blocking_locks.pid != blocked_locks.pid
         JOIN
     pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

Find largest schemata

SELECT pg_size_pretty(sum(pg_total_relation_size(relid))) as total_size, schemaname as table_schema from pg_catalog.pg_statio_user_tables GROUP BY schemaname ORDER BY sum(pg_total_relation_size(relid))  DESC LIMIT 15;

Find bloat

See Show database bloat - PostgreSQL wiki