Neues vom PostgreSQL Planet

Neues vom PostgreSQL Planet Feed abonnieren
Planet PostgreSQL
Aktualisiert: vor 1 Stunde 11 Minuten

Kat Batuigas: Querying Spatial Data with PostGIS and ogr_fdw

3. September 2021 - 18:40

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.

Jelte Fennema: Shard rebalancing in the Citus 10.1 extension to Postgres

3. September 2021 - 18:05

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.

Franck Pachot: Index Scan in YugabyteDB

3. September 2021 - 14:37

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

Florian Nadler: PostGIS upgrade with Ubuntu 20.04.02

2. September 2021 - 10:00

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 and should be referred to in parallel.

Let me first define our scenario and goal:

Peter Smith: Logical Replication Tablesync Workers

2. September 2021 - 3:05

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

Andreas Scherbaum: GSoC 2021 completed

2. September 2021 - 0:06

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.

David Christensen: Devious SQL: Message Queuing Using Native PostgreSQL

1. September 2021 - 16:25

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.

Adrien Nayrat: Partitioning use cases with PostgreSQL

1. September 2021 - 9:00

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.

Hans-Juergen Schoenig: PostgreSQL: The power of a SINGLE missing index

31. August 2021 - 10:00
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:

Francesco Tisiot: Solving the knapsack problem in PostgreSQL

31. August 2021 - 2:00

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.

Andreas 'ads' Scherbaum: Dmitry Dolgov

30. August 2021 - 16:00
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.

Frits Hoogland: Postgres query execution: JDBC prepared statements

30. August 2021 - 15:13

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:

Franck Pachot: UUID or cached sequences?

30. August 2021 - 9:41

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.

Ryan Lambert: Identify OpenStreetMap changes with Postgres

30. August 2021 - 7:01

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.

Gilles Darold: Announcing PG_DBMS_JOB in PostgreSQL for Oracle DBMS_JOB compatibility

27. August 2021 - 16:19

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.

Hubert 'depesz' Lubaczewski: How to get advisory lock in shell?

26. August 2021 - 17:36
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?"

David Christensen: Insert-Only Tables and Autovacuum Issues Prior to PostgreSQL 13

25. August 2021 - 21:57

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.

Joshua Drake: Mark Porter: CTO MongoDB on PostgreSQL and MongoDB

25. August 2021 - 19:09

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:

Hans-Juergen Schoenig: Fixing out-of-sync sequences in PostgreSQL

25. August 2021 - 10:00

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:

Paul Ramsey: Fast, Flexible Summaries with Aggregate Filters and Windows

24. August 2021 - 21:09

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.