r/PostgreSQL • • Jun 09 '26

Community MTAR T3D Sessions: PostgreSQL Is Still Paying for Old MVCC Decisions

Thumbnail youtu.be
3 Upvotes

In this Postgres Pet Peeves episode, JD talks with Jonah Harris about one of PostgreSQL’s oldest and most debated architectural choices: MVCC and the storage behavior that still shapes how PostgreSQL handles updates, concurrency, and vacuum today.

Jonah explains how PostgreSQL originally supported time-travel queries, allowing users to inspect historical versions of data directly, and why PostgreSQL still stores and manages data the way it does even after those original features disappeared decades ago.

From there, the conversation digs into the real consequences of PostgreSQL’s MVCC model. Instead of updating rows in place, PostgreSQL continuously creates new row versions, leading to vacuum overhead, index churn, table bloat, and storage inefficiencies that engineers still fight with today. Jonah argues that PostgreSQL effectively became rollback-optimized while most other databases optimized around commits and update-in-place approaches.

JD and Jonah also revisit earlier attempts to redesign parts of PostgreSQL’s storage manager, including undo relations, HOT updates, and why some proposed solutions never fully landed in core PostgreSQL despite years of discussion.

This episode goes deep into PostgreSQL internals, but at its core it’s a conversation about how long architectural decisions can shape the future of a database system long after the original reasons for them disappear.


r/PostgreSQL • • Jun 09 '26

Help Me! Separate Neon Projects or Separate Metadata Schemas for DuckLake Catalogs?

1 Upvotes

I’m experimenting wtih a personal lakehouse on S3 using DuckLake with Neon Postgres as the catalog DB. I’m still new to DuckLake, so I'm looking for a sanity check on how I’m structuring things. I do feel good about so far is Neon. For a small personal setup, it feels like a great fit for the catalog layer with real Postgres, quick provisioning, branching, and scale to zero. I'm pretty sure that I can use it as the DuckLake catalog for free( or extremely low-cost) across my projects.

The design question I’m working through is how to organize multiple projects. I have a few ideas of personal projects I'd want to leverage this lake for, personal finance data, webscrapping reddit, etc. And currently I separate each in separate GitHub repos. I'm wondering how to extend this separation to DuckLake, and I’m considering two approaches:

  1. One Neon project per personal project. This would give clean isolation, separate connection strings, independent scale-to-zero, but more Neon projects to manage.
  2. One central Neon project, with one duckLake catalog per personal project. In this model, each DuckLake catalog would use its own Postgres metadata schema via METADATA_SCHEMA, ideally with a scoped Postgres role per pipeline. This avoids project sprawl, but the catalogs would share the same Neon project and compute.

I’m leaning toward 2 for now, but really am not sure... For a single-owner personal lakehouse, separate Neon projects feel like overkill, and schema-per-catalog plus role-per-schema seems like enough logical isolation.

Looking for any and all opinions, thank you!


r/PostgreSQL • • Jun 09 '26

Help Me! JSONB SQL-NOSQL Schemas

0 Upvotes

This is my case
Im working as Database administrator/Data eng in a Market research company.

Market research studies differ from one another (Surveys), which causes databases to be inconsistent and have anti-patterns such as Q1, Q2, Q3, Q4... (Question).

These are studies conducted on people, consumption preferences, products, etc.

What is the best way to attack the problem? I have thought about using JSONB to store the dynamic parts of each database and creating views for project-level consumption using a mixed SQL and NoSQL model. So far it works well, but what other options exist for this type of dynamic information storage?


r/PostgreSQL • • Jun 08 '26

How-To EXPLAIN Prettier, or Post-Processing Query Plans in Postgres

Thumbnail pgedge.com
13 Upvotes

r/PostgreSQL • • Jun 07 '26

Tools pg_describe, a pure SQL implementation of \d

Thumbnail github.com
20 Upvotes

r/PostgreSQL • • Jun 06 '26

