Neues vom PostgreSQL Planet

PostgreSQL Person of the Week Interview with Sarah Conway: I am from Southern California, where I’ve spent a lot of time hiking in the beautiful local deserts and pursuing many creative endeavors. I’m a current developer advocate at Timescale and the founder of Talk to Me About Tech where I help companies like Data Bene with developer relations consulting & services.

Backups are a lot like Schrödinger’s cat. You think you have them, but when it’s time to check, they might not be what you expect! Even…

Indexes are crucial for accelerating database queries, and enhancing the performance of your PostgreSQL applications. However, not all indexes function the same way. Composite and partial indexes are two common types, each with distinct purposes and effects on performance. In this blog, we'll dive into what composite and partial indexes are, how they operate, and when to use them to achieve the best results for your database.

Forgive me Postgres community, for it has been five months since my last PGXN v2 Update. In my defense, it has been super busy! The time went into ongoing community discussions, planning, designs, and the start of implementation. Join me below for the lowdown.

I’ve submitted a lot of talk proposals to a lot of Postgres conferences over the years. Some got accepted, many more were not. And I’ve been on the other side of this process too, as a member of the CfP committee responsible for selecting talks. So let me give you a couple suggestions on how to write a good talk proposal.

People tend to think that a SELECT-operation is always a read. This might look obvious and make sense but does it actually hold true? Is a SELECT-statement a read? Can we really determine from the statement what it does so that we can safely load balance? In fact, no. The fact that something seems to be a pure read does not make it a read - and that has huge implications which will be discussed in this short but
important post.

CloudNativePG 1.24 introduces a highly customisable approach to managing PostgreSQL synchronous replication through the new .spec.postgresql.synchronous stanza. In this article, I’ll guide you through configuring synchronous replication within a single Kubernetes cluster and across multiple clusters. I’ll explore quorum-based and priority-based replication methods, highlighting their benefits and trade-offs. Additionally, I’ll explain how to adjust the synchronous_commit setting to strike the right balance between data durability and performance.

In an increasingly globalized world, companies and organizations are leveraging distributed systems to handle massive amounts of data across geographically separated locations. Whether it is for ensuring business continuity, disaster recovery, or simply improving data access for users in different regions, replication of data between nodes situated in diverse geographical locations has become a critical aspect of modern database systems.

• Sarah Conway organized a great community outreach campaign for Timescale's State of PostgreSQL survey for 2024, promoted by community partners:

PostgreSQL Person of the Week Interview with Philippe Noël: My name is Philippe. I was raised in the woods of Québec, in a small town called Rivière-du-Loup. I moved to the US for university, and since then have lived a bit all over the place, but I’m primarily based in NYC.

The focus of your backup strategy should not be backups. Instead, you should be focused on restoring databases, because, after all, that’s what matters. Simply having a backup is fine. However, if you can’t restore your data from that backup, it doesn’t matter. On top of this is the ability to ensure you can restore your databases to a point in time, not simply to the last full backup. That ability requires a different approach to how you perform your backups.

One my customer asked me "what tables holds references to table pg_roles"?

The reply is simple - none. pg_roles is view. But this view uses very important table pg_authid. For custom tables we can use a query:

In this post, you’ll learn a recipe that you can use to “shrink” a large table. This is a good fit when only a portion of the data is accessed, the big table has become unwieldy, and you don’t want a heavier solution like table partitioning.

This recipe has been used on tables with billions of rows, and without taking Postgres offline. How does it work?

Postgres testing details

These are the PostgreSQL instance details where this was tested:

Backups are dead. Now that I have your attention, let me clarify. Traditional backups have earned a solid reputation for their reliability over time. However, they are dead in the sense that a backup is essentially useless until it's restored—essentially "resurrected." In this post, we'll explore best practices for managing PostgreSQL snapshots and backups using pgBackRest. We will then provide some guidance of how you apply these techniques in Kubernetes using the Postgres Operator (PGO) from Crunchy Data.

Recently, I had to address a support case where a user was struggling to perform Point-in-Time Recovery in his PostgreSQL cluster using streaming replication. We have already discussed how to achieve a successful recovery in our past webinars, but the question remains: what is the impact on the standby servers? In this specific case, the user was unsure whether he needed to apply the Point-in-Time Recovery steps on the standby as well, and after doing so, the standby refused to connect to the primary.

I previously blogged about ensuring that the “ON CONFLICT” directive is used in order to avoid vacuum from having to do additional work. You can read the original blog here: Reduce Vacuum by Using “ON CONFLICT” Directive

I previously blogged about ensuring that the “ON CONFLICT” directive is used in order to avoid vacuum from having to do additional work. You can read the original blog here: Reduce Vacuum by Using “ON CONFLICT” Directive

Designing a database schema, whether for a new application or a new feature, always raises a lot of questions. The choices you make can have a big impact on how well your database performs and how easy it is to maintain and scale. Whether you’re just getting started with PostgreSQL or consider yourself a seasoned pro, it’s easy to rely on old habits or outdated advice. In this article, I want to take a fresh look at one of those topics that often sparks debate: the use of ENUMs in PostgreSQL.

Timescale PostgreSQL Survey

Timescale Inc. is a well known company in the PostgreSQL community, mostly because of their PostgreSQL extension TimescaleDB.

The survey for 2024 is open now until September 30th 2024. If you could spare some time to answer the questions, the community would appreciate it.

The results will be published shortly afer the survey has ended to everyone.

Introduction

Running ANALYZE (either explicitly or via auto-analyze) is very important in order to have uptodate data statistics for the Postgres query planner. In particular after in-place upgrades via pg_upgrade, ANALYZE needs to be run in order to have any query statistics at all. As ANALYZE samples only parts of the blocks in a table its I/O pattern looks more like random access than sequential read.