https://postgr.es/p/78O

Most applications start with a single PostgreSQL database, but over time, the need to scale out, distribute the load, or integrate naturally arises. PostgreSQL's logical replication is one of the features that meets these demands by streaming row-level changes from one PostgreSQL instance to another, all using a publish-subscribe model. Logical replication is more than an advanced feature; it provides a flexible framework you can build on to further distribute and integrate PostgreSQL within your architecture.

In this article, we will start with the foundation, explore the core ideas behind logical replication, and learn how to use it.

Physical vs. Logical Replication

Before we can dive deeper, let's understand the role of replication in PostgreSQL and how it's built on top of the Write-Ahead Log (WAL).

The WAL is a sequential, append-only log that records every change made to the cluster data. For durability purposes, all modifications are first written to the WAL and only then permanently written to disk. This allows PostgreSQL to recover from crashes by replaying logged changes.

Versioned changes, necessitated by concurrent transactions, are managed through Multi-Version Concurrency Control (MVCC). Instead of overwriting data directly, MVCC creates multiple versions of rows, allowing each transaction to see a consistent snapshot of the database. It is the WAL that captures these versioned changes along with the transactional metadata to ensure data consistency at any given point in time.

Physical replication is built directly on the Write-Ahead Log. It enables streaming of the binary WAL data from the primary server to one or more standby servers (replicas), effectively creating a byte-for-byte copy of the entire cluster. This requirement makes the replicas read-only, making them ideal candidates for failover or scaling purposes.

Compared to this, Logical replication, while also being built on top of the WAL data, takes a fundamentally different approach. Instead of streaming raw change data, logical replication decodes the WAL into logical, row-level changes – such as INSERT, UPDATE, and DELETE – and only then sends them to the subscribers using a Publish-Subscribe model. Compared to physical replication, this allows selective replication, while allowing writable subscribers which are not strictly tied to a single publisher. This might increase the flexibility of available setups, however logical replication does not replicate DDL changes.

Physical replication is your go-to for high availability and disaster recovery, where you want a fast, exact copy of the entire database cluster that can take over in case of failure. It’s simple to set up and very efficient but limited in flexibility.

Logical replication shines when you need fine-grained control over what data is replicated, require writable replicas, or want to integrate PostgreSQL with other systems or versions. It’s ideal for zero-downtime upgrades, multi-region deployments, and building scalable, modular architectures.

Setting up Logical Replication

Enough of boring theory for now. To get started, you will need two instances of PostgreSQL – it's up to you whether you provision two virtual machines or two clusters running on the same computer. We will call one publisher and the other subscriber.

Preparing the Publisher

First, you need to prepare the publisher to emit the logical changes. You will need to modify postgresql.conf (or add particular conf.d configuration) with the following parameters:

wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

Your publisher also needs to be reachable by the subscriber, in most cases via a TCP socket.

listen_addresses = '*'

Here, the configuration is:

• wal_level set to logical is a crucial piece of the configuration. It tells PostgreSQL how much information to write to the WAL, in this case, to support logical decoding.
• max_replication_slots defines the maximum number of replication slots that can be created on the server. Each logical replication subscription needs its own slot.
• max_wal_senders should be set high enough to accommodate all expected concurrent replication connections from subscribers. Each active replication subscription consumes a wal_sender slot.

should match the maximum number of connections from the publisher (primary) to subscribers or replication clients. The number should be higher than or equal to the number of replication slots to avoid replication problems.

You will also need to configure client authentication in pg_hba.conf to allow the subscriber to connect for replication (for simplification, we allow all users):

# TYPE      DATABASE        USER            ADDRESS                 METHOD
host        replication     all             subscriber_ip/32        scram-sha-256

While for the purposes of the article we will assume the use of a superuser, making it convenient:

CREATE USER my_user_name SUPERUSER PASSWORD 'my_secure_password';

It is recommended to use a dedicated replication user for real-life deployments:

CREATE USER replication_user WITH REPLICATION ENCRYPTED PASSWORD 'my_secure_password';

Once configured, restart PostgreSQL for the configuration changes to take effect. After that, connect to the server and prepare the environment.

-- example for psql

CREATE DATABASE logical_demo_publisher;
\c logical_demo_publisher;

Create a sample table and seed initial data:

CREATE TABLE products (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE
);

-- seed some data (10 records)
INSERT INTO products (
    name,
    category,
    price,
    stock_quantity,
    description,
    is_active
)
SELECT
    'Product Batch ' || s.id AS name,
    CASE (s.id % 5)
        WHEN 0 THEN 'Electronics'
        WHEN 1 THEN 'Books'
        WHEN 2 THEN 'Home Goods'
        WHEN 3 THEN 'Apparel'
        ELSE 'Miscellaneous'
    END AS category,
    ROUND((RANDOM() * 500 + 10)::numeric, 2) AS price,
    FLOOR(RANDOM() * 200)::int AS stock_quantity,
    'Auto-generated description for product ID ' || s.id || '. Lorem ipsum dolor sit amet, consectetur adipiscing elit.' AS description,
    (s.id % 10 <> 0) AS is_active
FROM generate_series(1, 10) AS s(id);

The final step for the publisher is to create a publication to define what data we want to publish.

