---
title: PostgreSQL
kind: note
description: open-sorce relational database
words: 137
readingMinutes: 1
created: '2024-08-18T15:02:50+02:00'
updated: '2026-06-29T20:34:03+02:00'
website: https://www.postgresql.org/
---
## Link

<https://www.postgresql.org/>

# Resources
- [pgRouting](https://pgrouting.org/)
  > pgRouting extends the [PostGIS](https://www.postgis.net/) / [PostgreSQL](https://www.postgresql.org/) geospatial database to provide geospatial routing functionality.
- [pg_arcgis](https://github.com/andreasscherbaum/pg_arcgis)
  > Reach out to ArcGIS web service from PostgreSQL or Greenplum
- [pg_osm](https://github.com/andreasscherbaum/pg_osm)
  > Reach out to OpenStreetMap web service from PostgreSQL or Greenplum
- [Installing PostgreSQL 12 on Raspbian Buster using Docker](https://medium.com/@mattvonrohr/installing-postgresql-12-on-raspbian-buster-using-docker-51032c0a0cdf)
- [psycopg](https://www.psycopg.org/)
  > **Psycopg** is the most popular [PostgreSQL](/notes/postgresql) adapter for the <span class="dead-link">Python programming language</span>. Its core is a complete implementation of the [Python DB API 2.0](https://www.python.org/dev/peps/pep-0249/) specifications. Several extensions allow access to many of the features offered by PostgreSQL.
- [EDB](https://www.enterprisedb.com/) - 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](https://pganalyze.com/blog/5mins-postgres-lock-monitoring-lwlock-log-lock-waits)
# FAQ
## Which query is blocking which other?
```sql
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
```sql
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](https://wiki.postgresql.org/wiki/Show_database_bloat)