PostgreSQLのこれまで、9.0、そしてこれから
-
Upload
takahiro-itagaki -
Category
Technology
-
view
4.488 -
download
3
description
Transcript of PostgreSQLのこれまで、9.0、そしてこれから
![Page 1: PostgreSQLのこれまで、9.0、そしてこれから](https://reader036.fdocument.pub/reader036/viewer/2022062617/54be750d4a79597e7b8b45e5/html5/thumbnails/1.jpg)
JPUG 2010 1
PostgreSQL
9.0
NTT
2010-06-19
![Page 2: PostgreSQLのこれまで、9.0、そしてこれから](https://reader036.fdocument.pub/reader036/viewer/2022062617/54be750d4a79597e7b8b45e5/html5/thumbnails/2.jpg)
2JPUG 2010
: PostgreSQL 9.0
:
: PostgreSQL
PGCon 2010
![Page 3: PostgreSQLのこれまで、9.0、そしてこれから](https://reader036.fdocument.pub/reader036/viewer/2022062617/54be750d4a79597e7b8b45e5/html5/thumbnails/3.jpg)
3JPUG 2010
PostgreSQL 9.0
1977
20032004
2005
2006
20072008
2009
2000
19961986
PostgreSQL
6.0
PostgreSQL
6.0
7.37.3
7.47.4
IngressIngress
POSTGRESPOSTGRES
•Windows•
• (PITR)
•
•Windows•
• (PITR)
•
8.0
••2
•
••2
•
8.1
•CPU
••GIN:
•CPU
••GIN:
8.2
•HOT:
•VACUUM
•
•HOT:
•VACUUM
•
8.3
2010
Window
•VACUUM
• DBMS
Window
•VACUUM
• DBMS
8.
•
•• /
•
•
•• /
•
9.0 (2010/8 )
9.0 5
2 / 3
9.0.0
![Page 4: PostgreSQLのこれまで、9.0、そしてこれから](https://reader036.fdocument.pub/reader036/viewer/2022062617/54be750d4a79597e7b8b45e5/html5/thumbnails/4.jpg)
4JPUG 2010
PostgreSQL 9.0
1.
2. ( )
3. VACUUM FULL
/
LISTEN/NOTIFY /
Windows 64-bit
( URL )
http://developer.postgresql.org/pgdocs/postgres/release-9-0.html
http://lets.postgresql.jp/documents/technical/9.0/
1 2 3
![Page 5: PostgreSQLのこれまで、9.0、そしてこれから](https://reader036.fdocument.pub/reader036/viewer/2022062617/54be750d4a79597e7b8b45e5/html5/thumbnails/5.jpg)
5JPUG 2010
1.
9.0 2
( )
VACUUM
/
PostgreSQL
•
•==
![Page 6: PostgreSQLのこれまで、9.0、そしてこれから](https://reader036.fdocument.pub/reader036/viewer/2022062617/54be750d4a79597e7b8b45e5/html5/thumbnails/6.jpg)
6JPUG 2010
vs.
WAL pg_standbyarchive_command
( v8.4)
READ WRITE READ WRITE
(v9.0)
READ WRITE READ WRITE
wal receiverwal sender WAL
8.4
9.0 WAL
( )
WAL
( 1 )
9.09.0
![Page 7: PostgreSQLのこれまで、9.0、そしてこれから](https://reader036.fdocument.pub/reader036/viewer/2022062617/54be750d4a79597e7b8b45e5/html5/thumbnails/7.jpg)
7JPUG 2010
vs.
○
( )
○
DB( )DB
PK , DDL ※SQL
31010☆
○
2.32.09.0
pgpool-IISlony-I
※PK= , DDL=☆
![Page 8: PostgreSQLのこれまで、9.0、そしてこれから](https://reader036.fdocument.pub/reader036/viewer/2022062617/54be750d4a79597e7b8b45e5/html5/thumbnails/8.jpg)
8JPUG 2010
(UNIQUE)
“ ” “ ”
(EXCLUDE)
“ ” “ ”
GPS
2.
![Page 9: PostgreSQLのこれまで、9.0、そしてこれから](https://reader036.fdocument.pub/reader036/viewer/2022062617/54be750d4a79597e7b8b45e5/html5/thumbnails/9.jpg)
9JPUG 2010
text gist contrib/btree_gist
period ( 9.1 )
CREATE TABLE placement (
object text,
location box, --
EXCLUDE USING gist (location WITH &&)
);
CREATE TABLE reservation (
room text,
during period, -- { , }
EXCLUDE USING gist (room WITH =, during WITH &&)
);
&& “ ”
gist
![Page 10: PostgreSQLのこれまで、9.0、そしてこれから](https://reader036.fdocument.pub/reader036/viewer/2022062617/54be750d4a79597e7b8b45e5/html5/thumbnails/10.jpg)
10JPUG 2010
3. VACUUM FULL
VACUUM FULL
8.4 “ ”
9.0 “ ”
VACUUM ( )
FULL
FULL+TRUNCATE
VACUUM FULL UPVACUUM FULL UP
![Page 11: PostgreSQLのこれまで、9.0、そしてこれから](https://reader036.fdocument.pub/reader036/viewer/2022062617/54be750d4a79597e7b8b45e5/html5/thumbnails/11.jpg)
11JPUG 2010
PostgreSQL
,
,
,
,
9.0
8.4
-REINDEX
+CLUSTER
VACUUM
FULL
VACUUM
VACUUM
VACUUM FULL (8.4)
REINDEX
VACUUM FULL (9.0) CLUSTER REINDEX
REINDEX
![Page 12: PostgreSQLのこれまで、9.0、そしてこれから](https://reader036.fdocument.pub/reader036/viewer/2022062617/54be750d4a79597e7b8b45e5/html5/thumbnails/12.jpg)
JPUG 2010 12
![Page 13: PostgreSQLのこれまで、9.0、そしてこれから](https://reader036.fdocument.pub/reader036/viewer/2022062617/54be750d4a79597e7b8b45e5/html5/thumbnails/13.jpg)
13JPUG 2010
8.1 8.3
CPU
8.2
8.3
2CPU
8CPU
16CPU
8.1
?
8.0
![Page 14: PostgreSQLのこれまで、9.0、そしてこれから](https://reader036.fdocument.pub/reader036/viewer/2022062617/54be750d4a79597e7b8b45e5/html5/thumbnails/14.jpg)
14JPUG 2010
- CPU
0000
1111
2222
3333
4444
5555
1111 2222 4444 8888 12121212 16161616 20202020 24242424 28282828 32323232
8.08.08.08.0
8.18.18.18.1
8.28.28.28.2
8.2
8CPU
8.1
4CPU
8.0
2CPU
Scaling PostgreSQL on SMP Architectures Doug Tolbert (Unisys), PGCon 2007, Ottawa, 2007-05-24http://www.pgcon.org/2007/schedule/events/16.en.html
core
8 16core
CPU
バージョンごとの、CPU1個に対する相対性能
![Page 15: PostgreSQLのこれまで、9.0、そしてこれから](https://reader036.fdocument.pub/reader036/viewer/2022062617/54be750d4a79597e7b8b45e5/html5/thumbnails/15.jpg)
15JPUG 2010
8.3
HOT 2
HOT8.2
FILLFACTOR
8.3 8.3
![Page 16: PostgreSQLのこれまで、9.0、そしてこれから](https://reader036.fdocument.pub/reader036/viewer/2022062617/54be750d4a79597e7b8b45e5/html5/thumbnails/16.jpg)
16JPUG 2010
– HOT (Heap-Only Tuples)
HOT
pgbench40%
VACUUM
VACUUM
Fill Factor
100%( )90~95%
ALTER TABLE tablenameSET (fillfactor=95);
pgbench -s400 (5GB)NTT OSS Center
Fill Factor TPS
100
120
140
160
180
200
220
240
260
280
70 75 80 85 90 95 100Fill Factor (%)
TPS
HOT
HOT
Fill Factor
40%UP!
![Page 17: PostgreSQLのこれまで、9.0、そしてこれから](https://reader036.fdocument.pub/reader036/viewer/2022062617/54be750d4a79597e7b8b45e5/html5/thumbnails/17.jpg)
17JPUG 2010
-
Checkpoint
Checkpoint Next Checkpoint
Time [sec]
Thro
ughput
[TP
M]
8.2
8.3
![Page 18: PostgreSQLのこれまで、9.0、そしてこれから](https://reader036.fdocument.pub/reader036/viewer/2022062617/54be750d4a79597e7b8b45e5/html5/thumbnails/18.jpg)
18JPUG 2010
SQL /
Bitmap Scanmin/max
Window
IN, EXISTS
ORDER BY LIMIT
“ ” Seq Scan
8.2
8.3
8.1
8.4
9.0
O/R ※
※ ORM SQL JOIN
![Page 19: PostgreSQLのこれまで、9.0、そしてこれから](https://reader036.fdocument.pub/reader036/viewer/2022062617/54be750d4a79597e7b8b45e5/html5/thumbnails/19.jpg)
19JPUG 2010
8.2
8.3
VACUUM
VACUUM
8.2 8.3
8.1
autovacuum
HOT
autovacuum
8.49.0
VACUUM
FULL
XID
8.0
VACUUM
7.4
REINDEX
XIDVACUUM
![Page 20: PostgreSQLのこれまで、9.0、そしてこれから](https://reader036.fdocument.pub/reader036/viewer/2022062617/54be750d4a79597e7b8b45e5/html5/thumbnails/20.jpg)
20JPUG 2010
PostgreSQL is NoSQL!
Not Only SQL : SQL( )
SQL
8.3
9.1
8.4
8.2
6.0
(GIN)
FDW
XML
(GiST)
SQL/MED
JSON
( / )
9.0
![Page 21: PostgreSQLのこれまで、9.0、そしてこれから](https://reader036.fdocument.pub/reader036/viewer/2022062617/54be750d4a79597e7b8b45e5/html5/thumbnails/21.jpg)
21JPUG 2010
VACUUM
: 8.3
![Page 22: PostgreSQLのこれまで、9.0、そしてこれから](https://reader036.fdocument.pub/reader036/viewer/2022062617/54be750d4a79597e7b8b45e5/html5/thumbnails/22.jpg)
JPUG 2010 22
PostgreSQL
![Page 23: PostgreSQLのこれまで、9.0、そしてこれから](https://reader036.fdocument.pub/reader036/viewer/2022062617/54be750d4a79597e7b8b45e5/html5/thumbnails/23.jpg)
23JPUG 2010
PGCon 2010
2010 5 20 21 @
150 ( )
35
9.0
9.1
![Page 24: PostgreSQLのこれまで、9.0、そしてこれから](https://reader036.fdocument.pub/reader036/viewer/2022062617/54be750d4a79597e7b8b45e5/html5/thumbnails/24.jpg)
24JPUG 2010
PGCon2010
:
Caixa Economica Federal ( )
22,000 ATM, 7 /
Enova Financial ( )
OLTP 1.3TB, 4000 / , 24
MySQL PostgreSQL
9.0
PgMQ :
Eventually Consistent
Postgres-XC
ACID
+
![Page 25: PostgreSQLのこれまで、9.0、そしてこれから](https://reader036.fdocument.pub/reader036/viewer/2022062617/54be750d4a79597e7b8b45e5/html5/thumbnails/25.jpg)
25JPUG 2010
: 9.0 9.1
PostgreSQL 9.0α
( : )
PostgreSQL 9.1( , )
MERGE : UPDATE, INSERTREPLACE, UPSERT SQL “MERGE”
Predicate Lock
SE-PgSQL : ,
SQL/MED : , DB
, JSON
PGCon 2010 http://wiki.postgresql.org/wiki/PgCon_2010_Developer_Meeting
??
![Page 26: PostgreSQLのこれまで、9.0、そしてこれから](https://reader036.fdocument.pub/reader036/viewer/2022062617/54be750d4a79597e7b8b45e5/html5/thumbnails/26.jpg)
26JPUG 2010
: PostgreSQL
“ ”
1
RDBMS , DBMS MySQL
SQL
![Page 27: PostgreSQLのこれまで、9.0、そしてこれから](https://reader036.fdocument.pub/reader036/viewer/2022062617/54be750d4a79597e7b8b45e5/html5/thumbnails/27.jpg)
27JPUG 2010
PostgreSQL “9.0”
“ ”
PostgreSQL 9.0 9.1
“ ”