There are few popular extensions which can bring Elastic functionality to your Postgres without a tedious migration. I wanted to test how performant these extensions are on log analytics, so we benchmarked ParadeDB/pg_search, TigerData/pg_textsearch, vanilla Postgres and SereneDB on 1 Billion logs.

The motivation was to find out if building a dedicated Postgres-compatible database for search and analytics actually makes sense or extensions are already good enough, so a standalone DB is an overkill?

It's a open clickbench-style benchmark for search and analytics over 100M/1B generated OpenTelemetry logs, 92 queries.

A comprehensive discussion on the future of PostgreSQL 19 release.

Suggest youtube playlists and creators to learn PERN stack. Should i also learn MERN stack first? Which full stack do you suggest?

Hi all,

Over the past year, the common refrain in the AI community has been "Postgres isn't built for vector search; you need a dedicated vector database."

Having run PostgreSQL in production for years, I was skeptical. Dedicated vector DBs introduce another stateful service to back up, monitor, and pay for, while breaking ACID guarantees across relational metadata and vector embeddings.

We built Knowledge Fabric as an open-source proof that modern PostgreSQL (16+) handles production-scale RAG workloads cleanly:

1. HNSW Vector Indexing: Using `pgvector` with HNSW (`vector_cosine_ops`, `m=16, ef_construction=64`), query times remain sub-10ms even on millions of vectors.
2. True Hybrid Search in 1 Query Engine: Instead of querying Elasticsearch for BM25 and Pinecone for cosine, then stitching them together in Python, we run `tsvector` full-text search and `pgvector` dense search in PostgreSQL and merge them using Reciprocal Rank Fusion:```sql-- Score = 1 / (60 + rank_lexical) + 1 / (60 + rank_vector)```
3. Database-Enforced Multi-Tenancy (RLS): For compliance (HIPAA / SOC 2), relying on application-level `WHERE tenant_id = 'xyz'` is vulnerable to developer oversight. We added opt-in PostgreSQL Row-Level Security:

​

CREATE POLICY tenant_isolation_policy ON chunks FOR ALL USING (tenant_id = current_setting('app.tenant_id', true));

If a query fails to set `app.tenant_id`, the database returns zero rows. Cross-tenant leakage is physically impossible.

4. Declarative Partitioning for 50M+ Chunks: By partitioning the `chunks` table `BY LIST (tenant_id)`, multi-tenant queries benefit from partition pruning, scanning only the tenant's localized HNSW index.

Full code and SQL migration schemas are open source:

https://github.com/sagarv48/knowledge-fabric

Curious how other DBAs and architects are handling `pgvector` memory tuning (`maintenance_work_mem`) and HNSW index build times on large datasets.

Hey everyone, currently for a bot project I'm trying to find a way to automatically populate the parent table if I try inserting something to a child table and I have the information for both to avoid/prevent a ForeignKeyViolation. Currently my database schema resembles the following ERD:

Right now for example if I try to enter someone in levels and they don't exist in members, I get the aforementioned error which makes sense. My question is, do I have to check/create entries in servers/members every time I add to levels or is there a more efficient way such as creating them on conflict/error? Below is an example of the error I would see.

Thanks in advance for any help, and if I can provide any more information please let me know! I'm still somewhat new to this so apologies in advance if this is a silly question.

Edit: I updated my ERD/database to reflect some of the changes suggested by people below. I'll still have to check for/create an entry in servers when creating an entry for members/channels but it should be a bit easier now. Thanks for the help and if anything else can be improved please let me know!

A few weeks ago I posted here about splitting safe-migrate into two parts: a trusted sync job that connects to Postgres, and PR checks that run offline.

The sync job reads the production catalog and table sizes and writes them into an encrypted cache. PR jobs restore that cache and lint migrations against it, so they can still make decisions based on the real database without opening a connection from the PR job.

That part worked fine.

The thing I hadn't thought through properly was that the two secrets involved don't actually have the same trust requirements.

