Just got back from Dallas. These three days were really great! This is the sixth PG Open I am attending, and each year the conference getting better and better. This year was probably the first time I didn’t come across any “so-so” presentations. Literally each and single one were worth attending.
Same goes with the overall technical level of the participants- it feels great to receive meaningful questions, and feel connected to the audience.
The first day was a day of tutorials; I’ve only attended the Advanced SQL and functions. It’s always fascinating to be able to “feel and touch” the internals of Postgres, to be able to ask the first hand: so, that’s what I see, can you explain me why it behaves that way? I’ve asked about the indexes on jsonb, because each time I am asked, why not to use jsonb type, if the object structure is likely to change. I am always replying, that it’s OK to use a jsonb object as long as you do not really care about what’s inside and treat it as a whole, but if you want to search by something…
And then people immediately tell me: but now you can index jsonb, what’s the problem? Well it is a problem, because those indexes are not even remotely as efficient and standard b-tree indexes. I’ve being telling this to everybody since these indexes were introduced; but now I have it first-hand :). Unfortunately Steven Frost didn’t have any comparison statistics, but at least I have his word :).
There were two other things of “I’ve always thought so, but didn’t have a proof”. One – that most of the time multi-field indexes perform worse, that several single-field indexes with bitmap matching. I would say the only situation they are beneficial is when you can use an index-only scan. And the second thing – that if you want you anti-join to be executed efficiently, you should perform an outer join and then filter by condition “id in this other table is null”. Once again, I never had any doubts about that, but I was always criticized for that!
I hope that those tutorials were recorded and will be available next week 🙂