2

There's a table wherein there currency exchange rates are.

fx_rates
---
id
buy_curr_code
sell_curr_code
rate
inserted_at

A table gets updated with unknown frequency: sometimes daily, sometimes several times per day, sometimes once in several days. The same goes for currencies: some may get updated this time, some the other.

And the data never gets deleted from the table, but only inserted or updated.

How will I query the latest, the most fresh exchanges rates from it? Meaning, a single exchange rate per each unique buy - sell currency_code pair.

Should I use distinct and if so, how?

New contributor
Camila326 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
2
  • What database system and version?
    – J.D.
    Jan 25 at 1:29
  • @J.D. latest Postgresql
    – Camila326
    Jan 25 at 2:49

3 Answers 3

4

Erwin's answer is correct, but the query will become slower and slower as the table grows. So let me suggest a different data model:

Add a column active of type boolean that is TRUE for the most recent entry per (buy_curr_code, sell_curr_code).

Then you'd add an index:

CREATE UNIQUE INDEX ON fx_rates (buy_curr_code, sell_curr_code) WHERE active;

To insert a new row, you would run this transaction:

BEGIN;

UPDATE fx_rates
SET active = FALSE
WHERE buy_curr_code = 42 AND sell_curr_code = 101
RETURNING inserted_at;

/* here I would add a test in my code if the "inserted_at" is more
   recent than "current_timestamp".  If yes, I would have the transaction
   fail, because there is already a more recent entry.
   This should take care of race conditions. */

INSERT INTO fy_rates (buy_curr_code, sell_curr_code, rate, inserted_at, active)
VALUES (42, 101, 2.71828, current_timestamp, TRUE);

COMMIT;

The nice thing about this solution is that it is now easy to query for the active entries, and the performance of that query will be independent of the size of the table.

To clean up data, you could partition the table by list on active and subpartition the FALSE partition by time range.

3

DISTINCT ON is one of the simplest ways:

SELECT DISTINCT ON (buy_curr_code, sell_curr_code) *
FROM   fx_rates
ORDER  BY buy_curr_code, sell_curr_code, inserted_at DESC;

Have an index on (buy_curr_code, sell_curr_code, inserted_at DESC) to make this fast. See:

If there are many rows per (buy_curr_code, sell_curr_code) - which seems likely - other query techniques will be faster. Specifics depend on undisclosed details. See:

Better design

If changing the DB design is an option I would consider an additional table with a single entry per conversion. Like:

CREATE TABLE current_rate (
  exchange_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, buy_curr_code   int NOT NULL  -- or whatever type 
, sell_curr_code  int NOT NULL
, rate            numeric NOT NULL
, inserted_at     timestamptz NOT NULL DEFAULT now()
, UNIQUE (buy_curr_code, sell_curr_code)
);

And triggers ON INSERT and ON UPDATE insert a new "log" entry in table fx_rates. All new entries are updates to current_rate. Only the trigger writes to table fx_rates. (The trigger might run additional checks.)

INSERT trigger:

-- function
CREATE OR REPLACE FUNCTION trg_current_rate_insbef()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   INSERT INTO fx_rate (exchange_id, rate, inserted_at)
   VALUES (NEW.exchange_id, NEW.rate, NEW.inserted_at);
        
   RETURN NEW;
END
$func$;

-- trigger
CREATE TRIGGER current_rate_insbef
BEFORE INSERT ON current_rate
FOR EACH ROW EXECUTE FUNCTION trg_current_rate_insbef();

Complete demo in this fiddle.

Then the content of current_rate is always the ready result you tried to generate.

Why?

Each approach has pros and cons. The only declared requirement is the list of current rates. My solution provides that with SELECT * FROM current_rate - as simple and fast as possible. Adding a new rate is a single UPDATE. Storage: fx_rates is orders of magnitude bigger than current_rate. We don't need any index at all on that big table. It's effectively INSERT-only, so no table and index bloat. We can make the big table even smaller by adding an integer IDENTITY column as surrogate PK to current_rate, and only write this one 4-byte ID to fx_rates. In fact, nothing in your question even says that we still need fx_rates once we have current_rates. (But I'd expect there will be additional purposes.)

The only moderate sophistication is the trigger, which is really simple, too. So unless you have other requirements, the suggested design is simpler, faster, smaller, more reliable.

4
  • why not let it remain with a single table, and instead add a flag: is_current? There'll be no need in a trigger, nor in a 2nd table. Are there downsides compared to your solution?
    – Camila326
    2 days ago
  • @Camila326: I elaborated in my answer. 2 days ago
  • Still there's no strong argument to use your solution. My solution provides that with SELECT * FROM current_rate - as simple and fast as possible. -- it's simple in one place, but it comes with a cost to others: a trigger and the fact that there're 2 tables.
    – Camila326
    2 days ago
  • @Camila326: The additional small table can bring down overall storage size and query times. Like I said, each approach has pros and cons. It really depends on undisclosed details of your use case. I answered the question as given. 2 days ago
1

A third alternative that does pretty well performance-wise and doesn't require you to change your table schema, is to use a window function like ROW_NUMBER(). This allows you to enumerate the rows within each group of buy_curr_code, sell_curr_code pairs, and then you can select only the latest row per group like so:

WITH ExchangeRatesSorted AS
(
    SELECT 
        id,
        buy_curr_code,
        sell_curr_code,
        rate,
        inserted_at,
        ROW_NUMBER() OVER (PARTITION BY buy_curr_code, sell_curr_code ORDER BY inserted_at DESC, id DESC) AS InsertedSortId
    FROM fx_rates
)

SELECT 
    id,
    buy_curr_code,
    sell_curr_code,
    rate,
    inserted_at
FROM ExchangeRatesSorted
WHERE InsertedSortId = 1

You probably want an index on (buy_curr_code, sell_curr_code, inserted_at, id) to make this most efficient.

The reason id is added to the end of the ORDER BY clause in the ROW_NUMBER() expression is for the off-chance two rows are inserted at the exact same inserted_at time, so the one with the latest id (ideally the row that truly came last) is the tie-breaker, to ensure the ORDER BY clause is deterministic.


Window Functions are very useful tools to be aware of for making calculations and manipulating the data.

2
  • Unfortunately, the alternative with row_number() performs poorly, currently. See: stackoverflow.com/a/34715134/939860 There have been improvements in Postgres 15 (current version), and more is in the pipeline for Postgres 16. 2 days ago
  • @ErwinBrandstetter Informative and surprising, thanks! I guess poorly is subjective depending on the context. E.g. the same test yields ~40ms on average on my SQL Server instance, which is good enough in most cases. But I imagine an insert-only table of currency exchange rates will surely grow much bigger than 200k rows, and therefore the approach probably does matter in OP's case.
    – J.D.
    2 days ago

Your Answer

Camila326 is a new contributor. Be nice, and check out our Code of Conduct.

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.