4 ms·

Probably ignorant question, but how do I avoid the issues of cross join say across several tables (or equivalents)? It seems like I always need more than a many

• fifilura 2y ago
  You can use awk for that and pipe it into separate files for each bucket. And the you awk a bit more and the you are done. This was sarcasm based on several earlier discussions. And i am prepared for the downvotes. I think awk is fine for some things but awk proponents underestimate the scale of a map reduce job with multiple dimensions in both directions. For example i believe awk will struggle with a question like "of one million customers and one billion sales, give me the top 10 spenders". And to stick to the topic, i believe a one node database will also struggle.
  • sgarland 2y ago
    > 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`).
    • fifilura 2y ago
      I have nothing else to say than thanks for the writeup. And you are most likely correct. I think what I (and OP) wanted to say was that there should be a stage where you store the intermediate results of the aggregation as well. Unless you have that in an index already. And that also costs. But you may be right in that it is possibly in the scale of a million so not a huge deal.