How to run a join between PostgreSQL and MongoDB

The question comes up as soon as an application stores its orders in PostgreSQL and its logs or events in MongoDB. At some point, you have to cross-reference this data: enrich a list of SQL users with their interactions stored in NoSQL, or group an account's orders with its application sessions. Yet neither PostgreSQL nor MongoDB can join the other directly.

There are four practical approaches in 2026. Each has its use context, its setup cost and its volume limits. The choice depends less on the technique than on how often it runs and how much data it processes.

Why this join isn't native

The two engines share neither protocol, nor data model, nor query planner. PostgreSQL speaks SQL and thinks in relational tables. MongoDB speaks its own binary protocol and thinks in BSON documents. No common mechanism lets one engine query the other without an intermediary.

This leaves three families of solutions: move the data to a common location, have one engine call the other through a connector, or run the JOIN in a third engine that can read both sources. The four approaches that follow fall into one of these families.

Synchronizing a copy with an ETL

The most direct-looking option is to replicate the MongoDB data to PostgreSQL, or the reverse, then do the join in the target engine. A script reads the BSON documents, flattens them into columns, and does INSERTs on the PostgreSQL side. Once the data is present on both sides, the JOIN becomes ordinary SQL.

The benefit is real: the result runs in a known engine, the performance is predictable, and the queries can be optimized with indexes. The cost is in the pipeline. You have to write the synchronization, handle schemas that drift on the MongoDB side, deal with type conflicts, and accept latency since the data is never up to the second.

Typical tools: Airbyte, Meltano, or a Python script with pymongo and psycopg. For recurring cold analytics, it's viable. For a one-off exploration, it's overkill.

PostgreSQL Foreign Data Wrappers

PostgreSQL has long offered FDWs, a mechanism that exposes an external source as a virtual table. The mongo_fdw project declares a MongoDB collection as a foreign table in PostgreSQL, and you then query it in standard SQL, including in a JOIN with local tables.

The setup sequence goes through CREATE EXTENSION mongo_fdw, then CREATE SERVER, CREATE USER MAPPING and CREATE FOREIGN TABLE. Once the table is declared, an ordinary SELECT with a JOIN is enough, and PostgreSQL delegates the MongoDB read internally.

This approach keeps a single SQL interface and avoids copying data. Performance depends on pushdown: if the PostgreSQL planner doesn't push the filters down to MongoDB, the server downloads the entire collection before filtering on the PostgreSQL side. On large volumes, this becomes unusable. Mapping BSON types to SQL also takes care, and not all MongoDB operators are exposed. Best when a central PostgreSQL usage already exists and the MongoDB volumes are reasonable.

Distributed federation engines: Trino, Presto, Starburst

Trino, formerly PrestoSQL, is a distributed SQL engine designed to run queries across several heterogeneous sources. It has a PostgreSQL connector and a MongoDB connector. You declare two catalogs, and you write a SELECT that mentions, for example, postgresql.public.orders and mongodb.logs.events in the same JOIN.

Trino solves the problem cleanly: a single planner, real pushdown when the connector supports it, distributed execution for large volumes. It's the reference tool on the data engineering side.

The cost is elsewhere: Trino is a full-fledged infrastructure. A cluster to deploy, catalog configuration, credential management, monitoring, version upgrades. For a developer who wants to explore an ad hoc join from their machine, it's the equivalent of installing a data warehouse to answer a single question. Best when federation is a recurring need at the scale of a data team.

Client-side federation: QoreDB and DuckDB

QoreDB takes another path: run the federation locally, at query time, with no server or cluster to provision. The mechanism relies on DuckDB, an analytical SQL engine embedded in the QoreDB binary.

The concrete flow: you declare your two connections (PostgreSQL and MongoDB) in QoreDB, open a SQL tab, and write a query that mentions both sources through a three-part syntax, like pg_prod.public.users JOIN mongo_logs.app.events. QoreDB detects the federated references, reads the necessary data from each source, loads it into an in-memory DuckDB instance, runs the JOIN, then returns the result. The DuckDB instance is ephemeral: created for the query, destroyed afterward.

Several technical choices make the approach viable for workstation use. The sources are read in parallel, with a per-source timeout (30 seconds by default) and a global timeout for the whole pipeline. A per-source row limit is applied to prevent a large collection from saturating the RAM. No persistent state is created: no permanent copy, no cache to invalidate, no schema to maintain.

The angle is deliberate: this isn't a production federation infrastructure, it's a tool for one-off exploration and cross-referencing on a developer's machine. To cross a few hundred thousand or a few million rows in order to understand a bug, produce a report or validate a hunch, it's enough and immediate. For larger volumes or repeated use by an entire team, Trino remains the right tool.

Which approach depending on the context

For a recurring large-scale analytics pipeline, on significant volumes: a scheduled ETL or Trino, depending on the expected freshness and the complexity of the SQL.

To durably expose MongoDB collections in an existing PostgreSQL application: mongo_fdw, validating pushdown on the critical queries with EXPLAIN.

For a one-off exploration or an on-demand cross-reference from a workstation: client-side federation like QoreDB, which avoids any server setup and keeps the query entirely local.

The need to join PostgreSQL and MongoDB isn't an exotic case; it's a reality of most modern stacks where relational OLTP coexists with document storage. Choosing the approach depends on the context: who runs the query, how often, on what volume. The four options exist and are mature, provided you pick the one that matches the real need rather than the one that looks most impressive.