Como consultar o par mais recente de cada par de dados exclusivo?

A resposta de Erwin está correta, mas a consulta ficará cada vez mais lenta conforme a tabela cresce. Então deixe-me sugerir um modelo de dados diferente:

Adicione uma coluna activedo tipo booleanTRUE para a entrada mais recente por (buy_curr_code, sell_curr_code).

Então você adicionaria um índice:

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

Para inserir uma nova linha, você executaria esta transação:

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;

O bom dessa solução é que agora é fácil consultar as entradas ativas e o desempenho dessa consulta será independente do tamanho da tabela.

Para limpar os dados, você pode particionar a tabela por lista activee subparticionar a partição FALSE por intervalo de tempo.

DISTINCT ONé uma das maneiras mais simples:

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

Tenha um índice (buy_curr_code, sell_curr_code, inserted_at DESC)para tornar isso rápido. Ver:

• Selecione a primeira linha em cada grupo GROUP BY?

Se houver muitas linhas por (buy_curr_code, sell_curr_code)- o que parece provável - outras técnicas de consulta serão mais rápidas. As especificidades dependem de detalhes não revelados. Ver:

• Otimize a consulta GROUP BY para recuperar a última linha por usuário

Melhor design

Se alterar o design do banco de dados for uma opção, eu consideraria uma tabela adicional com uma única entrada por conversão. Como:

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

E dispara ON INSERTe ON UPDATEinsere uma nova entrada de "log" na tabela fx_rates. Todas as novas entradas são atualizações para current_rate. Somente o gatilho grava na tabela fx_rates. (O gatilho pode executar verificações adicionais.)

INSERTacionar:

-- 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();

Demonstração completa neste violino .

Então o conteúdo de current_rateé sempre o resultado pronto que você tentou gerar.

Por que?

Cada abordagem tem prós e contras. O único requisito declarado é a lista de taxas atuais. Minha solução fornece isso SELECT * FROM current_rate- o mais simples e rápido possível. Adicionar uma nova taxa é um único arquivo UPDATE. Armazenamento: fx_ratesé ordem de grandeza maior que current_rate. Não precisamos de nenhum índice naquela grande mesa. É efetivamente INSERTapenas, portanto, nenhuma tabela e índice incham. Podemos tornar a tabela grande ainda menor adicionando uma IDENTITYcoluna inteira como substituto PK para current_rate, e apenas escrever este ID de 4 bytes para fx_rates. Na verdade, nada em sua pergunta diz que ainda precisamos, fx_ratesuma vez que temos current_rates. (Mas eu esperaria que houvesse propósitos adicionais.)

A única sofisticação moderada é o gatilho, que também é muito simples. Portanto, a menos que você tenha outros requisitos, o design sugerido é mais simples, rápido, menor e mais confiável.

Uma terceira alternativa que funciona muito bem em termos de desempenho e não exige que você altere o esquema da tabela, é usar uma função de janela como ROW_NUMBER(). Isso permite que você enumere as linhas dentro de cada grupo de buy_curr_code, sell_curr_codepares e, em seguida, selecione apenas a última linha por grupo da seguinte forma:

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

Você provavelmente deseja um índice (buy_curr_code, sell_curr_code, inserted_at, id)para tornar isso mais eficiente.

A razão idé adicionada ao final da ORDER BYcláusula na ROW_NUMBER()expressão é para a chance de duas linhas serem inseridas exatamente ao mesmo inserted_attempo, então aquela com a última id(idealmente a linha que realmente veio por último) é o desempate, para garantir que a ORDER BYcláusula seja determinística.

As funções de janela são ferramentas muito úteis para fazer cálculos e manipular os dados.