DEV Community: Vahid Yousefzadeh

Upgrading PostgreSQL 15 to 18 Using pg_upgrade

Vahid Yousefzadeh — Tue, 10 Nov 2026 14:22:50 +0000

PostgreSQL provides pg_upgrade to upgrade an existing database cluster to a newer major version without requiring a logical dump and restore.

In this article, I’ll demonstrate how to upgrade PostgreSQL 15 to PostgreSQL 18 using pg_upgrade with the — link option.

Unlike a traditional dump-and-restore approach, link mode allows PostgreSQL to reuse the existing relation files through hard links, significantly reducing the time and disk space required for an upgrade.

1. Checking the Existing PostgreSQL 15 Environment

First, let’s check the existing PostgreSQL installation and its configuration.

I’m using the following environment variables and version:

[postgres@OEL98-PG ~]$ export PGHOME=/usr/pgsql-15
[postgres@OEL98-PG ~]$ export PGDATA=/var/lib/pgsql/15/data
[postgres@OEL98-PG ~]$ export PGPORT=5415

postgres=# select version();
                                                 version
------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.19 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit
(1 row)

Check the current data directory and databases:

SHOW data_directory;
     data_directory
------------------------
 /var/lib/pgsql/15/data

postgres=# \l
                                                     List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           |
 template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +
           |          |          |                 |             |             |        |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | postgres=CTc/postgres+
           |          |          |                 |             |             |        |           | =c/postgres
 vahiddb   | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           |
(4 rows)

Before upgrading, let’s identify the physical location of a table. I’ll use mazandaran.tbl_person as an example.

vahiddb=# SELECT
    c.relname,
    pg_relation_filepath(c.oid) AS relative_path,
    current_setting('data_directory') || '/' ||
    pg_relation_filepath(c.oid) AS absolute_path
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'mazandaran'
  AND c.relkind IN ('r','p')
ORDER BY c.relname;
  relname   |  relative_path   |              absolute_path
------------+------------------+-----------------------------------------
 tbl_person | base/16386/16389 | /var/lib/pgsql/15/data/base/16386/16389
(1 row)

vahiddb=# select * from MAZANDARAN.tbl_person;
 id |  fname  |    lname    | sal
----+---------+-------------+------
  1 | Vahid   | Yousefzadeh | 5.00
  2 | Nader   | DastNeshan  | 6.00
  3 | Kourosh | Barmak      | 6.00
(3 rows)

Finally, use the Linux stat command to record the inode number, link count of the table’s physical file.

[postgres@OEL98-PG ~]$ stat -c 'Inode=%i Links=%h File=%n' \
> /var/lib/pgsql/15/data/base/16386/16389

Inode=973746 Links=1 File=/var/lib/pgsql/15/data/base/16386/16389

We’ll compare these values after the upgrade.

2. Checking the PostgreSQL 18 Installation

PostgreSQL 18 is already installed on the server. Let’s configure the environment variables for the new installation and check the new PostgreSQL version:

[postgres@OEL98-PG ~]$  export PGHOME=/usr/pgsql-18
[postgres@OEL98-PG ~]$  export PGDATA=/var/lib/pgsql/18/data
[postgres@OEL98-PG ~]$ export PGPORT=5418
[postgres@OEL98-PG ~]$ psql
psql (19beta4, server 18.6)
Type "help" for help.

postgres=# select version();
                                                  version
------------------------------------------------------------------
 PostgreSQL 18.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit
(1 row)

Check the new data directory:

postgres=# show data_directory;
     data_directory
------------------------
 /var/lib/pgsql/18/data
(1 row)

3. Stopping Both PostgreSQL Instances

Before running pg_upgrade, both PostgreSQL instances must be stopped.

I use two shell scripts, pg15.sh and pg18.sh, to switch between the environments.

Stop PostgreSQL 15:

[postgres@OEL98-PG ~]$ source pg15.sh
[postgres@OEL98-PG ~]$  pg_ctl -D /var/lib/pgsql/15/data -l logfile stop
waiting for server to shut down.... done
server stopped

Stop PostgreSQL 18:

[postgres@OEL98-PG ~]$ source pg18.sh
[postgres@OEL98-PG ~]$  pg_ctl -D /var/lib/pgsql/18/data -l logfile stop
waiting for server to shut down.... done
server stopped

4. Checking Upgrade Compatibility

Before performing the actual upgrade, it’s a good idea to run pg_upgrade with the — check option.

This checks whether the existing cluster is compatible with the new PostgreSQL installation without performing the upgrade.

Run:

[postgres@OEL98-PG ~]$ /usr/pgsql-18/bin/pg_upgrade \
  --old-datadir=/var/lib/pgsql/15/data \
  --new-datadir=/var/lib/pgsql/18/data \
  --old-bindir=/usr/pgsql-15/bin \
  --new-bindir=/usr/pgsql-18/bin \
  --check


Performing Consistency Checks
-----------------------------
Checking cluster versions                                     ok

Checking database connection settings                         ok
Checking database user is the install user                    ok
Checking for prepared transactions                            ok
Checking for contrib/isn with bigint-passing mismatch         ok
Checking data type usage                                      ok
Checking for not-null constraint inconsistencies              ok
Checking for presence of required libraries                   ok
Checking database user is the install user                    ok
Checking for prepared transactions                            ok
Checking for new cluster tablespace directories               ok

*Clusters are compatible*

The compatibility check completed successfully.

5. Performing the Upgrade Using Link Mode

Now we can perform the actual upgrade.

The important option in this command is — link.

[postgres@OEL98-PG ~]$ /usr/pgsql-18/bin/pg_upgrade \
--old-datadir=/var/lib/pgsql/15/data \
--new-datadir=/var/lib/pgsql/18/data \
--old-bindir=/usr/pgsql-15/bin \
--new-bindir=/usr/pgsql-18/bin \
--link



Performing Consistency Checks
-----------------------------
Checking cluster versions                                     ok
Checking database connection settings                         ok
Checking database user is the install user                    ok
Checking for prepared transactions                            ok
Checking for contrib/isn with bigint-passing mismatch         ok
Checking data type usage                                      ok
Checking for not-null constraint inconsistencies              ok
Creating dump of global objects                               ok
Creating dump of database schemas
                                                              ok
Checking for presence of required libraries                   ok
Checking database user is the install user                    ok
Checking for prepared transactions                            ok
Checking for new cluster tablespace directories               ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Setting locale and encoding for new cluster                   ok
Analyzing all rows in the new cluster                         ok
Freezing all rows in the new cluster                          ok
Deleting files from new pg_xact                               ok
Copying old pg_xact to new server                             ok
Setting oldest XID for new cluster                            ok
Setting next transaction ID and epoch for new cluster         ok
Deleting files from new pg_multixact/offsets                  ok
Copying old pg_multixact/offsets to new server                ok
Deleting files from new pg_multixact/members                  ok
Copying old pg_multixact/members to new server                ok
Setting next multixact ID and offset for new cluster          ok
Resetting WAL archives                                        ok
Setting frozenxid and minmxid counters in new cluster         ok
Restoring global objects in the new cluster                   ok
Restoring database schemas in the new cluster
                                                              ok
Adding ".old" suffix to old "global/pg_control"               ok

If you want to start the old cluster, you will need to remove
the ".old" suffix from "/var/lib/pgsql/15/data/global/pg_control.old".
Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.
Linking user relation files
                                                              ok
Setting next OID for new cluster                              ok
Sync data directory to disk                                   ok
Creating script to delete old cluster                         ok
Checking for extension updates                                ok

Upgrade Complete
----------------
Some statistics are not transferred by pg_upgrade.
Once you start the new server, consider running these two commands:
    /usr/pgsql-18/bin/vacuumdb --all --analyze-in-stages --missing-stats-only

    /usr/pgsql-18/bin/vacuumdb --all --analyze-only
Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

Important: What does — link actually do?

The — link option uses hard links instead of copying the user relation files into the new cluster.

This means the old and new cluster can reference the same underlying physical file through different directory entries.

Consequently:

The upgrade can be significantly faster than copying all relation files.
It requires less additional disk space.
Both directory entries initially reference the same underlying file.
Once the new PostgreSQL cluster is started, the old cluster must not be started again.
PostgreSQL explicitly warns about this in the upgrade output:

Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.

Important: Make sure you have a valid backup before performing this operation. With link mode, you should treat the old cluster as unusable once the new cluster has been started.

6. Starting PostgreSQL 18

After the upgrade finishes, start the new PostgreSQL instance:

[postgres@OEL98-PG ~]$ pg_ctl -D /var/lib/pgsql/18/data -l logfile start
waiting for server to start.... done
server started

7. Updating Optimizer Statistics

The pg_upgrade output recommends updating optimizer statistics:

[postgres@OEL98-PG ~]$ /usr/pgsql-18/bin/vacuumdb --all --analyze-in-stages --missing-stats-only

vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "vahiddb": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "vahiddb": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics
vacuumdb: processing database "vahiddb": Generating default (full) optimizer statistics

[postgres@OEL98-PG ~]$ /usr/pgsql-18/bin/vacuumdb --all --analyze-only
vacuumdb: vacuuming database "postgres"
vacuumdb: vacuuming database "template1"
vacuumdb: vacuuming database "vahiddb"

This performs a full analysis of the databases without performing vacuum operations.

8. Removing the Old PostgreSQL Cluster

After verifying that the new cluster is working correctly, you can remove the old cluster’s data files.

pg_upgrade automatically creates a cleanup script:

