PG.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.

About the Author

Photo of Markus Winand
Markus Winand tunes developers for high SQL performance. He also published the book SQL Performance Explained and offers in-house training as well as remote coaching at http://winand.at/

?Recent questions at
Ask.Use-The-Index-Luke.com

0
votes
2
answers
750
views

different execution plans after failing over from primary to standby server

Sep 17 at 11:46 Markus Winand ♦♦ 741
oracle index update
1
vote
1
answer
96
views

Generate test data for a given case

Sep 14 at 18:11 Markus Winand ♦♦ 741
testcase postgres
0
votes
1
answer
251
views

Database design suggestions for a data scraping/warehouse application?

Aug 27 at 09:29 Markus Winand ♦♦ 741
mysql optimization database