Citus Con 2023

I barely mentioned my participation in that event, partially because it was virtual and partially because it was too close to PG Day Chicago, but I want to say it again – I am glad I participated! Also, I am glad I recorded the video early, and I am very thankful to the organizers because it was the least stressful recording of all I ever had!

And it was such a delight to receive the speaker’s gifts today!

May Chicago PUG

We had a meetup on May 17, and I am really happy with how it went. It was the first time since the pandemic that we had local speakers talking about their experiences.

Before the pandemic, I tried to make every other event “made in Chicago,” which appeared to be the most difficult to revive, and I am glad we got there. I am also very happy that we had new PUG members: people who are starting to use Postgres and are looking for resources, guidance, and advice. And a place where they could ask questions.

As always, I am especially thankful to DRW for hosting us and to all of those who are coming and presenting! On this note, I am looking for speakers for the upcoming Meetups (July, September, October, November). If you are interested, I would love to hear from you!!!

Should a consultant know everything?

Although I am often asked to do some consulting and like helping people, my two consulting jobs left me with a strong opinion that I do not like to be a consultant.

Recently, I mentioned this fact in a professional conversation, and the reaction was somewhat unexpected. The conversation went like this: yes, some people feel uncomfortable consulting because you are expected to know everything, and then they are afraid that they will get a question to which they do not know the answer. But if you work for a consulting company, you do not need to worry about that because there are always others you can ask, and somebody will know. And the customer does not need to know that you used other resources. 

I never thought about consulting that way because I never saw it as a problem that I might not know something. It is perfectly normal not to know everything, even in your narrow field. I never have a problem saying that I am unsure about something and need to look it up or ask people who might know better than me. I do not think that ever compromised my credibility. Moreover, I would be more suspicious of someone who always has answers. 

Once, I interviewed a person for a Postgres DBA position and asked him to rate his knowledge of Postgres on a scale from one to ten. He said: seven. After he was hired and completed his first one hundred days on the job, I asked him at our one-on-one how he would rate his knowledge of Postgres now after he had learned so many things. He said: now it it indeed seven! At his first end-of-the-year review, I asked him the same questions. He said: oh, now, it is truly seven! 

To be honest, I often want to give myself seven in Postgres as well. Definitely not more than eight 🙂 

What do you think? Do you expect a consultant to answer all and any questions you have? How do you feel if they “need to look this up?”

What does “a busy system” mean?

A couple of weeks ago, I was fixing the problems with a system with “too many connections.” You know how these systems behave: applications initiate a large number of connections, most of which are idle, but at any given moment, there are a couple of dozen of active connections. Sometimes, the Postgres server reaches the maximum number of connections, and a DBA on call has to kill some idle sessions and then plan a system restart to increase the max_connections to yet another ridiculously large number. Sometimes, the system receives an “out of memory” OS error because each of these idle connections keeps the work memory that was initially allocated to it. Then we start hearing the conversations about moving this “very busy system” to a “bigger server” and also, “Why we won’t install pg_bouncer?”

When you read about PostgreSQL best practices and generic system architecture recommendations, you inevitably come across something like “On a busy system, you can increase this parameter to X” or “On a busy system, you may need to do Y.” One of the first questions on all known customer questionnaires is “What’s expected an average and maximum IOPS?” Then, recommendations are given/resources are planned based on the answer. 

I believe this planning starts in the wrong place. The first question we should ask is, “What makes your system busy?” 

Do not take me wrong; I do not imply that there is no such thing as a “high I/O system.” Still, the first question we should ask is, what’s the reason for the expected high I/O? Does the system serve millions of customers at any given moment? How many SQL statements are executed on average between the moment the user presses “Enter” and the results appear on the screen? 

If applications are using connection pools, they are already consolidating individual customer requests into a much smaller number of sessions. Will one more level of abstraction improve the system throughput? In many cases, it will only make things worse.

In my case, I talked to application developers showing them the percentage of idle sessions and estimated resource consumption. On the first take, they reduced the average number of open connections from 2,700 to a little over 1,000. I kept insisting on more research, and after one more week of digging into the application code, the average number of connections was reduced to sixty! 

There are many reasons for the system being busy. Sometimes it indeed provides the geodata to millions of drivers simultaneously, but in other cases, it may be just connection leakage!

As I always say, tuning system parameters may improve performance up to ten times, tuning individual queries may make everything run tens times faster, and optimizing the system architecture can make things run hundreds, up to thousand times faster!

Optimization Book 2.0

