Saturday, September 28, 2013

Features still wanted

I was uploading my recent slide decks to SlideShare and noticed I had the deck there from a talk I gave about 4 years ago at TriLug about new features in Postgres 8.4. In there is a slide (#24) labeled "Major missing SQL features" and containing these two items:
  • grouping sets
  • merge
Sadly, four years later those features are still missing. And they are still the thing I think are the most urgent new SQL features we should provide.  Both are hard to do well, and probably both require major funding kicks to have any chance of being done.

5 comments:

  1. There are a few corner cases to composite types that make them unusable in production - especially for primary keys. To me, joining on composite typed keys is a thing of beauty when compared to joining ordinary composite field keys.

    Unfortunately, I expect there isn't enough demand composite primary keys to warrant such a feature. :(

    ReplyDelete
  2. So MERGE (https://wiki.postgresql.org/wiki/Add_MERGE_command_GSoC_2010) has not been added to PostgreSQL?

    ReplyDelete
    Replies
    1. No. IIRC it was found not to be of adequate quality, and there are various other questions not yet resolved about exactly what behaviour we want to implement.

      Delete
  3. Table partitioning. We need a real world table partitioning which is not based on the assumption that the table will be split into just 10 or 12 subtables but rather in undreds or even thousands.
    Bookkeeping and stock control, for example, require billions of lines thus asking of a partitioning schema going, for example, along with weeks over 5+ years. That's 200+ subtables.
    Whatever implementation you choose (see the friendly manual), either you have a complex (and yet manual) and optimized partition management or a simple linear one.
    The CHECK() predicate (just like partial non-overlapping indexes) doesn't bring you any sub-linear optimization.
    Table partitioning is thus more an academic topic than a real world application.
    The question should not be "how does oracle implement it" but rathger "how do we implement it.
    Unluckily I have not enough expertise with PG source code and RDBMS programming to jump into that.

    ReplyDelete
  4. Andrew,

    Andrew Gierth is working on Grouping Sets for 9.4. I'm sure he'd be happy to have help, at least code review.

    ReplyDelete