Percona Open Source Database Conference 2019

Percona, a leading provider of unbiased open source database solutions, organized its yearly European Open Source Database Conference in Amsterdam from September 30th till October the 2nd. This conference is an excellent opportunity to discover and discuss the latest open source trends, technologies and innovations, and of course Percona’s own solutions. After Amsterdam in 2016 and Dublin in 2017, it was the third time Ordina JWorks signed up for this conference so it is gradually becoming a tradition.

Table of contents

Introduction

Nowadays, a company has to make many choices when determining an optimal database strategy. For example, does a company choose to lower the TCO (Total Cost of Ownership) or avoid vendor lock-in by switching from a commercial to an open source database management system? Another choice your company faces is whether a relational or non-relational database management system or both should be chosen. And does a company bring its data to the cloud, or keep it on-premise? Choosing the right solution for the database strategy can sometimes be overwhelming.

We looked for answers to these questions during the Percona Open Source Conference. Of course this conference is not only an excellent opportunity for Percona to showcase its own products such as Percona Server for MySQL, Percona XtraDB Cluster, Percona Backup for MongoDB and much more, but it also shows that there are many interesting things to tell about databases and more specifically about open source databases. As a supporter of commercial database systems, you’re not in the right place here.

As in previous editions of the conference, the concept remains unchanged.
The conference starts with a ‘Tutorials Day’ on the first day followed by 2 days of actual conference. Percona is the organizer of the conference so their tools and services will be discussed in detail, but with about 90 talks about everything that has to do with relational and non-relational open source databases, everyone will get their money’s worth. It only makes it more difficult to choose which sessions to attend.

Keynotes

Each self-respecting conference starts with one or more keynotes. Percona Live was no exception to this and kicked off with 3 keynotes :

The first keynote was a great plea for the cloud and open source software and the benefits of both were well discussed. Also it mainly summarised the results of The Open Source Data Management Software Survey conducted by Percona in the run-up to the conference. More insights and useful information can be found in the Open Source Data Management Software Survey.

To gain a good insight into how the open source database landscape is evolving, various sources such as db-engines and Stack Overflow were consulted. The graph below shows the most commonly used databases according to Stack Overflow.

In these disruptive times, the popularity of open source databases is undoubtedly increasing. But how are the elephant, dolphin and sea lion currently doing? Which one is the most popular and how do they relate to each other? A small tour along the mini zoo of relational open source databases.

The state of the elephant: PostgreSQL

PostgreSQL, as we know it under this name, is celebrating its 24th anniversary this year and the chart below clearly shows that its popularity is still growing. According to DB-engines that measures database popularity, PostgreSQL is now the fourth most popular database, after Oracle, MySQL and Microsoft SQL Server, and was referred to as ‘Database of the year’ in both 2017 and 2018. In 2019 it had to pass on this title to MySQL. The last major release, version 12, was launched the day after the Percona conference.

Although the focus of the conference was on MySQL, PostgreSQL was also well represented at the conference with more than 37 talks on the subject. The PostgreSQL sessions carried away my preference not least because it is a popular migration target, but also because the popularity increased in the most recent years, many developers think it is the coolest database out there.

Day 1 (Tutorials):

  • PostgreSQL For Oracle and MySQL DBAs and For Beginners

    From a Database administrator point of view this was the most interesting training. After a brief overview of the different features within Postgres, the specific terminology was also discussed.
    Within this training the necessary attention was also paid to the installation of PostgreSQL, the architecture, backup and partitioning within PostgreSQL.

  • Introduction to PL/pgSQL Development

    While the morning tutorial was intended for DBAs, the developers were given due attention in the afternoon. The standard PostgreSQL distribution includes four procedural languages, PL/Tcl, PL/Perl, PL/Python and PL/pgSQL. In addition, there are a number of procedural languages that are developed and maintained outside the core PostgreSQL distribution, like PL/Java, PL/PHP, PL/Ruby and a few others. This tutorial only focused on development in PL/pgSQL. Because of the many similarities between PL/pgSQL and the programming language of Oracle PL/SQL, this 3-hour session was a good repetition for a developer experienced in PL/SQL programming. Fortunately, the differences between the two were also sufficiently addressed. And just these differences are important if you want to port an application from Oracle to PostgreSQL.

