Neues vom PostgreSQL Planet

Neues vom PostgreSQL Planet Feed abonnieren
Planet PostgreSQL
Aktualisiert: vor 2 Stunden 41 Minuten

ahsan hadi: How to backup multiple tablespaces with pg_basebackup

30. April 2020 - 17:37

I was doing some testing recently with parallel backup feature along with backup manifest feature which was one of the last feature that got committed to PostgreSQL 13. Parallel backup is not committed to PG yet, it is currently under discussion in the hackers community. I was trying to take parallel backup of a database that contained multiple tablespaces and ran into some issues on how to pass multiple tablespaces to pg_basebackup. So decided to write this small blog on how to do that. This write-up might not be worthy of a blog but it will help someone who is facing the same issue.

Caterina Magini: How to migrate from inheritance-based partitioning to declarative partitioning in PostgreSQL

30. April 2020 - 16:04
Partitioning is one of the coolest features in the latest PostgreSQL versions. PostgreSQL 10 introduced native partitioning and more recent versions have continued to improve upon this feature. This article provides a guide to move from inheritance based partitioning to declarative partitioning, using the native features found in PostgreSQL 11+. The process itself moves the […]

Semab Tariq: How to use Tree Machine Learning model with 2UDA – PostgreSQL and Orange (Part 4)

30. April 2020 - 11:53
This article gives a step by step guide to utilizing Machine Learning capabilities with 2UDA. In this article, we will use examples of Animals to predict whether they are Mammals, Birds, Fish or Insects. Software versions We are going to use 2UDA version 11.6-1 to implement the Machine Learning model. This version 11.6-1 combines: PostgreSQL […]

Hans-Juergen Schoenig: Speeding up count(*): Why not use max(id) – min(id)

30. April 2020 - 9:30

Our PostgreSQL blog about “Speeding up count(*)” was widely read and discussed by our followers on the internet. We also saw some people commenting on the post and suggesting using different means to speed up count(*). I want to specifically focus on one of those comments and to warn our readers.

Steven Pousty: Using PostgreSQL for JSON Storage

29. April 2020 - 20:16

Let's imagine a scenario in which you are dealing with JSON in your application and you want to store it in your database. You let out a heavy sigh and think, "I guess I am going to have to add something besides my favorite DB (Postgres) to my architecture. I wish I could just keep using PostgreSQL."

Bruce Momjian: Optimal Use of SSDs

29. April 2020 - 14:15

SSDs have different performance characteristics than magnetic disks, and using them optimally isn't always clear. SSDs have several performance benefits:

Kirk Roybal: Oracle to PostgreSQL: ANSI outer join syntax in PostgreSQL

29. April 2020 - 2:16
We find ourselves at the third article in the Oracle migration series. This time, we look at those strange operators that modify the WHERE clause criteria in Oracle (+). Like everything else, PostgreSQL has a solution for that.

Denish Patel: Working with Amazon Aurora PostgreSQL: dag, standby rebooted again!

28. April 2020 - 21:55

While I continue to be amazed at how fast PG runs on Amazon Aurora (no WAL logging)  or how fast I can create a snapshot or standby (shared storage),  there are always a few clouds around.  Today the cloud I’m lookin’ at is replica reboots.  Every week or so, one of my standbys gets rebooted and I get notified.  It is always due to the same reason: “reboot due to slave lagging.”  Apparently, aurora will automatically restart a standby if it gets more than 10 seconds behind the primary, that’s right just 10 seconds.  And it is not configurable, burned in

Florian Nadler: Intersecting Tracks of individuals – MobilityDB

28. April 2020 - 9:30

Last time I announced to checkout MobilityDB to improve our approach to extract overlapping passage times of healthy and infected individuals – here we go!

MobilityDB itself is a PostgreSQL extension built on top of PostGIS, specializing on processing and analysing spatio-temporal data. To do so, the extension adds a bunch of types and functions on top of PostGIS to solve different kinds of spatial-temporal questions.

Marco Slot: How the Citus distributed query executor adapts to your Postgres workload

28. April 2020 - 1:25

In one of our recent releases of the open source Citus extension, we overhauled the way Citus executes distributed SQL queries—with the net effect being some huge improvements in terms of performance, user experience, Postgres compatibility, and resource management. The Citus executor is now able to dynamically adapt to the type of distributed SQL query, ensuring fast response times both for quick index lookups and big analytical queries.

Martin Davis: A Deep Dive into PostGIS Nearest Neighbor Search

27. April 2020 - 18:42

A classic spatial query is to find the nearest neighbours of a spatial feature.  Our previous post "Won’t You Be My Neighbor? Quickly Finding Who is Nearby"  discussed this capability from a PostgreSQL perspective.

Denish Patel: Working with Amazon Aurora PostgreSQL: what happened to the stats?

27. April 2020 - 18:41

If you reboot your PostgreSQL aurora instance, your distribution stats will still be there, but your monitoring stats will not. Official PostgreSQL documentation states that if you do a normal shutdown, both distribution and monitoring stats will still be there when you restart your instance (Monitoring Stats):

Denish Patel: How to set application_name for psql command line utility?

27. April 2020 - 16:04

If you are using psql command line utility and wonder how you can set application_name…

If you try to set application_name using psql variable, it doesn’t work …

Andreas 'ads' Scherbaum: Melanie Plageman

27. April 2020 - 15:00
PostgreSQL Person of the Week Interview with Melanie Plageman: I am a developer working on Greenplum at VMware. I live in the Bay Area. In my spare time, I enjoy coding side projects, taking computer science courses, running, traveling, and baking. I am an adventurous eater and love trying new foods.

Peter Eisentraut: A tale of password authentication methods in PostgreSQL

27. April 2020 - 8:32
Let’s say you want to implement a password authentication method in a client/server protocol. How would you do that and what would be the possible problems? The following is the story of how this was done in PostgreSQL. password In the beginning, PostgreSQL only had the method that is now known as “password” in pg_hba.conf. […]

Ernst-Georg Schmid: It looks like pgchem::tigress just got a major upgrade

25. April 2020 - 14:08
With the Release of PostgreSQL 12.x and OpenBabel 3.x, I decided to see if pgchem::tigress would still compile. Well, it took some minor changes, but YES, it does!

cary huang: Can Sequence Relation be Logically Replicated?

24. April 2020 - 22:18
1. Introduction

I have noticed that there is a page on the offical PostgreSQL documentation ( that states several restrictions to the current logical replication design. One of the restrictions is about sequence relation type where any changes associated with a sequence is not logically replicated to the subscriber or to the decoding plugin. This is an interesting restriction and I took the initiative to look into this restriction further and evaluate if it is possible to have it supported.

Nawaz Ahmed: My Favorite PostgreSQL Extensions - Part One

24. April 2020 - 21:17

This is in continuation of my previous blog entry wherein I had touched upon a topic of PostgreSQL  Extensions. PostgreSQL Extensions are a plug and play set of enhancements that add an extra feature-set to a PostgreSQL cluster.

Bruce Momjian: Multi-Host Technologies

24. April 2020 - 14:45

There are so many multi-host technologies and it is hard to remember the benefits of each one, so I decided to create a list:

High availability: Streaming replication is the simplest way to have multiple copies of your database, ready for fail over

Kaarel Moppel: 7 things that could be improved in PostgreSQL

24. April 2020 - 11:30

Usually in this blog I’m writing about various fun topics around PostgreSQL – like perhaps new cool features, some tricky configuration parameters, performance of particular features or on some “life hacks” to ease the life of DBA-s or developers.