[postgres@OEL98-PG ~]$ ./delete_old_cluster.sh
[postgres@OEL98-PG ~]$ ls -l /var/lib/pgsql/15/*
total 0

9. Verifying the PostgreSQL 18 Upgrade

Now let’s verify the new PostgreSQL installation.

Check the version:

postgres=# select version();
                                                  version
------------------------------------------------------------------------
 PostgreSQL 18.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit
(1 row)

Check the data directory:

[postgres@OEL98-PG ~]$ psql
psql (18.6)
Type "help" for help.

postgres=# show data_directory;
     data_directory
------------------------
 /var/lib/pgsql/18/data
(1 row)

List the databases:

postgres=# \l
                                                     List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           |
 template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +
           |          |          |                 |             |             |        |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | postgres=CTc/postgres+
           |          |          |                 |             |             |        |           | =c/postgres
 vahiddb   | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           |
(4 rows)

Check the table contents:

postgres=# \c vahiddb
You are now connected to database "vahiddb" as user "postgres".

vahiddb=# select * from mazandaran.tbl_person;
 id |  fname  |    lname    | sal
----+---------+-------------+------
  1 | Vahid   | Yousefzadeh | 5.00
  2 | Nader   | DastNeshan  | 6.00
  3 | Kourosh | Barmak      | 6.00
(3 rows)

Comparing Physical Relation Files Before and After the Upgrade Finally, let’s repeat the physical file location query.

vahiddb=# SELECT
    c.relname,
    pg_relation_filepath(c.oid) AS relative_path,
    current_setting('data_directory') || '/' ||
    pg_relation_filepath(c.oid) AS absolute_path
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'mazandaran'
  AND c.relkind IN ('r','p')
ORDER BY c.relname;
  relname   |  relative_path   |              absolute_path
------------+------------------+-----------------------------------------
 tbl_person | base/16386/16389 | /var/lib/pgsql/18/data/base/16386/16389
(1 row)

Notice that the relative path is still the same:

base/16386/16389

The absolute path has changed because the new PostgreSQL cluster uses a different data directory.

We can also check the inode number again:

[postgres@OEL98-PG ~]$ stat -c 'Inode=%i Links=%h File=%n' \
/var/lib/pgsql/18/data/base/16386/16389

Inode=973746 Links=1 File=/var/lib/pgsql/18/data/base/16386/16389
The inode number is the same as before the upgrade.

This is an important observation when investigating how pg_upgrade — link handles physical relation files.

Simplified Macros in Oracle AI Database 23.26.4

Vahid Yousefzadeh — Mon, 09 Nov 2026 07:51:00 +0000

SQL Macros, introduced in Oracle Database 21c, allow developers to create reusable SQL expressions and queries. I previously covered this feature in my article SQL Macros in Oracle Database 21c.

Previously, creating SQL Macros required PL/SQL functions that returned SQL statements as strings.
Oracle AI Database 23.26.4 introduces Simplified Macros, allowing developers to define macros directly using the new CREATE MACRO statement without writing PL/SQL code.
In this article, we will explore this enhancement and rewrite some of the examples from my previous article using the new syntax.

1. Creating a Scalar SQL Macro

Let’s start with a simple example.
Suppose we have a table named PERSON and want to add 60 to the salary of each employee.
In earlier releases, we could define a Scalar SQL Macro using a PL/SQL function:

SQL> CREATE OR REPLACE FUNCTION sal_func(
    sal_param NUMBER
)
RETURN VARCHAR2 SQL_MACRO(SCALAR)
IS
BEGIN
    RETURN q'{
        sal_param + 60
    }';
END;
/
Function created

We could then use this function in a SQL statement:

SQL> select sum(sal) from(select sal_func(salary) as sal from person);
  SUM(SAL)
----------
       197

With Oracle AI Database 23.26.4, we can define the same macro directly using SQL:

SQL> drop FUNCTION sal_func;
Function dropped

SQL> CREATE MACRO sal_func(sal_param NUMBER) AS
        sal_param + 60;
Done

The invocation remains unchanged:

SQL> select sum(sal) from(select sal_func(salary) as sal from person);
  SUM(SAL)
----------
       197

As you can see, we no longer need to create a PL/SQL function, specify SQL_MACRO(SCALAR), or return the SQL expression as a character string.

2. Creating a Table SQL Macro

SQL Macros can also return SQL queries and be used in the FROM clause.
Consider the following example from my previous article. This macro returns the size, in MB, of the largest segment belonging to a specified schema.
In earlier releases, we could implement it as follows:

SQL>CREATE OR REPLACE FUNCTION table_macro_Mesal(
    p_tab VARCHAR2
)
RETURN VARCHAR2 SQL_MACRO(TABLE)
IS
BEGIN
    RETURN q'{
        SELECT MAX(bytes/1024/1024) SIZE_MB
        FROM dba_segments d
        WHERE owner = table_macro_Mesal.p_tab
    }';
END;
/
Function created

SQL> SELECT * FROM table_macro_Mesal('SYS');
   SIZE_MB
----------
       168

With the new syntax, the definition becomes much simpler:

SQL>CREATE MACRO table_macro_Mesal(p_tab VARCHAR2) AS
    SELECT MAX(bytes/1024/1024) SIZE_MB
    FROM dba_segments d
    WHERE owner = table_macro_Mesal.p_tab;
Done

We can invoke the macro in exactly the same way:

SQL> SELECT * FROM table_macro_Mesal('SYS');
   SIZE_MB
----------
       168

The macro accepts a schema name as its parameter and returns a query that calculates the maximum segment size for that schema.

3. Passing Multiple Parameters to a Table SQL Macro

The new syntax also supports multiple parameters.
Let’s consider another example from my previous article. This macro accepts two parameters:
• cnt: The number of segments to return.
• typ: The segment type used to filter the results.
The original function-based implementation was:

SQL> CREATE OR REPLACE FUNCTION table_macro_Mesal2(
    cnt NUMBER,
    typ VARCHAR2
)
RETURN VARCHAR2 SQL_MACRO(TABLE)
IS
BEGIN
    RETURN q'{
        SELECT owner,
               segment_name,
               bytes / 1024 / 1024 SIZE_MB
        FROM dba_segments
        WHERE segment_type = table_macro_Mesal2.typ
        ORDER BY bytes DESC
        FETCH FIRST cnt ROWS ONLY
    }';
END;
/
Function created

SQL> SELECT * FROM table_macro_Mesal2(5, 'TABLE');
OWNER  SEGMENT_NAME            SIZE_MB
------ -------------------- ----------
SYS    IDL_UB2$                     18
SYS    IDL_UB1$                     15
SYS    OBJ$                         11
MDSYS  SDO_CS_SRS                    9
SYS    DEPENDENCY$                   6

Using the new CREATE MACRO statement, we can define it as follows:

SQL> CREATE MACRO table_macro_Mesal2(
    cnt NUMBER,
    typ VARCHAR2
) AS
    SELECT owner,
           segment_name,
           bytes / 1024 / 1024 SIZE_MB
    FROM dba_segments
    WHERE segment_type = table_macro_Mesal2.typ
    ORDER BY bytes DESC
    FETCH FIRST table_macro_Mesal2.cnt ROWS ONLY;
Done

SQL> SELECT * FROM table_macro_Mesal2(5, 'TABLE');
OWNER  SEGMENT_NAME            SIZE_MB
------ -------------------- ----------
SYS    IDL_UB2$                     18
SYS    IDL_UB1$                     15
SYS    OBJ$                         11
MDSYS  SDO_CS_SRS                    9
SYS    DEPENDENCY$                   6

4. Additional Capabilities of Native SQL Macros

Oracle AI Database 23.26.4 introduces additional capabilities for native SQL Macros, including default parameter values, table parameters, reusable predicates, and binding macros to specific tables or views.

Oracle also provides new data dictionary views, such as USER_MACROS and USER_MACRO_PARAMETERS, to inspect macro definitions and their parameters.

SQL> select owner,name,param_count from USER_MACROS;
OWNER  NAME                  PARAM_COUNT
------ --------------------  -----------
VAHID  SAL_FUNC                        1
VAHID  TABLE_MACRO_MESAL               1
VAHID  TABLE_MACRO_MESAL2              2

I will explore these capabilities in more detail in future articles.

SQL Macros in Oracle Database 21c

Vahid Yousefzadeh — Mon, 09 Nov 2026 06:56:02 +0000

As you may know, calling a PL/SQL function from a SQL query introduces a context switch between the SQL engine and the PL/SQL engine. This context switching can have a negative impact on query performance.

For example, the following two statements return the same result, but their execution times are significantly different.

Query without using a function

SQL> select sum(sal) from(select salary+60 as sal from person);

  SUM(SAL)

     ———

8891924480

Elapsed: 00:00:00.81

Query using a function

SQL> select sum(sal) from(select sal_func(salary) as sal from person);

  SUM(SAL)

———

8891924480

Elapsed: 00:00:10.91

Note: The sal_func function is defined as follows:

create or replace function sal_func(sal_param  number)
  return number
is
begin
  return sal_param +60 ;
end;

/

As you can see, the first query takes less than one second to execute, whereas the second query takes approximately 11 seconds.

This comparison might lead us to avoid calling PL/SQL functions from SQL queries. However, avoiding functions altogether can introduce significant maintenance overhead. For example, if a formula needs to be modified, we may have to update multiple parts of an application. Therefore, avoiding function calls in SQL statements is not always the best solution.

To address this performance issue, Oracle introduced SQL Macros in Oracle Database 21c.

SQL Macros allows Oracle Database to expand a function call into its corresponding SQL expression or query during SQL macro expansion. As a result, the SQL statement can be optimized as if the expanded SQL expression had been written directly in the original statement.

For example, Oracle transforms the following query:

select sum(sal) 
from(
select sal_func(salary) as sal 
from person
);

Into the following query:

Final query after transformations:******* UNPARSED QUERY IS *******

SELECT SUM(“PERSON”.”SALARY”+60) “SUM(SAL)” FROM “USEF”.”PERSON” “PERSON”

According to Oracle’s feature classification, SQL Macros are a new feature of Oracle Database 21c. However, they were also documented in Oracle Database 20c documentation and are available in Oracle Database 19c starting with Release Update 19.6.

Creating a SQL Macro

To create a SQL Macro, you must specify the SQL_MACRO clause in the function declaration.

The return type of a SQL Macro function must be a character data type, such as VARCHAR2 or CLOB, rather than the data type of the value returned by the expanded SQL expression.

Additionally, the SQL expression or query returned by the function must be enclosed in a quoted string, commonly using Oracle’s alternative quoting mechanism (q’{}’).

For example, we can rewrite the sal_func function as a Scalar SQL Macro:

create or replace function sal_func(sal_param number) 
return varchar2 sql_macro(scalar) is

begin

  return q'{
      sal_param +60
        }’;

end;

/

With this modification, the execution time of the query presented earlier decreases from approximately 11 seconds to just over one second:

SQL> select sum(sal) from(select sal_func(salary) as sal from person);

  SUM(SAL)

———-

8891924480

Elapsed: 00:00:01.11
Types of SQL Macros

Oracle Database supports two types of SQL Macros:

Scalar SQL Macros: Can be used in SQL expressions, including the SELECT list and applicable clauses such as WHERE and HAVING.
Table SQL Macros: Can be used in the FROM clause and return a SQL query that acts as a table expression.
Therefore, the SAL_FUNC function is an example of a Scalar SQL Macro.

The following function, which returns the size (in MB) of the largest segment belonging to a specified schema, is an example of a Table SQL Macro:

create or replace function table_macro_Mesal(p_tab varchar2)

  return varchar2 sql_macro(table) is

begin

  return q'{

    select max(bytes/1024/1024) SIZE_MB from
 dba_segments d 
where 
owner =table_macro_Mesal.p_tab

   }’;

end;

/

We can call this Table SQL Macro as follows:

SQL> select * from table_macro_Mesal(‘SYS’);

   SIZE_MB

    ———

    46

Passing Multiple Parameters to a Table SQL Macro

The following function accepts two input parameters:

cnt: The number of segments to return.
typ: the segment type to filter by.
It returns the largest segments based on their size, limited to the specified number of rows.

create or replace function table_macro_Mesal2(cnt number,typ varchar2)

  return varchar2 sql_macro(table) is

begin

  return q'{
 select owner, segment_name, bytes / 1024 / 1024 SIZE_MB
   from dba_segments
   where segment_type=table_macro_Mesal2.typ
  order by bytes desc fetch first cnt rows only
   }’;

end;
/

For example, to retrieve the five largest table segments in the database, we can execute:

SQL> select * from table_macro_Mesal2(5,’TABLE’);

OWNER      SEGMENT_NAME                 SIZE_MB

———- ————————- ———-

USEF       JADVAL_TEST                      312

USEF       PERSON                           104

SYS        IDL_UB2$                          16

SYS        WRI$_SQLSET_PLAN_LINES            15

SYS        IDL_UB1$                          12

As demonstrated in these examples, SQL Macros provide a way to encapsulate reusable SQL expressions and queries while allowing the SQL optimizer to work with the expanded SQL statement.

Oracle vs PostgreSQL: Schema-Level Privileges and Access to Future Tables

Vahid Yousefzadeh — Fri, 06 Nov 2026 07:55:23 +0000

Managing privileges across multiple database objects can become challenging, especially when users need access to all tables within a particular schema.

For example, suppose we have a schema named MAZANDARAN and want to grant a user named VAHID read access to all its tables.

There are different approaches to achieving this in Oracle and PostgreSQL.

Starting with Oracle AI Database 26ai, schema-level privileges provide a convenient way to grant system privileges on a specific schema without having to grant privileges individually on each object.

PostgreSQL provides several mechanisms for this purpose, including GRANT … ON ALL TABLES IN SCHEMA, ALTER DEFAULT PRIVILEGES, and the predefined role pg_read_all_data.

Let’s examine these approaches.

1. Oracle : Schema-Level Privileges

Starting with Oracle AI Database 26ai, we can grant certain system privileges on a specific schema.

For example, suppose we want to grant VAHID read access to all existing and future tables in the MAZANDARAN schema.

First, create the user and grant the required connection privilege:

SQL> create user vahid identified by vahid;

User created.

SQL> grant create session to vahid;

Grant succeeded.

Now, grant the schema-level privilege:

SQL> GRANT SELECT ANY TABLE ON SCHEMA MAZANDARAN TO VAHID;

Grant succeeded.

Unlike the traditional SELECT ANY TABLE system privilege, this grant is restricted to the specified schema.

Let’s test it:

SQL>  conn vahid/vahid@192.168.1.40:1521/vahiddb
Connected.

SQL> select * from MAZANDARAN.person;

  ID  FNAME     LNAME           SAL
----  --------- -------------- ----
   1  Vahid     Yousefzadeh       5
   2  Nader     DastNeshan        6
   3  Kourosh   Barmak            6

Access to future tables

One of the most useful aspects of schema-level privileges is that they apply to both existing and future objects.

For example, if the MAZANDARAN user creates another table:

SQL> conn mazandaran/a@192.168.1.40:1521/vahiddb

Connected.

SQL> create table mazandaran.person99 as select * from mazandaran.person;

Table created.

The VAHID user can immediately query the new table without requiring another GRANT statement:

SQL> conn vahid/vahid@192.168.1.40:1521/vahiddb
Connected.
SQL> select * from mazandaran.person99;

  ID  FNAME     LNAME           SAL
----  --------- -------------- ----
   1  Vahid     Yousefzadeh       5
   2  Nader     DastNeshan        6
   3  Kourosh   Barmak            6

This functionality is not dependent on the user who creates the table.

For example, even if SYS creates a new table in the MAZANDARAN schema, VAHID can still access it:

SQL> show user;
USER is "SYS"
SQL> create table mazandaran.person100 as select * from mazandaran.person;

Table created.

SQL> conn vahid/vahid@192.168.1.40:1521/vahiddb
Connected.
SQL> select * from mazandaran.person100;

  ID  FNAME     LNAME           SAL
----  --------- -------------- ----
   1  Vahid     Yousefzadeh       5
   2  Nader     DastNeshan        6
   3  Kourosh   Barmak            6

As demonstrated, Oracle AI database 26ai schema-level privileges simplify privilege management because the grant automatically covers new objects created within the specified schema.

2. PostgreSQL: Granting Access to Existing Tables

Let’s implement a similar scenario in PostgreSQL.

Suppose we have two schemas in the vahiddb database:

vahiddb=# \dt mazandaran.*

   Schema   |  Name  | Type  |  Owner
------------+--------+-------+----------
 mazandaran | person | table | postgres
(1 row)
vahiddb=# \dt gilan.*

           List of tables
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 gilan  | person | table | postgres
(1 row)

First, create a user named vahid:

vahiddb=# CREATE USER vahid WITH PASSWORD 'vahid';
CREATE ROLE

Let’s connect using this account and try to query the existing table:

vahiddb=# \c vahiddb vahid
You are now connected to database "vahiddb" as user "vahid".

vahiddb=> select * from mazandaran.person;
ERROR:  permission denied for schema mazandaran
LINE 1: select * from mazandaran.person;

In PostgreSQL, accessing a table requires appropriate privileges on both the schema and the table.

Therefore, we need to grant USAGE on the schema and SELECT on its existing tables.

As the postgres user:

vahiddb=> \c vahiddb postgres
You are now connected to database "vahiddb" as user "postgres".

vahiddb=# GRANT USAGE ON SCHEMA mazandaran TO vahid;
GRANT
vahiddb=# GRANT SELECT ON ALL TABLES IN SCHEMA mazandaran TO vahid;
GRANT

Now, VAHID can query the existing table:

vahiddb=# \c vahiddb vahid
You are now connected to database "vahiddb" as user "vahid".
vahiddb=>  select * from mazandaran.person;
 id |  fname  |    lname    | sal
----+---------+-------------+-----
  1 | Vahid   | Yousefzadeh |   5
  2 | Nader   | DastNeshan  |   6
  3 | Kourosh | Barmak      |   6
(3 rows)

However, there is an important difference between PostgreSQL and Oracle 26ai.

GRANT SELECT ON ALL TABLES IN SCHEMA applies to existing tables. It does not automatically grant access to tables created in the future.

Let’s demonstrate this behavior.

3. PostgreSQL: Access to Future Tables Using ALTER DEFAULT PRIVILEGES

Suppose the postgres user creates another table:

vahiddb=> \c vahiddb postgres
You are now connected to database "vahiddb" as user "postgres".

vahiddb=# create table mazandaran.newtbl as select * from mazandaran.person;
SELECT 3

Now, connect as vahid:

vahiddb=>  \c vahiddb vahid
You are now connected to database "vahiddb" as user "vahid".

vahiddb=> select * from mazandaran.newtbl;
ERROR:  permission denied for table newtbl

As expected, VAHID cannot access the newly created table.

To solve this problem, PostgreSQL provides the ALTER DEFAULT PRIVILEGES command.

For example:

vahiddb=>  \c vahiddb postgres
You are now connected to database "vahiddb" as user "postgres".

vahiddb=# ALTER DEFAULT PRIVILEGES
FOR ROLE postgres
IN SCHEMA mazandaran
GRANT SELECT ON TABLES TO vahid;
ALTER DEFAULT PRIVILEGES

This command configures default privileges for tables created by the postgres role in the mazandaran schema.

Important: ALTER DEFAULT PRIVILEGES does not modify privileges on existing objects. It only affects objects created after the command is executed.

Therefore, VAHID still cannot access the previously created newtbl.

We need to grant access to that table separately:

vahiddb=> \c vahiddb postgres
You are now connected to database "vahiddb" as user "postgres".

vahiddb=# GRANT SELECT ON mazandaran.newtbl TO vahid;
GRANT


vahiddb=#  \c vahiddb vahid
You are now connected to database "vahiddb" as user "vahid".

vahiddb=> select * from mazandaran.newtbl;
 id |  fname  |    lname    | sal
----+---------+-------------+-----
  1 | Vahid   | Yousefzadeh |   5
  2 | Nader   | DastNeshan  |   6
  3 | Kourosh | Barmak      |   6
(3 rows)

Let’s create another table to verify that the default privileges work:

vahiddb=>  \c vahiddb postgres
You are now connected to database "vahiddb" as user "postgres".

vahiddb=# create table mazandaran.newtbl2 as select * from mazandaran.person;
SELECT 3


vahiddb=# \c vahiddb vahid
You are now connected to database "vahiddb" as user "vahid".

vahiddb=> select * from mazandaran.newtbl2;
 id |  fname  |    lname    | sal
----+---------+-------------+-----
  1 | Vahid   | Yousefzadeh |   5
  2 | Nader   | DastNeshan  |   6
  3 | Kourosh | Barmak      |   6
(3 rows)

An important limitation of ALTER DEFAULT PRIVILEGES

There is another important consideration.

Default privileges are associated with the role that creates the object, not simply the schema in which the object is created.

For example, suppose another superuser named usef creates a table in the mazandaran schema:

vahiddb=> \c vahiddb usef
You are now connected to database "vahiddb" as user "usef".

vahiddb=# create table mazandaran.newtbl99  as select * from mazandaran.person;
SELECT 3

Although the table belongs to the mazandaran schema, it is owned by usef.

The default privileges configured for postgres do not automatically apply to objects created by usef.

Consequently, vahid cannot query this table:

vahiddb=#  \c vahiddb vahid
You are now connected to database "vahiddb" as user "vahid".

vahiddb=> select * from mazandaran.newtbl99;
ERROR:  permission denied for table newtbl99

We could configure default privileges separately for every role that creates objects in this schema.

However, managing this configuration can become complicated in environments where multiple users or administrators create database objects.

Is there a simpler solution?

4. PostgreSQL: Using the pg_read_all_data Predefined Role

PostgreSQL provides a predefined role named pg_read_all_data that can simplify this requirement.

This role allows reading all data!!!(tables, views, and sequences), as if the user had the corresponding SELECT privileges on those objects and USAGE privileges on all schemas.

It also applies to objects created in the future.

Let’s grant this role to vahid.

As a superuser:

postgres=# \c vahiddb usef
You are now connected to database "vahiddb" as user "usef".

vahiddb=# grant pg_read_all_data  to vahid;
GRANT ROLE


vahiddb=# create table mazandaran.newtbl100 as select * from mazandaran.person;
SELECT 3

vahiddb=# \c vahiddb vahid
You are now connected to database "vahiddb" as user "vahid".

vahiddb=> select * from mazandaran.newtbl100;
 id |  fname  |    lname    | sal
----+---------+-------------+-----
  1 | Vahid   | Yousefzadeh |   5
  2 | Nader   | DastNeshan  |   6
  3 | Kourosh | Barmak      |   6
(3 rows)

But there is something else worth mentioning.

Unlike the previous approaches, pg_read_all_data is not restricted to a particular schema.

For example, VAHID can also access tables in the GILAN schema without receiving any additional privileges:

vahiddb=# \c vahiddb vahid
You are now connected to database "vahiddb" as user "vahid".

vahiddb=> select * from gilan.person;
 id |  fname  |    lname    | sal
----+---------+-------------+-----
  1 | Vahid   | Yousefzadeh |   5
  2 | Nader   | DastNeshan  |   6
  3 | Kourosh | Barmak      |   6
(3 rows)

This is because pg_read_all_data provides read access across all schemas, rather than restricting access to a specific schema.

One important security consideration: pg_read_all_data does not bypass Row-Level Security (RLS) policies. If RLS is enabled, the applicable policies still control which rows the user can see.

As you can see, the pg_read_all_data predefined role is similar to Oracle’s SELECT ANY TABLE system privilege because both provide read access to tables across the database.

Oracle AI Database 26ai(23.26.4):SELECT FOR UPDATE with Row Limit

Vahid Yousefzadeh — Mon, 02 Nov 2026 09:38:12 +0000

Oracle AI Database 26ai Release 23.26.4 introduces support for combining Top-N queries using the FETCH FIRST clause with FOR UPDATE [SKIP LOCKED].

This enhancement allows applications to retrieve and lock a limited number of rows using the FETCH FIRST clause, simplifying the implementation of batch-processing operations that require controlled row-level locking.

Testing the Feature

The PERSON table contains the following rows:

SQL> select * from person;

        ID FNAME      LNAME                  SAL
---------- ---------- --------------- ----------
         1 Vahid      Yousefzadeh              4
         2 Nader      DastNeshan               5
         3 Kourosh    Barmak                   6

The FETCH FIRST clause works correctly when used without FOR UPDATE SKIP LOCKED:

SQL> select * from person order by 1 FETCH FIRST 1 ROWS ONLY;

        ID FNAME      LNAME                  SAL
---------- ---------- --------------- ----------
         1 Vahid      Yousefzadeh              4

Consider the following query executed on 23.26.3:

SQL> select * from person 
order by 1 
FETCH FIRST 1 ROWS ONLY 
FOR UPDATE SKIP LOCKED;
*
ERROR at line 1:
ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
Help: https://docs.oracle.com/error-help/db/ora-02014/

However, the statement fails with ORA-02014, indicating that Oracle does not allow the requested locking operation in this query context. Although the FETCH FIRST clause works independently, combining it with FOR UPDATE SKIP LOCKED produces an error in this release.

Release 23.26.4

Next, I tested the same statement on Oracle AI Database 26ai Release 23.26.4:

SQL> SELECT *
 FROM person
 ORDER BY 1
 FETCH FIRST 1 ROWS ONLY
 FOR UPDATE SKIP LOCKED;

ID FNAME LNAME SAL
 - - - - - - - - - - - - - - - - - - - - - - - 
 1 Vahid Yousefzadeh 4

Unlike the previous test on 23.26.3, this statement succeeds in the tested 23.26.4 environment, demonstrating that the combination works in this environment.

PostgreSQL 19 - Monitoring Lock Contention and Fast-Path Overflow with pg_stat_lock

Vahid Yousefzadeh — Wed, 28 Oct 2026 05:23:06 +0000

PostgreSQL 19 introduces the pg_stat_lock view, which provides cumulative statistics about lock activity across the database cluster. It helps database administrators investigate lock contention, measure time spent waiting for locks, and identify situations where relation locks exceed the available fast-path slots.

In this article, I will demonstrate these capabilities through two practical scenarios.

postgres=# \d pg_stat_lock
                        View "pg_catalog.pg_stat_lock"
      Column       |           Type           | Collation | Nullable | Default
-------------------+--------------------------+-----------+----------+---------
 locktype          | text                     |           |          |
 waits             | bigint                   |           |          |
 wait_time         | double precision         |           |          |
 fastpath_exceeded | bigint                   |           |          |
 stats_reset       | timestamp with time zone |           |          |

The view contains the following columns:

locktype: Type of lockable object, such as a relation or transaction ID
waits: Number of qualifying lock waits
wait_time: Total time spent waiting for locks, in milliseconds
fastpath_exceeded: Number of times a lock could not be acquired through the fast path because the available slots were exceeded
stats_reset: Time when the statistics were last reset

Initially, all counters are zero in my test environment. The view returns 12 rows, one for each lock type.

postgres=# SELECT * FROM pg_stat_lock;
     locktype     | waits | wait_time | fastpath_exceeded |          stats_reset
------------------+-------+-----------+-------------------+-------------------------------
 relation         |     0 |         0 |                 0 | 2026-10-26 08:56:30.602553+03
 extend           |     0 |         0 |                 0 | 2026-10-26 08:56:30.602553+03
 frozenid         |     0 |         0 |                 0 | 2026-10-26 08:56:30.602553+03
 page             |     0 |         0 |                 0 | 2026-10-26 08:56:30.602553+03
 tuple            |     0 |         0 |                 0 | 2026-10-26 08:56:30.602553+03
 transactionid    |     0 |         0 |                 0 | 2026-10-26 08:56:30.602553+03
 virtualxid       |     0 |         0 |                 0 | 2026-10-26 08:56:30.602553+03
 spectoken        |     0 |         0 |                 0 | 2026-10-26 08:56:30.602553+03
 object           |     0 |         0 |                 0 | 2026-10-26 08:56:30.602553+03
 userlock         |     0 |         0 |                 0 | 2026-10-26 08:56:30.602553+03
 advisory         |     0 |         0 |                 0 | 2026-10-26 08:56:30.602553+03
 applytransaction |     0 |         0 |                 0 | 2026-10-26 08:56:30.602553+03
(12 rows)

An important detail is that waits and wait_time only include waits that exceed deadlock_timeout and end with successful lock acquisition. In my environment, the configured threshold is one second:

vahiddb=# SHOW deadlock_timeout;
 deadlock_timeout
------------------
 1s
(1 row)

Investigating Lock Waits

In this scenario, I will demonstrate how pg_stat_lock records lock waits caused by concurrent transactions.

Concurrent Updates
I will use an existing table named tbl_counter. The table contains a counter row that both sessions attempt to update.

Before starting the experiment, I reset the lock statistics:

vahiddb=# SELECT pg_stat_reset_shared('lock');
 pg_stat_reset_shared
----------------------
(1 row)

Session 1
Start a transaction and update the counter:

--session 1:
vahiddb=# begin;
update tbl_counter set counter=counter+1;
BEGIN
UPDATE 1

The update completes, but the transaction remains open. PostgreSQL retains the relevant locks until the transaction ends.

Session 2
While Session 1 remains open, execute the same update:

--session 2:
vahiddb=# update tbl_counter set counter=counter+1;

The second update waits because Session 1 has modified the same row and has not yet committed.

At this point, querying pg_stat_lock shows that the counters have not increased for this wait:

vahiddb=# SELECT * FROM pg_stat_lock;
     locktype     | waits | wait_time | fastpath_exceeded |          stats_reset
------------------+-------+-----------+-------------------+-------------------------------
 relation         |     0 |         0 |                 0 | 2026-10-26 08:56:30.602553+03
 extend           |     0 |         0 |                 0 | 2026-10-26 08:56:30.602553+03
 frozenid         |     0 |         0 |                 0 | 2026-10-26 08:56:30.602553+03
 page             |     0 |         0 |                 0 | 2026-10-26 08:56:30.602553+03
 tuple            |     0 |         0 |                 0 | 2026-10-26 08:56:30.602553+03
 transactionid    |     0 |         0 |                 0 | 2026-10-26 08:56:30.602553+03
 virtualxid       |     0 |         0 |                 0 | 2026-10-26 08:56:30.602553+03
 spectoken        |     0 |         0 |                 0 | 2026-10-26 08:56:30.602553+03
 object           |     0 |         0 |                 0 | 2026-10-26 08:56:30.602553+03
 userlock         |     0 |         0 |                 0 | 2026-10-26 08:56:30.602553+03
 advisory         |     0 |         0 |                 0 | 2026-10-26 08:56:30.602553+03
 applytransaction |     0 |         0 |                 0 | 2026-10-26 08:56:30.602553+03
(12 rows)

The waits and wait_time values remain zero in the displayed statistics.

Why?

The second transaction is still waiting. PostgreSQL records this qualifying wait in the cumulative statistics when the lock is successfully acquired after the wait ends, rather than incrementing the counters continuously while the transaction is blocked.

Session 1
Commit the first transaction:

vahiddb=*# commit;
COMMIT

Session 2 can now proceed and complete its update.
Query the statistics again:

vahiddb=# SELECT * FROM pg_stat_lock;
     locktype     | waits | wait_time  | fastpath_exceeded |          stats_reset
------------------+-------+------------+-------------------+-------------------------------
 relation         |     0 |          0 |                 0 | 2026-10-26 08:56:30.602553+03
 extend           |     0 |          0 |                 0 | 2026-10-26 08:56:30.602553+03
 frozenid         |     0 |          0 |                 0 | 2026-10-26 08:56:30.602553+03
 page             |     0 |          0 |                 0 | 2026-10-26 08:56:30.602553+03
 tuple            |     0 |          0 |                 0 | 2026-10-26 08:56:30.602553+03
 transactionid    |     1 | 332098.097 |                 0 | 2026-10-26 08:56:30.602553+03
 virtualxid       |     0 |          0 |                 0 | 2026-10-26 08:56:30.602553+03
 spectoken        |     0 |          0 |                 0 | 2026-10-26 08:56:30.602553+03
 object           |     0 |          0 |                 0 | 2026-10-26 08:56:30.602553+03
 userlock         |     0 |          0 |                 0 | 2026-10-26 08:56:30.602553+03
 advisory         |     0 |          0 |                 0 | 2026-10-26 08:56:30.602553+03
 applytransaction |     0 |          0 |                 0 | 2026-10-26 08:56:30.602553+03
(12 rows)

The recorded wait_time is approximately 332 seconds.

Table-Level Lock Contention
Now let's generate a wait involving a relation lock.

First, reset the statistics again:

vahiddb=# SELECT pg_stat_reset_shared('lock');
 pg_stat_reset_shared
----------------------
(1 row)

Session 1
Acquire an exclusive table lock and keep the transaction open:

--session 1:
vahiddb=# BEGIN;
LOCK TABLE tbl_counter IN ACCESS EXCLUSIVE MODE;
BEGIN
LOCK TABLE

The ACCESS EXCLUSIVE lock conflicts with the ACCESS SHARE lock required by a normal SELECT.

Session 2
While Session 1 holds the lock, execute:

--session 2:
vahiddb=# select * from tbl_counter;

The query waits until Session 1 releases its lock.

Session 1
Commit the transaction:

vahiddb=*# commit;
COMMIT

After Session 2 completes, query the statistics:

vahiddb=# SELECT * FROM pg_stat_lock;
     locktype     | waits | wait_time | fastpath_exceeded |          stats_reset
------------------+-------+-----------+-------------------+-------------------------------
 relation         |     1 | 63338.103 |                 0 | 2026-10-26 09:34:16.935286+03
 extend           |     0 |         0 |                 0 | 2026-10-26 09:34:16.935286+03
 frozenid         |     0 |         0 |                 0 | 2026-10-26 09:34:16.935286+03
 page             |     0 |         0 |                 0 | 2026-10-26 09:34:16.935286+03
 tuple            |     0 |         0 |                 0 | 2026-10-26 09:34:16.935286+03
 transactionid    |     0 |         0 |                 0 | 2026-10-26 09:34:16.935286+03
 virtualxid       |     0 |         0 |                 0 | 2026-10-26 09:34:16.935286+03
 spectoken        |     0 |         0 |                 0 | 2026-10-26 09:34:16.935286+03
 object           |     0 |         0 |                 0 | 2026-10-26 09:34:16.935286+03
 userlock         |     0 |         0 |                 0 | 2026-10-26 09:34:16.935286+03
 advisory         |     0 |         0 |                 0 | 2026-10-26 09:34:16.935286+03
 applytransaction |     0 |         0 |                 0 | 2026-10-26 09:34:16.935286+03
(12 rows)

The relation row records one qualifying wait, with approximately 63.3 seconds of accumulated wait time.

This example differs from the concurrent-update scenario. The ACCESS EXCLUSIVE lock directly prevents another session from acquiring the relation lock required by its SELECT statement. Consequently, the recorded wait appears under relation.

The wait duration depends on the actual time the second session spends waiting. PostgreSQL only includes waits exceeding deadlock_timeout that subsequently end in successful acquisition.

Detecting Fast-Path Lock Overflow

PostgreSQL uses a fast-path mechanism to acquire certain relation locks efficiently. When the available fast-path slots are insufficient for a backend's relation-lock requirements, additional lock acquisitions can use the regular shared lock table instead.

The fastpath_exceeded counter helps identify this activity. Unlike waits and wait_time, it does not depend on a lock wait exceeding deadlock_timeout.
It can also help identify workloads that access many relations and may require tuning max_locks_per_transaction, which determines the initial lock-table capacity based on the expected number of locks per transaction.

vahiddb=# SHOW max_locks_per_transaction;
 max_locks_per_transaction
---------------------------
 128
(1 row)

To generate a workload that accesses many relations, I created a range-partitioned table with 5,000 partitions.

vahiddb=# CREATE TABLE tbl_parts (
    id integer
) PARTITION BY RANGE (id);
CREATE TABLE

Next, I created the partitions using a DO block:

vahiddb=# DO $$
BEGIN
    FOR i IN 1..5000 LOOP
        EXECUTE format(
            'CREATE TABLE tbl_parts_%s
             PARTITION OF tbl_parts
             FOR VALUES FROM (%s) TO (%s)',
            i,
            (i - 1) * 1000,
            i * 1000
        );
    END LOOP;
END $$;

DO
Time: 68588.738 ms (01:08.589)

The operation completed successfully and took approximately 68.6 seconds in my environment.

The table is empty, but its partition structure provides a useful test case because a query against the parent can require locks on many relations.

Before executing the test query, I reset the lock statistics:

vahiddb=#  SELECT pg_stat_reset_shared('lock');
 pg_stat_reset_shared
----------------------

(1 row)

At this stage, the fastpath_exceeded counters were zero.

Resetting the statistics makes it easier to attribute subsequent counter changes to the experiment. This function resets cluster-wide lock statistics, so it should be used carefully on shared or production servers.
Now, execute a simple aggregate query:

vahiddb=# SELECT count(*) FROM tbl_parts;
 count
-------
     0
(1 row)

Although the table contains no rows, PostgreSQL still needs to process the partitioned relation and its partitions. The query therefore provides a way to investigate relation-lock activity without inserting thousands of rows.

Next, query pg_stat_lock again:

vahiddb=# SELECT * FROM pg_stat_lock;
     locktype     | waits | wait_time | fastpath_exceeded |          stats_reset
------------------+-------+-----------+-------------------+-------------------------------
 relation         |     0 |         0 |             92796 | 2026-10-26 10:13:47.582012+03
 extend           |     0 |         0 |                 0 | 2026-10-26 10:13:47.582012+03
 frozenid         |     0 |         0 |                 0 | 2026-10-26 10:13:47.582012+03
 page             |     0 |         0 |                 0 | 2026-10-26 10:13:47.582012+03
 tuple            |     0 |         0 |                 0 | 2026-10-26 10:13:47.582012+03
 transactionid    |     0 |         0 |                 0 | 2026-10-26 10:13:47.582012+03
 virtualxid       |     0 |         0 |                 0 | 2026-10-26 10:13:47.582012+03
 spectoken        |     0 |         0 |                 0 | 2026-10-26 10:13:47.582012+03
 object           |     0 |         0 |                 0 | 2026-10-26 10:13:47.582012+03
 userlock         |     0 |         0 |                 0 | 2026-10-26 10:13:47.582012+03
 advisory         |     0 |         0 |                 0 | 2026-10-26 10:13:47.582012+03
 applytransaction |     0 |         0 |                 0 | 2026-10-26 10:13:47.582012+03
(12 rows)

The fastpath_exceeded counter increased to 92,796.

This result shows that the workload generated many relation-lock acquisitions that could not use the fast path because its available slots had been exceeded.

Conclusion

The pg_stat_lock view in PostgreSQL 19 provides a useful way to investigate lock activity through cumulative statistics.

Oracle AI Database 26ai(23.26.4): Mixing SELECT * with Other Columns and Expressions

Vahid Yousefzadeh — Mon, 26 Oct 2026 08:03:42 +0000

Oracle AI Database 26ai(26.23.4) introduces a useful SQL enhancement that allows the * wildcard to be combined with explicit column names, constants, and SQL expressions in the same SELECT list.

Before the 23.26.4 release, Oracle did not allow * to be used alongside other select-list items in this way:

Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free
Version 23.26.3.0.0

SQL> select id,*,fname from person;
select id,*,fname from person
          *
ERROR at line 1:
ORA-00936: missing expression
Help: https://docs.oracle.com/error-help/db/ora-00936/
The asterisk cannot be combined with explicit column names in this statement, so the query fails with ORA-00936.

Now, the same query executes successfully on Oracle AI Database 23.26.4:

Oracle AI Database 26ai Enterprise Edition Release 23.26.4.0.0 - Production
Version 23.26.4.0.0

SQL> select id,*,fname from person;

 ID  ID FNAME      LNAME                  SAL FNAME
--- --- ---------- --------------- ---------- ----------
  1   1 Vahid      Yousefzadeh              5 Vahid
  2   2 Nader      DastNeshan               6 Nader
  3   3 Kourosh    Barmak                   6 Kourosh

Oracle expands * into all columns from the PERSON table at its position in the select list. As a result, ID and FNAME appear twice: once through the explicit column references and once through the wildcard expansion.

Additional Examples:

1.Using Multiple Wildcards

SQL> select *,* from person;

 ID FNAME      LNAME            SAL  ID FNAME      LNAME           SAL
--- ---------- ---------------  --- --- ---------- --------------- ---
  1 Vahid      Yousefzadeh        5   1 Vahid      Yousefzadeh       5
  2 Nader      DastNeshan         6   2 Nader      DastNeshan        6
  3 Kourosh    Barmak             6   3 Kourosh    Barmak            6

Each occurrence of * expands independently, returning the complete set of table columns twice.

2. Combining * with SQL Functions

SQL> select *,upper(fname) ,lower(fname) from person;

        ID FNAME      LNAME                  SAL UPPER(FNAM LOWER(FNAM
---------- ---------- --------------- ---------- ---------- ----------
         1 Vahid      Yousefzadeh              5 VAHID      vahid
         2 Nader      DastNeshan               6 NADER      nader
         3 Kourosh    Barmak                   6 KOUROSH    kourosh

This makes it possible to retrieve the original table data and additional calculated values in a single query without explicitly listing every column.

3. Combining Constants with *

SQL> select 'test','test' as test ,* from person;

'TES TEST         ID FNAME      LNAME                  SAL
---- ---- ---------- ---------- --------------- ----------
test test          1 Vahid      Yousefzadeh              5
test test          2 Nader      DastNeshan               6
test test          3 Kourosh    Barmak                   6

In this example, Oracle returns two constant expressions followed by all columns from PERSON. The constant values are repeated for every row in the result set.

23% Faster Autovacuum with Parallel Workers in PostgreSQL 19

Vahid Yousefzadeh — Mon, 19 Oct 2026 07:31:39 +0000

Before PostgreSQL 19, autovacuum processed indexes without using parallel vacuum workers, even when a table had multiple large indexes. Although PostgreSQL already supported parallel vacuuming for manually executed VACUUM commands, this capability was not available to autovacuum.

For example, manual vacuuming can use parallel workers:

vahiddb=# VACUUM (PARALLEL 10) person;
VACUUM

PostgreSQL 19 introduces Parallel Autovacuum, allowing autovacuum to use parallel workers during index vacuuming and index cleanup.

This feature is particularly useful for tables with large indexes and substantial numbers of dead tuples. However, it does not parallelize the table’s heap-scanning phase. Consequently, tables without indexes cannot benefit from this feature, and tables with large indexes are promising candidates.

In this article, I will demonstrate the feature using a table containing five million rows and four indexes. I will compare the execution times of autovacuum with parallel workers disabled and enabled.

1. Inspecting the test table

My test environment contains a table named person in the vahiddb database.

First, I checked the number of rows:

vahiddb=# select count(*) from person;
  count
---------
 5000000
(1 row)

The table contains five million rows and has four indexes.

I used the following command to check their sizes:

vahiddb=# \dt+
                                     List of tables
 Schema |  Name  | Type  |  Owner   | Persistence | Access method |  Size  | Description
--------+--------+-------+----------+-------------+---------------+--------+-------------
 public | person | table | postgres | permanent   | heap          | 403 MB |
(1 row)

vahiddb=# \di+
                                               List of indexes
 Schema |        Name        | Type  |  Owner   | Table  | Persistence | Access method |  Size  | Description
--------+--------------------+-------+----------+--------+-------------+---------------+--------+-------------
 public | person_flname_idx  | index | postgres | person | permanent   | btree         | 41 MB  |
 public | person_full_idx    | index | postgres | person | permanent   | btree         | 366 MB |
 public | person_lfaname_idx | index | postgres | person | permanent   | btree         | 324 MB |
 public | person_pk          | index | postgres | person | permanent   | btree         | 107 MB |
(4 rows)

The four indexes occupy approximately 838 MB in total, which is more than twice the size of the table.

This workload is a good candidate for testing parallel autovacuum because index processing represents a substantial part of the maintenance work.

2. Configuring autovacuum logging

To measure the execution time, I enabled detailed autovacuum logging.

I set log_autovacuum_min_duration to 0 so that every autovacuum operation would be logged:

vahiddb=# ALTER SYSTEM SET log_autovacuum_min_duration = 0;
ALTER SYSTEM
vahiddb=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

3. Measuring autovacuum without parallel workers

In PostgreSQL 19, the autovacuum_max_parallel_workers parameter controls the maximum number of parallel workers that a single autovacuum worker can use.

Setting it to zero disables parallel vacuuming during autovacuum.

vahiddb=# ALTER SYSTEM SET autovacuum_max_parallel_workers = 0;
ALTER SYSTEM

vahiddb=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

Next, I updated three million rows to generate dead tuples and trigger autovacuum:

vahiddb=# UPDATE person
SET
    fname = 'Vahid' || fname,
    lname = 'Yousefzadeh' || lname
        where id<=3000000;

UPDATE 3000000

After autovacuum completed, I examined the server log. The following excerpt contains the most relevant results:

2026-10-18 19:45:55.201 +03 [2323] LOG:  automatic vacuum of table "vahiddb.public.person": index scans: 1
        pages: 0 removed, 88581 remain, 67964 scanned (76.73% of total), 0 eagerly scanned
        tuples: 3000000 removed, 5000070 remain, 0 are dead but not yet removable
        removable cutoff: 771, which was 0 XIDs old when operation ended
        frozen: 37038 pages from table (41.81% of total) had 3000058 tuples frozen
        visibility map: 67964 pages set all-visible, 67964 pages set all-frozen (0 were all-visible)
        index scan needed: 30927 pages from table (34.91% of total) had 3000000 dead item identifiers removed
        index "person_pk": pages: 21938 in total, 0 newly deleted, 0 currently deleted, 0 reusable
        index "person_flname_idx": pages: 11218 in total, 0 newly deleted, 0 currently deleted, 0 reusable
        index "person_lfaname_idx": pages: 72221 in total, 0 newly deleted, 0 currently deleted, 0 reusable
        index "person_full_idx": pages: 103264 in total, 0 newly deleted, 0 currently deleted, 0 reusable
        avg read rate: 16.980 MB/s, avg write rate: 14.303 MB/s
        buffer usage: 84022 hits, 291632 reads, 245655 dirtied
        WAL usage: 313620 records, 245655 full page images, 1048438885 bytes, 1018664200 full page image bytes, 98 buffers full
        memory usage: dead item storage 2.33 MB accumulated across 1 reset (limit 64.00 MB each)
        system usage: CPU: user: 6.97 s, system: 1.77 s, elapsed: 134.18 s

The most important measurement is the elapsed time reported by PostgreSQL:

elapsed: 134.18 s

This is our baseline for comparison.

4. Enabling Parallel Autovacuum

Now I enabled parallel autovacuum by setting the maximum number of parallel workers to four:

vahiddb=# ALTER SYSTEM SET autovacuum_max_parallel_workers = 4;
ALTER SYSTEM

vahiddb=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

A configuration reload is sufficient for this setting; a server restart is not required.

PostgreSQL 19 also supports a table-level setting named . This parameter allows us to configure the maximum number of parallel workers for an individual table.

For the person table, I used:

vahiddb=# ALTER TABLE person SET (
    autovacuum_enabled = true,
    autovacuum_parallel_workers = 4
);

ALTER TABLE

The effective number of workers is subject to the configured limits and available worker capacity. Setting a value of four does not guarantee that four workers will always be launched.

Resource considerations
Parallel autovacuum should be configured carefully on production systems.

The relevant settings include:

· autovacuum_max_parallel_workers: maximum parallel workers per autovacuum operation.

· autovacuum_parallel_workers: table-level parallel-worker limit.

· max_parallel_workers: overall limit for parallel workers.

· autovacuum_work_mem: memory available to each autovacuum worker, subject to its configuration and applicable limits.

Increasing parallelism can increase CPU and I/O demand. Although parallel vacuum operations apply maintenance memory limits differently from parallel queries, administrators should still account for concurrent maintenance operations and the memory requirements of the cluster.

For this test, I used four as the configured maximum and allowed PostgreSQL to determine the actual worker count.

5. Repeating the test with Parallel Autovacuum

I repeated the same update against the person table:

vahiddb=# UPDATE person
SET
    fname = 'Vahid' || fname,
    lname = 'Yousefzadeh' || lname
        where id<=3000000;

  UPDATE 3000000

The resulting autovacuum log included the following information:

2026-10-18 19:33:49.737 +03 [2273] LOG:  automatic vacuum of table "vahiddb.public.person": index scans: 1
        pages: 0 removed, 88581 remain, 67964 scanned (76.73% of total), 0 eagerly scanned
        tuples: 3000000 removed, 5000046 remain, 0 are dead but not yet removable
        removable cutoff: 762, which was 0 XIDs old when operation ended
        new relfrozenxid: 762, which is 6 XIDs ahead of previous value
        frozen: 37037 pages from table (41.81% of total) had 3000000 tuples frozen
        visibility map: 67964 pages set all-visible, 67964 pages set all-frozen (0 were all-visible)
        index scan needed: 30927 pages from table (34.91% of total) had 3000000 dead item identifiers removed
        parallel workers: index vacuum: 3 planned, 3 launched in total
        index "person_pk": pages: 21938 in total, 0 newly deleted, 0 currently deleted, 0 reusable
        index "person_flname_idx": pages: 11218 in total, 0 newly deleted, 0 currently deleted, 0 reusable
        index "person_lfaname_idx": pages: 72221 in total, 0 newly deleted, 0 currently deleted, 0 reusable
        index "person_full_idx": pages: 103264 in total, 0 newly deleted, 0 currently deleted, 0 reusable
        avg read rate: 22.357 MB/s, avg write rate: 18.781 MB/s
        buffer usage: 83274 hits, 292440 reads, 245655 dirtied
        WAL usage: 313620 records, 245655 full page images, 1048438901 bytes, 1018664348 full page image bytes, 139 buffers full
        memory usage: dead item storage 3.00 MB accumulated across 1 reset (limit 64.00 MB each)
        system usage: CPU: user: 4.50 s, system: 0.93 s, elapsed: 102.18 s

This is the line that explicitly confirms parallel vacuum execution:

parallel workers: index vacuum: 3 planned, 3 launched in total
PostgreSQL planned three parallel index-vacuum workers and successfully launched all three. The elapsed time was:

elapsed: 102.18 s

That represents an approximately 23.9% reduction in elapsed time in this particular experiment.

The key takeaway is that Parallel Autovacuum can improve maintenance performance for tables with large indexes, but the actual benefit depends on the workload, index sizes, available resources.

Calculating WAL Generation for DML Operations in PostgreSQL

Vahid Yousefzadeh — Mon, 12 Oct 2026 17:13:34 +0000

PostgreSQL provides the EXPLAIN command with the ANALYZE and WAL options to measure the WAL generated by a DML operation. This feature helps database administrators investigate the impact of INSERT, UPDATE, and DELETE statements on WAL generation.

In this article, we use EXPLAIN (ANALYZE, WAL) to measure the WAL generated when updating 10 million rows, compare the results with and without an index, and investigate the effect of enabling WAL compression.

1. Test environment

The test uses a person table containing 10 million rows. The table occupies approximately 880 MB, and WAL compression is initially disabled.

vahiddb=# \dt+
                                     List of tables
 Schema |  Name  | Type  |  Owner   | Persistence | Access method |  Size  | Description
--------+--------+-------+----------+-------------+---------------+--------+-------------
 public | person | table | postgres | permanent   | heap          | 880 MB |
(1 row)

vahiddb=# show wal_compression;
 wal_compression
-----------------
 off
(1 row)

2. Calculating WAL generated by an UPDATE

To measure WAL generation, execute the UPDATE statement with the following options:

vahiddb=# EXPLAIN (ANALYZE, WAL, COSTS OFF, TIMING OFF)
UPDATE person
SET name = 'Vahid',
    last_name = 'Yousefzadeh';

                                          QUERY PLAN
----------------------------------------------------------------------------------------------
 Update on person (actual rows=0.00 loops=1)
   Buffers: shared hit=72295238 read=319243 dirtied=315766 written=273030
   WAL: records=30258061 fpi=212178 bytes=4014818468 fpi bytes=1551338708 buffers full=144689
   ->  Seq Scan on person (actual rows=10000000.00 loops=1)
         Buffers: shared hit=298 read=205744 dirtied=72743 written=109115
         WAL: records=145390 fpi=72743 bytes=610946323 fpi bytes=590673276 buffers full=65677
 Planning Time: 0.116 ms
 Execution Time: 160129.955 ms
(8 rows)

The statement updates all 10 million rows. The WAL section of the execution plan reports the number of WAL records, full-page images (FPIs), and total WAL bytes generated during execution.

WAL: records=30258061 fpi=212178 bytes=4014818468 fpi bytes=1551338708 buffers full=144689

The reported WAL volume is approximately 4.01 GB.

3. Measuring the effect of indexes

Next, create a composite index on the columns being updated:

vahiddb=#  create index indname_lname on person(name,last_name);
CREATE INDEX

Execute the same UPDATE statement with WAL compression disabled:

vahiddb=#  EXPLAIN (ANALYZE, WAL, COSTS OFF, TIMING OFF)
UPDATE person
SET name = 'Vahid',
    last_name = 'Yousefzadeh';

                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Update on person (actual rows=0.00 loops=1)
   Buffers: shared hit=104133558 read=452220 dirtied=265562 written=95009
   WAL: records=40487705 fpi=151040 bytes=4310991885 fpi bytes=986566516 buffers full=126317
   ->  Seq Scan on person (actual rows=10000000.00 loops=1)
         Buffers: shared read=278835 written=27966
 Planning:
   Buffers: shared hit=18 read=3
 Planning Time: 0.504 ms
 Execution Time: 262993.363 ms
(9 rows)

The reported WAL volume increases from approximately 4.01 GB to 4.31 GB, while execution time increases from approximately 160 seconds to 263 seconds in the supplied runs:

WAL: records=40487705 fpi=151040 bytes=4310991885 fpi bytes=986566516 buffers full=126317

4. Measuring the effect of WAL compression

Enable WAL compression and repeat the test:

vahiddb=# set wal_compression=on;
SET

The non-indexed test reports approximately 2.65 GB of WAL, compared with 4.01 GB when compression is disabled. This represents an approximately 33.94% reduction in reported WAL volume:

vahiddb=# EXPLAIN (ANALYZE, WAL, COSTS OFF, TIMING OFF)
UPDATE person
SET name = 'Vahid',
    last_name = 'Yousefzadeh';

                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Update on person (actual rows=0.00 loops=1)
   Buffers: shared hit=73576559 read=479691 dirtied=272363 written=192157
   WAL: records=30146290 fpi=170676 bytes=2652681749 fpi bytes=211050036 buffers full=30525
   ->  Seq Scan on person (actual rows=10000000.00 loops=1)
         Buffers: shared hit=408 read=278428 dirtied=22268 written=76164
         WAL: records=45527 fpi=22268 bytes=33702425 fpi bytes=26333321 buffers full=2863
 Planning:
   Buffers: shared hit=11
 Planning Time: 0.112 ms
 Execution Time: 154784.519 ms
(10 rows)

WAL compression primarily compresses full-page images rather than all WAL records. Consequently, its effectiveness depends partly on how much of the generated WAL consists of compressible full-page images.

Prevent Accidental Duplicate Rows with JOIN TO ONE in Oracle AI Database 26ai

Vahid Yousefzadeh — Mon, 12 Oct 2026 06:57:54 +0000

Oracle AI Database 23.26.2 introduced the JOIN TO ONE syntax to address a common problem with SQL joins: a query can unexpectedly return multiple rows for a row in the main table.

This is especially useful when a query is designed to widen a table without increasing its number of rows. With JOIN TO ONE, Oracle raises an error when a subject row matches multiple rows in a joined table instead of silently returning the multiplied rows.

In this article, we will use three tables:

CUSTOMERS
    1
    |
    M
 ACCOUNTS
    1
    |
    M
TRANSACTIONS

The relationships are:

One customer can have multiple accounts.
Each account belongs to one customer.
One account can have multiple transactions.
Each transaction belongs to one account. For example:

Vahid Yousefzadeh
        |
        +-- A1001
              |
              +-- Transaction 1
              +-- Transaction 2
              +-- Transaction 3

The Normal JOIN

Suppose we want to display each account together with its customer:

SELECT a.account_number, c.customer_name
  FROM accounts a
  JOIN customers c
    ON c.customer_id = a.customer_id;

ACCOUNT_NUMBER       CUSTOMER_NAME
-------------------- ------------------------------
A1001                Vahid Yousefzadeh
A1002                Ebne moljem moradi
A1003                Payan Rafat

There is one row for each account. This works because every account belongs to exactly one customer.

Now suppose we want to show the same customer information only for accounts that have transactions:

SELECT a.account_number, c.customer_name
  FROM accounts a
  JOIN customers c
    ON c.customer_id = a.customer_id
  JOIN transactions t
    ON t.account_id = a.account_id;

ACCOUNT_NUMBER       CUSTOMER_NAME
-------------------- ------------------------------
A1001                Vahid Yousefzadeh
A1001                Vahid Yousefzadeh
A1001                Vahid Yousefzadeh
A1002                Ebne moljem moradi
A1002                Ebne moljem moradi
A1003                Payan Rafat
6 rows selected.

As you can see, some rows are duplicated. The main reason is that A1001 has three transactions:

A1001
 |
 +-- Transaction 1
 +-- Transaction 2
 +-- Transaction 3

Therefore, the normal join returns the account once for each matching transaction.

This is normal behavior for a one-to-many join. The problem is that our query may have been intended to return one row per account.

Traditional joins do not automatically identify this as a problem.

Using JOIN TO ONE

Oracle AI Database 26ai provides JOIN TO ONE for exactly this type of requirement.

We can write:

SQL> SELECT a.account_number, c.customer_name
  FROM accounts a
  JOIN TO ONE(customers c, transactions t
    ON t.account_id = a.account_id);  

ERROR at line 3:
ORA-18640: JOIN TO ONE reached multiple rows joining to "T", resulting in a non-unique join
Help: https://docs.oracle.com/error-help/db/ora-18640/

We are saying that the rows from ACCOUNTS should remain one row per account after the join.

However, A1001 matches three rows in TRANSACTIONS.

Therefore Oracle raises an error instead of silently returning:

A1001  Vahid Yousefzadeh
A1001  Vahid Yousefzadeh
A1001  Vahid Yousefzadeh

This tells us that the JOIN TO ONE requirement has been violated.

The important point is that JOIN TO ONE does not mean an account cannot have multiple transactions. The ACCOUNTS → TRANSACTIONS relationship is still one-to-many. It means that when ACCOUNTS is the subject table, each account is expected to remain a single row in the result.

What If TRANSACTIONS Is Only a Filter?

Now consider a different requirement:

Show accounts that have at least one transaction. We don’t actually need any columns from TRANSACTIONS. Using a normal join would unnecessarily multiply the account rows.

Instead, we can use EXISTS:

SQL> SELECT a.account_number, c.customer_name
  FROM accounts a
  JOIN customers c
    ON c.customer_id = a.customer_id
 WHERE EXISTS
 (SELECT 1 FROM transactions t WHERE t.account_id = a.account_id);

ACCOUNT_NUMBER       CUSTOMER_NAME
-------------------- ------------------------------
A1001                Vahid Yousefzadeh
A1002                Ebne moljem moradi
A1003                Payan Rafat

It doesn’t matter that A1001 has three transactions. We only want to know whether a transaction exists.

When a many-side table is being used only to filter the subject table, a semijoin such as EXISTS is an appropriate approach.

What If We Actually Need Transaction Data?

Suppose our requirement changes. Now we want: Display every transaction together with its account.

In this case, TRANSACTIONS should become the subject table.

SQL> SELECT t.transaction_id,
       t.amount,
       a.account_number
FROM transactions t
JOIN TO ONE (
    accounts a
);

TRANSACTION_ID     AMOUNT ACCOUNT_NUMBER
-------------- ---------- --------------------
             1        500 A1001
             2        250 A1001
             3        100 A1001
             4        800 A1002
             5        150 A1002
             6       1150 A1003
6 rows selected.

Each transaction belongs to one account.

Therefore, each transaction can be widened with account information without multiplying the transaction row.

The order of the tables in JOIN TO ONE is important because Oracle processes the joins from the subject table from left to right.

How Does Oracle Find the Join Condition?

In our example, we created these foreign keys:

CONSTRAINT accounts_customer_fk
    FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
and:

CONSTRAINT transactions_account_fk
    FOREIGN KEY (account_id)
    REFERENCES accounts(account_id)

Oracle can use foreign-key relationships to infer the join conditions for JOIN TO ONE.

We can also specify the join condition explicitly:

SQL> SELECT a.account_number, c.customer_name
  FROM accounts a
  JOIN TO ONE(customers c
    ON c.customer_id = a.customer_id);

ACCOUNT_NUMBER       CUSTOMER_NAME
-------------------- ------------------------------
A1001                Vahid Yousefzadeh
A1002                Ebne moljem moradi
A1003                Payan Rafat

Using an explicit ON condition can make the intended relationship clearer, particularly when there are multiple foreign keys between tables or the schema changes later.

Verifying PostgreSQL Backups Using pg_verifybackup

Vahid Yousefzadeh — Sat, 10 Oct 2026 10:27:09 +0000

Pg_verifybackup was introduced in PostgreSQL 13 and is used to check the integrity of a base backup taken using Pg_basebackup against the backup_manifest file generated by the server.

The manifest is a JSON document containing information about the backup files, including their paths, sizes, last modification times, and, optionally, checksums. For example:

{ "PostgreSQL-Backup-Manifest-Version": 2,
"System-Identifier": 7694938432524117566,
"Files": [
{ "Path": "backup_label", "Size": 227, "Last-Modified": "2026-10-10 07:42:44 GMT", "Checksum-Algorithm": "CRC32C", "Checksum": "509e2e33" },
{ "Path": "global/1262", "Size": 8192, "Last-Modified": "2026-10-10 07:35:34 GMT", "Checksum-Algorithm": "CRC32C", "Checksum": "d7e25e67" },
{ "Path": "global/2964", "Size": 0, "Last-Modified": "2026-10-10 07:31:08 GMT", "Checksum-Algorithm": "CRC32C", "Checksum": "00000000" },
{ "Path": "global/1213", "Size": 8192, "Last-Modified": "2026-10-10 07:31:09 GMT", "Checksum-Algorithm": "CRC32C", "Checksum": "332b6c66" },
{ "Path": "global/1260", "Size": 8192, "Last-Modified": "2026-10-10 07:31:09 GMT", "Checksum-Algorithm": "CRC32C", "Checksum": "bd0707e0" },

pg_verifybackup uses the information in this file to verify the backup. However, this tool cannot guarantee that the database can be restored successfully. It checks specific aspects of the backup’s integrity and consistency.

Creating and Verifying a Backup

First, let’s create a backup using pg_basebackup:

[postgres@OEL98-PG ~]$ pg_basebackup -D /backup/ -v

pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/4A000118 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_2003"
pg_basebackup: write-ahead log end point: 0/4A000328
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed

Now, let’s verify the backup:

[postgres@OEL98-PG ~]$ pg_verifybackup /backup/ -P
740739/740739 kB (100%) verified
backup successfully verified

The output indicates that the verification completed successfully.

Identifying Missing and Extra Files

If we rename a file in the backup, pg_verifybackup will identify the changes. For example:

[postgres@OEL98-PG ~]$ mv /backup/global/1213 /backup/global/1213-old

Now, execute the command again:

[postgres@OEL98-PG ~]$ pg_verifybackup /backup/ -P
pg_verifybackup: error: "global/1213-old" is present on disk but not in the manifest
pg_verifybackup: error: "global/1213" is present in the manifest but not on disk
740731/740731 kB (100%) verified

Two errors occurred. The first indicates that an extra file (1213-old) is present on disk but not in the manifest, while the second indicates that an expected file (1213) is missing.

Although the progress reaches 100%, the verification has failed because errors were detected.

Now, let’s restore the original filename:

[postgres@OEL98-PG ~]$ mv /backup/global/1213-old /backup/global/1213
Identifying Modified Files

In the next step, I want to modify a file and execute this tool again.

First, let’s check the file’s details:

[postgres@OEL98-PG 16384]$ ll /backup/base/16384/3456
-rw-------. 1 postgres postgres 122880 Oct 10 10:42 /backup/base/16384/3456

Now, modify the file’s contents:

[postgres@OEL98-PG ~]$ printf 'curropt' |dd of=/backup/base/16384/3456 bs=1 seek=100 conv=notrunc status=none

Execute the tool again:

[postgres@OEL98-PG ~]$ pg_verifybackup /backup/ -P
pg_verifybackup: error: checksum mismatch for file "base/16384/3456"
740739/740739 kB (100%) verified

The checksum verification failed, identifying a file whose contents differ from those recorded in the manifest. This indicates that the file has been modified, although it does not necessarily mean that accidental corruption occurred.
However, we can skip data-file checksum verification using — skip-checksums:

[postgres@OEL98-PG ~]$ pg_verifybackup /backup/ -P --skip-checksums
backup successfully verified

Note that skipping checksums does not repair the modified file. It disables data-file checksum verification, so this successful result does not mean that the backup is intact. Other checks, such as file presence, file sizes, and WAL verification, are still performed unless separately disabled.

WAL Verification

One of the more complex features of pg_verifybackup is WAL verification. This tool verifies the required WAL records using the WAL-Ranges section in the manifest file. For example:

"WAL-Ranges": [
{ "Timeline": 1, "Start-LSN": "0/4A000118", "End-LSN": "0/4A000328" }
],

Each range contains three important fields: Timeline, Start-LSN, and End-LSN.
PostgreSQL’s pg_verifybackup invokes the companion utility pg_waldump to parse the required WAL records. It passes the timeline and the start and end LSNs obtained from the manifest, using quiet mode so that only errors are reported.
A successful WAL check does not prove that every record will perform the correct operation during recovery. It checks whether the required WAL records can be read and parsed, but it cannot guarantee that recovery will succeed in every situation.

PostgreSQL 19: Online Table Reorganization with REPACK (Similar to Oracle DBMS_REDEFINITION)

Vahid Yousefzadeh — Wed, 07 Oct 2026 10:52:54 +0000

For years, PostgreSQL DBAs have relied on VACUUM FULL to rewrite tables and reclaim unused space. However, when minimizing the impact of table reorganization is important, tools such as pg_repack provide an online-style alternative.

PostgreSQL 19 introduces the new REPACK command as a built-in solution for table reorganization, including REPACK CONCURRENTLY, which allows the table to remain available for normal database activity during most of the operation.

PostgreSQL 19 also extends this capability with support for reorganizing a table according to an index, providing functionality comparable to the physical ordering achieved with CLUSTER.

How REPACK Works

REPACK does not simply compact the existing table in place. Conceptually, it creates a new, compact copy of the table, copies the existing rows into it, tracks changes made to the original table while the operation is running, and applies those changes to the new table.

At the end, it performs a short final synchronization and swaps the old table with the reorganized one. This approach allows normal DML to continue during most of the operation.

This approach is conceptually similar to Oracle’s DBMS_REDEFINITION. In Oracle, an interim table is populated while the original table remains available, and a materialized view log can capture changes made to the original table during the reorganization. These changes are then synchronized to the interim table before the final DBMS_REDEFINITION.FINISH_REDEF_TABLE operation switches the objects.

Limitations of REPACK

Although REPACK provides online table reorganization with minimal downtime, it has some limitations:

The table must have a primary key or a suitable NOT NULL unique index.
DDL operations on the target table cannot be performed while REPACK is running.
Long-running transactions can delay the final lock required to complete the reorganization.
In addition, the operation requires additional disk space because a new copy of the table and its indexes is created.

Testing REPACK on a Large Table

In this article, I demonstrate how REPACK can reorganize a large table with minimal disruption to concurrent DML. I also demonstrate the option to reorganize a table according to an index, similar to CLUSTER, and compare its behavior with VACUUM FULL.

The test uses a table containing 30 million rows.

1. Initial Table Size

First, I checked the number of rows in jadval1:

postgres=#  select count(*) from jadval1;

  count
----------
 30000000
(1 row)

I then checked the physical size of the table and its indexes:

postgres=# SELECT
    pg_size_pretty(pg_table_size('jadval1')) AS table_size,
    pg_size_pretty(pg_indexes_size('jadval1')) AS index_size,
    pg_size_pretty(pg_total_relation_size('jadval1')) AS total_size;
 table_size | index_size | total_size
------------+------------+------------
 4824 MB    | 1071 MB    | 5895 MB
(1 row)

Therefore, the table occupied approximately 4.8 GB, while the total relation size, including indexes, was approximately 5.9 GB.

To investigate how much space was actually occupied by live tuples, I enabled the pgstattuple extension:

postgres=# CREATE EXTENSION IF NOT EXISTS pgstattuple;
CREATE EXTENSION

Then I ran:

postgres=# SELECT
    table_len,
    tuple_count,
    tuple_len,
    dead_tuple_count,
    dead_tuple_len,
    free_space,
    pg_size_pretty(table_len) AS table_size,
    pg_size_pretty(tuple_len) AS live_tuple_size,
    pg_size_pretty(dead_tuple_len) AS dead_tuple_size,
    pg_size_pretty(free_space) AS free_space_size
FROM pgstattuple('jadval1');

 table_len  | tuple_count | tuple_len  | dead_tuple_count | dead_tuple_len | free_space | table_size | live_tuple_size | dead_tuple_size | free_space_size
------------+-------------+------------+------------------+----------------+------------+------------+-----------------+-----------------+-----------------
 5056790528 |    30000000 | 2730000000 |                0 |              0 | 2040987872 | 4823 MB    | 2604 MB         | 0 bytes         | 1946 MB
(1 row)

This result is interesting.

There were:

30 million live tuples
Approximately 2.6 GB of live tuple data
Approximately 1.9 GB of free space

2. Running REPACK CONCURRENTLY

To demonstrate that REPACK (CONCURRENTLY) allows normal database activity during table reorganization, I started the operation from Session 1:

— session 1:

postgres=# REPACK (CONCURRENTLY) Jadval1;
executing...
While REPACK was running, I performed concurrent updates from Session 2:

— session 2

postgres=# UPDATE Jadval1 SET name = 'Vahid Yousefzadeh' WHERE id BETWEEN 20000001 AND 20010000;
UPDATE 10000
postgres=#  UPDATE Jadval1 SET name = 'Vahid Yousefzadeh' WHERE id BETWEEN 20010000 AND 20020000;
UPDATE 10001
postgres=# UPDATE Jadval1 SET name = 'Vahid Yousefzadeh' WHERE id BETWEEN 20020000 AND 20030000;
UPDATE 10001
postgres=# UPDATE Jadval1 SET name = 'Vahid Yousefzadeh' WHERE id BETWEEN 20030000 AND 20040000;
UPDATE 10001

At the same time, Session 3 continuously queried the table:

session 3:

postgres=# SELECT count(*) FROM Jadval1 WHERE name = 'Vahid Yousefzadeh';                                                                                                                
 count
-------
     0
(1 row)

postgres=# \g
 count
-------
 10000
(1 row)

postgres=# \g
 count
-------
 20000
(1 row)

postgres=# \g
 count
-------
 30000
(1 row)
postgres=# \g
 count
-------
 40000
(1 row)

The increasing count confirms that the table remained accessible for both UPDATE and SELECT operations while REPACK (CONCURRENTLY) was reorganizing it.

Monitoring REPACK Progress

The pg_stat_progress_repack view can be used to monitor the operation:

postgres=# SELECT
    pid,
    phase,
    heap_tuples_scanned,
    heap_tuples_inserted,
    heap_tuples_updated,
    heap_tuples_deleted
FROM pg_stat_progress_repack;
 pid  |       phase       | heap_tuples_scanned | heap_tuples_inserted | heap_tuples_updated | heap_tuples_deleted
------+-------------------+---------------------+----------------------+---------------------+---------------------
 1554 | seq scanning heap |            12727565 |             12727565 |                   0 |                   0
(1 row)

Checking for Blocking

I also checked whether the concurrent activity was blocked:

postgres=# SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query,
    blocked.wait_event_type,
    blocked.wait_event
FROM pg_stat_activity blocked
CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid)
JOIN pg_stat_activity blocking
    ON blocking.pid = b.pid
WHERE blocked.datname = current_database();
 blocked_pid | blocked_query | blocking_pid | blocking_query | wait_event_type | wait_event
-------------+---------------+--------------+----------------+-----------------+------------
(0 rows)

No blocking session was reported during the check. Therefore, in this test, REPACK (CONCURRENTLY) allowed the table to remain available for normal SELECT and UPDATE activity while the table was being reorganized.

REPACK Completion Time

After the concurrent activity and monitoring tests were completed, REPACK (CONCURRENTLY) finished successfully:


postgres=# REPACK (CONCURRENTLY) Jadval1;
REPACK
Time: 211323.981 ms (03:31.324)

The table reorganization completed in approximately 3 minutes and 31 seconds, while the table remained available for concurrent database activity during the operation.

3. Verifying the Result of REPACK

After REPACK completed, I checked the table size again:

postgres=# SELECT
    pg_size_pretty(pg_table_size('jadval1')) AS table_size,
    pg_size_pretty(pg_indexes_size('jadval1')) AS index_size,
    pg_size_pretty(pg_total_relation_size('jadval1')) AS total_size;
 table_size | index_size | total_size
------------+------------+------------
 2894 MB    | 644 MB     | 3539 MB
(1 row)

The following tables hows the table and index sizes before and after REPACK:

Press enter or click to view image in full size

So the table and its indexes together released approximately 2.3 GB of physical storage.

Then, I used pgstattuple again to verify the result:

postgres=# SELECT
    table_len,
    tuple_count,
    tuple_len,
    dead_tuple_count,
    dead_tuple_len,
    free_space,
    pg_size_pretty(table_len) AS table_size,
    pg_size_pretty(tuple_len) AS live_tuple_size,
    pg_size_pretty(dead_tuple_len) AS dead_tuple_size,
    pg_size_pretty(free_space) AS free_space_size
FROM pgstattuple('jadval1');
 table_len  | tuple_count | tuple_len  | dead_tuple_count | dead_tuple_len | free_space | table_size | live_tuple_size | dead_tuple_size | free_space_size
------------+-------------+------------+------------------+----------------+------------+------------+-----------------+-----------------+-----------------
 3034136576 |    30000000 | 2728360000 |                0 |              0 |   26310320 | 2894 MB    | 2602 MB         | 0 bytes         | 25 MB

The following table shows the significant reduction in free space after REPACK:

Press enter or click to view image in full size

4.Comparing REPACK with VACUUM FULL

After completing the REPACK test, I ran VACUUM FULL on the same table:

postgres=# vacuum full jadval1;
VACUUM
Time: 82397.189 ms (01:22.397)

VACUUM FULL also rewrites the table and can significantly reduce its physical size. However, the main operational difference is table availability during the operation.

To demonstrate this, I executed a SELECT against jadval1 while VACUUM FULL was running:

postgres=# SELECT count(*) FROM Jadval1 WHERE name = 'Vahid Yousefzadeh';
 count
-------
 40000
(1 row)

Time: 70548.856 ms (01:10.549)

The blocking-session check showed:

postgres=# SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query,
    blocked.wait_event_type,
    blocked.wait_event
FROM pg_stat_activity blocked
CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid)
JOIN pg_stat_activity blocking
    ON blocking.pid = b.pid
WHERE blocked.datname = current_database();
 blocked_pid |                         blocked_query                          | blocking_pid |    blocking_query    | wait_event_type | wait_event
-------------+----------------------------------------------------------------+--------------+----------------------+-----------------+------------
        1674 | SELECT count(*) FROM Jadval1 WHERE name = 'Vahid Yousefzadeh'; |         1554 | vacuum full jadval1; | Lock            | relation
(1 row)

VACUUM FULL and REPACK can both rewrite and compact a table, but their behavior in a production environment can be very different:

Press enter or click to view image in full size

5.REPACK … USING INDEX: Clustering the Table According to an Index

REPACK provides another useful capability: reorganizing a table according to the ordering defined by an index.
First, I created an index on customer_code:

postgres=# CREATE INDEX idx_jadval1_customer_code ON jadval1(customer_code);
CREATE INDEX

Then, I reorganized the table using this index:

postgres=# REPACK (CONCURRENTLY) jadval1 USING INDEX idx_jadval1_customer_code;
REPACK

In this case, REPACK does more than simply reclaim unused space. The table is physically reorganized according to the specified index, improving the physical locality of rows with similar customer_code values.