CREATE PUBLICATION my_publication FOR TABLE products;

Preparing the Subscriber

Next, we will use our subscriber instance to receive the logical changes. There's no need to make any configuration changes just for subscribing, as it's not emitting changes itself (please note the "just").

And create the target database and schema. The schema creation is an important part as:

-- example for psql

CREATE DATABASE my_subscriber_db;
\c my_subscriber_db;

CREATE TABLE products (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE
);

Now we have the foundation for testing logical replication. The simplest way is to create a subscription using connection details and the name of the publication to subscribe to.

CREATE SUBSCRIPTION my_subscription
    CONNECTION 'host=publisher_ip_address port=5432 user=your_replication_user password=my_secure_password dbname=logical_demo_publisher'
    PUBLICATION my_publication;

• my_subscription should be a descriptive name for your subscription.
• CONNECTION defines how to connect to the publisher (which is a regular connection string and could also be a service).
• PUBLICATION specifies the name of the publication you created earlier on the publisher.

If your connection was correct, the subscription will by default start with the initial data sync and listen for incoming changes. If you have used the queries above, you can validate that the data is now on the subscriber.

# SELECT count(1) FROM products;
 count
---------
    10
(1 row)

The number of records should match the number of records created using generate_series above (10 in our example). You can go ahead and insert a single row again on your publisher instance and validate the data being replicated to the subscriber.

Congratulations! You have set up your first logical replication in PostgreSQL!

Core Concepts of Logical Replication

That was easy, right? And that's the goal. Now that you've seen logical replication in action, let's delve deeper into the core concepts that made this work.

Publication

As the name implies, a publication is where it all starts. It's essentially a catalogue of data you offer from the publisher. You can publish a number of objects:

--- specific tables
CREATE PUBLICATION my_publication FOR TABLE products, orders;

--- everything in your database (make sure you really want to do this)
CREATE PUBLICATION all_data FOR ALL TABLES;

--- replicate specific columns only (PostgreSQL 15 and higher)
CREATE PUBLICATION generic_data FOR TABLE products (id, name, price);

--- filter published data (PostgreSQL 15 and higher)
CREATE PUBLICATION active_products FOR TABLE products WHERE (is_active = true);

--- filter different operations
CREATE PUBLICATION my_publication FOR TABLE products, orders
WITH (publish = 'insert');

--- or you can mix & match it to get exactly what you need
CREATE PUBLICATION eu_customers FOR
    TABLE customers (id, email, country, created_at)
        WHERE (country IN ('DE', 'FR', 'IT')),
    TABLE orders (id, customer_id, total_amount)
        WHERE (total_amount > 100)
WITH (publish = 'insert, update');

As you can see, logical replication really gives you quite a lot of options and is far from the rigid, byte-for-byte copying of physical replication. This is exactly the characteristic that allows you to build complex topologies.

Once you set up your publication(s), you can review it:

--- in psql
\dRp
\dRp+ my_publication

--- using pg_catalog
SELECT * FROM pg_publication_tables WHERE pubname = 'my_publication';

From these options, it's easy to think of a publication as a customisable data feed.

Subscription

The other side of logical replication is a subscription. It defines what and how to consume events from a publisher. You subscribe to a publication using connection details and a number of options.

--- basic subscription with full connection detail
CREATE SUBSCRIPTION my_subscription
    CONNECTION 'host=publisher_host port=5432 user=repl_user password=secret dbname=source_db'
    PUBLICATION my_publication;

-- subscription using service definition
CREATE SUBSCRIPTION realtime_only
    CONNECTION 'service=my_source_db'
    PUBLICATION my_publication;

The default behaviour of the subscription is to copy existing data, start immediately, and continue with streaming data changes. Once you start experimenting with logical replication, you can control that behaviour.

--- subscribe without initial copy of the data
CREATE SUBSCRIPTION streaming_only
    CONNECTION 'service=my_source_db'
    PUBLICATION my_publication
    WITH (copy_data = false);

--- or defer the start for later
CREATE SUBSCRIPTION manual_start
    CONNECTION 'service=my_source_db'
    PUBLICATION my_publication
    WITH (enabled = false);

You can monitor your subscriptions:

--- in psql
\dRs
\dRs+ my_subscription

--- or their status using pg_catalog
SELECT subname, received_lsn, latest_end_lsn, latest_end_time
FROM pg_stat_subscription;

Replication Slot

Publications and subscriptions establish the data flow, but there's a critical piece missing: how does the publisher keep track of multiple subscribers reading the WAL at different speeds? Replication slots are the answer. They act as a persistent booking in the WAL stream that tracks exactly where each subscriber is (or was last time), ensuring no changes are lost even if the connection stops.

Let's have a look at what a replication slot can tell us about itself.

# SELECT * FROM pg_replication_slots;
-[ RECORD 1 ]-------+---------------
slot_name           | my_subscription
plugin              | pgoutput
slot_type           | logical
datoid              | 16390
database            | my_db_source
temporary           | f
active              | t
active_pid          | 3162
xmin                |
catalog_xmin        | 777
restart_lsn         | 0/1DEFBF0
confirmed_flush_lsn | 0/1DEFC28
wal_status          | reserved
safe_wal_size       |
two_phase           | f
inactive_since      |
conflicting         | f
invalidation_reason |
failover            | f
synced              | f

