Latest Blog Posts

Posted by Ashutosh Bapat on 2025-07-19 at 13:58

 A couple weeks back, I received my speaker's gift from POSETTE: An Event for Postgres


The woolen elephant was claimed by my daughter as soon as the parcel ws opened (and gave me this photo in exchange). My son has been collecting the activity books and stickers for some time now. He claimed those two. Me and my wife are eyeing the pair of socks. The most precious, a handwritten personal thank you note, remains with me!


PostgreSQL conferences are an important part of any PostgreSQL community member's life, developers, users, DBAs, anybody who is involved with PostgreSQL in any role. There are plenty of them that run throughout the year. Some of them are meetups which run for a few hours, some are PGDays which usually run for a day and then some are full blown conferences which run for at least a couple of days and have auxilliary events surrounding the conference days. Each of them is unique because of geography, language or the content. While their schedules facilitate networking and people interaction more, they are centered around talks. The conferences usually do not pay their speakers but they convey their appreciation through speaker's gifts. I thought I would post about speaker's gifts I have received so far and are still with me, the ones in the photo.


Leftmost is a bottle, made of mostly bamboo and thus environment friendly, which I received from Hari Kiran who runs https://lnkd.in/giwyMh96 and PGDay Hyderabad. Next to that is a black bottle I received from PGConf India last year. PGConf.Asia, when it was hosted in Tokyo, presented a steel glass with each speaker's name embossed on it. That is the oldest surviving gift but the steel is still strong and shiny as it was back then. I actually liked the box with deep shiny blue satin lining that contained it. I still have the box too.

The frame is again from PGConf.Asia when it was hosted in China but virtually. It accompanied me in my office in the COVID lock-down. It's eyes are peculiar and make it quite lively. I do
[...]

pgroll is on a roll! We hit 5k stars on Github
Posted by Gülçin Yıldırım Jelínek in Xata on 2025-07-18 at 14:15
Pgroll is an open-source, zero-downtime, reversible schema migration tool built exclusively for Postgres. We’ve reached 5k stars on GitHub!

July Prairie Postgres PUG
Posted by Henrietta Dombrovskaya on 2025-07-18 at 02:24

I am very thankful to everyone who attended yesterday’s meetup! I must be completely honest: I am always pleasantly surprised when people attend our summer meetups. There are so many better things you can do in Chicago in summer!

I am especially thankful to our speaker, Robert Ismo, who delivered a very interactive presentation about AI and Postgres and kept the audience engaged. In fact, the lively discussion lasted until I had to go to catch my train and asked people to relocate to continue their discussion, so I can’t even tell how long it lasted :).

Once again, thank you, everyone, and I will see you on September 10!

OrioleDB fastpath search
Posted by Alexander Korotkov on 2025-07-18 at 00:00

When you optimize the CPU time of a transactional database management system, it comes down to one question: how fast can you read a page without breaking consistency? In this post, we explore how OrioleDB avoids locks, trims memory copies, and — starting with beta12 — even bypasses both copying and tuple deforming altogether for fixed-length types during intra-page search. This means that not only are memory copies skipped, but the overhead of reconstructing tuples is also eliminated. The result: an even faster read path, with no manual tuning required.

Why page copies matter … and why they hurt

Every time a PostgreSQL backend descends an OrioleDB B-tree, it needs a consistent view of the target page. Instead of locking the whole page, OrioleDB keeps a 32-bit state word in the page header.

The low bits represent a change count that increments with every data modification; the high bits hold lightweight flags for "exclusive" and "read-blocked" states. A reader copies the necessary bytes, then re-reads the state, and retries if the counter has changed without using locks, yet achieving perfect consistency.

The following pseudo-code illustrates how to copy a consistent page image using a state variable for synchronization.

def read_page(page, image):
while true:
state = read_state(page)
if state_is_blocked(state):
continue

copy_data(image, page)

newState = read_state(page)
if state_is_blocked(state) or get_change_count(newState) != get_change_count(state):
continue

return

Copy only what you need

Obviously, copying the full 8KB page might be exhausting, especially when you only need it to locate a single downlink to navigate through the tree. That's why the OrioleDB page layout is chunked; the search code first grabs the small high-key strip (one high-key per chunk) to determine which chunk can possibly contain the key and then copies just that chunk.

