I didn't answer your question about the date column as your primary key because I haven't tried it. How are you handling time zones?

As my app is going to be used only for myself in my own personal business, I am not worried about timelines. So I am only using "date"...as in year, month and date.

timezones*

I don't think I would use a trigger with a second table if I could avoid it

Maybe I would use a view

That aggregates the transactions for each date.

if that proved to be too slow, then maybe I would consider caching it in another table like you've suggested. I try to let the db do the hard work if I can

Can you elaborate? I dont understand. Why would you avoid it? Is there a particular reason? I know I can use the aggregation methods (and they work fine). But my number of trnsactions would increase so much every single day that I am sure calculating balances on the go would be a waste of resources. Thats why I want to cache it.

since you have suggested that you are the only one using it, then easiest is to use a view

Thats the reason I am looking into this.

But have you proven it to be too slow?

Views are pretty powerful, if you haven't tried that yet, then I would experiment

Not really. i am assuming this would happen when my rows get into millions.

Postgres is pretty impressive

I'll give it a try.

Let me know how it goes. It's easy to add a million transactions into a test db to know for sure

then you can optimize against that test data until you have a solution that you know will work

I will try both methods and see which works better

duplicating data should be avoided, it means you need to keep it in sync yourself (instead of letting the rdbms doing it with a view)

If I can calculate new data with the existing data, and I store that calculated data, I consider it duplicate state.

So I avoid that until I am forced into it after measuring and trying to optimize

Oh. I understand now what you meant.

Sometimes caching is a necessary evil, but it is rarely my first approach since it can easily lead to desyncs

It takes about 3 seconds to sum a single column from 10 million rows which is acceptable I think.

I used view for this

Caching would obviously be much faster than this but I think that this is acceptable

Also, views cannot take in arguments for the WHERE statements as far as I can tell. So I might end up using a postgres database function instead to do the same query as my VIEW.

It may be possible to make that query faster. Do you have much experience with explain analyze? Also when you query a view table you should be able to use a where clause.

No I dont. I am hearing about t for the first time. What is it? Do you mean where clauses inside View or when selecting from view? If its the later, whats the point of that. I am already getting a single value from it.

Seems I’ve hit the limit of knowing what your specific use case is. Check the docs out, they are great. Maybe you can use materialized views that are updated when necessary.