Does anyone have experience using Postgrex with AWS Redshift, preferably recently and/or in production?
My team is looking to use Elixir to be a broker for a lot of our ETL data engineering work, and we make heavy use of Redshift, among other tools that traditionally have very good support for JVM languages.
I know there has been work done on this front before, but I’m unclear as to where it sits now. For example:
i don’t use ecto with that particular system. it may generate sql that redshift can’t handle (as it supports only a subset of what postgres supports) but i think if you stick to queries that redshift can handle it would be fine
We’ve gone with using Ecto to run stringified/templated SQL against our Redshift cluster. The system is purely internal and only runs trusted queries, so we elected to go this route despite string interpolation in SQL being wildly insecure otherwise.
One thing working in our favor is that the queries we’re running against Redshift using this method are 1. largely administrative, 2. simple, 3. rarely change. For those reasons, we haven’t felt a great need to use Ecto’s query builder or anything on that level of sophistication.
Overall this works well enough for our particular workflow, but I would definitely not use this method if any piece of the system was public facing.
thanks! I started trying out postgrex as suggested by the other user. Just wondering do you do upserts on your redshift data? It sucks that in order to update records, i have to delete and re-insert as per redshift document.
Since you are also using Redshift from Elixir you might be interested that we released RedshiftEcto (Redshift adapter for Ecto) yesterday. It aims to provide a good experience working with a Redshift database from Ecto. Features unsupported by Redshift will raise an error and the adapter supports table and column options specific to Redshift in migrations.
We’re in a VPC and I was trying to tunnel through a bastion box to connect from my machine. Otherwise nothing is really special, it’s phoenix 1.4, ecto 2.2, and that’s about it. We we just getting timeouts on any attempt to connect.
We may try to run this on regular Postgres, and see if that meets out latency goals.