06-09
Regrets on JSONB
I made a lot of columns JSONB for a lab information table. It is still one of the main tables we use in production at work.
Initially it was arguably a lean and useful solution, but now it is quite outdated and inefficient.
So the table looks like this but much much larger both horizontally and vertically
| id | lab_name | Awards | Papers |
|---|---|---|---|
| asdf-asdf-asdf-asdf | xx lab | [{ "title": "some award", "desc": "asdf" }] | [{ "title": "Paper A", "year": 2020 }] |
| asdf-asdf-asdf-asdf | yy lab | [{ "title": "London Award", "desc": "For excellence" }] | [{ "title": "Paper B", "year": 2021 }] |
| asdf-asdf-asdf-asdf | zz lab | [{ "title": "San Diego Prize", "desc": "Achievement award" }] | [{ "title": "Paper C", "year": 2022 }, { "title": "Paper D", "year": 2023 }] |
Biggest problems:
- A lot of network bandwidth used - a lot of cloud network cost.
- Cannot index individual entities within awards, unless using GIN.
- TOAST problem
I feel very incompetent in explaining these concepts deeply as I don't know the inner working of GIN, TOAST, etc. But for sure there are a lot of problems now and there is a big need for normalization at current state. It's just that it was not prioritized since we have so much crunch for developing new features.
Mistakes with SEO
I made quite a mistake at my company last week with SEO. Didn't know the existence of google search console that a few others have been using. I was monitoring SEO issues only by searching manually which was not a good approach.
I announced to my teammates that when I write an opinion on slack, I will try to write down the reasons for my thoughts(i.e. what I investigated to come to a conclusion that SEO was fine.).