HTAP By Accident HTAP By Accident 

Confidential & Proprietary ©Swarm64 AS, 2019 2 There's Gold In Them Thar Hills "The stone age did not end for the lack of stone, and the oil age will end long before the world runs out of oil." Organizations store lots of transactional data. They want to derive value from that data. Fast. 

Confidential & Proprietary ©Swarm64 AS, 2019 3 From OLTP To HTAP Data engineering today: driven by the right tool for the right job Elasticsearch for search / relevance Hadoop for Map-Reduce CouchDB and MongoDB for document stores Tableau for BI analytics and visualization Why? 

Confidential & Proprietary ©Swarm64 AS, 2019 4 The Vs Of Big Data Value Veracity Velocity Volume Variety 

DBAs & Analysts: One DB, Two Perspectives 

Confidential & Proprietary ©Swarm64 AS, 2019 6 DBAs Volume & Velocity Care about Install & configure databases Monitor performance Do capacity planning They 

Confidential & Proprietary ©Swarm64 AS, 2019 7 Analysts Veracity & Value Solve business problems by learning from data Convert data into information Help businesses make better decision using information Care about They 

Confidential & Proprietary ©Swarm64 AS, 2019 8 HTAP By Accident P A U L A D A M S VP Engineering S E B A S T I A N D R E S S L E R Team Lead Solution Engineering 

Confidential & Proprietary ©Swarm64 AS, 2019 9 The DBAs Benchmark Standardized & comparable: TPC-H* DWH benchmark 22 Queries 8 Tables (2 fact, 6 dimension) Various scale factors (up to PB) Run single or multiple streams (*) TPC-DS is newer but there are fewer (semi-)official data points to compare to 

Confidential & Proprietary ©Swarm64 AS, 2019 10 Setup Hardware Dual Intel Xeon Gold 6140 384GB RAM 8x960 GB SSD Software CentOS 7.6 PostgreSQL 11.3 Swarm64 DA 2.0 TPC-H 1 TB worth of data Biggest tables: 6bn & 1.5bn rows Configurations Single Node 1 Coordinator + 2 Data Nodes 2 Coordinators + 2 Data Nodes Same as above + Swarm64 DA 

Part 1: Postgres for Analytics (Single Instance) 

Confidential & Proprietary ©Swarm64 AS, 2019 12 Postgres & Analytics Postgres One of the world's most trusted and powerful databases Maturity built on decades of community-driven development Well-respected for OLTP workloads Very capable in HTAP and OLAP Well, there is a "but"... 

Let's Look At Some Analytics Queries 

Confidential & Proprietary ©Swarm64 AS, 2019 14 Queries By Example: TPC-H Q6 SELECT SUM(l_extendedprice * l_discount) AS revenue FROM lineitem WHERE l_shipdate >= DATE '1993-01-01' AND l_shipdate < DATE '1993-01-01' + INTERVAL '1' YEAR AND l_discount BETWEEN 0.05 - 0.01 AND 0.05 + 0.01 AND l_quantity < 24; Scanning > Parallelism helps, yet limited > Indices may cause scatter-gather Statistics help > They narrow selectivity Typical runtime: 10min 

Confidential & Proprietary ©Swarm64 AS, 2019 15 Queries By Example: TPC-H Q12 SELECT l_shipmode, SUM(CASE WHEN o_orderpriority = '1-URGENT' OR o_orderpriority = '2-HIGH' THEN 1 ELSE 0 END) AS high_line_count, SUM(CASE WHEN o_orderpriority <> '1-URGENT' AND o_orderpriority <> '2-HIGH' THEN 1 ELSE 0 END) AS low_line_count FROM orders, lineitem WHERE o_orderkey = l_orderkey AND l_shipmode IN ('TRUCK', 'AIR') AND l_commitdate < l_receiptdate AND l_shipdate < l_commitdate AND l_receiptdate >= DATE '1996-01-01' AND l_receiptdate < DATE '1996-01-01' + INTERVAL '1' YEAR GROUP BY l_shipmode ORDER BY l_shipmode; Typical runtime: >10min Expensive finalization > Early data reduction is key JOIN > May scatter-gather Filtering > Data reduction point #1 > Parallelism helps GROUP BY > Data reduction point #2 

Demo Time 

Confidential & Proprietary ©Swarm64 AS, 2019 17 Q6 Single Node 

The Analyst's Point Of View 

Confidential & Proprietary ©Swarm64 AS, 2019 19 Analytics On Real World Datasets Question How much more generous are passengers being picked up at The Dead Rabbit than those being dropped off? NYC Taxi Billions of rows Pickup location, drop-off location, tip, fare, ... 

Confidential & Proprietary ©Swarm64 AS, 2019 20 The Analyst's Query SELECT (outbound.tip - inbound.tip) / inbound.tip * 100 AS generosity_increase FROM ( SELECT AVG(tip_amount / fare_amount) AS tip FROM trip_dropoff_locations JOIN trips ON trip_dropoff_locations.id = trips.id WHERE (trip_dropoff_locations.longitude BETWEEN -74.0114803745 AND -74.0105174615) AND (trip_dropoff_locations.latitude BETWEEN 40.7030212228 AND 40.7032184606) AND tip_amount != 0 AND fare_amount != 0) inbound CROSS JOIN ( SELECT AVG(tip_amount / fare_amount) AS tip FROM trip_pickup_locations JOIN trips ON trip_pickup_locations.id = trips.id WHERE (trip_pickup_locations.longitude BETWEEN -74.0114803745 AND -74.0105174615) AND (trip_pickup_locations.latitude BETWEEN 40.7030212228 AND 40.7032184606) AND tip_amount != 0 AND fare_amount != 0) outbound; Range-based scan > Index helps to reduce data volume JOIN > May scatter-gather Serial repetition > Similar query, executes in sequence Filtering > Reduces data volume 

