What is the fastest way to fetch the last row from a table?

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?

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.

StackExchange Database Administrators Q#59000, answer score: 14