Neues vom PostgreSQL Planet

In my previous posting on tile serving, I demonstrated how pg_tileserv can publish spatial tables as dynamic vector tiles.

When designing a database application, the layout of data in disk is often neglected. However, the way data is stored by PostgreSQL can have a major performance impact. Therefore it makes sense to take a look at what can be done to improve speed and throughput. In this post you will learn one of the most important tricks.

PostgreSQL: To sort or not to sort

To demonstrate the importance of the on-disk layout I have created a simple test set:

This article parallels Oracle's copying of the SQL syntax from IBM in the late 1970's with Google's copying of the Java API. It also explains the possible impact of the current case soon to be decided by the US Supreme Court.

Ever since I released explain.depesz.com over 11 years ago there have been cases were people would upload a plan and it didn't parse. There were many reasons, but the most common was – plan was line-wrapped by injecting new-line characters where there shouldn't be one.

PostgreSQL Person of the Week Interview with Bruce Momjian: I live in Philadelphia and have worked on Postgres for 24 years. My heritage is Armenian.

This example will just be focusing on returning data from user defined functions, specifically returning a value as opposed to using OUT parameters.  The full code is on Github. To quickly review, PL/Julia calls jl_eval_string() to execute Julia code and captures the returning jl_value_t data structure, which contains the result of the Julia code executed.  […]

I was asked recently if PostgreSQL could authenticate login users against AWS Cognito.  Since PostgreSQL allows PAM authentication, I was pretty sure it could.

But an (admittedly not exhaustive) search on the web did not produce any PAMs for Cognito.

The title is not clickbait or hyperbole. I intend to prove that by virtue of both design and implementation that PostgreSQL is objectively and measurably a better database1 than anything currently available, with or without money considerations. How in the world can I claim and justify such a lofty statement? Read on, gentle nerd. I promise that your time will not be wasted.

Back in August 2019, I wrote multiple blogs with the title of “Horizontal scalability with Sharding in PostgreSQL – Where it is going Part 1 .. 3”. Little has happened since then, the purpose of this blog is discuss the important missing pieces of the puzzle, what are the minimum set of features needed to get to MVP (minimum viable product) and most importantly which efforts are currently going on to get to the MVP of Sharding in PostgreSQL core.

PostgreSQL 12 comes with a great new feature, Generated Columns. The functionality isn’t exactly anything new, but the standardization, ease of use, accessibility, and performance has been improved in this new version.

It is with heavy hearts that we have to announce that both Nordic PGDay 2020 (Helsinki, March 24th) and pgDay Paris 2020 (Paris, March 26th) have been canceled.

In light of the rapidly changing situation of the COVID-19 spread around Europe, and its effect on attendees, speakers, and sponsors ability and interest in safely attending, we feel at this point that this is the socially responsible thing to do.

There are no plans to postpone these events; we are instead focusing on returning next year, bigger and better than ever!

The organization team is closely following the recommendations of local, national, and international authorities. As of April 25, 2020 — in Ukraine — only assemblies of 200 people or more are restricted as well as events in contamination areas of which Lviv is not yet a part. However, the situation could change rapidly and we will update this page as needed. We also plan to keep attendees, speakers, and sponsors abreast of any significant changes.

In addition to viewing PostGIS spatial data as vector tiles using pg_tileserv, it is often necessary to access data features directly. This supports use cases such as:

© Laurenz Albe 2020

 

Recently, while troubleshooting PostgreSQL performance problems, I ran into problems with subtransactions twice. So I thought this was a nice topic for another blog post.

Tomas Vondra is a speaker of the PgDay Ukraine conference. He is working for 2ndQuadrant as a database engineer and developer, committer, …

PostgreSQL Person of the Week Interview with Christophe Pettus: I’m a native Californian. My background is kind of random: I’ve been involved in electronic music, movies, writing comics, historical recreation, flying, and a lot of strange eCommerce companies. My first ‘real’ job was at the Jet Propulsion Laboratory, so I can legitimately claim to be a rocket scientist. Through a long, complicated set of events involving my happening to own a professional video camera, I’ve ended up as CEO of PostgreSQL Experts, Inc., a consultancy supporting PostgreSQL.

Read our interview with Daniel Westermann who will talk about [x],[y],zheap - What it is good for.

A few months ago, I wrote a high-level presentation about the deployment benefits of using containers and cloud infrastructure for databases. I am now ready to share this presentation. I have also added QR codes to the first and last slides of all my presentations, linking to my website.

One method to handle input parameters with PL/Julia is to rewrite the body of the user defined function or stored procedure on the fly with the values declared as global variables. In order to do that, we need to create a new buffer big enough to hold the new global variable declarations in addition to […]

What is NULL?

There is often some confusion about NULL value, as it is treated differently in different languages. So there is an obvious need to clarify what NULL is, how it works in different languages, and what the actual value is behind the NULL. Before going into details, there is also a need to understand the concept of Three-valued logic[1] and Two-valued logic known as bivalent[2]. The bivalent is a  concept of boolean value where value can be true or false, but contrary to bivalent the Three-valued logic can be true, false, or (intermediate value) unknown.