Hello!

My name is Lauri Keel and I design and build all kinds of IT systems.

Websites, mobile apps, integrations, even hardware prototypes. Let's talk

Standalone DNS Authenticator plugin for Certbot

A plugin that uses an integrated DNS server to respond to the _acme-challenge records. Written in Python.

Gradle JavaScript Toolkit

A full solution for Gradle-based JavaScript development. Written in Groovy, JavaScript.

PostgreSQL VelocyPack extension

Adds the VelocyPack format support to PostgreSQL. Generally it is a jsonb analogue with more data types and support for logical types or tags. And faster. Written in C.

PHP VelocyPack extension

Adds support for the VelocyPack format to PHP. Also ships with a tags-based type mapper for more precise type or object document mapping. Written in C.

Design and Implementation of a Document Store Extension for PostgreSQL

tl;dr

Added VelocyPack format support to PostgreSQL, implemented a benchmarking framework and evaluated the effect of various ZFS filesystem, operating system and database parameters to the performance of different workloads of PostgreSQL and MongoDB. PostgreSQL is at least twice as fast as MongoDB.

Summary

The objective of this thesis was to enable using PostgreSQL as a full fledged document store and evaluate the effect of different system configurations to its performance. In the first chapter an overview was given about the background of the problem, different data serialization formats were studied and their suitability for using in a document store evaluated. The importance of data statistics was explained, different ways to evaluate the performance of document stores were studied and an overview was given about different database, filesystem and operating system configuration parameters that affect performance.

In the second chapter the relevant data serialization formats were analyzed and one chosen for implementation. Only one suitable format was found – Amazon Ion – but due to the lack of documentation for its C implementation it was found to be more efficient to modify another one – VelocyPack. Additionally, it was studied how to reuse the existing PostgreSQL statistics system and rules for destructuring documents were created that are the basis of the statistics generation system. In the third part of the chapter it was found that none of the existing performance testing frameworks are suitable for the current use case and it was decided to create a new one, so the requirements for the testing framework and conducting the tests were specified.

In the third chapter the required changes to VelocyPack and the PostgreSQL extension for the new data type, relevant operators, indexing and document statistics were engineered along with the testing framework and tests automation system. Additionally, it was explained how to carry out the tests with minimal external interference.

In the fourth chapter the implementation of the PostgreSQL extension, testing framework and the tests automation system were described in more detail. Additionally, the results of the testing framework were validated by a comparison with those of the YCSB framework and the results found to be satisfying.

In the fifth chapter the results of the performance tests were evaluated and found that PostgreSQL is at all aspects significantly faster than the current most popular document database – MongoDB. But the objective of the performance tests was not confirming that well-known fact, but evaluating the effect of different database, filesystem and operating system configurations to performance and give guidelines for further testing. It was found, that:

• the record size of the ZFS filesystem must be carefully selected according to the desired compression ratio and the throughput of the hard disk and the interface used; for PostgreSQL, the recommended record size for write-ahead log is 8KiB or 128KiB;
• using reduced data security (synchronous_commit=off) gives a write performance benefit of more than 3 times;
• too big shared_buffers cause a significant performance decline when their hit rate is low;
• the performance of PostgreSQL stays stable under high concurrency;
• raising concurrency does not bring any significant gain to MongoDB performance;
• using logbias=throughput for the database dataset does not bring any performance benefits nor changes in disk usage;
• the MongoDB in-memory storage engines are not usable due to an anomaly appearing when inserting data that causes high latency;
• using the jsonb format is 4-15% slower compared to the one implemented in this thesis;
• PostgreSQL is significantly faster than MongoDB in all tests;
• it does make sense to invest in a fast buffer-less or supercapacitor-backed SSD for storing the ZFS Intent Log (SLOG) – the difference in performance is more than two times;
• document size significantly affects the read and write performance of PostgreSQL;
• PostgreSQL achieves the lowest and most stable latency of all tested software.

Each application and their workloads are different and with differnet level of compexity, so the settings need to be evaluated for those particular workloads and applications. The results of the performance tests of this thesis give guidelines which aspects should be tested and how to choose the baseline scenario. The effect of many configuration options was already known, but the exact estimation was missing.

All objectives were achieved: PostgreSQL can store complex documents while keeping the original data types of its values, the query planner can make smarter decisions based on data statistics and the implementation has the best performance of the compared solutions.

Download: full thesis, PostgreSQL VelocyPack extension

• Optimizing existing systems
• Configuring and managing Linux-based servers
• Getting rid of anomalies in your WordPress installation
• Integrations with payment gateways, ERP and other external systems
• Hardware prototyping and programming (Arduino, ESP8266)
• Web scrapers for downloading data from external websites

Lauri Keel. Development of different IT solutions. Let's talk