Monthly Archives: December 2018

Braviant Holdings talks at 2Q PG Conf

Better later, than never: by popular demand here are the videos of both talks from Braviant Holdings, presented at 2Q  PG Conf. Enjoy 🙂


 

Advertisements

Leave a comment

Filed under Companies, events, talks

2Q PG Conf 2018 Chicago was a success!

It’s the second time that 2QPGConf was coming to Chicago, and this time around it was even better, than last year! This year it was a two-days conference, with a day of training preceding the actual conference day.

I’ve been asked by many people, what exactly did I like about the conference, so I thought I will put it in writing.

First, I really like an idea of regional conferences, I think they play a very important role in giving the users direct access to the leading specialists, an opportunity to know what other people are doing, and all of it at a reasonable cost. Not all the companies are as good as mine to have a budget for professional development for each employee, and many are reluctant to let their employees being away from work for a week. Or, as I was told, sometimes companies won’t even allow their employees to attend the conference unless they have an accepted talk. And it is so much easier to convince your manager to let you go for a day :).

Secondly, I really liked the talks, I think it was an excellent selection, both on technical level and on variety of topics and areas of use. These talks sparked a lot of conversations, and lots of new contacts have been made. Actually I think a couple of extra hours of networking would be great.

For me the most important thing was that I was able to promote Chicago PostgreSQL User group. I am really happy that our group is now considered to be one of the most successful in the country, and that people turn to me for advice on how to start (or re-energize) the groups in other cities. I hope that my work helps to promote Postgres as a database of choice. Actually in his opening keynote Tom Kincaid was talking about Postgres becoming more and more popular among developers, and I think that people are just starting to realise all it’s potential!

Leave a comment

Filed under events, talks

I hate views!

I really do. I have no idea, why they were even introduced into the SQL Language – for the record, there are no views in the relational theory. There is no justification for views to exist.

I suspect, that a person who’ve introduced the views for the first time just thought about themselves, and was lazy to repeat the same SQL over and over again, And then they never gave a thought, that somebody else may start using the same view.

The first reason I hate views it, actually comes out of the only legitimate reason to use the at all (yea, I am contradicting myself). I believe, the only legitimate reason to have a view is to use it as a canned report. So you come up with a perfect SQL for some requirements, and then you give it to the end user in form of a view, and say – use this! Just SELECT * FROM this-view.

But the next thing happening is (and that is my reason number one to hate views) that the end users take the words “as good as a table” literally and start actually using your perfect view as a table, like adding some conditions, or worse – joining it with other tables. Actually, even adding a condition may be damaging enough if your views contains a GROUP BY clause.

Why this is so bad? Because in general Postgres (and most of other databases) does not guarantee anything regarding how the execution plan with the view will go. It might or might not execute the view first. It may or may not push down additional selection criteria. In general, the performance becomes unpredictable.

The second reason is the continuation of the first. The end user does not know what’s inside the view (as they should not!), and often would make decision on which view to use literally by it’s name!

You might have guessed by now, that there is some immediate reason I am writing this post. Here is it: a couple of weeks ago I finally found some time to address the super-poor performance of one of the views used by our marketing team. Since I’ve written the first version of this view about 2.5 years ago, I thought I should be the one to fix it now.

I’ve started to read the code, looking at what have changed in the past 2+ years. Some of the conditions looked questionable to me, and I’ve scheduled a meeting with the primary business stakeholder to go over those conditions.

You won’t believe what I found out! None, NONE of the complicated conditions from the original report were needed! None. Instead, only a certain subset of the old output was needed, with a couple of extra fields. So all the complex calculations from the original view – they were performed, but the results were not needed. Instead, there was an additional filtering, since less than half of the result set was used. Well, the name of the view was still reflecting its purpose, so the marketing department never had a second thought 🙂

I can’t even call this an optimization! I’ve just crossed out 805 of the old code and added a new condition. A second instead of 15 minutes! So now we are good for now… till somebody will try to use this view for something else 🙂

2 Comments

Filed under Data management, SQL