Not a lot of people remember what Postgres was like before version 8.4. In many ways, this was the first “modern” release of the database engine. CTEs, Window Functions, column level permissions, in-place upgrade compatible with subsequent versions, collation support, continuous query statistic collection; it was just a smorgasbord of functionality.
Of these, CTEs or Common Table Expressions, probably enjoy the most user-level exposure; for good reason. Before this, there was no way to perform a recursive query in Postgres, which really hurts in certain situations.
The United States held an election recently, and there has been some … mild controversy regarding the results. Many raised issues about this before the election itself, but what if we had used instant-runoff voting instead? More importantly, can we implement it with Postgres?
Well, the answer to the last question is a strong affirmative. So long as we don’t break the results down into voting districts, and make wild unsupported assumptions regarding rankings, that is.
With the release of PostgreSQL 8.4, the community gained the ability to use CTE syntax. As such, this is a fairly old feature, yet it’s still misunderstood in a lot of ways. At the same time, the query planner has been advancing incrementally since that time. Most recently, PostgreSQL has gained the ability to perform index-only scans, making it possible to fetch results straight from the index, without confirming rows with the table data.
