Neues vom PostgreSQL Planet

Was asked recently about optimization of interesting case. There was table like: =$ CREATE TABLE input_data ( category_id INT8, object_id INT8, interaction_ts timestamptz, interaction_type TEXT, interaction_count INT4 ); And there was a code that was grouping it all by sum()ing interaction_count per category, object, interaction_type, and timestamp truncated to hour.

In our this installment about character encodings, locations, and locales in PostgreSQL, we’re talking about locale providers.

A reminder about locales

A “locale” is a bundled combination of data structures and code that provides support services for handling different localization services. For our purposes, the two most important things that a locale provides are:

My notes from PGConf.EU 2024 in Athens, Greece. Talks, extension summit, diversity committee meeting, Xata Postgres dinner, and more!

Transitioning from Oracle to PostgreSQL can be a transformative experience for database administrators because of the subtle differences between the two technologies. Understanding how the two handle concurrency differently is critical to managing highly concurrent workloads.

Next month, I'll be hosting a discussion of Melanie Plageman's talk, Intro to Postgres Planner, given at PGCon 2019. You can sign up using this form. To be clear, the talk is not an introduction to how the planner works from a user perspective, but rather how to hack on it and try to make it better and perhaps get your improvements committed to PostgreSQL. If you're interested, please join us.

Large language models (LLM) provide some truly unique capacities that no other software does, but they are notoriously finicky to run, requiring large amounts of RAM and compute.

That means that mere mortals are reduced to two possible paths for experimenting with LLMs:

A pgconf EU talk recap covering how the expand-contract pattern and pgroll enable zero-downtime schema changes and rollbacks.

For database experts well-versed in Oracle, moving to PostgreSQL opens up new indexing methods that differ significantly in terms of structure, management, and optimization. While both databases leverage indexing to enhance query speed, their approaches vary, particularly in terms of available types, performance tuning, and maintenance. This guide clarifies key differences and provides practical strategies for effectively handling indexes in PostgreSQL.

Understanding Indexing in Databases: The Basics

Kubernetes (and OpenShift) have become highly popular deployment methods to run PostgreSQL at scale. While Kubernetes is an excellent way of running things it also helps to make things such as consistency, compliance and a lot more easier to achieve. In this post you will learn how to deploy the Zalando operator for PostgreSQL and make things work quickly and easily.

• PGConf.Brazil 2024 was held in Belo Horizonte, Brazil, on November 7th-8th. It was organized by:

  • Kenia Galiego
  • Danielle Monteiro
  • Euler Taveira
  • Fernando Franquini
  • Lucio Chiessi
  • William Ivanski

  The Call for Papers committee was formed by:

psql is a CLI editor that ships with Postgres. It’s incredibly powerful for working with Postgres, and doesn’t take too much of a learning curve to start to get comfortable so you can really feel like an expert working with your database.

Just a rundown of a few things to get you started:

Once connected in psql you can get an idea of all utility commands available with:

\?

A handy thing I use all the time is \d.

The PL/pgSQL language, available as a default extension in PostgreSQL, provides powerful tools and flexibility for application developers to build complex, enterprise-scale functionality within the database. Through PL/pgSQL’s functions and procedures, developers can choose different volatility categories—IMMUTABLE, STABLE, or VOLATILE—that offer varying performance benefits, especially in terms of result caching and data state awareness. For a deeper dive, refer to the official documentation.

Whether you are designing a standalone application or a microservice, you will inevitably encounter the topic of sharing identifiers. Whether it’s URLs of web pages, RESTful API resources, JSON documents, CSV exports, or something else, the identifier of specific resources will be exposed.

/orders/123 /products/345/variants/1

While an identifier is just a number and does not carry any negative connotations, there are valid reasons why you might want to avoid exposing them. These reasons include:

Continuing our series on Crunchy Postgres via Automatin, we’re here this week to discuss the highlights of our latest release line, v2.

It is never immediately obvious how to go from a simple SQL query to a complex one -- especially if it involves intricate calculations. One of the “dangers” of SQL is that you can create an executable query but return the wrong data. For example, it is easy to inflate the value of a calculated field by joining to multiple rows.

Let’s take a look at a sample query. This appears to look for a summary total of invoice amounts across teams. If you look closely, you might see that the joins would inflate a team’s yearly invoice spend for each team member.

PostgreSQL is widely known for its Multi-Version Concurrency Control (MVCC) model, which allows multiple transactions to occur simultaneously without interfering with each other. 

However, one side effect of MVCC is the creation of dead tuples—old versions of data rows that are no longer needed but still occupy space. 

Dead tuples also lead to a phenomenon known as table bloat, which refers to the excessive unused space in a table caused by dead tuples that haven't been cleaned up, resulting in inefficient storage and reduced performance

A key aspect of maintaining backup integrity is understanding data checksums. Without proper checksum validation, detecting data corruption becomes virtually impossible. Therefore, we will start with The Importance of Data Checksums.

The Importance of Data Checksums

Ensuring data integrity is crucial for the reliability of any database system. Data checksum validation is essential for ensuring data integrity. Checksums help detect data corruption caused by hardware issues.

This year, the 14th annual PostgreSQL Conference Europe (PGConf.EU for short) took place from October 22nd to October 25th in the historic city of Athens, Greece, featuring awesome content and awesome Mediterranean weather.

A round up of the fourth PGConf.be

The shared presentations are online, as are a couple of recordings and turtle-loading have-a-cup-of-tea locally stored photos.

TL;DR Kubernetes was built for stateless apps, but as more stateful applications (like databases) run on it, operators include quite heavy implementations to Kubernetes workload management API (such as StatefulSets) deficits. While creating custom methods allows flexibility and faster time to market, it also leads to inconsistency and complexity. In this blog, I want to […]