Neues vom PostgreSQL Planet

In my last post, I did a simple intro to foreign data wrappers in PostgreSQL. postgres_fdw is an extension available in Postgres core that allows you to issue queries against another Postgres database.

With the 10.1 release to the Citus extension to Postgres, you can now monitor the progress of an ongoing shard rebalance—plus you get performance optimizations, as well as some user experience improvements to the rebalancer, too.

The goal of this post is to help reading the index access path in a YugabyteDB execution plan.

Last time, we installed PostGIS on top of PostgreSQL. Today, I will complement this article by describing how someone can upgrade PostGIS on Ubuntu. A detailed description can be found at postgis.net and should be referred to in parallel.

Let me first define our scenario and goal:

The Fujitsu OSS team, in collaboration with other OSS community members, has been contributing code to enhance Logical Replication for PostgreSQL.

The Google Summer of Code 2021 for the PostgreSQL Project is wrapped up. The timeline this year was shortened to half, compared to previous years. That’s good, because smaller projects can be worked on, and students have a chance to cope with a changing environment at home and university. On the other hand, the shorter time doesn’t allow diving into more complex projects.

An interesting question came up on the #postgresql IRC channel about how to use native PostgreSQL features to handle queuing behavior. There are existing solutions for queuing, both in PostgreSQL, with the venerable pgq project, or dedicated message queues like RabbitMQ, Kafka, etc.

After a short break, I’m back to writing technical articles about Postgres. This is also an opportunity for me to announce my change of activity. Since 2021, I’m a freelancer to give companies the opportunity to benefit from my experience on Postgres.

Index missing?

When an index is missing,
good performance won’t be kissing
a PostgreSQL user looking for efficiency
but instead feels like a legacy.

To satisfy a DBA’s desire and thirst,
let us load some data first.

pgbench is the tool of the day
but the next listing will explain that anyway:

The knapsack problem: how to use limited luggage space to pack only and all the items you're most likely to need? Enter PostgreSQL! Come and find out how to use the world's best open source database to help you pack.

PostgreSQL Person of the Week Interview with Dmitry Dolgov: Hi, I’m Dmitry, proud inhabitant of the Earth (do not trust those rumors stating otherwise), PostgreSQL contributor, trying to make the world a bit better place.

This post is about postgres JDBC (PGJDBC) prepared statements, the setting prepareThreshold, and the performance implication of it.

what is a prepared statement?

A prepared statement is statement that is defined so that it's variables can be set, and executed. An example:

One reason why people choose a UUID for the surrogate key is scalability. Because it generates unique values without having to synchronize with a central generator like a sequence. There are other reasons to use UUID but this post is about scalability. In most databases, sequences can be scalable with a cache: the central catalog object that manages the sequence doesn't have to be read each time we need a next value. Even when the database does not provide a sequence cache, it is very easy to manage from the application.

The data in the main OpenStreetMap database is constantly changing. Folks around the world are almost certainly saving changes via JOSM, iD, and other editors as you read these words. With change constantly occurring in the data, it is often desirable to have an idea of what has actually changed in the data. This post explores one approach to tracking changes to the tags attribute data once it has been loaded to Postgres.

When you are working on Oracle to PostgreSQL migrations, one of the Oracle packages that cause conversion issues is DBMS_JOB. Traditionally, we used extensions like : pg_agent, pg_cron or more recently pg_timetable for scheduling jobs.

Recently we had interesting problem. There are some maintenance tools, that do stuff that touch database. One can be calling repack, another can be specific dump, and yet another can be applying migrations from application. The problem is that they can step over each other toes, and cause issues. So we needed to add some … Continue reading "How to get advisory lock in shell?"

Generally, one appreciates new features of PostgreSQL on the release date after anxious inspection of the release notes or having skimmed through the git logs.

I had an opportunity to sit down with Mark Porter, the CTO of MongoDB to discuss PostgreSQL, Aurora PostgreSQL and MongoDB. Mark is one of the creators of Aurora PostgreSQL and now enjoys a leadership role at MongoDB. There are two episodes:

Creating auto increment columns in PostgreSQL is easy. Simply use two pseudo data types serial and serial8, respectively, then PostgreSQL will automatically take care of your auto increment columns. However, once in a while problems can still occur. Let us take a look and see.

Sequences: Avoid manual values

To understand the underlying problem, one has to understand how auto increment and sequences work in PostgreSQL:

PostgreSQL can provide high performance summaries over multi-million record tables, and supports some great SQL sugar to make it concise and readable, in particular aggregate filtering, a feature unique to PostgreSQL and SQLite. 

A huge amount of reporting is about generating percentages: for a particular condition, what is a value relative to a baseline.