How to try Atomic Visibility

1. Download the latest version of the source code for postgres_fdw_plus and the latest version of the source code for PostgreSQL that postgres_fdw_plus supports.

   export WORKDIR=/workdir
   cd $WORKDIR
   git clone -b main --depth 1 https://github.com/pgfdwplus/postgres_fdw_plus.git
   git clone -b master --depth 1 https://github.com/postgres/postgres.git
   

2. Apply the patch (patch/0001-Add-GUC-parameter-for-Atomic-Visibility-feature.patch) that postgres_fdw_plus provides to PostgreSQL.

   cd postgres
   git am $WORKDIR/postgres_fdw_plus/patch/0001-Add-GUC-parameter-for-Atomic-Visibility-feature.patch
   

3. Build the patched PostgreSQL.

   ./configure --prefix=$WORKDIR/pgsql
   make -j 4
   make install
   

4. Build postgres_fdw_plus with the patched version of PostgreSQL.

   cd $WORKDIR/postgres_fdw_plus
   make USE_PGXS=1 PG_CONFIG=$WORKDIR/pgsql/bin/pg_config
   make USE_PGXS=1 PG_CONFIG=$WORKDIR/pgsql/bin/pg_config install
   

5. Create three new database clusters.

   export PATH=$PATH:$WORKDIR/pgsql/bin
   initdb -D $WORKDIR/data1 --locale=C --encoding=UTF8
   initdb -D $WORKDIR/data2 --locale=C --encoding=UTF8
   initdb -D $WORKDIR/data3 --locale=C --encoding=UTF8
   

6. Configure the settings for Atomic Visibility.

   cat <<EOF >> $WORKDIR/data1/postgresql.conf
   port = 5432
   max_prepared_transactions = 10
   log_line_prefix = '%t [data1][%b] '
   atomic_visibility = on
   EOF
   
   cat <<EOF >> $WORKDIR/data2/postgresql.conf
   port = 5433
   max_prepared_transactions = 10
   log_line_prefix = '%t [data2][%b] '
   atomic_visibility = on
   EOF
   
   cat <<EOF >> $WORKDIR/data3/postgresql.conf
   port = 5434
   max_prepared_transactions = 10
   log_line_prefix = '%t [data3][%b] '
   atomic_visibility = on
   EOF
   

7. Start those three PostgreSQL servers.

   pg_ctl -D $WORKDIR/data1 start
   pg_ctl -D $WORKDIR/data2 start
   pg_ctl -D $WORKDIR/data3 start
   

8. Register postgres_fdw_plus on each PostgreSQL server and configure the Atomic Visibility settings.

   for portnum in 5432 5433 5434; do
   cat <<EOF | psql -p $portnum
   CREATE EXTENSION postgres_fdw_plus;
   ALTER SYSTEM SET postgres_fdw.two_phase_commit TO on;
   ALTER SYSTEM SET postgres_fdw.use_read_committed TO on;
   SELECT pg_reload_conf();
   EOF
   done
   

9. Create foreign servers and user mappings to connect all three PostgreSQL servers with each other.

   for portnum in 5432 5433 5434; do
   cat <<EOF | psql -p $portnum
   CREATE SERVER data1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (port '5432');
   CREATE SERVER data2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (port '5433');
   CREATE SERVER data3 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (port '5434');
   CREATE USER MAPPING FOR public SERVER data1;
   CREATE USER MAPPING FOR public SERVER data2;
   CREATE USER MAPPING FOR public SERVER data3;
   EOF
   done
   

