DecentDB vs DuckDB: When to Choose Which¶

-- Typical OLTP workload: point lookups and small writes
SELECT * FROM sessions WHERE token = $1;
UPDATE carts SET item_count = item_count + 1 WHERE user_id = $1;
INSERT INTO orders (user_id, total) VALUES ($1, $2) RETURNING id;

-- DecentDB: every COMMIT is durable. No settings to tune.
BEGIN;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- fsync'd before returning to caller

-- DecentDB: full FK support including CASCADE
CREATE TABLE users (id INT PRIMARY KEY, name TEXT);
CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT REFERENCES users(id) ON DELETE CASCADE
);
INSERT INTO orders (user_id) VALUES (999);  -- ERROR: FK violation
DELETE FROM users WHERE id = 1;             -- Cascades to orders

-- DuckDB: FK enforced, but CASCADE not supported
CREATE TABLE users (id INT PRIMARY KEY, name TEXT);
CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT REFERENCES users(id)  -- CASCADE syntax parsed but not enforced
);
INSERT INTO orders (user_id) VALUES (999);  -- ERROR: FK violation (enforced!)

-- DecentDB: audit trail via trigger
CREATE TABLE audit_log (id INT PRIMARY KEY, msg TEXT, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

CREATE TRIGGER log_user_insert AFTER INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION decentdb_exec_sql('INSERT INTO audit_log (msg) VALUES (''user created'')');

-- DecentDB: partial rollback within a transaction
BEGIN;
INSERT INTO orders (user_id, total) VALUES (1, 29.99);
SAVEPOINT sp1;
INSERT INTO order_items (order_id, sku) VALUES (1, 'INVALID-SKU');
ROLLBACK TO SAVEPOINT sp1;  -- undo the bad insert only
INSERT INTO order_items (order_id, sku) VALUES (1, 'SKU-123');
RELEASE SAVEPOINT sp1;
COMMIT;  -- order + valid item committed

-- DecentDB: recursively walk nested JSON
SELECT key, value, type, path
FROM json_tree('{"config":{"db":{"host":"localhost","port":5432},"cache":{"ttl":300}}}');

-- Returns:
-- config | {"db":{"host":"localhost","port":5432},"cache":{"ttl":300}} | object | $.config
-- db     | {"host":"localhost","port":5432}                          | object | $.config.db
-- host   | localhost                                                  | text   | $.config.db.host
-- port   | 5432                                                       | number | $.config.db.port
-- cache  | {"ttl":300}                                                | object | $.config.cache
-- ttl    | 300                                                        | number | $.config.cache.ttl

-- DecentDB: trigram index for substring search
CREATE INDEX idx_users_name_trgm ON users USING trigram(name);

-- Uses the trigram index instead of a full scan
SELECT * FROM users WHERE name LIKE '%alice%';

-- DecentDB
SELECT TOTAL(amount) FROM orders WHERE category = 'nonexistent';  -- returns 0.0
SELECT SUM(amount) FROM orders WHERE category = 'nonexistent';    -- returns NULL

-- DuckDB: no TOTAL(), must use COALESCE
SELECT COALESCE(SUM(amount), 0.0) FROM orders WHERE category = 'nonexistent';

DecentDB model:
  Process
    ├─ Writer thread (holds commit lock)
    ├─ Reader thread 1 (snapshot, no blocking)
    ├─ Reader thread 2 (snapshot, no blocking)
    └─ Reader thread N (snapshot, no blocking)

DuckDB model:
  Connection
    └─ Query runs with internal parallelism (multi-core within one query)

-- Analytical query: DuckDB excels here
SELECT
  DATE_TRUNC('month', order_date) AS month,
  product_category,
  COUNT(*) AS orders,
  SUM(amount) AS revenue,
  AVG(amount) AS avg_order_value
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY 1, 2
ORDER BY 1, 3 DESC;

-- DuckDB: query Parquet files directly
SELECT * FROM 'data/orders.parquet' WHERE amount > 100;

-- DuckDB: query S3 directly
SELECT * FROM 's3://my-bucket/logs/*.parquet' WHERE status = 'error';

-- DuckDB: query CSV with automatic schema inference
SELECT * FROM read_csv('data/users.csv', auto_detect = true) WHERE age > 30;

-- DuckDB: join a local table with a remote Parquet file
SELECT l.name, r.total
FROM local_users l
JOIN 's3://analytics/revenue.parquet' r ON l.id = r.user_id;

-- DuckDB: convert CSV to Parquet
COPY (SELECT * FROM read_csv('raw.csv')) TO 'output.parquet' (FORMAT PARQUET);

-- DuckDB: export query results to JSON
COPY (SELECT * FROM users WHERE active) TO 'active_users.json' (FORMAT JSON);

-- DuckDB: write to Parquet with compression and partitioning
COPY orders TO 'orders/' (FORMAT PARQUET, PARTITION_BY (year, month), COMPRESSION zstd);

-- DuckDB: this query runs in parallel across cores automatically
SELECT category, COUNT(*), AVG(price), SUM(quantity)
FROM line_items
GROUP BY category;

-- DuckDB: nested and complex types
CREATE TABLE events (
  id INT,
  tags LIST<TEXT>,
  metadata STRUCT(host TEXT, port INT),
  properties MAP(TEXT, TEXT)
);

SELECT tags FROM events WHERE 'error' IN tags;
SELECT metadata.host FROM events WHERE metadata.port = 8080;

-- DuckDB: statistical functions
SELECT
  CORR(x, y) AS correlation,
  COVAR_POP(x, y) AS covariance,
  REGR_SLOPE(y, x) AS slope,
  REGR_INTERCEPT(y, x) AS intercept
FROM measurements;

-- DuckDB: quantile with multiple methods
SELECT QUANTILE_CONT(amount, 0.95) FROM orders;
SELECT QUANTILE_DISC(amount, 0.5) FROM orders;
SELECT MEDIAN(amount) FROM orders;

-- DuckDB: rolling 7-day average
SELECT date, amount,
  AVG(amount) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7d_avg
FROM daily_sales;

-- DuckDB: cumulative sum with frame
SELECT date, amount,
  SUM(amount) OVER (
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM daily_sales;

-- DuckDB: install and load extensions
INSTALL spatial;
LOAD spatial;

-- Query GeoJSON with the spatial extension
SELECT name, ST_Area(geom) FROM countries;

-- DuckDB: httpfs for remote data
INSTALL httpfs;
LOAD httpfs;
SELECT * FROM 'https://example.com/data.parquet';

import duckdb
import pandas as pd

df = pd.read_csv("sales.csv")
# Query the DataFrame directly with SQL
result = duckdb.sql("SELECT category, SUM(amount) FROM df GROUP BY category").df()

-- DuckDB: LISTAGG with ordering and distinct
SELECT department,
  LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) AS team
FROM employees
GROUP BY department;

-- DuckDB: LISTAGG with DISTINCT
SELECT department,
  LISTAGG(DISTINCT role, ', ') WITHIN GROUP (ORDER BY role) AS roles
FROM employees
GROUP BY department;

-- Both support standard aggregates
SELECT COUNT(*), SUM(amount), AVG(amount), MIN(amount), MAX(amount) FROM orders;

-- Both support statistical aggregates
SELECT STDDEV(amount), VARIANCE(amount) FROM orders;

-- DuckDB: additional analytics functions
SELECT
  QUANTILE_CONT(amount, 0.5) AS median,
  QUANTILE_CONT(amount, 0.95) AS p95,
  SKEWNESS(amount) AS skew,
  KURTOSIS(amount) AS kurtosis
FROM orders;

-- DecentDB: equivalent using built-in functions
SELECT
  MEDIAN(amount) AS median,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY amount) AS p95
