In late 2023, the Java community started a challenge to find the most efficient way to process a file with 1 billion rows of data. Unsurprisingly, many database communities quickly took on the same challenge with varying results. Postgres, in many cases, performed the worst without close attention to settings and efficient resource utilization. But, with a little more effort, could it compete head-to-head?

In this session, we’ll look at the original challenge and how to approach it with vanilla Postgres beyond the basics. Next, we’ll explore how the increasingly popular in-memory analytics database, DuckDB, handles the same challenge. Finally, we’ll explore recent opportunities to integrate the two databases together to provide a powerful analytical engine with Postgres for the best of both worlds.

Postgres.sql script:

--CREATE DATABASE obrc;

SHOW shared_buffers;

CREATE UNLOGGED TABLE obrc
(
  station_name VARCHAR(26),
  measurement  NUMERIC(3,1)
);

-- Use to override server default
ALTER TABLE obrc SET (parallel_workers=16);
-- this took ~3.0 minutes in a single table
COPY obrc(station_name, measurement)
 FROM '/mnt/1brc/measurements.txt'
 WITH
 (
  FORMAT CSV,
  DELIMITER ';'
 );

SELECT * FROM obrc LIMIT 20;

-- This will take a very long time to complete
SELECT count(*) FROM obrc;

ANALYZE obrc;
-- If ANALYZE is up to date
SELECT reltuples::numeric as count
FROM pg_class
WHERE relname='obrc';
/*
 * ~34 seconds
 */
EXPLAIN analyze
SELECT
  station_name,
  MIN(measurement) AS min_measurement,
  ROUND(AVG(measurement),1) AS mean_measurement,
  MAX(measurement) AS max_measurement
 FROM obrc
 GROUP BY station_name
 ORDER BY station_name;

/*
 * ~34 seconds
 */
EXPLAIN analyze
SELECT
  '{' ||
    STRING_AGG(station_name || '=' || min_measurement || '/' || mean_measurement || '/' || max_measurement, ', ' ORDER BY station_name) ||
  '}' AS result
 FROM
  (SELECT station_name,
          MIN(measurement) AS min_measurement,
          ROUND(AVG(measurement), 1) AS mean_measurement,
          MAX(measurement) AS max_measurement
    FROM obrc
     GROUP BY station_name
  );

-- partitioned by station name
CREATE UNLOGGED TABLE obrc_partitioned
(
  station_name VARCHAR(26),
  measurement  NUMERIC(3,1)
) PARTITION BY hash(station_name);

TRUNCATE obrc_partitioned;

CREATE UNLOGGED TABLE obrc_p0
PARTITION OF obrc_partitioned 
FOR VALUES WITH (modulus 10, remainder 0);

CREATE UNLOGGED TABLE obrc_p1
PARTITION OF obrc_partitioned 
FOR VALUES WITH (modulus 10, remainder 1);

CREATE UNLOGGED TABLE obrc_p2
PARTITION OF obrc_partitioned 
FOR VALUES WITH (modulus 10, remainder 2);

CREATE UNLOGGED TABLE obrc_p3
PARTITION OF obrc_partitioned 
FOR VALUES WITH (modulus 10, remainder 3);

CREATE UNLOGGED TABLE obrc_p4
PARTITION OF obrc_partitioned 
FOR VALUES WITH (modulus 10, remainder 4);

CREATE UNLOGGED TABLE obrc_p5
PARTITION OF obrc_partitioned 
FOR VALUES WITH (modulus 10, remainder 5);

CREATE UNLOGGED TABLE obrc_p6
PARTITION OF obrc_partitioned 
FOR VALUES WITH (modulus 10, remainder 6);

CREATE UNLOGGED TABLE obrc_p7
PARTITION OF obrc_partitioned 
FOR VALUES WITH (modulus 10, remainder 7);

CREATE UNLOGGED TABLE obrc_p8
PARTITION OF obrc_partitioned 
FOR VALUES WITH (modulus 10, remainder 8);

CREATE UNLOGGED TABLE obrc_p9
PARTITION OF obrc_partitioned 
FOR VALUES WITH (modulus 10, remainder 9);

-- This doesn't make a difference because COPY is 
-- single threaded
COPY obrc_partitioned(station_name, measurement)
 FROM '/tmp/measurements.txt'
 WITH
 (
  FORMAT CSV,
  DELIMITER ';'
 );

EXPLAIN analyze
SELECT
  station_name,
  MIN(measurement) AS min_measurement,
  ROUND(AVG(measurement),1) AS mean_measurement,
  MAX(measurement) AS max_measurement
 FROM obrc_partitioned
 GROUP BY station_name
 ORDER BY station_name;


EXPLAIN analyze
SELECT
  '{' ||
    STRING_AGG(station_name || '=' || min_measurement || '/' || mean_measurement || '/' || max_measurement, ', ' ORDER BY station_name) ||
  '}' AS result
 FROM
  (SELECT station_name,
          MIN(measurement) AS min_measurement,
          ROUND(AVG(measurement), 1) AS mean_measurement,
          MAX(measurement) AS max_measurement
    FROM obrc_partitioned
     GROUP BY station_name
  );


 /*
  * DuckDB
  * Stop Postgres Container
  * 
  * 
  */
 .duckdb/cli/latest/duckdb
 .timer ON
 
 SELECT * FROM READ_CSV_AUTO('/tmp/measurements.txt') LIMIT 5;
 
 CREATE OR REPLACE TABLE measurements AS
        SELECT * FROM READ_CSV('/tmp/measurements.txt', 
        		header=false, 
        		columns= {'station_name':'VARCHAR','measurement':'double'}, 
        		delim=';');

SELECT station_name,count(*) FROM measurements
GROUP BY 1;

SELECT station_name, 
           MIN(measurement),
           AVG(measurement),
           MAX(measurement)
    FROM measurements 
    GROUP BY station_name
    LIMIT 5;

   
WITH src AS (SELECT station_name, 
                    MIN(measurement) AS min_measurement,
                    CAST(AVG(measurement) AS DECIMAL(8,1)) AS mean_measurement,
                    MAX(measurement) AS max_measurement
            FROM measurements 
            GROUP BY station_name
            LIMIT 5)
    SELECT station_name || '=' || CONCAT_WS('/',min_measurement, mean_measurement, max_measurement)
    FROM src;
 
   
/*
 * Stop duckdb
 * Start pg_duckdb container
 */
--CALL duckdb.recycle_ddb();
--ALTER SYSTEM SET duckdb.max_memory='15GB';
--SELECT * FROM pg_available_extensions ORDER BY name;
--CREATE EXTENSION pg_duckdb;
--truncate obrc;
--SHOW duckdb.max_memory;

-- Can still use duckdb functions for reading data
SELECT * FROM read_csv('/mnt/1brc/measurements.txt') LIMIT 20;
SELECT count(*) FROM read_csv('/mnt/1brc/measurements.txt');

-- Not supported yet
INSERT INTO obrc
SELECT * FROM read_csv('/mnt/1brc/measurements.txt') r LIMIT 20;

CREATE TEMPORARY TABLE obrc_temp(
	station_name text, 
	measurment NUMERIC(10,2)) USING duckdb;

/*
 * also takes ~1.5 minutes
 */
COPY obrc_temp FROM '/mnt/1brc/measurements.txt';

SELECT count(*) FROM obrc_temp;

/*
 * ~7 seconds
 */
SELECT station_name,count(*) FROM obrc_temp
GROUP BY 1;

/*
 * On my Docker image this didn't have an impact
 * for some reason.
 */
SET duckdb.max_workers_per_postgres_scan=12;
SET duckdb.worker_threads=12;
SET duckdb.force_execution=true;

/*
 * We can utilize the native file formats
 */
COPY (SELECT * FROM obrc_temp) TO '/mnt/1brc/measurements.parqet' (format parquet);

/*
 * pull station names out for later transformation and JOIN queries
 */
COPY (SELECT DISTINCT(station_name) FROM obrc_test) TO '/mnt/1brc/stations.txt';

CREATE TABLE station(
	id INT generated ALWAYS AS identity, 
	station_name text);

COPY station(station_name) FROM '/mnt/1brc/stations.txt';

--DROP TABLE station;
SELECT * FROM station;

/*
 * 12 seconds
 */
--EXPLAIN analyze
SELECT
  id, obrc_temp.station_name,
  MIN(measurment) AS min_measurement,
  ROUND(AVG(measurment),1) AS mean_measurement,
  MAX(measurment) AS max_measurement
 FROM obrc_temp JOIN station using (station_name)
 GROUP BY id, station_name
 ORDER BY station_name;

SELECT station_name, count(*) FROM obrc_temp
GROUP BY 1;

/*
 * playing with timestamps
 */
WITH dates AS (
	--SELECT o,t FROM generate_series('2024-01-01','2025-12-31','30 seconds'::interval) WITH ordinality AS g(t,o)
	SELECT ROW_NUMBER() OVER() AS o,t FROM generate_series('2024-01-01','2025-12-31','30 seconds'::interval) AS g(t)
),
m AS (
	SELECT row_number() over() AS o, station_name, measurment 
	FROM obrc_temp
	WHERE station_name='Helsinki'
)
INSERT INTO obrc_temp SELECT t, station_name, measurment
FROM dates
JOIN m USING (o);

SELECT time_bucket('1 day',ts) bucket, station_name, avg(measurement) FROM obrc_test2
GROUP BY 1,2
ORDER BY 1;

CREATE TEMPORARY TABLE obrc_temp2(
	ts timestamptz,
	station_name text,
	measurement numeric(10,2)
) using duckdb;

/*
 * OOM error trying to process
 */
SELECT row_number() OVER(PARTITION BY station_name), * FROM obrc_test LIMIT 10;




/*
 * Stop pg_duckdb container
 * Start pg_mooncake container
 */
CREATE DATABASE obrc;
CREATE EXTENSION pg_mooncake;
DROP TABLE obrc;
CREATE TABLE obrc
(
  station_name VARCHAR(26),
  measurement  NUMERIC(3,1)
);

CALL mooncake.create_table('obrc_iceberg', 'obrc');

-- not supported
ALTER TABLE obrc SET (parallel_workers=16);


-- Not supported yet
INSERT INTO obrc
SELECT station_name,measurement 
FROM mooncake.read_csv('/mnt/obrc/measurements.txt') 
	AS (station_name text, measurement float4) LIMIT 20;


-- Works as normal
-- Back of napkind ~20% faster
COPY obrc(station_name, measurement)
 FROM '/mnt/obrc/measurements.txt'
 WITH
 (
  FORMAT CSV,
  DELIMITER ';'
 );

-- This didn't seem to work while COPY was happening.
SELECT * FROM pg_stat_progress_copy;


select pg_size_pretty(pg_total_relation_size('obrc'));
SELECT count(*) FROM obrc;

 
SELECT * FROM obrc LIMIT 10;

--EXPLAIN analyze
SELECT
  station_name,
  MIN(measurement) AS min_measurement,
  ROUND(AVG(measurement),1) AS mean_measurement,
  MAX(measurement) AS max_measurement
 FROM obrc_iceberg
 GROUP BY station_name
 ORDER BY station_name;

TRUNCATE obrc;

postgres_config.sql script:

ALTER SYSTEM SET max_worker_processes=16;
ALTER SYSTEM SET max_parallel_workers=8;
ALTER SYSTEM SET max_parallel_workers_per_gather=8;
ALTER SYSTEM SET work_mem='1GB';
ALTER SYSTEM SET shared_buffers='20GB';
ALTER SYSTEM SET random_page_cost=1.1;
ALTER SYSTEM SET jit=OFF;