Dear friends and colleagues, we are about to start working on the second edition of PostgreSQL Query Optimization! Since the time the first edition was published two years ago, we have received a lot of thanks and a lot of comments and suggestions! I wanted to thank everyone who took the time to reach out, pointing out inaccuracies, typos, factual mistakes, imperfect code formatting, etc. All feedback was saved so that issues could be addressed in the second edition.

If you have some feedback you can share, if you’ve noticed something while reading the book or running examples from the postgres_air database, if you have comments, if you noticed typos – please-please-please reach out! We want to hear from you!

Thank you, and good luck to us 🙂

The Complete History Of PG Day Chicago

I felt compelled to write a blog explaining why I am so excited for PG Day Chicago finally being a reality, not just my dream. Many people heard at least some parts of this story, but it’s the first time I am presenting the whole timeline.

I started working with PostgreSQL in July 2011, and shortly after, I attended the first Postgres conference, PG Open, which was back then happening in Chicago and was a three-day event with huge attendance! Before that, I only attended academic conferences, and that one looked very different. Needless to say, in addition to its novelty, I felt completely stupid because I barely understood what it was all about. I had enough courage to ask somebody (maybe even Magnus) whether they plan to have subtransactions inside functions :)).

I attended the next PG Open, and I also went to Ottawa for PG Conf, and gradually, I stopped feeling completely stupid, but I was still a “nobody.” And then, in 2013, I submitted the talk proposal to PG Open, which was accepted!

I posted the first entry of this blog after PG Open 2013. Interestingly, I said nothing about my own talk: I felt that it was entirely unimportant in comparison with others’ talks! Then my talk was accepted for 2014, and also, a strange thing happened: I came to register and realized that everybody knew me! I could not understand how it happened! Still, I felt like Postgres conferences were “not real” and tried to submit to “real” conferences, meaning the academic ones. (I had two acceptances and two papers published, one in 2014 and another in 2016, but that’s a different story).

And then, a horrible thing happened: PG Open was moved from Chicago to Dallas! I enjoyed every moment on it, and I have a whole bunch of blog posts about it (one, two, and three, and actually, I forgot that it was the first time I learned about FDW), but I also remember being extremely upset about this move to Dallas! And whatever Steve Frost is saying now about this move, I remember very well what he said back then: there is nobody left in Chicago to do the conference! Yes, some dramatic events were happening at that time, but I felt offended that the fact that three people left meant that “there was nobody!”

Then came PG Open 2016, again in Dallas, and that’s when I presented pg_bitemporal for the first time and met many new people (one, two, and three). And since by that time, “everybody knew me,” and I even managed to organize an impromptu after-party, I felt brave enough to ask Steve whether we could have PG Open back in Chicago. He asked me: how is Chicago PUG doing? I replied: pretty much not doing anything, barely alive. He said: see, there are 250 PUG members in Dallas! How can we have PG Open in Chicago when there is nobody to come? Give me your PUG first, and then we’ll talk! I said: OK! I will give you Chicago PUG!

I returned back to Chicago (and for the context, I was at Braviant for just five months) and asked our leadership team whether we could host Chicago PUG. They said: yes, starting in January. I called the then-organizer of Chicago PUG and invited him for lunch. When we met, I asked him in a very straightforward way whether he wanted to give me the reign :). He said that I was an answer to his prayers because he was not charismatic, and I was 😀, and we decided to complete a transition within the next three months.

My first meetup was in January 2017, and the number of people who showed up was three times more than in November! (actually, more people than RSVPs!)

PG Open 2017 was in San Francisco, and I asked Steve Frost: Do you remember what you told me a year ago? He said: Of course not! I told him: you said that if I give you Chicago PUG, you will give Chicago a conference! Did you hear how Chicago PUG is doing these days?! He replied: Oh, yes!!! But still, PG Open didn’t go to Chicago!

Instead, PG Open 2018 was in Dallas again, and once again, I enjoyed it a lot, and we presented a “pre-version” of NORM, but Chicago was still not in the picture! Thankfully, 2ndQuadrant started to hold their conferences in Chicago, and I enthusiastically helped them, but it was still not a true community conference. I Dallas, I talked with several community members (I am not pointing fingers at the moment:)) who said they would help me to organize a Chicago-based conference, but once again, nothing happened, regardless of how hard I championed this idea.

