PGConf.Russia 2018

Pluggable storages is hot subject in PostgreSQL development. The period of heated debates about whether we need them is over. Skepticism about pluggable storages, based on concern that they may be source of inconsistent behavior, was weakened after criticism of PostgreSQL MVCC implementation from Uber side. It became widely understandable that pluggable storages are needed at least for an alternative MVCC implementation. And that is one of way-points for pluggable storages interface design.

At the moment, work on pluggable storages is in the practical stage. There is a thread is pgsql-hackers where few people are developing patchset and several people are doing review.

This talk will cover following subjects:

• overview of pluggable storages interface;
• changes in PostgreSQL core required to implement this interface;
• current and potential implementations of pluggable storages including heap with undo-log and in-memory OLTP engine;
• current state of patchset and prospective of its commit
• further development of interface allowing more possibilities in pluggable storages (columnar, index-organized, LSM and so on).

It's not a secret for anyone that statistics can not be transferred with a major upgrade. For small and not heavily loaded databases this is not a problem, you can quickly collect new statistics. But we have databases with a volume of about 5TB and a load of about 100k rps, for which it became a big problem: taking off without statistics, the replicas could not even replay WAL. In my report I'll tell you what tricks we went to upgrade these databases with requirements of 100% read only availability, about what mistakes were made, and about how these errors were painfully corrected. The result of these errors was the extension called "pg_dirty_hands", in which we will collect various hacks, which can be last resort to repair data corruption.

Jsonb is a popular data type in PostgreSQL, it provides the web developers an ability to work with ubiquitous json inside the database and use all the power of proven relational database. Fast querying of jsonb data is a challenge for database and PostgreSQL provides several options for indexing jsonb. We present the new way of efficient indexing of jsonb, based on improvement of indexing infrastructure.

It's known, that json is a greedy data type, it may contains many auxiliary data not interesting for searching and that affects the size of index. Partial index will not helps, since it filters the rows before indexing, while we are interested in extracting of parts of jsonb. Functional indexes on specific keys could introduce too big overhead. We present an improvement of indexing infrastructure, which allows to control the index behaviour by passing parameters to operator class at index creation. For example, to index a user-defined subset of jsonb it is possible to pass to operator class the powerful path expression (either jsonpath of upcoming sql/json or jspath from jsquery extension), which can be used to extract the parts of jsonb tree. That makes index more effective and reduces the overhead of its maintaining.

Another use of parameterized operator classes is to allow a user to specify parameters instead of hard coding them, for example, the GiST signature size is currently hard coded inside the implementations of several opclasses (tsvector, hstore, intarray, pg_trgm, ltree), while it is natural to use different signature length for different data to have optimal size of index and its performance.

Full text search on parts of document can be improved by passing labels to the operator class and letting him index only specified parts of document, that allow to avoid currently used recheck of the rows returned by the index.

One of advantages of document-oriented databases like MongoDB or Couchbase over RDBMSs is an ability to change the data scheme easily, fast and often. The traditional approach in RDBMS world involves doing an expensive ALTER TABLE operation, slow upgrade of an existing data, and stuff like this. This approach is often slow and inconvenient for application developers.

To solve this issue PostgreSQL provides JSON and JSONB datatypes. Also there are extensions like zson and pg_protobuf. From this talk you will learn how to work with these datatypes and extensions, their pros and cons and also related future work in this area.