Postgres is Enough

postgresql_is_enough.md

PostgreSQL is Enough

• Simplify: move code into database functions
• Just Use Postgres for Everything
• PostgreSQL is the worlds’ best database
• Postgres is eating the database world
• Hacker News discussion

Background and Cron Jobs

• https://github.com/citusdata/pg_cron
• https://github.com/cybertec-postgresql/pg_timetable

Message Queues

• https://adriano.fyi/posts/2023-09-24-choose-postgres-queue-technology
• https://github.com/tembo-io/pgmq

GIS/Mapping

• https://github.com/postgis/postgis

Audit Logs

• https://github.com/arkhipov/temporal_tables
• https://github.com/supabase/supa_audit
• https://github.com/pgMemento/pgMemento
• https://github.com/pgaudit/pgaudit

Access Control & Authorization

• https://github.com/arkhipov/acl
• https://www.postgresql.org/docs/current/pgcrypto.html
• https://github.com/michelp/pgjwt
• https://supabase.com/docs/guides/database/vault

Search

Full Text

• Postgres Full Text Search (bunch of helpful links)
• https://github.com/paradedb/paradedb

Vector

• https://github.com/neondatabase/pg_embedding
• https://github.com/pgvector/pgvector

Time Series

• https://github.com/timescale/timescaledb

Column Oriented

• https://github.com/hydradatabase/hydra
• https://github.com/citusdata/cstore_fdw

NoSQL

• https://www.postgresql.org/docs/current/datatype-json.html
• https://github.com/robconery/dox
• https://www.ferretdb.com

Graph Data

• https://age.apache.org

Foreign Data

• https://github.com/supabase/wrappers

Alternative Query Langauges

• https://github.com/kaspermarstal/plprql
• https://supabase.github.io/pg_graphql

HTTP

• https://github.com/pramsey/pgsql-http
• https://github.com/supabase/pg_net

APIs

• https://github.com/PostgREST/postgrest
• https://github.com/hasura/graphql-engine
• https://postgraphile.org
• https://supabase.github.io/pg_graphql

Events, Replication, CDC,

• https://www.postgresql.org/docs/current/sql-notify.html
• https://github.com/cpursley/walex (Disclosure: I maintain this and think it's pretty awesome)
• https://github.com/PeerDB-io/peerdb
• https://github.com/debezium/debezium
• https://github.com/2ndQuadrant/pglogical

Caching

• https://martinheinz.dev/blog/105
• https://github.com/readysettech/readyset

Unit Tests

• https://github.com/theory/pgtap
• https://github.com/peterldowns/pgtestdb

HTML and Applications

• SQLpage
• Omnigres
• pg_render
• plmustache

Graphics / Visual

• pgSVG

Migrations

• https://github.com/purcell/postgresql-migrations
• https://www.bytebase.com/
• https://github.com/xataio/pgroll
• https://github.com/stripe/pg-schema-diff

Performance Tuning

• Index Advisor
• Dexter
• HypoPG
• pg_hint_plan
• PGHero

Scalling

• How Cloudflare Achieved 55 Million Requests per Second with Just 15 PostgreSQL Clusters!
• https://github.com/supabase/supavisor
• https://github.com/pg-sharding/spqr

Dashboards / UIs

• Baserow
• NocoDB
• AppSmith

Data Visualization

• Evidence
• Metabase

Package Management

• https://github.com/pgxman/pgxman
• https://github.com/supabase/dbdev

Language Servers

• https://github.com/supabase/postgres_lsp

Miscellaneous

• Very comprehensive list of Postgres tooling

What's missing? Please share in the comments.

I found https://github.com/purcell/postgresql-migrations useful for code-based database migrations.

In case you want to use a self-contained postgres server in a python project, here's a postrgres+pgvector wheel that you can pip install
https://github.com/orm011/pgserver/
The goal is to make it easier to get started using postgres even if you have no admin privileges to run the server system-wide as it is normally run.

In the HTML section, you can add SQLPage (https://www.postgresql.org/about/news/announcing-sqlpage-build-dynamic-web-applications-in-sql-2672/) ! It lets you quickly build user interfaces on top of a postgres database.

https://postgraphile.org/

this belongs in the api section, imo

very cool, thank you for putting this together!

Any tips on suitability or replacement for documentdb and mongo?

For CDC, PeerDB is a good option: https://github.com/PeerDB-io/peerdb

@meekrosoft I don't know documentdb so well but jsonb is a very capable replacement for unstructured data. I use it all the time.

@ralyodio I don't know surrealdb - can you sum up its capabilities?

For rendering SVG look at pgSVG.

HTAP/OLAP: https://www.youtube.com/watch?v=W_3e07nGFxY

Within "Dashboards / UIs", it would be great to mention Mathesar — a web based interface specifically tailored for PostgreSQL.

Advanced Scheduler (cron) pg_timetable

pgroll: https://github.com/xataio/pgroll

How about a multimaster cluster where i can upgrade postgres version in rolling node by node upgrades and not resort to ancient methods? How about having a cluster of postgresql machines where i can add/remove nodes, add nodes with new versions and upgrade the whole cluster without downtime? How come mongodb and elastic can do that. Cockroachdb and Yugabyte can do that. But with the classic postgresql I'm back in stone age. Yes some 3rd party clustering methods exist there but they are all s..t on a stick kind of solutions, not proper ones. Not the ones where i could say "hey shard that table into 16 shards for me and have parity over 3 nodes, let me command the shards off a node that i want to maintain".

Don't get me wrong, i have used postgresql for 18 years by now. I love a big bunch of what i can do with it. But managing a high-avialability setup just makes devops cry.

Something missing that at least right now i feel should NOT be in Postgres is secrets management

@jherazob what are you thoughts on this approach?: Supabase Vault

Cockroachdb and Yugabyte can do that

@kulminaator I think you answered your own question; these are pg wire compatible. There's also Citus.

@jherazob what are you thoughts on this approach?: Supabase Vault

I'm only vaguely aware of the existence of Supabase and have never really evaluated it before, so will have to give it a good look before forming an opinion, gut feeling still tells me that secrets on the DB is probably not good but i have to analyze their approach to give it a fair assessment

@cpursley something that can be added to Audit Logs: https://github.com/BemiHQ/bemi

Great! That this idea is getting traction :-)

This is missing data warehousing, reporting and analytics.

@kenfar Any suggestions for those?

@cpursley @kenfar its deprecated but technically tobs can be salveged for this purpose: https://github.com/timescale/tobs

@cpursley - no product is needed, just the right features:

• partitioning
• query parallelism
• dimensional model (rather than say one-big-table)
• pre-computed aggregate tables for common queries
• appropriate hardware (sufficient memory, cpu, maybe very fast IO)

This is missing an MPP configuration (data distributed across multiple servers working together on queries), and columnar structures. But, those aren't absolutely essential.

My Postgres collection, perhaps you'd like to use some of the items as well. Here is my list of Postgres blogs worth visiting regularly.

These are great @mjf, thanks! I added a link under miscellaneous.

I've recently put together the Rust crate postgresql-embedded that will allow you to embed PostgreSQL into an executable for use in environments where downloading the installation may not be an option.

@cpursley @kenfar checkout https://github.com/hydradatabase/hydra and https://github.com/paradedb/paradedb for analytics/column stores.

I have been using https://github.com/sqitchers/sqitch for a while on production to handle migrations with Postgres it works perfectly.