The most interesting attributes are:

• slot_name
• plugin used for logical replication
• slot_type confirming it's for logical replication
• active indicates whether there's a subscriber reading from this slot

And most important of those being:

• restart_lsn identifying the LSN where this slot "holds" WAL files from being released
• confirmed_flush_lsn being the last LSN position the subscriber confirmed it has successfully processed (i.e., applied).

While we won't go into details about WAL, it's enough to say LSN (Log Sequence Number) is a unique address or position in the WAL that identifies the position in the stream of database changes.

In most cases, you don't have to create replication slots manually – subscriptions create and manage them. The persistent nature of the slots guarantees they survive the restart of both publisher and subscriber.

Please note, if the subscription is not actively consuming the changes, the publisher PostgreSQL won't release WAL files that contain changes a slot has not consumed. This prevents data loss, but can easily fill up disk if any of the subscribers fall too far behind.

You can monitor the WAL retention per each replication slot.

# SELECT
    slot_name,
    active,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_retained
FROM pg_replication_slots;

-[ RECORD 1 ]+--------------------
slot_name    | my_subscription
active       | t
wal_retained | 265 MB

Replication Identity

As we have already covered, logical replication works with row-level changes, such as INSERT, UPDATE, and DELETE. And not all operations are equal. While INSERT is relatively straightforward (a new row is sent to the subscriber), for both UPDATE and DELETE operations, PostgreSQL needs to be able to uniquely identify the target row to modify on the subscriber.

This is managed by the REPLICA IDENTITY property of each published table. By default, if a table has a primary key (it has, right?), or you can specify it USING INDEX for a unique index, or as an alternative, specify FULL (should be generally avoided) to all old column's row values to find the target row to update. Please note, PostgreSQL might default to REPLICA IDENTITY FULL if no primary key or suitable key index exists.

Whenever possible, please always:

• Choose REPLICA IDENTITY DEFAULT (for primary key), giving you the most efficient choice. If you don't have a primary key, please consider using it (a surrogate index is always an option).
• REPLICA IDENTITY USING INDEX index_name if there would be a better (or smaller) unique index matching the business logic or architecture of your database model.

There are special cases of what not to do when it comes to replication identity, but we will touch on those cases in the advanced section of this guide.

Schema Changes

Although it was already mentioned, it's vital to reiterate that logical replication, as it streams only row-level changes rather than full WAL segments, does not automatically replicate Data Definition Language (DDL) changes.

Any schema changes made on the publisher must be manually applied to all subscribers before data reflecting the change is replicated.

We will cover schema specifically in later parts of this guide, but to sum it up, this is the recommended order of operations when it comes to schema changes:

1. (Optional) but recommended for breaking changes, pause replication where applicable.
2. Apply and verify DDL changes to the subscribers.
3. Apply and verify DDL changes on the publisher.
4. Resume the replication if applicable.

While this might seem like a major drawback compared to physical replication, it's the characteristic that gives us the most flexibility.

Other Considerations

As logical replication reliably handles row-level changes, it's crucial to understand other specific limitations and behaviours. Specifically:

• It's important to understand that sequences are not replicated. While they are used to generate the value on the publisher, and their value advances there, the corresponding sequence (if it exists) on the subscriber won't be updated.
• Other database objects won't be replicated. While it might be understandable for views, stored procedures, triggers, and rules, you also can't rely on it for materialized views.
• Special consideration (similar to schema changes) must be paid to user-defined data types. If a replicated table uses a user-defined type (e.g., a column using an ENUM), the type must already be available on the subscriber(s) and have exactly the same name and structure. As ENUMs are represented as ordered sets, even the order of the values matters!

Living with Logical Replication

In our previous example, we set up the publisher and subscriber, relied on the initial copy of the data, and let things run. Both of them will survive a restart, and thanks to the replication slot, they will keep going as soon as they come online.

But what if you need to perform maintenance or do regular things like a schema update on the subscriber or the publisher? Sometimes you might need to temporarily stop the replication. PostgreSQL makes this straightforward with subscription management, allowing you to stop consuming the changes.

ALTER SUBSCRIPTION my_subscription DISABLE;

As we hinted earlier, when disabled, the subscription stops consuming changes from the publisher, while keeping the replication slot. This means:

• No new changes are applied on the subscriber.
• WAL files will start to accumulate on the publisher (since the replication slot holds its position).

While disabled, you can't really check the status from the subscriber, as only the replication slot has the data. You can use the query we mentioned above on the publisher to check the status.

SELECT
    slot_name,
    active,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_retained
FROM pg_replication_slots;

When ready, you can resume the subscription.

ALTER SUBSCRIPTION my_subscription ENABLE;

Coordinating Schema Changes

As we mentioned earlier, since logical replication does not automatically replicate DDL changes, you need to coordinate schema updates manually. Here's a basic overview of how to perform such a change, step by step.

First, disable the replication on the subscriber and apply your schema changes.

--- disable replication
ALTER SUBSCRIPTION my_subscription DISABLE;

--- apply changes on the subscriber
ALTER TABLE products ADD COLUMN category_id INTEGER;
CREATE INDEX products_by_category ON products(category_id);