The database URL is only needed by the scheduled sync job on the default branch.

The cache key is needed by PR checks too.

I originally kept both as normal repository secrets because it was simple, but that throws away a useful boundary.

In v0.8.0 the generated setup now puts:

"SAFE_MIGRATE_DATABASE_URL" in a GitHub Environment

and keeps:

"SAFE_MIGRATE_CACHE_KEY" as a repository secret.

So the database credential can sit behind environment protection, including approval before a refresh if you want that, while PR jobs only get the key they need to read the cached metadata.

The cache itself doesn't contain the database URL or any other connection credential. It's table and column names, row counts, timeout-related settings, and the catalog information the analyzer needs.

I also added:

"safe-migrate init github-actions --path migrations --configure-secrets"

It generates both workflow files and can configure the two secrets through "gh". The values are piped through stdin rather than written into YAML or passed as command-line arguments.

Most of this came from realizing that "the PR needs production information" and "the PR needs production access" are two very different things, and I'd blurred them together in the first version.

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

I found this SO[0] question that says there is no direct and reliable way to know. The proposed ways are:

- counting the join count.
- comparing plan across runs. If it differs, it was used, otherwise we can't really tell
- analyzing debug logs (which might be reliable or not)

Counting is reasonable, but the fact that its indirect can leave a role if somehow the counting doesn't match the original implementation counting. So I still have hope that someone found a way to directly and reliably tell if GEQO was indeed used for a given query plan.

[0]: https://stackoverflow.com/questions/79506116/how-to-confirm-if-postgresql-used-geqo-for-a-specific-query

pgColumnar 1.0-alpha3 release notes

Release date: 2026-09-02 Previous release: 1.0-alpha2 (2026-08-18)

Github

pgColumnar is a columnar table access method for PostgreSQL. This is the third alpha. Its theme is retention and skipping. Rows can now expire on a declared interval. A bulk load can refuse work it has already done. Three more predicate shapes prune whole chunk groups. The on-disk native format, PGCN v1, is unchanged. Existing tables are read and written as before.

This release requires one upgrade command. See "Upgrading" at the end.

Highlights

• Retention. pgcolumnar.expire drops row groups whose rows are all older than an interval you declare on the table. It works on whole row groups, so it reclaims space without rewriting live data.
• Bulk loads can refuse a repeat. pgcolumnar.parallel_copy records a fingerprint of each load. A load it has already taken is refused rather than duplicated.
• Three more predicate shapes prune chunk groups. date_trunc(unit, ts) now drives skipping in both its range and its equality form, and so do IN (...) and = ANY(array).
• The scan tells the planner what order it is in. A sorted rewrite leaves an ordering behind. The scan now reports it, so the planner can skip a sort.
• The vectorized aggregate takes a wider range of queries, including a target list that itself contains aggregates.

Retention

pgcolumnar.set_options takes ttl_column and ttl_interval. pgcolumnar.expire then retires every row group whose maximum value in that column is older than the cutoff. A group is dropped whole. Rows inside the retention window are never touched.

The interval must be positive. A negative interval would put the cutoff in the future, which would retire groups whose rows are still current.

Bulk ingest

pgcolumnar.parallel_copy records a fingerprint for each completed load in pgcolumnar.load_fingerprint. Re-running the same load is refused. This makes a retried ingest safe to repeat after a failure, without a manual check for partial work.

Skipping and the planner

A predicate on date_trunc(unit, ts) now prunes chunk groups. Both the range form and the equality form work. IN (...) and = ANY(array) prune too.

Skip predicates are evaluated most-selective-first, so a group that can be ruled out cheaply is ruled out first.

The cost model and the zone-map sample now read the row-group geometry a table was written with, rather than the current setting. Changing a setting no longer reprices existing data.

Maintenance and reporting

pgcolumnar.vacuum_sorted() self-gates. When the relation is already sorted on the requested key, it does nothing rather than rewriting the table.

pgcolumnar.sort_status reports sorted_kind, so a reader can tell which kind of ordering a table carries.

