Tag Archives: databases

The best things from 2017 and what I am looking forward to in 2018

When I am talking to people about the year 2017, and what was important, I would repeat over and over: everything I was striving for during my 30+ years of professional career had happened. All wishes have come true. Which would inevitably lead to the question: well, Hettie, what are you going to next then?

And there are plenty and plenty of things I want to do, but first I wanted to reflect on 2017 one more time. I’ve already listed multiple times all our technical accomplishments, all the wonderful things I was able to implement in a real production environment.  But when I look back at what was the best, it is definitely working with our new tech team. As I am reiterating over and over again, “the database is a service”, and whatever we are doing inside the database, can only make any impact, if our results can be utilized by “somebody” – by our end users.

And most of the time our end users are  application developers.  I can’t imagine any serious database development without continuous interaction with the rest of the development team. For years my “golden standard” for the teamwork has been my work for New York Department of Education, where I had a full support of the tech leadership, which helped me to defend my approaches and produce the results I am still happy about.

But what has being going on in the past several months is even better. The way we discuss the user stories. The way we make decisions on what should go into the application, and what – into the database. The way we debug. The way we discuss, what is critical, and what’s not. The willingness of each of the team members “to go extra mile”, to adjust in order to make others job easier. Basically, whatever I could imagine for the teamwork being perfect, is there. 🙂

Being in this industry for 34 years, I know that nothing lasts forever:). I know that every tea,, every organization evolve, and that nothing is granted forever. But I also learned to be thankful for what’s going on right now, and enjoy the moment.

Looking forward to 2018 – I hope that our team will continue to be the most amazing team ever. I am looking forward for the stress -test of our new applications with high data volumes. After all, I was designing the data storage and data access to be super-scalable, and I hope that it will work this way.

I am looking  forward to use the performance data we’ll obtain to improve our bitemporal library, and I already have some ideas of what I exactly I want to do. Actually, I have new technical ideas on almost everything I’ve developed through the last year. I still didn’t get notifications on whether any of the papers I’ve submitted for the next year conferences were accepted,  but I know that both are very good papers :), so even if none will get accepted… I will try for another conference!

 

 

 

 

Advertisements

Leave a comment

Filed under Development and testing, SQL, Systems, Team and teamwork

I finally figure out how it works!

I never understood, why in some cases when you look at the Postgres execution plan you see “Recheck condition”. This happens, when an index search is executed, and when I was looking at the execution plans of that kind, I never could understand, why Postgres wants to go back to the table and “recheck” something, while all the conditions should be satisfied by the index itself.

Last week however, i found myself in non-typical situation, when I had a query to optimize, and I could not bring it to the acceptable speed. If was imperative, that it would execute in less than a minute, actually highly preferable – within 30 sec. I knew that the culprit was one of the sub-selects, which was querying a very large table, and thereby I absolutely had to convince Postgres to use the index-only scan.

And it didn’t work. I’ve created a compound partial index, which would exactly describe a condition I was filtering by, and I’ve included all the fields I needed to select in this index. But the query continued to be slow, and the execution plan was clearly showing “Recheck condition”.

After a number of experiments I’ve realized, that I in order to ensure the index-only scan I need to include into the index not only the attributes I am selecting, but also the attributes which I am constraining.

Specifically, when I was running a query

SELECT loan_id,
min(date) AS first_date
FROM very_large_table
WHERE days_past_due >30 AND status in (1,2)

having the index

CREATE INDEX cond_index ON
very_large_table(loan_id, date, days_past_due)
WHERE days_past_due>30 AND status IN (1,2)

was not enough to ensure the index-only scan. Instead I had to build the following index:

CREATE INDEX cond_index_2 ON
very_large_table(loan_id, date, days_past_due, status)
WHERE days_past_due>30 AND status IN (1,2)

I was wondering for a while – why? But then I sort of thought about this “as a database” and realized, that if the status is not included into the index, then, if for some record only the status will be changed, and all indexed attributes remain the same, the rebuild of the index just won’t be triggered, and thereby the condition will require a check. On the contrary, if the status would change from “something else” to 1 or 2, the index condition will be triggered, and this record will be re-indexed.

Now I will remember!

Leave a comment

Filed under SQL

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!

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