r/PostgreSQL

I never thought of data governance as a sexy topic. My main focus has always been on performance, insights, cost reduction.

That is, until I joined a startup as the sole data engineer. Dealing with tons of PII/PHI, I realized just how much effort it was to write all these custom tools to handle everything: infinite GRANTs, trigger functions for versioning, cron jobs for retention - and it all needed so much attention and maintenance. Or I could go with an off-the-shelf product that's a complete black-box with a learning curve.

Always one to prefer spending 10x longer automating the task than just doing it, I built a CLI tool that lets users build their DB/governance specs in declarative yamls, and writes all the SQL code for you. And it's open source, fully transparent, as secure as I could conceive of making it, and hopefully super user-friendly too.

I've linked the first release in my repo. Anyone want to try it out?

In the interest of transparency: I did code this with assistance from Claude, but I've been in data engineering for almost 20 years and manually debugged every line. I also got it to build me a suite of over 300 tests that run through GitHub Actions automatically on each commit.

Hi, as I learn about and work with Postgres I will have to do some db migrations.

Most of my servers are managed Postgres but also some are on-prem on vms.

Anyway, when you are asked to migrate Postgres databases, what's your goto procedure?

Backup/restore?

Logical replication?

Cloud based migration services?

I'm a huge fan of graphs, they tend to simplify a lot of problems (permissions that inherit, content that relates, entities for RAG, etc.) and preserve optionality when the data modeling is uncertain. Most of the time you need a graph, you don't need a graph database. Your app already has a perfectly good SQL DB and you can get pretty far with recursive CTEs. After building this a few dozen times I decided to package it all up with a nice DX and open source it.

