CODEWITHSUNDEEP
sqlpostgresqlcalculated-columnssql-viewhasura
Philip Seyfi
Philip Seyfi

Reputation: 959

Hasura computed fields vs. Postgres generated columns vs. Postgres views

We're implementing an e-commerce data model and struggle to decide between Postgres generated columns, Hasura computed fields, and Postgres views.

As a simplification, we have two tables:

items
------------
id
order_id
unit_price
quantity

orders
------------
id

We now want to add total_price to the items table (unit_price * quantity), and total_price to orders (sum of total_price of all items in the order).

In the first case, we used a Postgres generated column. The benefit here seems to be that it is generated once, and stored, rather than rerun on each query like a Hasura computed field would be.

Is this the right choice?

items
------------
id
order_id
unit_price
quantity
total_price : Postgres generated column : (unit_price * quantity)

CREATE TABLE orders (
    ...,
    total_price integer GENERATED ALWAYS AS (unit_price * quantity) STORED
);

In the second case, we can't use a generated column, as it cannot reference fields from a different table.

Instead, we can solve this using a Hasura computed field:

orders
------------
id
total_price : Hasura computed field : SUM(items.total_price)

CREATE FUNCTION calculate_order_total_price(orders_row orders)
RETURNS INTEGER AS $$
    SELECT CAST(SUM(total_price) AS INTEGER)
      FROM items
     WHERE order_id = orders_row.id
$$ LANGUAGE sql STABLE;

Alternatively, we can create a Postgres view:

vw_orders
------------
orders.id
total_price : SELECT SUM(items.total_price)

CREATE VIEW vw_orders AS 
    SELECT orders.id,
        (SELECT sum(items.total_price) AS sum
           FROM items
          WHERE (items.order_id = orders.id)) AS total_price
        FROM orders;

The Hasura computed field solution has the detriment that total_price is only exposed in GraphQL queries, so we could not use it in SQL.

The Postgres view solution does not seem to have any problems, at first sight.

Are we missing something?

Why would one ever use a Hasura computed field over Postgres generated fields or views?

Is there a comparison table or flow chart somewhere that would help us decide which approach is best in each particular situation?

Finally, for all of these, we could of course also use Postgres triggers, Hasura event triggers, and Hasura actions... when would those be the appropriate solutions?

Cheers!

Upvotes: 5

Views: 2054

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246483

For this specific case I would go with a PostgreSQL view, because the computation involved is quite cheap (a single multiplication). It may even be that the savings in storage space and the resulting speed gain in sequential scans outweigh the downside of having to perform the computation whenever the value is needed. Recent PostgreSQL versions can also use just-in-time compilation (JIT) to make such expressions cheaper to compute (for bigger queries).

Generally speaking, the best strategy will probably depend on the computational intensity of the expression. If that is high, and the rows are more often read than written, it will probably make sense to use a generated column.

You could run a little benchmark and examine the difference. In your case, I don't think that the impact will be very high, and it may be best to choose the implementation that is simplest or most readable, because that improves maintainability.

Upvotes: 2

Related Questions