Only then can you apply the changes to the publisher.

ALTER TABLE products ADD COLUMN category_id INTEGER;
CREATE INDEX products_by_category ON products(category_id);

And resume the replication.

ALTER SUBSCRIPTION my_subscription ENABLE;

If you applied the schema change to the publisher first, any new data using the new column would fail to replicate to the subscriber that doesn't have the column yet.

Handling Incompatible Changes

PostgreSQL's default conflict resolution is very simple. When a conflict occurs, logical replication stops, and the subscription enters an error state. For example, if you consider a product row already present on the subscriber, you will see something like this in the log files:

ERROR: duplicate key value violates unique constraint "products_pkey"
DETAIL: Key (id)=(452) already exists.
CONTEXT:  processing remote data for replication origin "pg_16444" during message type "INSERT" for replication target relation "public.products" in transaction 783, finished at 0/1E020E8
LOG:  background worker "logical replication apply worker" (PID 457) exited with exit code 1

As you can see, logical replication stopped with an error code and won't continue until you manually resolve the conflict. To do so, you need to identify the conflicting data and decide what to do with it.

-- option 1: remove the conflicting data
DELETE FROM products WHERE id = 452;

-- option 2: update the data to match the expected state
UPDATE products SET name = 'New Product', price = 29.99 WHERE id = 452;

Only after the conflict is resolved can the logical replication continue. This has been only a very simple example of a conflict that might arise during logical replication. Other examples might involve:

• Data problems, constraint violations, or type mismatch
• Schema conflicts (missing or renamed table/column)
• Permissions issues or row-level security

In all those cases, you still need to go and fix the problem before logical replication can resume.

Wrap Up

Logical replication in PostgreSQL opens up powerful possibilities beyond traditional physical replication. We have covered the foundations that can get you started with setting up your own logical replication environment, including understanding the differences between physical and logical replication, configuring publishers and subscribers, and managing core components like publications, subscriptions, and replication slots.

This article is part of the upcoming guide Mastering Logical Replication in PostgreSQL. If you are interested in the topic, please consider subscribing to get the latest articles as they are published.

https://postgr.es/p/78O

https://postgr.es/p/78M

Of note:

more...

https://postgr.es/p/78M

https://postgr.es/p/78K

Regular expressions and PostgreSQL have been a great team for many many years. The same is true for PostgreSQL arrays, which have been around for a long time as well. However, what people rarely do is combine those two technologies into something more powerful that can be used for various purposes.

Using ANY and ALL

One of the most widely adopted ways of dealing with arrays is the idea of ANY and ALL, which has been supported by PostgreSQL since God knows when. Those two keywords allow us to figure out if a certain value and an array are a match.

Here are some examples:

```sql
test=# SELECT array_agg(x) FROM generate_series(1, 5) AS x;
  array_agg  
-------------
 {1,2,3,4,5}
(1 row)

test=#  SELECT 3 = ANY(array_agg(x)) 
	FROM   generate_series(1, 5) AS x;
 ?column? 
----------
 t
(1 row)

test=#  SELECT 3 = ALL(array_agg(x)) 
	FROM   generate_series(1, 5) AS x;
 ?column? 
----------
 f
(1 row)
```

The first statement simply generates an array of numbers, which can be used in my example to demonstrate how ANY and ALL work. The idea is simple: ANY will check if one of the values in the array matches - ALL will check if all of the values are a match. So far this is quite common.

However, what happens if we try to apply this concept to regular expressions?

PostgreSQL and regular expressions

Many readers might be surprised to learn that combining those two techniques is indeed possible and actually fairly straight forward. Note that in the example above ANY and ALL were essentially used in combination with the = operator. However, we can also apply the ~ operator, which is the PostgreSQL way of handling regular expressions:

```sql
test=# SELECT 'my fancy string' ~ '.*ancy.*ri.+$';
 ?column? 
----------
 t
(1 row)
```

What it essentially does is matching the regular expression on the right hand side of the operator with the string on the left. So far so good, but what happens if we expand on this a bit?

Here is what we can do:

```sql
test=# SELECT   array_agg(exp)
       FROM (
	 SELECT  '.*SELECT.*' AS exp
	 UNION ALL
	 SELECT  '.*HAVING.*'
	 UNION ALL
	 SELECT  '.*GROUP\s+BY.*'
	) AS x;
		array_agg                 
-------------------------------------------
 {.*SELECT.*,.*HAVING.*,".*GROUP\\s+BY.*"}
(1 row)
```

What we have just created is an array of regular expressions that we can easily apply:

```sql
test=# SELECT 'SELECT name, count(*) 
	 FROM   tab
	 GROUP BY 1
	 HAVING count(*) > 2' ~ALL(array_agg(exp))
FROM (
	SELECT  '.*SELECT.*' AS exp
	UNION ALL
	SELECT  '.*HAVING.*'
	UNION ALL
	SELECT  '.*GROUP BY.*'
     ) AS x;
 ?column? 
----------
 t
(1 row)
```

Voilà, we can see that all expressions have matched successfully. All we had to do was utilize ~ALL instead of =ALL to do the trick. While this might be obvious to some, it does not seem to be common knowledge out there.

Finally ...

