Personal blog of Yzmir Ramirez

PG_TDE is an experimental transparent data encryption access method for PostgreSQL 16 and beyond that I reviewed last October. There have been a lot of changes that covered in this post. This software is under active development. In the spirit of open and transparent communication, we would appreciate your feedback and invite PostgreSQL users to […]

PostgreSQL differs from certain other databases as it lacks a native solution to encrypt data on disk transparently. At Percona, we are in the process of developing PG_TDE, a tool designed to encrypt data using the AES-CTR encryption algorithm. This code is currently under development, and we are seeking your assistance in testing it.This blog […]

Securing your data can be a tough task. Unlike some other databases, PostgreSQL does not have a native solution to encrypt data on disk transparently. Percona is developing PG_TDE to encrypt that data using the AES-CTR encryption algorithm. This code is in development, and we need your help testing it.This blog will cover what you […]

PG_TDE is an experimental transparent data encryption access method for PostgreSQL 16 and beyond. This software is under active development and at a very early stage of design and implementation. In the spirit of open and transparent communication, we would appreciate your feedback and invite PostgreSQL users to test the extension and provide feedback either via the GitHub repository or in the forum.

What is TDE?

Transparent Data Encryption (TDE) offers encryption at the file level and solves the problem of protecting data at rest. This is something that is available in other databases but not provided in upstream, vanilla Postgres.

Percona has received user feedback that this would be a useful feature, so we are working on this as an open source extension for Postgres that anyone can deploy. Percona co-founder Peter Zaitsev’s blog on why PostgreSQL needs TDE highlights some of the technical and business reasons why you might want TDE. Since PostgreSQL doesn’t have TDE features yet, Percona wants to provide the TDE feature as an extension to PostgreSQL.

Running pg_tde

The following examples use Docker to demonstrate what is needed to test pg_tde.

stoker@testa:~$sudo docker run --name pg-tde -e POSTGRES_PASSWORD=mysecretpassword -d perconalab/postgres-tde-ext
2ccbe758f32348e286cb277aed17c1c3f9c880b37f92303bd2266a334096b0b1
Log in to PostgreSQL

We specified the POSTGIS_PASSWORD in the docker run command above.

stoker@testa:~$ sudo docker run -it --rm postgres psql -h 172.17.0.2 -U postgres
Password for user postgres:
psql (16.0 (Debian 16.0-1.pgdg120+1))
Type "help" for help.

Verify that pg_tde is installed

Use the psql dx command to double-check that pg_tde is installed.

postgres=# dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
pg_tde | 1.0 | public | pg_tde access method
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)

Another way to check is to try to create the pg_tde extension. The server should inform you that it is already installed. If you create a new database, you will have to re-create the extension side of it as below.

postgres=# CREATE EXTENSION pg_tde;
ERROR: extension "pg_tde" already exists
postgres=#

Now, we can create a table that uses pg_tde.

postgres=# CREATE TABLE sbtest1 ( id SERIAL, k INTEGER DEFAULT '0' NOT NULL, PRIMARY KEY (id)) USING pg_tde;
CREATE TABLE

And now you can insert data, delete data, update data, and do all the DML you are used to with PostgreSQL.

postgres=# SELECT cmin, cmax, xmin, xmax, ctid, * FROM sbtest1;

cmin | cmax | xmin | xmax | ctid | id | k
------+------+------+------+--------+----+----
0 | 0 | 744 | 0 | (0,1) | 1 | 1
0 | 0 | 744 | 0 | (0,2) | 2 | 2
0 | 0 | 744 | 0 | (0,3) | 3 | 3
0 | 0 | 744 | 0 | (0,7) | 7 | 7
0 | 0 | 744 | 0 | (0,8) | 8 | 8
0 | 0 | 744 | 0 | (0,9) | 9 | 9
0 | 0 | 744 | 0 | (0,10) | 10 | 10
0 | 0 | 746 | 0 | (0,11) | 11 | 11
0 | 0 | 746 | 0 | (0,12) | 12 | 12
0 | 0 | 746 | 0 | (0,13) | 13 | 13
(10 rows)

postgres=#

Please test pg_tde

Percona wants to make pg_tde your choice of TDE encryption, and for that, we need as many people testing and providing feedback as possible.

Follow the directions above or on the Github repository. Please let us know what you like and what you dislike about pg_tde. Let Percona know about any issues you discover, tell us what additional tooling about pg_tde you would like to have, and any other feedback.

This is open source software, and the old adage about having many eyeballs on the code to ensure its quality is applicable here, as Percona wants your input.

Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community, in a single distribution, designed and tested to work together.

 

Download Percona Distribution for PostgreSQL Today!

Performance is one of the essential aspects of a database management system. Very little can be more annoying and frustrating for users than poor performance, meaning long-running queries and high response times at the front end. 

One of the most effective ways to tackle performance improvement is having the proper indexes for the table columns. An index can save a lot of time in data access and lead the queries to gather the results the fastest way possible.

In PostgreSQL, there are different ways it can leverage the indexes to produce the most efficient plan.

In this blog post, we will review the following three different PostgreSQL scan types for an index depending on the table, what the queries are retrieving, and the used filters:

• Bitmap Index Scan
• Index Scan
• Index Only Scan

Building the testing scenario

We will use one table with a single index for the following exercises and examples and review how the scan strategy can change depending on the query conditions. 

Next is the table definition. I included a sequence creation statement for the id column since one best practice is always to have a primary key column, but for the specific examples we will go through, we don’t need it. 

CREATE SEQUENCE public.person_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

CREATE TABLE public.person(
  id integer DEFAULT nextval('public.person_id_seq'::regclass) NOT NULL,
  first_name text NOT NULL,
  last_name text NOT NULL,
  age integer NOT NULL,
  email text NOT NULL,
  register_date timestamp with time zone DEFAULT now() NOT NULL,
  is_active boolean DEFAULT true NOT NULL
);

Taking some inspiration from the How To Generate Test Data for Your Database With SQL Percona blog, we will use the following SQL statement to insert ten million rows to this table:

INSERT INTO public.person
SELECT generate_series
    , md5(random()::text)
    , md5(random()::text)
    , floor(random() * 99)::int
    , md5(random()::text) || '@gmail.com'
    , now() - (random() * (interval '90 days'))
    , case when random() > 0.5 then true else false end
  FROM generate_series(1, 10000000);

 Now we have our test table with some dummy data so we can practice.

Indexing the data

As we stated before, a best practice is to add a primary key for the table, but we are skipping this step and adding just a composite index that will help us to review the different scan types.

We create this multicolumn index as follows: 

CREATE INDEX idx_person_age_date_active ON person(age,register_date,is_active);

Here, we considered three columns with different cardinalities, meaning the proportion of distinct values about the total number of rows. The following are the columns ordered from the higher to the lower cardinality:

1. register_date. We loaded the 10M rows setting this column with the help of the random() function, so the number of distinct values is the largest from these three columns.
2. age. When we loaded the data, we also used the random() function, but we “limited” the results with the floor() function, so all the different values are between 1 and 99. 
3. is_active. This column data type is boolean, so only two different values are possible, true and false. 

It is essential to think about the data cardinality of a column when planning the indexes and, even before that, the filters we will execute against the data.

For example, in the above columns, having a single index over the is_active columns will not add any advantage because, from all the 10M rows, only two values are possible, so if we would want to filter all the is_active = true rows, the planner will use sequential scan with no doubt. 

One way to verify a column’s number of distinct values is by querying the pg_stats view in our database. For this is important to ensure the statistics are fresh; in this case, we run the ANALYZE command:

db1=# ANALYZE person;
ANALYZE

