Neues vom PostgreSQL Planet

A couple of our recent podcasts are directly related to PostgreSQL, they are listed below. I have really enjoyed meeting the people behind the technology that drives PostgreSQL in the global community. A lot of us are used to collectively gathering a few times a year with conferences. Obviously the pandemic has put a halt to that but launching the podcast has allowed me to connect with some amazing folks.

This document provides an introduction to tuning PostgreSQL and EDB Postgres Advanced Server (EPAS), versions 10 through 13. The system used is the RHEL family of linux distributions, version 8. These are only general guidelines and actual tuning details will vary by workload, but they should provide a good starting point for the majority of deployments. [Continue reading...]

Contributing author David Youatt

Many might have seen PostgreSQL issue the following error message: "ERROR: deadlock detected". But what does it really mean? How can we prevent a deadlock and how can we reproduce the problem? Let’s dive into PostgreSQL locking and understand what deadlock and deadlock_timeout really mean.

It’s been 5 months since interactive Postgres Observability version – pgstats.dev was released. Since then I got plenty of comments and suggestions that helped me to implement several additions and changes to make this tool ever more useful to the Postgres community.

Here is an overview of key features that you will now see. 

Diagram changes:

Having been involved in open source for the past 30 years, I have always felt like an outsider in social settings, having to explain the purpose of open source and its communities. (I remember having to explain what email, Usenet, and the Internet was too, but that need has passed. )

PostgreSQL Person of the Week Interview with Corey Huinker: I grew up in Wisconsin near a circus museum. I have no skill at juggling, so joining them was not an option. Instead I became a programmer and wandered around the US (Raleigh, Portland, Austin) before finally settling down in New York City.

Pandas is a very popular tool for data analysis. It comes built-in with many useful features, it's battle tested and widely accepted. However, pandas is not always the best tool for the job.

SQL databases has been around since the 1970s. Some of the smartest people in the world worked on making it easy to slice, dice, fetch and manipulate data quickly and efficiently. SQL databases have come such a long way, that many developers and data scientists lost track of what they can do with the database they already have!

PostgreSQL supports a wide variety of data types when compared to a few commercial databases like Oracle. For performance benefits, it may be wise to choose the optimal data types supported in PostgreSQL, instead of choosing the same ones as the source, while migrating from another database.

When you buy a proprietary database from one of those billion-dollar companies, you get more than a database — you get backup, monitoring, high availability, and maybe scaling features. These all come from the same company, though some might require extra cost.

We already got acquainted with the structure of the buffer cache — one of the main objects of the shared memory — and concluded that to recover after failure when all the RAM contents get lost, the write-ahead log (WAL) must be maintained.

This release fix critical segfault crash introduced in pspg 4.6.0. https://github.com/okbob/pspg/releases/tag/4.6.1

© Laurenz Albe 2021

In this article, I’ll talk about row level triggers, which are the most frequently used kind of triggers. I will describe what the return value of the trigger function means and suggest a useful code simplification.

Triggers in PostgreSQL

A trigger in PostgreSQL consists of two parts:

Indexes are key database server features that enhance its performance to retrieve data faster than a complete table scan (unless the index is vastly bloated). They generally work by maintaining a smaller set of data in a more “searchable” structure as compared to the complete table data.

This data organization comes at the cost of maintaining indexes which is a space and processing overhead for the database server. The benefits, however, significantly outweigh these overheads.

Every so often, on irc, someone asks how to get value from column that is passed as argument. This is generally seen as not possible, as pl/PgSQL doesn't have support for dynamic column names. We can work around it, though. Are the workarounds usable, in terms of performance? To test it, I will need some … Continue reading "Getting value from dynamic column in pl/PgSQL triggers?"

Following my blog entry about assumptions, this long email thread gives an interesting comparison of Oracle and Postgres. The original poster compares install times, initial database sizes, and relative bug counts. From there, things get complicated.

We all operate on assumptions, e.g., we expect to be alive tomorrow, we expect our family to always support us, we assume our car will start. Everyone needs to make some assumptions to function. But assumptions aren't always true, e.g., some day the sun will rise, and you will not be alive to see it. So, where does that leave us? We have to make assumptions to survive, but what we assume today might be false tomorrow. Fundamentally, successful people, while they rely on assumptions, are always alert for signs that their assumptions are false.

PostgreSQL Person of the Week Interview with Pavlo Golub: Hello. My name is Pavlo Golub. I’m originally from Ukraine, but now I live in Slovakia. I’m a developer and consultant at Cybertec PostgreSQL International.

Back in Nov 2021, I wrote about calling Stored procedure from Java and demonstrated how we can call a simple stored procedure from Java or a one that contains a IN parameter. I have done some more playing around with calling Stored procedure from Java so this blog is about calling a stored procedure with multiple IN parameters and multiple INOUT parameters. As I had explained earlier in my blog of the “Stored Procedure” series that the stored procedures which don’t have a return value and if you want to return a value then you need to use a INOUT parameter.

 

 Photo by Free-Photos