How to query most recent pair of each unique data-pair?

Question

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?

Answer 1

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

Answer 2

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:

• Select first row in each GROUP BY group?

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:

• Optimize GROUP BY query to retrieve latest row per user

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.

Answer 3

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.