Row pattern recognition feature for PostgreSQL

What is row pattern recognition feature?

Row pattern recognition (RPR) is a feature defined in the SQL standard. It allows to search for a sequence of rows by pattern.  Since I am working on this feature for PostgreSQL, I would like to give a brief introduction to RPR.

 

Consider a table holding date and daily stock price of a company. 

 company  |   tdate    | price
----------+------------+-------
 company1 | 2024-07-01 |   100
 company1 | 2024-07-02 |   200
 company1 | 2024-07-03 |   150
 company1 | 2024-07-04 |   140
 company1 | 2024-07-05 |   150
 company1 | 2024-07-06 |    90
 company1 | 2024-07-07 |   110
 company1 | 2024-07-08 |   130
 company1 | 2024-07-09 |   120
 company1 | 2024-07-10 |   130
(10 rows)

Suppose you want to find a sequence of rows in which the stock price rises once or more then falls once or more. For example, the stock price on July 1 is 100, then rises to 200 on July 2 and then falls to 150 and 140 on July 2 to July 3. RPR allows users to write this kind of queries in intuitive way.

 

RPR syntax

 To express the query in RPR, you first define row pattern  variables.

 

DEFINE

START AS TRUE,

UP AS price > PREV(price),

DOWN AS price < PREV(price)

Here DEFINE is a keyword to start the definition. START, UP and DOWN are row pattern variable names. The right hand side of AS is logical expression which the pattern variable needs to satisfy. The logical expression "TRUE" means always true. PREV() is a special function that only used in RPR (cannot use elsewhere, for example SELECT target or WHERE clause).  PREV takes a column name as an argument, and returns the previous row's column value. So if today's price is higher than yesterday's price, "price > PREV(price)" returns TRUE. DOWN has opposite meaning.

Once the row pattern variables are defined, you can define patterns to search for by using the row pattern variables.

PATTERN (START, UP+, DOWN+)

Here PATTERN is a keyword to start the definition. START matches any row and this is the starting point of the pattern matching. If the next row satisfies UP, then more rows are examined if they satisfy the logical expression UP because '+' is added to UP. This is a similar concept of regular expressions. If any row does not satisfy UP, then DOWN is tested against the row. If it satisfies, more rows are examined like UP. If no row matches DOWN, then the search finishes. 

The RPR syntax

The explanation above is just a part of RPR syntax. Now let's see the whole RPR syntax.

 

 WINDOW window_name AS (
[ PARTITION BY ... ]
[ ORDER BY... ]
[ MEASURES ... ]
ROWS BETWEEN CURRENT ROW AND ...
[ AFTER MATCH SKIP ... ]
[ INITIAL|SEEK ]
PATTERN (...)
[ SUBSET ... ]
DEFINE ...
)

As may notice, this is an expansion to WINDOW clause. Actually there are two types of RPR defined in the SQL standard: R010 and R020. R020 is the syntax above. Since I am working on implementing R020 in PostgreSQL,  I focus on R202 in this article. Also current implementation does not support MEASURE and SUBSET, I will not explain these in the rest of this article. If you are interested in R010 or MEASURE, SUBSET, please look into the SQL standard.

 

Let's run RPR

If I implement the example above in the R020 syntax, the whole query and the result will look like this:

SELECT company, tdate, price,
 count(*) OVER w
 FROM stock
 WINDOW w AS (
 PARTITION BY company
 ORDER BY tdate
 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
 AFTER MATCH SKIP PAST LAST ROW
 INITIAL
 PATTERN (START UP+ DOWN+)
 DEFINE
  START AS TRUE,
  UP AS price > PREV(price),
  DOWN AS price < PREV(price)
);

 
 company  |   tdate    | price | count
----------+------------+-------+-------
 company1 | 2024-07-01 |   100 |     4
 company1 | 2024-07-02 |   200 |     0
 company1 | 2024-07-03 |   150 |     0
 company1 | 2024-07-04 |   140 |     0
 company1 | 2024-07-05 |   150 |     0
 company1 | 2024-07-06 |    90 |     4
 company1 | 2024-07-07 |   110 |     0
 company1 | 2024-07-08 |   130 |     0
 company1 | 2024-07-09 |   120 |     0
 company1 | 2024-07-10 |   130 |     0
(10 rows)

As we already see the stock price on July 1 is 100, then rises to 200 on July 2 and then falls to 150 and 140 on July 2 to July 3. The count column shows that the query found 4 matching rows. The reason why the count column for July 2 to 4 are 0 is they are skipped and are not evaluated. This behavior comes from "AFTER MATCH SKIP PAST LAST ROW" clause: as 4 rows are found to be matched, we skip 4 rows and restart next pattern matching from July 5. Since stock price on July 6 is 90, the pattern matching fails. Thus the count column is 0.

 

And next pattern match starts at July 6. Now we find 4 matching rows.

 

Next pattern matching  starts at July 10. The pattern match needs at least 3 rows. However there's only 1 ow remains in the partition, patter matching fails.

 

Variation of pattern

 The pattern matching can be easily extended. For example you can add more constraint to the pattern like "not only the stock price rises but the range is more than 50". In this case you can change the DEFINE clause as follows:

 

 DEFINE
  START AS TRUE,
  UP AS price > PREV(price) + 50,
  DOWN AS price < PREV(price)

 

 With this change we get this:

 company  |   tdate    | price | count
