Hi there, I need to optimise for storage on my target-postgres. Current implementation is optimized to not loose any data, which is nice but isn't convenient for my use case. Example: Some of my text fields would benefit from a pg domain/type and some other text data would be more efficiently stored as bytea. Even better if I could manipulate the tablespace of the schema/table or set a table to be an hydra columnar table, a timescaledb table, or set pg_partman config somehow. I see multiple proposals in GitHub but idk if there is a consensus on a target implementation to force a column type to be something else or to hook into table dll. If there's no consensus I'll just fork target-postgres and do my own thing but just wanted to ask in case that could be contributed back somehow

Related discussions. Optimizing for storage/querying types in meltano • https://github.com/MeltanoLabs/target-postgres/issues/181 • https://github.com/MeltanoLabs/target-postgres/pull/251 • https://github.com/MeltanoLabs/target-postgres/issues/13 Hex data: • https://json-schema.org/draft/2020-12/draft-bhutton-json-schema-validation-00#rfc.section.8.3 • https://stackoverflow.com/a/52261158 • https://datatracker.ietf.org/doc/html/rfc4648#section-8 Enums: • https://pydoc.dev/sqlalchemy/latest/sqlalchemy.dialects.postgresql.ENUM.html • https://json-schema.org/understanding-json-schema/reference/enum • https://www.rfc-editor.org/rfc/rfc8927#section-2.2.4 • https://www.rfc-editor.org/rfc/rfc8927#semantics-form-enum • https://cswr.github.io/JsonSchema/spec/generic_keywords/#enumerated-values

Thanks @Clément! I've left some comments in https://github.com/MeltanoLabs/target-postgres/pull/287.

Thank you sir! Really appreciate your support and reactivity 🙏

Alright, should be fixed, not sure about the naming of the option so added a bunch of documentation

okaaay, let's do something similar for the enum thing

oof this is way more complex than anticipated

Still working on it, struggling to make SQL alchemy understand not to drop the type along with the temporary table

oooooook, FINALLY something that works https://github.com/MeltanoLabs/target-postgres/pull/292

hope it's not too bad 🫡