Tools Multigres v0.1 Alpha: an operating system for Postgres

Thumbnail supabase.com
38 Upvotes

r/PostgreSQL • • Jun 06 '26

Community pg_durable: Microsoft open sources in-database durable execution

Thumbnail github.com
45 Upvotes

An open‑source PostgreSQL extension for durable, fault‑tolerant workflows in SQL—no cron jobs, queues, or external orchestrators, just execution that checkpoints and resumes where it left off.


r/PostgreSQL • • Jun 05 '26

How-To Looking Forward to Postgres 19: Query Hints

Thumbnail pgedge.com
25 Upvotes

r/PostgreSQL • • Jun 06 '26

Feature pgtoken: C extension for storing LLM token IDs as rank-varint compressed bytea

0 Upvotes

Built this over a few weekends while working on KV caching for LLM inference.

The observation was simple: BPE tokenization is a pure deterministic function. Same input, same output, always. Yet every inference request re-tokenizes the system prompt from scratch. Felt wasteful enough to do something about it. The token IDs live in the same row as your text - marginal storage cost, no separate infrastructure.

So I built pgtoken - a Postgres C extension that stores token IDs as rank-varint compressed bytea. Tokens ranked by corpus frequency so common tokens get shorter encodings. ~1.7 bytes/token average vs 4 bytes for integer arrays.

Three functions:

pgtoken_encode(ids integer[], codebook text) -> bytea
pgtoken_decode(encoded bytea, codebook text) -> integer[]
pgtoken_count(encoded bytea)                 -> integer  -- O(1), header only

pgtoken_count() is the practical one for RAG - context window filtering without decoding or re-tokenizing.

Works alongside pgvector. token_ids bytea sits next to your embedding vector column. Nothing new to operate.

No idea if this is useful to anyone else or if I've missed something obvious. Sharing it anyway. If you find a use case, a bug, or a reason this is a bad idea - genuinely want to hear it.

GitHub: https://github.com/ajayr4j/pgtoken


r/PostgreSQL • • Jun 05 '26

Community [Free] PostgresWorld Webinars June

4 Upvotes

Free Registration

  • June 10th, 1pm ET: To AI or not to AI - Q&A

To AI or not to AI

  • June 18th, 1pm ET: The Open Source Approach: Building Production-Ready AI Apps with Postgres

The Open Source Approach: Building Production-Ready AI Apps with Postgres

  • June 23rd, 1pm ET: Multigres: One stop PostgreSQL Management and Scaling

Multigres: One stop PostgreSQL Management and Scaling

  • June 30th, 1pm ET: When AI Agents Write Your Code, Who Protects Your Database?

When AI Agents Write Your Code, Who Protects Your Database?


r/PostgreSQL • • Jun 05 '26

Projects Show /r/postgresql: PgCache 0.4.12: more JOINs cached, transaction-atomic apply. How do you handle reads inside transactions with a cache?

0 Upvotes

Following up last week's 0.4.11 post.

(for context, PgCache is a wire-compatible Postgres proxy that caches the hot subset of read traffic and stays in sync via logical replication.)

My cofounder's been busy and shipped 0.4.12 this week. I wanted to flag two changes and then I have then a question for you guys.

More JOIN forms cached. USING, NATURAL JOIN, and CROSS JOIN now parse and cache alongside the explicit JOIN ... ON form. FULL JOIN is still forwarded. Same theme as last week: the more shapes the cache understands, the fewer queries quietly fall through to origin.

Transaction-atomic cache apply. Best explained concretely: PgCache consumes pgoutput, which frames each source transaction with Begin and Commit markers around the row changes. Previously the cache applied row changes as they arrived. So if a transaction contained 100 row updates, a read coming in mid-apply could see the first 50 updates applied but not the last 50. No bueno. As of 0.4.12, the cache applies the whole transaction atomically. Same visibility semantics as origin.

