- About Optimizer Hints
- August 12–22: Online Training (EU shift)
- Clustered Indexes Dirty Secret
- FOSDEM Impressions
- June 8–18: Online Training (US shift)
- MySQL is to SQL like ??? to NoSQL
- Non-monetary micro sponsoring
- November 11-12 in Frankfurt am Main
- Online-Training in July and August
- Oracle + PostgreSQL
- Oracle Bootcamp
- Party time
- PostgreSQL Performance Event
- Quiz Results
- SQL Server Performance Kurs in Stuttgart
- SQL Server performance training in London
- Shipping Terms
- Springtime for SQL
- The two top performance problems caused by ORM tools
- Top Tweets January 2013
- Training Survey
- Training and Conference Dates
- Use The Index, Luke
2012-11-13PG.Conf EU 2012 — Summary
With a little delay of just three weeks, I’m writing something about this years PostgreSQL conference Europe.
Including the training day, it was four days full of database stuff—in other terms: an awesome time, if you love databases :) But let’s start from the beginning.
I booked the full day training with Joe Celko on the first day. And I must say that was the best part of the conference—mostly because Joe is great. If you never meet Joe Celko, or don’t even know who the heck he is, you should definitely look for an opportunity to meet him.
The training he gave (“A day of SQL with Celko”) was a mixture of SQL related topics like keys, naming (e.g., plurals for tables), metadata, rollup (group by grouping sets, group by cube) and trees. Due to my day to day work, I’ve already seen some of the “rarely known features” he mentioned. It was still very valuable because he presented good examples for them. The “nested sets model” is particularly noteworthy. I hope to write about some of these topics soon.
Outside of the training, it was also very nice to talk to Joe. He even signed a copy of my book for me—kidding that he should write “I did not write this book.” On the next day, when I was skipping one session, I had a chance to chat with him. So, I challenged him about his famous “80-95% of the work in SQL is done in the DDL, not the DML” statement (e.g., recently cited here and here) and asked if he considers create index as part of DDL in this context. Well, there is a good reason create index is not part of the SQL standard: because indexing is an implementation detail. Still, I argued that it is required to make the final system working and I believe Joe agreed after a while. I hope he still agrees after a few weeks :)
Day 2, after Joe’s opening keynote, I attended to Bruce Momjian’s “Programming the SQL Way with Common Table Expressions.” Well, I did not like this talk because it was not focused on CTEs in general, but mostly about the "writable CTE" extenstion by PostgreSQL. Sure it is OK to focus on specifics on a PostgreSQL conference, but I don’t like this particular feature—even though I like some other proprietary features of PostgreSQL.
In the afternoon, I listened to Guillaume Lelarge’s “Understanding EXPLAIN’s output”. He covered almost all explain plan operations in 50 minutes, but it was still possible to follow him. I learned about EXPLAIN BUFFERS (how could I miss that so far?).
The talk about range types (“Your Life Will Never Be The Same”) did not exactly hold up to my expectations. It introduced range types in a, well, superficial way so that I neither had questions during the talk, nor did I feel enlightened. The only thing that remained was the feeling that I should have a closer look into this.
Josh Berkus’s talk “Elephants and Windmills” was also a little bit disappointing. It was basically about a data warehouse, and as far as I could see, there was not particularly noteworthy innovation in their solution. On the contrary: in the age of NoSQL, sharding and cloud computing, it sounds strange that they just dedicated separate hardware to handle the load of separate clients because it was just too much for a single box. However, I might have missed a critical point.
I liked Bruce Momjians second talk “Inside PostgreSQL Shared Memory”. It was just what I expected: some insight into internals you probably don’t need to know about :)
The last talk on Thursday “Index support for regular expression search” by Alexander Korotkov was very interesting. He is basically transforming regular expressions to logical expressions made up of trigraphs (3-letter groups) like this:
/[ab]cde/ => (acd OR bcd) AND cde (taken from his slides). After that, just use the existing PostgreSQL trigraph index, check each trigraph and apply the logic. Finally, the full regular expression must be applied to filter false positives (the logical expression can match too many rows). After all, it is very promising research that might give considerable speed improvements in some cases.
Friday morning: “Beyond Query Logging”. It basically explained new ways to find slow queries—which is, an absolute essential trouble shooting tool. It seems that PostgreSQL is coming considerably closer to the tools other databases, like Oracle, support.
Gianni Ciolli’s "Debugging complex SQL queries with writable CTEs" was the last session I attended. Well, writeable CTEs. I had to attend that just to give writeable CTEs a second chance. I liked the presentation (Gianni does it in a very funny way) and I didn’t dislike the use of writable CTEs in this context because it is not intended for production use. The trick is as following: Just convert all sub-queries into CTEs, and capture the content of each CTE with another writable CTE that copies the content into a debug table. In this way, you can capture the intermediate results of an SQL statement and review it later to see where you have an error in the statement. Without this approach, we have to run each part of the SQL statement after another. It is a nice gimmick presented in a very funny way, which doesn’t change my opinion about writable CTEs.
There were also two social events (Wednesday and Thursday evening, sponsored by EnterpriseDB and Heroku) and it was very nice to chat about the talks, about Prague, and about SQL Performance Explained. Writing a book is a very lonesome job—it was great to meet some of my readers in person.