FROM orders;

-- DecentDB: durable by default, savepoints supported
BEGIN;
UPDATE inventory SET qty = qty - 1 WHERE sku = 'WIDGET';
SAVEPOINT sp1;
INSERT INTO shipments (order_id) VALUES (42);
ROLLBACK TO SAVEPOINT sp1;  -- undo shipment, keep inventory update
COMMIT;

-- DuckDB: savepoints not supported, durability is configurable
BEGIN;
UPDATE inventory SET qty = qty - 1 WHERE sku = 'WIDGET';
COMMIT;
-- No partial rollback available

-- DuckDB: query Parquet directly, no import step
SELECT user_id, SUM(amount) AS total
FROM 's3://analytics/2024/orders/*.parquet'
WHERE region = 'us-east'
GROUP BY user_id
HAVING total > 1000;

-- DecentDB: must import first, then query
-- Step 1: Load data into the .ddb file
-- Step 2: Run the query against the local table
SELECT user_id, SUM(amount) AS total
FROM orders
WHERE region = 'us-east'
GROUP BY user_id
HAVING total > 1000;

-- DecentDB: recursive JSON traversal
SELECT key, value, path FROM json_tree('{"a":{"b":1},"c":[2,3]}');

-- DuckDB: no json_tree(), use repeated json_extract() for known paths
SELECT
  json_extract(config, '$.a.b') AS a_b,
  json_extract(config, '$.c[0]') AS c_0
FROM settings;

-- DecentDB: RETURNING is supported
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com') RETURNING id;

-- DuckDB: RETURNING is also supported (both have it)
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com') RETURNING id;