simrayz
Combining postgresql and neo4j
I’m using a Neo4j database to supplement my Postgresql database. Postgres is used to store the entities themselves (e.g. users), and should always be the one source of truth. However, I am struggling with figuring out how I make sure the Neo4j database consistent with my postgres database. I am using Ecto and Bolt.Sips as my database adapters.
How can I make sure that a user is only created/deleted/changed if the action is successful in both databases? Both Ecto and Bolt.Sips has transaction/rollback functionality, but is there a way to combine them?
This is what I have so far, but this would cause problems if the postgres transaction fails after the neo4j transaction has finished.
def delete_user(%User{} = user) do
Ecto.Multi.new()
|> Ecto.Multi.delete(:delete, user)
|> Ecto.Multi.run(:delete_neo4j, fn _, _ ->
Neo4j.Accounts.delete_user(user)
|> Repo.transaction()
Most Liked
yourpalal
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:
- make a PR To support it
- hack it into your codebase in some way
OvermindDL1
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! 
sorentwo
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
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
Enabling companies to succeed by building software people love.
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #javascript
- #code-sync
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #websockets
- #supervisor
- #advent-of-code
- #elixirconf-us
- #distillery
- #processes
- #forms
- #api
- #metaprogramming
- #security
- #performance
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Real-time error tracking, performance insights, and observability for devs.
Practical resources that improve the lives of professional developers.
Producing high quality Elixir screencasts since 2017.
The deployment platform built for Elixir: PaaS ease, VPS control.
Courses that'll move you from confusion to "Aha, now I get it!"