Monthly Archives: October 2009

PG West Slides

Posted on by

I really enjoyed PostgreSQL Conference WEST this year. It’s a shame I didn’t have a little more time to explore Seattle, however; it looks like a nice city.

There were a lot of great talks. Scott Bailey’s temporal data talk was excellent, of course — but I’m not an impartial observer, I expressed enthusiasm before the conference began. Another talk that I found very interesting was Bill Karwin’s talk on practical object oriented models in SQL. The talk was well-researched and the points were made very clearly, although I would have liked to see a little more depth. There was some audience discussion, and I’d like to see the points hashed out in a little more detail.

I’m always excited to present the projects that I’m working on, and this conference was no exception. I thoroughly enjoyed discussing Operator Exclusion Constraints, the new feature that I’m writing for PostgreSQL 8.5 that enables “temporal keys”. Slides are here — I’ll write in more detail in another article. I was pleased with the audience participation and questions during the talk, and I hope that everyone enjoyed listening to me as much as I enjoyed speaking.

Slides for my talk on extensibility are available here.

PostgreSQL WEST and Temporal Databases

Posted on by

I’ve been interested in temporal data and relational databases for quite some time. There are going to be at least two people talking about temporal data at PostgreSQL WEST in Seattle: Scott Bailey and me. See the talk descriptions.

In the past, I’ve worked on a temporal extension to PostgreSQL that implements the PERIOD data type. This is a data type that offers both a definite beginning and a definite end time, which is important for describing things that happen over a period of time, rather than instantaneously. Trying to use separate attributes for “start” and “end” is bad for a number of reasons, and will certainly be addressed in a subsequent blog entry. For now, I’ll just say that I believe the PERIOD data type is fundamentally important for handling all kinds of time data, which I believe is a common problem.

At WEST, I’ll be presenting my progress on temporal keys. Temporal keys are used to prevent overlapping periods of time — a schedule conflict — by using an index and following the same concurrent behavior as UNIQUE with minimal performance cost (one extra index search, to be precise).

Temporal keys cannot be expressed in PostgreSQL 8.4, unless you resort to triggers and a full table lock (ouch!). So, additional backend support is required. This is accomplished in my patch for operator exclusion constraints, which are a more general way of using arbitrary operators and index searches to enforce a constraint. I plan to do what’s required for the patch to be accepted in PostgreSQL 8.5.

Temporal modeling is a common problem. It seems like almost every PostgreSQL conference has had at least one talk on the matter, so we know there is some demand for improvement. If you’re interested, I hope you come to WEST and chat with Scott or I, and let’s see if we can come up with some real solutions.