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

You need to use a 2-step transaction in postgres (https://www.postgresql.org/docs/9.3/sql-prepare-transaction.html)

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

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.

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.