PostgreSQL is extensible, allowing it to serve as a multi-model or converged database with built-in data types and indexes. If you're interested in exploring its features, compiling and installing all extensions can be quite a bit of work, but having an image packed with extensions makes things much easier. For this, I recommend using https://pglayers.github.io/ (thanks to Ismael Mejía).
Start pglayers-full in Docker
I began by using the ghcr.io/pglayers/pglayers-full:17 image to start the container. Not only did I get all the extensions installed, but it also ships the MongoDB-compatible endpoint offered by DocumentDB:
# remove previous container with same name ⚠️
docker rm -f pg-documentdb
# install DocumentDB extension before it can open the MongoDB-compatible endpoint
echo 'CREATE EXTENSION IF NOT EXISTS documentdb_core CASCADE;
CREATE EXTENSION IF NOT EXISTS documentdb CASCADE;' > /tmp/01-documentdb.sql
# start the container, exposes the PostgreSQL and MongoDB-compatible ports n host, set
docker run -d --name pg-documentdb \
-p 5432:5432 -p 10260:10260 \
-e POSTGRES_PASSWORD=xxxMongoDBxxx\
-v /tmp/01-documentdb.sql:/docker-entrypoint-initdb.d/01-documentdb.sql:ro \
ghcr.io/pglayers/pglayers-full:17 \
-c max_worker_processes=64 \
-c max_connections=200 \
-c documentdb.pg_gw_username=postgres \
-c documentdb.pg_gw_password=xxxMongoDBxxx\
-c cron.database_name=postgres
I waited for the PostgreSQL endpoint to be up:
echo -n "Waiting to get PostgreSQL endpoint up on 5432"
until docker exec pg-documentdb pg_isready -p 5432 2>/dev/null |
grep "5432 - accepting connections"
do echo -n . ; sleep 1 ; done
I listed the available extensions:
docker exec -i pg-documentdb psql -U postgres -t -c "select 'Available extensions: '||string_agg(name,', ') from pg_available_extensions;"
I could already connect to the PostgreSQL endpoint and use those extensions. As I also wanted to use the MongoDB-compatible API, I downloaded the MongoDB image that contains the client (MongoSH):
docker pull mongo
I waited for the MongoDB endpoint to be up:
echo -n "Waiting to get MongoDB emulation up on 10260"
until docker logs pg-documentdb 2>/dev/null |
grep "bound to port 10260"
do echo -n . ; sleep 1 ; done
echo "Ready."
I can connect with MongoSH simply by linking the PostgreSQL container when starting the MongoDB client:
docker run -it --rm --link pg-documentdb:pg mongo \
mongosh "mongodb://postgres:xxxMongoDBxxx@pg:10260/?tls=true&tlsAllowInvalidCertificates=true"
Now I define aliases to connect to PostgreSQL via the two endpoints, with psql and mongosh:
alias p='docker exec -it pg-documentdb psql -U postgres'
alias m='docker run --rm -it --link pg-documentdb:pg mongo mongosh "mongodb://postgres:xxxMongoDBxxx@pg:10260/?tls=true&tlsInsecure=true"'
Ready to explore all extensions. I'll go further with DocumentDB.
Import MongoDB Sample Dataset to PostgreSQL DocumentDB
I tested with some data using the MongoDB client image to access the Sample Dataset and imported it into PostgreSQL via the DocumentDB endpoint with mongoimport:
# ⬇️ using --link to quickly connect to the other container
docker run --rm -i --link pg-documentdb:pg mongo bash <<'SH'
# get wget
apt update -qqy
apt install wget -qy
# get sample data
wget -q -c https://atlas-education.s3.amazonaws.com/sampledata.archive
# restore sample data
mongorestore -j 5 --drop --uri "mongodb://postgres:xxxMongoDBxxx@pg:10260/?tls=true&tlsInsecure=true" --archive=sampledata.archive
# create the index that failed because of "textIndexVersion": 3
mongosh "mongodb://postgres:xxxMongoDBxxx@pg:10260/sample_mflix?tls=true&tlsInsecure=true" --eval '
SH
...
One index creation failed because it sets textIndexVersion 2. No worries, we will create the same index later, just without mentioning the version, which is specific to vanilla MongoDB.
Look at MongoDB-compatible and DocumentDB execution plans
I can connect to the MongoDB API, create a text index on the movies collection, and execute a query just like I would in MongoDB. I used the m alias defined above, but you can connect with any MongoDB client.
Current Mongosh Log ID: 6a50e7576d644ce7f3c3a7d7
Connecting to: mongodb://<credentials>@pg:10260/?tls=true&tlsInsecure=true&directConnection=true&appName=mongosh+2.9.2
Using MongoDB: 7.0.0
Using Mongosh: 2.9.2
For mongosh info see: https://www.mongodb.com/docs/mongodb-shell/
To help improve our products, anonymous usage data is collected and sent to MongoDB periodically (https://www.mongodb.com/legal/privacy-policy).
You can opt-out by running the disableTelemetry() command.
[direct: mongos] test> disableTelemetry()
Telemetry is now disabled.
[direct: mongos] test> use sample_mflix;
switched to db sample_mflix
[direct: mongos] sample_mflix> show collections
comments
embedded_movies
movies
sessions
theaters
users
[direct: mongos] sample_mflix> db.movies.createIndex({
"cast": "text",
"fullplot": "text",
"genres": "text",
"title": "text"
}, {
"name": "cast_text_fullplot_text_genres_text_title_text",
"weights": { "cast": 1, "fullplot": 1, "genres": 1, "title": 1 },
"default_language": "english",
"language_override": "language"
}
)
cast_text_fullplot_text_genres_text_title_text
[direct: mongos] sample_mflix> db.movies.find(
{
$text: {
$search: "\"star wars\""
}
}
).explain("executionStats").executionStats
;
{
nReturned: Long('13'),
executionTimeMillis: 137.596,
executionStartAtTimeMillis: 135.295,
totalDocsExamined: Long('13'),
totalKeysExamined: Long('13'),
executionStages: {
stage: 'FETCH',
nReturned: Long('13'),
executionTimeMillis: 137.596,
executionStartAtTimeMillis: 135.295,
totalDocsExamined: 13,
totalKeysExamined: 13,
numBlocksFromCache: 73,
inputStage: {
stage: 'FETCH',
nReturned: Long('13'),
executionTimeMillis: 137.57,
executionStartAtTimeMillis: 135.292,
totalDocsExamined: 13,
totalKeysExamined: 13,
exactBlocksRead: 12,
numBlocksFromCache: 73,
inputStage: {
stage: 'IXSCAN',
nReturned: Long('13'),
executionTimeMillis: 0.831,
executionStartAtTimeMillis: 0.831,
indexName: 'cast_text_fullplot_text_genres_text_title_text',
totalKeysExamined: 13,
numBlocksFromCache: 10
}
}
}
}
I've shown the execution plan to verify that the MongoDB-compatible text index was utilized.
Here is the plan displayed by the DocumentDB for VS Code extension:
I can also switch to the SQL API, using the p alias defined above, and run the same query to view the PostgreSQL execution plan for it:
psql (17.10 (Debian 17.10-1.pgdg13+1))
Type "help" for help.
postgres=# \pset pager off
postgres=# EXPLAIN (ANALYZE, BUFFERS, VERBOSE, COSTS OFF)
SELECT document FROM documentdb_api_catalog.bson_aggregation_find(
'sample_mflix',
'{
"find":"movies",
"filter":{
"$text":{
"$search":"\"star wars\""
}
}
}'::documentdb_core.bson
);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Custom Scan (DocumentDBApiQueryScan) (actual time=36.533..38.597 rows=13 loops=1)
Output: document
Buffers: shared hit=34
-> Bitmap Heap Scan on documentdb_data.documents_112 collection (actual time=36.530..38.587 rows=13 loops=1)
Output: document
Filter: documentdb_api_internal.bson_text_meta_qual(collection.document, '''star'' <-> ''war'''::tsquery, '\x0400000000000000ffffffff000000000000000000000000000000007b0000002c0000007f000000040000000000803f000000000000803f000000000000803f000000000000803f000000000400000063617374000800000066756c6c706c6f74000600000067656e72657300050000007469746c6500b83300006c616e677561676500'::bytea, true)
Heap Blocks: exact=12
Buffers: shared hit=34
-> Bitmap Index Scan on cast_text_fullplot_text_genres_text_title_text (actual time=1.208..1.209 rows=13 loops=1)
Index Cond: (collection.document OPERATOR(documentdb_api_catalog.@#%) '''star'' <-> ''war'''::tsquery)
Buffers: shared hit=10
Planning:
Buffers: shared hit=1
Planning Time: 0.340 ms
Execution Time: 39.203 ms
(15 rows)
postgres=# \d documentdb_data.documents_112
Table "documentdb_data.documents_112"
Column | Type | Collation | Nullable | Default
-----------------+----------------------+-----------+----------+---------
shard_key_value | bigint | | not null |
object_id | documentdb_core.bson | | not null |
document | documentdb_core.bson | | not null |
Indexes:
"collection_pk_112" PRIMARY KEY, btree (shard_key_value, object_id)
"documents_rum_index_152" documentdb_rum (document documentdb_api_catalog.bson_rum_text_path_ops (weights='{ "cast" : 1.0, "fullplot" : 1.0, "genres" : 1.0, "title" : 1.0 }', defaultlanguage=english, languageoverride=language))
Check constraints:
"shard_key_value_check" CHECK (shard_key_value = '112'::bigint)
Both execution plans indicate that the text index was used to directly retrieve 13 keys (totalKeysExamined: 13 in the MongoDB-compatible plan and rows=13 in the PostgreSQL plan) from 10 index pages (numBlocksFromCache: 10, Buffers: shared hit=10) and then fetch the corresponding documents for the results.
Here is the plan dusplayed by the PostgreSQL for VS Code extension:
The MongoDB text index is implemented in PostgreSQL as a RUM index through the DocumentDB extension. This highlights PostgreSQL’s robust open-source ecosystem and community. Vanilla PostgreSQL offers a permissive license and supports extensions for additional data types, such as BSON. PostgresPro improved text search with RUM indexes that include scoring. Microsoft also added support for BSON text search with RUM. Ismael developed Docker images containing all necessary extensions. As a result, we can now easily connect and utilize this unified multi-model database.
The experimentation is limitless. With this pglayers-full image, you can use plenty of extensions (some were already enabled by DocumentDB):
postgres=# CREATE EXTENSION IF NOT EXISTS documentdb;
NOTICE: extension "documentdb" already exists, skipping
CREATE EXTENSION
postgres=# CREATE EXTENSION IF NOT EXISTS vector;
NOTICE: extension "vector" already exists, skipping
CREATE EXTENSION
postgres=# CREATE EXTENSION IF NOT EXISTS postgis;
NOTICE: extension "postgis" already exists, skipping
CREATE EXTENSION
postgres=# CREATE EXTENSION IF NOT EXISTS timescaledb;
ERROR: function "time_bucket" already exists with same argument types
postgres=# CREATE EXTENSION IF NOT EXISTS pg_textsearch;
CREATE EXTENSION
postgres=#
postgres=# CREATE EXTENSION IF NOT EXISTS pg_graphql;
CREATE EXTENSION
postgres=# CREATE EXTENSION IF NOT EXISTS orafce;
CREATE EXTENSION
postgres=# CREATE EXTENSION IF NOT EXISTS pg_duckdb;
CREATE EXTENSION
I selected DocumentDB for this demonstration because it offers numerous advantages as a document database. The DocumentDB extension for PostgreSQL provides a fully open-source engine for MongoDB applications—both the MongoDB client and PostgreSQL server are OSS, as is the DocumentDB extension, which is part of the Linux Foundation.
Multi-model emulations
Although other converged databases and MongoDB emulations exist, they typically lack the same level of flexibility and features. For example, the index shown here, combining document and full-text search capabilities, is not available in Oracle's MongoDB emulation. Instead of native support, it converts the emulation to built-in features, which may not support identical properties:
I haven't chosen a specific case here. I simply generated the index from the Sample Dataset supplied by MongoDB's beginner courses.
By the way, DocumentDB on PostgreSQL can even run faster that vanilla MongoDB on such case. If you run the same on Atlas, you will see that first retrieves postings for the individual words, with two index scans, merges them, fetches the documents, and only afterwards verifies that the phrase exists:
To compare with PostgreSQL RUM indexes in DocumentDB, you should use MongoDB Atlas Search Indexes (Lucene indexes maintained by eventually consistent change stream) rather than legacy $text index.
Native extensibility vs. bolt-on multi-model
Next time you hear about multi-model, converged databases, or native APIs, remember that PostgreSQL was built by Michael Stonebraker before those marketing labels (THE DESIGN OF POSTGRES - 1986). It was specifically designed to take a relational foundation and make it extensible enough to natively support complex objects and abstract data types. Over time, SQL databases experimented with ORDBMS, and NoSQL databases promoted multi-model approaches, which became quite popular. RDBMS vendors responded by adding different API layers on top of their SQL schemas, calling it converged. Meanwhile, the PostgreSQL ecosystem continued to innovate by integrating native data types and access methods through its very flexible, extensible architecture. This extension framework allows developers to plug in new data types and index types as native extensions rather than transformations in the query layer. It's highly compatible with Docker image layers, and projects like pglayers.github.io use it to improve the developer experience.
Top comments (0)