Thursday, May 16, 2013

PostgreSQL New Development Priorities 2: Pluggable Storage

Over the last decade, Greenplum, Vertica, Everest, Paraccel, and a number of non-public projects all forked off of PostgreSQL.  In each case, one of the major changes to the forks was to radically change data storage structures in order to enable new functionality or much better performance on large data.  In general, once a Postgres fork goes through the storage change, they stop contributing back to the main project because their codebase is then different enough to make merging very difficult.

Considering the amount of venture capital money poured into these forks, that's a big loss of feature contributions from the community.  Especially when the startup in question gets bought out by a company who buries it or loots it for IP and then kills the product.

More importantly, we have a number of people who would like to do something interesting and substantially different with PostgreSQL storage, and will likely be forced to fork PostgreSQL to get their ideas to work.  Index-organized tables, fractal trees, JSON trees, EAV-optimized storage, non-MVCC tables, column stores, hash-distributed tables and graphs all require changes to storage which can't currently be fit into the model of index classes and blobs we offer for extensibility of data storage.  Transactional RAM and Persistent RAM in the future may urge other incompatible storage changes.

As a community, we want to capture these innovations and make them part of mainstream Postgres, and their users part of the PostgreSQL community.  The only way to do this is to have some form of pluggable storage, just like we have pluggable function languages  and pluggable index types.

The direct way to do this would be to refactor our code to replace all direct manipulation of storage and data pages with a well-defined API.  This would be extremely difficult, and would produce large performance issues in the first few versions.  It would, however, also have the advantage of allowing us to completely solve the binary upgrade of page format changes issue.

A second approach would be to do a MySQL, and build up Foreign Data Wrappers (FDWs) to the point where they could perform and behave like local tables.  This may be the more feasible route because the work could be done incrementally, and FDWs are already a well-defined API.  However, having Postgres run administration and maintenance of foreign tables would be a big step and is conceptually difficult to imagine.

Either way, this is a problem we need to solve long-term in order to continue expanding the places people can use PostgreSQL.


  1. Absolutely.

    That's the reason why I pointed out that it would be worth keeping the storage engine pluggable.

    Re: Adding probes for smgr

    I wish we will have a chance to discuss that at Ottawa. :)

  2. There is also one other thing that would help. Implement the proper collations independent from the underlying OS. Like what MSSQL and MySQL/MariaDB do.

    We are in the last stages of developing an SaaS and are now undergoing an painstaking process of switching to the MariaDB, just because the PostgreSQL doesn't allow us to have one universal collation e.g Unicode.

    1. Can you go into more detail on this? PostgreSQL has full support for Unicode -- better than MySQL's, actually -- so I'm not clear on what you're complaining about.

    2. You are right, en_US.UTF-8 on modern Linux will sort better than unicode collation in mysql. which says that glibc maintainers have done really good job (better than Mysql/mariadb guys) at implementing DUCET and I have just confirmed that. At least to me the characters from European locales seem to get sorted and upper/lower cased properly. What's really missing is that you could say that the locale is case insensitive so there would be no need to upper/lowercase the strings in each and every query or each and every column inside index. The other thing is that would be nice is to have the locales where you could say that some characters should be taken into an account when sorting. e.g. +-& , punctuations ..

    3. There's a couple of bits of work in that direction. There's the citext extension for case-insensitive text, and the unaccent extension for collating text regardless of accenting (aimed mainly at French). One thing to consider is that not all languages regard upper and lower case characters as equivalent, and many don't have cases at all. It's a complicated set of problems, and PostgreSQL labors under a relentless perfectionism in trying to make it work exactly right.

    4. We have patch for years, which implements case-insensitive text with ICU suppport, which is OS-independent. You can download it and use as extension (mchar datatype).

  3. Is this article related in how Bizgres initially developed and currently is not maintained?

    As the main developer of Bizgres, can you tell me the history of it and why it can not be maintained?

    1. It is related. As a simple summary, Bizgres was a casualty of the increasing difficulty of merging mainstream Postgres with the Greenplum code. Pluggable storage would have helped that a LOT, and Greenplum would have contributed more code than they did to PostgreSQL.

      Of course, Bizgres also dies because I left Greenplum.

  4. They gave me a bunch of details & storage options, and offered to help more if I called back. If only other Companies had such service, I would never leave them.
    self storage in paddington