10. Create tables for pgbench benchmark.

    cat <<EOF | psql -p 5432
    CREATE TABLE pgbench_accounts (aid integer NOT NULL, bid integer, abalance integer, filler character(84)) PARTITION BY hash (aid);
    CREATE TABLE pgbench_accounts_data1 PARTITION OF pgbench_accounts (aid PRIMARY KEY) FOR VALUES WITH (MODULUS 3, REMAINDER 0);
    CREATE FOREIGN TABLE pgbench_accounts_data2_ft PARTITION OF pgbench_accounts FOR VALUES WITH (MODULUS 3, REMAINDER 1) SERVER data2 OPTIONS (table_name 'pgbench_accounts_data2');
    CREATE FOREIGN TABLE pgbench_accounts_data3_ft PARTITION OF pgbench_accounts FOR VALUES WITH (MODULUS 3, REMAINDER 2) SERVER data3 OPTIONS (table_name 'pgbench_accounts_data3');
    CREATE TABLE pgbench_branches (bid integer PRIMARY KEY, bbalance integer, filler character(88));
    CREATE TABLE pgbench_tellers (tid integer PRIMARY KEY, bid integer, tbalance integer, filler character(84));
    CREATE TABLE pgbench_history (tid integer, bid integer, aid integer, delta integer, mtime timestamp without time zone, filler character(22));
    EOF
    
    cat <<EOF | psql -p 5433
    CREATE TABLE pgbench_accounts (aid integer NOT NULL, bid integer, abalance integer, filler character(84)) PARTITION BY hash (aid);
    CREATE FOREIGN TABLE pgbench_accounts_data1_ft PARTITION OF pgbench_accounts FOR VALUES WITH (MODULUS 3, REMAINDER 0) SERVER data1 OPTIONS (table_name 'pgbench_accounts_data1');
    CREATE TABLE pgbench_accounts_data2 PARTITION OF pgbench_accounts (aid PRIMARY KEY) FOR VALUES WITH (MODULUS 3, REMAINDER 1);
    CREATE FOREIGN TABLE pgbench_accounts_data3_ft PARTITION OF pgbench_accounts FOR VALUES WITH (MODULUS 3, REMAINDER 2) SERVER data3 OPTIONS (table_name 'pgbench_accounts_data3');
    CREATE FOREIGN TABLE pgbench_branches (bid integer NOT NULL, bbalance integer, filler character(88)) SERVER data1 OPTIONS (table_name 'pgbench_branches');
    CREATE FOREIGN TABLE pgbench_tellers (tid integer NOT NULL, bid integer, tbalance integer, filler character(84)) SERVER data1 OPTIONS (table_name 'pgbench_tellers');
    CREATE FOREIGN TABLE pgbench_history (tid integer, bid integer, aid integer, delta integer, mtime timestamp without time zone, filler character(22)) SERVER data1 OPTIONS (table_name 'pgbench_history');
    EOF
    
    cat <<EOF | psql -p 5434
    CREATE TABLE pgbench_accounts (aid integer NOT NULL, bid integer, abalance integer, filler character(84)) PARTITION BY hash (aid);
    CREATE FOREIGN TABLE pgbench_accounts_data1_ft PARTITION OF pgbench_accounts FOR VALUES WITH (MODULUS 3, REMAINDER 0) SERVER data1 OPTIONS (table_name 'pgbench_accounts_data1');
    CREATE FOREIGN TABLE pgbench_accounts_data2_ft PARTITION OF pgbench_accounts FOR VALUES WITH (MODULUS 3, REMAINDER 1) SERVER data2 OPTIONS (table_name 'pgbench_accounts_data2');
    CREATE TABLE pgbench_accounts_data3 PARTITION OF pgbench_accounts (aid PRIMARY KEY) FOR VALUES WITH (MODULUS 3, REMAINDER 2);
    CREATE FOREIGN TABLE pgbench_branches (bid integer NOT NULL, bbalance integer, filler character(88)) SERVER data1 OPTIONS (table_name 'pgbench_branches');
    CREATE FOREIGN TABLE pgbench_tellers (tid integer NOT NULL, bid integer, tbalance integer, filler character(84)) SERVER data1 OPTIONS (table_name 'pgbench_tellers');
    CREATE FOREIGN TABLE pgbench_history (tid integer, bid integer, aid integer, delta integer, mtime timestamp without time zone, filler character(22)) SERVER data1 OPTIONS (table_name 'pgbench_history');
    EOF
    

11. Temporarily disable the Atomic Visibility settings for data loading and load the initial pgbench data.

    cat <<EOF | psql -p 5432
    ALTER SYSTEM RESET postgres_fdw.two_phase_commit;
    ALTER SYSTEM RESET postgres_fdw.use_read_committed;
    SELECT pg_reload_conf();
    EOF
    
    pgbench -i -IG -s 10 -p 5432
    
    cat <<EOF | psql -p 5432
    ALTER SYSTEM SET postgres_fdw.two_phase_commit TO on;
    ALTER SYSTEM SET postgres_fdw.use_read_committed TO on;
    SELECT pg_reload_conf();
    EOF
    

12. Run pgbench benchmark.

    pgbench -c 4 -j 4 -T 10