Lessons Learned from Scaling to Multi-Terabyte Datasets

yes, we're evaluating Delta. We went with Iceberg out of concern that Delta was too closely tied to Databricks.

Following the Tabular acquisition, the decision is murkier.

Full-range query with aggregations is around 40-60 seconds. Can be optimized, of course.

> A trillion rows of indexed data will fit on a single cloud VM and be reasonably performant.

create and subsequently update index on trillion rows is very untrivial task performance wise.. Which DB you would suggest to use for this?

> one million customers and one billion sales, give me the top 10 spenders... i believe a one node database will also struggle.

Doubtful. Assume a simplified (but still normalized) schema like below:

  CREATE TABLE customer (
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL,
    first_name TEXT NOT NULL,
    middle_name TEXT NULL,
    last_name TEXT NULL
  );

  CREATE TABLE product (
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    is_active BOOL NOT NULL DEFAULT TRUE,
    name TEXT NOT NULL,
    description TEXT,
    price NUMERIC(8,2) NOT NULL
  );

  CREATE TABLE sale (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    customer_id INT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customer (id)
  );

  CREATE TABLE sale_item (
    sale_id BIGINT NOT NULL,
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id INT NOT NULL,
    product_qty SMALLINT NOT NULL,
    product_price NUMERIC(8,2) NOT NULL,
    FOREIGN KEY (sale_id) REFERENCES sale (id),
    FOREIGN KEY (product_id) REFERENCES product (id)
  );

An empty row in Postgres is 24 bytes, and with the optimal column ordering above, a `sale` row would be 24 + 8 + 8 + 4 = 44 bytes. `customer` would be 40 bytes, plus the actual length of the text. Assume 30 bytes for each to cover most cases, so a total of 160 bytes per row. 1E6 sales, and 1E9 customers, so 44 GB for `sale`, and 160 MB for `customer`.

Assume 10,000 products, all active. `product` is 44 bytes per row, plus the actual length of the text. Assume `name` is 30 bytes, and `description` is 300 bytes, for a total of 374 bytes per row, so ~4 MB.

Assume each sale had 10 items. `sale_item` is 47 bytes per row. 1E9 sales * 10 items per sale, so 470 GB for `sale_item`.

Each table will presumably also have some indices other than the PK. Since the query in question is not likely to be run frequently, you don't have to worry about fitting it entirely into RAM (but if you did want to, you could on larger instances). Alternatively, you could create a materialized view, then create a descending index on the total. This would also have the benefit of severely reducing the buffer thrashing, since you'd only need to read in `sale_item` periodically to refresh the materialized view, and the latter would be quite small in comparison (since it would have a number of rows equal to `customer`).

Ah yes then I am very familiar with Athena- like tools. At my old job I used pyspark and I must say I did enjoy how easy it was to make custom python UDFs in combination with SQL so I miss that in a pure SQL environment. But for a lot of applications SQL gets the job done.