PG Open 2016 – day one

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 🙂

Advertisements

Leave a comment

Filed under SQL, talks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s