TypeGraph (open source, I'm the author) is a graph modeling + query layer that compiles to SQL. It is explicitly not a graph database — you keep your Postgres/SQLite, your transactions, your backups, and you inherit your DB's performance. Comes with some tradeoffs but also a lot of power, like being able to connect from relational to graph.

• Each algorithm (shortestPath, reachable, canReach, neighbors, degree) compiles to one recursive CTE with cycle detection and depth limits — identical semantics on SQLite and Postgres
• Postgres CTEs emit NOT MATERIALIZED hints; LIMIT is pushed past GROUP BY in safe aggregation cases
• Server-side prepared statements (named) cache plans — ~6× faster on multi-hop traversals in my benchmarks
• refreshStatistics() wraps ANALYZE (per-table on PG) for post-bulk-load plan stability
• withTransaction(externalTx) shares one transaction across TypeGraph and your existing Drizzle/relational writes — atomic across both models, no data syncing
• Multi-driver Postgres: node-postgres, postgres-js, Neon WS + HTTP; SQLite via better-sqlite3, libsql, and Cloudflare Durable Objects

Embraces TypeScript and related libraries: a single Zod schema per node/edge is the source of truth for runtime validation, storage, and type inference. Result types come from your select clause. Traversal autocomplete only shows valid target kinds.

It ships a basic ontology with the ontology as data so you can do things like admin.implies(editor) and every query expands automatically, no getEffectivePermissions() duplicated across services.

It does all the fancy AI stuff too (semantic search, fulltext, hybrid retrieval w/ RRF) but it's really just graphs done right on top of a DB you're probably already using

Honest feedback welcome, especially on the type ergonomics.

GitHub: https://github.com/nicia-ai/typegraph · Docs: https://typegraph.dev

Hi,

There's something I've been working on for a few months and wanted to share here:

pg_deltax, a fast time-series extension for PostgreSQL. Basically an Apache-licensed Timescale alternative.

I've started working on this project because I wanted a pure-OSS Postgres extensions for time-series. Then I got competitive on ClickBench and wanted to see how fast I can make it.

It’s very early days, but the benchmarks already look really good (see in the README).

It's still slower than analytics-focused DBs like clickhouse or duckdb, but actually not that far behind. Consider getting that kind of performance without ever having to take the data out of Postgres. This is the part that I'm most excited about.

Now in its 5th year, POSETTE: An Event for Postgres 2026 is a free & virtual developer event with 44 talks across 4 livestreams—organized by the Postgres team at Microsoft in partnership with AMD.

No travel budget required. You could just check out the PosetteConf.com schedule & speakers page (and then mark your calendars if it looks useful to you) but this ultimate guide is mean to give you a map to help you find which talks and which livestreams are right for you.

Let me know what you think.

Hi all,

Our systems are slowly starting to move to PostgreSQL, at least for some services. I'm currently a senior SQL Server DBA and I want to learn PostgreSQL from the DBA side.

Over the past few days I've been trying to find a course that walks through the database step by step, but I can't find anything good or consistent. I've tried Udemy and YouTube and it's all over the place. I can't find a structured course on this.

Does anyone have a recommendation? I'm willing to pay a good amount of money for a quality course, I'm not looking for something free. But it has to be DBA-focused, not developer-focused. I've already picked up most of the new syntax from W3Schools, and after years of T-SQL it's not too hard to adjust to.

Thanks.

Story behind a fix that just shipped in JobRunr 8.6.0 that other folks doing JDBC-side table validation might find useful.

A user reported that on their Postgres setup (10 schemas, ~8000 tables each, so roughly 80k tables total) just validating which JobRunr tables existed during application startup was taking over 40 minutes.

The offending code was straightforward:

ResultSet tables = conn.getMetaData().getTables(catalog, null, "%", null);

That "%" pattern pulls metadata for every table in every schema in the catalog. On a small Postgres database it's instant but on a database with tens of thousands of tables, it's a pg_class / pg_namespace scan can take a while...

The fix: filter by name pattern at the metadata-query level instead. We let DatabaseMetaData tell us how identifiers are stored (upper / lower / mixed case) and pass a narrowed pattern:

DatabaseMetaData md = conn.getMetaData();
String pattern = "%";
if (md.storesMixedCaseIdentifiers()) pattern = "%";
else if (md.storesUpperCaseIdentifiers()) pattern = "%JOBRUNR%";
else if (md.storesLowerCaseIdentifiers()) pattern = "%jobrunr%";
ResultSet tables = md.getTables(catalog, null, pattern, null);

On the same 80k-table Postgres setup, validation went from 40+ minutes to under 5 seconds.

The identifier-casing dance matters because Postgres folds unquoted identifiers to lowercase, Oracle / DB2 fold to uppercase, and mixed-case databases like MS SQL keep them as written. A blanket %JOBRUNR% pattern would miss tables on Postgres, and %jobrunr% would miss them on Oracle.

For anyone hitting similar slowness on getMetaData().getTables() in their own apps, this is the lever to pull.

PR: https://github.com/jobrunr/jobrunr/pull/1539
Original issue with the user's reproduction: https://github.com/jobrunr/jobrunr/issues/1538

(JobRunr is an open-source background job library for Java if you haven't heard of it. This post isn't really about JobRunr, just a Postgres / JDBC startup-perf pattern that's worth sharing.)

Hi,

Is there a tool to visualize a graph within a PostgreSQL database similarly how Neo4j desktop Bloom viewer allows you to do?

https://preview.redd.it/5o1q1v852f1h1.png?width=1000&format=png&auto=webp&s=b7af64493d4cff64c376e600fbf1c67a42884fec

Thanks

Hi r/postgresql!

We just open sourced ParadeDB Benchmarker, a multi-backend benchmarking framework built on top of the excellent Grafana k6 (blog post).

One of the goals was avoiding a shared query abstraction layer. PostgreSQL queries stay PostgreSQL queries, with their own driver and native SQL.

Supports PostgreSQL, Elasticsearch, OpenSearch, ClickHouse, MongoDB, and ParadeDB with:

• mixed read/write workloads
• support for docker-compose profiles per backend
• dataset loader
• config and setup capture
• live metrics + exported reports

One of the ah-ha moments I had building this was using the pgx Go driver in anger for the first time, I'm a Rust guy, but I'm seriously impressed with pgx and what it can do.

Any comments welcome, we will be using this to benchmark ParadeDB, but you can write your own datasets and workloads which have nothing to do with full-text search.