DEV Community

Franck Pachot for YugabyteDB Distributed PostgreSQL Database

Posted on • Updated on

Open-source🍃MongoDB API to 🚀YugabyteDB with 🥭MangoDB proxy

Please note that MangoDB is now called FerretDB and has moved to:

There is no doubt that one great thing about MongoDB is the API. Many developers loves it. I'm a big fan of SQL, but we need to listen to all users, and they have use-cases for it. Another thing we expect from NoSQL is the scalability.

In this example we have both, open-source, ACID and resilient, with

  • MangoDB proxy between MongoDB and PostgreSQL protocols
  • YugabyteDB with its PostgreSQL compatible API on top of the fully consistent distributed storage

This MangoDB project is new, and when looking for it you will see Google still trying to tell you that you made a typo. So this post is a first quick test to validate how it works. Things will probably change with contributions to https://github.com/MangoDB-io/MangoDB

MangoDB example

I'll take de demo application from:

git clone https://github.com/MangoDB-io/example
cd example
Enter fullscreen mode Exit fullscreen mode

and install it on my laptop because I became recently a big fan of Docker on Windows.

The docker-compose.yml starts a PostgreSQL database and I'll replace that with a YugabyteDB one. This is easy.

postgres service

First, I remove the postgres service. And add the yb-master and yb-tserver ones from YugabyteDB docker-compose.yml

I didn't change any parameters, and I keep the defaults:

  • host name is yb-tserver (in a distributed database you can connect to any server)
  • port is 5433 (this is our default, rather than the 54322 default for PostgreSQL)
  • user is yugabyte (and password is the same)
  • database is yugabyte (you can create a dedicated one of course)

setup service

I change this in the setup service command, which starts just to create the schema for the application (which is a "todo" list in this example):

psql -h yb-tserver -p 5433 -U yugabyte -d yugabyte -c 'CREATE SCHEMA IF NOT EXISTS todo'
Enter fullscreen mode Exit fullscreen mode

and the docker-compose dependecy is set to yb-tserver instead of postgres

mangodb service

The application also needs the connection string. We use the PostgreSQL driver as it is the same protocol, and change the host, port and database name only:

    depends_on:
      - 'postgres'
    entrypoint: ["sh", "-c", "psql -h postgres -U postgres -d mangodb -c 'CREATE SCHEMA IF NOT EXISTS todo'"]
Enter fullscreen mode Exit fullscreen mode

Start the application

Here is my final docker-compose.yml:

version: "3"

volumes:
  yb-master-data-1:
  yb-tserver-data-1:

services:
  client:
    build: ./app/client
    hostname: 'todo_client'
    container_name: 'todo_client'
    stdin_open: true
  api:
    build: ./app/api
    hostname: 'todo_api'
    container_name: 'todo_api'
  nginx:
    image: nginx
    hostname: 'nginx'
    container_name: 'nginx'
    ports:
      - 8888:8888
    volumes:
      - ./nginx.conf:/etc/nginx/conf.d/default.conf
  yb-master:
      image: yugabytedb/yugabyte:latest
      container_name: yb-master-n1
      volumes:
      - yb-master-data-1:/mnt/master
      command: [ "/home/yugabyte/bin/yb-master",
                "--fs_data_dirs=/mnt/master",
                "--master_addresses=yb-master-n1:7100",
                "--rpc_bind_addresses=yb-master-n1:7100",
                "--replication_factor=1"]
      ports:
      - "7000:7000"
      environment:
        SERVICE_7000_NAME: yb-master
  yb-tserver:
      image: yugabytedb/yugabyte:latest
      container_name: yb-tserver-n1
      volumes:
      - yb-tserver-data-1:/mnt/tserver
      command: [ "/home/yugabyte/bin/yb-tserver",
                "--fs_data_dirs=/mnt/tserver",
                "--start_pgsql_proxy",
                "--rpc_bind_addresses=yb-tserver-n1:9100",
                "--tserver_master_addrs=yb-master-n1:7100"]
      ports:
      - "9042:9042"
      - "5433:5433"
      - "9000:9000"
      environment:
        SERVICE_5433_NAME: ysql
        SERVICE_9042_NAME: ycql
        SERVICE_6379_NAME: yedis
        SERVICE_9000_NAME: yb-tserver
      depends_on:
      - yb-master
  mangodb:
    image: ghcr.io/mangodb-io/mangodb:latest
    hostname: 'mangodb'
    container_name: 'mangodb'
    command:
      [
        '-listen-addr=:27017',
        '-postgresql-url=postgres://yugabyte@yb-tserver:5433/yugabyte',
      ]
    ports:
      - 27017:27017
  setup:
    image: postgres:14.0
    hostname: 'setup'
    container_name: 'setup'
    restart: 'on-failure'
    depends_on:
      - 'yb-tserver'
    entrypoint: ["sh", "-c", "psql -h yb-tserver -p 5433 -U yugabyte -d yugabyte -c 'CREATE SCHEMA IF NOT EXISTS todo'"]

