Tag Archives: databases

One more reminder about the conference on November 9

Just wanted to make sure one more time, that all my friends in Chicago  remember about the 2Q PG Conf, which will take place in Chicago on November 9.

I’ve already mentioned earlier, that I will be presenting at this conference, but now the whole schedule is published, and you can see, that all the speakers are just extraordinary!

Hope you can attend!

Advertisements

Leave a comment

Filed under events, talks

My presentation at PG Open 2017

I was waiting for the videos to be uploaded, but since I am not sure when this is going to happen I figured out I will at least post my presentation.  If you compare it with the one I’ve presented at PG Open 2016, you’ll see lots of improvements.

Here is it:

 My Presentation at PG Open 2017c

Leave a comment

Filed under publications and discussions, talks

Bruce Momjan’s talk at PG Open will be replayed in Chicago – stay tuned!

Once again, I did’t write more about PG Open, although I had grand plans. But there were so many interesting talks, that I am still going to write about them, even though it’s already 3 weeks past the conference.

One talk I really enjoyed, was Bruce Momjian “The Window Magic“. You can find the presentation here.

That’s what I really liked about it. Window functions are hard. Seriously. This is one of those things, where you know there are options and answers to everything, but figuring out how to do this takes so long that you almost do not want to do this. What I absolutely love about this presentation is the fact, that it starts from the very basic example, and then adds one feature after another, and each time it is one easy – and logical! – step. And the amazing thing is, that Bruce manages to make sense out of literally each and single option, and they all make sense! None of them looks artificial or “why anybody would want to do that?”

Granted, it won’t be me, if I won’t have any comments :). The comment I had here was, that people should be warned that the window functions are resource consuming. The moment you really figure out this magic, you want to use it everywhere, even instead of min/max with group by! And the execution time will be very different in this case.

And now the best part – Bruce graciously suggested to bring his talk right here to Chicago!  Thereby we are moving the November PUG date – it is now scheduled for November 8.  Please RSVP to this event here.

Leave a comment

Filed under news, People, talks

Don’t forget about Chicago PUG this Tuesday!

Attention fellow chicagoans! Do you want to learn more about PG Open 2017 straight from the participants? Come to Chicago PUG meetup on September 12!

Here is a https://www.meetup.com/Chicago-PostgreSQL-User-Group/events/242081721/link to the meetup – please RSVP there.

Leave a comment

Filed under events, Uncategorized

About PG Open 2017

Usually I start to write about any conference I am attending while still at the conference. But this time around I had such a busy time, there were so many things going on, that I didn’t… and even now, two days after the conference, I am not sure where to start 🙂

Overall the conference was great. The weather in San Francisco was beautiful, the hotel was great except of the usual “too-much-air-conditioning” .

This time I didn’t sign up for any of the tutorials. so the day I came, I just met several people (which is always the goal at a conference in any case), and we also had the speakers dinner (a very nice one!).

The opening:

Continue reading

Leave a comment

Filed under events, news, People, talks

Optimizing something you can’t control

This is very much  like  finding Pluto! At Braviant, we use several external service providers to perform some business tasks. And then, as I’ve mentioned in one of my presentations about our usage of foreign data wrappers, we need to manage data, when we do not really own the data.

But this time around the task was even more complex, and I’ve spent weeks trying to figure out how to approach it. There is one Really Large Table on the “other” side, and to refresh the Data Mart, we need to select a small subset of records each time, basically “all records starting from the moment we refreshed last time”.

For some reason unknown to me something on the way from “them” to “us” did not work, and we could not push the condition to the external site. No matter what I was selecting, what was really happening (I’ve figured it out by observing the query behavior closely) – the whole table was fetched from the third-party server, and only then the selection criteria was applied.

The problem looked unsolvable, because “everything worked on the other side”. Then I cam up with one crazy idea. I thought: if we can’t push our condition through, may be we can create similar condition on the other side.

So, I’ve asked our service provider tech support, whether they can create a view on their side, which would restrict the size of object, I am selecting from, Note, I’ve asked for just a view, not a materialized view. So it was literally “query is executed locally”. And then I’ve mapped this view to the foreign table, so there was no changes to reporting.

Yes, this view has way more records than I need (it contains “last 24 hours”), while I refresh data every  two hours. However, now I select from way smaller data set, because the view contains only last 24 hours, not the last 2 months!

… and now tell me, which optimizer would be able to execute this kind of optimization?!

Leave a comment

Filed under Data management, SQL

Last Day to submit your talk proposals!

Hey fellow Postgres enthusiasts, especially a Chicago bunch! A friendly reminder, that today is a deadline for the 2Q PGCONF talks submission. If you didn’t submit anything, there is still time, just 1-2 paragraphs highlighting the main idea of  the talk  will be sufficient. Let’s show that Chicago CAN!

Leave a comment

Filed under events, talks