----------+------------+-------+-------
 company1 | 2024-07-01 |   100 |     4
 company1 | 2024-07-02 |   200 |     0
 company1 | 2024-07-03 |   150 |     0
 company1 | 2024-07-04 |   140 |     0
 company1 | 2024-07-05 |   150 |     0
 company1 | 2024-07-06 |    90 |     0
 company1 | 2024-07-07 |   110 |     0
 company1 | 2024-07-08 |   130 |     0
 company1 | 2024-07-09 |   120 |     0
 company1 | 2024-07-10 |   130 |     0
(10 rows)

Please note that July 6 row does not match any more.

What's in the current implementation?

I have been working on this feature and publishing patches on pgsql-hackers mailing list.  The whole discussion can be found at [1]. The latest patch can be found near the end of the thread. At this point the supported features in the patch are explained in [2]. If you are interested more examples, probably the regression test result in the patch is what you want to check (src/test/regress/expected/rpr.out).

How to test the patch?

Since the patch is against PostgreSQL git repository master branch, you need to clone the git repository first.

git clone https://git.postgresql.org/git/postgresql.git

 

Save the patch somewhere. Suppose you save in /tmp/rpr. 

git apply /tmp/rpr/*.patch

Then build PostgreSQL as usual. Since the patch changes some system catalogue, you need to create a fresh database cluster by using initdb.

If you find something with the patch or have comments/suggestions, please post to [1].

Also you can watch a talk I gave at PGConf.dev 2024 [3] and the slide[4].

[1]  https://www.postgresql.org/message-id/flat/20230625.210509.1276733411677577841.t-ishii%40sranhm.sra.co.jp

 

[2] https://www.postgresql.org/message-id/20240613.092501.1861371988766701762.t-ishii%40sranhm.sra.co.jp

 

[3]  https://youtu.be/mPcmqi-6cGU

 

[4]  https://www.sraoss.co.jp/wp-content/uploads/files/event_seminar/material/2024/2024-05-29-PGConf.dev-RPR.pdf

Speeding up pgbench using COPY FREEZE

 

Photo by Florian Kurz

 What is pgbench

Pgbench is a simple tool to perform performance tests on PostgreSQL. Since pgbench comes with PostgreSQL distribution and it's easy to use, many users and developers regularly use pgbench.  The original version of pgbench was written by me in 1999 and then it was contributed to PostgreSQL. Since then many people have enhanced the tool.

Loading data takes long time

Pgbench has a feature to load initial data into the benchmark tables (-i or --initialize option). By specifying scaling factor (-s option) users can create data as large as he/she wants. For example scaling factor 100 will create 10 millions rows. When I execute pgbench command with the scaling factor 100, it took 70 seconds on my laptop. You can see how long  each part of the process took:

 done in 70.78 s (drop tables 0.21 s, create tables 0.02 s, client-side generate 12.42 s, vacuum 51.11 s, primary keys 7.02 s).

  As you can see, most of the time was spent in vacuum: 51.11 seconds.

What "pool" means in Pgpool-II?

 

Photo by Prachya Singhto

 Pgpool-II started its history as a connection pooler

The word "pool" in Pgpool-II had traditionally meant "connection pooling".  Actually the software was born as a connection pooling software in 2003. However after 18 years passed Pgpool-II is not only a connection pooling software anymore. Rather, the connection pooling feature is just one of rich features of Pgpool-II.

 More feature has been added to Pgpool-II

 In 2004 native replication feature (statement based replication) was added. In 2010 PostgreSQL's streaming replication can be managed. In 2012 Watchdog, a high availability feature for Pgpool-II itself, has been added.  Moreover automatic failover and load balancing are possible.

Now "pool" means "pool of PostgreSQL"

With all these feature now Pgpool-II is effectively a PostgreSQL cluster management software. So I decided to redefine the meaning of "pool" to be "pool of PostgreSQL servers". In this direction I have updated the intro chapter of PostgreSQL manual for the next release (Pgpool-II 4.3).

Pgpool-II manages a pool of PostgreSQL servers to achieve some features that are not available with single PostgreSQL installation.

An HTML compiled version of the chapter can be viewed here.

I hope this change gives people more appropriate view of this software.

Using IVM in a docker container

Real time, fast update of materialized views

 In  this blog entry I have introduced the ongoing project: Incremental View Maintenance (IVM), which allows to real time, fast update of materialized views. Since the bolg, IVM now can handle outer joins and self joins. The out come of the project is in public in a form of set of patches against git master branch of PostgreSQL. Also a GitHub repository for this project is in public as well.

Docker image for IVM

So skilled PostgreSQL developers can test IVM today. However it's not that easy for people who do not regularly compile and build PostgreSQL and IVM from source code. Fortunately one of IVM developers "Yugo Nagata" comes up and starts to provide complete docker image of IVM. I am going to demonstrate how to use it step by step.

# Pull docker image  

docker pull yugonagata/postgresql-ivm 

 

 # Run docker container using port mapping 15432:5432 

docker run -d --name pgsql-ivm -e POSTGRES_PASSWORD=postgres -p 15432:5432 yugonagata/postgresql-ivm

 

 # Run psql to connect to PostgreSQL to create database named "test"

docker run -it --rm --link pgsql-ivm yugonagata/postgresql-ivm psql -h pgsql-ivm -U postgres 

# create database test; 

\q

# Run psql to run pgbench
docker run -it --rm --link pgsql-ivm yugonagata/postgresql-ivm pgbench -i -h pgsql-ivm -U postgres test

# Run psql to try IVM
docker run -it --rm --link pgsql-ivm yugonagata/postgresql-ivm psql -h pgsql-ivm -U postgres test

# Create a materialized view "mv1" for query "select count(*) from pgbench_accounts.

# Note that "pgbench_accounts" was created by pgbench
test=# create incremental materialized view mv1 as select count(*) from pgbench_accounts;
SELECT 1

# select the result from the view
test=# select * from mv1;
 count  
--------
 100000
(1 row)

 # Start a transaction

 test=# begin;
BEGIN

# delete 1 row from pgbench_accounts table
test=*# delete from pgbench_accounts where aid = 1;
DELETE 1

# Check to see if mv1 reflects the deletion
test=*# select * from mv1;
 count
-------
 99999
(1 row)

# Abort the transaction
test=*# abort;
ROLLBACK

# The deletion was cancelled in mv1 as well
test=# select * from mv1;
 count  
--------
 100000
(1 row)

Summary

IVM has been continuously developed. Now it can be easily tested in a docker container. Please try it out!

 

New statistics data in Pgpool-II

 

Upcoming Pgpool-II 4.2 will provide new statistics data such as number of INSERT/DELETE/UPDATE etc.  to you. In this blog I will show you how it will look like.

Existing statistics

Before jumping into the new feature, let's take a look at existing statistics data. PostgreSQL already gives you tuple access data like number of tuples inserted in a database (see pg_stat_database manual). Before Pgpoool-II 4.1 or before gives number of SELECT sent to PostgreSQL in "show pool_nodes" command.

How is like the new statistics?

A new command called "show backend_stats" provides the statistics data like number of NSERT, UPDATE and DELETE issued since Pgpool-II started up. So it's not like PostgreSQL's pg_stat_database in that PostgreSQL provides number of tuples accessed, while Pgpool-II provides number SQL commands issued.  If you want number of tuples based statistics you can always access PostgresQL's statistics data.

 Here is an example of the new statistics data:

test=# show pool_backend_stats;
 node_id | hostname | port  | status |  role   | select_cnt | insert_cnt | update_cnt | delete_cnt | ddl_cnt | other_cnt | panic_cnt | fatal_cnt | error_cnt 
---------+----------+-------+--------+---------+------------+------------+------------+------------+---------+-----------+-----------+-----------+-----------
 0       | /tmp     | 11002 | up     | primary | 12         | 10         | 30         | 0          | 2       | 30        | 0         | 0         | 1
 1       | /tmp     | 11003 | up     | standby | 12         | 0          | 0          | 0          | 0       | 23        | 0         | 0         | 1
(2 rows) 

select_cnt, insert_cnt, update_cnt, delete_cnt, ddl_cnt, other_cnt are the numbers of SQL command issued since Pgpool-II started up. What is "other_cnt"? Well, it is a count of: CHECKPOINT/DEALLOCATE/DISCARD/EXECUTE/EXPLAIN/LISTEN/LOAD/LOCK/NOTIFY/PREPARE/SET/SHOW/Transaction commands/UNLISTEN. If an SQL does not belong to SELECT/INSERT/UPDATE/DELETE/other, then it is counted as DDL.

Please note that these counts are counted up even if an SQL command fails or roll backed.

panic_cnt, fatal_cnt and error_cnt are number of errors returned from PostgreSQL.  They are counts of PANIC, FATAL or ERROR. These error statistics is handy because PostgreSQL does not provide error statistics.

Summary

The "show pool_backend_stats" gives you at a glance view of whole cluster statistics on number SQL commands issued. Moreover, error counts, which does not exists in PostgreSQL could be useful for those who are interested in database or application failures.

Snapshot Isolation Mode

Pgpool-II 4.2 is under development

Pgpool-II developers have been working hard for upcoming Pgpool-II 4.2 release, which is supposed to be released around this September or October.

Snapshot isolation mode is coming

For the new release I have just added new clustering mode called "Snapshot isolation mode", which would be not only interesting for Pgpool-II users but PostgreSQL users and I think I decided to write about it in this blog.

Existing clustering mode

Until Pgpool-II 4.1, we have following clustering mode:

• Streaming replication mode. This is most widely used at this point. PostgreSQL's streaming replication is used for the replication task in this mode.
• Native replication mode. Until streaming replication is available, this was the most common setting. In this mode Pgpool-II issues same data modifying statements (DML/DDL) to all the PostgreSQL servers. As a result same data is replicated in all PostgreSQL servers.
• Slony mode. In this mode replication is done by Slony-I instead of streaming replication. This had been popular until streaming replication came up.
• Raw mode. No replication is done in this mode. Rarely used.

The new "Snapshot isolation mode" is pretty close to the existing native replication mode. It does not use the streaming replication at all. Replication is done by sending DML/DDL statements to all the PostgreSQL servers.  If so, what's the difference?

Atomic visibility

Before explaining that I would like to talk about the background a little bit.

PostgreSQL developers has been discussing about technology which can scale to handle large data. One of such a technical directions is extending FDW (Foreign Data Wrapper). It sends query to another PostgreSQL server to replicate or shard data. The idea is similar to the native replication mode in Pgpool-II because it also sends DML/DDL to servers to replicate data.

As for replicating data, they look good so far. However, there is a problem when multiple sessions try to access data at the same time.

Suppose we have 2 sessions: session 1 continuously executes UPDATE t1 SET i = i +1; session 2 continuously executes INSERT INTO log SELECT * FROM t1. Table t1 has only 1 row and it's initial value is 0 (actually each UPDATE and INSERT are executed in an explicit transaction).

So after session 1 and session 2 end, table "log" should have exactly same sequential numbers like "0, 1, 2, 3...." on both PostgreSQL servers. But see the example session below:

Session 1 executed UPDATE at t1 on servers1, while on server 2 UPDATE was executed on t2. Session 2 executed INSERT at t3 with value 1. Unfortunately on server2 INSERT was executed at t3, and t1 was not updated yet thus the value used was 0.

So after the sessions end, we will see different sequences of numbers in log table on server1 and server2. If we do not have atomic visibility this kind of data inconsistency could happen. Atomic visibility guarantees that value can be seen on server1 and server2 are same as long as they are executed in a same session. In the example above, data logged into log table would be either 0 (t1 has not been updated/committed yet) or 1 (t1 has been already updated/committed).

Snapshot isolation mode guarantees atomic visibility

The upcoming snapshot isolation mode is different from existing native replication mode in that it guarantees atomic visibility. So we don't need to worry about such that data inconsistency in the example above. In fact we have new regression to test the new snapshot isolation mode by using essentially same SQL used in the example above. Please note that in order to use snapshot isolation mode, the transaction isolation mode must be "REPEATABLE READ". (Postgres FDW also runs foreign transactions in this mode).

How to achieve atomic visibility?

PostgreSQL developers already recognize the necessity of atomic visibility. Postgres-XC, a fork of PostgreSQL, invented Global Transaction Manager to achieve atomic visibility. Some PostgreSQL developers are proposing CSN (Commit Sequence Number) approach. However these approch needs major surgery in PostgreSQL code.

Pgpool-II uses completely different approach, which is called "Pangea" proposed in an academic paper. With Pangea, no modification is necessary with PostgreSQL. Even you can use older versions of PostgreSQL like 8.0. So you can enjoy snapshot isolation mode of Pgpool-II today with your favorite version of PostgreSQL.

In Pagea the idea is called Global Snapshot Isolation, which extends snapshot isolation of local server to across servers. That's the reason why we call the new clustering mode as "Snapshot Isolation mode".

How Pangea works?

 With Pangea each first command in a transaction needs to wait if other transactions are trying to commit. If there are no commit is ongoing, the command will get snapshot which defines the visibility of the transaction. Since local transactions on each server acquire snapshots while there are no committing transactions, they can be regarded as consistent. In the example above, the INSERT command in session 2 will be forced to wait until t4, and at that time session 1 commits. Thus each INSERT command in session 2 will see "1" in table t1. Same thing can be said to the commit. Commit must wait if there's ongoing snapshot acquisition.

Summary

Pgpool-II 4.1 will come with new clustering mode called "Snapshot Isolation Mode" which guarantees the atomic visibility and is not provided in current standard PostgreSQL. Unlike other techniques to implement the atomic visibility, Pgpool-II does not require any modification to PostgreSQL. Also it can be used even with older version of PostgreSQL. Stay tuned.

Automatically updating materialized views

Materialized views are convenient and efficient way to retrieve information from database. Unlike ordinary views, materialized views save the query result and provide faster access to the data. For example, user can create a simple materialized view containing the number of rows in a table:

 CREATE MATERIALIZED VIEW mv1 AS SELECT count(*) FROM pgbench_accounts;

Obtaining the number of rows using the materialized view mv1 is much faster than  directly accessing number of rows in pgbench_accounts.

test=# SELECT * from v1;
 count 
--------
 100000
(1 row)

Time: 0.810 ms
test=# SELECT count(*) FROM pgbench_accounts;
 count 
--------
 100000
(1 row)

Time: 13.945 ms

However if you delete a row from pgbench_accounts, the number of rows in mv1 is still unchanged. To reflect the change of the base table (in this case pgbench_accounts) , you need to recreate or refresh (this actually recreate the contents of materialize views from scratch), which may take long time.

To overcome the problem, SRA OSS is proposing to add a new feature to existing materialized view  "incremental materialized view maintenance". This works like this.

• add new syntax to allow incrementally update the materialized view when it is created.

• install triggers automatically to be attached to the base table(s).

• when UPDATE/DELETE/INSERT applies on the base tables, the trigger calculate the difference to the rows in the materialized view and update the rows.

These should update the materialized view in a real time manner. The price is the over head of trigger invocation. So frequently updating tables are not best suite for the incremental materialized view maintenance. Here are demonstrations how to use the incrementally updating materialized view.

First create incrementally updating materialized view.

CREATE INCREMENTAL MATERIALIZED view mv2 AS SELECT count(*) FROM pgbench_accounts;

The view initially says there are 100,000 rows.

SELECT * FROM mv2;
 count 
--------
 100000
(1 row)

Delete a row from pgbench_accounts. Now the number of rows is 99999.

DELETE FROM pgbench_accounts WHERE aid = 10;
DELETE 1
SELECT count(*) FROM pgbench_accounts;
 count
-------
 99999
(1 row)

Make sure that mv2 reports the same number of rows.

SELECT * FROM mv2;
 count
-------
 99999
(1 row)

It works!

This is a very simple materialized view. More complex queries, for example inner joins also work.

CREATE INCREMENTAL MATERIALIZED VIEW mv3 AS SELECT a.aid, b.bid, t.tid FROM pgbench_accounts a INNER JOIN pgbench_branches b ON (a.bid = b.bid ) INNER JOIN pgbench_tellers t ON (b.bid = t.bid) WHERE a.aid BETWEEN 1 AND 5;
SELECT 50

SELECT * FROM mv3 LIMIT 5;
 aid | bid | tid
-----+-----+-----
   3 |   1 |  10
   2 |   1 |   1
   2 |   1 |   5
   2 |   1 |   4
   5 |   1 |   3
 (5 rows)

DELETE FROM pgbench_accounts WHERE aid = 2;
DELETE 1

SELECT * FROM mv3 LIMIT 5;
 aid | bid | tid
-----+-----+-----
   3 |   1 |  10
   5 |   1 |   3
   1 |   1 |   1
   5 |   1 |   8
   4 |   1 |   3
(5 rows)

Implementing other types of queries such as outer joins, self joins are in our plan, possibly toward PostgreSQL 13.

Here are some pointers to information regarding our incremental materialized view maintenance project:

• Project wiki page

• Discussion in pgsql-hacers

• Github

Pgpool-II 4.0 released! (SCRAM authentication)

After 1 year of hard work by Pgpool-II developers,  Pgpool-II 4.0 is finally out.
This version celebrates the 15th anniversary of Pgpool-II, and introduces rich new features. I will explain them one by one in a series of blogs.

Support for SCRAM authentication

SCRAM authentication is a modern, secure authentication method supported by PostgreSQL 10 or later. Pgpool-II 4.0 supports the method by providing multiple new functionalities.

• pool_hba.conf now has "scram-sha-256" authentication method.
• If this method is specified, users connecting to Pgpool-II are required  to response in proper way of SCRAM authentication.
• Passwords stored in pool_passwd can be used to verify user's password before a session starts.
• Passwords can be store in pool_passwd in multiple ways: clear text or AES256 encrypted (md5 hashed passwords can also be stored in pool_passwd but in this case SCRAM cannot use the password).
• AES256 encryption is preferred and the key to decrypt it is stored in .pgpoolkey under the user's home directory who started Pgpool-II, or a file specified by PGPOOLKEYFILE environment variable.
• For AES256 encryption, new tool pg_enc can be used.

Of course, Pgpool-II can authenticate itself in SCRAM while connecting to PostgreSQL, if it requires SCRAM authentication to Pgpool-II.

Good thing with Pgpool-II 4.0 is, it allows users to choose different authentication method in users vs. Pgpool-II  and Pgpool-II vs. PostgreSQL. For example, using SCRAM between users and Pgpool-II, while using md5 authentication method between Pgpool-II and PostgreSQL.

SCRAM authentication can also be used for health check and replication delay check.  Before 4.0, user name and password for them are stored in clear text format in pgpool.conf. This is not the most secure way. Pgpool-II 4.0 allows to store AES 256 encrypted passwords in pgpool.conf. .pgpookey is also used for decrypting these passwords.

Next time I will explain about other features of authentication method in Pgpool-II 4.0.

More load balancing fine control

Pgpool-II is known as it provides load balancing across multiple PostgreSQL servers for read queries.

Consider a cluster consisting of Pgpool-II and two PostgreSQL servers configured with streaming replication. One is a primary server, the other is a standby server. Also the load balancing weight is 0:1, which means read queries will be basically sent to the standby server.

In an explicit transaction if a read query is sent from a client, Pgpool-II sends it to the standby server (as long as replication delay is not too much). Next a write query is coming. Of course it is sent to the primary server. Interesting thing happens if a read query is sent. Unlike previous read query, it is sent to the primary to avoid the replication delay.

Pgpool-II 4.0 will have a new configuration parameter called "disable_load_balance_on_write" to give more fine control of the behavior above.

If disable_load_balance_on_write = 'transaction', the behavior is exactly same as above. This is the default.

If disable_load_balance_on_write = 'off', Pgpool-II no longer takes account of the write query and feels free to load balance read queries as much as possible.

 This choice is good if you want to take maximum benefit of load balancing.

The variant of 'tranacton' is, 'trans_transaction'. Unlike 'transaction', the effect of disable load balancing after a write query continues even if the transaction closes and a next transaction begins.

So 'trans_transaction' is good for those who want data consistency than load balancing. Please note that a ready query issued between explicit transactions are not affected by the parameter.

Finally there's 'always'. If the mode is specified and once write query is sent, any read query regardless it's in an explicit transaction or not, will not be load balanced and sent to the primary server until the session ends.

In summary, the degree of protecting data consistency is in the order of always, trans_transaction, transaction and off. In contrast, the degree of performance is in the order of off, transaction, trans_transaction and always. If you are not sure which one is the best choice for you, I recommend you to start with 'transaction' because this is on a good balance between data consistency and performance.

Creating regression test suite for pgpool-II

Having a regression test suite has been a long standing TODO item. Now that we have pgpool_setup, I started to think about making it real. Today I think I finally made it.

The idea behind it is

• create required pgpool cluster (pgpool + PostgreSQL instances) by using pgpool_setup
• each test case is a simple shell script
• each script returns 0 or 1 according to the test result

Sounds too simple? Yes, I admit. However it works as expected.

$ sh regress.sh
testing 001.load_balance...ok.
testing 002.native_replication...ok.
testing 003.failover...ok.
testing 050.bug58...ok.
testing 051.bug60...ok.
testing 052.do_query...ok.
testing 053.insert_lock_hangs...ok.
out of 7 ok:7 failed:0

For example, "001.load_balance" checks whether load balancing is working against 2 node native replication or streaming replication cluster by using this simple script:

#! /bin/sh
#-------------------------------------------------------------------
# test script for load balancing.
#
source $TESTLIBS
TESTDIR=testdir

for mode in s r
do
    rm -fr $TESTDIR
    mkdir $TESTDIR
    cd $TESTDIR

# create test environment
    echo -n "creating test environment..."
    sh $PGPOOL_SETUP -m $mode -n 2 || exit 1
    echo "done."

    source ./bashrc.ports

    echo "backend_weight0 = 0" >> etc/pgpool.conf
    echo "backend_weight1 = 1" >> etc/pgpool.conf
    echo "black_function_list = 'f1'" >> etc/pgpool.conf

    ./startall

    export PGPORT=$PGPOOL_PORT

    wait_for_pgpool_startup

    psql test <<EOF
CREATE TABLE t1(i INTEGER);
CREATE FUNCTION f1(INTEGER) returns INTEGER AS 'SELECT \$1' LANGUAGE SQL;
SELECT * FROM t1;        -- this load balances
SELECT f1(1);        -- this does not load balance
EOF

# check if simle load balance worked
    fgrep "SELECT * FROM t1;" log/pgpool.log |grep "DB node id: 1">/dev/null 2>&1
    if [ $? != 0 ];then
    # expected result not found
        ./shutdownall
        exit 1
    fi

# check if black function list worked
    fgrep "SELECT f1(1);" log/pgpool.log |grep "DB node id: 0">/dev/null 2>&1
    if [ $? != 0 ];then
    # expected result not found
        ./shutdownall
        exit 1
    fi

    echo "white_function_list = 'f1'" >> etc/pgpool.conf
    echo "black_function_list = ''" >> etc/pgpool.conf

    ./pgpool_reload

    psql test <<EOF
SELECT f1(1);        -- this does load balance
EOF

# check if white function list worked
    fgrep "SELECT f1(1);" log/pgpool.log |grep "DB node id: 1">/dev/null 2>&1
    if [ $? != 0 ];then
    # expected result not found
        ./shutdownall
        exit 1
    fi

    ./shutdownall

    cd ..

done

exit 0

I hope pgpool developers would add more test cases.

Setting up everything at one time

While developping pgpool-II 3.3, I created a small script to make developer's life a little bit easier. However, the tool might be useful for PostgreSQL/pgpool-II beginners. So I decided to introduce it in my blog.

Disclaimer: the environment created by the tool below should never be used for production.

The script is called "pgpool_setup".  It creates followings by just one command under current directory. The directory must be empty. PostgreSQL and pgpool binaries must be installed and in your command search path beforehand.

• PostgreSQL clusters. You can choose "streaming replication mode" or "native replication mode". The former you already know, I assume. The latter is just a set of identical PostgreSQL clusters except the port numbers are different.
• If you choose streaming replication mode, one primary server and standby servers are ready after the command runs (even with WAL archival is enabled).
• By default those PostgreSQL cluster port number start from 11000 and continues 11001, 11002 and so on.
• The port numbers of pgpool and pcp follow.
• One pgpool server, with failover/online recovery/followmaster command are all set.
• A test database "test" is created.
• pcp commands(which are for pgpool administrator) account and password are ready for you.

By running pgpool_setup, you are ready to start pgpool and PostgreSQL with complete settings (at least for testing pupose) on your computer. I use this tool to create multiple groups  of pgpool+PostgreSQL set on my laptop.

So here's a sample session.

$ pgpool_setup
Satrting set up in streaming replication mode
creating startall and shutdownall
creating failover script
creating database cluster /home/t-ishii/work/git.postgresql.org/tmp2/data0...done.
update postgreql.conf
creating pgpool_remote_start
creating basebackup.sh
creating recovery.conf
creating database cluster /home/t-ishii/work/git.postgresql.org/tmp2/data1...done.
update postgreql.conf
creating pgpool_remote_start
creating basebackup.sh
creating recovery.conf
temporarily start data0 cluster to create extensions
temporarily start pgpool-II to create standby nodes
waiting for failover happens...done.
 node_id | hostname | port  | status | lb_weight |  role   
---------+----------+-------+--------+-----------+---------
 0       | /tmp     | 11000 | 2      | 0.500000  | primary
 1       | /tmp     | 11001 | 3      | 0.500000  | standby
(2 rows)

recovery node 1...done.
creating follow master script
Pager usage is off.
 node_id | hostname | port  | status | lb_weight |  role   
---------+----------+-------+--------+-----------+---------
 0       | /tmp     | 11000 | 2      | 0.500000  | primary
 1       | /tmp     | 11001 | 2      | 0.500000  | standby
(2 rows)

shutdown all

pgpool-II setting for streaming replication mode is done.
To start the whole system, use /home/t-ishii/work/git.postgresql.org/tmp2/startall.
To shutdown the whole system, use /home/t-ishii/work/git.postgresql.org/tmp2/shutdownall.
pcp command user name is "t-ishii", password is "t-ishii".
Each PostgreSQL, pgpool-II and pcp port is as follows:
#1 port is 11000
#2 port is 11001
pgpool port is 11002
pcp port is 11003
The info above is in README.port.
$ ls
README.port  bashrc.ports*  data1/  log/              run/          startall*
archivedir/  data0/         etc/    pgpool_setup.log  shutdownall

# So here you can see PostgreSQL cluster 0 as "data0" and cluster 1 as
# "data1".  You can start everything (pgpool and PostgreSQL) by using
# "startall", which is creaeted by the comand.

$ sh startall
waiting for server to start.... done
server started
waiting for server to start.... done
server started

# Make sure primary and standby are running.  "show pool_nodes" shows
# the info. Port 11002 is the port for pgpool.  As you can see
# primary's port is 11000 and standby's port is 11001.
# Status "2" means up and running.

$ psql -p 11002 -c 'show pool_nodes' test
 node_id | hostname | port  | status | lb_weight |  role   
---------+----------+-------+--------+-----------+---------
 0       | /tmp     | 11000 | 2      | 0.500000  | primary
 1       | /tmp     | 11001 | 2      | 0.500000  | standby
(2 rows)

# Now let's make sure that streaming replication is activated.

$ psql -p 11002 test
Pager usage is off.
psql (9.3beta1)
Type "help" for help.

test=# create table t1(i int);
CREATE TABLE
test=# insert into t1 values(1);
INSERT 0 1
test=# select * from t1;
 i 
---
 1
(1 row)

test=# \q

# Connect to standby.
$ psql -p 11001 test
Pager usage is off.
psql (9.3beta1)
Type "help" for help.

test=# select * from t1;
 i 
---
 1
(1 row)

test=# \q

# Ok, "t1" is propagated.

# Next test failover. So shutdown standby.

$ pg_ctl -D data1 -m f stop
waiting for server to shut down.... done
server stopped
$ psql -p 11002 -c 'show pool_nodes' test
 node_id | hostname | port  | status | lb_weight |  role   
---------+----------+-------+--------+-----------+---------
 0       | /tmp     | 11000 | 2      | 0.500000  | primary
 1       | /tmp     | 11001 | 3      | 0.500000  | standby
(2 rows)

# Note that status "3" means down.

# Let's stary standby and make it online.
$ pg_ctl -D data1 start
server starting
$ pcp_attach_node 1 localhost 11003 t-ishii t-ishii 1
$ psql -p 11002 -c 'show pool_nodes' test
 node_id | hostname | port  | status | lb_weight |  role   
---------+----------+-------+--------+-----------+---------
 0       | /tmp     | 11000 | 2      | 0.500000  | primary
 1       | /tmp     | 11001 | 2      | 0.500000  | standby
(2 rows)

# Next test is promotion. Let's stop primary and see if standby promotes.

$ pg_ctl -D data0 -m f stop
waiting for server to shut down.... done
server stopped

# Wait for while to let our failover script and follow master script do the job.

$ psql -p 11002 -c 'show pool_nodes' test
 node_id | hostname | port  | status | lb_weight |  role   
---------+----------+-------+--------+-----------+---------
 0       | /tmp     | 11000 | 2      | 0.500000  | standby
 1       | /tmp     | 11001 | 2      | 0.500000  | primary
(2 rows)

# Ok, now node 0 is standby, and 1 is primary. 

pgpool_setup is available in Pgpool-II 3.4 or later.

Larger large objects

Large objects (BLOBs) have been there since PostgreSQL was born. The size limit of a large objects has been 2GB(assuming default block size) . Now I decide to expand the limit for PostgreSQL 9.3: 4TB is the target. Actually PostgreSQL backend could hold up to 4TB large objects. It has been just limitation of API: for example, lo_lseek() and lo_tell cannot return over 2GB offset. Those function's return type is "int". So you might wonder why not they cannot return over 2GB value? Well, the secret is frontend/backend protocol for large object.

The underlying protocol is called "fast path interface". It's similar to RPC(Remote Procedure Call). Client sends "Function call" packet along with target function OID(Object Id). The called function within backend is executed and the result is returned back through "Function call response".

The functions called in large object interface are:

• lo_open
• lo_close
• lo_creat
• lo_create
• lo_unlink
• lo_lseek
• lo_tell
• lo_truncate
• loread
• lowrite

Those functions OIDs are retrieved from backend at the first time when accessing large objects and they are cached in the connection handle(I'm talking about libpq and other interfaces such as JDBC's implementation might be different).

Problem is, lo_lseek and lo_tell as I said earlier. First, their offset parameter is defined 4 bytes long. Second their result length is defined as 4 byte long. So we can handle only up 2^31-1 = 2GB. What shall we do? Well, we will add new function in backend namely, lo_lseek64 and lo_tell64. Libpq will check if those 64-bit functions exist. If yes, then use them. Otherwise (that means backend is likely pre-9.3 version) we use plain old 32-bit limited lo_lseek and lo_tell. This way, we do not break backward compatibility. Of course you need to use 9.3 libpq to enjoy "larger large objects".

I hope I'm going to post the first cut of patch by in September.

Visiting Brussels

After visiting Paris, I moved to Brussels to join FOSDEM, the largest open source conference in Europe.

I gave a 45-minute talk abut PostgreSQL and pgpool-II.

After finishing the talk we enjoyed a small tour of the beautifull city.


This is the famous "Grand Place".


There are nice Art Nouveau caffes in the city.

I bought some laces in a old lace shop. They are hand-made and very nice.

There is a great chuch which has a big pipe organ. I was so impressed and bought a CD, recorded the acuall play of the organ!

Playing with PHP PDO

PHP PDO(PHP Data Object) allows you to access database including PostgreSQL within PHP scripts. Good thing with PDO is, you do not need to heavily modify your PHP script even if you change the database product you want to use. This resembles to JDBC driver or Perl DBI. Moreover you could use database product dependent interface. Which means you can enjoy the full power of PostgreSQL with sacrificing portability of course. There's no free lunch. In summary, PHP PDO is great and I would like to recommend to anyone who is thinking about serious database programming in PHP.

One day I came across a complaint from pgpool-II user. According to him, his PHP client was blocked by pgpool-II while trying to connect to it. Ok, this is a typical situation when user misconfigured num_init_children and I thought he tried to connect pgpool-II concurrently with more than num_initi_children sessions. But his answer is, no. I suspected the PDO internal but I was too lazy to look into PDO source code. Instead I use strace to see what PDO is doing. To make the long story short, PDO releases the connection to pgpool-II long after "$dbh = null;"($dbh is the "handle" of the connection. This should release the connection). So if his script continues and connects to pgoool-II again after he thinks that he releases the connection, the script actually has two concurrent connections to pgpool-II. Of course this will create more connections than he thought.

I think the reason why many people do not come up with similar complaint is, their script usually create just one connection and release it at the end of the script.

Tutorial to pgpool-II plus Streaming replication

I have written a tutorial to pgpool-II using PostgreSQL 9.0's streaming replication. The tutorial is designed to use minimum resource: a Linux box. On the box, pgpool-II, pgpoolAdmin(a PHP based pgpool-II GUI admin tool) and two PostgreSQL 9.0 instances.

Though the tutorial system is surprisingly simple, you could learn:

- How pgpool-II detects PostgreSQL failure and automatically promote a standby server to primary server

- How pgpool-II re-sync the failed node by using "online recovery" capability

Please enjoy!

PostgreSQL 9.0 and pgpool-II endurance test

I have started to run an endurance test of PostgreSQL 9.0beta4 and pgpool-II 3.0-dev on Aug 12 17:47.
The hardware: two dual-core opteron 2.2GHz, 2GB mem.
One is used for the streaming replication primary server and the other is used for the standby server.
The load is provided by pgbench -N -c 10 (10 concurent users) -C (each time the transaction starts, pgbench connects to pgpool), which runs on the primary server.

Up to now the test has lasted for about 88 hours. No errors, no OOM killer run because of memory leak. Totally 291 million transactions have been processed.

Here is some TPS data. "excluding" means excluding connect/disconnect time. "including" means including connect/disconnect time. So it's not supprising that "excluding" is far better than "including". This is the reson why you want connection pooling.

select avg(excluding) as excluding_avg,
stddev(excluding) as excluding_stddev,
avg(including) as including_avg,
stddev(including) as including_stddev
from endurance_test;

-[ RECORD 1 ]----+-----------------
excluding_avg    | 1290.55333121045
excluding_stddev | 18.6107550266789
including_avg    | 916.442996303828
including_stddev | 10.3926488799762

Talk with the author of Streaming Replication

Yesterday I talked with Masao Fujii, the author of Streaming Replication. Streaming Replication(SR) is a long awaited feature, "built in replication" of PostgreSQL. The great feature will come with PostgreSQL 9.0, which is supposed to be released this Summer. With SR, pgpool-II will provide two important features: load balancer and failover control. Since SR is a master/slave type replication system, the standby server (slave) do not accept write queries such as INSERT, DELETE, UPDATE, CREATE TABLE and so on. Any write query sent to pgpool will be redirected to the primary server (master) and users do not need to worry about it. On the other hand, as SELECT can be executed both on the primary and the standby, pgpool will choose one of them and send the SELECT to it. This technique is known as "load balancing" and this will significantly improve the over all performance.

One thing we were worried about was, what if the connection between the primary server and the standby server goes down? There's no easy way for pgpool to know it. If pgpool does not know that, SELECT can be sent to the standby whose data is far behind the primary. Apparently this is what users want to avoid. To overcome the problem, pgpool checks the transaction log number (LSN) on both the primary and the standby. If standby's LSN is too small comparing with the one on the primary, we might have a problem. So pgpool stops the load balancing and send SELECT only to the primary. How behind is "behind" should be defined by new directive in pgpool.conf. Good thing with this is, the technique will also detects the delay because of the heavy load of the standby.

We will continue to discuss how to improve the usability of SR and pgpool-II.