Combining postgresql and neo4j

There’s no simple way to have transactional behaviour across two database. You could look into implementing patterns such as the saga pattern but this is a lot of work and easy to get wrong.

The more straightforward solution may be to manually write some undo logic for the Neo4j database that runs after a postgresql transaction fails, but this will be error prone and imperfect.

I would be tempted to remove the Neo4j database if possible and move the graph queries into postgresql using recursive queries.

The reason we’re using Neo4j is to handle complex relationships. For example, we have containers of widgets, that can be connected to other containers. Containers can also subscribe to widgets of other containers.
Another reason is that access permissions are not necessarily granted directly (as in user has permissions on container), but by checking if the user has permissions somewhere in the hierarchy. E.g. user A is an admin in organization B, which has a group C with a container D. Simply by being an admin in B, user A should have permissions on container D.

Would you still opt to model it in postgres, despite there being a potential for many levels of joins?

I would build the system using the existing postgresql database and recursive queries, and then benchmark the system with generated production-like data to measure performance to see if an alternative approach is needed.

If the performance of postgresql is sufficient (in my case it was) I would go that way as (in my opinion) if offers a better operational and development experience while offering better durability and data integrity guarantees. In addition use of single database means we can use database transactions

If I were to use both I would likely write to Postgres in the transaction (as the primary datastore and source of truth) and then replicate a subset of that information to Neo4j for faster querying with eventual consistency.

I ran into this problem before with an app I’ve worked for a while, as @lpil mentioned there’s no simple way to have two transactional behaviours, depending on the app using postgres only is the right solution, but I’ve work with graphs and I know there’s way too many benefits over a relational one on some use cases

In this case I’d recommend you look into another solutions that guarantee consistency over the whole single database while maintaining the graph data structure

GitHub - skaiworldwide-oss/agensgraph: AgensGraph, a transactional graph database based on PostgreSQL · GitHub a graph database on top of postgres, you’ve both sql and graph queries available and you can combine them as well, the problem is documentation, is hard to get started

https://dgraph.io/ it’s a distributed and acid complaint database, it’s supposed to be the same as postgres consistency while being graph and distributed, tho in my time I was using it the dev team was quite “problematic” and didn’t focus well on what they want to do, so I stop using it, I don’t know the actual status of the dev team right now but seems like everything is going fine now

GitHub - cayleygraph/cayley: An open-source graph database · GitHub this is more like a plugin for many databases, it works for postgres too so you can do the same as agens graph, documentation is a problem as well

In my case I just end up using https://www.foundationdb.org/ and creating a graph like layer myself (GitHub - OkamiIO/Nomure: A proof-of-concept graph Database on top of FoundationDB · GitHub , I’ve another version but is not released yet, once is finished I’ll share it for the moment the code you saw there is not the best),

There’s another graph layer for fdb like GitHub - JanusGraph/janusgraph-foundationdb: FoundationDB storage adapter for JanusGraph · GitHub tho is not complete afaik. The folks at Ebay made a new layer on top of janusgraph and fdb but they have not released it (yet?), for more info check here FoundationDB Summit 2019: NuGraph: GraphDB as a Cloud Service Built Upon JanusGraph and FoundationDB - Community - FoundationDB

Using only ecto, it’s not possible to manage a transaction on both postgres and neo4j at the same time.
To do so use existing pattern like saga as @lpil said or implement your own system: having a transaction on top on the two other, use events to do / undo operation, having ‘Temp’ label in Neo4j, etc.

Well, everyone seems to show the way to “put all your data in postgres”. But why not the opposite? Is having all the data is Neo4j so horrible? (Well, if you plan to do mostly statistics, yes it’s awful…)
What offers postgres that couldn’t be done by Neo4j? (except that they are not designed for the same purpose )
And keep in mind that if you don’t do “heavy” traversal queries, you probably don’t need Neo4j. If all your requests can be express with Ecto.Query, then you don’t need Neo4j.

And this:

If I were to use both I would likely write to Postgres in the transaction (as the primary datastore and source of truth) and then replicate a subset of that information to Neo4j for faster querying with eventual consistency.

works very well.
But it depends on wether you need to serve ultra fresh data or not.
Again, trade-off…

I’m going to be contrarian and suggest that yes, this is possible!

You need to use a 2-step transaction in postgres (PostgreSQL: Documentation: 9.3: PREPARE TRANSACTION)

Basically it goes like this:

postgres: BEGIN TRANSACTION
neo4j: BEGIN TRANSACTION

...

postgres: PREPARE TRANSACTION
if failed, neo4j: ROLLBACK
neo4j: COMMIT
if failed: postgres ROLLBACK PREPARED
postgres: postgres COMMIT PREPARED

the key being the PREPARE feature in postgres. This allows you to almost commit a transaction, with the possibility of rolling it back. The transaction is pretty much committed at that point, and any database checks such as constraints etc. have passed. Then you can commit neo4j knowing the postgres data is safe, and then finally finish up the postgres commit by telling postgres that it’s okay to finalize that transaction.

I’m not sure what the performance implications are but this is the safest way to do it.

Edit: It looks like postgrex doesn’t have native support for this but I feel like it would probably not be that hard to either:

1. make a PR To support it
2. hack it into your codebase in some way

Well if there is a neo4j FDW plugin for postgresql, then postgresql could do the cross database transaction as well as any is possible.

FDW plugins let you use another database from inside pgsql itself, I use it and it works well, is not a touch slower but hey joining across makes up for it!

Storing data in both Neo4J and Postgres isn’t something I recommend. As mentioned earlier in this thread, something should be the source of truth and then you can replicate to another data store for querying.

There is a great talk on this exact situation by Glen Vanderburg at RailsConf a few years back (this was a project I was initially involved in): https://m.youtube.com/watch?v=Nz-aU3vOFbw

Thanks for a lot of good answers!

From what I’ve gathered, using Neo4j as document storage is not the best idea (it doesn’t scale well). However, a lot of projects use a combination of MongoDB as document storage and Neo4j for metadata. I’m thinking that Postgres should be able to fill the role of document storage just as well, as it can store json documents and keep separate fields for what needs to be queryable. Is using Mongo + Neo4j a good idea, and if so, why is this not also the case for Postgres + Neo4j?

The reason for using Neo4j is to simplify modeling of complex structures such as permission hierarchies, as it can be queried and examined exactly as it’s stored, but also to make queries faster when they get several levels deep. I feel like “don’t do it” is a bit of a rash decision, as there’s no doubt that graph databases excel at both these points. However, I can’t find a single record of someone combining Postgres + Neo4j, so I do feel like I’m doing something wrong

I think with Mongo + Neo4j you’ll likely have a lot of the same problems as with Postgres + Neo4j. Mongo vs Postgres is probably a matter of personal preference in many ways, but I would go for Postgres even if I wanted a document database (using Postgres JSONB).