Skip to content

Open source Team

Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.

721 posts since September 2011

Insert…on conflict with partitions finally works in PostgreSQL 11

Another partitioning improvement for PostgreSQL 11: Insert…on conflict is now supported (for most cases) in PostgreSQL 11 thanks to this commit. Lets see how it works. We’ll again use the slightly modified little list partitioned table from the last post, here in PostgreSQL 10: postgres=# select version(); version Insert —————————————————————————————————————————- PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623… 

The WAL segment size becomes changeable in PostgreSQL 11

Up to PostgreSQL 10 the wal segment size was fixed once PostgreSQL was compiled and installed. When you wanted to have something else than the default you needed to tell that to the configure command when building from source, e.g. postgres@pgbox:/home/postgres/postgresql/ [PGDEV] ./configure –with-wal-segsize=64MB For PostgreSQL 11 two commits landed (1 and 2) which change this. Lets have a look.… 

The same MAC address for two interfaces on the same host?

Yes, sounds strange, but exactly this is what we faced today. In the end it is clear and not strange anymore but for a moment we really thought we are hitting a bug in the operating system, which was RedHat 7.4. For the little demo for this post I’ll use CentOS 7.4 but this should not really matter. Lets go.… 

When you execute the same statement thousands of times -> prepare it

Every time you send a SQL statement to PostgreSQL it must be parsed. Parsing is fast, yes, but parsing the same statement a thousand times can quickly sum up to quite some time the database could spend for something else. To avoid that PostgreSQL comes with the prepare statement. Using that you can avoid parsing of statements and only planning… 

Updating the partition key now works in PostgreSQL 11

In the last post about partitioning improvements in PostgreSQL 11 we talked about unique indexes and primary keys on partitioned tables. Both did not work in PostgreSQL 10 but now do in PostgreSQL 11. Another operation that did not work in PostgreSQL 10 and does now in PostgreSQL 11 is: Updating the partition key, which means that a row will… 

What is the maximum in list size in PostgreSQL?

Yesterday, while being at a customer, an interesting question popped up: What is the maximum of in list values in PostgreSQL? I couldn’t answer although I never read somewhere that there is a limit. The following is for fun only and I am not saying that creating huge in lists is a good idea. Lets go. The version I tested… 

Local partitioned indexes in PostgreSQL 11

When declarative partitioning was introduced with PostgreSQL 10 this was a big step forward. But as always with big new features some things do not work in PostgreSQL 10 which now get resolved in PostgreSQL 11. One of those are local partitioned indexes. To make it easier to understand lets start with an example in PostgreSQL 10. A very simple… 

pg_basebackup and redirecting progress messages to a file

Recently I came over that commit and wondered what that is about. The answer is quite simple but I didn’t know that this issue existed. Basically it is about how progress messages are written to screen and how they are written to a file. Lets have a look. When your run pg_basebackup with progress messages and in verbose mode the… 

PostgreSQL 11: Procedures and transaction control

Up to PostgreSQL 10 it was not possible to create procedures in PostgreSQL. Of course you can create functions which do not return anything but the possibility to create a procedure was not there. That will probably change in PostgreSQL 11 when nothing happens which will lead to the removal of that commit. On top of that there was another… 

Finally you will be able to use exit and quit in psql

When giving the PostgreSQL DBA Essentials workshop one of the main issues people have is how they can exit psql. Even on stackoverflow this is a popular topic. The good news for people who still don’t like to use “q” here is the commit that will add additional options to quit/exit from psql. Up to PostgreSQL 10 what you can… 

Parallel pg_dump is slow by default?

Short answer: Yes, it is. Being at a customer the last days we wanted to parallel pg_dump a 2TB database. We were quite surprised that it was quite slow and it was not immediately clear why it was. Well, the answer is in the documentation: When you go for parallel dumps you need to use the directory format and this… 

MySQL – Foreign keys and “Cannot delete or update a parent row: a foreign key constraint fails” error

As you know, foreign keys establish a sort of relationship between 2 tables. MySQL requires InnoDB storage engine to support foreign keys. In our example, we have the following parent table in a MySQL 5.7.21 server: mysqld3-(root@localhost) [sakila]> show create table actorG *************************** 1. row *************************** Table: actor Create Table: CREATE TABLE `actor` ( `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,…