UUIDs are a popular identifier data type - they are unpredictable, and/or globally unique (or at least very unlikely to collide) and quite easy to generate. Traditional primary keys based on sequences won't give you any of that, which makes them unsuitable for public identifiers, and UUIDs solve that pretty naturally.
But there are disadvantages too - they may make the access patterns much more random compared to traditional sequential identifiers, cause WAL write amplification etc. So let's look at an extension generating "sequential" UUIDs, and how it can reduce the negative consequences of using UUIDs.
PostgreSQL 11 was released recently, with exciting new features. One of them is the ability to write SQL procedures that can perform full transaction management, enabling developers to create more advanced server-side applications. SQL procedures can be created using the CREATE PROCEDURE command and executed using the CALL command. Since OmniDB 2.3.0 it is possible to debug PostgreSQL PL/pgSQL functions. Support to PostgreSQL 11 functions and procedures was added in OmniDB 2.11.0.
Last week we released OmniDB 2.12.0 with nice new features and a new revamped visual, so I'm going to show you how OmniDB 2.12.0 can debug PostgreSQL 11 procedures.
First of all, if you have not done that already, download and install a binary PostgreSQL library called omnidb_plugin and enable it in (more…)
Last month, I was at 2ndQuadrant booth at P2D2, an annual conference organized in Prague by local user group called CSPUG (Czech and Slovak PostgreSQL User Group). The conference was certainly a huge success and more than 200 people showed up! Meeting a lot of PostgreSQL enthusiasts in town, I wondered why there are no regular meetups in Prague. At the conference after-party, I started to question folks and learned that given a chance some of them would give talks and many more would be happy to attend. The initial reactions verified my observation and I felt motivated enough to start organizing meetups myself!
Even though I've been living in Prague for some time now, it has been a little difficult to manage the myself all by myself. My colleague Tomas Vondra stepped forward to (more…)
Two serious security vulnerabilities (code named Meltdown and Spectre) were revealed a couple of weeks ago. Initial tests suggested the performance impact of mitigations (added in the kernel) might be up to ~30% for some workloads, depending on the syscall rate.
Those early estimates had to be done quickly, and so were based on limited amounts of testing. Furthermore, the in-kernel fixes evolved and improved over time, and we now also got retpoline which should address Spectre v2. This post presents data from more thorough tests, hopefully providing more reliable estimates for typical PostgreSQL workloads.
On 12-Dec-2017, 2ndQuadrant held the first in a series of PostgreSQL webinars. The session was conducted by Andrew Dunstan, Principal Contributor of JSON functionality to PostgreSQL and Senior Developer & PostgreSQL Committer at 2ndQuadrant.
As promised, the recording of the Webinar is now available. Those who couldn't make it to the live session, can now view Introduction to JSON data types in PostgreSQL here.
While Andrew was able to address many questions live, there were several queries that couldn’t be answered due to time restrictions. Andrew Dunstan has taken time to answer those questions below.
Q1: Since PostgreSQL 9.4 JSONB supports GIN index, does that means that GIN index has a better performance on JSONB? What about JSON?
A: There is no direct indexing (more…)
In Part 2 of this series, we will continue our journey within the developmental dynamics of the Barman open source project for PostgreSQL database backup and disaster recovery. After providing a small introduction to devops and Kanban in Part 1, let's focus on the basic element of our daily management: The Boards.
We very often hear about devops culture, lean and agile methodologies, kanban, pair programming, peer review, testing, and many more; but how many of us could effectively put these things into practice?
Support for using the TAP protocol to run extended regression tests was added to PostgreSQL back in 9.4 with the adoption of Perl's prove tool and Test::More to test initdb, pg_basebackup, etc.
Since then the TAP-based tests have been greatly expanded, particularly with the advent of the src/test/recovery tests and the PostgresNode module in PostgreSQL 9.6. PostgreSQL now comes with a built-in test harness for easily starting up postgres instances, creating and restoring backups for replication, setting up streaming, and lots more.
You can now use this to test your extensions.
pg_regress and its limitations
Extensions have long supported pg_regress based tests. Just drop the test scripts in sql/. Put the expected results in expected/. List the test names (sans directory and file (more…)