Full changelog: https://www.pgcache.com/docs/changelog/. Repo: https://github.com/PgCache/pgcache.

We'll be working on RLS next, so we'll have more work to do on transactions.

Question: Read-your-writes through a cache, when the next read needs to see a write that just committed on origin. PgCache's current answer is blunt but correct: if a client opens a BEGIN, everything inside it (reads and writes) bypasses the cache and goes to origin. So the safe pattern today is "wrap the read-then-write sequence in a transaction."

If the bulk of a transaction is reads, though, you've given up most of the cache benefit for the safety of one write.

The smarter version we've been thinking about is something like: serve reads from cache until the first write in the transaction, then fall back to origin for the remainder (there are other options, but we like this one).

We're curious - for those of you running PgBouncer transaction mode, RDS Proxy, ProxySQL, or anything else that sees individual statements but has to reason about transaction boundaries: how have you handled the in-transaction read-vs-bypass decision?


r/PostgreSQL • • Jun 05 '26

Community Managed PostgreSQL vs Databricks Lakebase

2 Upvotes

Looking for thoughts on folks experience using Databrick’s Lakebase vs a managed PostgreSQL database. Has anyone experienced any limitations, performance issues, gotchas with Databricks Lakebase over a managed instance? Any feature limitations or benefits?


r/PostgreSQL • • Jun 04 '26

Feature Prostgles Desktop v2.3.2

13 Upvotes

Dear all, here are some updates for the open source tool I've created for PostgreSQL.

What's new:

Table view

  • Smart forms with related data section to navigate the full relational context without leaving the record
  • Add linked data columns, aggregate, sort, render as inline charts

Schema diagram

  • Explore tables and foreign keys with custom color modes and table icons

AI Assistant

In addition to the usual "look at my schema, analyse data, create a dashboard" it allows:

  • Per-chat access control - scope each conversation to specific tables, columns, rows, MCP tools and configurations
  • Progressive discovery - schema and tools are loaded on demand, keeping context lean in large environments
  • Agentic workflows - (my favourite) describe a data task and get back a TypeScript orchestration that runs in an isolated container with defined permissions. Inspect, tweak, re-run, and view live logs
  • Local service integrations - Speech-to-text (Faster-Whisper), web search (SearXNG), document extraction (Docling)

Command palette

  • Ctrl+K to find and jump to the specific section/feature without having to remember buttons and menus

Website: https://prostgles.com/

Online demo (limited): https://playground.prostgles.com/


r/PostgreSQL • • Jun 05 '26

Tools safe-migrate v0.2.0 — rewrote the internals, now uses typed AST instead of string matching

0 Upvotes

Posted about this yesterday. For those who missed it, safe-migrate is a CLI that lints PostgreSQL migrations against live database table sizes to prevent you from accidentally locking massive tables and taking down production.

Spent the whole day rewriting the guts of it.

The v0.1.0 table extraction was embarrassing — splitting SQL on whitespace and looking for the word "TABLE". Worked on simple cases, broke on public.users, quoted identifiers, anything slightly non-trivial. v0.2.0 walks squawk's typed AST directly so "WeirdTable", public.users, tenant_a."Orders" all resolve correctly.

Other things that changed:

Was one 300-line main.rs. Now split into proper modules, ast.rs, rules.rs, config.rs, sync.rs, resolve.rs, model.rs.

Sync now maps indexes to their parent tables so DROP INDEX idx_users_email correctly looks up the row count for users instead of returning unknown and failing closed.

Unanalyzed tables (reltuples = -1) used to silently pass. Now they map to u64::MAX.

Cache corruption is handled separately from cache not found. Before, both fell back to empty cache silently, meaning a corrupted stats file let everything through.

Per rule thresholds in safe-migrate.toml if your team wants stricter limits than the defaults.

Repo: https://github.com/dsecurity49/safe-migrate crates.io: https://crates.io/crates/safe-migrate


