the future of postgres sharding - postgresql europetitle: the future of postgres sharding - this...

,

The Future of Postgres ShardingThis presenta on will cover the advantages of sharding and future

Postgres sharding implementa on requirements.Crea ve Commons A ribu on License

h p://momjian.us/presenta onsLast updated: August, 2019

B M , A K

2019 October 17

B M , A K The Future of Postgres Sharding 1 / 39

,Outline

1. Scaling

2. Ver cal scaling op ons

3. Non-sharding horizontal scaling

4. Exis ng sharding op ons

5. Built-in sharding accomplishments

6. Future sharding requirements


,1. Scaling

Database scaling is the ability to increase database throughput by u lizingaddi onal resources such as I/O, memory, , or addi onal computers.However, the high concurrency and write requirements of databaseservers make scaling a challenge. Some mes scaling is only possible withmul ple sessions, while other op ons require data model adjustments orserver configura on changes.Postgres Scaling Opportuni eshttp://momjian.us/main/presentations/overview.html#scaling


http://momjian.us/main/presentations/overview.html#scaling

,2. Ver cal Scaling

Ver cal scaling can improve performance on a single server by:▶ Increasing I/O with

▶ faster storage▶ tablespaces on storage devices▶ striping ( 0) across storage devices▶ Moving to separate storage

▶ Adding memory to reduce read I/O requirements▶ Adding more and faster s


,3. Non-Sharding Horizontal Scaling

Non-sharding horizontal scaling op ons include:▶ Read scaling using Pgpool and streaming replica on▶ C /memory scaling with asynchronous mul -master

The en re data set is stored on each server.


,Pgpool II With Streaming Replica on

SELECTINSERT, UPDATE,

DELETE to master

host

Slave SlaveMasterreplication

replication

to any host

pgpool

streaming

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Streaming replica on avoids theproblem of non-determinis cqueries producing different resultson different hosts.


,Why Use Sharding?

▶ Only sharding can reduce I/O, by spli ng data across servers▶ Sharding benefits are only possible with a shardable workload▶ The shard key should be one that evenly spreads the data▶ Changing the sharding layout can cause down me▶ Addi onal hosts reduce reliability; addi onal standby servers might

be required


,Typical Sharding Criteria

▶ List▶ Range▶ Hash


,4. Exis ng Sharding Solu ons

▶ Applica on-based sharding▶ PL/Proxy▶ Postgres-XC/XL▶ Citus▶ Hadoop

The data set is sharded (striped) across servers.


,

5. Built-in Sharding Accomplishments:Sharding Using Foreign Data Wrappers ( )

��

��

��

��

��

��

SQL Queries

SQL Queries

PG FDW

Foreign Server Foreign Server Foreign Server

https://wiki.postgresql.org/wiki/Built-in_ShardingB M , A K The Future of Postgres Sharding 10 / 39

https://wiki.postgresql.org/wiki/Built-in_Sharding

,F Sort/Join/Aggregate Pushdown

SQL Queries

PG FDW


joins (9.6)

sorts (9.6)aggregates (11)


,

Advantages of F Sort/Join/AggregatePushdown

▶ Sort pushdown reduces and memory overhead on thecoordinator

▶ Join pushdown reduces coordinator join overhead, and reduces thenumber of rows transferred

▶ Aggregate pushdown causes summarized values to be passed backfrom the shards

▶ W clause restric ons are also pushed down


,Aggregate Pushdown in Postgres 11

SQL Queries

PG FDW


Aggregates, e.g., SUM()

Unfortunately, aggregates are currently evaluated one par on at a me,i.e., serially.B M , A K The Future of Postgres Sharding 13 / 39

,F D Pushdown in Postgres 9.6 & 11

SQL Queries

PG FDW


INSERT, UPDATE

DELETE, COPY


,

6. Future Sharding Requirements: Parallel ShardAccess

��

��

��

��

��

��

SQL Queries

PG FDW


Shard Parallel Access

Parallel shard access is wai ng for an executor rewrite, which is necessaryfor improvements.B M , A K The Future of Postgres Sharding 15 / 39

,Advantages of Parallel Shard Access

▶ Can use libpq’s asynchronous to issue mul ple pending queries▶ Ideal for queries that must run on every shard, e.g.,

▶ restric ons on sta c tables▶ queries with no sharded-key reference▶ queries with mul ple shared-key references

▶ Parallel aggrega on across shards


,Joins With Replicated Tables

SQL Queries

SQL Queries

with joins to static data

PG FDW

and static data restrictions

Foreign S. Foreign S. Foreign S.repl. repl. repl.


,Implemen ng Joins With Replicated Tables

Joins with replicated tables allow join pushdown where the queryrestric on is on the replicated (lookup) table and not on the shardedcolumn. Tables can be replicated to shards using logical replica on. Theop mizer must be able to adjust join pushdown based on which tables arereplicated on the shards.


,shardman

https://github.com/postgrespro/shardman▶ Automa on of sharding using par oning + FDW.▶ Redundancy and automa c failover using streaming replica on.▶ Distributed transac ons using 2PC.▶ Distributed visibility.▶ Distributed query planning/execu on.


https://github.com/postgrespro/shardman

,Redundancy in shardman

Streaming replica on.Server 1

Instance 1Instance 4replica

Instance 2replica

Server 2

Instance 3replicaInstance 2

Instance 1replica

Server 4

Instance 3replica

Instance 1replica Instance 4

Server 3

Instance 3

Instance 4replica

Instance 2replica


,Move to logical replica on