If you want to learn more about pattern matching and fuzzy search, consider reading my blog post dealing with this topic and hopefully learn more about some of those techniques.

The post Using regular expressions and arrays in PostgreSQL appeared first on CYBERTEC PostgreSQL | Services & Support.

https://postgr.es/p/78K

https://postgr.es/p/78I

https://postgr.es/p/78I

https://postgr.es/p/78v

I recently had a customer that wanted to leverage read replicas to ensure that their read queries were not going to impeded with work being done on the primary instance and also required an SLA of at worst a few seconds. Ultimately they weren’t meeting the SLA and my colleagues and I were asked to look at what was going on.

The first thing we came to understand is that the pattern of work on the primary is a somewhat frequent large DELETE statement followed by a data refresh accomplished by a COPY from STDIN command against a partitioned table with 16 hash partitions.

The problem being observed was that periodically the SELECTs occurring on the read replica would time out and not meet the SLA. Upon investigation, we found that the “startup” process on the read replica would periodically request an “exclusive lock” on some random partition. This exclusive lock would block the SELECT (which is partition unaware) and then cause the timeout. But what is causing the timeout?

After spending some time investigating, the team was able to correlate the exclusive lock with a routine “autovacuum” occurring on the primary. But why was it locking? After inspection of the WAL, it turns out that it the issue was due to a step in the vacuum process whereby it tries to return free pages at the end of the table back to the OS, truncation of the High Water Mark (HWM). Essentially the lock is requested on the primary and then transmitted to the replica via the WAL so that the tables can be kept consistent.

To confirm that it was in fact the step in VACUUM that truncates the HWM, we decided to alter each partition of the table to allow VACUUM to skip that step:

ALTER TABLE [table name / partition name] SET (vacuum_truncate = false);

After letting this run for 24 hours, we in fact saw no further blocking locks causing SLA misses on the replicas. Should we worry about shrinking the High Water Mark (HWM)? Well as with everything in IT, it depends. Other DBMS engines like Oracle do not shrink the High Water Mark (HWM), typically maintenance operations such as DBMS_REDEF or ALTER TABLE … SHRINK SPACE / SHRINK SPACE COMPACT deal with that. So now that we are talking about PostgreSQL do we need to worry about it? This is where the pg_freespacemap extension can help. We can use this extension and a script to check to see if in fact the High Water Mark (HWM) is growing or staying put. If it is growing, we can just execute a regular VACUUM with an additional option called TRUNCATE to handle it:

VACUUM (verbose, truncate) [schema].[table name];

When you do this, you will see one additional message in the VACUUM output signifying that the VACUUM truncated the High Water Mark (HWM):

INFO:  table "large_table": truncated 302534 to 302233 pages

As I stated earlier, we can use pg_freespacemap to see if we actually need to worry about the High Water Mark (HWM) growing. I could have taken a lot of time to write a script to figure it out, but instead, I enlisted Google Gemini to see what it would come up with. After a few iterations, the output was nearly perfect!

CREATE EXTENSION pg_freespacemap;

CREATE OR REPLACE FUNCTION show_empty_pages(p_table_name TEXT)
RETURNS VOID AS $$
DECLARE
    -- Core processing variables
    table_oid_regclass  REGCLASS;
    block_size          BIGINT;
    fsm_granularity     BIGINT;
    max_fsm_free_space  BIGINT;
    total_pages         BIGINT;
    high_water_mark     BIGINT := 0;

    -- Variables for the final summary
    first_empty_block   BIGINT;
    free_pages_at_end   BIGINT;
    free_space_at_end   TEXT;
BEGIN
    -- Setup
    table_oid_regclass := p_table_name::regclass;
    block_size  := current_setting('block_size')::bigint;
    SELECT relpages INTO total_pages FROM pg_class WHERE oid = table_oid_regclass;
    fsm_granularity    := block_size / 256;
    max_fsm_free_space := floor((block_size - 24) / fsm_granularity) * fsm_granularity;

    --------------------------------------------------------------------------------
    -- PASS 1: FIND THE HIGH-WATER MARK (last page with data)
    --------------------------------------------------------------------------------
    FOR i IN REVERSE (total_pages - 1)..0 LOOP
        IF pg_freespace(table_oid_regclass, i) < max_fsm_free_space THEN
            high_water_mark := i;
            EXIT;
        END IF;
    END LOOP;

    --------------------------------------------------------------------------------
    -- FINAL STEP: CALCULATE AND RAISE THE SUMMARY NOTICE
    --------------------------------------------------------------------------------
    first_empty_block := high_water_mark + 1;
    free_pages_at_end := total_pages - first_empty_block;
    IF free_pages_at_end < 0 THEN
        free_pages_at_end := 0;
    END IF;
    free_space_at_end := pg_size_pretty(free_pages_at_end * block_size);

    RAISE NOTICE '-------------------------------------------------------------';
    RAISE NOTICE 'Summary for table: %', p_table_name;
    RAISE NOTICE '-------------------------------------------------------------';
    RAISE NOTICE 'The High Water Mark (HWM) is at page: %', total_pages;
    IF total_pages <> first_empty_block THEN
    	RAISE NOTICE 'First potentially empty page is at: %', first_empty_block;
    	RAISE NOTICE 'Total Pages in Table: %', total_pages;
    	RAISE NOTICE 'Number of potentially truncatable pages at the end: %', free_pages_at_end;
    	RAISE NOTICE 'Amount of free space at the end of the table: %', free_space_at_end;
    ELSE
    	RAISE NOTICE 'There are no empty pages to truncate';
    END IF;
    RAISE NOTICE '-------------------------------------------------------------';
