Category Archives: Percona Blogs

Postgresql_fdw Authentication Changes in PostgreSQL 13

PostgreSQL 13 is released with some cool features, such as index enhancement, partition enhancements, and many others. Along with these enhancements, there are some security-related enhancements that require some explanation. There are two major ones: one is related to libpq and the other is related to postgres_fdw. As it is known that postgres_fdw is considered to be a “reference implementation” for other foreign data wrappers, all other foreign data wrappers follow their footsteps in development. This is a community-supported foreign-data wrapper. The blog will explain the security changes in postgresq_fdw.
Read More

Migrating to PostgreSQL Version 13: Incompatibilities You Should Be Aware Of

The PostgreSQL 13 Beta is out in the testing phase with a rich feature set. It is a very good learning effort to participate in the testing of one of the finest databases in the world. It does not matter how much development, coding, and administration experience you have for the testing of the PostgreSQL version; you can participate in reviewing the documentation, validation of features, and on some small tasks. The complete testing guide is also available on the wiki page.
Read More

Multi-Master Replication Solutions for PostgreSQL

Due to the immense generation of data, scalability has become one of the hottest topics in the field of databases. Scalability can be achieved horizontally or vertically. Vertical scalability means adding more resources/hardware to existing nodes to enhance the capability of the database to store and process more data, for example, adding a new process, memory, or disk to an existing node. Every DBMS engine improves the capability of vertical scalability by improving the locking/mutex mechanisms and the concurrency by which it can use the newly added resources more effectively. The database engines provide configuration parameters, which helps to utilize the available hardware resources more effectively.

Read More

10 Common PostgreSQL Errors

Sometimes PostgreSQL users get errors and warnings and they are unable to understand why. To cater to these situations, this blog will cover some common errors and warnings in PostgreSQL. In some cases, it is a user setting problem or query error, but in other cases, it can be a PostgreSQL bug. But, it is quite rare to be a PostgreSQL bug, and therefore it is really important to differentiate between user error and PostgreSQL bug. Here is a list of some common PostgreSQL errors, with symptoms and solutions.
Read More

Useful Queries For PostgreSQL Index Maintenance

PostgreSQL has a rich set of indexing functionality, and there are many articles explaining the syntax, usage, and value of the index. In this article, I will write basic and useful queries to see the state of database indexes. People develop databases and after some time, when there is a demand to do changes in the architecture of software, they forget to do the previous indexes’ cleanup. This approach creates a mess and sometimes slows down the database because of too many indexes. Whenever we do an update or insert, the index will be updated along with the actual table, therefore there is a need for cleanup.
Read More

Handling NULL Values in PostgreSQL

There is often some confusion about NULL value, as it is treated differently in different languages. So there is an obvious need to clarify what NULL is, how it works in different languages, and what the actual value is behind the NULL. Before going into details, there is also a need to understand the concept of Three-valued logic[1] and Two-valued logic known as bivalent[2]. The bivalent is a concept of boolean value where value can be true or false, but contrary to bivalent the Three-valued logic can be true, false, or (intermediate value) unknown. Now, back to NULL. In some languages NULL acts as bivalent, and in others, Three-valued logic (especially in databases).

Read More

Proposal for Global Indexes in PostgreSQL

A global index, by very definition, is a single index on the parent table that maps to many underlying table partitions. The parent table itself does not have a single, unified underlying store so it must, therefore, retrieve the data satisfying index constraints from physically distributed tables. In very crude terms, the global index accumulates data in one place so that data spanning across multiple partitions are accessed in one go as opposed to individually querying each partition.
Read More

A Faster, Lightweight Trigger Function in C for PostgreSQL

We have been writing blog posts about how to write simple extensions in C language and a little more complex one by Ibrar which were well received by PostgreSQL user community. Then we observed that many PostgreSQL users create simple triggers for small auditing requirements, and then feel the pain of trigger on transactions. So we were discussing how simple/lightweight and faster a trigger function is when written in C. Generally, Trigger functions are written in high-level languages like PlpgSQL, but it has a higher overhead during execution and it can impact the transactions – and thereby application performance.
Read More

How to Adjust Linux Out-Of-Memory Killer Settings for PostgreSQL

In a Linux world, whenever your database server crashes or gets terminated, you need to find its cause. There can be several reasons for this. It can be SIGSEGV, which is a crash due to some bug in the backend server, but this is the least likely reason. The most common reason is running out of disk space or running out of memory. If you are getting the “running out of space” error, the only solution is to clear some space and restart your database.

Read More

Parallelism in PostgreSQL

PostgreSQL is one of the finest object-relational databases, and its architecture is process-based instead of thread-based. While almost all the current database systems utilize threads for parallelism, PostgreSQL’s process-based architecture was implemented prior to POSIX threads. PostgreSQL launches a process “postmaster” on startup, and after that spans new process whenever a new client connects to the PostgreSQL.
Read More

Deep Dive Into PostgreSQL Indexes Webinar: Q & A

I want to thank everybody who attended my session “Deep Dive in PostgreSQL Indexes” in Austin. It was quite a wonderful experience! To cover a bigger audience, I also did a webinar on the same topic. There were many questions in that webinar, but unfortunately, there was not enough time to cover each and every question, so I decided to have a followup Q&A session with a blog post on the topic.
Read More

