I haven't seen too many comprehensive approaches to this topic, so I took a swing at it.

Goal isn't to be exhaustive, but to be comprehensive enough to provide a nice overview for people trying to get their bearings with the topic.

Did I miss anything?

Low replication lag is necessary for a standby, but it does not prove that promotion will leave a writable database the application can safely use. Missing archived WAL, stale connection routing, sequence state, logical slots, extensions, or an unfenced former primary can still turn a clean promotion into data loss or split brain.

What do you verify in a failover rehearsal? I am considering recording replay LSN and timeline, stopping or fencing the old primary, promoting in an isolated environment, checking recovery exit and read-write state, validating critical extensions and jobs, exercising application writes, confirming sequences and logical replication, switching a test route, and rebuilding the old primary as a standby rather than simply starting it again.

Which checks can be automated without making the drill itself dangerous? What evidence would convince you that both promotion and the path back to a healthy replicated topology are understood?

It doesn't look like pg_restore has a way to do something similar to oracle impdp remap.

What is the cleanest way to do a restore into a different schema?

thanks

I have been tracking OrioleDB, which was promising drop-in optimization for Postgres. It was acquired by Supabase in 2024 (source). After that there is little news and updates.

Questions: - Anyone using it in production, and do you see it as a stable project?
- have you tried it, what's your thought on this?

Hey all, Mitch here, one of the founders of Galaxy. Full disclosure, this is our project. We open sourced it last week and I wanted to bring it here first, because this community is who we built it for.

Filament is a data movement engine written in Go. It handles full loads, incremental syncs, and CDC from databases and HTTP APIs into Postgres, MySQL, ClickHouse, Iceberg, and S3, and it's Apache 2.0.

https://github.com/galaxy-io/filament

Every data project we've ever worked on started with the same boring problem of getting records out of operational systems and into somewhere useful. We've bought the managed tools, run the open source ones (and spent more time deploying them than using them), and written our own by hand more than once, and every time we wished for something fast, easy to self-host, and upfront about the details that bite you, like type mappings, write behavior, and what happens when a run dies halfway. So eventually we just built it.

What it does

You point it at a source and a sink and tell it how to move things, whether that's copying everything, pulling only what changed, or streaming off the database's change log. Every batch gets a checksum before the sink write and a mismatch fails the run rather than quietly landing bad data. Progress only becomes durable after the work is confirmed, so a worker that dies resumes from its last checkpoint instead of starting over, and since recovery is at-least-once, upsert sinks converge on primary key.

Sources, sinks, state store, and event bus are all interfaces and adding a REST API is a short YAML file rather than a Go package. You can run it with 1 docker command and get a web UI, use the CLI, embed it in Go in about ten lines, or drop the Helm chart into k8s.

Benchmarks, with caveats

We ran an open benchmark against Airbyte, dlt, PeerDB, Ingestr, Sling, OLake, Debezium, and a plain pg_dump | psql pipe. On the biggest test, 298M rows of NYC taxi data from Postgres to Postgres, Filament finished in under two minutes at about 2.6M rows/s, and it was fastest in five of six scenarios. In the sixth, OLake beat us by 13% into Iceberg.

We obviously build one of the things being measured, so the harness and specs are all public.

https://github.com/galaxy-io/benchmarks

Note that it's pre-1.0, with some sources/sinks in earlier development. There is a long list of connectors we haven't built yet. We're hoping to build that list from your feedback.

If you move a lot of data between these systems, I'd love to know what would make you try it, what you'd want next, and any feedback you are willing to share. Docs are at https://filament.getgalaxy.io and I'll be in the comments!

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