Sammlung von Newsfeeds

This post was triggered by Crunchy Data's recent article and the YugabyteDB approach to using Postgres as an almost stateless entry point, which parses incoming analytic queries and splits the work among multiple instances managed by another database system that fits the task of storing and processing massive data volumes and can execute relatively simple queries.

"Get or create" is a very common operation for syncing data in the database, but implementing it correctly may be trickier than you may expect. If you ever had to implement it in a real system with real-life load, you may have overlooked potential race conditions, concurrency issues and even bloat!

In this article I explore ways to "get ot create" in PostgresSQL.

About a month ago I presented a keynote at Swiss PGDay 2024 about the state of the Postgres community. My talk included a couple charts illustrating the evolution and current state of various parts of the community - what works fine and what challenges will require more attention.

Judging by the feedback, those charts are interesting and reveal things that are surprising or at least not entirely expected. So let me share them, with a bit of additional commentary.

Chaired by Karen Jex, the PGConf.EU Program Committee (Afsane Anand, Andreas Karlsson, Derk van Veen, Gülçin Yıldırım Jelínek, and Stefan Fercot) published the schedule for the 2024 conference.

We just published the PGConf.EU 2024 schedule and I couldn't be more excited to share it with everyone. We hope you love the talks we've chosen as much as we do.

A lot of the work of the talk selection committee (a.k.a. program committee, CfP committee or CfPC) happens behind closed doors, and people are understandably interested in how many submissions we had, how the process works, how the talks were chosen etc.

Tuning SQL is an integral part of most developer’s or DBA’s primary responsibilities. Understanding and dissecting problematic execution plans are critical steps in the overall tuning process.

One key ingredient of SQL performance tuning, along with the runtime plan, is object statistics. Looking only at the execution plan is often insufficient for making solid recommendations; we need a fair understanding of current object statistics.As the saying goes,

Behind every optimized SQL query lies a deep understanding of the underlying object stats.

We invite you to the 14th annual PostgreSQL Conference Europe that will take place in Athens on October 22-24!

The schedule for the conference has now been published, and as you can see, we’ll have a great, diverse speaker lineup this year:

https://www.postgresql.eu/events/pgconfeu2024/schedule/

• PostgreSQL in Google Summer of Code:

The PG Day France took place on June 11th and 12th in Lille, my hometown. It is the event of the French PostgreSQL community that settles in a different city each year. The opportunity was too good for me and I met many people from all over France and its surroundings, to discuss PostgreSQL during two days of workshops and conferences.

Join me and PostgreSQL/Kubernetes expert Leonardo Cecchi in Athens on October 22 for the first-ever public training session on CloudNativePG at the 2024 PostgreSQL European Conference, where you’ll master essential day-2 operations for PostgreSQL in Kubernetes. Learn from the founders and maintainers of CloudNativePG through hands-on sessions covering monitoring, scaling, automated failover, and more.

© Laurenz Albe 2024

Useful as memory overcommit may be for other applications, it is bad news for your PostgreSQL database. With this article I am trying to raise the awareness of this problem. I'll give you hints for the proper kernel configuration and discuss the implications for a containerized setup.

Concurrency control in databases ensures that multiple transactions can occur simultaneously without causing data errors. It’s essential because, without it, two people updating the same information at the same time could lead to incorrect or lost data. There are different ways to manage this, including optimistic locking and pessimistic locking. Optimistic locking assumes that conflicts […]

I'm pleased to be able to formally announce the PostgreSQL Hacking Workshop, as well as our first two topics, planned for August and September 2024. 

Read more »

This story is about a controversial PostgreSQL feature - query plan freezing extension (see its documentation for details) and the code's techniques underpinning it. The designing process had one specific: I had to invent it from scratch in no more than three months or throw this idea away, and because of that, solutions were raised and implemented on the fly. Time limit caused accidental impromptu findings, which can be helpful in other projects.

PostgreSQL Connection Poolers

The reason ot use connection poolers is, that it is relative expensive to open a new connection to a PostgreSQL server.

This is due to the architecture of PostgreSQL as each connection is a forked process. This is obviously taking some time, in fact longer than checking the user credentials.

PostgreSQL has a rich set of features designed to handle complex queries efficiently. Much like any database system, however, its performance can degrade over time due to inefficient queries, improper indexing, and various other factors. To tackle these challenges, PostgreSQL provides several tools and extensions, among which `pg_stat_statements` stands out as a critical component for performance monitoring and tuning.

Introduction to pg_stat_statements

• Gülçin Yıldırım Jelinek is organizing DivaConf in Turkey.

To preload, or not to preload, that is the question:
Whether ’tis nobler in the ram to suffer
The slings and arrows of pointer functions,
Or to take arms against a sea of backends,
And by alter role limit them: to session, to user

— William Shakespeare, DBA (probably)

Introduction

Data cloning from one table to another in relational databases is a commonly used process to copy data from an existing table to a new or pre-existing table definition within the same database. This process can be performed for various reasons, such as data backup, information replication, and testing, among other purposes. Postgres and other third-party tools offer several techniques to clone data from one table to another.

Indexes are, by far, the most feature related to performance provided by every general purpose relational database. Without indexes, there are no such things as efficient search operations, no unique constraints, and no primary keys. Achieving good performance on reasonable amounts of data without indexes is therefore totally impossible.