feature-request: postgres storage support

[c-nv-s]

please add postgres support to the storage engine options

[peakji]

We are currently implementing a in-memory storage and will work on adding relational database support when it is stable.

[peakji]

Update: the in-memory store is available in v0.3.0, and now we have a roadmap for adding more storage engines.

[zeina1i]

Hi @peakji
Are you going to define tasks for the roadmap items, and if we are interested,could we be involved?
Thanks

[peakji]

@zeina1i The roadmap is just a rough plan, and contributions of any kind are always welcome!

In fact, I don't have much experience with Postgres, so I haven't started working on the relevant parts yet. If you are interested in implementing support for a new storage engine, you can refer to the engine package. And if the test cases in testsuite all pass, then it should work properly.

[c-nv-s]

@zeina1i did you decide to have a look?

[zeina1i]

@c-nv-s unfortunately currently I don't have enough time to be involved.

[c-nv-s]

not sure why I didn't think of this earlier, but I'll see if I can test this with https://github.com/FerretDB/FerretDB sometime next week.
Hopefully it will serve as a stop-gap until the final implementation.

[c-nv-s]

just to update on this...
unfortunately not all features worked because it looks like the ferretdb team haven't yet implemented how to handle the hint query functionality from mongodb yet
here is the docker-compose.yml file I used:

services:
  postgres:
    image: postgres
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=password
      - POSTGRES_DB=ferretdb
    volumes:
      - ./data:/var/lib/postgresql/data

  ferretdb:
    image: ghcr.io/ferretdb/ferretdb
    ports:
      - 27017:27017
    environment:
      - FERRETDB_POSTGRESQL_URL=postgres://postgres:password@postgres:5432/ferretdb

  ratus:
    image: hyperonym/ratus:0.6.2
    restart: unless-stopped
    depends_on:
      - ferretdb
    environment:
      ENGINE: "mongodb"
      MONGODB_URI: "mongodb://ferretdb:27017"
      MONGODB_DATABASE: "ferretdb"
      MONGODB_COLLECTION: "tasks"
      MONGODB_RETENTION_PERIOD: "72h"
    ports:
      - "8000:80"

networks:
  default:
    name: ferretdb

and you have to remember to add the ferretdb database to your search_path when in psql
I did this using: ALTER ROLE postgres SET search_path = "$user", public, ferretdb ;

[c-nv-s]

still hoping this feature can be progressed...
the intended workaround using ferretdb seems dependent on them developing the TimeToLive functionality but that ticket seems low priority at the moment, so integrating the feature directly in ratus still seems like the best approach at the moment

[AlekSi]

All major compatibility issues with FerretDB v2 (including TTL indexes) should be resolved now: FerretDB/FerretDB#2256.
One thing that does not work now is that Ratus sets hints for many queries, and FerretDB/DocumentDB does not support them yet. I briefly checked the code, and I don't think there is a real need to set them. Could there be an option to avoid doing that?

[c-nv-s]

@peakji what do you think of AlekSi's feedback?