Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to make it possible for user to search products within a price range. User should be able to use any currency (USD, EUR, GBP, JPY, ...), no matter what currency is set by the product. So, the product price is 200USD and, if the user searches the products that costs 100EUR - 200EUR, he still may find it. How to make it fast and effective?

Here is what I have done until now. I store the price, currency code and the calculated_price that is the price in Euros (EUR) that is the default currency.

CREATE TABLE "products" (
  "id" serial,
  "price" numeric NOT NULL,
  "currency" char(3),
  "calculated_price" numeric NOT NULL,
  CONSTRAINT "products_id_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "currencies" (
  "id" char(3) NOT NULL,
  "modified" timestamp NOT NULL,
  "is_default" boolean NOT NULL DEFAULT 'f',
  "value" numeric NOT NULL,       -- ratio additional to the default currency
  CONSTRAINT "currencies_id_pkey" PRIMARY KEY ("id")
);

INSERT INTO "currencies" (id, modified, is_default, value)
  VALUES
  ('EUR', '2012-05-17 11:38:45', 't', 1.0),
  ('USD', '2012-05-17 11:38:45', 'f', '1.2724'),
  ('GBP', '2012-05-17 11:38:45', 'f', '0.8005');

INSERT INTO "products" (price, currency, calculated_price)
  SELECT 200.0 AS price, 'USD' AS currency, (200.0 / value) AS calculated_price
    FROM "currencies" WHERE id = 'USD';

If user is searching with other currency, let's say USD, we calculate the price in EUR and search the calculated_price column.

SELECT * FROM "products" WHERE calculated_price > 100.0 AND calculated_price < 200.0;

In this way we can compare prices very fast, because we don't need to calculate the actual price for every row, because it's calculated once.

Bad thing is that at least every day we have to re-calculate the default_price for all rows, because the currency rates has been changed.

Is there a better way to do deal with this?

Isn't there any other clever solution? Maybe some mathematical formula? I have an idea that the calculated_price is a ratio against some variable X and, when the currency changes, we update only that variable X, not the calculated_price, so we even don't need to update anything (rows)... Maybe some mathematician can solve it like this?

share|improve this question
    
I was initially confused by this question because based on the title I thought you were referring to the built-in "money" type in PostgreSQL (postgresql.org/docs/current/interactive/datatype-money.html). Thankfully you aren't; it's IMO not a very useful data type. Title edited. –  Craig Ringer Nov 1 '12 at 11:56
3  
Rather than updating the products, I'd want to store the calculated product summary in a separate table. That way you can efficiently TRUNCATE and re-generate it, treating it as a materialized view. You'll save all sorts of locking and table bloat pain on the main table, and it'll be easy to just ANALYZE after re-generating. If it's slow to generate you can create a new one in a temp table, then truncate and copy the rows; that way you can do it much more frequently than daily. –  Craig Ringer Nov 1 '12 at 12:03
    
Are you lookup price ranges fixed (say can be chosen from a dropdown list)? In this case you can only calculate the reanges in different currencies. –  dezso Nov 1 '12 at 12:18
    
Doing the thing you do (calculated price column, search on the 'virtual currency' price) is the best option I know of. The repopulate every day (or every few hours) is unfortunate, but not necessarily a show-stopper. –  Remus Rusanu Nov 1 '12 at 12:39
    
@dezso No, the ranges are not strictly defined. User may write the lowest and the highest price and filter the products by that. –  Taai Nov 1 '12 at 20:34
show 6 more comments

3 Answers

This sounds like a job for a materialized view. While PostgreSQL doesn't explicitly support them, you can create and maintain materialized views using functions and triggers on normal tables.

I would:

  • Create a new table, say products_summary, with the schema of your current products table;
  • ALTER TABLE products DROP COLUMN calculated_price to get rid of the calculated_price column in products
  • Write a view that produces the output you want for products_summary by SELECTing from products and JOINing on currencies. I'd call that products_summary_dynamic but the naming is up to you. You could use a function instead of a view if you wanted.
  • Periodically refresh the materialized view table products_summary from products_summary_dynamic with BEGIN; TRUNCATE products_summary; INSERT INTO products_summary SELECT * FROM products_summary_dynamic; COMMIT;.
  • Create a AFTER INSERT OR UPDATE OR DELETE ON products trigger that runs a trigger procedure to maintain the products_summary table, deleting rows when deleted from products, adding them when added to products (by SELECTing from the products_summary_dynamic view), and updating them when the product details change.