▶ Logical pg_rewind▶ Parallel decoding & parallel apply▶ Logical decoding of 2PC▶ Online streaming of large transac ons▶ High availability (Ra ?)▶ DDL support


,Distributed visibility in shardman

▶ Based on Clock-SI scheme 1.▶ Needs PostgreSQL core patching, ideally needs CSN▶ Good scalability: only nodes involved in transac onare involved in snapshot management.

▶ Local transac ons runs locally.▶ No dedicated service is required.▶ Short lock for some of readers during distributedCSN coordina on.

1Clock-SI: Snapshot isola on for par oned data stores using loosely synchronizedclocksB M , A K The Future of Postgres Sharding 22 / 39

http://research.microsoft.com/en-us/people/samehe/clocksi.srds2013.pdf

http://research.microsoft.com/en-us/people/samehe/clocksi.srds2013.pdf

,Visibility in work

Instance 1 Instance 2 Instance 3

BEGINBEGIN

Some work

PREPAREPREPARE

PRECOMMITPRECOMMIT

COMMITCOMMIT

▶ PRECOMMIT stagemarks transac onsin-doubt and calculatesdistributed CSN.

▶ It blocks readers ONLYIF: they access the datamodified by currentlyin-doubt transac onand acquired snapshota er it enters in-doubtstage.


,

Distributed visibility in PostgreSQL Core:wrong way

▶ C API (set of hooks), which allows to overridelow-level visibility-related func ons.

▶ pg_tsdtm extension, which implements CSN andClock-SI on top of that.


,

Distributed visibility in PostgreSQL Core:right way

▶ CSN snapshots to PostgreSQL Core.▶ C API for management of transac on CSN.▶ Proper Clock-SI implementa on (as extension or inCore?)


,

How does shardmanplan/execute distributed (OLAP)

queries?


,

Distributed planning step 1:local plan

Scan A1

Append

Scan B1

Append

Scan A2 Scan B2

Join


,

Distributed planning step 2:add distributed nodes

Scan A1

Append

Scan B1

Append

Scan A2 Scan B2

Join

Shuffle Shuffle

Gather

Distributedexecution


,

Distributed planning step 3:spread plans across the nodes

Scan A1

Append

Scan B1

Append

Scan A2 Scan B2

Join

Shuffle Shuffle

Gather

Node 1

Scan A1

Append

Scan B1

Append

Scan A2 Scan B2

Join

Shuffle Shuffle

Gather

Node 2Distributedexecution


,Stages of distributed query execu on

1. Preparedistributedquery plan at coordinator node2. Portable serializa on of theplan, collectlist of

foreign servers3. At the begin of query execu on, passtheplanto

eachforeign serverbyFDWconnec on4. Localize the plan-walk across scan nodes, remove

unneeded scan nodes5. Executethe plan

▶ Steps 1-3 for coordinator node▶ Steps 3-4 for every involved node


,

How distributed planning/execu on integratesto PostgreSQL?

▶ Planner hooks: set_rel_pathlist_hook,set_join_pathlist_hook,

▶ Custom node: ExchangePlanNode,▶ Portable plan serializa on/deserializa on. 1


,set_rel_pathlist_hook

Scan A1

Append

Gather

Scan A2

Scan A1

Append

ForeignScan A2

Basic path Shardman path


,set_join_pathlist_hook

Scan A1

Append

Gather

Scan A2

Shuffle Broadcast

Scan B1

Append

Gather

Scan B2

Shuffle Broadcast

HashJoin HashJoin NestedLoopJoin


,ExchangePlanNode

▶ Compute des na on instanceforeachincomingtuple▶ Transfer the tuple to the corresponding EXCHANGE node atthe

instance▶ If des na on isitself ? transfer the tupleup bythe plan tree▶ Anydistributed plan hasEXCHANGE nodeingather mode at

the top of the plan: collect all results at the coordinator node.

Modes:▶ Shuffle ? transfer tuplecorresponding to distribu on func on▶ Gather ? gather all tuples at one node▶ Broadcast ? transfer each tuple to each node (itself too)


,Portable plan serializa on/deserializa on

▶ Patch nodeToString(), stringToNode() code.▶ Serializa on replaces OIDs with object names.▶ Deserializa on replaces object names back to OIDs.▶ pg_exec_plan(plan text) deserializes, localizes andlaunches execu on of the plan.


,PostgreSQL core modifica ons

▶ Patch nodeToString(), stringToNode() code.▶ Change par oning code in the planner:par oningof joinrel can be changingaccording to path (May bewe transfer par oning-related fields fromRelOptInfo toPath structure?)


,Distributed planning/execu on status

▶ WIP▶ Need to patch PostgreSQL core.▶ HashJoin, NestedLoopJoin and HashAgg areimplemented, MergeJoin and GroupAgg are in TODOlist.

▶ Observed up to 5- mes improvement in comparisonwith FDW on 4-nodes cluster (async execu on!).

▶ https://github.com/postgrespro/shardman ?go try it.


https://github.com/postgrespro/shardman

,Conclusion

Following features to push into PostgreSQL Core:▶ CSN▶ Distributed-visibility C API (CSN-based)▶ Portable serializa on/deserializa on fornodeToString(), stringToNode()

▶ Planner improvements for par oning▶ Logical replica on improvements (a lot of them)


,Conclusion

h p://momjian.us/presenta ons h ps://www.flickr.com/photos/anotherpintplease/


the future of postgres sharding - postgresql europetitle: the future of postgres sharding - this...

Documents