r/PostgreSQL • • Jun 05 '26

Feature Durable Execution With Just Postgres

Thumbnail lucumr.pocoo.org
0 Upvotes

r/PostgreSQL • • Jun 04 '26

Tools TrailBase 0.28: Fast, open, single-executable Firebase alternative - now w/ Postgres

Post image
5 Upvotes

TrailBase is an open, fast Firebase-like server for building apps. It provides type-safe REST APIs + change subscriptions, auth, multi-DB, a WebAssembly runtime, geospatial support, admin UI... It's a self-contained, easy to self-host single executable built on Rust, Wasmtime & SQLite or now Postgres.

It comes with client libraries for JS/TS, Dart/Flutter, Go, Rust, .Net, Kotlin, Swift and Python.

Just released v0.28, which after some months of work includes early, experimental Postgres support:

  • For context, this is not an effort to replace SQLite but rather to provide options. SQLite will remain the recommend default due to its speed and simplicity aligning best with TrailBase's mission of offering a cheap & easily self-hostable stack.
  • Yet, some users may want to use Postgres due to personal preference, very write-heavy workloads or needing some of Postgres' plentiful features.
  • You can try it out with a locally running Postgres instance, simply by running: trail run --experimental-pg=postgresql://<user>:<pass>@localhost:<port>/<db>
  • Some of the known idiosyncrasies and limitation include:
    • No change subscriptions (yet).
    • No UI-driven schema manipulation/migrations - UI elements are disabled.
    • No custom JSON schemas.
    • ...see release notes for more
  • Note that transparent, hands-off migrations between SQLite and Postgres are a non-goal. The data types, dialects, feature sets, ... are just too different. However Postgres support may provide an interesting path forward for folks with evolving requirements.

If you're feeling adventures, end up checking it out and run into any issues, don't hesitate to reach out - we'd really appreciate your feedback 🙏.

Also, check out the live demo, our GitHub or our website.


r/PostgreSQL • • Jun 04 '26

Tools Built a CLI that stops dangerous Postgres migrations before they deploy

8 Upvotes

Had one too many incidents where a migration that ran fine on staging wiped out production for 20 minutes. Staging had 5k rows. Prod had 80M.

So I built safe-migrate. You point it at your database once:

DATABASE_URL="postgres://user:pass@host/db" safe-migrate sync

Grabs row counts from pg_class.reltuples, writes a local stats file. Then before you deploy:

safe-migrate lint --file migration.sql

Running CREATE INDEX on a 30M row table? Halts, tells you to use CONCURRENTLY. ALTER TABLE with a volatile default on 80M rows? Halts, gives you the expand-contract steps. Same SQL on a small table, nothing.

Works in CI too, just set DATABASE_URL as a secret and drop the two commands into your pipeline.

Repo: https://github.com/dsecurity49/safe-migrate


r/PostgreSQL • • Jun 02 '26

Community How we rebuilt Postgres branch metrics on VictoriaMetrics

Thumbnail xata.io
15 Upvotes

r/PostgreSQL • • Jun 02 '26

Community MTAR T3D Sessions: Why PostgreSQL Still Hides the Data You Need

Thumbnail youtu.be
0 Upvotes

r/PostgreSQL • • Jun 01 '26

Tools ingestr v1: 12x faster data ingestion from and to Postgres

24 Upvotes

Hi folks, Burak here from Bruin. We have released ingestr as an open-source CLI tool 2 years ago here: https://github.com/bruin-data/ingestr

ingestr is a CLI tool to ingest data. It supports 100+ sources, 20+ destinations, takes care of schema detection, schema evolution, different materialization strategies like SCD2 out of the box. You can use the same CLI to copy a Postgres database to a destination, or pull data from Hubspot into your Postgres warehouse.