This approach will take an exclusive lock on products_summary during the TRUNCATE ..; INSERT ...; transaction that updates the summary table. If that causes stalls in your application because it takes so long, you can instead keep two versions of the products_summary table. Update the one that isn't in use, then in a transaction ALTER TABLE products_summary RENAME TO products_summary_old; ALTER TABLE products_summary_new RENAME TO products_summary;


An alternative but very dodgy approach would be to use an expression index. Because updating the currency table with this approach is likely to unavoidably require a lock during a DROP INDEX and CREATE INDEX I wouldn't do it too often - but it might be suitable for some situations.

The idea is to wrap your currency conversion up in an IMMUTABLE function. Since it's IMMUTABLE you're guaranteeing to the database engine that the return value for any given arguments will always be the same, and that it's free to do all sorts of insane things if the return value differs. Call the function, say, to_euros(amount numeric, currency char(3)) returns numeric. Implement it however you want; a big CASE statement by currency, a lookup table, whatever. If you use a lookup table you must never change the lookup table except as described below.

Create an expression index on products, like:

CREATE INDEX products_calculated_price_idx
ON products( to_euros(price,currency) );

You can now search quickly for products by calculated price, eg:

SELECT *
FROM products
WHERE to_euros(price,currency) BETWEEN $1 and $2;

The problem now becomes how to update the currency tables. The trick here is that you can change the currency tables, you just have to drop and re-create the index to do it.

BEGIN;

-- An exclusive lock will be held from here until commit:
DROP INDEX products_calculated_price_idx;
DROP FUNCTION to_euros(amount numeric, currency char(3)) CASCADE;

-- It's probably better to use a big CASE statement here
-- rather than selecting from the `currencies` table as shown.
-- You could dynamically regenerate the function with PL/PgSQL
-- `EXECUTE` if you really wanted.
--
CREATE FUNCTION to_euros(amount numeric, currency char(3))
RETURNS numeric LANGUAGE sql AS $$
SELECT $1 / value FROM currencies WHERE id = $2;
$$ IMMUTABLE;

-- This may take some time and will run with the exclusive lock
-- held.
CREATE INDEX products_calculated_price_idx
ON products( to_euros(price,currency) );

COMMIT;

I drop and re-define the function above only to stress that you must drop everything that uses the function if you re-define an immutable function. Using a CASCADE drop is the best way to do that.

I strongly suspect that a materialized view is the better approach. It's certainly the safer. I'm including this one mostly for kicks.

share|improve this answer
    
I learned a lot after reading your answer and reading more about this. I voted up your answer, but it's not completely the answer what I'm hoping for. About that expression index... It would be unnecessary since in my program I pre-calculate the lowest and highest price in Euros and not calculating in the PostgreSQL side. I would index only calculated_price using normal index. –  Taai Nov 2 '12 at 11:32
    
@Taai Fair enough; what are you hoping for, though? –  Craig Ringer Nov 2 '12 at 12:04
    
Right now I'm thinking about this - why should I update the calculated_price at all? I could just store the initial_currency_value (constant currency rate that is taken, let's say, today) and always calculate against that! And when displaying the price in Euros, calculate against actual currency rate, of course. Am I right? Or there is a problem that I don't see? –  Taai Nov 2 '12 at 12:16
    
This is fine except for the full table scans that will result when selecting rows in a given price range at the time of the query. I've submitted an answer that tries to address this problem. –  Daniel Vérité Nov 2 '12 at 12:59
add comment

Here's a different approach for which recomputing the calculated_price is just an optimization, as opposed to being strictly necessary.

Suppose that in the currencies tables, you add another column, last_rate, which contains the exchange rate at the time the calculated_price was last updated, no matter when this happened.

To quickly retrieve a set of products with a price point between, say, 50 USD and 100 USD that include the desired results, you could do something like that:

  SELECT * FROM products
   WHERE calculated_price > 50.0/(:last_rate*
    (SELECT coalesce(max(value/last_rate),1) FROM currencies
      WHERE value>last_rate))
   AND calculated_price < 100.0/ (:last_rate*
    (SELECT coalesce(min(value/last_rate),1) FROM currencies
      WHERE value<last_rate))

