Sammlung von Newsfeeds

Laurenz Albe: Tuning max_connections in PostgreSQL

Neues vom PostgreSQL Planet - 17. April 2020 - 11:00

© Laurenz Albe 2020

In my daily work, I see many databases with a bad setting for max_connections. There is little advice out there for setting this parameter correctly, even though it is vital for the health of a database. So I decided to write up something.

Pavel Stehule: split string to table

Neues vom PostgreSQL Planet - 17. April 2020 - 8:14
Sometimes life can be funny. This week there was a question about XMLTABLE function in Postgres's maling list. It was related to migration from Oracle to Postgres.

CREATE TABLE user_pool_clean (
fk_user_pool_pk bytea NOT NULL,
user_id character varying(255) NOT NULL,
email_address character varying(250),
is_mil numeric,
is_civ numeric,
is_ctr numeric,
is_gov numeric,
is_edu numeric,
role_id character varying(50),
user_profile_id character varying(50),

Luca Ferrari: PL/pgSQL Trends

Neues vom PostgreSQL Planet - 17. April 2020 - 2:00

A graph that shows the trends of PL/pgSQL, according to

PL/pgSQL Trends

I discovered this excellent graphing system that shows several programming language trends, according to

So, let’s compare PL/pgSQL with SQL and plSQL:

Dan Langille: Using DEFERRABLE INITIALLY DEFERRED on constraints

Neues vom PostgreSQL Planet - 16. April 2020 - 23:34
This post is another in the packages series. It documents how the data is transformed from the raw form (pacakges_raw) into normalized data (packages). The Packages – how is this data stored? post may be useful reading. The primary purpose of this blog post is documentation of the above mentioned transformation process. Along the way [...]

Jobin Augustine: SQL Optimizations in PostgreSQL: IN vs EXISTS vs ANY/ALL vs JOIN

Neues vom PostgreSQL Planet - 16. April 2020 - 17:13

This is one of the most common questions asked by developers who write SQL queries against the PostgreSQL database. There are multiple ways in which a sub select or lookup can be framed in a SQL statement. PostgreSQL optimizer is very smart at optimizing queries, and many of the queries can be rewritten/transformed for better performance.

Let’s discuss the topic with an example, for which I am using schema created by pgbench.

Leigh Halliday: Full Text Search in Milliseconds with Rails and PostgreSQL

Neues vom PostgreSQL Planet - 16. April 2020 - 14:00
Imagine the following scenario: You have a database full of job titles and descriptions, and you’re trying to find the best match. Typically you’d start by using an ILIKE expression, but this requires the search phrase to be an exact match. Then you might use trigrams, allowing spelling mistakes and inexact matches based on word similarity, but this makes it difficult to search using multiple words. What you really want to use is Full Text Search, providing the benefits of ILIKE and trigrams…

Mark Wong: PDXPUG April 2020: Multiranges and SQL:2011 Temporal Features

Neues vom PostgreSQL Planet - 15. April 2020 - 17:53

2020 April 23 Meeting 6:30pm-8:30pm

Note: This will be an online event. You must RSVP to get the link to the meeting, which will be set shortly before the meeting as we will be using Jitsi. Jitsi can be used directly with Firefox and Chrome (with extension), or phones/tablets with the Jitsi Meet app.

Speaker: Paul Jungwirth

Dan Langille: Procedural vs query approaches

Neues vom PostgreSQL Planet - 14. April 2020 - 23:54
This is the latest in a series of posts dealing with displaying what packages are available for a given FreeBSD port. The Packages – how is this data stored? post may help you follow along. It outlines what the data and the tables used in this post. Last night I managed to get the right [...]

Everything You Need to Know About PostgreSQL Locks

PostgresqlHelp - 14. April 2020 - 16:26

In the context of transaction processing, Isolation plays a significant role.

Isolation is the property that controls how and when changes are made and when they must be visible to each other, users, and systems.

PostgreSQL achieves Isolation by multi-version concurrency control architecture.

Multi-version concurrency control is a technique to allow multiple sessions to access the same record concurrently, i.e., while session A is updating a record, session B can still access the record.


Florian Nadler: Intersecting GPS-Tracks to identify infected individuals

Neues vom PostgreSQL Planet - 14. April 2020 - 10:30

In times of COVID-19, governments contemplate tough measures to identify and trace infected people. These measures include the utilization of mobile phone data to trace down infected individuals and subsequently contacts to curb the epidemic. This article shows how PostGIS’ functions can be used to identify “overlapping” sections of infected and healthy individuals by analysing tracks spatio-temporally.
This time we don’t focus on performance and tuning, rather strive boosting your creativity on PostgreSQL’s spatial extension and its functionalities.

Joshua Drake: How to add seconds to a timestamp to get an ending timestamp?

Neues vom PostgreSQL Planet - 13. April 2020 - 19:12

Command Prompt is one of the oldest Postgres support companies in the world and we have been blessed to be extremely busy with Professional community development including meetups and conferences. With the current climate we thought it would also be useful to remind people of “Simple Tips”. Simple tips will help new users of Postgres and related technologies to answer those pesky little questions that are in the back of their heads about how to do “insert idea here”.

How to add seconds to a timestamp to get an ending timestamp?

Assume you have have 2 values:

Andreas 'ads' Scherbaum: Ilaria Battiston

Neues vom PostgreSQL Planet - 13. April 2020 - 16:00
PostgreSQL Person of the Week Interview with Ilaria Battiston: I am Ilaria, a 22-year-old Italian student currently studying Data Engineering at TU Munich. My main hobby, original enough, is open source advocacy: volunteering, contributing and learning. I am incredibly passionate about databases and algorithms, hoping to be able to work with them for life. I also love travelling and cooking :)

