Postgrex + Redshift. Anyone?

Tags: #<Tag:0x00007f114241bd10> #<Tag:0x00007f114241ba90>


Hey folks,
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:!msg/elixir-lang-talk/QGB5Tcr18ms/-56rADBMEQAJ points to this fork that is at this point a little old and unloved:

as well as some stuff on the issue tracker for Postgrex:✓&q=redshift

Greatly appreciate any reports/help!


i use postgrex with redshift internally. you need version 0.13.1 or later (not 1.0.0-rc.0 or 1.0.0-rc.1 which predate the 0.13 series) but it should work fine


@talentdeficit how is this working out for you? Are you using this along with Ecto?


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



Just wondering what you ended up using?

Thanks! =)


Hi @demem123,

The system is now in production and works well.

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.
Thanks! =)


redshift doesn’t support upsert and the redshift client library is actually just postgrex so if you try to use ecto upserts with redshift you’ll get sql errors in your queries


Thanks! I’m sticking with postgrex then. Do you have to use a pool to manage db connections. poolboy? I think Ecto repo has a pool of connections to work off of.


postgrex has pooling built in. see the options for start_link


thanks! will check it out. I think the last time I tried, i used a child_spec and it didnt create a pool. :stuck_out_tongue:

Thanks so much! =)