Day 2:

  • Why PostgreSQL is Becoming A Migration Target in Large Enterprises

    The driving reasons for migrating to PostgreSQL are not only initiated from the management of an organization, who is particularly interested in the lower TCO, the no-vendor lock-in and the licencing policy. Also developers, operation teams, architects all have their reasons to migrate from proprietary databases or noSQL databases to PostgreSQL. PostgreSQL is not only the most loved relational database among developers, it contains the latest SQL standards, contains many programming languages and it supports JSON. Operation teams especially appreciate the legendary stability, the options for High Availability and the good backup tools. For architects, the support of procedures in PostgreSQL is very interesting. These are very helpful for Oracle to PostgreSQL migration. The interoperability and sharding capabilities are also a plus. The session ended with a demo of Oracle Foreign Data Wrappers.

  • Pg_catalog Unrevealed! That Part of PostgreSQL You Are Probably Underusing

    This talk was intended to review the possibilities offered by the PostgreSQL catalog. Plenty of information is available there but it is unknown to many users, and therefore unused. The speaker demonstrated in an enlightening way how to exploit the catalog, how to send the information to other monitoring tools, and how the tables in pg_catalog are fundamentally interconnected to other topics such as performance, replication, MVCC, security, the universe and everything.

  • Join Heterogeneous Databases Using PostgreSQL Foreign Data Wrappers

    I followed this presentation because I was especially interested in how PostgreSQL can communicate with other databases like Oracle, MySQL, PostgreSQL itself or even NoSQL data sources like MongoDB. The feature to achieve this are Foreign Data Wrappers. Simply put, it allows you to create foreign tables in a PostgreSQL database that are proxies for some other data source. With a few examples it was shown how you can easily set up these Foreign Data Wrappers. An extensive overview of the different data wrappers that exist can be found on the wiki.

Day 3:

  • Parted Ways with Partitioning? It’s Time to Reconsider

    When a DBA has to deal with very large tables of which the performance has to be improved, partitioning comes into the picture. Partitioning splits a table into multiple tables, and is generally done in a way that applications accessing the table don’t notice any difference, other than being faster to access the data that it needs. Before PostgreSQL 10, table partitioning in PostgreSQL was only possible via table inheritance, but with release 10, declarative partitioning made its appearance, which actually means that natively partitioned tables were introduced. Declarative partitioning in PostgreSQL 10 initially provided built-in support for Range partitioning and List partitioning and in version 11 has also been added. Many more improvements were added. During this talk it was shown how Partitioning has evolved within the latest versions of PostgreSQL.

  • Handling Transaction ID Wraparound in PostgreSQL

    Transaction IDs in PostgreSQL are stored in a 32-bit counter. But that involves potential risks.
    Without any form of intervention, transaction ids could get exhausted after 2 billion transactions. A way to deal with this is described in detail on the following blog    .

  • Top 10 Mistakes When Migrating From Oracle to PostgreSQL

    Very interesting session that exposes a handful of grammatical differences, for which the functionality is the same, between both database systems. This way you learn, for example, that synonyms or nulls do not exactly mean the same thing in Oracle and PostgreSQL.

  • PostgreSQL Plan at Execution Time: A Quick Show

    The last lightning talk about PostgreSQL on the conference showed a look behind the scenes of Explain plan in PostgreSQL and what decisions could be made from it during the execution phase. This was demonstrated by means of a demo.

Migrating to PostgreSQL

PostgreSQL has become a mature database and is known for its reliability, feature robustness, and performance. Able to handle all levels of workload with thousands of tools, extensions, connectors and community-contributed add-ons, it is a popular choice for Oracle database migration. But one of the main drivers is of course the cost, because Oracle’s licensing model is often a nightmare. PostgreSQL commends itself as the world’s most advanced open source relational database.

So when the company’s management has decided to migrate to PostgreSQL, is it a difficult task as Oracle DBA to make the switch? Not really. Because the fundamentals and features remain largely the same, the learning curve is not steep at all. A good starting point is the wiki of Postgres.

There are several tools on the market that can assist you in converting an Oracle database into a PostgreSQL database, but keep in mind that there is no tool that can convert a 100% Oracle database into PostgreSQL. Some manual changes are required. Below is a list of frequently used conversion tools, but a detailed description of each tool is beyond the scope of this blog post.

The pitfalls you often encounter as an administrator were discussed in the session “Top 10 Mistakes When Migrating From Oracle to PostgreSQL”.

The state of the dolphin: MySQL

In 2019, MySQL took over the torch again from PostgreSQL as Database of the year. MySQL is one of the most widely used databases in the world and, in terms of popularity, only has to accept Oracle in front of them. The latest release, release 8, was released on April 19, 2018. With no less than 250 new features it was a big leap forward from version 5.7. An overview of these new features can be found here. During several sessions some of these new features were discussed in more detail.

A selection of some interesting talks on MySQL :

The state of the sea lion: MariaDB

From the MariaDB graph we can deduce that the popularity has risen sharply in recent years, but the rate of increase has slowed down over the past year. MariaDB has not followed the same release path as MySQL in recent years and the latest stable release of MariaDB is 10.4.

There were only a handful of sessions on MySQL’s fork at the conference. But with a talk that takes a look at the query optimizer of MariaDB and another one that goes into the security setup within MariaDB, two very interesting topics were covered.

To see the full schedule of the Conference have a look here: Percona Live Europe Schedule.

Useful links & further reading

Dieter is Oracle DBA at Ordina Belgium. He has a very strong interest in Open Source RDBMS and Next generation databases.