Thursday, January 17, 2013

PostgreSQL 9.3: Current Feature Status

So we're finally beginning the final playoffs for 9.3 features, otherwise known as "CommitFest 4".  Here's where we are on the various features which have been discussed, proposed, submitted, or thought about for 9.3.  The "likely/uncertain" is my own judgement based on the activity I've seen on the feature so far, and may not reflect the project consensus (although it does largely reflect the "needs review/ready for committer" split on those patches).

I've also probably missed stuff. Mostly this is by not seeing some patches as significant, which actually are.

As you can see, what's already committed would make a respectable release for any other DBMS.  But here at PostgreSQL, we don't believe that there's any such thing as "enough features".   If your database can't process 16TB of data with six nines of uptime, parallelize all your application logic, make breakfast and drive the kids to school without waking you up, we still have work to do!

Already Committed:
  • Use POSIX shared memory: no more SHMMAX
  • LATERAL subqueries
  • Streaming-only Cascading and Remastering
  • extend large object access to 4TB (was 2GB)
  • Improve pg_upgrade performance
  • COPY FREEZE mode
  • Automatically updatable views
  • Create your own background workers
  • configuration file directories
  • additional ALTER, IF NOT EXISTS objects
  • pg_basebackup generates replica configuration
  • pg_terminate_backend your own queries
  • SP-GiST indexing for Range Types

Pending CF4, Looking Likely:
  • Writeable Foreign Tables
  • PostgreSQL database federation (pgsql_fdw)
  • Event Triggers
  • pg_retainxlog
  • Improved Foreign Key locking (FOR KEY SHARE)
  • Recursive VIEWs
  • Improved performance for SP-GiST and Range Types
  • Merging recovery.conf into postgresql.conf
  • pg_ping utility
  • Additional JSON features and built-in functions
  • Updating postgresql.conf from SQL command-line

In CF4, Uncertain:
  • Logical Streaming Replication, and its many dependant patches.
  • Data page checksums
  • Auto-updated Materialized Views
  • Reducing Hint Bit I/O
  • Storing counts, other data in GIN indexes
  • Row-Level Security and dependant SEPgSQL features.
  • xlogreader
  • use pg_receivexlog for promotion
  • Parallel pg_dump

Already Bounced to 9.4:
  • pg_dump file-per-object format ("split")
  • Trim Trailing NULLs optimization
  • Automatic audit logging ("timing events")
  • pg_stats_lwlocks view
  • array foreign keys

Never Got Worked On in 9.3:
  • Query parallelization
  • Merge joins for Ranges
  • Global temp tables
  • Temp tables on Replicas
  • Reordering database columns
  • Spatial JOIN
  • special JSON GiST Indexes
If your favorite feature is in the "never got worked on" or the "already bounced", it's a bit too late to do anything about it. However, if your favorite feature is in CF4 -- especially if it's on the "uncertain" list -- now's the time to get involved.  Download the patch, test the functionality, test performance, find bugs, submit a review, help writing docs (since new features don't get accepted without docs).  After February 15th, it'll be too late, and you'll have to wait for another year.

Regardless of what does or doesn't make it in at this point, we have a great 9.3 expected.


  1. "pg_basebackup starts replica automatically" Not quite. It generates recovery.conf, but it doesn't start it - partially because it's a PITA to do that platform independent...

    pg_cancel_backend() your own queries is in 9.2 already. terminate is new.

  2. Was really hoping for TEMP tables on replicas

  3. I'm particularly excited by "Improved Foreign Key locking (FOR KEY SHARE)", which should address a very common problem I've blogged about here:

    1. Mina, you should help with testing that!

    2. I've already taken 9.3 (from git) for a spin, and it looks very very good for our use cases :)

  4. What do you mean by "Recursive VIEWs"? Normally to read that makes it sound like "recursive functions" (a function that may invoke itself), meaning a VIEW defined over a list of table variables that includes itself, but that doesn't make sense because a VIEW is a variable so would only have a single value at any given time. Or do you mean that the definition of a VIEW may include a WITH RECURSIVE? You should clarify what you mean.

    1. Darren:

  5. Last week, I posted in performace list about transparent table partitioning (a.k.a. auto/easy partitioning). This is a killer feature abandoned by developers. It's a pain to configure and keep partitions with the current way. Another great feature is to know what is the most frequently values searched in a column, helping in creation of partial indexes and partitions.

    1. Those are both ideas which have been kicked around, and there's even been some work done on auto-partitioning. We are a volunteer, non-profit project though, so there's always more TODOs than developers. You can always help things along by participating: coding, reviewing, documenting, and funding.

  6. Hi Josh,

    Thanks for this list. Your mention of logical replication caught my eye. There have been a couple of interesting posts concerning logical change set generation on pgsql-hackers, but I'm unable to find a specification for this feature beyond Simon's slides introductory slides from the last pgcon. Do you know if there is a write-up of the current state of logical replication?

    Cheers, Robert

  7. So is merge/upsert support stalled? The current status of the wiki suggests that it's being actively worked on.