Neues vom PostgreSQL Planet

The lock is an essential part of a database system. In PostgreSQL, there are various locks, such as table lock, row lock, page lock, transaction lock, advisory lock, etc. Some of these locks are automatically added to complete the database functions during the operation of the database system, and some are manually added for the elements in PostgreSQL through some SQL commands. This blog explores the locks in PostgreSQL.

PostgreSQL Conference Germany 2020 in Stuttgart, Germany, on May 15th is now open for registrations.

The Call for Papers is already closed, and we are working with the last speakers to confirm their talks, and we will have a full schedule published soon.

There are still a few "EARLYBIRD" tickets available, until end of February.

See you in Stuttgart!

If you are using Azure PostgreSQL and have upgraded your client side libpq to version 12 (which can happen automatically for example if you use the PostgreSQL apt repositories), you may see connection attempts fail with symptoms like:

This blog post is about the journey of parallelism in PostgreSQL till now and what is in store for the future.  Since PostgreSQL 9.6 where the first feature of parallel query has arrived, each release improves it.  Below is a brief overview of the parallel query features added in each release.

PG9.6 has added Parallel execution of sequential scans, joins, and aggregates.

The PostgreSQL caching system has always been a bit of a miracle to many people and many have asked me during consulting or training sessions: How can I figure out what the PostgreSQL I/O cache really contains? What is in shared buffers and how can one figure out? This post will answer this kind of question and we will dive into the PostgreSQL cache.

Creating a simple sample database

Before we can inspect shared buffers we have to create a little database. Without data the stuff we are going to do is not too useful:

Recently, on irc, there were couple of cases where someone wanted to use uuid as datatype for their primary key. I opposed, and tried to explain, but IRC doesn't really allow for longer texts, so figured I'll write a blogpost. First problem – UUID values are completely opaque. That means – uuids generated for table … Continue reading "Why I’m not fan of uuid datatype"

Couple of days ago RhodiumToad reported, on irc, a bug in explain.depesz.com. Specifically – if explain was done using JSON/XML/YAML formats, and node type was Aggregate, the site didn't extract full info. In text explains the node type is one of: Aggregate HashAggregate GroupAggregate But in non-text formats, type of Aggregate was ignored. As of … Continue reading "Fix for displaying aggregates on explain.depesz.com"

Some time ago I wrote blogpost which showed how to list tables that should be autovacuumed or autoanalyzed. Query in there had one important problem – it didn't take into account per-table settings.

On 12nd of February 2020, Michael Paquier committed patch: Add %x to default PROMPT1 and PROMPT2 in psql   %d can be used to track if the current connection is in a transaction block or not, and adding it by default to the prompt has the advantage to not need a modification of .psqlrc, something … Continue reading "Waiting for PostgreSQL 13 – Add %x to default PROMPT1 and PROMPT2 in psql"

On 6th of February 2020, Michael Paquier committed patch: Add leader_pid to pg_stat_activity   This new field tracks the PID of the group leader used with parallel query. For parallel workers and the leader, the value is set to the PID of the group leader. So, for the group leader, the value is the same … Continue reading "Waiting for PostgreSQL 13 – Add leader_pid to pg_stat_activity"

To avoid having concurrent transactions interfere with each other, SQL engines implement isolation as a feature. This property corresponds to the I letter in the well known ACID acronym, the other properties being Atomicity, Consistency and Durability.

Isolation happens to be configurable, with different levels that correspond to different behaviors when executing concurrent transactions.

Small note - I finished support of statement and branch coverage metrics calculations for plpgsql_check

https://github.com/okbob/plpgsql_check/commit/c6f9896ead0a0969db23c3062f1fe7ce5f38029b

cloudfs_fdw now supports .xls (Excel 97-2003), .xlsx, and .ods (Open Document Format) Spreadsheets via pandas, xlrd, and odfpy. It requires pandas >= 1.0.1, so Multicorn must be compiled against Python 3.

In previous blog posts that I've written about VACUUM, and I seem to be accumulating an uncomfortable number of those, I've talked about various things that can go wrong with vacuum, but one that I haven't really covered is when autovacuum seems to be running totally normally but you still have a VACUUM problem. In this blog post, I'd like to talk about how to recognize that situation, how to figure out what has caused it, how to avoid it via good monitoring, and how to recover if it happens.

As hardware and software evolve, the bottlenecks in a database system also shift. Many old problems might disappear and new types of problems pop-up.

Old Limitations

There were days when CPU and Memory was a limitation. More than a decade back, servers with 4 cores were “High End” and as a DBA, my biggest worry was managing the available resources. And for an old DBA like me, Oracle’s attempt to pool CPU and Memory from multiple host machines for a single database using RAC architecture was a great attempt to solve it.

The right way to get the current PostgreSQL configuration is by means of pg_settings.

Take advantage of pg_settings when dealing with your configuration

I often see messages on PostgreSQL related mailing list where the configuration is assumed by a Unix-style approach. For example, imagine you have been asked to provide your autovacuum configuration in order to see if there’s something wrong with it; one approach I often is the copy and paste of the following:

One of the worst performance hits a Postgres query can take is having to perform a sort or hash operation on disk. When these space-intensive operations require more memory than is available, Postgres uses disk space instead. Disk space is much slower to read and write than RAM, so this generally takes significantly longer.

The best solution to this problem is to avoid having to perform the operation entirely, for example by adding a judicious index.

When migrating from MS SQL to PostgreSQL, one of the first things people notice is that in MS SQL, object names such as tables and columns all appear in uppercase. While that is possible on the PostgreSQL side as well it is not really that common. The question therefore is: How can we rename all those things to lowercase – easily and fast?

 

 

Julia provides an API so that Julia functions can be called from C.  PL/Julia will use this C API to execute Julia code from its user defined functions and stored procedures. Julia’s documentation provides an example C program that starts up the Julia environment, evaluates the expression sqrt(2.0), displays the resulting value to the standard […]

This is the second installment of a two-part series on 2ndQuadrant’s repmgr, an open-source high-availability tool for PostgreSQL. In the first part, we set up a three-node PostgreSQL 12 cluster along with a “witness” node. The cluster consisted of a primary node and two standby nodes. The cluster and the witness node were hosted in […]