9

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?
  • 4
    Using a partial index with the condition where active would probably help even more to retrieve the latest product. – a_horse_with_no_name Feb 16 '14 at 7:59
9

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.

  • This query needs an index on (product_id, updated_at) - or on (product_id, updated_at DESC) - to be efficient. Not just on (updated_at). – ypercubeᵀᴹ Apr 24 '17 at 13:26
4

Without knowledge of the rest of your database, you can afford a bit of non normal form to speed up products price fetching (assuming there is one price for each item).

Just create a new column named last_priceof type price in your product table and create a trigger AFTER INSERT ON EACH ROWon your price table. Every time a new price is created, it updates the related product with the latest price. This way, every time you fetch a product, you also fetch its last price.

Since version 9.3, PostgreSQL supports materialized views. This is a nice way to denormalize the data, keeping a normal form for writing and denormalized view for reading. The update of the view can be triggered by Postgres’ LISTEN/NOTIFY mechanism.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.