Hacker News Reader

Most common one I’ve seen in the last 5-10 years: using a JSON column instead of putting in a lookup table, or instead of properly analyzing and normalizing your data.

That’s a mistake that you’ll be paying for for a while.

View on HN

How much slower is it in your experience?

If you let the JSON blobs grow past the page size the cost of recovering TOAST tuples can be 10x reading main storage. Also JSON is fundamentally de-normalized so you can incur scan and read costs just from hauling out duplicate values where a nice normalized lookup would be snappy. And finally JSON recovery is going to pull the whole object every time, even though you are probably only interested in one element of the object, so again, higher recovery times compared to an equivalent normalized model.

It’s not even the speed, it’s writing queries. The syntax is clunkier, and since you don’t have a nice clean schema, you end up doing a lot of jumping through hoops to check for the presence of keys/proper data types, etc. Or doing your basic data validation in your app instead of letting the database do its job.

Reply on news.ycombinator.com