I have a table with 100 million rows. These are transactions for different accounts. A single account may have more than 500,000 transactions.
Here is basically what I'm working with:
CREATE TABLE public.transactions (
"timestamp" timestamptz NOT NULL,
tx varchar(255) NOT NULL,
account varchar(255) NOT NULL
);
CREATE INDEX transactions_account_timestamp_idx ON public.transactions USING btree (account, "timestamp" DESC);
CREATE INDEX transactions_timestamp_idx ON public.transactions USING btree ("timestamp" DESC);
CREATE UNIQUE INDEX unique_transactions ON public.transactions USING btree ("timestamp", account, tx);
I would like to be able to keep an up to date record of all the transactions for each account based on different timeframes:
IE: provide number of tx for account 'abc' in last hour, provide number of tx for account 'abc' in last year.
select count(*) from transactions where account = 'abc' and "timestamp" >= date_trunc('hour', now());
select count(*) from transactions where account = 'abc' and "timestamp" >= date_trunc('year', now());
The problem is count(*) is incredibly slow and I need to update the # of transactions for each possible account once every 10-15 minutes or so. I'm unable to drop indexes as this needs to be an ongoing operation.
(account, timestamp)
explain (analyze, buffers)