June 22nd, 2010 by depesz | Tags: , , , | 14 comments »
Did it help? If yes - maybe you can help me?

Relatively soon we will have 9.0, and we will move with development to 9.1 (we as in: PostgreSQL community, I don't know C so I can't help developing myself, but I will at least try to keep up with changes in the ‘Waiting for' series).

There are some fabulous plans about new functionality, including security extensions, sync replication, writable cte, sql/med and a lot of other stuff. But while all of these are great, and important, I think that some of these features are rather exotic.

With this in mind, I tried to compile my own, personal, dream wishlist of features that I, personally, would love to get in Pg. These are probably not the most glamorous, or the most asked for, or most complex, or simplest or anything else “best of …". These are just things, that I would love to get to make my life happier/easier.

  • concurrent reindex. Including indexes used by constraints.
  • covering indexes/index only scans. Brilliant idea, and great optimization.
  • concurrent pg_dump. Doesn't need explanation, does it?
  • hstore in core (or some other way to get richer datatypes in plpgsql)
  • ability to iterate over fields of records in plpgsql (which can be solved by above point, or differently)

Don't get me wrong – I will be happy to get writable cte, row-level security and other features. But the points above would make me most happy because they will cut the burden on day-to-day administration tasks.

  1. 14 comments

  2. # Anonymous
    Jun 22, 2010

    Segment Exclusion! It would make maintenance of large tables MUCH easier, as an alternative for explicit partitioning. And also potentially provide many performance benefits.

  3. # tom
    Jun 22, 2010

    SQL/MED is a must have for pushing postgres in the business world. Destination: Replace M$-SQL-Server with postgres 🙂

  4. Jun 22, 2010

    @Anonymous:
    can you explain a bit what would that be, and what it’s for?

    @Tom:
    It’s in the plan, so there is chance it will make it.

  5. # cybernd
    Jun 22, 2010

    My opinion is that sql/med is one of the most important features. It offers a smooth migration path either from postgresql to something else, or from something else to postgresql.

    In case of hstore it might be helpfull to add json capabilities on top of that (there are already projects trying to get this done).

  6. # gregj
    Jun 22, 2010

    with development, not deployment – to 9.1

  7. Jun 22, 2010

    @GregJ:

    Thanks. Fixed.

  8. am I allowed to say I’d like PSM to be added for standards compliance (and mysql parity to help kill mysql)

  9. # Anonymous
    Jun 23, 2010

    @depesz: Segment Exclusion is documented here: http://wiki.postgresql.org/wiki/Segment_Exclusion (first hit in Google 😉

  10. # Dan S
    Jun 23, 2010

    True Serializability would be really nice to have.
    It’s in the SQL standard, and a requirement for a truly ACID compliant database.
    Plus,having tested your transaction alone will guarantee that it will always give the same result in a concurrent environment, or be rolled back.

  11. # os10000
    Jun 23, 2010

    @DanS: my understanding is that PostgreSQL uses optimistic concurrency control for true serialisability (serialisability means that transactions get executed in an interleaved or even parallel fashion, while after the fact an order can be named in which the transactions could have been executed serially to get the exact same result). If you demand pessimistic concurrency control (two phase locking — a lock acquisition phase followed by a lock release phase) then you are simply asking for a performance feature (because in certain workloads you may have to throw away a lot of the optimistically executed transactions).

    @DanS: could you explain a bit more what you mean?

    Thanks,

    Oliver

  12. # Dan S
    Jun 23, 2010

    @OS10000:
    When you ask postgres for serializable isolation you will instead get snapshot isolation which is a lower isolation level.
    That means that you can get anomalies in a concurrent environment which would not happen if your transactions are run in true serializable isolation mode.
    How you achieve serializability doesn’t matter from a correctness point (but probably from a performance point).
    There has been a discussion on postgres hackers list about a new technique which could permit higher concurrency than an ordinary 2 phase locking implementation.
    There is also a wiki page you can read:
    http://wiki.postgresql.org/wiki/Serializable

  13. # M.G.
    Jun 24, 2010

    Updatable views please!

  14. Jun 25, 2010

    Yah I can think about a 1000 things I’d like

    1) SQL/MED would be big since that is one thing I think SQL Server does much better than other PostgreSQL. I don’t know much about SQL/Med standard, but SQL Server linked server feature just rocks and I miss that kind of ease in PostgreSQL

    2) CREATE TABLE IF NOT EXISTS — like what MySQL has. I know you can sort of mimick the behavior now with the DO command, but its not quite as simple and well defined for such a common place thing.

    3) Synonyms. Oracle has synonyms, SQL Server has synonyms, DB2 has them, PostgreSQL needs them. I know it will make PostGIS life a lot easier for migration when we finally bite the bullet and define our own schema or rename things (so we can maintain backward compatibility for those who really need it)

    It makes a lot of other things easier too where you don’t want to force everyone to do a search_path a certain way or schema qualify everything, but you have certain functions/tables everyone needs to see.

  15. # Anonymous
    Jul 28, 2010

    Operationally speaking, the following would be by far the most helpful in my mind:

    1) concurrent/online (re)index
    2) concurrent/online alter table
    3) concurrent/online table & index movement to another tablespace

    I am willing to pay the performance cost necessary (hopefully only during the DDL =) for these features. They are very important for real-time read/write typical web workloads that are important to me.

Leave a comment