Sammlung von Newsfeeds

© 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.

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),

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

PL/pgSQL Trends

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

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

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 [...]

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.

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…

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

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 [...]

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.

But,

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.

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:

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 :)

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 '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.

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:

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 […]

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.

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.”

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

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.