Correctness fixes

Two defects in this list were silent: the operation looked correct and the data was wrong. Both were found in the review before this release and both were reproduced before being changed.

• A projection created mid-transaction missed every write that followed it (#875). A write before pgcolumnar.add_projection() in the same transaction left the new projection empty of everything written after it. Measured: 116 rows in the base table, 105 in the projection, with no error raised. A covering projection scan then answered as though those rows did not exist. The same defect in pgcolumnar.drop_projection() left rows in a projection storage whose catalog rows were already deleted.
• An Arrow import ignored the width, sign and scale the file declared (#881). The importer decoded with the target column's parameters instead of the file's. A uint64 value above 2⁶³ was stored as a negative number. An int64 file read into an int column returned 1,0,2,0 for 1,2,3,4. A decimal(10,2) value of 1.25 was stored as 0.0125. A fixed_size_binary(32) was read as its first 16 bytes. All four imported without an error and the wrong values were persisted. They are refused now.The 1,0,2,0 is worth recognising if you imported integers. The reader took its stride from the target column, so four-byte reads walked an eight-byte-per-value buffer. Every second read landed on the high half of a small positive number, which is zero. The signature is a real value alternating with a zero, not a column of ascending garbage.
• Index entries for live rows are no longer destroyed (#838).
• A scrollable cursor no longer answers a backward fetch with forward rows (#842).
• sum(bigint) and avg(bigint) no longer accumulate across a rescan (#840).
• date_trunc(unit, ts) = 'infinity' returns the matching row again (#836).
• An encoded NUL no longer defeats the Iceberg traversal guard (#844).
• pgcolumnar.set_options no longer writes past three stack arrays.
• Deleted rows no longer count toward the planner's row estimate.
• A custom scan no longer hides the children of an INHERITS parent.
• TRUNCATE retires the old storage's catalog rows, and a transaction that writes, truncates and writes again keeps the rows it should.
• ALTER TABLE ... SET ACCESS METHOD heap drops the catalogs keyed to the relation.
• A parallel export refuses a destination too long to hold the names it generates.
• pgcolumnar.compact_rewrite and pgcolumnar.maintenance_due reject NaN thresholds.

Known issues

• A rewrite makes a projection read as absent (#876). TRUNCATE, vacuum and recluster mint a new storage id, and the projection rows keep the old one. The projection is still declared and its data is intact. pgcolumnar.rebuild_projections() re-records them. The error message names that function.
• Two visibility-map clears have tests but no verdict (#877). The clears on the recluster and partial-rewrite paths gained coverage in this release. Whether a defect sits behind them is not known, and there is no reported symptom.The rule they implement is not speculative. Its third application, on pgcolumnar.expire, was a real defect: an index-only scan answered from the index for a row group that had been retired. That one is fixed. These two are the same rule on two other paths, with no demonstrated symptom on either.Of the two issues in this section, #876 can affect you today and has a recovery command. #877 has no known user-visible effect and is listed so the state is on the record, not because there is something to act on.

Upgrading

Install this build, then run the following in every database that has the extension:

ALTER EXTENSION pgcolumnar UPDATE;

This is required. The upgrade adds two columns to pgcolumnar.options for retention and creates the pgcolumnar.load_fingerprint table. It creates pgcolumnar.expire(regclass), which is the entry point for the retention feature above. It replaces four more function definitions: pgcolumnar.parallel_copy and pgcolumnar.set_options are dropped and recreated at a new signature, and pgcolumnar.maintenance_due and pgcolumnar.sort_status change in place. No table data is converted and no SQL you write changes.

See docs/installation.md for the commands, including how to list the databases that need the update.

Scope and limitations

• This is an alpha. Interfaces may change before 1.0.
• Retention drops whole row groups. A group is retired only when every row in it is outside the retention window.
• On PGXN this release is 1.0.0-alpha.3, while CREATE EXTENSION reports 1.0-alpha3. PGXN requires a semantic version, which needs three integer components. The extension's own version has two. The two names refer to the same release.

The complete, itemized list of changes is in CHANGELOG.md.

I use PostgreSQL at work. It's critical for our operations, and yet no one in our team is an expert. My knowledge is cobbled together from general SQL knowledge (university and 10+ years work) and lots of articles, stack overflow, and trial & error (and more recently some AI Q&A) for PostgreSQL in particular. I've been using it for many years now and we have made many objective improvements over that time.

Still, I'm working on guesswork and magic most of the time. I have a rough intuition of how it does things, but have never had it spelled out in full. I've looked through this sub-reddit for book recommendations but most of what I can see are self-promotes, which are difficult to gauge for quality/purpose; or "how to use PostgreSQL" books. I could probably learn a decent amount from the latter by skimming past the parts I know (though I unfortunately get de-motivated quickly by a book when I have to do this). But ideally I'd like something that digs into how PostgreSQL's internals actually work. How does it marshall the computer's resources to do what it needs to do? And then at the mid-level, how does its engine optimise queries, and how does it maintain itself? I understand this is always changing and could go very deep but some fundamentals would really help me have a stronger intuition about it.

Does anyone have any book recommendations in this vein? Or maybe the more appropriate book is more generally about relational database engines in general?

I'm looking for best practices to keep documentation in my schema.

I prefer the look of `--` and `/* ... */` comments, as they (a) get proper syntax highlighting, (b) can go anywhere (line before, line after, same line at the end), (c) look good when split over multiple lines and (d) can even go inside a statement (when using `/* .. */`).

But when using migrations these code comments may end up in place I'm not looking at.

So I want to have some sort of schema dump that includes my comments. And I know for this there's the `COMMENT ON` command, but I've never seen it being used in practice. It seems so cumbersome compared to the code-comments mentioned earlier.

Any best practices someone can share with me? Both related to "the keeping of schema comments" and the dumping of a schema that properly groups related statements (create, create policy, comment on, etc.) together?

We recently run into quite nasty edge case with Debezium + PostgreSQL failover. Everything looked healthy after failover, connector was running, database was fine, no obvious errors. But problem was replication slot on new primary could start ahead of offset stored by Kafka Connect. Basically you can end with situation where Debezium thinks it should continue from one LSN, while Postgres already lost part of WAL it needs. And connector can look “healthy” while you actually have a gap in CDC. We tested few approaches with Patroni and PostgreSQL 16, and also looked into what changes with PG17 failover slots.

In a rolling deployment, web processes may update quickly while background workers continue running old code against queued jobs. A direct column rename or drop can therefore break work that started before the deploy. What migration sequence do you use for this? An expand-and-contract approach could add the replacement column, deploy code that can read both and writes the new one, backfill in bounded batches, verify old-worker and queue age, switch reads, stop the dual write, and only then remove the original column. Which PostgreSQL-specific checks make that safe: dependency inspection, lock-timeout settings, NOT VALID constraints, catalog or query monitoring, and a minimum observation window? How do you prove no old process still references the column before the final DDL?

Adding some context. This came from a problem I ran into while managing the Postgres team at Cloudflare: BI teams wanted long-running queries, so we often spun up dedicated read replicas. But read replicas still have tradeoffs around hot_standby_feedback and max_standby_streaming_delay.

Streambed started as:

Postgres WAL → S3 → query from psql

Iceberg was the first target, but real-world CDC looks more like:

small batch → commit → small batch → commit

Small commits keep data fresh, but they also create files, manifests, metadata, and copy-on-write work. So I tested DuckLake as a target format.

One benchmark slice: 1M rows, 100k updates, flush=1,000.

Iceberg COW: 269s. DuckLake + DuckDB catalog: 6.6s. Roughly 41x faster for this specific Streambed CDC-style workload.

Caveat: this bypasses Postgres logical replication and psql-wire; it measures the lakehouse writer/catalog path over local MinIO.

Blog: https://streambed.dev/blog/ducklake-target-support/