Hi, is there any way of using redshift for

MELTANO_DATABASE_URI

? We have tried installing

sqlalchemy-redshift

and using the

redshift+psycopg2

prefix, but it is complaining about type

BYTEA

for

value

in the

plugin_settings

table. We think this may be due to this line here.

There may be a way to get this working but I would not generally advise using Redshift, just because it is not tuned for OLTP workloads and you may experience performance issues for the large number of small incremental writes and updates. Do you have another option of a database to use by chance?

We are only using meltano for only one job atm so the writes would be small (one a day). How would you go about getting redshift working? 😄

@elliot

MELTANO_DATABASE_URI

is only for Meltano's system database, where it stores job state and plugin settings if you're in read-only mode. I think you intend to use Redshift as a data destination, right? In that case you need https://hub.meltano.com/loaders/redshift.

@edgar_ramirez_mondragon We are already using Redshift as a data destination, we would now also like to use it for the system database. Is this possible?

Hmm I see. You could, but as an OLTP, Redshift is Postgres with fewer features so among other things, some data types (like

BYTEA

as you found out) will be missing: https://docs.aws.amazon.com/redshift/latest/dg/c_unsupported-postgresql-datatypes.html It's also missing primary keys, foreign keys and indexes: https://docs.aws.amazon.com/redshift/latest/dg/c_unsupported-postgresql-features.html. That may be ok for a few thousand records, without noticeable performance issues, but I'd still recommend using something like Postgres RDS