I have a table of "transactions" where each transaction has an amount: http://sqlfiddle.com/#!15/42849/1
Records in the table are never REMOVE'ed or UPDATE'ed. Only new transactions are added.
I wish to calculate the SUM of the amounts. The calculation doesn't have to be 100 percent up to date for every request.
On a dataset of about a million rows this takes about 400 ms on my database. This is way too slow for my application and I am trying to find the best solution for speeding this up.
What I have tried so far
- Materialized view: Adds complexity of having to have a cronjob running which updates the view every X seconds.
- Caching on the application server: Every X request will be slow when the cache needs an update.
- Storing results of queries on an old subset: Store the SUM of previous request and use these to calculate the correct total. Adds complexity.
Does PostgreSQL provide a solution for speeding this type of query up?
The SUM query is just a basic sum on a single column so I don't believe that this query in itself can get any faster. The solution is probably to do some kind of caching/precalculation or similar. Does PostgreSQL have any features in this regard?
Table in question:
CREATE TABLE transactions ( id bigserial NOT NULL, amount bigint NOT NULL );
Query in question:
SELECT SUM(amount) FROM transactions;
I found that I actually need a "type" as well.
CREATE TABLE transactions ( id bigserial NOT NULL, amount bigint NOT NULL, type int NOT NULL );
SELECT SUM(amount) FROM transactions GROUP BY type;
SQL Fiddle: http://sqlfiddle.com/#!15/77e67/2