How have PostgreSQLcommunity and Marketevolved in Japan
Tatsuo Ishii
SRA OSS, Inc. Japan

2Who am I?
●OSS developer
PostgreSQL committer●pgpool-II community lead●●Community activity
Co founder of Japan
PostgreSQL Users Group●
Secretary general ofPostgreSQL EnterpriseConsortium●●Branch manager of SRA
OSS, Inc. Japan
Copyright(c) 2015 SRA OSS, Inc. Japan

3About SRA OSS, Inc.
●●

●Running PostgreSQL business from 1999
Established in 2005 as SRA OSS, 100% owned bySRA, Inc.
Our main businesses areas are follows:●●Open source software support–PostgreSQL, pgpool-II, Hinemos, Zabbix andmany more–No MySQL:-)–600 support contracts

PostgreSQL based product: PowerGres–●●Transparent encryption of data, redundant WAL

Training and ConsultingContributions●pgbench●multi byte characters●Windows port●

Recursive query

Copyright(c) 2015 SRA OSS, Inc. Japan

PostgreSQL community history
in JapanIn 1995 Postgres95 mailing list
started (turned into PostgreSQL
mailing list in 1996)●
In 1998 PostgreSQL 6.3.1
merged the multi byte patch●PostgreSQL documentationtranslated into Japanese●In 1998 the first PostgreSQL
book published●In 1999 Japan PostgreSQL
User's Group established●In 2012 PostgreSQL Enterprise
Consortium establieshed●Copyright(c) 2015 SRA OSS, Inc. Japan

45PostgreSQL history
Materializedview
Foreign datawrapperlines of code

JSONB
Logicaldecoding

1,400,000
Streaming
replication
Recursive SQLWindow function

1,200,000
Multi
Processor

1,000,000

Scale up to 80
cores80
Cascadingreplication

Sub-query
Multi byte

800,000

WAL log
TOAST
PL/pgSQL

600,000
TriggerHOT
Full text
searchautovacuum

400,000
Windows
Concurrent
Vacuum200,000
06.1PostgreSQL
Mailing list

6.2

6.3

6.4

JPUG

Rowlocks
MVCC
6.5 7.0

7.1

7.2

7.3

7.4

8.0

SRA OSS

8.1

8.2

8.3

PGConCopyright(c) 2015 SRA OSS, Inc. Japan

8.4

true serializable
isolation
level

9.0

9.1

9.2

9.3

9.4

PGConf.Russia

6Japan PostgreSQL Users Group

Copyright(c) 2015 SRA OSS, Inc. Japan

Activity of Japan PostgreSQL Users
Group (JPUG)
Established in 1999●
Major activities include:●

Translating PostgreSQL documents●
Mailing lists●Seminars●Regional branches●
Tech blogs●●JPUG helps:
to promote PostgreSQL to people who are not familiar with English●beginners of PostgreSQL in ML and seminars●–●Beginners are often spoiled in OSS community :-)to promote PostgreSQL in the early days of OSS in Japan

Copyright(c) 2015 SRA OSS, Inc. Japan7

8

PostgreSQL is in the second position in the Japan's
database market

OracleSQL Server

PostgreSQL

OraclePostgreSQLMySQLSQL Server
DB2
FireBird
PowerGres
その他

MySQL

Copyright(c) 2015 SRA OSS, Inc. Japan

From the survey result of “Linux World 2007”(multiple answers was permitted)

PostgreSQL EnterpriseConsortium

Copyright(c) 2015 SRA OSS, Inc. Japan

9

About PostgreSQL Enterprise Consortium What is PostgreSQL Enterprise Consortium?
 PostgreSQL Enterprise Consortium (PGECons) is a non-profit organization aiming at promoting PostgreSQL in pro
duction use, especially in mission critical area
 Who are the members of PGECons?
 Leading IT/OSS companies in Japan 17 regular members and 30 general members (as of 201

5/1/26)

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

10

Major activities of PGEConsCollaborative verificationPGECons performs necessary verification collaboratively by using resources provided by ourmember companies

