6 ms·
Oh gosh this pattern. The first time I encountered it was in my first job where we used Magento. Super flexible. Also super slow. Does anyone have any advice ho
by nstart 9y ago
Oh gosh this pattern. The first time I encountered it was in my first job where we used Magento. Super flexible. Also super slow. Does anyone have any advice how to make a db design like this work faster? Generally I thought when data is arranged like this it might be a prime candidate for document based storage. But I'm no dba so I have no idea if that would be correct.
- matte_black 9y agoIf you are using Postgres, the JSONB datatype will let you do exactly this while still using the full power of SQL. Simply create a column where you keep a JSON object full of random user properties, if flexibility is what you want. You can even index properties.
- icebraining 9y agoThe question is whether it can be stored like this while allowing for fast queries. For example, unless it changed recently, Postgres doesn't calculate statistics to help the query planner on jsonb fields.
- matte_black 9y agoThe answer would make a great blog post.
- zaarn 9y agoJSONB columns should behave just like any datatype with a GIN index in the recent releases, to my knowledge. Still, a JSON column will arguably be faster than a pure KV table since you can more efficiently query it, especially any non-JSON columns.
- ptman 9y agoIIRC JSONB still has problem with index statistics So values in JSONB columns can be indexed nicely, but the statistics can be much worse than for non-JSONB columns, which can lead the query planner astray.
- NickNameNick 9y agoPostgres will let you create an index on an expression into the JSON column, so querying should still be very quick.
- mhd 9y agoOr just create ad hoc tables with user fields. Quite often it's not that a customer has n different fields for n entities, but a few that apply to the majority (like internal ERP ids, classifcation etc.). Put them in a few tables, index them, join them. If you don't want to parse internal DB descriptors, create a set of "schema" tables to build queries from.
- bitexploder 9y agoThis just isn't that hard. They don't have that much data. It is really late for me, but, put it all in memory and figure it out. These just aren't hard problems. DBAs have been solving performance issues for decades with a clever index on the right column for 30+ years. Sorry if this is get off my lawn-ish, but I have been on too many projects where I made a DB index and solved a major bottleneck. Too many new developers are ignorant to the nuance of RDBMS tuning. I am not even a DBA.
- collyw 9y agoI am not a DBA, but I could see this crap when the NoSQL hype took off. "Relational databases don't scale" Well they worked fine for decades before Mongo's marketing claimed so.
- thecatspaw 9y ago> Does anyone have any advice how to make a db design like this work faster? Normalize it properly. If this is not possible, ensure that both the userid and the property are indexed
- bpicolo 9y agoIf what you actually want is data arranged like such, Datomic is probably a prime candidate https://www.datomic.com/ https://www.datomic.com/