Then came 2019. I submitted a tutorial proposal, and it was put on the reserve list, and the organizers asked me whether I would attend. The problem was that by that time, I already knew that I was a finalist for the Illinois Technologist of the Year Award. If my talk was accepted, I would still fly in for just a day to deliver it, but being on reserve and having the final happening on the same week, I didn’t feel like it would be a wise idea to fly for just one day.

Since I could not tell the real reason for declining, the PC thought that I was obnoxious, and they stopped talking to me, and nobody even mentioned my award at the conference (I thought that it would be cool to mention it in connection with Postgres, but…)

And then, Magnus and Devrim came to Chicago in December 2019, and I asked them to present at Chicago PUG (we had a record attendance!), and I took them to Zbar in Peninsula, and I finally got Magnus’ blessings to start preparing to PG Day Chicago! During January and February 2020, we had endless conversations with Stacey Haysler, during which she patiently explained to me how I was doing everything wrong, and then… everybody knows what happened! Or rather, what didn’t happen!

Nothing was happening until May 2022, when Steve Frost was in Chicago, and I got a chance to meet with him. I said that I still wanted PG Day Chicago to happen, but I have nobody to help me, and I do not have a team anymore, so without help, nothing would happen.

And then – everything happened! And now, I do not even want to describe all my fears and what went wrong. Now you know what a long journey it was, and why I am so thankful to Magnus Hagander, Steve Frost, Pg.Us Board for making my dreams a reality, DRW for letting me do community activities, and everybody who contributed to PG Day Chicago in all possible ways!!!

POSTGRES_AIR updates

Thanks to our GitHub followers and many people who had given a try to previous versions of the postgres_air database and provided valuable feedback, we are now happy to announce that we are releasing a new version!

In addition to moving the timestamps one more year forward (the new “today” is August 17, 2023), there are multiple important fixes based on the feedback received from our amazing followers and book readers:

• Arrival timestamps were corrected to make the flight durations consistent with distances. This change resulted in inconsistent itineraries for some bookings. To address that issue, the inconsistent flights were rebooked.
• Inconsistently named integrity constraints renamed.
• Overlapping bookings for the same passenger are replaced with bookings for other passengers.
• The problem of overbooked flights is addressed in three different ways: (1) additional flights, (2) aircraft change (to larger capacity), and (3) rebooking passengers.
• The backup contains stored procedure postgres_air.advance_air_time ( p_weeks int default 52, p_schema_name text default ‘postgres_air’, p_run boolean default false)
• This procedure moves all the dates in the schema for the number of weeks specified by the first parameter.

As always: please give it a try, and please let us know what we have forgotten this time!!!

PGSQL Phriday #007 – A word about triggers

I am two days late for the PGSQL Phriday #007 deadline, but I can’t skip the question asked by Laeticia Avrot: do you love triggers? Do you hate triggers? Also, I really enjoyed reading Ryan Booz’s response. I can relate to many of his statements about triggers.

Since Ryan already said everything I could possibly say about triggers, I will be brief. Triggers can do a lot of harm! Triggers can go really wrong – those who attended Nordic PG Day are sure to remember this war story. I do not support those who say that triggers should be banned, but I understand their frustration.

Still, PostgreSQL is impossible without triggers. Did you hear about internal triggers? Postgres creates these triggers behind the scene to support constraints (how would you support them otherwise?!). When I wanted to add foreign key constraints to pg_bitemporal, I had to create a bitemporal version of these triggers.

Triggers are exceptionally powerful, which is both good and bad. I believe that there is no problem in Postgres that you can’t solve by creating triggers that will do exactly what you want. A couple of months ago, I complained about permissions in Postgres: what I do not like in the default permissions setup and what I would change. Since patience is not my virtue, and since I do not like to complain and prefer “being a change,” I decided to model my ideal permissions settings. I achieved it by building a system of event triggers. I am not going to lie – the result is inhumanly complex, but event triggers made it possible.

My short summary for this short post is the following: please stop pretending that we can live without triggers. We can’t!

PG Day Paris 2023

I know that these days, people do not blog about the event three days after, but I think it is still better to blog about a great event than not.

On Thursday, I attended PG Day Paris for the second time; it was the first time I presented there. Also, this year, it was a two-day event, with a training session on day one and two tracks on day two. And also, one track was all-female :).

My personal milestones it was my first presentation of something I developed while being with DRW and the first time I came back from the conferences not with just “I learned some interesting things,” but with “I learned something new which will be useful for us, and I want to implement it right now!”

My Speaker’s Interview for PG Day Paris 2023

https://2023.pgday.paris/speaker-interviews/henrietta-dombrovskaya/

I am looking forward to present in Paris!