The following pseudo-code illustrates how to copy only part of the page containi

[...]

One Year of Hacking Workshops
Posted by Robert Haas in EDB on 2025-07-17 at 20:24
I started running PostgreSQL Hacking Workshops just about one year ago, and I've run one each month, except for May, when we had pgconf.dev. Signups are now open for August, if you're interested in joining us for a discussion of Peter Geoghan's talk on Multidimensional search strategies for composite B-Tree indexes, but I'd also like to take a few minutes to summarize where we are after one year of hacking workshops, both the good and the maybe not quite as good. So here goes.
Read more »

A Guide to Deploying Production-Grade Highly Available Systems in PostgreSQL
Posted by semab tariq in Stormatics on 2025-07-17 at 13:46

In today’s digital landscape, downtime isn’t just inconvenient, it’s costly. No matter what business you are running, an e-commerce site, a SaaS platform, or critical internal systems, your PostgreSQL database must be resilient, recoverable, and continuously available. So in short

High Availability (HA) is not a feature you enable; it’s a system you design.

In this blog, we will walk through the important things to consider when setting up a reliable, production-ready HA PostgreSQL system for your applications.

Eliminate Single Point of Failure (SPOF)

A Single Point of Failure (SPOF) is any component in your system whose failure would cause the entire system to stop functioning. In PostgreSQL deployments, common SPOFs include:

    • A single power source or network path
    • A single witness node
    • A single proxy server and
    • A single connection pooler, etc

    If any one of these fails and you have no fallback, your application is down.

    There can also be scenarios where, for example, you have only a single backup node or a single monitoring node. If either or both go down, the application may continue to function normally without any immediate impact. Therefore, these components aren’t technically single points of failure for your PostgreSQL cluster. However, it’s still crucial to restore backup and monitoring capabilities as soon as possible, since they play an important role in long-term reliability, recovery, and observability.

    To identify single points of failure (SPOFs) in your cluster, begin by mapping out your entire architecture. List every component involved in supporting your PostgreSQL database storage, compute, network, witnesses, monitoring, backups, and so on. For each component, ask yourself: If this fails, what happens? If the answer is that the entire cluster would stop functioning, then that component is a SPOF and requires a proper fallback or redundancy plan.

    [...]

    Reaction to the PlanetScale PostgreSQL benchmarks
    Posted by Tudor Golubenco in Xata on 2025-07-16 at 16:00
    Our reaction to the PostgreSQL benchmarks published by PlanetScale and the results of running them against a Xata instance.

    SQLAlchemy versus Distributed Postgres
    Posted by Dave Page in pgEdge on 2025-07-16 at 02:55

    One of our customers recently asked if they could use their Python application built with SQLAlchemy with pgEdge, and were pleased to learn that they could. But what is SQLAlchemy, and what considerations might there be when working with a distributed multi-master PostgreSQL cluster like pgEdge Distributed Postgres?SQLAlchemy is “the Python SQL Toolkit and Object Relational Mapper” according to its website. Most famously, it is used for its ORM capabilities which allow you to define your data model and to manage the database schema and access from Python, without having to worry about inconveniences like SQL. A good example from my world is pgAdmin, the management tool project for PostgreSQL that I started nearly 30(!) years ago; pgAdmin 4 stores most of its runtime configuration in either a SQLite database, or for larger shared installations, PostgreSQL. Most of the database code for that purpose uses SQLAlchemy both to handle schema creation and upgrades (known as migrations) as it makes it trivial to manage.One of my awesome colleagues, Gil Browdy, took on the task of showing the customer how pgEdge can work in a distributed environment, and started with a simple script. The script shows the very basics of how we might get started working with SQLAlchemy and pgEdge, so let’s take a look at Gil’s example.

    Setup

    First, we need to get everything set up. We’re going to import the SQLAlchemy library, which we’ll be using with the psycopg PostgreSQL interface for Python, so we need to get them installed into a virtual environment:

    Code

    With the environment set up we can play with our script. First, the boiler plate to import the SQLAlchemy functions we need:Next, we’ll create connections to each of the three nodes in my pgEdge cluster:
    We define an array of connection strings, and then an  object for each:We need a table to work with to demonstrate that replication works, so we can define a SQLAlchemy  object. This is attached to a  object which is a collection that holds all table objects. The tables them[...]

    Switching PostgreSQL the conference calendars to Nextcloud
    Posted by Stefanie Janine on 2025-07-15 at 22:00

    Switching the PostgreSQL Conference Calendar Location

    Currently Calendar Location

    So far the conference calendars in https://proopensource.it/conference-calendar/ have been stored in Google Calendar.

    As that is a foreign ressource, I decided to move them away from Google.

    New Conference Calendar Location

    ProOpenSource OÜ has a Nextcloud instance running nearly from the start of the business. And Nextcloud has a calendare extension.

    I switched the private calendars away from Google quite some time ago, but the conference calendars have been a bit of work to get them running in an iframe.

    Now, as it is working nicely with the embedded calendars from the ProOpenSource Nextcloud instance, the change is public from 2025-07-16 on.

    Subscribe to Conference Calendars

    You can also subscribe directly to the calendars:

    End of Google Calendars

    Until the end of this year, 2025, I will publish new entries on both calendars, but in 2026 I will publish new calendar entries only Nextcloud, as shown and listed in Conference Calendar.

    I will also add a calendar entry on 2025-12-31 with a reminder to inform everyone who has or still is using the old Google calendars, that everyone who is still subscribed to the Google calendars will get a notification.

    Alternative Calendar

    For those who still want a Google calendar, there is another source available maintained by some people from the community, including me, on the PostgreSQL Person of the Week website.

    There is also a difference between the calendare on PostgreSQL Person of the Week and on Conference Calendar:

    Differences Between the two Calendars

    The one on PostgreSQL Person of the Week is one calendar containing both, conferences and Calls for Papers of conferences (CfP).
    And the one on Conference Calendar contains two calendars, one for Calls for Papers of conferences (CfP), and one for conferences.

    PostgreSQL storage: Comparing storage options
    Posted by Hans-Juergen Schoenig in Cybertec on 2025-07-15 at 05:52

    Storing and archiving data efficiently

    When it comes to designing and optimizing databases, one of the most critical aspects is the choice of storage options. PostgreSQL, like many other relational databases, provides various storage options that can significantly impact performance, data integrity, and overall database efficiency.

    In this case study, we'll delve into each of PostgreSQL's main storage options, their characteristics, and the factors that influence their choice, enabling you to make informed decisions about your database's storage strategy. You will also learn how you can archive data in a hybrid environment for long term storage. 

    What we will compare:

    • PostgreSQL row store (heap)
    • PostgreSQL column store
    • CSV files
    • Parquet files

    Each storage type serves a different purpose and can be used to achieve different goals.

    Sample data: Oracle audit logs

    Compliance is a key topic in database engineering and handling large volumes of audit data matters. Therefore taking data from the “Oracle unified audit trail” is a good way to demonstrate PostgreSQL capabilities.

    Using PostgreSQL heap storage

    For the purpose of this evaluation we have imported roughly 144 million rows into PostgreSQL using a “heap” (which is the default storage method):

    lakehouse=# SELECT count(*) FROM t_row_plain;
    
       count   
    
    -----------
    
     144417515
    
    (1 row)

    When storing a typical Oracle audit trails those 144 million rows will (without indexes) translate to roughly 500 bytes per entry which means that we can expect a table that is roughly 72 GB in size:

    lakehouse=# SELECT pg_size_pretty(
    pg_total_relation_size('t_row_plain')
    );
     pg_size_pretty 
    ----------------
     72 GB
    (1 row)

    Heaps have significant advantages but also disadvantages compared to other storage methods. While a normal table needs a lot of space compared to other storage options, we can index at will and access single rows in the most efficient way possible. 

    When talking about audit logs a he

    [...]

    Detection and resolution of conflicts in PostgreSQL logical replication
    Posted by Ajin Cherian in Fujitsu on 2025-07-15 at 01:00

    At this year’s PGConf.dev, the premier gathering for PostgreSQL contributors, developers, and community leaders, Zhijie Hou and I had the opportunity talk about the challenges and solutions around conflict handling in logical replication — a topic increasingly relevant as PostgreSQL adoption continues to grow.

    OrioleDB beta12: features and benchmarks
    Posted by Alexander Korotkov on 2025-07-15 at 00:00

    Since our last public update, OrioleDB has continued to evolve with a series of new releases. These updates refine the core engine, extend functionality, and improve performance across a range of workloads. Together, they move us closer to a beta release and lay the groundwork for broader adoption.

    What is OrioleDB?

    OrioleDB is a PostgreSQL storage extension that implements a custom Table Access Method as a drop‑in replacement for the default Heap storage engine. It is designed to address scalability bottlenecks in PostgreSQL’s buffer manager and reduces the WAL, enabling better utilization of modern multi-core CPUs and high‑performance storage systems.

    By rethinking core components such as MVCC, page caching, and checkpoints, OrioleDB improves throughput and predictability in transactional workloads without altering PostgreSQL’s user-facing behavior.

    What’s New in OrioleDB?

    Building on this foundation, recent releases have introduced several user-facing enhancements:

    • Support for non‑B-tree index types on OrioleDB tables.
    • Support for rewinding recent changes in the database.
    • Support for tablespaces.
    • fillfactor support for OrioleDB tables and indexes.
    • orioledb_tree_stat() SQL function for space utilization statistics.
    • Support for tables with more than 32 columns.

    These additions improve OrioleDB/PostgreSQL compatibility and provide more flexibility for workloads with diverse schema and indexing requirements.

    Performance improvements

    Alongside these user‑facing additions, significant performance improvements have been made:

    Storage and Transactional Efficiency

    • Sparse file support for more efficient storage allocation.
    • Separation of row‑level and page‑level undo logs.
    • Optimizations to avoid undo records for insertions into tables created by the same transaction

    Query and Index Performance

    • Custom scan node for primary key lookups, reducing redundant index searches.
    • Faster B-tree inner page navigati
    [...]

    TIL - Debian comes with a pg_virtualenv wrapper!
    Posted by Kaarel Moppel on 2025-07-14 at 21:00
    Some weeks ago Postgres (well, not directly the official Postgres project) again managed to surprise me during my daily muscle-memory operations, prompting even one relatively lazy human to actually write about it. So while minding some other common Postgres business, I double-tapped “Tab” to complete on “pg_v”, looking for pg_verifybackup...

    Dian Fay
    Posted by Andreas 'ads' Scherbaum on 2025-07-14 at 14:00
    PostgreSQL Person of the Week Interview with Dian Fay: I live in Michigan with my partner Trent, a cat named Bread, and a tarantula named Carmilla. I’m originally from the Florida panhandle, but I moved up here for school lo these many years ago and wound up sticking around – it’s nice having seasons. My mother taught me how to type on her old Selectric when I was six or seven (my handwriting never recovered), and my father taught me BASIC when I was eight by copying code listings out of his Sky & Telescope magazines. In other words, I never stood a chance.

    PgPedia Week, 2025-07-13
    Posted by Ian Barwick on 2025-07-14 at 09:34
    PostgreSQL 19 changes this week new system view pg_dsm_registry_allocations the LSN output format is now uniformly set to %X/%08X this also affects the pg_lsn datatype, with e.g. SELECT '0/0'::pg_lsn returning 0/00000000 (in PosgreSQL 18 and earlier: 0/0 ). the CHECKPOINT command now accepts the following options: FLUSH_UNLOGGED [ boolean ] MODE { FAST | SPREAD } new libpq connection parameter servicefile added PostgreSQL 18 changes this week

    PostgreSQL 18 beta2 is will likely be released on July 17th (Thursday): 18beta2 next week .

    libpq the PQservice() function added in commit 4b99fed7 has been removed btree_gist the two changes resulting in extension version bumps have been consolidated into version 1.8 PostgreSQL 18 articles

    more...

    20 Years of DALIBO: PostgreSQL and the Sense of the Community
    Posted by damien clochard in Dalibo on 2025-07-13 at 12:30

    The cooperative company DALIBO is celebrating its 20th anniversary today, giving me an opportunity to reflect on the reasons behind the success of this collective adventure.

    Happy Birhtday

    Speaking of DALIBO’s success means first speaking of the PostgreSQL community’s success. When we created the company in 2005 with Jean-Paul Argudo, Dimitri Fontaine and Alexandre Baron, PostgreSQL was a marginal, confidential and unattractive project. Two decades later, it has become the dominant database: an obvious choice, a consensus among most developers, administrators and decision-makers…

    So today I could easily tell you the fable of a visionary company, a pioneer that knew before everyone else that Postgres would devour everything in its path… But the truth is that we were lucky to board the right train at the right time :-)

    In 2005, even though I had the intuition that this Postgres train would take us far, it was difficult to imagine that the journey would lead us to the very top of the database market… At that time, Oracle was probably the most powerful IT company in the world, Microsoft SQL Server had its own unwavering user base, MySQL was the rising star among web developers and the NoSQL hype was about to begin…

    On paper, PostgreSQL seemed to be the ugly duckling of the group: no flashy interface for developers, no outstanding benchmarks, no bombastic press releases…

    But in hindsight, the main ingredient for success was already there: an open, decentralized and self-managed community.

    When I participated in creating DALIBO, I clearly remember how warm and stimulating the community’s welcome was: people like Bruce Momjian, Simon Riggs and many others supported, encouraged and inspired us.

    Because what is so unique about the Postgres community is the sense of community that runs through it.

    What I mean by “sense of community” is the ability for individuals to perceive, understand and value what unites them within the same collective. When people manage to grasp together a common objective, s

    [...]

    Contributions for the week of 2025-06-23 (Week 26)
    Posted by Pavlo Golub in postgres-contrib.org on 2025-07-13 at 10:58

    PUG Stuttgart

    PUG Stuttgart happened on June, 26th - hosted by Aleshkova Daria

    Speakers

    Prague PostgreSQL Meetup

    Prague PostgreSQL Meetup on June 23 organized by Gulcin Yildirim

    Speakers

    Swiss PGDay 2025

    Swiss PGDay 2025 took place on June 26th and 27th in Rapperswil (Switzerland)

    Program committee

    Organization committee

    Speakers

    • Bruce Momjian How Open Source and Democracy Drive Postgres
    • Gulcin Yildirim Jelinek Anatomy of Table-Level Locks in PostgreSQL
    • Laurenz Albe Mach das nicht!
    • Patrick Stählin Using logical replication for fun and profit
    • Aarno Aukia Operating PostgreSQL at Scale: Lessons from Hundreds of Instances in Regulated Private Clouds
    • [Bertrand Hartwig-Peillon] pgAssistant
    • Gianni Ciolli The Why and What of WAL
    • Daniel Krefl Hacking pgvector for performance
    • Kaarel Moppel Das 1x1 der Testdatengenerierung mit Postgres
    • Franck Pachot Normalize or De-normalize? Relational SQL Columns or JSON Document Attributes?
    • Andreas Geppert Tabellen vs Objekte: warum nicht einfach beides (in Postgres)?
    • Devrim Gunduz Know the less known about PostgreSQL
    • Dirk Krautschick Benchmarking – Eine unerwartete Reise
    • J
    [...]

    Fixing Slow Row-Level Security Policies
    Posted by Dian Fay on 2025-07-13 at 00:00

    At my day job, we use row-level security extensively. Several different roles interact with Postgres through the same GraphQL API; each role has its own grants and policies on tables; whether a role can see record X in table Y can depend on its access to record A in table B, so these policies aren't merely a function of the contents of the candidate row itself. There's more complexity than that, even, but no need to get into it.

    Two tables, then.

    set jit = off; -- just-in-time compilation mostly serves to muddy the waters here
    
    create table tag (
      id int generated always as identity primary key,
      name text
    );
    
    insert into tag (name)
    select * from unnest(array[
        'alpha', 'beta', 'gamma', 'delta', 'epsilon', 'zeta', 'eta', 'iota', 'kappa', 'lambda', 'mu',
        'nu', 'xi', 'omicron', 'pi', 'rho', 'sigma', 'tau', 'upsilon', 'phi', 'chi', 'psi', 'omega'
    ]);
    
    create table item (
      id int generated always as identity primary key,
      value text,
      tags int[]
    );
    
    insert into item (value, tags)
    select
      md5(random()::text),
      array_sample((select array_agg(id) from tag), trunc(random() * 4)::int + 1)
    from generate_series(1, 1000000);
    
    create index on item using gin (tags);
    
    alter table tag enable row level security;
    alter table item enable row level security;

    We'll set up two roles to compare performance. item_admin will have a simple policy allowing it to view all items, while item_reader's access will be governed by session settings that the user must configure before attempting to query these tables.

    create role item_admin;
    grant select on item to item_admin;
    grant select on tag to item_admin;
    
    create policy item_admin_tag_policy on tag
    for select to item_admin
    using (true);
    
    create policy item_admin_item_policy on item
    for select to item_admin
    using (true);
    
    create role item_reader;
    grant select on item to item_reader;
    grant select on tag to item_reader;
    
    -- `set item_reader.allowed_tags = '{alpha,beta}'` and see items tagged
    -- alpha or beta
    create policy item_reader_tag_policy on tag
    for select to item
    [...]

    Microservices in Postgres
    Posted by Vibhor Kumar on 2025-07-12 at 22:49

    Vibhor Kumar and Marc Linster; last updated July 10 2025


    Great, big monolithic databases that assembled all the company’s data used to be considered a good thing. When I was Technical Director at Digital Equipment (a long time ago), our business goal was to bring ‘it’ all together into one enormous database instance, so that we could get a handle on the different businesses and have a clear picture of the current state of affairs. We were dreaming of one place where we could see which components were used where, what product was more profitable, and what parts of the business could be evaluated and optimized.

    What changed? Why do we now consider monoliths to be dinosaurs that inhibit progress and that should be replaced with a new micro-services architecture?

    This article reviews the pros and cons associated with large, monolithic databases, before diving into modular database (micro-)services. We review their advantages and challenges, describe a real-world problem from our consulting background, and outline design principles. The article ends with a discussion of Postgres building blocks for microservices.

    Why did we ever want the big monolithic database?

    Every business I know has been struggling with uniform definitions, such as a uniform price list with historical prices, or a single source of truth, such as the definite list of customers and their purchases. Trying to move all the data into one ginormous system with referential integrity is very tempting, and when it works, it can be very rewarding.

    There are also other operational benefits, such as a single maintenance window, a single set of operating instructions, a single vendor, and a single change management process.
    However, this centralized approach begins to show its limitations as the database grows to an extreme scale, leading to performance bottlenecks and inflexibility.

    Challenges when dealing with monoliths

    The challenges of monolithic systems are significant, and many architects believe that the

    [...]

    Worklog July 3-11, 2025
    Posted by Mankirat Singh on 2025-07-12 at 00:00
    Progress on ABI Compliance Reporting with BuildFarm

    Efficiency of a sparse hash table
    Posted by Ashutosh Bapat on 2025-07-11 at 15:01

    When implementing an optimization for derived clause lookup myself, Amit Langote and David Rowley argued about the initial size of hash table (which would hold the clauses). See some discussions around this email on pgsql-hackers.

    The hash_create() API in PostgreSQL takes initial size as an argument. It allocates memory for those many hash entries upfront. If more entries are added, it will expand that memory later. The point of argument was what should be the initial size of the hash table, introduced by that patch, containing the derived clauses. During the discussion, David hypothesised that the size of the hash table affects the efficiency of the hash table operations depending upon whether the hash table fits cache line. While I thought it's reasonable to assume so, the practical impact wouldn't be noticeable. I thought that beyond saving a few bytes choosing the right hash table size wasn't going to have any noticeable effects. If an derived clause lookup or insert became a bit slower, nobody would even notice it. It was practically easy to address David's concern by using the number of derived clauses at the time of creating the hash table to decide initial size of the hash table. The patch was committed.

    Within a few months, I faced the same problem again when working on resizing shared buffers without server restart. The buffer manager maintains a buffer look table in the form of a hash table to map a page to buffer. When the number of configured buffers changes upon a server restart the size of buffer lookup table also changes. Doing that in a running server would be significant work. To avoid that, we could create a buffer lookup table large enough to accommodate future buffer size needs. Even if the buffer pool shrinks or expands, the size of the buffer lookup table would not change. As long as the expansion is within the buffer lookup table size limit, it could be done without a restart. Buffer lookup table isn't as large as the buffer pool itself, thus wasting a bit of memory can be consi

    [...]

    Replication Types and Modes in PostgreSQL
    Posted by semab tariq in Stormatics on 2025-07-10 at 13:09

    Data is a key part of any mission-critical application. Losing it can lead to serious issues, such as financial loss or harm to a business’s reputation. A common way to protect against data loss is by taking regular backups, either manually or automatically. However, as data grows, backups can become large and take longer to complete. If these backups are not managed properly or tested often, a sudden system crash could result in permanent data loss.

    To address this, many organizations use real-time data replication as a more reliable solution. In this setup, data from the primary server is continuously copied to one or more standby servers. If the primary server fails, there’s no need to restore from a backup the standby server can be quickly promoted to take over as the new primary. This allows the application to resume with minimal downtime, reducing the Recovery Time Objective (RTO), and can bring the Recovery Point Objective (RPO) close to zero or even zero.

    PostgreSQL also supports replication to keep standby servers in sync with the primary server using Write-Ahead Log (WAL) files. Every change made to the database is first recorded in these WAL files on the primary server. These logs are then continuously streamed to the standby server, which applies them to stay up to date. This method ensures that all standby servers stay in sync with the primary and are ready to be promoted in case the primary server fails.

    In this blog, we will explore the different types and modes of replication available in PostgreSQL to help you understand which option best fits your business needs.

    Types of Replication

    PostgreSQL offers different types of replication, with each method designed to serve specific use cases.

    • Physical Replication
    • Logical Replication
    • Cascading Replication

    Physical Replication

    Physical replication is the foundation for setting up high availability in active-passive clusters. In this replication

    [...]

    Active-active replication - the hidden costs and complexities
    Posted by Jan Wieremjewicz in Percona on 2025-07-10 at 00:00

    In Part 1 of this series, we discussed what active-active databases are and identified some “good” reasons for considering them, primarily centered around extreme high availability and critical write availability during regional outages. Now, let’s turn our attention to the less compelling justifications and the substantial challenges that come with implementing such a setup.

    What are “bad” reasons?

    1. Scaling write throughput
 Trying to scale your write capacity by deploying active-active across regions may sound like a clean horizontal solution, but it is rarely that simple. Write coordination, conflict resolution, and replication overhead introduce latency that defeats the purpose.