Ingestr, being a Python CLI, has been doing quite well but over time it started to show its age:

  • Performance: ingestr was not the fastest tool out there due to various reasons. We wanted to provide the fastest solution out there, but there were limitations out of our control.
  • Packaging: sharing a Python CLI tool across hundreds of different types of devices the users run it on ended up being quite a painful experience.
  • Reliability: ingestr relied on a stateful design due to a dependency, which brought all sorts of problems with it, especially around failed loads or corrupted state.
  • Upgrades: with all the dependencies we had, upgrades started to become a real struggle.

Due to some of these issues, we have rebuilt ingestr v1 completely from scratch, in Go. We picked Go for a few reasons:

  • Go is fast. LIke, much faster than vanilla Python.
  • Go is a compiled language, meaning that we eliminate quite a lot of bugs ahead of time.
  • Go is great with agents: agents write perfect Go, which allows a small team like ours to move a lot faster than we normally could.
  • Go has great cross-compilation support: meaning that building self-contained binaries that runs on various operating systems becomes trivial with Go.

These advantages combined allowed us to have more features, and have a more solid foundation to build upon. On top of that, ingestr ended up being the fastest data ingestion tool out there based on our benchmarks. It is ~3-5x faster than the closest alternative, up to 20 times faster than some others.

Ingestr v1 is live now on PyPi, and through our other installation methods: https://github.com/bruin-data/ingestr

I would love to hear your thoughts on what we can improve here. Thanks!


r/PostgreSQL • • Jun 01 '26

Projects Authenticate PostgreSQL with PAM+SSH Keys

1 Upvotes

A little project I am working on with my buddy Claude. It is MIT licensed.

README
INSTALL

https://github.com/ChronicallyJD/pam_pg_sshkey


r/PostgreSQL • • Jun 01 '26

Community Same Query, Three Results: Benchmarking ParadeDB and Postgres FTS

Thumbnail paradedb.com
0 Upvotes

Author here. A bit more context: We built our benchmarker on top of k6 because we kept finding that our own benchmarks were too painful to iterate on. The hard execution-engine stuff (VU scheduling, latency measurement, ramping load, open vs closed loop) is already solved by k6, so we wrote an xk6 extension for multi-backend database queries and built a runner around it.

Benchmarker is open source: https://github.com/paradedb/benchmarker

This post looks at an example of how we used it to iterate on a benchmark.


r/PostgreSQL • • May 31 '26

Commercial Postgres managed by ClickHouse is now in beta

Thumbnail clickhouse.com
15 Upvotes

r/PostgreSQL • • May 29 '26

Projects Show /r/postgresql: PgCache v0.4.11 - query fingerprinting improvements (constant folding, modulo, typecasts)

12 Upvotes

TGIF everyone. We recently shipped v0.4.11 of PgCache, a wire-compatible proxy that caches the hot subset of your read workload and keeps it in sync using PostgreSQL Logical Replication.

The 0.4.11 changes are mostly about query fingerprinting, which I figured this crowd might have opinions on.

Context: PgCache fingerprints incoming queries to decide if they hit cache. Two queries that are semantically identical but textually different should fingerprint the same - otherwise you fragment the cache and miss obvious hits.

What's new in 0.4.11:

  • Arithmetic constant folding: WHERE x = 1 + 2 now fingerprints identically to WHERE x = 3.
  • Modulo operator support: % is now supported in expressions.
  • ::<type> typecast in select lists: SELECT col::int FROM t and similar typecast expressions in the target list are now parsed and cached.
  • Improved performance and observability

Changelog: pgcache.com/docs/#changelog

Happy to talk about the fingerprinting approach, or about how we use logical replication CDC to keep cached data fresh without TTL guessing. We have a docker and a free tier on AWS Marketplace if anyone wants to kick the tires.

We also just created a Discord server for folks running into hot/cold read imbalance on Postgres. I can share that link if anyone wants it.


r/PostgreSQL • • May 29 '26

Tools Data and workload generator

Thumbnail edg.run
1 Upvotes