For the previous columns, the following is the result of querying the pg_stats view:

db1=# SELECT tablename AS table_name,attname AS column_name,n_distinct AS num_distinct_values
      FROM pg_stats
      WHERE tablename = 'person'
      AND attname IN ('age','register_date','is_active')
      ORDER BY num_distinct_values DESC;
table_name |  column_name  | num_distinct_values
------------+---------------+---------------------
person     | age           |                  99
person     | is_active     |                   2
person     | register_date |                  -1
(3 rows)

And we confirm the age column has 99 distinct values whereas is_active only 2. The register_date column shows a negative value of -1 because, as described in the documentation, the ANALYZE believed the number of distinct values is likely the same as the total number of rows:

[…] -1 indicates a unique column in which the number of distinct values is the same as the number of rows.

One index, different scan types

Now that we have the table data and the index in place, we can test the different scan types. First, and to have a starting point, let’s verify how PostgreSQL will resolve a query going for all the table data with no filters:

db1=# EXPLAIN (ANALYZE)
db1-# SELECT * FROM person;
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Seq Scan on person  (cost=0.00..304082.18 rows=10000018 width=126) (actual time=0.016..934.805 rows=10000000 loops=1)
 Planning Time: 0.129 ms
 Execution Time: 1183.355 ms
(3 rows)

As expected, to retrieve all the data from the table, the planner decided on a Sequential Scan, going for all the 10M rows. It makes sense since it is getting all the rows at once. The total time it took was over 1183ms (~1.1sec.) 

Bitmap Index Scan

The planner chooses this index scan method when the query asks for a large enough amount of data that can leverage the benefits of the bulk read, like the sequential scan, but not that large that actually requires processing ALL the table. We can think of the Bitmap Index Scan as something between the Sequential and Index Scan. 

The Bitmap Index Scan always works in pair with a Bitmap Heap Scan; the first scan the index to find all the suitable row locations and builds a bitmap, then the second use that bitmap to scan the heap pages one by one and gather the rows. 

The following is an example of a Bitmap Index Scan using the table and the index we built before:

db1=# EXPLAIN (ANALYZE)
      SELECT * FROM person
      WHERE age = 20 ;
                                                                      QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Gather  (cost=3682.90..212050.63 rows=97334 width=126) (actual time=46.142..221.876 rows=101476 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Bitmap Heap Scan on person  (cost=2682.90..201317.23 rows=40556 width=126) (actual time=24.783..189.769 rows=33825 loops=3)
        Recheck Cond: (age = 20)
        Rows Removed by Index Recheck: 534475
        Heap Blocks: exact=17931 lossy=12856
        ->  Bitmap Index Scan on idx_person_age_date_active  (cost=0.00..2658.57 rows=97334 width=0) (actual time=36.926..36.926 rows=101476 loops=1)
              Index Cond: (age = 20)
Planning Time: 0.122 ms
Execution Time: 225.554 ms
(11 rows)

In the inner node (which executes first) is a Bitmap Index Scan on the idx_person_age_date_active index. It creates the bitmap with all the suitable row locations and passes it to its parent node (that executes after), a Parallel Bitmap Heap Scan on the person table. This second stage goes through the pages individually, executes a Recheck for the filter condition, and returns the result data set. 

To compare, consider how the same operation performs using just a sequential scan:

db1=# START TRANSACTION ;
START TRANSACTION
db1=*# DROP INDEX idx_person_age_date_active;
DROP INDEX
db1=*#
db1=*# EXPLAIN (ANALYZE)
db1-*# SELECT * FROM person
WHERE age = 20 ;
                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Gather  (cost=1000.00..266898.83 rows=97334 width=126) (actual time=0.852..402.355 rows=101476 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on person  (cost=0.00..256165.43 rows=40556 width=126) (actual time=0.056..365.647 rows=33825 loops=3)
        Filter: (age = 20)
        Rows Removed by Filter: 3299508
Planning Time: 0.335 ms
Execution Time: 406.671 ms
(8 rows)

db1=*# ROLLBACK ;
ROLLBACK

Considering this query went for 101K rows, about 1% of the total rows. The Bitmap Index Scan took advantage of the “sequential scan” style bulk read over a limited number of pages and produced a better result than a direct Sequential Scan, performing 2x faster. 

Index Scan

It might be the scan method you think about when hearing something like, “Hey, this query is doing good; it uses the index….” This method is the basic definition of accessing the data by an index. 

The Index Scan consists of two steps, the first is to get the row location from the index, and the second is to gather the actual data from the heap or table pages. So every Index Scan access is two read operations. But still, this is one of the most efficient ways of retrieving data from a table. 

The planner picks this scan method when the number of rows to retrieve is small, so executing the two-step Index Scan operations is “cheaper” and faster than gathering the data by processing the table pages individually. 

Using our test table, the following is an example of an Index Scan:

db1=# EXPLAIN (ANALYZE)
      SELECT * FROM person
      WHERE age = 20
      AND register_date = '2023-03-23 19:50:03.22938+00'::timestamp;
                                                            QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_person_age_date_active on person  (cost=0.56..8.58 rows=1 width=126) (actual time=0.039..0.040 rows=1 loops=1)
  Index Cond: ((age = 20) AND (register_date = '2023-03-23 19:50:03.22938'::timestamp without time zone))
Planning Time: 0.190 ms
Execution Time: 0.064 ms
(4 rows)

See that in the query we used before, we have added a new filter expression: AND register_date = ‘2023-03-23 19:50:03.22938+00’::timestamp. The register_date column is part of the multicolumn index ​​idx_person_age_date_active. Since we are filtering by a singular value for it, there is one index entry for the same, so PostgreSQL gets the specific row location from the index in one read and then all the row data from the table page within that location. The whole query took 0.064ms; that was fast! 

In the above example, the query filters by a specific timestamp value for the register_date column, but PostgreSQL will still choose the Index Scan for multiple rows if the number of rows is small, for example, in the following:

db1=# EXPLAIN (ANALYZE)
      SELECT * FROM person
      WHERE age = 20
      AND register_date BETWEEN '2023-03-23 19:50:00'::timestamp AND '2023-03-23 20:00:00'::timestamp;
                                                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_person_age_date_active on person  (cost=0.56..8.58 rows=1 width=126) (actual time=0.044..0.167 rows=8 loops=1)
  Index Cond: ((age = 20) AND (register_date >= '2023-03-23 19:50:00'::timestamp without time zone) AND (register_date <= '2023-03-23 20:00:00'::timestamp without time zone))
Planning Time: 0.127 ms
Execution Time: 0.337 ms
(4 rows)

The query filtered the register_date column by a range using the BETWEEN operator. Based on the statistics, the planner thought the result would be one row and chose the Index Scan. In the end, the result set was eight rows, so eight pairs of read operations existed. Still, the query was resolved very quickly, with 0.337ms. 

Index Only Scan

Finally, we will review the Index Only Scan method. This is a really good approach that PostgreSQL uses to improve the standard Index Scan method. 

PostgreSQL uses this method when all the data the query asks for already exists in the index; in other words, the columns/expressions in the SELECT and WHERE clauses should be part of the index so that it is possible to avoid the second read operation to get the data from the table pages and return the result data only from the index read operation.

In the following, we are using almost the same query we used for the Index Scan example, but instead of asking for ALL the row columns (*), we are just retrieving the three columns we used to build the multicolumn index:

db1=# EXPLAIN (ANALYZE)
      SELECT age,register_date,is_active FROM person
      WHERE age = 20
      AND register_date = '2023-03-23 19:50:03.22938+00'::timestamp;
                                                              QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using idx_person_age_date_active on person  (cost=0.56..4.58 rows=1 width=13) (actual time=0.034..0.036 rows=1 loops=1)
  Index Cond: ((age = 20) AND (register_date = '2023-03-23 19:50:03.22938'::timestamp without time zone))
  Heap Fetches: 0
Planning Time: 0.103 ms
Execution Time: 0.058 ms
(5 rows)

See the EXPLAIN output now says Index Only Scan, and also, it confirms there was no access to the heap (table pages) with the line Heap Fetches: 0. The time was even better than the Index Scan from before, only 0.058ms. This scan method can help get the best possible performance for the queries that fit its conditions. 

Remember, indexing ALL the columns so the index contains ALL the same data as the table is “not a good idea.” If that is the case, PostgreSQL will not see any advantage of using the index and will pick the sequential scan approach. See the following:

db1=# START TRANSACTION ;
START TRANSACTION
db1=*# DROP INDEX "idx_person_age_date_active";
DROP INDEX
db1=*#
db1=*# CREATE INDEX idx_person_all ON person(id,first_name,last_name,age,email,register_date,is_active);
CREATE INDEX
db1=*#
db1=*# ANALYZE person;
ANALYZE
db1=*#
db1=*# EXPLAIN (ANALYZE)
      SELECT * FROM person
      WHERE age = 20
      AND register_date = '2023-03-23 19:50:03.22938+00'::timestamp;
                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Gather  (cost=1000.00..267582.21 rows=1 width=126) (actual time=6662.141..6671.741 rows=1 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on person  (cost=0.00..266582.11 rows=1 width=126) (actual time=5093.681..6636.195 rows=0 loops=3)
        Filter: ((age = 20) AND (register_date = '2023-03-23 19:50:03.22938'::timestamp without time zone))
        Rows Removed by Filter: 3333333
Planning Time: 2.704 ms
Execution Time: 6673.001 ms
(8 rows)

db1=*# ROLLBACK ;
ROLLBACK

In the above, in a single transaction, we dropped the multicolumn index we used in the previous examples, and created a new one considering ALL the table columns, then refreshed the statistics and tried one query asking for all the columns (*) on a specific filter, as a result, the planner chosen the Sequential Scan, it wanted to boost it by executing the operation with parallelism. Still, the final execution time was far from our good results. 

Final pieces of advice

Now that we have reviewed the different scan options PostgreSQL can use on a single index depending on the stored data and the query filter conditions let me share some final thoughts you can find helpful when planning your query’s filters and table indexes.

1. Index the columns with the most cardinality. Following this, your queries can perform best when filtering by the same column. Having indexes on columns with low cardinality will have the opposite effect since it will add extra maintenance, and with high certainty, the planner will not use them.
2. Plan your queries for small (specific) data sets rather than larger ones. If your workload and service design affords it, consider filtering your data thinking in retrieving just a few rows. As we saw, the Index Scan is an effective and optimized technique to retrieve data faster, and PostgreSQL will use it if the result data is small enough. 
3. Retrieve just the columns you need. By doing this, PostgreSQL can leverage Index Only Scan and avoid the “extra” read from the heap (table pages). These saves will produce a notorious good effect in a high query volume environment. Remember that the multicolumn indexes are not the only way to lead the planner to choose the Index Only Scan; you can also consider the covering indexes (matter for another blog, perhaps). 
4. Tune the random_page_cost parameter. Lowering this will lead the planner to prefer index scans rather than sequential scans. Modern SSD can deliver a better throughput for random read access, so you might analyze adjusting this parameter accordingly.
5. Tune the effective_cache_size parameter. Setting this parameter to a higher value (nearly 75% of total RAM if your machine is dedicated to the PostgreSQL service) will help the planner choose index scans over sequential scans. 

Remember that every implementation is different, and the details matter, so before adjusting anything on production is advisable to analyze and test the effects in a lower environment.

Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community in a single distribution, designed and tested to work together.

 

Download Percona Distribution for PostgreSQL Today!

While working as a DBA, we perform many regular tasks, and one of them is upgrading our database systems. There are some techniques to perform a PostgreSQL database upgrade, such as data dump and import, logical replication, or in-site upgrade using pg_upgrade.

The last is a good option when you can afford some downtime and desire to reuse the same server where your current instance is running.

The process using pg_upgrade is well documented, and you can easily find the instructions with little googling.

However, there are a few occasions when you face some unusual conditions that require additional research. In this blog post, I will show you a particular example I faced when working with an upgrade exercise using pg_upgrade.

As a regular procedure, the following steps were followed:

• Install the same PostgreSQL packages you already have installed in the current version for the new one.
• Run pg_upgrade with the –check flag to verify the compatibility between the current and the new version clusters.
• If all is good, perform the upgrade removing the –check flag.

You might consider some extra steps, such as verifying the existence of gin/gist indexes, unknown datatypes, or planning the upgrade of any standby server using the rsync approach. These are not in this blog’s scope to keep this example simple.

Ideally, all the three above steps execute with no issues, and you get your new PostgreSQL version up and running.

There are some situations where the second step, the –check one, fails, and you need to investigate and fix it before moving forward. A “regular” case is you missed installing a required package in the new version. You will end with something like the following:

Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for system-defined composite types in user tables  ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for tables WITH OIDS                               ok
Checking for invalid "sql_identifier" user columns          ok
Checking for presence of required libraries                 fatal

In this case, the pg_upgrade –check command will provide a hint:

Your installation references loadable libraries that are missing from the
new installation.  You can add these libraries to the new installation,
or remove the functions using them from the old installation.  A list of
problem libraries is in the file:
    loadable_libraries.txt

For example, if checking the content of the loadable_libraries.txt file, you see something like the next:

$ cat loadable_libraries.txt
could not load library "$libdir/pg_repack": ERROR: could not access file "$libdir/pg_repack": No such file or directory

You can immediately identify the missing package as the pg_repack, so you need to install it for the new version.

Well, sometimes, this information is not quite “evident.” Let’s review the case.

Example case

When working with the upgrade exercise, the goal was to move from PostgreSQL 11 to PostgreSQL 12. This was a specific requirement, but the situation could also happen when upgrading to a different version.

Following the main process, I installed the same packages as the existing version 11 to version 12 and verified.

ii  postgresql-11                   11.18-1.pgdg20.04+1               amd64        The World's Most Advanced Open Source Relational Database
ii  postgresql-12                   12.13-1.pgdg20.04+1               amd64        The World's Most Advanced Open Source Relational Database
ii  postgresql-common               246.pgdg20.04+1                   all          PostgreSQL database-cluster manager
ii  postgresql-client-11            11.18-1.pgdg20.04+1               amd64        front-end programs for PostgreSQL 11
ii  postgresql-client-12            12.13-1.pgdg20.04+1               amd64        front-end programs for PostgreSQL 12
ii  postgresql-client-common        246.pgdg20.04+1                   all          manager for multiple PostgreSQL client versions

Looking good, then we can execute the second step and verify the cluster’s compatibility.

Mmmh, something is missing. Let’s check what could be.

Ok, a “randomness” library doesn’t sound like a usual library, but we can review some details to find it.

I could miss some packages for the new version; let’s check again.

The same packages are installed for both versions.

The next idea is to verify the extensions we have installed in the source database.

Here, nothing looks “related” to the randomness library.

Just to double-check check, we can even try to find the related libraries from the PostgreSQL $libdir directory. For example, with the following bash snippet.

for lib in $(psql -qtA pgbench -c"select extname||'.so' from pg_extension") ; do find $(<pg version bin path>/pg_config --pkglibdir) -name $lib -type f ; done

Checked for version 11:

And the same for version 12:

Everything seems OK, so where is the randomness library coming from?

There is something I am missing. I tried pg_dump with the –binary-upgrade flag to review some other details regarding the extensions

pg_dump -C --schema-only --quote-all-identifiers --binary-upgrade dbname=<DATABASE_NAME> -f dump_binary_upgrade.sql

From the PostgreSQL documentation we can see the following statement about this flag:

–binary-upgrade

This option is for use by in-place upgrade utilities. Its use for other purposes is not recommended or supported. The behavior of the option may change in future releases without notice.

But the intention is not to restore. Just take a look at the extra information we can get.

The –binary-upgrade option will produce a “verbose” output related to the extensions. The CREATE EXTENSION IF NOT EXISTS commands will be replaced with DROP EXTENSION IF EXISTS …, SELECT pg_catalog.binary_upgrade_create_empty_extension() calls.

Also, you will see a section with the explicit creation of the C language functions for the extensions. An example is the next for the pg_stat_statements extension:

--
-- Name: pg_stat_statements_reset(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION "public"."pg_stat_statements_reset"() RETURNS "void"
    LANGUAGE "c" PARALLEL SAFE
    AS '$libdir/pg_stat_statements', 'pg_stat_statements_reset';

-- For binary upgrade, handle extension membership the hard way
ALTER EXTENSION "pg_stat_statements" ADD FUNCTION "public"."pg_stat_statements_reset"();


ALTER FUNCTION "public"."pg_stat_statements_reset"() OWNER TO "postgres";

Looking at the following line:

    AS '$libdir/pg_stat_statements', 'pg_stat_statements_reset';

You can identify the physical library ($libdir/pg_stat_statements) used to build the Postgres function and the C function (‘pg_stat_statements_reset‘) within that library.

For some extra good material about writing extensions in PostgreSQL, you can take a look at the blog post Writing PostgreSQL Extensions is Fun – C Language, it really worth it.

Well, while doing this review about the extension’s definition, I saw the next one:

--
-- Name: bytea_size("bytea"); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION "public"."bytea_size"("bytea") RETURNS integer
    LANGUAGE "c" STRICT
    AS 'randomness', 'bytea_size';


ALTER FUNCTION "public"."bytea_size"("bytea") OWNER TO "postgres";

Aha! There is the randomness library call! A custom C function is using it.

We should remember PostgreSQL includes support for some procedural languages with its base distribution, such as  PL/pgSQL, PL/Tcl, PL/Perl, and PL/Python. So, having C functions is perfectly supported; however, it is not what we might say the usual.

As described in the PostgreSQL documentation, there are a few paths where the libraries can be placed:

The following algorithm is used to locate the shared object file based on the name given in the CREATE FUNCTION command:

1. If the name is an absolute path, the given file is loaded.
2. If the name starts with the string $libdir, that part is replaced by the PostgreSQL package library directory name, which is determined at build time.
3. If the name does not contain a directory part, the file is searched for in the path specified by the configuration variable dynamic_library_path.
4. Otherwise (the file was not found in the path, or it contains a non-absolute directory part), the dynamic loader will try to take the name as given, which will most likely fail. (It is unreliable to depend on the current working directory.)

For this case, I searched all the paths and did not find the randomness. No library. It seems somebody was playing around to build a C function and at some point, removed the library but left the Postgres function behind (this was a DEV environment).

After confirming this “public“, “bytea_size” function is not used, the solution was to DROP it, then retry the pg_upgrade –check command.

After this point, the pg_upgrade was successful.

Conclusion

As we all already know, PostgreSQL is really powerful, and its core capabilities can be extended with custom functions and extensions. Writing your own in C language functions requires some extra knowledge, but you can definitely get very interesting results.

Remember, every time a custom C function is created, PostgreSQL itself doesn’t compile the C code, which should be done beforehand, but the CREATE FUNCTION command is recorded literally in the system catalogs. So it will be referenced for any other load calls, including the pg_upgrade command.

When working with databases, there are always some projects oriented to performing analytics and reporting tasks over the information stored in the database. Usually, these tasks leverage window functions to do calculations “across a set of table rows that are somehow related to the current row,” as is described in the documentation.

There are several built-in windows functions available in PostgreSQL. In the latest release, PostgreSQL 15, some performance improvements were added for the rank(), row_number(), and count() functions. First, let’s review what these functions can do.

The window functions

As mentioned above, the window functions let us perform some calculations on a set of table rows related to the current one. The “set of table rows” is usually identified as a “partition” defined by a column or columns. As we read in the documentation, the named functions work for:

rank () ? bigint

Returns the rank of the current row, with gaps; that is, the row_number of the first row in its peer group.

row_number () ? bigint

Returns the current row number within its partition, counting from 1.

count ( * ) ? bigint

Computes the number of input rows.

Consider the following table and data:

                                          Table "public.employee"
    Column      |           Type           | Collation | Nullable |                 Default
-----------------+--------------------------+-----------+----------+------------------------------------------
emp_id          | integer                  |           | not null | nextval('employee_emp_id_seq'::regclass)
emp_name        | character varying(20)    |           | not null |
emp_country     | character varying(35)    |           | not null |
emp_salary      | integer                  |           | not null |
date_of_joining | timestamp with time zone |           | not null | now()
Indexes:
    "employee_pkey" PRIMARY KEY, btree (emp_id)

demo=# SELECT * FROM employee ;
emp_id  |   emp_name   | emp_country | emp_salary |    date_of_joining
--------+--------------+-------------+------------+------------------------
      1 | KELIO        | Japon       |       2000 | 2021-10-26 00:00:00+00
      2 | JEAN-VINCENT | Canada      |       6500 | 2021-01-22 00:00:00+00
      3 | JUNO         | Japon       |       4000 | 2021-02-27 00:00:00+00
      4 | GUY-EMMANUEL | Salvador    |       2000 | 2020-07-27 00:00:00+00
      5 | WALI         | Japon       |       7000 | 2021-01-31 00:00:00+00
      6 | HENRI-PAUL   | Canada      |       4500 | 2021-08-19 00:00:00+00
      7 | MUHAMED      | France      |       5000 | 2021-07-20 00:00:00+00
      8 | MUHITTIN     | Madagascar  |       2500 | 2021-12-31 00:00:00+00
      9 | DEVLIN       | Madagascar  |       7000 | 2022-04-03 00:00:00+00
     10 | JOSUE        | Salvador    |       5500 | 2020-09-25 00:00:00+00
(10 rows)

rank()

We can use the rank() function to get the rank of employees (id) per country based on their salary. Look at the next example.

demo=# SELECT 
         emp_id, 
         emp_salary, 
         emp_country, 
         rank() OVER (PARTITION BY emp_country ORDER BY emp_salary DESC) 
       FROM employee;

emp_id  | emp_salary | emp_country | rank
--------+------------+-------------+------
      2 |       6500 | Canada      |    1
      6 |       4500 | Canada      |    2
      7 |       5000 | France      |    1
      5 |       7000 | Japon       |    1
      3 |       4000 | Japon       |    2
      1 |       2000 | Japon       |    3
      9 |       7000 | Madagascar  |    1
      8 |       2500 | Madagascar  |    2
     10 |       5500 | Salvador    |    1
      4 |       2000 | Salvador    |    2
(10 rows)

row_number()

In the next example, the row_number() function gets a sorted list of employees’ names per country and their relative numeric position.

demo=# SELECT 
         emp_id, 
         emp_name, 
         emp_country, 
         row_number() OVER (PARTITION BY emp_country ORDER BY emp_name) 
       FROM employee;

emp_id  |   emp_name   | emp_country | row_number
--------+--------------+-------------+------------
      6 | HENRI-PAUL   | Canada      |          1
      2 | JEAN-VINCENT | Canada      |          2
      7 | MUHAMED      | France      |          1
      3 | JUNO         | Japon       |          1
      1 | KELIO        | Japon       |          2
      5 | WALI         | Japon       |          3
      9 | DEVLIN       | Madagascar  |          1
      8 | MUHITTIN     | Madagascar  |          2
      4 | GUY-EMMANUEL | Salvador    |          1
     10 | JOSUE        | Salvador    |          2
(10 rows)

count()

The count() function is an “old known” tool used by almost everyone with access to a SQL engine. This function is part of the aggregate functions list but can act as windows functions when the OVER clause follows the call. So we can use it to know how many employees share the same salary as a given employee name. 

demo=# SELECT 
         emp_name, 
         emp_salary, 
         emp_country, 
         count(*) OVER (PARTITION BY emp_salary) 
       FROM employee;

  emp_name   | emp_salary | emp_country | count
--------------+------------+-------------+-------
KELIO        |       2000 | Japon       |     2
GUY-EMMANUEL |       2000 | Salvador    |     2
MUHITTIN     |       2500 | Madagascar  |     1
JUNO         |       4000 | Japon       |     1
HENRI-PAUL   |       4500 | Canada      |     1
MUHAMED      |       5000 | France      |     1
JOSUE        |       5500 | Salvador    |     1
JEAN-VINCENT |       6500 | Canada      |     1
WALI         |       7000 | Japon       |     2
DEVLIN       |       7000 | Madagascar  |     2
(10 rows)

Window functions in PostgreSQL 15

Now that we have refreshed what the window functions are, let’s consider what the PostgreSQL 15 release notes say:

Improve the performance of window functions that use row_number(), rank(), and count() (David Rowley)

Accordingly, if we are users of the window functions and move from a previous version to version 15, we should see an improvement in the performance of our workload. Let’s test it.

Laboratory case

To test the performance of the window functions, I created three instances of PostgreSQL, (a) version 13, (b) version 14, and (c) version 15. 

I used the same public.employee table used in the previous examples, and I loaded it with 10K rows. Then I executed the same queries we saw before for the window functions. I got the output from an EXPLAIN (ANALYZE) command which executes the query, and we can see the timing for the specific window function.

The EXPLAIN (ANALYZE) output was the same for each version of PostgreSQL.

rank() 

PG15

                                                      QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
WindowAgg  (cost=1288.49..1488.49 rows=10000 width=25) (actual time=11.946..18.732 rows=10000 loops=1)
  ->  Sort  (cost=1288.49..1313.49 rows=10000 width=17) (actual time=11.928..12.803 rows=10000 loops=1)
        Sort Key: emp_country, emp_salary DESC
        Sort Method: quicksort  Memory: 980kB
        ->  Seq Scan on employee  (cost=0.00..180.00 rows=10000 width=17) (actual time=0.008..2.402 rows=10000 loops=1)
Planning Time: 0.143 ms
Execution Time: 19.268 ms
(7 rows)

PG14

                                                      QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
WindowAgg  (cost=844.39..1044.39 rows=10000 width=25) (actual time=12.585..20.921 rows=10000 loops=1)
  ->  Sort  (cost=844.39..869.39 rows=10000 width=17) (actual time=12.560..13.545 rows=10000 loops=1)
        Sort Key: emp_country, emp_salary DESC
        Sort Method: quicksort  Memory: 1020kB
        ->  Seq Scan on employee  (cost=0.00..180.00 rows=10000 width=17) (actual time=0.011..1.741 rows=10000 loops=1)
Planning Time: 0.449 ms
Execution Time: 21.407 ms
(7 rows)

PG13

                                                      QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
WindowAgg  (cost=844.39..1044.39 rows=10000 width=25) (actual time=18.949..28.619 rows=10000 loops=1)
  ->  Sort  (cost=844.39..869.39 rows=10000 width=17) (actual time=18.896..19.998 rows=10000 loops=1)
        Sort Key: emp_country, emp_salary DESC
        Sort Method: quicksort  Memory: 1020kB
        ->  Seq Scan on employee  (cost=0.00..180.00 rows=10000 width=17) (actual time=0.011..1.228 rows=10000 loops=1)
Planning Time: 0.460 ms
Execution Time: 29.111 ms
(7 rows)

We can easily see from the WindowAgg node that the total time was smaller in the PG15 than in the other two. The performance improvement is clear here.

To verify this is consistent, I got the Total Time for the WindowAgg node from 500 executions and plotted the next graph.

We can see the timing from the PG15 version is better than the other versions. Also, I added a trending line. We see the PG13 performed the “worst,” and even when the PG14 showed a better trend, the PG15 was the best.

I did the same exercise for the row_number() and count() functions. 

row_number() 

count()

I also got the results from 500 executions for these last two functions. In both cases, the difference was smaller than the rank() function, but the PG15 still showed better results, as seen in the trend line.

The performance improvement for the rank(), row_number(), and count() window function introduced in the new PostgreSQL 15 will let all those analytic and reporting projects process their data faster than in previous versions. As always, every environment has its characteristics and challenges, but as we saw in these quick tests, the new version delivers the improvement just out of the box.

Consuming data from a remote foreign database into our local database just like another local table is a great feature that adds an excellent level of flexibility to the design of a database solution. 

This option is present in a number of database products. Oracle (DBLINK), MySQL (FEDERATED Engine), and obviously, PostgreSQL (dblink, postgres_fdw) support it. PostgreSQL supports multiple foreign data wrappers. But in this blog, we will review a specific feature added for postgres_fdw in the new version 15.

How postgres_fdw works

Let’s say that you have a local and a remote PostgreSQL server. The second one has a table that your application using the local server needs to access. You might think of any of the following solutions:

1. Configure your application to be able to connect both servers.
2. Set up some background jobs to perform a copy every then and now.
3. Configure logical replication between the two servers so the local table syncs up with the remote one.

Any of the above has its own advantages and disadvantages. For the first one, you might need to configure your network and grant access privileges to both DB servers. All these might add complexity and open the door for some security issues. The second option will add extra load on both servers. The data is not going to be up-to-date in real-time, so some reading inconsistencies might happen. The third one actually could be a good option. But if you are planning to MODIFY the synced data then you might face some collisions.  

Then the solution might be relaying in the Foreign Data Wrapper. From the official documentation the next steps are required to access data from a remote PostgreSQL server:

1. Install the postgres_fdw extension using CREATE EXTENSION.
2. Create a foreign server object, using CREATE SERVER, to represent each remote database you want to connect to. Specify connection information, except user and password, as options of the server object.
3. Create a user mapping, using CREATE USER MAPPING, for each database user you want to allow to access each foreign server. Specify the remote user name and password to use as user and password options of the user mapping.
4. Create a foreign table, using CREATE FOREIGN TABLE or IMPORT FOREIGN SCHEMA, for each remote table you want to access. The columns of the foreign table must match the referenced remote table. You can, however, use table and/or column names different from the remote tables, if you specify the correct remote names as options of the foreign table object.

You will end with something like this:

So now, with the proper privileges, your application is able to access the data from the remote table just like another local table, you can SELECT or even execute DML statements on it.

What has changed on PG15?

The described functionality has been present for a while in the PostgreSQL versions, and the new enhancement that is going to be available in version 15 is the parallel transaction commit when using postgres_fdw. This is particularly useful when your solution design relies on having multiple remote servers, such as a distributed PostgreSQL database. 

From the Transaction Management documentation, we know that “during a query that references any remote tables on a foreign server, postgres_fdw opens a transaction on the remote server if one is not already open corresponding to the current local transaction”, in the case of our local transaction references multiple remote tables from different foreign servers, then a (sub)transaction is opened on each remote server.

Up to version 14, when there are multiple (sub)transactions tied to a local transaction and the transaction is committed locally, postgres_fdw commits each remote (sub)transaction one by one (sequentially). This behavior is still the default on version 15, but now we have the option to enable the parallel_commit option for each foreign server tied to a local transaction, and in that case, when the local transaction commits all the remote (sub)transactions will commit at once (parallel). 

Laboratory case

I built a laboratory to test the sequential and the parallel remote (sub)transactions commit and catch the differences. 

I decided to use the pgbench tool for the benchmark and the option to create partitions for the pgbench_accounts table, I adapted these partitions to be foreign tables, just as was described on a very interesting Percona blog about Sharding on PostgreSQL, worth having a look on this last one. Finally, I created a partitioned table with 70 partitions distributed over seven remote servers, each one tied to a postgres_fdw foreign server. 

The next diagram gives some insight.

Pgbench execution

To test the new feature I tried two different loads with the parallel_commit option disabled (default) and then enabled:

• I ran the “normal” pgbench workload. It accesses one row per transaction, which means every local transaction opened only one remote (sub)transaction.
• I ran a pgbench “custom” script to retrieve 100 rows per transaction. In this case, every local transaction opened multiple remote (sub)transactions.

Running the regular pgbench load against the PG15 server was not more than:

pgbench --progress-timestamp -c 16 -j 4 -T 3600 -P 2 \
  -h pg15 -U pgbench pgbench

After getting the results I enabled the parallel_commit option on each foreign server the next way:

ALTER SERVER remote_server<N> OPTIONS (ADD parallel_commit 'true');

And repeated the pgbench load. The results are as follows.

We see no big difference between the load with the parallel option set OFF or ON. This was kind of expected due to in the regular pgbench load every local transaction opened only one remote transaction.

For the second test scenario I used a custom pgbench script to go for 100 rows on each transaction, it was as follows:

$ cat pgbench_custom.sql
\set v1 random(1, 100000 * :scale)
\set v2 :v1 + 100

BEGIN;
  SELECT abalance FROM pgbench_accounts
  WHERE aid BETWEEN :v1 AND :v2;
END;

Before running the load I disabled the parallel_commit option from the foreign servers executing the next for each one:

ALTER SERVER remote_server<N> OPTIONS (DROP parallel_commit);

And then I ran the custom load with NO parallel commit the next way:

pgbench --progress-timestamp -c 8 -j 4 -T 3600 -P 2 \
  -h pg15 -U pgbench -f pgbench_custom.sql pgbench

Then I enabled it on each foreign server with the command I used before and ran the custom load again. The next are the results.

Now we see a clear improvement when using the parallel_commit option enabled. Consistently the number of TPS was higher during the test when the option was enabled showing how the performance can be improved.

In general, the TPS dropped down compared with the regular pgbench load. The details are out of the scope of this blog post, the goal here was to get an idea of how the performance might be improved using the new parallel_commit option in similar conditions. 

Conclusion

The capabilities a PostgreSQL database gets with postgres_fdw have been known for a while, and multiple system designs have taken advantage of them. 

The addition of the new parallel_commit option when using postgres_fdw in PostgreSQL 15 brings a very good opportunity to improve the performance of our databases if they are designed as distributed systems and our local transactions would be tied to multiple remote (sub)transactions. Every solution and system design requires its own analysis. This blog post only intends to show the new option, and how we can deliver a higher throughput under the same conditions just by enabling the new parallel_commit option. 

The PostgreSQL RDBMS has shown rapid growth in terms of adoption and usability for a wide range of industries and projects. The same is true as for the new methodologies for application development and deployment, which currently focuses on making it easier for developers to get the infrastructure and the DB model they need with not too much intervention from other IT teams, so they can jump into their development process as quick as possible.

The PostgreSQL community is very active and always has included new features in every release that cover or improve some necessities for this changing and always evolving IT world. 

In this blog post, I’m going to go through a “small” feature just added in the latest PostgreSQL version 14 release. This for sure comes in handy when the time for granting privileges for users in the DB model comes. This is the inclusion of two new predefined roles, which can simplify the privilege management:

• pg_read_all_data 
• pg_write_all_data

This new facility to grant access in a wide scope requires to be used with the proper control and awareness, especially when we work in a production environment. With that said, let’s check what is this about.

Users, Roles, and Privileges

When working with a PostgreSQL system, the concepts of Users and Roles may be used indistinguishably from each other, they refer to a cluster-level object that usually represents a database user (User) and/or can act as a logical container for privileges (Role), the syntax is often interchangeable, ie:

ALTER ROLE... 
ALERT USER...

NOTE: In this blog, we are going to grant the privileges directly to the users for sake of simplicity, however, as a best practice is advisable to set all permissions to a role rather than a user so we can get better control over them.

The privileges are all those permissions an administrator, or object owner, can grant to a role (and thus to a user, or a set of users) and apply to specific objects within a database context. Depending on the object type there are different privileges that can be granted, their usage and effect are not exactly in the scope of this blog post but you can review the official documentation as always. 

We are going to review a specific use case to get how the new predefined roles can be used to simplify some of the actions related to the access control.

The Use Case

Let’s imagine we are working in a new application or service that will use our loved PostgreSQL database as backend, this application can be an ETL, a data extraction tool, or even an on-demand microservice, the thing is it requires access practically to all the data in our PG cluster, that means all the user tables (and views) within all the schemas and in all the databases present in the cluster. Think in the next model, two databases, one has two schemas the other only one schema, each schema is owned by a different user, each schema has one or more tables. Our application user is going to be named appuser. 

database database1:
  schema schema1:
    owner: user1
    table table1:
      columns:
      - id: integer
      - data: text
    table table2:
      columns:
      - id: integer
      - data: text
  schema schema2:
    owner: user2
    table table1:
      columns:
      - id: integer
      - data: text
database database2:
  schema schema1:
    owner: user1
    table table1:
      columns:
      - id: integer
      - data: text

Getting Hands-On

Before trying this with the new predefined roles let’s check how it would be handled in a PostgreSQL 13.

First, create the user

pg13-database1 postgres =# create user appuser;
CREATE ROLE
pg13-database1 postgres =# alter user appuser password 'secretsuperpasswd';
ALTER ROLE

If we connect with our new user and try to access the tables on schema1:

??pg13-database1 user1 => \c database1 appuser
Password for user appuser:
psql (14.1, server 13.5 (Debian 13.5-1.pgdg110+1))
You are now connected to database "database1" as user "appuser".

pg13-database1 user1 => select * from schema1.table1 limit 5;
ERROR:  permission denied for schema schema1
LINE 1: select * from schema1.table1 limit 5;
                      ^

Ok, grant USAGE on the schema to the user:

Pg13-database1 postgres =# grant usage on schema schema1 to appuser;
GRANT

Now try to select from the table:

pg13-database1 appuser => select * from schema1.table1 limit 5;
ERROR:  permission denied for table table1

Ok, ok, we need to grant SELECT to the table, got it:

pg13-database1 user1 => grant select on schema1.table1 to appuser;
GRANT

pg13-database1 appuser => select * from schema1.table1 limit 5;
 id | data
----+------
  1 | 1
  2 | 2
  3 | 3
  4 | 4
  5 | 5
(5 rows)

Now try the other table in the schema:

pg13-database1 appuser => select * from schema1.table2 limit 5;
ERROR:  permission denied for table table2

Mmmh, ok, let’s grant SELECT over all the tables in the schema:

pg13-database1 user1 => grant select on all tables in schema schema1 to appuser;
GRANT

pg13-database1 appuser => select * from schema1.table2 limit 5 ;
 id | data
----+------
  1 | 1
  2 | 2
  3 | 3
  4 | 4
  5 | 5
(5 rows)

Worked!

What about somebody finds that the model needs a new table (table3), look at the next:

pg13-database1 user1 => create table schema1.table3 (like schema1.table1) ;
CREATE TABLE

pg13-database1 user1 => insert into schema1.table3(id, data)
select i,i::text from generate_series(1,1000) i;
INSERT 0 1000

Now our appuser tries to read from it, we already granted SELECT over all the tables, right?:

pg13-database1 appuser => select * from schema1.table3 limit 5;
ERROR:  permission denied for table table3

We have to repeat the previous grants in the new table, just as we did for the table2 table.  

To let our user keep the read access even in new tables we have to alter the default privileges at the schema:

pg13-database1 user1 => alter default privileges in schema schema1 grant select on tables to appuser;
ALTER DEFAULT PRIVILEGES

Now if a new table is created our user will have access to it:

pg13-database1 user1 => create table schema1.table4 (like schema1.table1) ;
CREATE TABLE

pg13-database1 user1 => insert into schema1.table4(id, data)
select i,i::text from generate_series(1,1000) i;
INSERT 0 1000

pg13-database1 appuser => select * from schema1.table4 limit 5 ;
 id | data
----+------
  1 | 1
  2 | 2
  3 | 3
  4 | 4
  5 | 5
(5 rows)

Nice!

But do not forget we have another schema, and the appuser should be able to get the data from there as well.

pg13-database1 appuser => select * from schema2.table1 limit 5;
ERROR:  permission denied for schema schema2

OK, we have to apply all the above… again. 

And also remember we have a different database in the cluster, so:

pg13-database1 appuser => \c database2 appuser
psql (14.1, server 13.5 (Debian 13.5-1.pgdg110+1))
You are now connected to database "database2" as user "appuser".

pg13-database2 appuser =>  select * from schema1.table1 limit 5;
ERROR:  permission denied for schema schema1

We have to do the same for this database’s schemas and tables as well. 

Is true we can follow the well-known “code once, execute many” and turn all these steps into a single automation piece, but we still need to take them into consideration since they are needed.

What about the “little” feature in PostgreSQL 14 we want to check around, the new predefined roles are described as follow:

Role	Allowed Access
pg_read_all_data	Read all data (tables, views, sequences), as if having SELECT rights on those objects, and USAGE rights on all schemas, even without having it explicitly. This role does not have the role attribute BYPASSRLS set. If RLS is being used, an administrator may wish to set BYPASSRLS on roles which this role is GRANTed to.
pg_write_all_data	Write all data (tables, views, sequences), as if having INSERT, UPDATE, and DELETE rights on those objects, and USAGE rights on all schemas, even without having it explicitly. This role does not have the role attribute BYPASSRLS set. If RLS is being used, an administrator may wish to set BYPASSRLS on roles which this role is GRANTed to.

https://www.postgresql.org/docs/14/predefined-roles.html

So, how our previous scenario would work in PostgreSQL 14? let’s check it out. 

As before, we start creating our user

pg14-database1 postgres =# create user appuser;
CREATE ROLE
pg14-database1 postgres =# alter user appuser password 'supersecretpasswd';
ALTER ROLE

If we try accessing the table right now with our new user, we know what is going to happen:

pg14-database1 user1 => \c database1 appuser
Password for user appuser:
You are now connected to database "database1" as user "appuser".

pg14-database1 appuser => select * from schema1.table1 limit 5;
ERROR:  permission denied for schema schema1
LINE 1: select * from schema1.table1 limit 5 ;

The magic starts here, we can grant the new predefined role:

pg14-database1 postgres =# grant pg_read_all_data to appuser ;
GRANT ROLE

And…

pg14-database1 appuser => select * from schema1.table1 limit 5 ;
 id | data
----+------
  1 | 1
  2 | 2
  3 | 3
  4 | 4
  5 | 5
(5 rows)

WORKED!

Even for the other schema:

pg14-database1 appuser => select * from schema2.table1 limit 5 ;
 id | data
----+------
  1 | 1
  2 | 2
  3 | 3
  4 | 4
  5 | 5
(5 rows)

Now let’s add a new table:

pg14-database1 user1 => create table schema1.table3 (like schema1.table1) ;
CREATE TABLE

pg14-database1 user1 => insert into schema1.table3(id, data)
database1-> select i,i::text from generate_series(1,1000000) i;
INSERT 0 1000000

And try the SELECT from it:

pg14-database1 appuser => select * from schema1.table3 limit 5;
 id | data
----+------
  1 | 1
  2 | 2
  3 | 3
  4 | 4
  5 | 5
(10 rows)

WORKED!

What about the other database:

pg14-database1 appuser => \c database2 appuser
You are now connected to database "database2" as user "appuser".

pg14-database2 appuser => select * from schema1.table1 limit 5;
 id |  data
----+------
  1 | 1
  2 | 2
  3 | 3
  4 | 4
  5 | 5
(5 rows)

WOW!

As we can see, a single command did the work for the previous multiple commands we required in PG13, cool!.

For the write privileges we should do almost the same: 

• For PG13 grant the UPDATE, INSERT, and (acting with caution) the DELETE instead of SELECT.
• In PG14 grant the pg_write_all_data role.

NOTE: Granting only the privileges for DML directly or using the new PG14 predefined role, without the read part the user won’t be able to perform UPDATES or DELETES over subsets, due to it requires the filtering, and that only is viable if the read access is in place.

pg14-database1 write_appuser => \dg write_appuser
                List of roles
 Role name      | Attributes |      Member of
----------------+------------+---------------------
 write_appuser  |            | {pg_write_all_data}
 
pg14-database1 write_appuser => insert into schema1.table1(id, data)
values(2000000, 'database1.schema1.table1-2000000');
INSERT 0 1

pg14-database1 write_appuser => update schema1.table1 set data = 'database1.schema1.table1-2000000-upd' where id = 2000000 ;
ERROR:  permission denied for table table1

pg14-database1 write_appuser => delete from schema1.table1 where id = 2000000 ;
ERROR:  permission denied for table table1

pg14-database1 write_appuser => update schema1.table1 set data = '';
UPDATE 1000001
pg14-database1 write_appuser => delete from schema1.table1;
DELETE 1000001

This feature might help us to get things done quickly, especially if we are working in large environments. However, we always need to be sure to who we are giving access to and how it will be used. 

Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community, in a single distribution, designed and tested to work together.

Download Percona Distribution for PostgreSQL Today!

We have several PostgreSQL versions that support logical decoding to replicate data changes from a source database to a target database, which is a cool and very powerful tool that gives the option to replicate all the tables in a database, only one schema, a specific set of tables or even only some columns/rows, also is a helpful method for version upgrades since the target database can run on a different (minor or major) PostgreSQL version.

There are some cases when the databases have been hosted in the AWS Relational Database Service (RDS) which is the fully auto-managed solution offered by Amazon Web Services, there is no secret that choosing this option for our database backend comes with a level of vendor lock-in, and even when RDS offers some build-in replica solutions such as Multi-AZ or read-replicas sometimes we can take advantage of the benefits from logical replication.

In this post I will describe the simplest and basic steps I used to implement this replica solution avoiding the initial copy data from the source database to the target, creating the target instance from an RDS snapshot. Certainly, you can take advantage of this when you work with a big/huge data set and the initial copy could lead to high timeframes or network saturation.   

NOTE: The next steps were tested and used for a specific scenario and they are not intended to be an any-size solution, rather give some insight into how this can be made and most importantly, to stimulate your own creative thinking.  

The Scenario

Service Considerations

In this exercise, I wanted to perform a version upgrade from PostgreSQL v11.9 to PostgreSQL v12.5, we can perform a direct upgrade using the build-in option RDS offers, but that requires a downtime window that can vary depending on some of the next:

• Is Multi-AZ enabled?
• Are the auto backups enabled?
• How transactional is the source database?

During the direct upgrade process, RDS takes a couple of new snapshots of the source instance, firstly at the beginning of the upgrade and finally when all the modifications are done, depending on how old is the previous backup and how many changes have been made on the datafiles the pre backup could take some time. Also, if the instance is Multi-AZ the process should upgrade both instances, which adds more time for the upgrade, during most of these actions the database remains inaccessible.

The next is a basic diagram of how an RDS Multi-AZ instance looks, all the client requests are sent to the master instance, while the replica is not accessible and some tasks like the backups are executed on it.

Therefore, I choose logical replication as the mechanism to achieve the objective, we can aim for a quicker switch-over if we create the new instance in the desired version and just replicate all the data changes, then we need a small downtime window just to move the traffic from the original instance to the upgraded new one.

Prerequisites

To be able to perform these actions we would need:

• An AWS user/access that can operate the DB instances, take DB snapshots and upgrade and restore them.
• The AWS user also should be able to describe and create DB PARAMETER GROUPS.
• A DB user with enough privileges to create the PUBLICATION on source and SUBSCRIPTION on target also is advisable to create a dedicated replication user with the minimum permissions. 

The 1-2-3 Steps

Per the title of this post, the next is the list of steps to set up a PostgreSQL logical replication between a PostgreSQL v11.9 and a v12.5 using an RDS snapshot to initialize the target database. 

1. Verify the PostgreSQL parameters for logical replication
2. Create the replication user and grant all the required privileges
3. Create the PUBLICATION
4. Create a REPLICATION SLOT
5. Create a new RDS snapshot 
6. Upgrade the RDS snapshot to the target version
7. Restore the upgraded RDS snapshot
8. Get the LSN position 
9. Create the SUBSCRIPTION
10. Advance the SUBSCRIPTION 
11. Enable the SUBSCRIPTION

Source Database Side

1. Verify the PostgreSQL parameters for logical replication

We require the next PostgreSQL parameters for this exercise

demodb=> select name,setting from pg_settings where name in (
        'wal_level',
        'track_commit_timestamp',
        'max_worker_processes',
        'max_replication_slots',
        'max_wal_senders') ;
          name          | setting
------------------------+---------
 max_replication_slots  | 10
 max_wal_senders        | 10
 max_worker_processes   | 10
 track_commit_timestamp | on
 wal_level              | logical
(5 rows)

NOTE: The parameter track_commit_timestamp can be optional since in some environments is not advisable for the related overhead, but it would help to track and resolve any conflict that may occur when the subscriptions are started.

2. Create the replication user and grant all the required privileges

demodb=> CREATE USER pgrepuser WITH password 'SECRET';
CREATE ROLE
demodb=> GRANT rds_replication TO pgrepuser;
GRANT ROLE
demodb=> GRANT SELECT ON ALL TABLES IN SCHEMA public TO pgrepuser;
GRANT

3. Create the PUBLICATION

demodb=> CREATE PUBLICATION pglogical_rep01 FOR ALL TABLES;
CREATE PUBLICATION

4. Create a REPLICATION SLOT

demodb=> SELECT pg_create_logical_replication_slot('pglogical_rep01', 'pgoutput');
 pg_create_logical_replication_slot
------------------------------------
 (pglogical_rep01,3C/74000060)
(1 row)

AWS RDS Steps

5. Create a new RDS snapshot 

aws rds create-db-snapshot \
    --db-instance-identifier demodb-postgres\
    --db-snapshot-identifier demodb-postgres-to-125

6. Upgrade the RDS snapshot to the target version

aws rds modify-db-snapshot \
    --db-snapshot-identifier demodb-postgres-to-125 \
    --engine-version 12.5

7. Restore the upgraded RDS snapshot 

Since we are moving from version 11.9 to 12.5 we may need to create a new DB parameter group if we are using some custom parameters. 
From the instance describe we can verify the current parameter group

aws rds describe-db-instances \
        --db-instance-identifier demodb-postgres \| 
jq '.DBInstances | map({DBInstanceIdentifier: .DBInstanceIdentifier, DBParameterGroupName: .DBParameterGroups[0].DBParameterGroupName})'
[
  {
    "DBInstanceIdentifier": "demodb-postgres",
    "DBParameterGroupName": "postgres11-logicalrep"
  }
]

Then we can validate the custom parameters 

aws rds describe-db-parameters \
	--db-parameter-group-name postgres11-logicalrep \
	--query "Parameters[*].[ParameterName,ParameterValue]" \
	--source user --output text 
track_commit_timestamp	1

We need to create a new parameter group in the target version

aws rds create-db-parameter-group \
	--db-parameter-group-name postgres12-logicalrep \
	--db-parameter-group-family postgres12

Finally, we need to modify the parameters we got before in the new parameter group

aws rds modify-db-parameter-group \
	--db-parameter-group-name postgres12-logicalrep \
	--parameters "ParameterName='track_commit_timestamp',ParameterValue=1,ApplyMethod=immediate"

Now we can use the new parameter group to restore the upgraded snapshot

aws rds restore-db-instance-from-db-snapshot \
	--db-instance-identifier demodb-postgres-125 \
	--db-snapshot-identifier demodb-postgres-to-125 \
	--db-parameter-group-name postgres12-logicalrep

8. Get the LSN position from the target instance log

To list all the database logs for the new DB instance

aws rds describe-db-log-files \
	--db-instance-identifier demodb-postgres-125

We should pick the latest database log

aws rds download-db-log-file-portion \
	--db-instance-identifier demodb-postgres-125 \
	--log-file-name "error/postgresql.log.2021-03-23-18"

From the retrieved log portion we need to find the value after for the log entry redo done at:

...
2021-03-23 18:19:58 UTC::@:[5212]:LOG:  redo done at 3E/50000D08
...

Target Database Side

9. Create SUBSCRIPTION

demodb=> CREATE SUBSCRIPTION pglogical_sub01 CONNECTION 'host=demodb-postgres.xxxx.us-east-1.rds.amazonaws.com port=5432 dbname=demodb user=pgrepuser password=SECRET' PUBLICATION pglogical_rep01
WITH (
  copy_data = false,
  create_slot = false,
  enabled = false,
  connect = true,
  slot_name = 'pglogical_rep01'
);
CREATE SUBSCRIPTION

10. Advance the SUBSCRIPTION 

We need to get the subscription id

demodb=> SELECT 'pg_'||oid::text AS "external_id"
FROM pg_subscription 
WHERE subname = 'pglogical_sub01';
 external_id
-------------
 pg_73750
(2 rows)

Now advance the subscription to the LSN we got in step 8

demodb=> SELECT pg_replication_origin_advance('pg_73750', '3E/50000D08') ;
pg_replication_origin_advance
-------------------------------
(1 row)

11. Enable the SUBSCRIPTION

demodb=> ALTER SUBSCRIPTION pglogical_sub01 ENABLE;
ALTER SUBSCRIPTION

Once we are done with all the steps the data changes should flow from the source database to the target, we can check the status at the pg_stat_replication view. 

Conclusion

Choosing DBaaS from cloud vendors bring some advantages and can speed up some implementations, but they come with some costs, and not all the available tools or solutions fits all the requirements, that is why always is advisable to try some different approaches and think out of the box, technology can go so far as our imagination.