Neues vom PostgreSQL Planet

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

As databases grow, managing large tables becomes more challenging. Table partitioning is a tried-and-tested approach that helps break down large tables into smaller, more manageable segments, enhancing performance, maintainability, and scalability.

What is Table Partitioning?

As databases grow, managing large tables becomes more challenging. Table partitioning is a tried-and-tested approach that helps break down large tables into smaller, more manageable segments, enhancing performance, maintainability, and scalability.

What is Table Partitioning?

Checking logs for pgdoc.link I noticed that some people where searching for system views, like pg_stat_activity, or pg_stat_all_tables in Pg 9.3. Now, these will work. This increased total number of known keywords from 1840 to 1883. Not much, but it's a progress 🙂

A note about mgirating from other databases…

PostgreSQL is super solid in enforcing (well established) constraints!

Well, let’s turn that around: SQLite3 is somehow too flexible in allowing you to store data!

We all know that.

And we all have been fighting situations where we have a well defined structure in SQLite3 and, ocne we try to migrate to PostgreSQL, a bad surprise arrives! As an example, today I was trying to migrate a Django project with the built-in loaddata from a dumpdata, and sadly:

We at Tembo truly love Postgres. We're convinced everyone would love Postgres just as much as we do if only upgrading Postgres between major versions wouldn't be so darn difficult.

On 1st of November 2024, Michael Paquier committed patch: Add SQL function array_reverse()   This function takes in input an array, and reverses the position of all its elements. This operation only affects the first dimension of the array, like array_shuffle().   The implementation structure is inspired by array_shuffle(), with a subroutine called array_reverse_n() that … Continue reading "Waiting for PostgreSQL 18 – Add SQL function array_reverse()"

• PGConf.EU 2024, held in Athens from October 22-25, was the biggest PostgreSQL event in the world yet! It was organized by:

At pgConf.eu in Athens - by the way, reported to be the largest PostgreSQL conference to date, attesting to the software’s undeniable success! - I got into a sort of interesting hallway discussion. I guess my chat companion was relatively new to the ecosystem - and said something along the...

PostgreSQL Person of the Week Interview with Rushabh Lathia: My name is Rushabh Lathia, I am recognized as a PostgreSQL Contributor. Originally, I hail from the vibrant state of Gujarat, India, known for its rich culture, delectable cuisine, and warm hospitality. However, for the past few years, I have been residing in the beautiful city of Pune, Maharashtra. While I cherish my roots in Gujarat, Pune has become my second home, a place where I’ve found my stride, forged lasting connections, and embarked on new adventures.

I wrote a blog post in the past about a generic approach on how to expose databases in Kubernetes with Ingress controllers. Today, we will dive deeper into PostgreSQL with ingress and also review two different ways that can be taken to expose the TCP service. The goal is to expose multiple PostgreSQL clusters through […]