# Efficiently comparing prices in different currencies

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?

-
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
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

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.

-
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

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 `SELECT`ing from `products` and `JOIN`ing 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 `SELECT`ing 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.

-
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

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

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,

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. :)

-
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
The main problem I see with that approach is that it doesn't take advantage of database indices on the price (ORDER BY calculated_price clauses). – rosenfeld May 13 at 20:39