Promoting PostgreSQL

Through seminars PGECons presents technicalreports created by the activities above. Also PG
ECons provides various case studies, which areimportant for those who are trying to adopt Pos
tgreSQL.
http://www.pgecons.org/en/aboutCopyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

11

Evaluation details
Increase the number of concurrent clients on many coremachinespgbench -h [host] -p [port] [dbname] -c [c] -j [j] -T 30 -n -f custom.sql
Increase number of clients（-c）
Number of threads is ½ of -cpgbench dedicated machine is preparedpgbenchmem: 64GB
16 core pgbenchmachinemem: 2TB

80 core PostgreSQL server

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

12Custom.sql\set nbranches :scale\set ntellers 10 * :scale\set naccounts 100000 * :scale\set row_count 10000\set aid_max :naccounts ­ :row_count\setrandom aid 1 :aid_max\setrandom bid 1 :nbranches\setrandom tid 1 :ntellers\setrandom delta ­5000 5000
SELECT count(abalance) FROM pgbench_accounts WHERE
aid BETWEEN :aid and :aid + :row_count;

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

13

The result!
Scale factor: 1000
Number of rows:
a hundred million(15GB)

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

14

Benchmarking with shared nothing clusters
pgpool-II configuration
 Using pgpool-II(3.2.1) & PostgreSQL(9.2.1)
 Pgpool-II is configured to use native replication mode(synchronous replication)
Pgbench configuration

Up to 4 pgbench clients used. TPS is defined as the summary of each pgbench TPS
Pgbench default scenario is used for write query test
Read test uses custom scenario because default scenario(-S) is too subtle

Writepgbenchpgbenchpgbenchpgbenchpgpool-II

pgpool-II ※pgpool-II ※pgpool-II ※PostgreSQL
PostgreSQL

PostgreSQL
PostgreSQL

PostgreSQL
PostgreSQL

ReadPostgreSQL
PostgreSQL※ Arrows for second node or above is omitted to avoid
complexity of the figure

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

15

Read query result

total tps
average per nodeSettings

100,000
90,000
80,000

PostgreSQL shared memory: 32GB
Pgbench scale factor: 1,000
Each duration(-T): 300 seconds
Number of concurrent sessions (-c):
100
Number of worker threads (-j): 2070,000
TPS

60,00050,000The custom scenario. Randomly extracts
2,000 rows.

40,00030,00020,00010,00001234nodes count\set nbranches :scale\set ntellers 10 * :scale\set naccounts 100000 * :scale\set range 2000
\set aidmax :naccounts - :range\setrandom aid 1 :aidmax\setrandom bid 1 :nbranches\setrandom tid 1 :ntellers\setrandom delta -5000 5000
SELECT count(abalance) FROM
pgbench_accounts WHERE aid
BETWEEN :aid and :aid + :range; Total TPS increases as number of nodes increases(scale out)

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

16

17

What is pgpool-II?
●

●

●

●

●

●

Proxy like cluster management
tool for PostgreSQL andcompatible servers
BSD licensed OSS
Completely separated from
PostgreSQL (not modified
PostgreSQL like Postgres-XC)
Can be used with wide variety ofPostgreSQL versions
Rich function sets to enhance
performance and availability ofPostgreSQL
Actively developed●●Once a year major version up
2-3 times a year of minor
version up
Copyright(c) 2015 SRA OSS, Inc. Japan18

How pgpool-II works(1)Looks like as if PostgreSQL fromapplication's point of view
SELECT/UPDATE etc.
Check if the query can beload balanced
SELECT
UPDATE etc.

PostgreSQL
primary

UPDATE is propagatedby streaming replication

pgpool-II

Distribute SELECTs to servers toenhance performance(load balancing)

SELECT

SELECT

PostgreSQL
standbystreaming replication
Copyright(c) 2015 SRA OSS, Inc. Japan

PostgreSQL
standby19

How pgpool-II works(2)SELECT/UPDATE etc.
pgpool-II

SELECT
UPDATE etc.