Confidential & Proprietary ©Swarm64 AS, 2019 21 Analytics On Real World Datasets SELECT (outbound.tip - inbound.tip) / inbound.tip * 100 AS generosity_increase FROM ( SELECT AVG(tip_amount / fare_amount) AS tip FROM trip_dropoff_locations JOIN trips ON trip_dropoff_locations.id = trips.id WHERE (trip_dropoff_locations.longitude BETWEEN -74.0114803745 AND -74.0105174615) AND (trip_dropoff_locations.latitude BETWEEN 40.7030212228 AND 40.7032184606) AND tip_amount != 0 AND fare_amount != 0) inbound CROSS JOIN ( SELECT AVG(tip_amount / fare_amount) AS tip FROM trip_pickup_locations JOIN trips ON trip_pickup_locations.id = trips.id WHERE (trip_pickup_locations.longitude BETWEEN -74.0114803745 AND -74.0105174615) AND (trip_pickup_locations.latitude BETWEEN 40.7030212228 AND 40.7032184606) AND tip_amount != 0 AND fare_amount != 0) outbound; 1min 56s 

Part 2: Postgres for Analytics (Scaled-Out) 

Confidential & Proprietary ©Swarm64 AS, 2019 23 Scale-Out: When & Why? Separation of concerns I/O intensive vs. CPU intensive Bottlenecks on your single node I/O CPU RAM Why is that? Data grows More concurrent users More demanding queries 

Confidential & Proprietary ©Swarm64 AS, 2019 24 Scale-Out: Typical Approach Coordinator Table metadata Node to connect to & query on Does the compute intesive part Data Nodes Table data Nodes where coordinators connect to Performs scanning and other I/O operations (e.g. filtering) 

Confidential & Proprietary ©Swarm64 AS, 2019 25 Scale-Out: "3rd-Party Options" (Patroni) ... there are more. 

Confidential & Proprietary ©Swarm64 AS, 2019 26 Scale-Out With PG Tools Native scale-out Use the Postgres Foreign Data Wrapper extension (postgres_fdw) Coordinator tables are postgres_fdw tables Connect to data nodes where the data is actually located Use partitions for parallelism Pitfall postgres_fdw not parallelized out-of-the-box, needs a patch 

Confidential & Proprietary ©Swarm64 AS, 2019 27 Q6 2 Coordinator + 2 Data Nodes 

Confidential & Proprietary ©Swarm64 AS, 2019 28 Analytics On Real World Datasets Same query, better result? Split computation & I/O Data gathering on data nodes Final computation is done on the coordinator 1min 46s 

Part 3: Software & Hardware Acceleration 

Confidential & Proprietary ©Swarm64 AS, 2019 30 Tuning Postgres Data For Analytics Add Indices They help you on point-lookups, range queries, full text search, ... Upside Access data faster Downside They cost extra storage & CPU They can cause non-optimal I/O patterns Decide for fast reads over fast writes Add Partitions Mostly reduce data on range queries by selecting the right partition Upside Lower data volume & better maintenance (partitions can be plugged out) Downside Parallelism might be limited Changing the partition scheme might be hard 

Are There Any Other Options? 

Confidential & Proprietary ©Swarm64 AS, 2019 32 How To Increase Parallelism? Postgres limits parallelism... to prevent resource over-allocation to ensure transactional safety, even on a highly loaded system Patched postgres_fdw Parallelize scans on remote tables for higher throughput Workload management Determine and assign resources prior to query execution Monitor system state to acknowledge change Query rewriting Transform query plans to be executed more efficiently 

Confidential & Proprietary ©Swarm64 AS, 2019 Optimized Columns ROW- / COLUMN-HYBRID BLOCKS UP TO 3 RANGE-INDICES I/O transfer from storage device WHERE ws_order_number BETWEEN 150 AND 15000 AND ws_sold_date_sk BETWEEN 2450820 AND 2452000 WHERE ws_order_number BETWEEN 150 AND 15000 AND ws_sold_date_sk BETWEEN 2450820 AND 2452000 M ulti-part index 

Confidential & Proprietary ©Swarm64 AS, 2019 Decompress Pick Rows Pick Columns Result FROM SELECT Parallel Plan Optimized Columns WHERE Executed on the HW Accelerator WHERE 34 + Hardware Acceleration 

Confidential & Proprietary ©Swarm64 AS, 2019 35 Q6 Side-By-Side 

Confidential & Proprietary ©Swarm64 AS, 2019 36 Q6 2 Coordinator + 2 Data Nodes + HW Acceleration 

Confidential & Proprietary ©Swarm64 AS, 2019 37 Analytics On Real World Datasets Same query, best result? Split computation & I/O Data gathering on data nodes Final computation is done on the coordinator Plus Higher scan & filter parallelism Higher throughput due to compression 21s 

Confidential & Proprietary ©Swarm64 AS, 2019 38 Conclusions Postgres is great for analytics! Single node performs well Postgres can scale-out natively Hardware and software optimizations allow for greater parallelism and higher throughput Postgres can be a true analytics engine 

Confidential & Proprietary ©Swarm64 AS, 2019 39 Got Questions? Come and find us in the exhibitor area P A U L A D A M S VP Engineering [email protected] @theRealPAdams S E B A S T I A N D R E S S L E R Team Lead Solution Engineering [email protected] @theDressler 

[email protected] Follow us: ©Swarm64 AS, 2019