PostgreSQL
Link
Excerpt
The World’s Most Advanced [Open Source](…/Open Source/) Relational Database
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;