Enter fullscreen mode Exit fullscreen mode

I pull the images, create the containers and run the services:

docker-compose up
Enter fullscreen mode Exit fullscreen mode

Here is the start from command line:
Image description
Visible in Docker Desktop:
Image description

The YugabyteDB console is available on: http://localhost:7000

The example application:

The application is accessible on http://localhost:8888/ and we can add items to the To-Do list:
Image description
This calls the db.collection.insertOne() MongoDB function:

Image description

Check the database

This MongoDB call is translated to SQL by the MangoDB proxy. The collection is a table:

$ psql postgres://yugabyte:yugabyte@localhost:5433/yugabyte
psql (12.7, server 11.2-YB-2.9.0.0-b0)
Type "help" for help.

yugabyte=# \dn

  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
 todo   | yugabyte
(2 rows)

yugabyte=# set schema 'todo';
SET

yugabyte=# \d
         List of relations
 Schema | Name  | Type  |  Owner
--------+-------+-------+----------
 todo   | tasks | table | yugabyte
(1 row)

yugabyte=# \d+ todo.tasks
                                   Table "todo.tasks"
 Column | Type  | Collation | Nullable | Default | Storage  | Stats target | Description
--------+-------+-----------+----------+---------+----------+--------------+-------------
 _jsonb | jsonb |           |          |         | extended |              |

yugabyte=# select * from todo.tasks;
                                                                    _jsonb
-----------------------------------------------------------------------------------------------------------------------------------------------
 {"$k": ["description", "completed", "_id"], "_id": {"$o": "6182627a17462641b80439d4"}, "completed": false, "description": "Play 😎"}
 {"$k": ["description", "completed", "_id"], "_id": {"$o": "6182627017462641b80439d3"}, "completed": false, "description": "Start MangoDB"}
 {"$k": ["description", "completed", "_id"], "_id": {"$o": "6182626817462641b80439d2"}, "completed": false, "description": "Start YugabyteDB"}
(3 rows)

yugabyte=#

Enter fullscreen mode Exit fullscreen mode

The storage is very simple: one table with one JSONB column.

SQL Statements

I'll track the statements used with the pg_stat_statements extension which is enabled by default in YugabyteDB. Just resetting in my lab:

yugabyte=# select pg_stat_statements_reset();
 pg_stat_statements_reset
--------------------------

(1 row)
Enter fullscreen mode Exit fullscreen mode

In the application I refresh, mark "Play" as completed, Insert a new task, delete it, and refresh multiple times.

yugabyte=# select calls,query from pg_stat_statements;
 calls |                                                        query
-------+----------------------------------------------------------------------------------------------------------------------
     1 | select query from pg_stat_statements
     1 | INSERT INTO "todo"."tasks" (_jsonb) VALUES ($1)
     1 | DELETE FROM "todo"."tasks" WHERE _jsonb->$1 = $2
     1 | SELECT _jsonb FROM "todo"."tasks" WHERE _jsonb->$1 = $2
     1 | UPDATE "todo"."tasks" SET _jsonb = $1 WHERE _jsonb->'_id' = $2
     7 | SELECT _jsonb FROM "todo"."tasks"
     1 | select pg_stat_statements_reset()
    11 | SELECT COUNT(*) > 0 FROM information_schema.columns WHERE column_name = $1 AND table_schema = $2 AND table_name = $3
    11 | SELECT COUNT(*) > 0 FROM information_schema.tables WHERE table_schema = $1 AND table_name = $2
     1 | select * from pg_stat_statements
(10 rows)
Enter fullscreen mode Exit fullscreen mode

There are many things to optimize here. Reading the information_schema many time is not the most efficient. We need an index on the ID (which is in the JSON document). And updates are re-writing the whole document. I'll think about this and probably contribute to this open-source project. Probably the ID should be in another column, that we can properly index and shard, rather than scanning the whole table or adding an additional index.