PostgreSQL
primary

If standby goes down, pgpool-II
automatically detach the node andSELECT continue operation

PostgreSQL
standbystreaming replication
Copyright(c) 2015 SRA OSS, Inc. JapanX

PostgreSQL
standby20

How pgpool-II works(3)SELECT/UPDATE etc.
pgpool-II

SELECT
UPDATE etc.
If primary goes down
the whole system becomes
unusableX

PostgreSQL
primary

SELECT

PostgreSQL
standbystreaming replication
Copyright(c) 2015 SRA OSS, Inc. Japan

SELECT

PostgreSQL
standby21

How pgpool-II works(4)SELECT/UPDATE etc.
pgpool-II

If primary goes down,
pgpool-II disconnect the broken
primary and promote
one of the standbys

X

PostgreSQL
primary

SELECT
UPDATE etc. SELECT

PostgreSQL
primary

streaming replication
Copyright(c) 2015 SRA OSS, Inc. Japan

PostgreSQL
standby22

How pgpool-II works(5)SELECT/UPDATE etc.
pgpool-II

Automatically recover downed
primary as new standby
SELECT
PostgreSQL
standby

SELECT
UPDATE etc. SELECT
PostgreSQL
primary

streaming replication
Copyright(c) 2015 SRA OSS, Inc. Japan

PostgreSQL
standby

Run heavy analytic queries
without disturbing
OLTP transactions
Heavy analytic
application

OLTP
applications

pgpool-II

Applications are recognizedby “application name”.
Pgpool-II sends OLTP tx
to primary and standby,
while transactions fromanalytic application aresent to the standby for
analytic data.

OLTP
transactions

Heavy analyticqueries

PrimaryDB

Streaming
replicationStandby
DB

Copyright(c) 2015 SRA OSS, Inc. Japan

AnalyticDB23

How to use query
redirect in pgpool-II●

Parameters to control read query load balancing●

database_redirect_preference_list
DB_name:node_spec,DB_name:node_spec...
– Regular expression can be used for DB name– “Node spec” can be either of:–●●“primary”, “standby”, or node number

app_name_redirect_preference_list
app_name:node_spec,app_name:node_spec...
– Regular expression can be used for app name– “Node spec” can be either of:–●●“primary”, “standby”, or node number

If database_redirect_preference_list andapp_name_redirect_preference_list conflicts each other,はapp_name_redirect_preference_list is used

Copyright(c) 2015 SRA OSS, Inc. Japan24

25

Case study:Gengo
Provides translation service via Web orAPI for over 37 languages. 13,000translators all over the world●All servers are running on Amazon
Web Services (AWS) using PostgreSQL●Had troubles w occasional DB down
and performance problem●
To achieve more reliability andperformance, pgpool-II is used withstreaming replication●In addition to this they need to moveto the recent PostgreSQL version●All migration efforts went smoothly inthe planned maintenance window●
http://www.sraoss.co.jp/case_study/gengo_en.php●Copyright(c) 2015 SRA OSS, Inc. Japan

26

Case study: DAI-ICHI HOKI
DAI-ICHI HOKI co., ltd. is a company thatsells law-related books and provides online
data●
They employed PostgreSQL as a database,and pgpool-II for redundancy and toimprove performance●
Before the data update was once a monthwhich was frustrating for their customers●
With the new system data update is once aday!
●The auto fail over is managed by pgpool-II,
which allows the system to continue theoperation when the master DB goes down
(it actually happened)●
pgpool-II's in memory query cache boosts
the performance●
http://www.sraoss.co.jp/case_study/daiic
hihoki_en.php●

Copyright(c) 2015 SRA OSS, Inc. Japan

27

SummaryPostgreSQL is quite popular in Japan●The reason behind this is user's community(JPUG) and company's community (PGECons)●
pgpool-II●●

pgpool-II: http://pgpool.net

Case studies●URLs●

Copyright(c) 2015 SRA OSS, Inc. Japan28Copyright(c) 2015 SRA OSS, Inc. Japan