What's your opinion on json/jsonb postgresql column types? You'd have a single column to cram the less predictable/structured data

I think thats key here, if your data has a predictable schema, many columns win for me over singular EDN-string or JSON/JSONB columns. JSON/JSONB, for me, are for semi-structured or many-shaped data.

@U45T93RA6 I thought about that, but I find that the deserialization from JSON is a bit tricky because the type info is lost. Using (prn-str) and (read-string) I get exactly the same type back.

One could use e.g. Malli coercion afterwards of course

JSON(B)? is queryable in Postgres, thats also something to consider — but still, tables and columns if you can 😉

@UDF11HLKC Good point, definitely a plus. But then I was wondering when I would need that? Probably mostly for migrations, to change all JSON blobs at once. But then this is also possible (if not easier) with the columns, as you write, or with the idea I sketched above.

In case I would go with individual columns, I wonder if it is a good idea to programmatically generate the columns (generate the table schema) using my Malli spec……. seems to be a fragile approach?

I mean, what you say exists, its EAV databases like Datomic. It is an idea that has merit on its own

Yeah I feel like imitating Datomic in PostgreSQL 😄

Reddit famously ha(d|s) it, thats why so many things are upvoteable. Anyhow, I’ll stop, this is a distraction 😛

Sorry, Reddit has what?

They used(?) to have an EAV database schema. Anyhow, I want to steer away from this haha, your usecase sounds structured, so that is where I would go — I’m merely saying that systems exist with less rigidly structured data, and EAV databases can help with that. Their ups and downs are well understood I think ^^.

Yeah I have to admit that I was not aware of the term EAV anymore, so that pointer is helpful. And yes, you are right, in my case it is fairly structured so I guess going for individual columns would be the right way. Thanks for you time and advice!

Another option, if you don't plan to query by column on that data or run numeric computations over it, is to use EDN for the whole map instead of JSON(B). But it really does depend on what you're planning to do with the data in terms of SQL queries.

We have some data that we never query except for one field, so our DB table has: PK, that one field name, and then a TEXT blob containing the EDN. Perfect for our use case. We also have some data that potentially has a lot of columns and we need to query by any/all of them, so we use EAV for that rather than actual DB columns.

Hi @U04V70XH6, storing the whole map as EDN in a TEXT blob was in fact my first implementation. Quick and easy, but I am a bit worried about how I would handle future migrations to the map structure, once I have all those EDN blobs in my DB. I guess I could add a version number to the map and then read it accordingly (migrate on the fly, I guess thats how MongoDB and the likes do it, right?) or write a one-time migration script, but it still feels a bit shaky. Therefore reinvented the EAV wheel at first, but now I guess I’ll go back to the roots using good old columns. 😉

All of these approaches are totally reasonable and the reason Postgres is so popular is that it is this flexible. Wide tables, JSON colums, EDN blobs, KV tables, ... they all have their use cases. And the beauty of Postgres is that you can cover them all with a single tool. And it's good enough for 99% of cases. For many cases starting with any of the approaches is probably fine. Once you learn how it works in practice and what exactly your specific pain points are you can reevaluate.

Thanks @U8ZN5EHGU, that gives me some confidence. The hardest decisions are often the ones where you have equally good options… 🙂