If you are thinking about low latency writes between regions like Australia and the US, keep in mind that you will still be paying the round-trip cost, typically 150-200ms+, to maintain consistency. Physics don’t do any favors.
Even if you have multiple primaries, unless you accept weaker consistency or potential conflicts, your writes will not scale linearly. In many real-world cases, throughput actually suffers compared to a well tuned primary replica setup.
If your real goal is better throughput, you are usually better served by:
      • Regional read replicas
      • Sharding
      • Task queuing and eventual delegation
    2. Performance
 Performance is often used as a vague justification. But active-active is not a panaceum for general slowness. If the issue is database bottlenecks, reasons may be as basic as not enough work spent on data structuring, indexing, query tuning or scaling reads before jumping into multi primary deployments.
Way too often we find that performance problems come not from scale limits, but from poor design and neglected maintenance. We are in 2025 and lessons like this classic one on fast inserts are still painfully relevant.
If what you are really facing is application level latency, that is a different challenge. And even then, active-active with strong guara
    [...]

    So why don't we pick the optimal query plan?
    Posted by Tomas Vondra on 2025-07-08 at 10:00

    Last week I posted about how we often don’t pick the optimal plan. I got asked about difficulties when trying to reproduce my results, so I’ll address that first (I forgot to mention a couple details). I also got questions about how to best spot this issue, and ways to mitigate this. I’ll discuss that too, although I don’t have any great solutions, but I’ll briefly discuss a couple possible planner/executor improvements that might allow handling this better.

    Josef Machytka
    Posted by Andreas 'ads' Scherbaum on 2025-07-07 at 14:00
    PostgreSQL Person of the Week Interview with Josef Machytka: I was born in Czechia and lived most of my life there. However, for the last 12 years, my family and I have been calling Berlin, Germany, our home. It is an open-minded place that gives people a lot of freedom, we like it here very much.

    PgPedia Week, 2025-07-06
    Posted by Ian Barwick on 2025-07-07 at 11:38

    PostgreSQL 19 development is now officially under way, so from now on any new features will be committed to that version. Any significant PostgreSQL 18 changes (e.g. reversions or substantial changes to already committed features) will be noted here separately (there were none this week).

    PostgreSQL 19 changes this week

    The first round of new PostgreSQL 19 features is here:

    new object identifier type regdatabase , making it easier look up a database's OID COPY FROM now supports multi-line headers cross-type operator support added to contrib module btree_gin : non-array variants of function width_bucket() now permit operand input to be NaN

    more...

    Analyzing PostgreSQL Performance Using Flame Graphs
    Posted by Jan Kristof Nidzwetzki on 2025-07-05 at 00:00

    A flame graph is a graphical representation that helps to quickly understand where a program spends most of its processing time. These graphs are based on sampled information collected by a profiler while the observed software is running. At regular intervals, the profiler captures and stores the current call stack. A flame graph is then generated from this data to provide a visual representation of the functions in which the software spends most of its processing time. This is useful for understanding the characteristics of a program and for improving its performance.

    This blog post explores the fundamentals of flame graphs and offers a few practical tips on utilizing them to identify and debug performance bottlenecks in PostgreSQL.

    The content presented in this blog post is based on material found in other articles or blog posts, as well as in Brendan Gregg’s excellent book on system performance. Over the years, I have collected a number of commands in my lab notebook that I typically use when diagnosing PostgreSQL-related performance problems. I have shared these commands in several emails over the years, so I decided to write a whole blog post on this topic.

    Flame Graphs

    Flame graphs are based on data captured by a profiler. They aggregate call stacks to make it easier to see where a program spends most of its processing time. Without aggregation, it is difficult to see the big picture in the thousands (or more) of call stacks that a profiler collects.

    When a flame graph is created, these call stacks are collapsed, and the time spent in similar call stacks is summed up. Based on this data, the flame graph is created. The idea behind this is as follows: the more time a program spends in a particular code path, the more often those call stacks will appear in the samples. Since the resulting graph consists of call stacks of different heights, and the stacks are usually colored in red to yellow tones, it looks like a flame.

    Brendan Gregg states in ‘The Flame Graph’, ACM Queue, Vol 14, N

    [...]

    PGDay UK 2025 - Schedule Published
    Posted by Chris Ellis in PGDay UK on 2025-07-04 at 13:27

    We are excited to announce the schedule for PGDay UK 2025 has been published. We've got an exciting line up for talks over a range of topics. There will be something for everyone attending.

    Take a look at what we have going on: https://pgday.uk/events/pgdayuk2025/schedule/

    We'd like to extend our gratitude to the whole CFP team, who did an amazing job selecting the talks to make up the schedule.

    Thank you to all speakers whom submitted talks, it's always a shame that we can't accept all, and as ever it's a tough choice to choose the talks for the schedule. Be it your 100th time or 1st time submitting a talk, we hope you submit again in the future and at other PostgreSQL Europe events.

    PGDay UK 2025 is taking place in London on September 9th, so don't forget to register for PGDay UK 2025, before it's too late!

    https://2025.pgday.uk/

    PGConf.be 2025
    Posted by Wim Bertels on 2025-07-04 at 12:07

    A round up of the fifth PGConf.be

    The shared presentations are online, as are a couple of recordings and turtle-loading have-a-cup-of-tea locally stored photos.

    Using the well known and broadly spread technique of inductive reasoning we came to the conclusion that this fourth PGConf.be conference was a success, as well as the art work. No animals or elephants we’re hurt during this event.

    The statistics are

    • 60 attendants

      • depending on the session, an extra 60 to 150 students attended as well

    • 10 speakers

    • 2 sponsors

    This conference wouldn’t have been possible without the help of volunteers.
    To conclude a big thank you to all the speakers, sponsors and attendants.
    Without them a conference is just a like tee party.

    pgroll 0.14 - New commands and more control over version schemas
    Posted by Andrew Farries in Xata on 2025-07-04 at 11:45
    pgroll 0.14 is released with several new subcommands and better control over how version schema are created.

    Top posters

    Number of posts in the past two months

    Top teams

    Number of posts in the past two months

    Feeds

    Planet

    • Policy for being listed on Planet PostgreSQL.
    • Add your blog to Planet PostgreSQL.
    • List of all subscribed blogs.
    • Manage your registration.

    Contact

    Get in touch with the Planet PostgreSQL administrators at planet at postgresql.org.