END;
$$ LANGUAGE plpgsql;

This handy script could be periodically executed to check the High Water Mark (HWM) and will produce the following output:

(postgres@10.3.1.17:5432) [postgres] > SELECT * FROM show_empty_pages('public.large_table');
NOTICE:  -------------------------------------------------------------
NOTICE:  Summary for table: public.large_table
NOTICE:  -------------------------------------------------------------
NOTICE:  The High Water Mark (HWM) is at page: 302534
NOTICE:  First potentially empty page is at: 302233
NOTICE:  Total Pages in Table: 302534
NOTICE:  Number of potentially truncatable pages at the end: 301
NOTICE:  Amount of free space at the end of the table: 2408 kB
NOTICE:  -------------------------------------------------------------

If there is no freespace after the last full block the output will look like this:

NOTICE:  -------------------------------------------------------------
NOTICE:  Summary for table: public.large_table
NOTICE:  -------------------------------------------------------------
NOTICE:  The High Water Mark (HWM) is at page: 302233
NOTICE:  There are no empty pages to truncate
NOTICE:  -------------------------------------------------------------

So while there is no right answer on how to deal with this, ensure you know the implications of each step in the process. In this case, we have decided to turn the “vacuum_truncation” option to false, but maybe another option might be to tune vacuum in another way such as either making it more or less frequent. Always evaluate your own situation, but in any case it’s always good to know what happens in your database when certain commands are executed.

Enjoy!

https://postgr.es/p/78v

https://postgr.es/p/78s

Logical replication is a versatile feature offered in PostgreSQL. I have discussed the the theoretical background of this feature in detail in my POSETTE talk. At the end of the talk, I emphasize the need for monitoring logical replication setup. If you are using logical replication and have setup monitoring you will be familiar with pg_stat_replication_slots. In some cases this view shows high amount of spill_txns, spill_count and spill_bytes, which indicates that the WAL sender corresponding to that replication slot is using high amount of disk space. This increases load on the IO subsystem affecting the performance. It also means that there is less disk available for user data and regular transactions to operate. This is an indication that logical_decoding_work_mem has been configured too low. That's the subject of this blog: how to decide the right configuration value for logical_decoding_work_mem. Let's first discuss the purpose of this GUC. Blog might serve as a good background before reading further.

Reorder buffer and logical_decoding_work_mem

Setting logical_decoding_work_mem optimally

https://postgr.es/p/78s

https://postgr.es/p/78q

I do a fair number of benchmarks, not only to validate patches, but also to find interesting (suspicious) stuff to improve. It’s an important part of my development workflow. And it’s fun ;-) But we’re dealing with complex systems (hardware, OS, DB, application), and that brings challenges. Every now and then I run into something that I don’t quite understand.

Consider a read-only pgbench, the simplest workload there is, with a single SELECT doing lookup by PK. If you do this with a small data set on any machine, the expectation is near linear scaling up to the number of cores. It’s not perfect, CPUs have frequency scaling and power management, but it should be close.

Some time ago I tried running this on a big machine with 176 cores (352 threads), using scale 50 (about 750MB, so tiny - it actually fits into L3 on the EPYC 9V33X CPU). And I got the following chart for throughput with different client counts:

This is pretty awful. I still don’t think I entirely understand why this happens, or how to improve the behavior. But let me explain what I know so far, what I think may be happening, and perhaps someone will correct me or have an idea how to fix it.

https://postgr.es/p/78q

https://postgr.es/p/78p

POSETTE: An Event for Postgres 2025 is back for its 4^th year—free, virtual, and packed with deep expertise. No travel needed, just your laptop, internet, and curiosity.

This year’s 45 speakers are smart, capable Postgres practitioners—core contributors, performance experts, application developers, Azure engineers, extension maintainers—and their talks are as interesting as they are useful.

The four livestreams (42 talks total) run from June 10-12, 2025. Every talk will be posted to YouTube afterward (un-gated, of course). But if you can join live, I hope you do! On the virtual hallway track on Discord, you’ll be able to chat with POSETTE speakers—as well as other attendees. And yes, there will be swag.

This “ultimate guide” blog post is your shortcut to navigating POSETTE 2025. In this post you’ll get:

• A “by the numbers” summary
• 2 Amazing Keynotes
• 18 Postgres core talks
• 12 Postgres ecosystem talks
• 10 Azure Database for PostgreSQL talks
• Where to find the POSETTE Schedule
• How to watch & how to participate on Discord
• What’s new in POSETTE 2025?
• A big thank you to our 45 amazing speakers
• Join us for POSETTE 2025 & mark your calendars

“By the numbers” summary for POSETTE 2025

Here’s a quick snapshot of what you need to know about POSETTE:

And to give you a feel for the hi-level categories and detailed “tags”, to help you navigate all 42 of the talks, maybe this diagram will help.

