Tools: PgBench on MongoDB via Foreign Data Wrapper

Setup (Docker) ## PostgreSQL (tps = 4085, latency average = 12 ms) ## MongoDB (tps = 4922, latency average = 10 ms) Disclaimer: This is an experiment, not a benchmark, and not an architectural recommendation. Translation layers do not improve performance, whether you emulate MongoDB on PostgreSQL or PostgreSQL on MongoDB. I wanted to test the performance of the mongo_fdw foreign data wrapper for PostgreSQL and rather than writing a specific benchmark, I used PgBench. The default PgBench workload is not representative of a real application because all sessions update the same row — the global balance — but it’s useful for testing lock contention. This is where MongoDB shines, as it provides ACID guarantees without locking. I stressed the situation by running pgbench -c 50, with 50 client connections competing to update those rows. To compare, I've run the same pgbench command on two PostgreSQL databases: I was using my laptop (MacBook Pro Apple M4 Max), with local MongoDB atlas I compiled mongo_fdw from EDB's repository to add to the PostgreSQL 18 image with the following Dockerfile: I built this image (docker build -t pachot/postgres_mongo_fdw) and started it, linking it to a MongoDB Atlas container: I created a separate database for each test: For the PostgreSQL baseline, I initialized the database with pgbench -i pgbench_postgres, which creates the tables with primary keys and inserts 100,000 accounts into a single branch. For MongoDB, I defined the collections as foreign tables and connected with psql pgbench_mongo_fdw: On the MongoDB server, I created the user and the collections mapped from PostgreSQL (using mongosh): Because pgbench -i truncates tables, which the MongoDB Foreign Data Wrapper does not support, I instead use INSERT commands (via psql pgbench_mongo_fdw) similar to those run by pgbench -i: Here is what I’ve run—the results follow: Here are the results of the standard pgbench benchmark on PostgreSQL tables: The run averages 4,000 transactions per second with 12 ms latency. Most latency comes from the first update, when all connections target the same row and cannot execute concurrently. Here is the same run, with foreign tables reading from and writing to MongoDB instead of PostgreSQL: MongoDB doesn’t wait for locks, so all statements have similar response times. This yields higher throughput and lower latency, with the additional layer’s overhead offset by the faster storage engine. In the Dockerfile, I patched the foreign data wrapper’s connection.c when I've seen unnecessary ping in the call stack. Running on MongoDB collections was still faster than PostgreSQL with the original code. The PostgreSQL foreign data wrapper, while useful, is rarely optimized, adds latency, and offers limited transaction control and pushdown optimizations. It can still be acceptable to offload some tables to MongoDB collections until you convert your SQL and connect directly to MongoDB. Templates let you quickly answer FAQs or store snippets for re-use. Are you sure you want to hide this comment? It will become hidden in your post, but will still be visible via the comment's permalink. Hide child comments as well For further actions, you may consider blocking this person and/or reporting abuse COMMAND_BLOCK: FROM docker.io/postgres:18 AS build # Install build dependencies including system libmongoc/libbson so autogen.sh doesn't compile them itself RUN apt-get update && apt-get install -y --no-install-recommends wget unzip ca-certificates make gcc cmake pkg-config postgresql-server-dev-18 libssl-dev libzstd-dev libmongoc-dev libbson-dev libjson-c-dev libsnappy1v5 libmongocrypt0 && rm -rf /var/lib/apt/lists/* # Build environment ENV PKG_CONFIG_PATH=/tmp/mongo_fdw/mongo-c-driver/src/libmongoc/src:/tmp/mongo_fdw/mongo-c-driver/src/libbson/src ENV LD_LIBRARY_PATH=/usr/lib/x86_64-linux-gnu ENV MONGOC_INSTALL_DIR=${LD_LIBRARY_PATH} ENV JSONC_INSTALL_DIR=${LD_LIBRARY_PATH} # get MongoDB Foreign Data Wrapper sources RUN apt-get update && apt-get install -y --no-install-recommends wget unzip ca-certificates make gcc cmake pkg-config postgresql-server-dev-18 libssl-dev libzstd-dev libmongoc-dev libjson-c-dev libsnappy1v5 libmongocrypt0 ADD https://github.com/EnterpriseDB/mongo_fdw/archive/refs/heads/master.zip /tmp/sources.zip RUN mkdir -p /tmp/mongo_fdw && unzip /tmp/sources.zip -d /tmp/mongo_fdw # Build MongoDB Foreign Data Wrapper WORKDIR /tmp/mongo_fdw/mongo_fdw-master # remove useless ping RUN sed -i -e '/Ping the database using/d' -e 's?if (entry->conn != NULL)?/*&?' -e 's?return entry->conn?*/&?' connection.c # build with Mongodb client RUN ./autogen.sh && make USE_PGXS=1 && make USE_PGXS=1 install # final stage FROM docker.io/postgres:18 COPY --from=build /usr/share/postgresql/18/extension/mongo_fdw* /usr/share/postgresql/18/extension/ COPY --from=build /usr/lib/postgresql/18/lib/mongo_fdw.so /usr/lib/postgresql/18/lib/ RUN apt-get update && apt-get install -y libmongoc-1.0-0 libbson-1.0-0 libmongocrypt0 libsnappy1v5 libutf8proc-dev && rm -rf /var/lib/apt/lists/* Enter fullscreen mode Exit fullscreen mode COMMAND_BLOCK: FROM docker.io/postgres:18 AS build # Install build dependencies including system libmongoc/libbson so autogen.sh doesn't compile them itself RUN apt-get update && apt-get install -y --no-install-recommends wget unzip ca-certificates make gcc cmake pkg-config postgresql-server-dev-18 libssl-dev libzstd-dev libmongoc-dev libbson-dev libjson-c-dev libsnappy1v5 libmongocrypt0 && rm -rf /var/lib/apt/lists/* # Build environment ENV PKG_CONFIG_PATH=/tmp/mongo_fdw/mongo-c-driver/src/libmongoc/src:/tmp/mongo_fdw/mongo-c-driver/src/libbson/src ENV LD_LIBRARY_PATH=/usr/lib/x86_64-linux-gnu ENV MONGOC_INSTALL_DIR=${LD_LIBRARY_PATH} ENV JSONC_INSTALL_DIR=${LD_LIBRARY_PATH} # get MongoDB Foreign Data Wrapper sources RUN apt-get update && apt-get install -y --no-install-recommends wget unzip ca-certificates make gcc cmake pkg-config postgresql-server-dev-18 libssl-dev libzstd-dev libmongoc-dev libjson-c-dev libsnappy1v5 libmongocrypt0 ADD https://github.com/EnterpriseDB/mongo_fdw/archive/refs/heads/master.zip /tmp/sources.zip RUN mkdir -p /tmp/mongo_fdw && unzip /tmp/sources.zip -d /tmp/mongo_fdw # Build MongoDB Foreign Data Wrapper WORKDIR /tmp/mongo_fdw/mongo_fdw-master # remove useless ping RUN sed -i -e '/Ping the database using/d' -e 's?if (entry->conn != NULL)?/*&?' -e 's?return entry->conn?*/&?' connection.c # build with Mongodb client RUN ./autogen.sh && make USE_PGXS=1 && make USE_PGXS=1 install # final stage FROM docker.io/postgres:18 COPY --from=build /usr/share/postgresql/18/extension/mongo_fdw* /usr/share/postgresql/18/extension/ COPY --from=build /usr/lib/postgresql/18/lib/mongo_fdw.so /usr/lib/postgresql/18/lib/ RUN apt-get update && apt-get install -y libmongoc-1.0-0 libbson-1.0-0 libmongocrypt0 libsnappy1v5 libutf8proc-dev && rm -rf /var/lib/apt/lists/* COMMAND_BLOCK: FROM docker.io/postgres:18 AS build # Install build dependencies including system libmongoc/libbson so autogen.sh doesn't compile them itself RUN apt-get update && apt-get install -y --no-install-recommends wget unzip ca-certificates make gcc cmake pkg-config postgresql-server-dev-18 libssl-dev libzstd-dev libmongoc-dev libbson-dev libjson-c-dev libsnappy1v5 libmongocrypt0 && rm -rf /var/lib/apt/lists/* # Build environment ENV PKG_CONFIG_PATH=/tmp/mongo_fdw/mongo-c-driver/src/libmongoc/src:/tmp/mongo_fdw/mongo-c-driver/src/libbson/src ENV LD_LIBRARY_PATH=/usr/lib/x86_64-linux-gnu ENV MONGOC_INSTALL_DIR=${LD_LIBRARY_PATH} ENV JSONC_INSTALL_DIR=${LD_LIBRARY_PATH} # get MongoDB Foreign Data Wrapper sources RUN apt-get update && apt-get install -y --no-install-recommends wget unzip ca-certificates make gcc cmake pkg-config postgresql-server-dev-18 libssl-dev libzstd-dev libmongoc-dev libjson-c-dev libsnappy1v5 libmongocrypt0 ADD https://github.com/EnterpriseDB/mongo_fdw/archive/refs/heads/master.zip /tmp/sources.zip RUN mkdir -p /tmp/mongo_fdw && unzip /tmp/sources.zip -d /tmp/mongo_fdw # Build MongoDB Foreign Data Wrapper WORKDIR /tmp/mongo_fdw/mongo_fdw-master # remove useless ping RUN sed -i -e '/Ping the database using/d' -e 's?if (entry->conn != NULL)?/*&?' -e 's?return entry->conn?*/&?' connection.c # build with Mongodb client RUN ./autogen.sh && make USE_PGXS=1 && make USE_PGXS=1 install # final stage FROM docker.io/postgres:18 COPY --from=build /usr/share/postgresql/18/extension/mongo_fdw* /usr/share/postgresql/18/extension/ COPY --from=build /usr/lib/postgresql/18/lib/mongo_fdw.so /usr/lib/postgresql/18/lib/ RUN apt-get update && apt-get install -y libmongoc-1.0-0 libbson-1.0-0 libmongocrypt0 libsnappy1v5 libutf8proc-dev && rm -rf /var/lib/apt/lists/* COMMAND_BLOCK: # start MongoDB Atlas (use Atlas CLI) atlas deployments setup mongo --type local --port 27017 --force # start PostgreSQL with Mongo FDW linked to MongoDB docker run -d --link mongo:mongo --name mpg -p 5432:5432 \ -e POSTGRES_PASSWORD=x pachot/postgres_mongo_fdw Enter fullscreen mode Exit fullscreen mode COMMAND_BLOCK: # start MongoDB Atlas (use Atlas CLI) atlas deployments setup mongo --type local --port 27017 --force # start PostgreSQL with Mongo FDW linked to MongoDB docker run -d --link mongo:mongo --name mpg -p 5432:5432 \ -e POSTGRES_PASSWORD=x pachot/postgres_mongo_fdw COMMAND_BLOCK: # start MongoDB Atlas (use Atlas CLI) atlas deployments setup mongo --type local --port 27017 --force # start PostgreSQL with Mongo FDW linked to MongoDB docker run -d --link mongo:mongo --name mpg -p 5432:5432 \ -e POSTGRES_PASSWORD=x pachot/postgres_mongo_fdw CODE_BLOCK: export PGHOST=localhost export PGPASSWORD=x export PGUSER=postgres psql -c 'create database pgbench_mongo_fdw' psql -c 'create database pgbench_postgres' Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: export PGHOST=localhost export PGPASSWORD=x export PGUSER=postgres psql -c 'create database pgbench_mongo_fdw' psql -c 'create database pgbench_postgres' CODE_BLOCK: export PGHOST=localhost export PGPASSWORD=x export PGUSER=postgres psql -c 'create database pgbench_mongo_fdw' psql -c 'create database pgbench_postgres' CODE_BLOCK: DROP EXTENSION if exists mongo_fdw CASCADE; -- Enable the FDW extension CREATE EXTENSION mongo_fdw; -- Create FDW server pointing to the MongoDB host CREATE SERVER mongo_srv FOREIGN DATA WRAPPER mongo_fdw OPTIONS (address 'mongo', port '27017'); -- Create user mapping for the current Postgres user CREATE USER MAPPING FOR postgres SERVER mongo_srv OPTIONS (username 'postgres', password 'x'); -- Foreign tables for pgbench schema CREATE FOREIGN TABLE pgbench_accounts( _id name, aid int, bid int, abalance int, filler text ) SERVER mongo_srv OPTIONS (collection 'pgbench_accounts'); CREATE FOREIGN TABLE pgbench_branches( _id name, bid int, bbalance int, filler text ) SERVER mongo_srv OPTIONS (collection 'pgbench_branches'); CREATE FOREIGN TABLE pgbench_tellers( _id name, tid int, bid int, tbalance int, filler text ) SERVER mongo_srv OPTIONS (collection 'pgbench_tellers'); CREATE FOREIGN TABLE pgbench_history( _id name, tid int, bid int, aid int, delta int, mtime timestamp, filler text ) SERVER mongo_srv OPTIONS (collection 'pgbench_history'); Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: DROP EXTENSION if exists mongo_fdw CASCADE; -- Enable the FDW extension CREATE EXTENSION mongo_fdw; -- Create FDW server pointing to the MongoDB host CREATE SERVER mongo_srv FOREIGN DATA WRAPPER mongo_fdw OPTIONS (address 'mongo', port '27017'); -- Create user mapping for the current Postgres user CREATE USER MAPPING FOR postgres SERVER mongo_srv OPTIONS (username 'postgres', password 'x'); -- Foreign tables for pgbench schema CREATE FOREIGN TABLE pgbench_accounts( _id name, aid int, bid int, abalance int, filler text ) SERVER mongo_srv OPTIONS (collection 'pgbench_accounts'); CREATE FOREIGN TABLE pgbench_branches( _id name, bid int, bbalance int, filler text ) SERVER mongo_srv OPTIONS (collection 'pgbench_branches'); CREATE FOREIGN TABLE pgbench_tellers( _id name, tid int, bid int, tbalance int, filler text ) SERVER mongo_srv OPTIONS (collection 'pgbench_tellers'); CREATE FOREIGN TABLE pgbench_history( _id name, tid int, bid int, aid int, delta int, mtime timestamp, filler text ) SERVER mongo_srv OPTIONS (collection 'pgbench_history'); CODE_BLOCK: DROP EXTENSION if exists mongo_fdw CASCADE; -- Enable the FDW extension CREATE EXTENSION mongo_fdw; -- Create FDW server pointing to the MongoDB host CREATE SERVER mongo_srv FOREIGN DATA WRAPPER mongo_fdw OPTIONS (address 'mongo', port '27017'); -- Create user mapping for the current Postgres user CREATE USER MAPPING FOR postgres SERVER mongo_srv OPTIONS (username 'postgres', password 'x'); -- Foreign tables for pgbench schema CREATE FOREIGN TABLE pgbench_accounts( _id name, aid int, bid int, abalance int, filler text ) SERVER mongo_srv OPTIONS (collection 'pgbench_accounts'); CREATE FOREIGN TABLE pgbench_branches( _id name, bid int, bbalance int, filler text ) SERVER mongo_srv OPTIONS (collection 'pgbench_branches'); CREATE FOREIGN TABLE pgbench_tellers( _id name, tid int, bid int, tbalance int, filler text ) SERVER mongo_srv OPTIONS (collection 'pgbench_tellers'); CREATE FOREIGN TABLE pgbench_history( _id name, tid int, bid int, aid int, delta int, mtime timestamp, filler text ) SERVER mongo_srv OPTIONS (collection 'pgbench_history'); CODE_BLOCK: db.createUser( { user: "postgres", pwd: "x", roles: [ { role: "readWrite", db: "test" } ] } ) ; db.dropDatabase("test"); use test; db.pgbench_branches.createIndex({bid:1},{unique:true}); db.pgbench_tellers.createIndex({tid:1},{unique:true}); db.pgbench_accounts.createIndex({aid:1},{unique:true}); db.createCollection("pgbench_history"); Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: db.createUser( { user: "postgres", pwd: "x", roles: [ { role: "readWrite", db: "test" } ] } ) ; db.dropDatabase("test"); use test; db.pgbench_branches.createIndex({bid:1},{unique:true}); db.pgbench_tellers.createIndex({tid:1},{unique:true}); db.pgbench_accounts.createIndex({aid:1},{unique:true}); db.createCollection("pgbench_history"); CODE_BLOCK: db.createUser( { user: "postgres", pwd: "x", roles: [ { role: "readWrite", db: "test" } ] } ) ; db.dropDatabase("test"); use test; db.pgbench_branches.createIndex({bid:1},{unique:true}); db.pgbench_tellers.createIndex({tid:1},{unique:true}); db.pgbench_accounts.createIndex({aid:1},{unique:true}); db.createCollection("pgbench_history"); CODE_BLOCK: \set scale 1 INSERT INTO pgbench_branches (bid, bbalance, filler) SELECT bid, 0, '' FROM generate_series(1, :scale) AS bid; INSERT INTO pgbench_tellers (tid, bid, tbalance, filler) SELECT tid, ((tid - 1) / 10) + 1, 0, '' FROM generate_series(1, :scale * 10) AS tid; INSERT INTO pgbench_accounts (aid, bid, abalance, filler) SELECT aid, ((aid - 1) / 100000) + 1, 0, '' FROM generate_series(1, :scale * 100000) AS aid; Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: \set scale 1 INSERT INTO pgbench_branches (bid, bbalance, filler) SELECT bid, 0, '' FROM generate_series(1, :scale) AS bid; INSERT INTO pgbench_tellers (tid, bid, tbalance, filler) SELECT tid, ((tid - 1) / 10) + 1, 0, '' FROM generate_series(1, :scale * 10) AS tid; INSERT INTO pgbench_accounts (aid, bid, abalance, filler) SELECT aid, ((aid - 1) / 100000) + 1, 0, '' FROM generate_series(1, :scale * 100000) AS aid; CODE_BLOCK: \set scale 1 INSERT INTO pgbench_branches (bid, bbalance, filler) SELECT bid, 0, '' FROM generate_series(1, :scale) AS bid; INSERT INTO pgbench_tellers (tid, bid, tbalance, filler) SELECT tid, ((tid - 1) / 10) + 1, 0, '' FROM generate_series(1, :scale * 10) AS tid; INSERT INTO pgbench_accounts (aid, bid, abalance, filler) SELECT aid, ((aid - 1) / 100000) + 1, 0, '' FROM generate_series(1, :scale * 100000) AS aid; COMMAND_BLOCK: docker exec -it mpg \ pgbench -T 60 -P 5 -c 50 -r -U postgres -M prepared pgbench_postgres docker exec -it mpg \ pgbench -n -T 60 -P 5 -c 50 -r -U postgres -M prepared pgbench_mongo_fdw Enter fullscreen mode Exit fullscreen mode COMMAND_BLOCK: docker exec -it mpg \ pgbench -T 60 -P 5 -c 50 -r -U postgres -M prepared pgbench_postgres docker exec -it mpg \ pgbench -n -T 60 -P 5 -c 50 -r -U postgres -M prepared pgbench_mongo_fdw COMMAND_BLOCK: docker exec -it mpg \ pgbench -T 60 -P 5 -c 50 -r -U postgres -M prepared pgbench_postgres docker exec -it mpg \ pgbench -n -T 60 -P 5 -c 50 -r -U postgres -M prepared pgbench_mongo_fdw CODE_BLOCK: franck.pachot % docker exec -it mpg \ pgbench -T 60 -P 5 -c 50 -r -U postgres -M prepared pgbench_postgres pgbench (18.1 (Debian 18.1-1.pgdg13+2)) starting vacuum...end. progress: 5.0 s, 3847.4 tps, lat 12.860 ms stddev 14.474, 0 failed progress: 10.0 s, 4149.0 tps, lat 12.051 ms stddev 12.893, 0 failed progress: 15.0 s, 3940.6 tps, lat 12.668 ms stddev 12.576, 0 failed progress: 20.0 s, 3500.0 tps, lat 14.300 ms stddev 16.424, 0 failed progress: 25.0 s, 4013.0 tps, lat 12.462 ms stddev 13.175, 0 failed progress: 30.0 s, 3437.4 tps, lat 14.539 ms stddev 25.607, 0 failed progress: 35.0 s, 4421.9 tps, lat 11.308 ms stddev 12.100, 0 failed progress: 40.0 s, 4485.0 tps, lat 11.140 ms stddev 12.031, 0 failed progress: 45.0 s, 4286.2 tps, lat 11.654 ms stddev 13.244, 0 failed progress: 50.0 s, 4008.6 tps, lat 12.476 ms stddev 13.586, 0 failed progress: 55.0 s, 4551.8 tps, lat 10.959 ms stddev 13.791, 0 failed progress: 60.0 s, 4356.2 tps, lat 11.505 ms stddev 15.813, 0 failed transaction type: <builtin: TPC-B (sort of)> scaling factor: 1 query mode: prepared number of clients: 50 number of threads: 1 maximum number of tries: 1 duration: 60 s number of transactions actually processed: 245035 number of failed transactions: 0 (0.000%) latency average = 12.234 ms latency stddev = 14.855 ms initial connection time = 38.862 ms tps = 4085.473436 (without initial connection time) statement latencies in milliseconds and failures: 0.000 0 \set aid random(1, 100000 * :scale) 0.000 0 \set bid random(1, 1 * :scale) 0.000 0 \set tid random(1, 10 * :scale) 0.000 0 \set delta random(-5000, 5000) 0.036 0 BEGIN; 0.058 0 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; 0.039 0 SELECT abalance FROM pgbench_accounts WHERE aid = :aid; 10.040 0 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; 1.817 0 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; 0.041 0 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); 0.202 0 END; Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: franck.pachot % docker exec -it mpg \ pgbench -T 60 -P 5 -c 50 -r -U postgres -M prepared pgbench_postgres pgbench (18.1 (Debian 18.1-1.pgdg13+2)) starting vacuum...end. progress: 5.0 s, 3847.4 tps, lat 12.860 ms stddev 14.474, 0 failed progress: 10.0 s, 4149.0 tps, lat 12.051 ms stddev 12.893, 0 failed progress: 15.0 s, 3940.6 tps, lat 12.668 ms stddev 12.576, 0 failed progress: 20.0 s, 3500.0 tps, lat 14.300 ms stddev 16.424, 0 failed progress: 25.0 s, 4013.0 tps, lat 12.462 ms stddev 13.175, 0 failed progress: 30.0 s, 3437.4 tps, lat 14.539 ms stddev 25.607, 0 failed progress: 35.0 s, 4421.9 tps, lat 11.308 ms stddev 12.100, 0 failed progress: 40.0 s, 4485.0 tps, lat 11.140 ms stddev 12.031, 0 failed progress: 45.0 s, 4286.2 tps, lat 11.654 ms stddev 13.244, 0 failed progress: 50.0 s, 4008.6 tps, lat 12.476 ms stddev 13.586, 0 failed progress: 55.0 s, 4551.8 tps, lat 10.959 ms stddev 13.791, 0 failed progress: 60.0 s, 4356.2 tps, lat 11.505 ms stddev 15.813, 0 failed transaction type: <builtin: TPC-B (sort of)> scaling factor: 1 query mode: prepared number of clients: 50 number of threads: 1 maximum number of tries: 1 duration: 60 s number of transactions actually processed: 245035 number of failed transactions: 0 (0.000%) latency average = 12.234 ms latency stddev = 14.855 ms initial connection time = 38.862 ms tps = 4085.473436 (without initial connection time) statement latencies in milliseconds and failures: 0.000 0 \set aid random(1, 100000 * :scale) 0.000 0 \set bid random(1, 1 * :scale) 0.000 0 \set tid random(1, 10 * :scale) 0.000 0 \set delta random(-5000, 5000) 0.036 0 BEGIN; 0.058 0 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; 0.039 0 SELECT abalance FROM pgbench_accounts WHERE aid = :aid; 10.040 0 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; 1.817 0 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; 0.041 0 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); 0.202 0 END; CODE_BLOCK: franck.pachot % docker exec -it mpg \ pgbench -T 60 -P 5 -c 50 -r -U postgres -M prepared pgbench_postgres pgbench (18.1 (Debian 18.1-1.pgdg13+2)) starting vacuum...end. progress: 5.0 s, 3847.4 tps, lat 12.860 ms stddev 14.474, 0 failed progress: 10.0 s, 4149.0 tps, lat 12.051 ms stddev 12.893, 0 failed progress: 15.0 s, 3940.6 tps, lat 12.668 ms stddev 12.576, 0 failed progress: 20.0 s, 3500.0 tps, lat 14.300 ms stddev 16.424, 0 failed progress: 25.0 s, 4013.0 tps, lat 12.462 ms stddev 13.175, 0 failed progress: 30.0 s, 3437.4 tps, lat 14.539 ms stddev 25.607, 0 failed progress: 35.0 s, 4421.9 tps, lat 11.308 ms stddev 12.100, 0 failed progress: 40.0 s, 4485.0 tps, lat 11.140 ms stddev 12.031, 0 failed progress: 45.0 s, 4286.2 tps, lat 11.654 ms stddev 13.244, 0 failed progress: 50.0 s, 4008.6 tps, lat 12.476 ms stddev 13.586, 0 failed progress: 55.0 s, 4551.8 tps, lat 10.959 ms stddev 13.791, 0 failed progress: 60.0 s, 4356.2 tps, lat 11.505 ms stddev 15.813, 0 failed transaction type: <builtin: TPC-B (sort of)> scaling factor: 1 query mode: prepared number of clients: 50 number of threads: 1 maximum number of tries: 1 duration: 60 s number of transactions actually processed: 245035 number of failed transactions: 0 (0.000%) latency average = 12.234 ms latency stddev = 14.855 ms initial connection time = 38.862 ms tps = 4085.473436 (without initial connection time) statement latencies in milliseconds and failures: 0.000 0 \set aid random(1, 100000 * :scale) 0.000 0 \set bid random(1, 1 * :scale) 0.000 0 \set tid random(1, 10 * :scale) 0.000 0 \set delta random(-5000, 5000) 0.036 0 BEGIN; 0.058 0 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; 0.039 0 SELECT abalance FROM pgbench_accounts WHERE aid = :aid; 10.040 0 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; 1.817 0 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; 0.041 0 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); 0.202 0 END; CODE_BLOCK: franck.pachot % docker exec -it mpg \ pgbench -n -T 60 -P 5 -c 50 -r -U postgres -M prepared pgbench_mongo_fdw pgbench (18.1 (Debian 18.1-1.pgdg13+2)) progress: 5.0 s, 4752.1 tps, lat 10.379 ms stddev 4.488, 0 failed progress: 10.0 s, 4942.9 tps, lat 10.085 ms stddev 3.356, 0 failed progress: 15.0 s, 4841.7 tps, lat 10.292 ms stddev 2.256, 0 failed progress: 20.0 s, 4640.4 tps, lat 10.744 ms stddev 3.498, 0 failed progress: 25.0 s, 5011.3 tps, lat 9.943 ms stddev 1.724, 0 failed progress: 30.0 s, 4536.0 tps, lat 10.996 ms stddev 8.739, 0 failed progress: 35.0 s, 4862.1 tps, lat 10.248 ms stddev 2.062, 0 failed progress: 40.0 s, 5080.6 tps, lat 9.812 ms stddev 1.740, 0 failed progress: 45.0 s, 5238.3 tps, lat 9.513 ms stddev 1.673, 0 failed progress: 50.0 s, 4957.9 tps, lat 10.055 ms stddev 2.136, 0 failed progress: 55.0 s, 5184.8 tps, lat 9.608 ms stddev 1.550, 0 failed progress: 60.0 s, 4998.5 tps, lat 9.970 ms stddev 2.296, 0 failed transaction type: <builtin: TPC-B (sort of)> scaling factor: 1 query mode: prepared number of clients: 50 number of threads: 1 maximum number of tries: 1 duration: 60 s number of transactions actually processed: 295288 number of failed transactions: 0 (0.000%) latency average = 10.122 ms latency stddev = 3.487 ms initial connection time = 45.401 ms tps = 4921.889293 (without initial connection time) statement latencies in milliseconds and failures: 0.000 0 \set aid random(1, 100000 * :scale) 0.000 0 \set bid random(1, 1 * :scale) 0.000 0 \set tid random(1, 10 * :scale) 0.000 0 \set delta random(-5000, 5000) 0.121 0 BEGIN; 2.341 0 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; 0.339 0 SELECT abalance FROM pgbench_accounts WHERE aid = :aid; 2.328 0 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; 2.580 0 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; 2.287 0 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); 0.126 0 END; Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: franck.pachot % docker exec -it mpg \ pgbench -n -T 60 -P 5 -c 50 -r -U postgres -M prepared pgbench_mongo_fdw pgbench (18.1 (Debian 18.1-1.pgdg13+2)) progress: 5.0 s, 4752.1 tps, lat 10.379 ms stddev 4.488, 0 failed progress: 10.0 s, 4942.9 tps, lat 10.085 ms stddev 3.356, 0 failed progress: 15.0 s, 4841.7 tps, lat 10.292 ms stddev 2.256, 0 failed progress: 20.0 s, 4640.4 tps, lat 10.744 ms stddev 3.498, 0 failed progress: 25.0 s, 5011.3 tps, lat 9.943 ms stddev 1.724, 0 failed progress: 30.0 s, 4536.0 tps, lat 10.996 ms stddev 8.739, 0 failed progress: 35.0 s, 4862.1 tps, lat 10.248 ms stddev 2.062, 0 failed progress: 40.0 s, 5080.6 tps, lat 9.812 ms stddev 1.740, 0 failed progress: 45.0 s, 5238.3 tps, lat 9.513 ms stddev 1.673, 0 failed progress: 50.0 s, 4957.9 tps, lat 10.055 ms stddev 2.136, 0 failed progress: 55.0 s, 5184.8 tps, lat 9.608 ms stddev 1.550, 0 failed progress: 60.0 s, 4998.5 tps, lat 9.970 ms stddev 2.296, 0 failed transaction type: <builtin: TPC-B (sort of)> scaling factor: 1 query mode: prepared number of clients: 50 number of threads: 1 maximum number of tries: 1 duration: 60 s number of transactions actually processed: 295288 number of failed transactions: 0 (0.000%) latency average = 10.122 ms latency stddev = 3.487 ms initial connection time = 45.401 ms tps = 4921.889293 (without initial connection time) statement latencies in milliseconds and failures: 0.000 0 \set aid random(1, 100000 * :scale) 0.000 0 \set bid random(1, 1 * :scale) 0.000 0 \set tid random(1, 10 * :scale) 0.000 0 \set delta random(-5000, 5000) 0.121 0 BEGIN; 2.341 0 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; 0.339 0 SELECT abalance FROM pgbench_accounts WHERE aid = :aid; 2.328 0 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; 2.580 0 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; 2.287 0 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); 0.126 0 END; CODE_BLOCK: franck.pachot % docker exec -it mpg \ pgbench -n -T 60 -P 5 -c 50 -r -U postgres -M prepared pgbench_mongo_fdw pgbench (18.1 (Debian 18.1-1.pgdg13+2)) progress: 5.0 s, 4752.1 tps, lat 10.379 ms stddev 4.488, 0 failed progress: 10.0 s, 4942.9 tps, lat 10.085 ms stddev 3.356, 0 failed progress: 15.0 s, 4841.7 tps, lat 10.292 ms stddev 2.256, 0 failed progress: 20.0 s, 4640.4 tps, lat 10.744 ms stddev 3.498, 0 failed progress: 25.0 s, 5011.3 tps, lat 9.943 ms stddev 1.724, 0 failed progress: 30.0 s, 4536.0 tps, lat 10.996 ms stddev 8.739, 0 failed progress: 35.0 s, 4862.1 tps, lat 10.248 ms stddev 2.062, 0 failed progress: 40.0 s, 5080.6 tps, lat 9.812 ms stddev 1.740, 0 failed progress: 45.0 s, 5238.3 tps, lat 9.513 ms stddev 1.673, 0 failed progress: 50.0 s, 4957.9 tps, lat 10.055 ms stddev 2.136, 0 failed progress: 55.0 s, 5184.8 tps, lat 9.608 ms stddev 1.550, 0 failed progress: 60.0 s, 4998.5 tps, lat 9.970 ms stddev 2.296, 0 failed transaction type: <builtin: TPC-B (sort of)> scaling factor: 1 query mode: prepared number of clients: 50 number of threads: 1 maximum number of tries: 1 duration: 60 s number of transactions actually processed: 295288 number of failed transactions: 0 (0.000%) latency average = 10.122 ms latency stddev = 3.487 ms initial connection time = 45.401 ms tps = 4921.889293 (without initial connection time) statement latencies in milliseconds and failures: 0.000 0 \set aid random(1, 100000 * :scale) 0.000 0 \set bid random(1, 1 * :scale) 0.000 0 \set tid random(1, 10 * :scale) 0.000 0 \set delta random(-5000, 5000) 0.121 0 BEGIN; 2.341 0 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; 0.339 0 SELECT abalance FROM pgbench_accounts WHERE aid = :aid; 2.328 0 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; 2.580 0 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; 2.287 0 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); 0.126 0 END; - PostgreSQL tables created with pgbench -i, and benchmark run with pgbench -T 60 -c 50 - PostgreSQL foreign tables storing their rows into MongoDB collections, though the MongoDB Foreign Data Wrapper, and the same pgbench command with -n as there's nothing to VACUUM on MongoDB.