Sammlung von Newsfeeds

Recently I joined Justin Garrison and Autumn Nash for episode “FROM guests SELECT Andrew” of Ship It, a Changelog podcast.

We had a great conversation! I made bullet point notes from the episode, and added extra details.

Let’s get into it!

Spring is in full swing as we bring you the hottest winter news of the January Commitfest. Let’s get to the good stuff right away!

Previous articles about PostgreSQL 17: 2023-07, 2023-09, 2023-11.

We are excited to launch pg_timeseries: a PostgreSQL extension focused on creating a cohesive user experience around the creation, maintenance, and use of time-series tables. You can now use pg_timeseries to create time-series tables, configure the compression and retention of older data, monitor time-series partitions, and run complex time-series analytics functions with a user-friendly syntax.

Recently, while debugging a performance issue of a CREATE INDEX operation, I was reminded that PostgreSQL might produce temporary files when executing a parallel query, including parallel index creation, because each worker process has its own memory and might need to use disk space for sorting or hash tables.

Thanks to Peter Geoghegan answering this pgsql-admin email thread.

Howto guide for upgrading PostgreSQL from version 15 to 16 on Ubuntu, after its upgrade from version 23.10 to 24.04 (Noble Numbat).

Let's recap the first part of "Deep Dive into PostgREST," where we explored the basic functionality to expose and query any table using an API, demonstrated using cURL. All it took was to set up a db-schema and give the db-anon-role some permissions. But unless you are creating the simplest of CRUD applications, this only scratches the surface.

Introduction

When you send a query to PostgreSQL, it normally would go through stages of query processing and return you the results at the end. These stages are known as:

Learn about synchronous and asynchronous replication in PostgreSQL, including how they work and when to use each.

The post Understanding Synchronous and Asynchronous Replication in PostgreSQL – What is Best for You? appeared first on Stormatics.

Documenting a pattern for making multi-tenant applications global by distributing the data, using only standard PostgreSQL functionality.

PGConf.DE 2024 was last month, the feedback is now closed - time for a review.

PostgreSQL's pg_repack extension offers a solution to database maintenance challenges by optimizing table storage without disrupting operations. It tackles issues like bloat and fragmentation efficiently. Learn about its operation, options, and restrictions for effective database management.

The post Removing Bloat with pg_repack Extension appeared first on Stormatics.

Nowadays, most Debian packages are maintained in Git repositories, with the majority hosted on Debian's GitLab instance at salsa.debian.org.Debian is running a "vcswatch" service that keeps track of the status of all packaging repositories that have a Vcs-Git (and other VCSes) header set. This status is stored in a PostgreSQL database, which is then utilized by Debian's package maintenance infrastructure for various purposes.

In my previous article on how to submit patches by email for PostgreSQL, I skipped over whether patches should be split up. Let’s discuss that now.

(See the previous article, as well as general Git documentation, for the technical details of how to split up a patch. Here, I’m only talking about why.)

Boy howdy that went fast.

There are several benefits to using temp tables in SQL DML operations. Given my long relationship and work on Oracle databases, I am highly familiar with the Oracle style “Global Temporary Tables” and have used them to solve many performance issues when complex joins are involved. That’s why I was excited when Gilles Darold released the extension “pgtt” for PostgreSQL. I was even more excited when Google released the extension for use in the Google CloudSQL for Postgres and AlloyDB for Postgres products.

Introduction – Invisible or Hidden Index.

Any database conversion offers valuable insights into how things work across platforms or how to achieve similar functionality in the target database. Invisible indexes are one such feature that is often requested in PostgreSQL when migrating away, due to their use case of create new or marking existing indexes as invisible for the optimizer.

Making indexes invisible can be beneficial for several use cases, such as:

In this post, let’s see how to control psql from outside with the coproc functionality in bash.

PostgreSQL Person of the Week Interview with Philip Marks: London boy, life long Arsenal fan! Married, two kids, well they’re both in their 20s now! Now currently living on the Welsh English borders in the hills, thank you broadband for finally getting there.

For PGSQL Phriday #016, Ryan’s asked us to “…discuss your process for tuning difficult queries. Specifically, try to focus on that one problematic query that really challenged you and you always use it as an example when helping or teaching others your methods.” Here are the generic steps I take, mostly in order. After that […]

The primary motivation behind boringSQL is to explore the robust world of SQL and the PostgreSQL ecosystem, demonstrating how these "boring" tools can cut through the ever-increasing noise and complexity of modern software development. In this series, I'll guide you through building a simple yet fully functional application—a Time Off Manager. The goal of this project is not only to demonstrate practical database/SQL approaches but also to provide a complete, extendable solution that you can immediately build upon.