Sammlung von Newsfeeds

Vacuum is one of the sub-systems in PostgreSQL which gets improved with each release.  I have checked past five releases and each has quite a few improvements for vacuum.  Following the trend, there are a number of improvements in vacuum in the upcoming PostgreSQL release (v13) which are covered in this blog.

I am often asked how someone can learn more about Postgres, particularly the internals. There is the Postgres developer page, which has links to many resources, and the developer's FAQ item about learning about the internals.

Everybody who has ever written any kind of database application had to use time and date. However, in PostgreSQL there are some subtle issues most people might not be aware of. To make it easier for beginners, as well as advanced people, to understand this vital topic I have decided to compile some examples which are important for your everyday work.

Can I use PostgreSQL in Kubernetes and expect to achieve performance results of the storage that are comparable to traditional installations on bare metal or VMs? In this article I go through the benchmarks we did in our own Private Cloud based on Kubernetes 1.17 to test the performance of local persistent volumes using OpenEBS […]

Advanced PostgreSQL high-availability and disaster recovery strategies designed to maximize uptime involve distributing database clusters across multiple data centers. While on the surface, this may seem intuitive (e.g.

When writing software, it is necessary to decide whether to use external facilities available in command-line tools, libraries, frameworks, and the operating system, or write the facilities yourself. Why would you write them yourself? You might be worried about adding reliance on an external facility or a facility might not have sufficient flexibility or performance.

This is my fourth annual post on who contributes to PostgreSQL development. See previous posts in this series for methodology. I calculate that this year, 189 people were primary authors of at least one PostgreSQL commit. 37 of those people accounted for 90% of the new lines, and 12 people accounted for 66% of the new lines. In total, there were 2127 commits by 26 committers.

Tools like the PostgreSQL Operator make it easy to get PostgreSQL up and running on Kubernetes, but what about actually accessing your Postgres databases?

For businesses handling personal data, the security of their database is a serious matter. Faced with an increasing number of attacks, organizations must improve their security and compliance policies, enhance their database infrastructure, and ensure all proper security protocols are in place. As part of the ongoing PostgreSQL Webinar Series, 2ndQuadrant hosted a webinar on […]

One of the least-appreciated PostgreSQL extensions is the powerful PgRouting extension, which allows routing on dynamically generated graphs.

PostgreSQL 13 introduces two useful features to enhance automated validation of physical backups: backup manifests and a new tool called pg_verifybackup. In this short article I attempt to provide an overview of them. As you probably know if you are familiar with PostgreSQL and the PostgreSQL Community, I have been promoting business continuity and disaster […]

PostgreSQL Person of the Week Interview with Jimmy Angelakos: I grew up in Athens, Greece, then went to Scotland to study at the University of Aberdeen. After 15 years of working in the industry with Open Source tools, I found myself working with my favourite database, as Senior PostgreSQL Architect at 2ndQuadrant in Edinburgh. Music is a big part of my life, I love listening to diverse genres, a long time ago I played in a rock band, and in the more recent past I presented a music-oriented radio show.

Before Postgres 12, queries specified as common table expressions (WITH clauses) always behaved as optimization barriers, meaning that common table expression queries were executed independently, and were not moved to later parts of the query.

For a standby node to be used in a repmgr configuration, it has to be registered.  Normally you do this by allowing repmgr to clone the standby first.  If a node was not cloned by repmgr, you can nonetheless register it using the -F/–force option.  This is useful for cases where you want to use another tool (like pgbackrest) to backup and restore your clusters.

The apt.postgresql.org has been extended to cover the arm64 architecture.

We had occasionally received user request to add "arm" in the past, but it was never really clear which kind of "arm" made sense to target for PostgreSQL. In terms of Debian architectures, there's (at least) armel, armhf, and arm64. Furthermore, Raspberry Pis are very popular (and indeed what most users seems to were asking about), but the raspbian "armhf" port is incompatible with the Debian "armhf" port.

This is the second part of my blog “My Favorite PostgreSQL Extensions” wherein I had introduced you to two  PostgreSQL extensions, postgres_fdw and pg_partman. In this part I will explore three more.

Postgres must guarantee durability and good performance. To meet these objectives, Postgres does writes to the file system and storage in the background as much as possible. In fact, there are only two major cases where writes happen in the foreground:

This is the second blog in a series of blogs attempting to walk you through the query optimization process. We started from the very basics of understanding the “EXPLAIN” command. Reading part 1 is not a prerequisite, however, if you wish to understand how you can reconstruct a query from a given plan, Optimizing SQL – Step 1: EXPLAIN in PostgreSQL – Part 1 may help.

I was doing some testing recently with parallel backup feature along with backup manifest feature which was one of the last feature that got committed to PostgreSQL 13. Parallel backup is not committed to PG yet, it is currently under discussion in the hackers community. I was trying to take parallel backup of a database that contained multiple tablespaces and ran into some issues on how to pass multiple tablespaces to pg_basebackup. So decided to write this small blog on how to do that. This write-up might not be worthy of a blog but it will help someone who is facing the same issue.

Partitioning is one of the coolest features in the latest PostgreSQL versions. PostgreSQL 10 introduced native partitioning and more recent versions have continued to improve upon this feature. This article provides a guide to move from inheritance based partitioning to declarative partitioning, using the native features found in PostgreSQL 11+. The process itself moves the […]