JSONB indexing

As YugabyteDB plugs the distributed storage to a full PostgreSQL query layer, we can even index this Here is the table with just one JSONB (it was created by with CREATE TABLE "todo"."tasks" (_jsonb jsonb);):

yugabyte=# select * from todo.tasks;
                                                              _jsonb
----------------------------------------------------------------------------------------------------------------------------------
 {"$k": ["description", "completed", "_id"], "_id": {"$o": "618282aea9a2a141efa3c401"}, "completed": false, "description": "Franck"}
(1 row)
Enter fullscreen mode Exit fullscreen mode

If I select one key, it has to scan the whole table:

yugabyte=# explain analyze select * from todo.tasks where _jsonb->>'_id' = '{"$o": "618282aea9a2a141efa3c401"}';
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Seq Scan on tasks  (cost=0.00..105.00 rows=1000 width=32) (actual time=0.815..0.817 rows=1 loops=1)
   Filter: ((_jsonb ->> '_id'::text) = '{"$o": "618282aea9a2a141efa3c401"}'::text)
 Planning Time: 0.048 ms
 Execution Time: 0.874 ms
(4 rows)
Enter fullscreen mode Exit fullscreen mode

But I can create a unique index on it:

yugabyte=# create unique index task_pk ON todo.tasks
           ((_jsonb ->> '_id'::text) hash);
CREATE INDEX
Enter fullscreen mode Exit fullscreen mode

Do not forget the double parenthesis (this is the PostgreSQL syntax):

  • one for the list of columns to index,
  • and one because it is not directly a column but a value derived from the JSON document.

The HASH modifier is optional here because hash sharding is the default on the first column. And this is what we want on this generated identifier. But if you have range scans, you could change it to ASC or DESC.

Now I have a fast access to the document:

yugabyte=# explain analyze select * from todo.tasks where _jsonb->>'_id' = '{"$o": "618282aea9a2a141efa3c401"}';

                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Index Scan using task_pk on tasks  (cost=0.00..4.12 rows=1 width=32) (actual time=13.617..13.622 rows=1 loops=1)
   Index Cond: ((_jsonb ->> '_id'::text) = '{"$o": "618282aea9a2a141efa3c401"}'::text)
 Planning Time: 11.593 ms
 Execution Time: 13.706 ms
(4 rows)
Enter fullscreen mode Exit fullscreen mode

This means that a query with the key will go to the right tablet (the tables and indexes are automatically sharded in YugabyteDB) and to the right row. We are ready to scale out and keep the low latency.

Index Only Indexes

The previous execution plan may require two RPC on a scale-out database: one to the index and one to the table. Because, for better agility, all indexes are global in YugabyteDB. And, of course, with no compromise on strong consistency. But an Index Only Scan would be better. It is easy to acheive (I explained in How a Distributed SQL Database Boosts Secondary Index Queries with Index Only Scan):

yugabyte=# create unique index task_pk ON todo.tasks
           ((_jsonb ->> '_id'::text) hash) include (_jsonb);
CREATE INDEX
Enter fullscreen mode Exit fullscreen mode

And here is the fastest access you can have to a document on a SQL distributed database, still with the full agility of a JSON document:

yugabyte=# explain analyze select * from todo.tasks where _jsonb->>'_id' = '{"$o": "618282aea9a2a141efa3c401"}';
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Index Only Scan using task_pk on tasks  (cost=0.00..4.12 rows=1 width=32) (actual time=2.559..2.561 rows=1 loops=1)
   Index Cond: (((_jsonb ->> '_id'::text)) = '{"$o": "618282aea9a2a141efa3c401"}'::text)
   Heap Fetches: 0
 Planning Time: 5.229 ms
 Execution Time: 2.607 ms
(5 rows)
Enter fullscreen mode Exit fullscreen mode

Now you have all in the index and don't need the table at all. In PostgreSQL you have no choice as you need to maintain the heap table. But YugabyteDB stores tables in LSM trees where rows are organized for fast access on the primary key. When storing documents into a SQL table, it is better to have the identifier in its own column, an integer or uuid, to really have a (key uuid, value jsonb) schema. I'll suggest that to the MangoDB project, as well as some other optimizations for PostgreSQL or YugabyteDB. But the essence is there: a simple MongoDB API to distributed SQL database.

Top comments (0)