Established: 4th-Jul-2017 Location: Tokyo / Japan Businesses: ✓ Development of high-performance data-processing software on top of heterogeneous architecture. ✓ Engineering service on GPU&DB area. We have developed the high-performance data processing software by the Heterogeneous Computing Technology for PostgreSQL Database. Who are you? KaiGai Kohei（海外 浩平） Chief Architect & CEO of HeteroDB,Inc Have contributed development of PostgreSQL and Linux kernel more than 10 years. (for security, database federation, custom-scan API, etc...) Primary developer of PG-Strom Award of genius programmer by METI/IPA (2006) PG-Strom
Commercial Applications DB server (OLTP) DB server (OLTP) DB server (OLAP) BI Tools Business Applications (Batch) Machine Learning acceleration of analytics, search and summarize workloads for large-scale dataset 20250411 - Apache Arrow MeetUp in Tokyo 4
NVME-SSD to GPU PCI-E Bus SCAN JOIN GROUP BY Storage Block Read by NVIDIA GPUDirect Storage P2P-DMA P2P-DMA : Peer-to-Peer Direct Memory Access 20250411 - Apache Arrow MeetUp in Tokyo 8
FDW modules are responsible for translation between external data and PostgreSQL internal (e.g, Decimal numeric). Arrow_Fdw maps Apache Arrow files on the filesystem. (≠ import) Foreign Table － a feature to read/write external data sources as if they are PostgreSQL tables, by intermediation of FDW modules PostgreSQL Table Foreign Table postgres_fdw Foreign Table file_fdw Foreign Table twitter_fdw Foreign Table Arrow_fdw External RDBMS CSV Files Twitter (Web API) Arrow Files
is the Apache Arrow format superior? Only the referenced columns are loaded, so the amount of I/O is limited. Due to the characteristics of the GPU memory bus, it can consume bus bandwidth efficiently. Since it is read-only data, there is no need to perform MVCC checks at runtime. Transfer the referenced columns only by the GPU Direct SQL mechanism PCIe Bus NVMe SSD GPU SSD-to-GPU P2P DMA WHERE-clause JOIN GROUP BY Only reference columns by the query GPU kernel now supports Apache Arrow data format in addition of PostgreSQL’s heap data format. Only pre-processed small results set are written back. Results metadata
ymd < ‘2024-01-01’ Arrow tables in the database（1/2） Log Data Transaction Data （PostgreSQL Heap） enlargement Dump the old data to Arrow format For very old data, compress them with gzip or others, then store it in backup storage. Table Partition mytable_2022.arrow mytable_2023.arrow 20250411 - Apache Arrow MeetUp in Tokyo 13
MeetUp in Tokyo 18 ▌pg2arrow It dumps PostgreSQL’s query results as an Apache Arrow file. It supports parallel dump, and append results to the existing Arrow file. ▌mysql2arrow MySQL version of pg2arrow ▌pcap2arrow It captures network packets and saves them to Arrow files. Lossless capture over 50Gbps. ▌vcf2arrow It converts VCF file to Apache Arrow. ▌arrow2csv It displays arrow file contents in CSV format. ▌fluent-plugin-arrow-file Fluentd’s writer plugin to save received logs in Apache Arrow. $ git clone https://github.com/heterodb/pg-strom.git $ cd pg-strom/arrow-tools $ make $ sudo make install
PostgreSQL server -d DATABASE database name to connect -o FILENAME Arrow file name to be written out --append FILENAME Arrow file name to be append (must be identical Schema) -c COMMAND SQL command to run -t TABLE_NAME Equivalent to -c ‘SELECT * FROM TABLE_NAME’ -s SEGMENT_SIZE Threshold to write out a record-batch --stat=COLUMNS Specifies the columns to embed statistics ➔ It allows to skip record-batches which have no matched tuples obviously. pg2arrow - Dumps PostgreSQL’s query results in Apache Arrow format $ pg2arrow -d ssbm -o /opt/arrow/f_lineorder_1996.arrow --progress ¥ -c ‘SELECT * FROM lineorder WHERE lo_orderdate BETWEEN 19960101 ¥ AND 19961231’ worker:0 SQL=[SELECT * FROM lineorder WHERE lo_orderdate BETWEEN 19960101 AND 19961231] 2024-12-16 01:21:57 RecordBatch[0]: offset=1680 length=268436376 ¥ (meta=920, body=268435456) nitems=1303083 2024-12-16 01:22:01 RecordBatch[1]: offset=268438056 length=268436376 ¥ (meta=920, body=268435456) nitems=1303083 2024-12-16 01:22:03 RecordBatch[2]: offset=536874432 length=268436376 ¥ (meta=920, body=268435456) nitems=1303083 : 20250411 - Apache Arrow MeetUp in Tokyo 19
table using ctid system column. ssbm=# explain SELECT * FROM lineorder WHERE ctid >= '(28706520,0)'::tid AND ctid < '(57413040,0)'::tid; QUERY PLAN --------------------------------------------------------------------------------- Tid Range Scan on lineorder (cost=0.01..43706589.35 rows=1500006535 width=107) TID Cond: ((ctid >= '(28706520,0)'::tid) AND (ctid < '(57413040,0)'::tid)) (2 rows) pg2arrow pg2arrow pg2arrow pg2arrow SELECT * FROM lineorder WHERE ctid < '(28706520,0)'::tid SELECT * FROM lineorder WHERE ctid >= '(28706520,0)'::tid AND ctid < '(57413040,0)'::tid SELECT * FROM lineorder WHERE ctid >= '(57413040,0)'::tid AND ctid < '(86119560,0)'::tid SELECT * FROM lineorder WHERE ctid >= '(86119560,0)'::tid (28706520,0) (0,0) (57413040,0) (86119560,0) 20250411 - Apache Arrow MeetUp in Tokyo 21
22 Worker threads receives query results individually, then write out them into the different record-batches. So, it is nearly linear scale design. Record-batch buffer shall be merged only at the end of worker thread on the tail of query execution. ☺
kaigai
kawaguti
tomoki10
tajimon
sansan33
pfn
yudai00
kubell_hr
madoxten
kazukihayase
teru0x1
lara
samlambert
mclloyd
jonrohan
chriscoyier
sergeychernyshev
jeffersonlam
bkeepers
honnibal
colly
