PostgreSQL – Fastest Way to Fetch the Last Row from a Table

physical-designpostgresql

I have a PostgreSQL table, Prices, with the columns:

  • price (Decimal)
  • product_id (Int)

There are also created_at and updated_at columns.

Prices get updated regularly and I keep old prices in the table. For a given product, the last price in the table is the current price.

What is the most efficient way to get the last price for a specific product:

  • Index product_id and query for the last record
  • Add a third column active (Boolean) to mark the latest price and create a composite index (product_id and active)
  • Or something else?

Best Answer

You'll need an index on product_id regardless of solution.

Provided you have an index on the updated_at column, and all you need is to fetch "a specific product" as you stated, then I would do:

select *
from Prices
where product_id = ?
order by updated_at desc 
limit 1

But if I did not get the results I wanted or if I needed to get the current price for many products, then I would try the option of adding a active column, and setting it to N for all prices other than the new one when doing updates of the prices and then I would create a partial index where active as suggested by a_horse_with_no_name. I would go there only if I needed to as it adds a layer of complexity of updating previous price rows to not be active, etc.

Related Question