Neues vom PostgreSQL Planet

Howto guide for upgrading PostgreSQL from version 14 to 15 on Ubuntu, after its upgrade from version 22.10 to 23.04 (Lunar Lobster).

As a PostgreSQL DBA, memory management is one of the most important aspects of ensuring optimal database performance. PostgreSQL stores data in memory for faster access, so memory usage can have a significant impact on query execution times. If less memory is allocated, PostgreSQL will have to read data from disk more often, resulting in slower query execution. If too much memory is allocated, other processes on your system may be starved for memory.

In this post, we’ll discuss some parameters for PostgreSQL memory assignment.

I just attended my fifth in-person conference of 2023, four of them PostgreSQL focused. I look forward to attending more in the coming months to share and learn about the platform, meet more community members, and continue to invest however I can. Unfortunately, between these conferences, several recent blogs, and the general attitude towards PostgreSQL … Continue reading "In defense of PostgreSQL MVCC and Vacuuming"

LZ4 and ZSTD pg_dump compression

I wrote a “pg_dump compression specifications in PostgreSQL 16” post a while ago. Frankly speaking, I thought new compression methods would not be implemented in PostgreSQL until 2-3 years from now. Probably demand is so high that LZ4 and ZSTD made their way into PostgreSQL 16!

Here is a fun little view on the progress of PostgreSQL. Consider the number of “CREATE SOMETHING” commands each release contains. As more features are added over time, more such CREATE commands are added.

PostgreSQL has a separate command-line tool that’s been available for decades and is included with any installation of PostgreSQL. Many long-term PostgreSQL users, developers, and administrators rely on psql to help them quickly connect to databases, examine the schema, and execute SQL queries.

Knowing how to install and use basic psql commands is an essential skill to have for anyone that will connect to PostgreSQL.

Indexes are generally considered to be the panacea when it comes to SQL performance tuning, and PostgreSQL supports different types of indexes catering to different use cases. I keep seeing many articles and talks on “tuning” discussing how creating new indexes speeds up SQL but rarely ones discussing removing them. The urge to create more and more indexes is found to be causing severe damage in many systems. Many times, removing indexes is what we should be doing first before considering any new indexes for the benefit of the entire system. Surprised?

PostgreSQL Person of the Week Interview with Sébastien Lardière: My name is Sébastien Lardière, and I am from France, near the city of Clisson. The place is best known for the Hell Fest, a big metal festival, even if there are a lot of other things to do and see; and drink.

Structured Postgres Regression Tests

I've been using pg_regress tests for a while. It's generally a great way to ensure the behavior of your Postgres code works and continues working as expected. However, as my tests became larger, I started getting lost in them; and there are limits as to what you can test by having a psql session.

Hello folks, This small post is about PGSQL Phriday #007, the “trigger” PGSQL Friday. I’m very happy that this challenge “triggered” so many discussions that a French friend of mine asked what was all this trend for blog posts about triggers! Why is this fanfare about triggers recently all around? Databases already have a handful of people talking bs than we have a new one? (Anyway you're living in the edge there 🤣)

Crunchy Data is getting ready to be at RailsConf 2023 in Atlanta next week and we’ve been thinking about our Rails and ActiveRecord users and customers. One of the easiest ways to improve query performance using an ORM is to lean on as much SQL as you can. I’m going to walk through some of the ActiveRecord basics and how to use some smart SQL to work around N+1 query problems.

The easy CRUD Basics with ActiveRecord

What do I mean by "CRUD"? It's short-hand for create-read-update-delete. For instance, ORMs make it so nice to do any of the following.

The PgOSM Flex Project is looking forward to the 0.8.0! If you aren't familiar with PgOSM Flex, it is a tool that loads high quality OpenStreetMap datasets to PostGIS using osm2pgsql. I have a few examples of using OpenStreetMap data loaded this way.

New Features in the v1.2 release of pg_graphql

Highly available PostgreSQL clusters are an essential component of modern database infrastructures. These clusters provide critical services to organizations that must ensure that their applications have reliable and continuous access to their databases. In such clusters, auto-failover is a crucial feature that ensures that the cluster continues to operate even when one of the nodes fails. However, network latency can pose significant challenges in auto-failover situations.

Data-at-rest encryption is essential for compliance with regulations that require the protection of sensitive data. Encryption can help organizations comply with regulations and avoid legal consequences and fines. It is also critical for securing sensitive data and avoiding data breaches.

PostgreSQL does not natively support Transparent Data Encryption (TDE). TDE is a database encryption technique that encrypts data at the column or table level, as opposed to full-disk encryption (FDE), which encrypts the entire database.

Tables storing historical data like audit logs or saved webhooks will drastically increase in size over time. But deleting old rows will be slow and the disk space will only be reclaimed when rebuilding them. This can take hours with big tables. But the cleanup workflow can be optimized by transparently splitting those tables into many smaller (e.g. monthly) partitions. Dropping an entire partition is very fast because just the linked partition file on disk will be removed, reclaiming the used disk space.

PostgreSQL lightweight-lock manager, with its interface in src/include/storage/lwlock.h, is a facility aimed at controlling the access to shared memory data structures. One set of routines is at the center of this post:

Introduction

PostgreSQL 16 will normally (as there is always a risk of seeing something reverted in the beta phase) include this commit: Allow logical decoding on standbys.

On 7th of April 2023, Tom Lane committed patch: Add array_sample() and array_shuffle() functions.   These are useful in Monte Carlo applications.   Martin Kalcher, reviewed/adjusted by Daniel Gustafsson and myself   Discussion: https://postgr.es/m/9d160a44-7675-51e8-60cf-6d64b76db831@aboutsource.net Arrays are supported in PostgreSQL since forever, documentation for Pg15 lists 18 functions.

SQL and numeric constants

NEW in PostgreSQL 16 – support for underscores in integer and numeric constants! I already wrote about support for different notations in the upcoming PostgreSQL 16. This new major version also implements the SQL:202x standard (draft), allowing you to use underscore separators for integers and numeric constants.