where :last_rate contains the EUR/USD exchange rate at the time of the last update. The idea is to increase the interval to take into account the maximum variation of every currency. The increase factors for both ends of the interval are constant between rates updates, so they could be pre-computed.

Since the rates change only slightly over short periods of time, the above query is likely to give a close approximation of the final result. To get the final result, let's filter out the products for which the prices have slipped out of the bounds due to the changes in rates since the last update of calculated_price:

  WITH p AS (
   SELECT * FROM products
   WHERE calculated_price > 50.0/(:last_rate*
    (SELECT coalesce(max(value/last_rate),1) FROM currencies
      WHERE value>last_rate))
   AND calculated_price < 100.0/ (:last_rate*
    (SELECT coalesce(min(value/last_rate),1) FROM currencies
      WHERE value<last_rate))
  )
  SELECT price,c.value FROM p join currencies c on (p.currency=c.id)
     WHERE price/c.value>50/:current_rate
       AND price/c.value<100/:current_rate;

where :current_rate is the more up-to-date rate with EUR for the money choosen by the user.

The efficiency comes from the fact that the range of rates is supposed to be small, the values being close together.

share|improve this answer
    
Well, solution may be good. But when it comes to big numbers, the difference may be huge. And still we will have to update the table as often as we can, if we want to keep the difference as minimal as possible. +1 :) –  Taai Nov 2 '12 at 15:56
add comment

I came up with my own idea. Tell me if it's actually going to work, please!

The problem.

When product is being added in the products table, the price gets converted to the default currency (EUR) and stored in calculated_price column.

We want that user can search (filter) prices of any currency. It's done by converting the input price to default currency (EUR) and comparing it with calculated_price column.

We need to update the currency rates, so the users will be able to search by fresh currency rate. But the problem is - how to update calculated_price efficiently.

The solution (hopefully).

How to update calculated_price efficiently.

Don't! :)

The idea is that we take yesterday's currency rates (all of the same date) in the calculated_price use only those. Like...forever! No daily updates. Only thing we need before we compare/filter/search the prices is to take today's currency rates like they were yesterday's.

So, in calculated_price we will use only the currency rate of the fixed date (we have chosen, let's say, yesterday). What we will need is to convert today's price into yesterday's price. In other words, take the today's rate and convert it to yesterday's rate:

cash_in_euros * ( rate_newest / rate_fixed )

And this is table of the currencies:

CREATE TABLE "currencies" (
  "id" char(3) NOT NULL, -- currency code (EUR, USD, GBP, ...)
  "is_default" boolean NOT NULL DEFAULT 'f',

  -- Set once. If you update, update all database fields that depends on this.
  "rate_fixed" numeric NOT NULL, -- Currency rate against default currency
  "rate_fixed_updated" timestamp NOT NULL,

  -- Update as frequently as needed.
  "rate_newest" numeric NOT NULL, -- Currency rate against default currency
  "rate_newest_updated" timestamp NOT NULL,

  CONSTRAINT "currencies_id_pkey" PRIMARY KEY ("id")
);

This is how to add a product that costs 200 USD and how the calculated_price get's calculated: from the USD to newest EUR rate and into fixed (old) rate

INSERT INTO "products" (price, currency, calculated_price)
  SELECT
  200.0 AS price,
  'USD' AS currency,

  ((200.0 / rate_newest) * (rate_newest / rate_fixed)) AS calculated_price

    FROM "currencies" WHERE id = 'USD';

This can also be pre-calculated in client side and that's what I'm going to do - calculate the user input price to the calculated_price compatible value before we make a query, so there will be used good old SELECT * FROM products WHERE calculated_price > 100.0 AND calculated_price < 200.0;

Conclusion.

This idea came to me just few hours ago and currently I'm asking you to check if I'm right about this solution. What do you think? Is this going to work? Or I have mistaken?

I hope you understand all this. I'm not a native English speaker, also it's late and I'm tired. :)

UPDATE

Well, it looks like it resolves one problem, but introduces another. Too bad. :)

share|improve this answer
    
The problem is that the rate_newest / rate_fixed is different per currency, and this solution considers only the one for the user-choosen money in search. Any price in a different currency would not be compared with up-to-date rates. The answer I've submitted somehow had a similar problem but I think I've fixed it in the updated version. –  Daniel Vérité Nov 6 '12 at 11:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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