ahsan hadi: Stored Procedures in PG 11 – Better late then never

Neues vom PostgreSQL Planet - 10. April 2020 - 18:09

I was going a give a short talk on this subject in pgconf New York 2020, however the conference like many other conference this year unfortunately got cancelled due to COVID-19. So I have decide to write a short about this schema object introduced in PG 11. As a Oracle database developer / DBA I had made extensive use of stored procedure so finally it landed in PostgreSQL in PG 11.  

Andreas Scherbaum: Related Projects

Neues vom PostgreSQL Planet - 10. April 2020 - 17:06

Andreas 'ads' Scherbaum

The PostgreSQL main website has a new page: "Related Projects".

This page lists the projects which help running and maintaining the PostgreSQL project, the infrastructure, and other things like the translations for press releases. For each project it lists links to the source, as well as information where to send updates, patches, or input.

Paul Ramsey: Polygon Averaging in PostGIS

Neues vom PostgreSQL Planet - 9. April 2020 - 22:49

The GIS Stack Exchange is a great repository of interesting questions and answers about how to work with spatial data, and with PostGIS.

For example, this question:

Semab Tariq: How to use the Random Forest Machine Learning Model with 2UDA – PostgreSQL and Orange (Part 3)

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

Kaarel Moppel: Detailed look on the new PostgreSQL troubleshooting extension – pg_show_plans

Neues vom PostgreSQL Planet - 9. April 2020 - 9:30

2 weeks ago we announced a new, long awaited, Postgres extension to look into execution plans for “live” running queries: pg_show_plans. This was not possible before in PostgreSQL yet, so it’s really a very cool piece of functionality and we’d like to echo out the message again.

Claire Giordano: Documenting the Citus extension to Postgres: an interview with begriffs

Neues vom PostgreSQL Planet - 9. April 2020 - 2:00

The last two months, I managed the agenda for our weekly Citus team meeting, the one time each week where our entire distributed team—with people spread across 6 different countries—gets together to talk about Citus things. As I chatted with our PostgreSQL folks to find speakers to give 10-minute “lightning talks”, I heard a chorus from several of the engineers: “see if you can get Joe to give a talk. His talks are always super interesting.”

Luca Ferrari: Are we at the ZeroConference point?

Neues vom PostgreSQL Planet - 9. April 2020 - 2:00

I’m seeing that around all the world the conferences are being cancelled. This is also true for PostgreSQL related conferences.

Luis M Carril : Backup and restore data in PostgreSQL foreign tables using pg_dump

Neues vom PostgreSQL Planet - 8. April 2020 - 17:22

In this article, we describe a recent enhancement to pg_dump that Swarm64 contributed that more fully supports the backup and restoration of foreign tables. The contribution has been committed to PostgreSQL 13.