2 Amazing Keynotes

If you’re interested in what Microsoft is building for Postgres these days, then Charles Feddersen’s keynote is a must-watch. And in spite of all the hype about AI, you’re guaranteed to enjoy Bruce Momjian’s keynote about databases in the AI trenches.

• KEYNOTE: What Microsoft is Building for Postgres in 2025, by Charles Feddersen in Livestream 1 and Livestream 4 (Azure, Postgres 18, Async IO, AI, RAG, VS Code, community, livestream1, livestream4)
• KEYNOTE: Databases in the AI Trenches, by Bruce Momjian in Livestream 2 and Livestream 3 (AI, semantic search, vector search, RAG, livestream2, livestream3)

18 Postgres Core talks

Performance

• Best Practices for Tuning Slow Postgres Queries, by Lukas Fittl (EXPLAIN, optimizing queries, performance, startup, livestream1)
• Fun With UUIDs, by Chris Ellis (UUIDs, indexes, performance, livestream3)
• Hacking Postgres Executor For Performance, by Amit Langote (PG internals, executor, performance, livestream4)
• Leveraging table partitioning for query performance and data archiving, by Derk van Veen (partitioning, performance, livestream4)
• Performance Archaeology - 20 years of improvements, by Tomas Vondra (performance, benchmarks, livestream1)
• What’s new in the Postgres 18 query planner / optimizer, by David Rowley (PG internals, planner, livestream2)

Postgres internals

• Designing a monitoring feature in PostgreSQL, by Rahila Syed (monitoring, PG internals, livestream4)
• PostgreSQL and Linux Kernel interactions, by Krishnakumar "KK" Ravi (Async IO, PG internals, Linux, troubleshooting, livestream3)

Replication

• Logical replication theory and concepts, by Ashutosh Bapat (replication, wal, livestream2)
• Myths and Truths about Synchronous Replication in PostgreSQL, by Alexander Kukushkin (HA, replication, livestream3)
• Securing Postgres with streaming replication, by Jan Karremans (replication, disaster recovery, WAL, livestream4)

Community

• Setting max_connection: Building PostgreSQL user groups that bring people together, by Ellyne Phneah (community, startup, user groups, livestream4)

Fun

• Debugging Data Corruption in PostgreSQL: A Systematic Approach, by Palak Chaturvedi & Nitin Jadhav (debugging, Linux, livestream2)
• Implementing Strict Serializability with pg_xact, by Jimmy Zelinskie (strict serializability, pg_xact, livestream3)
• Incremental Backup in PostgreSQL, by Robert Haas (backup, incremental backup, livestream1)
• Managing Postgres at scale: Challenges, Tools & Techniques, by Karen Jex (backups, HA, monitoring, partitioning, scalability, sharding, livestream3)
• Neon: How we made PostgreSQL serverless, by Heikki Linnakangas (serverless, Neon, startup, livestream2)
• Postgres Storytelling: Cunning Schema Design with Creative Data Modeling, by Boriss Mejías & Sarah Conway (data modeling, livestream1)

12 Postgres Ecosystem talks

Analytics

• Building a PostgreSQL data warehouse, by Marco Slot (extensions, analytics, data warehouse, livestream4)
• pg_duckdb: Ducking awesome analytics in Postgres, by Jelte Fennema-Nio (extensions, analytics, DuckDB, startup, livestream2)

App dev

• Building modern Python web apps with PostgreSQL, by Pamela Fox (app dev, Python, livestream1)
• Exploring .NET and PostgreSQL on Linux as your OSS app dev stack, by Silvano Coriani (app dev, .NET, Linux, livestream2)

Extensions

• Can We Use Rust to Develop Extensions for PostgreSQL?, by Shinya Kato (extensions, pgrx, Rust, livestream2)
• Designing for Document Databases in PostgreSQL, by Vinod Sridharan (extensions, DocumentDB, livestream1)
• Elasticsearch-Quality Full-Text Search in Postgres via Tantivy, by Philippe Noël (Full text search, extensions, pg_search, startup, livestream1)
• From MongoDB to Postgres: Building an Open Source Standard for Document Databases, by Peter Farkas (FerretDB, extensions, DocumentDB, MongoDB, startup, livestream4)
• Hitchhiker's Guide to Row-Level Security in Citus, by Adam Wølk (security, Citus, livestream4)
• Resource Control Admission - I have a date with my PSI, by Cédric Villemain (extensions, pg_psi, monitoring, Linux, PG Internals, livestream4)

Patroni

• What is Patroni, really?, by Polina Bungina (HA, Patroni, livestream2)

VS Code

• Introducing Microsoft’s VS Code Extension for PostgreSQL, by Matt McFarland (VS Code, IDE, livestream1)

10 Azure Database for PostgreSQL talks

AI-related talks

• Building Enterprise RAG with Azure Database for PostgreSQL and pgvector, by Michael John Pena (AI, Azure, RAG, livestream4)
• Building Intelligent Applications with Graph-Based RAG on PostgreSQL, by Abe Omorogbe (AI, AzureDBPostgres, RAG, livestream3)

Customer talks

