Neues vom PostgreSQL Planet

For PGSQL Phriday #003 (the last one of 2022), Pat Wright asked us to consider the PostgreSQL community, what it means to us, and how we would get started as a new user. I love this topic for a couple of reasons, not the least of which is that my interest in, and enjoyment of, … Continue reading "PostgreSQL Community: Passionate, Knowledgeable, and Thoughtful"

I compiled and uploaded zip files with latest orafce and plpgsql_check for PostgreSQL 14 and PostgreSQL 15.

Setup:

Very excited to take part in my third #PGSQLPhriday blogging event, even more so because it’s a topic that’s quite near and dear to my heart, community. To say that I’m new to the PostgreSQL community isn’t simply an understatement. Other than some online stuff, I haven’t been anywhere near the PostgreSQL community. That’s not […]

Crunchy Data hosted the 4th annual PostGIS Day on November 17, 2022. PostGIS Day always comes a day after GIS Day which occurs annually on the 3rd Wednesday of November.

We had speakers from 10 different countries and attendees from more than 70 countries.

PostGIS is the most popular spatial relational database worldwide with:

PostgreSQL traditionally uses shell commands to achieve continuous WAL archiving, which is essential for backups and stable standby replication.  In the past, we blogged about the inefficiency in that design and how some of the backup tools like PgBackRest solve that problem. It is a well-known problem in the PostgreSQL community, and many discussions happened in the past about the same.

YugabyteDB YEDIS is a key-value database that is compatible with the redis commands library.

Important: YEDIS is deprecated!

There are other limitations for YEDIS see the description in the YugabyteDB documentation.

The most important part of this blogpost is how to remove the YEDIS, see: 'YEDIS can be removed'.

YugabyteDB YEDIS is a key-value database that is compatible with the redis commands library.

Important: YEDIS is deprecated!

There are other limitations for YEDIS see the description in the YugabyteDB documentation.

The most important part of this blogpost is how to remove the YEDIS, see: 'YEDIS can be removed'.

This blog post is for PGSQL Phriday #003. Read Ryan Booz' introduction from September for more details on PGSQL Phriday. Pat Wright (SQL Asylum) is this month's host and chose the topic: What is the PostgreSQL community to you?

TLDR;

The Postgres community is helpful.

One of my favourite (in an ironic sense) data formats is the "CSV in the CSV", a CSV file in which one or more of the column is itself structured as CSV.

Putting CSV-formatted columns in your CSV file is a low tech approach to shipping a multi-table relational data structure in a single file. The file can be read by anything that can read CSV (which is everything?) and ships around the related data in a very readable form.

YugabyteDB uses the PostgreSQL source code for PostgreSQL compatibility, which we call YSQL. However, once we store tuples, we transform the PostgreSQL tuples into a storage format that is called 'protobuf', which gets send to our distributed storage layer called 'DocDB' via RPC calls. After DocDB has received the tuples, it is stored in the database we use for the storage, which is rocks db. Rocksdb is a key-value store which stores the PostgreSQL tuples (as well as YCQL tuples from our Cassandra compatible storage engine) as key-value pairs and sub-key-value pairs.

Whenever I'm doing some testing I need sample data. Easiest way to do it is to generate data using some random/generate_series queries. But what if I need specific frequencies? For example, I need to generate 10,000,000 rows, where there will be 10% of ‘a', 20% of ‘b', and the rest will be split equally between … Continue reading "Picking random element, with weights"

What is GRANT VACUUM, ANALYZE?

PostgreSQL uses table VACUUM and ANALYZE commands to optimize the database. The VACUUM command reclaims storage space and makes it available for re-use. It also updates the visibility map, which helps the query planner to quickly identify which parts of the table have live rows.

A new command to interactively get the pgagroal runtime configuration.

I’m excited to announce that the translation of Part IV of the “PostgreSQL 14 Internals” book is published. This part delves into the inner workings of the planner and the executor, and it took me a couple of hundred pages to get through all the magic that covers this advanced technology.

You can download the book freely in PDF. The last part is yet to come, stay tuned!

Partitioning is one of the most desired features of PostgreSQL, widely adopted by developers. This is not only true for in PostgreSQL 15, but also for older versions which did not provide as many features as the latest version of the database. That’s why you should know not only how to properly partition tables, but how to find out which partition a piece of data belongs to.

So, there’s a small question which often arises: How DO I figure out which partition I just inserted my data into? Some small “old consultant’s trickery” can help to find out.

Let’s say you are following a patch in the PostgreSQL commit fest. The author has posted “v5” of a patch, some feedback has been sent, the author comes back with “v6” and a note saying that they have incorporated all that feedback. How do you check what actually changed?

A straightforward way is to make two separate branches in your local Git repository, commit the two patches, and compare the results, perhaps like this:

I released pspg 5.6.0. There is only one (not too visible change). It allows to use true color themes in "konsole" terminal (when TERM) is xterm-direct.

PostgreSQL Person of the Week Interview with Oleksandr Shulgin: My name is Oleksandr. It is the same as “Alexander”, but in the Ukrainian spelling, so you’re right if you guessed that I originally come from Ukraine.

1. Overview

Followed my previous blog, Global Index, a different approach, we posted our initial Global Unique Index POC to Postgres community for open discussion about this approach. Now, this blog is trying to explain how the benchmark was performed using pgbench on this initial Global Unique Index POC.

To see how JSON is stored in YugabyteDB, we have to take a look at both PostgreSQL and YugabyteDB.

Let's create two test tables for investigation: