Skip to main content

Loading Data

This recipe covers the ways to get rows into ScramDB: single inserts, upserts, and high-throughput bulk loads. Everything you load is visible to analytical queries the instant it commits, so there is no reload step and no waiting for a pipeline to catch up.

We will use one example table throughout:

CREATE TABLE products (
    id          BIGINT PRIMARY KEY,
    sku         VARCHAR(40) NOT NULL,
    name        VARCHAR(200),
    category    VARCHAR(60),
    price       DOUBLE PRECISION,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Insert a single row​

INSERT INTO products (id, sku, name, category, price)
VALUES (1, 'SKU-1001', 'Aluminum Bottle', 'kitchen', 19.99);

List the columns you are inserting. Any column you leave out takes its DEFAULT (here, updated_at fills in the current timestamp).

Insert many rows at once​

Batch related rows into one statement. One multi-row INSERT is far faster than many single-row inserts, because the whole batch is written together.

INSERT INTO products (id, sku, name, category, price)
VALUES
    (2, 'SKU-1002', 'Ceramic Mug',    'kitchen',   12.50),
    (3, 'SKU-1003', 'Steel Kettle',   'kitchen',   45.00),
    (4, 'SKU-1004', 'Cotton Apron',   'textiles',  22.00),
    (5, 'SKU-1005', 'Linen Napkins',  'textiles',  16.75);

Insert the results of a query​

Copy rows from one table into another with INSERT ... SELECT. This runs entirely inside the engine, so no data leaves the database.

CREATE TABLE IF NOT EXISTS products_archive (
    id BIGINT, sku VARCHAR(40), name VARCHAR(200), category VARCHAR(60),
    price DOUBLE PRECISION, updated_at TIMESTAMP
);
INSERT INTO products_archive
SELECT * FROM products
WHERE updated_at < DATE '2024-01-01';

Upsert: insert or update on conflict​

When a row might already exist, use ON CONFLICT to decide what happens on a key collision instead of failing.

Skip rows that already exist:

INSERT INTO products (id, sku, name, category, price)
VALUES (2, 'SKU-1002', 'Ceramic Mug', 'kitchen', 12.50)
ON CONFLICT (id) DO NOTHING;

Update the existing row instead. Use excluded to reference the values you tried to insert:

INSERT INTO products (id, sku, name, category, price)
VALUES (2, 'SKU-1002', 'Ceramic Mug', 'kitchen', 13.25)
ON CONFLICT (id) DO UPDATE
    SET price = excluded.price,
        updated_at = CURRENT_TIMESTAMP;

You can guard the update with a condition, so it only fires when it should:

INSERT INTO products (id, sku, name, category, price)
VALUES (2, 'SKU-1002', 'Ceramic Mug', 'kitchen', 13.25)
ON CONFLICT (id) DO UPDATE
    SET price = excluded.price
    WHERE products.price <> excluded.price;

Bulk load with COPY​

For large loads (thousands to millions of rows), COPY is the fast path. It streams the file in batches rather than parsing one statement per row.

Load a CSV that has a header line:

COPY products FROM '/data/products.csv' WITH (FORMAT CSV, HEADER true);

Choose a different delimiter for pipe- or tab-separated files:

COPY products FROM '/data/products.psv' WITH (FORMAT CSV, DELIMITER '|', HEADER true);

COPY table FROM '<path>' reads a file on the server. To load a file sitting on your own machine through psql, use the client-side \copy, which takes the same options:

\copy products FROM 'local-products.csv' WITH (FORMAT CSV, HEADER true)

Export with COPY​

COPY also writes data out. Export a whole table or the result of any query:

-- Export a filtered result set
COPY (SELECT id, sku, price FROM products WHERE category = 'kitchen')
    TO '/data/kitchen.csv' WITH (FORMAT CSV, HEADER true);

For moving data between two ScramDB databases, the binary format is the most compact and fastest to reload:

COPY products TO '/data/products.bin' WITH (FORMAT BINARY);
COPY products FROM '/data/products.bin' WITH (FORMAT BINARY);

Load atomically​

Wrap a multi-step load in a transaction so it either fully lands or not at all. If anything fails, ROLLBACK leaves the table exactly as it was.

BEGIN;
INSERT INTO products (id, sku, name, category, price)
VALUES (6, 'SKU-1006', 'Glass Jar', 'kitchen', 8.40);
INSERT INTO products (id, sku, name, category, price)
VALUES (7, 'SKU-1007', 'Wooden Spoon', 'kitchen', 4.10);
COMMIT;

Tips for fast loads​

  • Prefer COPY over row-by-row INSERT for anything large. It is built for volume and streams the input in batches.
  • Group inserts. One statement with many rows beats many statements with one row each.
  • Create secondary indexes after the bulk load when you can, so the load is not paying to maintain them row by row.
  • Loaded rows are queryable the moment the transaction commits. There is nothing else to refresh.