postgresql - FIFO inventory accounting for blockchain data in SQL - Stack Overflow

admin管理员组

文章数量:1390555

I'm trying to implement real-time analytics on aggregated blockchain data. My database stores swap events (SwapEvent table) with information about tokens traded, amounts, and USD values. Currently, I've implemented FIFO accounting using materialized views to calculate metrics like trading volume, realized pnl and win rate. If this isn't best practice then I'm willing to do the accounting outside the database or something.

My problem currently is my materialized views rely heavily on CTEs, subqueries, and window functions, which is causing performance issues as my dataset grows. Also, I'm considering using Timescale & continuous aggregates which don't support the previous functions. Here's what one of my current views looks like:

-- tokenPositions view that uses multiple CTEs and window functions
WITH swaps AS (
  SELECT se."walletId", se."timestamp", se."mintIn", ...
),
inventory AS (
  SELECT s_1."walletId", s_1."timestamp", ...
  row_number() OVER (PARTITION BY s_1."walletId", s_1."mintOut" ORDER BY s_1."timestamp") AS "purchaseOrder"
),
...

My swap event table:

create table public.swap_events (
  enriched_transaction_id text not null,
  wallet_id text not null,
  signature text not null,
  timestamp timestamp(3) not null,
  mint_in text not null,
  mint_out text not null,
  usd_in numeric(65, 30) not null,
  usd_out numeric(65, 30) not null,
  related_swap_id text,
  
  primary key (signature, timestamp)
);

(note: the difference between usd in and usd out here is trade slippage, in order to find pnl you must match with previous txs)

I've considered denormalizing the database with a separate Trades table that would split each swap into buy/sell records, but I'm still not sure how to efficiently calculate realized profit without using complex SQL constructs.

本文标签： postgresqlFIFO inventory accounting for blockchain data in SQLStack Overflow