• Fortifying Azure Database for PostgreSQL: Stop Intrusions in Their Tracks, by Johannes Schuetzner (Azure, authentication, customer, Mercedes, networking, security, livestream2)
• Scaling Postgres to the next level at OpenAI, by Bohan Zhang (AI, Azure, customer, OpenAI, livestream1)

Flexible Server talks

• Azure Database for PostgreSQL: 15 Essential Standards for Compliance and Security, by Taiob Ali (Azure, authentication, backup, compliance, security, livestream3)
• Boosting Azure PostgreSQL Performance with Azure Advisor, by Gayathri Paderla (Azure, Azure Advisor, performance, livestream1)
• Dear Azure Database for PostgreSQL, can you automate my index?, by Nacho Alonso Portillo (Azure, indexes, livestream2)
• Overcoming Performance Hurdles in Postgres Partitioned Tables, by Sarat Balijepalli (Azure, partitioning, performance, livestream3)
• What’s New with Azure Postgres Flexible Server in 2025 🆕, by Varun Dhawan (Azure, what's new, flexible server, livestream3)

Oracle to Postgres talks

• Migrating from Oracle to Azure Database for PostgreSQL, Seamlessly, by Neeta Goel & Sandeep Rajeev (Azure, Oracle to Postgres, migration, livestream3)

Where to find the POSETTE Schedule

You may be thinking, “I know how to use a website, Claire.” Fair. But hear me out: the POSETTE 2025 Schedule page has 4 tabs—one for each livestream—and it always opens to Livestream 1 by default.

So if you’re looking for talks in Livestreams 2, 3, or 4:

• Head to the POSETTE Schedule page
• Click the tab for the livestream you want
• Voila—talks for that stream

How to watch & how to participate on Discord

Here’s how to tune in—and how to participate in the conference.

How to watch the livestreams

• All 4 livestreams will be watchable on the PosetteConf 2025 home page
• Pro tip: If you’ve left the page open since the last stream, refresh your browser to see the next livestream.

How to join the virtual hallway track

• Head to the #posetteconf channel on Discord (on the Microsoft Open Source Discord)
• That’s where speakers and attendees hang out during the livestreams—it’s where you can ask questions, share reactions, and just say hi.

What’s new in POSETTE 2025

If you attended POSETTE last year (or back when it was called Citus Con), you might be wondering, what’s different this year?

In many ways, the POSETTE playbook is the same: useful and delightful Postgres talks in a virtual, accessible format. But here’s what’s new:

• New website: And, a new domain too: PosetteConf.com
• Only 2 keynotes: instead of 4 keynotes last year. We’re honored that Bruce Momjian & Charles Feddersen accepted the invitation to be keynote speakers. Each keynote will be repeated twice.
• 58% speakers new to POSETTE: 26 out of 45 speakers (58%) are brand new to POSETTE
• New livestream hosts: 3 of the 7 livestream hosts are brand new to hosting POSETTE livestreams: welcome to Adam Wølk, Derk van Veen, & Thomas Munro
• Same name: The POSETTE: An Event for Postgres name is here to stay—and we still love the name

Big thank you to our 45 amazing speakers

Every great event starts with great talks—and great talks start with great speakers. Want to learn more about the people behind these talks?

• Visit the POSETTE 2025 Speaker page
• Click a speaker’s bio to see their interview (if available)
• If a speaker has been a guest on the Talking Postgres podcast in the past, then you’ll find a link to their episode there, too

Join us for POSETTE 2025! Mark your calendars

I hope you join us for POSETTE 2025. Consider yourself officially invited. As part of the talk selection team, I’m definitely biased—but I truly believe these speakers and talk are worth your time.

I’ll be hosting Livestream 1 and Livestream 2 and you’ll find me in the #posetteconf Discord chat. I hope to see you there.

And please—tell your Postgres friends, so they don’t miss out!

🗓️ Add the livestreams to your calendar

• Livestream 1: Tue June 10^th 8am-2pm PDT (UTC-7)
• Livestream 2: Wed June 11^th 8am-2pm CEST (UTC+2)
• Livestream 3: Wed June 11^th 8am-2pm PDT (UTC-7)
• Livestream 4: Thu June 12^th 8am-2pm CEST (UTC+2)

Watch last year’s talks in advance: And if you want to get ready, check out the POSETTE 2024 playlist on YouTube. Lots of gems in there.

Acknowledgements & Gratitude

I’ve already thanked the amazing speakers above. In addition, thanks go to Daniel Gustafsson, Teresa Giacomini, and My Nguyen for reviewing parts of this post before publication. And of course, big thank you to the POSETTE 2025 organizing team and POSETTE talk selection team—without you, there would be no POSETTE!

This article was originally published on citusdata.com.

https://postgr.es/p/78p

https://postgr.es/p/788

https://postgr.es/p/788

https://postgr.es/p/78m

Many companies these days are thinking about migrating their databases from legacy or proprietary system to PostgreSQL. The primary aim is to reduce costs, enhance capabilities, and ensure long-term sustainability. However, even just the idea of migrating to PostgreSQL can be overwhelming. Very often, knowledge about the legacy applications is limited or even lost. In some cases, vendor support is diminishing, and expert pools and community support are shrinking. Legacy databases are also often running on outdated hardware and old operating systems, posing further risks and limitations. (more…)

https://postgr.es/p/78m