, the great people at DuckDB launched a database communication protocol referred to as Quack. Its most important purpose was to permit DuckDB databases held on completely different servers to speak with one another over HTTP in a shopper/server association and permit them to learn and write every others knowledge.
In different phrases, utilizing the Quack protocol, DuckDB sitting on server A might now question or write to a DuckDB database on distant server B. This may sound a bit like distributed knowledge processing, but it surely’s not and the DuckDB group was at pains to emphasise that Quack doesn’t facilitate distributed question processing.
Even so, I used to be intrigued and will see many makes use of for Quack. Specifically, I used to be to search out out if it was doable to fireside off parallel SQL statements on every server and have the outputs of every question gathered and made accessible to be used or show on a coordinating server.
Word that this can be a completely different proposition from merely becoming a member of tables throughout completely different servers in a single SQL assertion. DuckDB can do this by attaching a database from server B to server A, for instance, then operating SQL on server A that may learn knowledge on server B as if it have been native.
So, to analyze the concurrent reads and writes that Quack ought to allow, I created a GitHub repo referred to as cluster-duck, and no, it’s not a distributed DuckDB cluster. It’s only a comedian tackle the frequent, impolite expression you most likely already know. However it’ll allow you to do concurrent reads and writes on distant DuckDB databases.
Word: Earlier than continuing I’d wish to state that I’ve no affiliation or business asscociation with any of the merchandise, methods or their creators which are talked about on this article.
The arrange
To check Quack out, I arrange 3 AWS EC2 servers by way of a CloudFormation stack. Every server holds one DuckDB database with one of many servers additionally appearing as a coordinator node. You’ll find the CF stack on my GitHub repo.
From AWS CloudShell (or regionally in case you have the AWS CLI put in), with the repository checked out, deploy the stack with:
aws cloudformation deploy
--region us-east-2
--stack-name cluster-duck-test-v2
--template-file python-reference/infra/aws/cluster-duck-3-node.yaml
--capabilities CAPABILITY_NAMED_IAMFor all three EC2 servers, the next was put in,
- Amazon Linux 2023 ARM64.
- A 512 MB swap file.
- Python 3.12 and pip.
- A Python digital setting at: /choose/cluster-duck-venv
- duckdb==1.5.5
- boto3
- DuckDB 1.5.5 ARM64 CLI at: /usr/native/bin/duckdb
- The official DuckDB Quack extension, loaded by the employee course of.
- The Quack server program at: /choose/cluster-duck/quack_server.py
- The employee database at: /var/lib/cluster-duck/employee.duckdb
- A systemd service named: cluster-duck-quack.service
- An computerized shutdown timer, 4 hours by default.
Quack listens on port 9494 by default. Its authentication token is retrieved from an encrypted SSM Parameter Retailer parameter.
The Python supply is copied onto all three servers as a result of they share the identical CloudFormation launch template. Nevertheless, the coordinator is barely made executable as a command on one server – normally employee 1.
These information are put in on each server:
/choose/cluster-duck/quack_server.py
/choose/cluster-duck/seed_related_data.py
/choose/cluster-duck/related_cluster_sql.py
/choose/cluster-duck/cluster_duck/sql_api.pyThe coordination server (Employee 1) moreover will get these command launchers:
/usr/native/bin/cluster-duck
/usr/native/bin/cluster-duck-sqlOnce you run cluster-duck-sql on the coordinator, this ultimately begins:
/choose/cluster-duck-venv/bin/python
/choose/cluster-duck/related_cluster_sql.pyThe Python supply is compressed and embedded instantly contained in the CloudFormation template as a Base64-encoded archive.
Throughout EC2 bootstrap, the user-data script:
- Decodes the embedded archive.
- Creates /choose/cluster-duck.
- Extracts the Python information into that listing.
- Creates the command launchers on employee 1.
- Begins the Quack service on each employee.
Every thing wanted is contained within the CloudFormation file.
How the coordination works
Quack carries every SQL assertion to the chosen DuckDB server and returns its outcome. The half that coordinates the three calls is the Python code operating on Employee 1.
First, each — question or — query-file argument is validated as a single SQL assertion and was a QueryFragment. The fragments are labelled within the order by which they have been provided:
fragments.append(
QueryFragment(worker_id, f"query-{index}", sql)
)The coordinator then creates one thread per fragment and a barrier with the identical variety of contributors:
barrier = threading.Barrier(len(fragments))
epoch = time.perf_counter()
def run_fragment(fragment):
barrier.wait()
began = time.perf_counter()
raw_result = self.executor(fragment.worker_id, fragment.sql)
completed = time.perf_counter()
return {
"start_offset_ms": (began - epoch) * 1000,
"duration_ms": (completed - began) * 1000,
"outcome": raw_result,
}
with ThreadPoolExecutor(max_workers=len(fragments)) as pool:
futures = {
pool.submit(run_fragment, fragment): fragment
for fragment in fragments
}The barrier holds the threads till each fragment is prepared, then releases them collectively. They won’t begin on exactly the identical CPU cycle as a result of regular operating-system scheduling nonetheless applies, which is why the output features a measured begin unfold.
Every fragment will get its personal native DuckDB shopper connection on the coordinator. That connection masses Quack, attaches one distant employee and sends the assertion by way of distant.question():
with duckdb.join() as connection:
connection.execute("INSTALL quack")
connection.execute("LOAD quack")
connection.execute(
f"ATTACH {endpoint} AS distant "
f"(TYPE quack, TOKEN {token}, DISABLE_SSL true)"
)
cursor = connection.execute(
f"SELECT * FROM distant.question({sql_string(sql)})"
)
columns = tuple(description[0] for description in cursor.description)
return FragmentResult(columns, cursor.fetchall())The decision to fetchall() materialises every outcome on Employee 1. The coordinator waits for all of the futures, information when each began and the way lengthy it took, after which presents the separate leads to one output. Quack is doing the distant execution and transport; the fragment building, simultaneous launch, timing and outcome assortment are all being accomplished by Python.
Creating our take a look at knowledge
Every of the three servers has a unique DuckDB database as follows.
Employee Database file Generated desk
---------------------------------------------------------------
Employee 1 /var/lib/cluster-duck/employee.duckdb gross sales
Employee 2 /var/lib/cluster-duck/employee.duckdb clients
Employee 3 /var/lib/cluster-duck/employee.duckdb merchandiseEvery generated desk incorporates an applicable artificial knowledge set of ten million information. Listed below are the primary 5 information of every to present you a greater thought what’s in them.
query-1 (worker-1) - choose * from gross sales restrict 5
sale_id customer_id product_id amount sales_channel payment_method sale_status catalogue_price sold_unit_price discount_pct sale_date
------- ----------- ---------- -------- ------------- -------------- ----------- --------------- --------------- ------------ ----------
1 7,920 104,730 2 retailer bank_transfer shipped 1,052.3 999.69 0.05 2024-01-02
2 15,839 209,459 3 market pockets processing 99.59 89.63 0.1 2024-01-03
3 23,758 314,188 4 phone bill returned 1,146.88 974.85 0.15 2024-01-04
4 31,677 418,917 5 on-line card cancelled 194.17 194.17 0 2024-01-05
5 39,596 523,646 1 retailer bank_transfer accomplished 1,241.46 1,179.39 0.05 2024-01-06
query-2 (worker-2) - choose * from clients restrict 5
customer_id customer_code nation section membership_tier is_active credit_limit joined_date last_seen_at
----------- --------------- ------- -------------- --------------- --------- ------------ ----------- -------------------
1 CUST-0000000001 US small_business silver True 250.10 2015-01-02 2025-01-01 00:00:01
2 CUST-0000000002 DE enterprise gold True 250.20 2015-01-03 2025-01-01 00:00:02
3 CUST-0000000003 FR public_sector customary True 250.30 2015-01-04 2025-01-01 00:00:03
4 CUST-0000000004 CA shopper silver True 250.40 2015-01-05 2025-01-01 00:00:04
5 CUST-0000000005 AU small_business gold True 250.50 2015-01-06 2025-01-01 00:00:05
query-3 (worker-3) - choose * from merchandise restrict 5
product_id sku class model supplier_region catalogue_price stock_quantity discontinued introduced_date
---------- -------------- -------- ------- --------------- --------------- -------------- ------------ ---------------
1 SKU-0000000001 residence Bramble EU 5.01 13 False 2020-01-02
2 SKU-0000000002 backyard Cobalt US 5.02 26 False 2020-01-03
3 SKU-0000000003 sports activities Dove APAC 5.03 39 False 2020-01-04
4 SKU-0000000004 clothes Elm UK 5.04 52 False 2020-01-05
5 SKU-0000000005 meals Aster EU 5.05 65 False 2020-01-06The information is generated instantly inside every DuckDB database through the first EC2 bootstrap. It isn’t uploaded out of your pc or copied between servers. Every server follows this sequence.
1. Decide which employee it is
CloudFormation offers each EC2 occasion a WorkerIndex tag:
Employee 1 → WorkerIndex=1
Employee 2 → WorkerIndex=2
Employee 3 → WorkerIndex=3
The bootstrap script reads that tag by way of the EC2 Occasion Metadata Service:
WORKER_INDEX=$(curl -fsS
-H "X-aws-ec2-metadata-token: $IMDS_TOKEN"
http://169.254.169.254/newest/meta-data/tags/occasion/WorkerIndex)2. Run the data-generation program
CloudFormation installs this program on each server:
/choose/cluster-duck/seed_related_data.pyIt then runs:
/choose/cluster-duck-venv/bin/python /choose/cluster-duck/seed_related_data.py
--worker "$WORKER_INDEX"
--rows 10000000The row depend comes from the CloudFormation RowCount parameter, which defaults to 10 million.
3. Open the employee’s DuckDB file
This system opens:
/var/lib/cluster-duck/employee.duckdbwhich incorporates this code.
with duckdb.join(str(args.database)) as connection:
connection.execute(
CREATE_SQL[args.worker],
{"row_count": args.rows},
)Each server makes use of the identical database filename, but it surely’s a unique file on a unique EC2 occasion.
Accessing your coordinator terminal window
We wish to run some demos, and for that you just want to have the ability to entry the CLI terminal of your coordinator EC2 server. To try this open the AWS console and go to the EC2 console. You’ll see a display screen like this.
Click on the Occasion ID that corresponds to your coordinator occasion. On the subsequent display screen there can be a Join button in direction of the highest proper nook. Click on that. You’ll see this display screen
Be sure you’ve chosen the SSM Session Supervisor radio button, then click on the Join button on the backside proper of the display screen. That ought to provide you with entry to the CLI terminal window like this,
Examples
Within the following examples, to make issues as clear as doable I exploit the uncooked SQL textual content within the code snippets, nevertheless it’s additionally doable to retailer the SQL in separate information and use these information as enter. For instance,
[root@ip-10-42-0-10 ~]# cluster-duck-sql
--query-file "worker-1=/root/cluster-duck-sql/gross sales.sql"
--query-file "worker-2=/root/cluster-duck-sql/clients.sql"
--query-file "worker-3=/root/cluster-duck-sql/merchandise.sql"1. Working some easy SQL statements
Within the terminal CLI, sort within the following code.
sh-5.2$ sudo -i
[root@ip-10-42-0-10 ~]# cluster-duck-sql
--query "worker-1=SELECT sale_status, COUNT(*) FROM gross sales GROUP BY sale_status ORDER BY sale_status"
--query "worker-2=SELECT nation, COUNT(*) FROM clients GROUP BY nation ORDER BY nation"
--query "worker-3=SELECT class, COUNT(*) FROM merchandise GROUP BY class ORDER BY class"
# output
#
Concurrent distant queries
employee desk start_offset_ms duration_seconds
-------- ------- --------------- ----------------
worker-1 query-1 0.996 0.572
worker-2 query-2 5.514 0.572
worker-3 query-3 0.738 0.54
Begin unfold: 4.776 ms
query-1 (worker-1)
sale_status count_star()
----------- ------------
cancelled 2,000,000
accomplished 2,000,000
processing 2,000,000
returned 2,000,000
shipped 2,000,000
query-2 (worker-2)
nation count_star()
------- ------------
AU 1,666,666
CA 1,666,667
DE 1,666,667
FR 1,666,667
UK 1,666,666
US 1,666,667
query-3 (worker-3)
class count_star()
----------- ------------
clothes 1,666,667
electronics 1,666,666
meals 1,666,666
backyard 1,666,667
residence 1,666,667
sports activities 1,666,6672. Some advanced SQL (I’ve minimize out among the output to save lots of area)
[root@ip-10-42-0-10 ~]# time cluster-duck-sql
--query "worker-1=WITH every day AS (
SELECT
sale_date,
sales_channel,
payment_method,
sale_status,
COUNT(*) AS transaction_count,
SUM(amount) AS items,
SUM(amount * sold_unit_price) AS income,
AVG(discount_pct) AS average_discount,
QUANTILE_CONT(sold_unit_price, 0.50) AS median_price,
QUANTILE_CONT(sold_unit_price, 0.95) AS p95_price
FROM gross sales
GROUP BY ALL
),
analysed AS (
SELECT
*,
SUM(income) OVER (
PARTITION BY sales_channel
ORDER BY sale_date
ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
) AS rolling_30_row_revenue,
RANK() OVER (
PARTITION BY sale_date
ORDER BY income DESC
) AS daily_revenue_rank
FROM every day
)
SELECT *
FROM analysed
WHERE daily_revenue_rank <= 3
ORDER BY sale_date DESC, daily_revenue_rank
LIMIT 100"
--query "worker-2=WITH customer_groups AS (
SELECT
nation,
section,
membership_tier,
is_active,
YEAR(joined_date) AS joined_year,
CASE
WHEN credit_limit < 2500 THEN 'under_2500'
WHEN credit_limit < 5000 THEN '2500_to_4999'
WHEN credit_limit < 7500 THEN '5000_to_7499'
ELSE '7500_plus'
END AS credit_band,
COUNT(*) AS customer_count,
AVG(credit_limit) AS average_credit_limit,
STDDEV_POP(credit_limit) AS credit_limit_stddev,
QUANTILE_CONT(credit_limit, 0.50) AS median_credit_limit,
QUANTILE_CONT(credit_limit, 0.95) AS p95_credit_limit,
MIN(joined_date) AS first_joined,
MAX(last_seen_at) AS most_recent_activity
FROM clients
GROUP BY ALL
),
ranked AS (
SELECT
*,
SUM(customer_count) OVER (
PARTITION BY nation
) AS country_total,
RANK() OVER (
PARTITION BY nation
ORDER BY customer_count DESC
) AS group_rank
FROM customer_groups
)
SELECT
*,
ROUND(100.0 * customer_count / country_total, 2) AS percentage_of_country
FROM ranked
WHERE group_rank <= 10
ORDER BY nation, group_rank
LIMIT 100"
--query "worker-3=WITH inventory_groups AS (
SELECT
class,
model,
supplier_region,
discontinued,
YEAR(introduced_date) AS introduced_year,
COUNT(*) AS product_count,
SUM(stock_quantity) AS stock_units,
SUM(stock_quantity * catalogue_price) AS inventory_value,
AVG(catalogue_price) AS average_price,
STDDEV_POP(catalogue_price) AS price_stddev,
QUANTILE_CONT(catalogue_price, 0.50) AS median_price,
QUANTILE_CONT(catalogue_price, 0.95) AS p95_price
FROM merchandise
GROUP BY ALL
),
ranked AS (
SELECT
*,
SUM(inventory_value) OVER (
PARTITION BY class
) AS category_inventory_value,
RANK() OVER (
PARTITION BY class
ORDER BY inventory_value DESC
) AS inventory_rank
FROM inventory_groups
)
SELECT
*,
ROUND(
100.0 * inventory_value / category_inventory_value,
2
) AS percentage_of_category_value
FROM ranked
WHERE inventory_rank <= 10
ORDER BY class, inventory_rank
LIMIT 100"
#
# Output
Concurrent distant queries
employee desk start_offset_ms duration_seconds
-------- ------- --------------- ----------------
worker-1 query-1 1.673 15.491
worker-2 query-2 1.84 4.045
worker-3 query-3 1.42 4.045
Begin unfold: 0.420 ms
query-1 (worker-1)
sale_date sales_channel payment_method sale_status transaction_count items income average_discount median_price p95_price rolling_30_row_revenue daily_revenue_rank
---------- ------------- -------------- ----------- ----------------- ------ ------------- ---------------- ------------ --------- ---------------------- ------------------
2025-12-30 retailer bank_transfer cancelled 6,849 34,245 32,722,337.65 0.05 955.72 1,810.568 588,590,148.25 1
...
...
...
2025-11-11 market pockets accomplished 6,849 6,849 6,199,619.04 0.1 905.5 1,714.096 557,458,798.88 2
query-2 (worker-2)
nation section membership_tier is_active joined_year credit_band customer_count average_credit_limit credit_limit_stddev median_credit_limit p95_credit_limit first_joined most_recent_activity country_total group_rank percentage_of_country
------- -------------- --------------- --------- ----------- ----------- -------------- -------------------- ------------------- ------------------- ---------------- ------------ -------------------- ------------- ---------- ---------------------
AU small_business gold True 2,017 7500_plus 21,659 8,874.317 794.81 8878.10 10115.70 2017-01-01 2025-04-26 17:20:41 1,666,666 1 1.3
AU public_sector gold True 2,017 7500_plus 21,649 8,873.263 794.868 8876.70 10114.30 2017-01-01 2025-04-26 17:20:35 1,666,666 2 1.3
...
...
...
US small_business silver True 2,022 7500_plus 21,605 8,876.671 792.939 8873.30 10110.10 2022-01-01 2025-04-26 17:46:37 1,666,667 10 1.3
query-3 (worker-3)
class model supplier_region discontinued introduced_year product_count stock_units inventory_value average_price price_stddev median_price p95_price category_inventory_value inventory_rank percentage_of_category_value
----------- ------- --------------- ------------ --------------- ------------- ----------- --------------- ------------- ------------ ------------ --------- ------------------------ -------------- ----------------------------
clothes Aster US False 2,020 33,457 83,743,010 84191813103.00 1,004.833 577.373 1004.90 1904.90 4188462626332.28 1 2.01
clothes Aster UK False 2,020 33,458 83,228,180 83715767600.00 1,005.04 577.369 1005.20 1905.42 4188462626332.28 2 2
...
...
...
residence Dove APAC False 2,024 32,999 82,797,401 83251410292.23 1,004.931 577.217 1005.23 1904.83 4190160306717.15 8 1.99
residence Dove APAC False 2,020 33,008 82,760,072 83227553226.96 1,005.078 577.503 1005.33 1905.43 4190160306717.15 9 1.99
...
...
sports activities Elm EU False 2,020 33,006 82,731,982 83205651717.58 1,005.109 577.373 1005.19 1905.44 4190156973777.15 9 1.99
sports activities Bramble EU False 2,020 33,005 82,700,285 83167881906.05 1,004.964 577.415 1005.21 1905.36 4190156973777.15 10 1.98
actual 0m17.664s
consumer 0m1.643s
sys 0m0.389s
[root@ip-10-42-0-10 ~]#3. Concurrent writes/reads
To point out this we’ll concurrently write 20 new information into our gross sales desk and hearth off three reads. Every learn sees a constant snapshot containing whichever impartial insert transactions had dedicated earlier than that learn started. It could subsequently see none, some or the entire new rows, but it surely is not going to see half of a person insert or a outcome that modifications whereas the SELECT is being scanned.
Each insert on this instance is a separate autocommit transaction. If 19 inserts succeed and one fails, the 19 profitable writes stay dedicated. There isn’t any distributed transaction and Cluster-Duck doesn’t roll profitable statements again.
To make the demonstration repeatable, first take away any rows left by any earlier runs:
[root@ip-10-42-0-10 ~]# cluster-duck-sql
--allow-write
--query "worker-1=DELETE FROM gross sales WHERE sale_id BETWEEN 30000001 AND 30000020"Word additionally that in an effort to make modifications to knowledge in a database we must always provide the — allow-write argument.
Now run the 20 inserts and three reads collectively. To see what SQL is run for every of the question labels within the output we will use the — show-sql argument.
[root@ip-10-42-0-10 ~]# cluster-duck-sql
--show-sql
--allow-write
--query "worker-1=INSERT INTO gross sales VALUES (30000001,1,1,1,'on-line','card','accomplished',100.00,100.00,0.0,DATE '2026-08-09')"
--query "worker-1=INSERT INTO gross sales VALUES (30000002,2,2,2,'retailer','bank_transfer','processing',110.00,104.50,0.05,DATE '2026-08-09')"
--query "worker-1=INSERT INTO gross sales VALUES (30000003,3,3,3,'market','pockets','shipped',120.00,108.00,0.10,DATE '2026-08-09')"
--query "worker-1=INSERT INTO gross sales VALUES (30000004,4,4,4,'phone','bill','accomplished',130.00,110.50,0.15,DATE '2026-08-09')"
--query "worker-1=INSERT INTO gross sales VALUES (30000005,5,5,5,'on-line','card','processing',140.00,112.00,0.20,DATE '2026-08-09')"
--query "worker-1=INSERT INTO gross sales VALUES (30000006,6,6,1,'retailer','bank_transfer','shipped',150.00,150.00,0.0,DATE '2026-08-09')"
--query "worker-1=INSERT INTO gross sales VALUES (30000007,7,7,2,'market','pockets','accomplished',160.00,152.00,0.05,DATE '2026-08-09')"
--query "worker-1=INSERT INTO gross sales VALUES (30000008,8,8,3,'phone','bill','processing',170.00,153.00,0.10,DATE '2026-08-09')"
--query "worker-1=INSERT INTO gross sales VALUES (30000009,9,9,4,'on-line','card','shipped',180.00,153.00,0.15,DATE '2026-08-09')"
--query "worker-1=INSERT INTO gross sales VALUES (30000010,10,10,5,'retailer','bank_transfer','accomplished',190.00,152.00,0.20,DATE '2026-08-09')"
--query "worker-1=INSERT INTO gross sales VALUES (30000011,11,11,1,'market','pockets','processing',200.00,200.00,0.0,DATE '2026-08-09')"
--query "worker-1=INSERT INTO gross sales VALUES (30000012,12,12,2,'phone','bill','shipped',210.00,199.50,0.05,DATE '2026-08-09')"
--query "worker-1=INSERT INTO gross sales VALUES (30000013,13,13,3,'on-line','card','accomplished',220.00,198.00,0.10,DATE '2026-08-09')"
--query "worker-1=INSERT INTO gross sales VALUES (30000014,14,14,4,'retailer','bank_transfer','processing',230.00,195.50,0.15,DATE '2026-08-09')"
--query "worker-1=INSERT INTO gross sales VALUES (30000015,15,15,5,'market','pockets','shipped',240.00,192.00,0.20,DATE '2026-08-09')"
--query "worker-1=INSERT INTO gross sales VALUES (30000016,16,16,1,'phone','bill','accomplished',250.00,250.00,0.0,DATE '2026-08-09')"
--query "worker-1=INSERT INTO gross sales VALUES (30000017,17,17,2,'on-line','card','processing',260.00,247.00,0.05,DATE '2026-08-09')"
--query "worker-1=INSERT INTO gross sales VALUES (30000018,18,18,3,'retailer','bank_transfer','shipped',270.00,243.00,0.10,DATE '2026-08-09')"
--query "worker-1=INSERT INTO gross sales VALUES (30000019,19,19,4,'market','pockets','accomplished',280.00,238.00,0.15,DATE '2026-08-09')"
--query "worker-1=INSERT INTO gross sales VALUES (30000020,20,20,5,'phone','bill','processing',290.00,232.00,0.20,DATE '2026-08-09')"
--query "worker-1=SELECT COUNT(*) AS visible_rows FROM gross sales WHERE sale_id BETWEEN 30000001 AND 30000020"
--query "worker-1=SELECT COUNT(*) AS visible_rows FROM gross sales WHERE sale_id BETWEEN 30000001 AND 30000020"
--query "worker-1=SELECT COUNT(*) AS visible_rows FROM gross sales WHERE sale_id BETWEEN 30000001 AND 30000020"
#
# Output
Concurrent distant queries
employee desk start_offset_ms duration_seconds
-------- -------- --------------- ----------------
worker-1 query-19 77.923 3.648
worker-1 query-11 26.242 3.705
worker-1 query-23 4.64 3.727
worker-1 query-5 8.747 3.691
worker-1 query-1 4.832 3.744
worker-1 query-2 5.243 3.903
worker-1 query-15 113.315 3.823
worker-1 query-6 15.126 3.923
worker-1 query-22 84.907 3.853
worker-1 query-14 35.127 3.907
worker-1 query-18 71.325 3.871
worker-1 query-21 56.68 3.888
worker-1 query-12 28.05 3.936
worker-1 query-7 15.349 3.949
worker-1 query-20 89.082 3.875
worker-1 query-10 36.901 3.93
worker-1 query-16 77.02 3.895
worker-1 query-13 19.964 3.954
worker-1 query-3 5.632 3.968
worker-1 query-8 58.938 3.916
worker-1 query-9 15.774 3.959
worker-1 query-4 50.36 3.954
worker-1 query-17 84.575 3.936
Begin unfold: 108.675 ms
query-19 (worker-1)
SQL:
INSERT INTO gross sales VALUES (30000019,19,19,4,'market','pockets','accomplished',280.00,238.00,0.15,DATE '2026-08-09')
Consequence:
Depend
-----
1
query-11 (worker-1)
SQL:
INSERT INTO gross sales VALUES (30000011,11,11,1,'market','pockets','processing',200.00,200.00,0.0,DATE '2026-08-09')
Consequence:
Depend
-----
1
query-23 (worker-1)
SQL:
SELECT COUNT(*) AS visible_rows FROM gross sales WHERE sale_id BETWEEN 30000001 AND 30000020
Consequence:
visible_rows
------------
4
...
...
query-22 (worker-1)
SQL:
SELECT COUNT(*) AS visible_rows FROM gross sales WHERE sale_id BETWEEN 30000001 AND 30000020
Consequence:
visible_rows
------------
16
...
...
query-18 (worker-1)
SQL:
INSERT INTO gross sales VALUES (30000018,18,18,3,'retailer','bank_transfer','shipped',270.00,243.00,0.10,DATE '2026-08-09')
Consequence:
Depend
-----
1
query-21 (worker-1)
SQL:
SELECT COUNT(*) AS visible_rows FROM gross sales WHERE sale_id BETWEEN 30000001 AND 30000020
Consequence:
visible_rows
------------
13
...
...
query-17 (worker-1)
SQL:
INSERT INTO gross sales VALUES (30000017,17,17,2,'on-line','card','processing',260.00,247.00,0.05,DATE '2026-08-09')
Consequence:
Depend
-----
1
[root@ip-10-42-0-10 ~]#The output exhibits that by the point query-23 ran, 4 information had been inserted. By the point query-22 ran, 16 information had been inserted and for query-21, 13 new information had been written. This is sensible as we will see from the start_offset_ms timings that the order of operating for the queries was query-23, then query21, and at last query-22.
5. You may run DDL too
Create 3 new tables, one in every database , then question them.
[root@ip-10-42-0-10 ~]# cluster-duck-sql
--allow-write
--query "worker-1=CREATE OR REPLACE TABLE sales_agg AS SELECT sale_status, COUNT(*) AS sale_count FROM gross sales WHERE sale_status = 'cancelled' GROUP BY sale_status"
--query "worker-2=CREATE OR REPLACE TABLE customers_agg AS SELECT nation, COUNT(*) AS customer_count FROM clients WHERE nation = 'UK' GROUP BY nation"
--query "worker-3=CREATE OR REPLACE TABLE products_agg AS SELECT class, COUNT(*) AS product_count FROM merchandise WHERE class = 'sports activities' GROUP BY class"
cluster-duck-sql
--query "worker-1=SELECT * FROM sales_agg ORDER BY sale_count DESC"
--query "worker-2=SELECT * FROM customers_agg ORDER BY customer_count DESC"
--query "worker-3=SELECT * FROM products_agg ORDER BY product_count DESC"
#
# Output
Concurrent distant queries
employee desk start_offset_ms duration_seconds
-------- ------- --------------- ----------------
worker-1 query-1 1.141 0.599
worker-2 query-2 1.033 0.603
worker-3 query-3 0.755 0.622
Begin unfold: 0.386 ms
query-1 (worker-1)
Depend
-----
1
query-2 (worker-2)
Depend
-----
1
query-3 (worker-3)
Depend
-----
1
Concurrent distant queries
employee desk start_offset_ms duration_seconds
-------- ------- --------------- ----------------
worker-1 query-1 0.925 0.464
worker-2 query-2 1.178 0.448
worker-3 query-3 0.675 0.467
Begin unfold: 0.503 ms
query-1 (worker-1)
sale_status sale_count
----------- ----------
cancelled 2,000,000
query-2 (worker-2)
nation customer_count
------- --------------
UK 1,666,666
query-3 (worker-3)
class product_count
-------- -------------
sports activities 1,666,667The price of all this.
The price of this set-up shouldn’t be a priority. For a begin, DuckDB and Quack are free to obtain and use. The three EC2 servers that I’m standing up are tiny t4g.nano situations. In addition to that, we now have three 8 GB gp3 volumes, public IPv4 addresses, Methods Supervisor, Parameter Retailer and a Lambda customized useful resource. The Lambda invocation is short-lived, however stays deployed till the stack is deleted. This Lambda known as TokenManagerFunction within the CloudFormation template. Its solely job is to handle the three Quack authentication tokens. It really works like this,
CloudFormation stack creation
↓
Invoke TokenManager Lambda
↓
Generate three random 64-character tokens
↓
Retailer them as SecureString parameters in SSM
↓
Return success and ceaseRight here is an estimated price if we ran this entire set-up for 4 hours.
Element Approximate price
4 hours of EBS $0.011
4 hours of EC2 $0.050
4 hours of public IPv4 $0.060
Whole $0.121The $0.121 determine is an estimate for us-east-2, earlier than any credit or free-tier allowances, tax and data-transfer expenses. Qualifying AWS Free Tier customers might obtain some public IPv4 hours at no cost. AWS payments gp3 storage in per-second increments, with a 60-second minimal.
For peace of thoughts, although, I might at all times advise tearing down any AWS infrastructure created after you’re accomplished with it. That is simply accomplished in case you use CloudFormation by operating the next command with the AWS CLI.
aws cloudformation delete-stack
--region us-east-2
--stack-name cluster-duck-test-v2Abstract
I created the “cluster-duck” repo to check DuckDB’s new Quack communications protocol. Quack permits DuckDB databases on completely different servers to “discuss” to one another over HTTP, and DuckDB is positioning it as an enabler of shopper/server communications between DuckDB databases.
This improvement is probably very helpful and I used to be notably to see how nicely Quack dealt with concurrent reads and writes to and from a distant database.
In my exams, and within the instance I demonstrated, the reply appears to be it handles it fairly nicely.
The DuckDB group have acknowledged that Quack is an experimental function and really a lot a work-in-progress. To that time, you’ll be able to anticipate potential modifications to the protocol, operate names, settings and defaults, so positively don’t use Quack for any manufacturing methods.
Hopefully the ideas I’ve outlined on this article can be helpful in case you have a have to run parallel queries or different SQL statements moere typically in opposition to DuckDB databases operating on completely different servers.
I can’t assist however surprise what future plans DuckDB have for Quack. If it turns into a completely supported a part of the DuckDB eco-system I might positively see Quack supplying the transport and session dealing with to a future distributed DuckDB engine, however that’s most likely a good distance off. However, even it simply does what its able to now, Quack can be helpful in its personal proper.
That’s all from me for now. You may entry all of the code, CloudFormation template and so forth… in my GitHub repo at:
https://github.com/taupirho/cluster-duck
Extra data on DuckDB and Quack may be discovered within the DuckDB on-line documentation at this hyperlink,
https://duckdb.org/docs/present
PS I’m in the marketplace for contract work simply now. If you happen to or somebody you realize is on the lookout for an skilled knowledge engineer, both distant or primarily based in Edinburgh, UK, with abilities in AWS, AI, Python, SQL, PySpark, DuckDB, and so forth., let me know by way of LinkedIn