Improving OLAP Workload Performance for PostgreSQL with ClickHouse Database

Every database management system is not optimized for every workload. Database systems are designed for specific loads, and thereby give better performance for that workload. Similarly, some kinds of queries work better on some database systems and worse on others. This is the era of specialization, where a product is designed for a specific requirement or a specific set of requirements. We cannot achieve everything performance-wise from a single database system. PostgreSQL is one of the finest object databases and performs really well in OLTP types of workloads, but I have observed that its performance is not as good as some other database systems for OLAP workloads. ClickHouse is one of the examples that outperforms PostgreSQL for OLAP.
Read More

Benchmark ClickHouse Database and clickhousedb_fdw

In this research, I wanted to see what kind of performance improvements could be gained by using a ClickHouse data source rather than PostgreSQL. Assuming that I would see performance advantages with using ClickHouse, would those advantages be retained if I access ClickHouse from within postgres using a foreign data wrapper (FDW)? The FDW in question is clickhousedb_fdw – an open source project from Percona!
Read More

PostgreSQL CommitFest 2019-03 Ends

PostgreSQL has a very unique way to review the code submitted by developers. Most open source software uses Github pull requests to accommodate users’ code. PostgreSQL has a Github page but doesn’t manage pull requests using Github. Many years ago, PostgreSQL introduced CommitFest to manage its patches, where postgres opens for a three to four month “festival” to accept patches. The CommitFest is set up to keep track of patches’ statuses. In practice, CommitFest is mainly an admin page used to manage patches. Volunteers and community committers review the submitted code, after which committers are able commit those patches that have been approved. There have been almost 22 CommitFest events since 2014.

Read More

Writing PostgreSQL Extensions is Fun – C Language

PostgreSQL is a powerful open source relational database management system. It extends the SQL language with additional features. A DBMS is not only defined by its performance and out of the box features, but also its ability to support bespoke/additional user-specific functionality. Some of these functionalities may be in the form of database constructs or modules, like stored procedures or functions, but their scope is generally limited to the functionality being exposed by the DBMS. For instance, how will you write a custom query-analyzing application that resides within your DBMS?

Read More

PostgreSQL: Access ClickHouse, One of the Fastest Column DBMSs, With clickhousedb_fdw

Database management systems are meant to house data but, occasionally, they may need to talk with another DBMS. For example, to access an external server which may be hosting a different DBMS. With heterogeneous environments becoming more and more common, a bridge between the servers is established. We call this bridge a “Foreign Data Wrapper” (FDW). PostgreSQL completed its support of SQL/MED (SQL Management of External Data) with release 9.3 in 2013. A foreign data wrapper is a shared library that is loaded by a PostgreSQL server. It enables the creation of foreign tables in PostgreSQL that act as proxies for another data source.
Read More

Settling the Myth of Transparent HugePages for Databases

The concept of Linux HugePages has existed for quite a while: for more than 10 years, introduced to Debian in 2007 with kernel version 2.6.23. Whilst a smaller page size is useful for general use, some memory intensive applications may gain performance by using bigger memory pages. By having bigger memory chunks available to them, they can reduce lookup time as well as improve the performance of read/write operations. To be able to make use of HugePages, applications need to carry the specific code directive, and changing applications across the board is not necessarily a simple task. So enter Transparent HugePages (THP).

Read More

Upcoming Webinar Friday 1/4: High-Performance PostgreSQL, Tuning and Optimisation Guide

PostgreSQL is one of the leading open-source databases. Out of the box, the default PostgreSQL configuration is not tuned for any workload. Thus, any system with least resources can run it. PostgreSQL does not give optimum performance on high permanence machines because it is not using the all available resource. PostgreSQL provides a system where you can tune your database according to your workload and machine’s specifications. In addition to PostgreSQL, we can also tune our Linux box so that the database load can work optimally.
Read More

Benchmark PostgreSQL With Linux HugePages

Linux kernel provides a wide range of configuration options that can affect performance. It’s all about getting the right configuration for your application and workload. Just like any other database, PostgreSQL relies on the Linux kernel to be optimally configured. Poorly configured parameters can result in poor performance. Therefore, it is important that you benchmark database performance after each tuning session to avoid performance degradation. In one of my previous posts, Tune Linux Kernel Parameters For PostgreSQL Optimization, I described some of the most useful Linux kernel parameters and how those may help you improve database performance. Now I am going to share my benchmark results with you after configuring Linux Huge Page with different PostgreSQL workload. I have performed a comprehensive set of benchmarks for many different PostgreSQL load sizes and different number concurrent clients.
Read More

Tuning PostgreSQL Database Parameters to Optimize Performance

Out of the box, the default PostgreSQL configuration is not tuned for any particular workload. Default values are set to ensure that PostgreSQL runs everywhere, with the least resources it can consume and so that it doesn’t cause any vulnerabilities. It has default settings for all of the database parameters. It is primarily the responsibility of the database administrator or developer to tune PostgreSQL according to their system’s workload. In this blog, we will establish basic guidelines for setting PostgreSQL database parameters to improve database performance according to workload.

Read More