Database Improvements

[r2dedios]

This issue collects ideas and potential improvements to apply for the database:

[r2dedios]

@vbelouso Thoughts?

[vbelouso]

@vbelouso Thoughts?

I would add the following points

• Consider replacing text-based primary keys (PK) with identity columns (ref)

Move text fields into separate columns like external_id
It should reduce the database and index size, speeding up all JOIN operations

• Consider using JSONB instead of storing a separate tags table (will require a very careful study of the use cases). So far I do not see cases where tags are not involved in the search by key=value, only for aggregation and display

• Analyze index usage and optimization

For example, a simple query with an index

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM expenses
WHERE date >= CURRENT_DATE - INTERVAL '15 days';

Without index

"Seq Scan on expenses (cost=0.00..3400.00 rows=3981 width=27) (actual time=8.791..29.475 rows=6077 loops=1)"
"Filter: (date >= (CURRENT_DATE - '15 days'::interval))"
"Rows Removed by Filter: 119730"
"Buffers: shared hit=1244"
"Planning Time: 0.063 ms"
"Execution Time: 29.748 ms"

With index

"Index Scan using idx_expenses_date on expenses (cost=0.30..832.09 rows=6046 width=27) (actual time=0.015..0.976 rows=6077 loops=1)"
" Index Cond: (date >= (CURRENT_DATE - '15 days'::interval))"
" Buffers: shared hit=1024"
"Planning Time: 0.073 ms"
"Execution Time: 1.137 ms"

I agree about partitioning, we need to clarify whether we can do it automatically using pg_partman or pg_cron
I completely agree with the materialized view. I would remove most of the triggers and use only mview where it is possible