= Останати теми: Безбедност, перформанси и одржување на базата

=== 1. Безбедност на ниво на база
Имплементирани се следните безбедносни механизми директно при комуникацијата со базата, со што се намалува нападниот вектор независно од самиот NextJS сервер:
 - '''Заштита од SQL вбризгување(SQL injection):''' Клиентската библиотека `postgres.js` користи параметризирани прашалници преку Tagged Template Literals(на пример `sql\`...\``) по дизајн. Ова спречува директно извршување на малициозен SQL код преку корисничките влезни параметри, бидејќи влезот е секогаш парсиран како податок, а не како команда.
 - '''Енкриптирана комуникација(SSL/TLS):''' Како што е наведено во конфигурацискиот фајл([source:/app/lib/db.ts#L11]), конекцијата кон базата строго наметнува SSL сертификат преку параметарот `ssl: 'require'`. Ова ја штити транзицијата на чувствителните податоци и лозинки од "man-in-the-middle" напади.
 - '''Безбедно поврзување со база:''' Конекциските стрингови никогаш не се чуваат во изворниот код. Тие се изолирани преку заштитени околински променливи (`POSTGRES_URL`).

=== 2. Перформанси и оптимизација
Анализата е направена со `EXPLAIN (ANALYZE, BUFFERS)`, при што се споредува планот за извршување пред и по додавање на дополнителните индекси. Во почетната состојба базата ги содржи само индексите кои PostgreSQL автоматски ги креира за `PRIMARY KEY` и `UNIQUE` ограничувања. Овие индекси не се третираат како дополнителна оптимизација, туку како дел од основната релациска структура на базата.

Првично тестирање беше направено со мал број редови во табелите:

[wiki:SmallDatabasePerformance Перформанси со мала датабаза]

Во оваа состојба PostgreSQL користеше `Seq Scan` и по додавање на индексите. Тоа е очекувано однесување, бидејќи за табели со многу мал број редови целосното читање на табелата е поевтино од пристап преку индекс.

Поради тоа, за пореална анализа беше генериран поголем сет на тест-податоци, при што податоците се логички поврзани преку постоечките релации во базата.

По генерирање на тест-податоците, базата ја има следната состојба:

||= Табела =||= Број на редови =||
|| `user` || 103 ||
|| `transaction` || 100012 ||
|| `transaction_account` || 505 ||
|| `transaction_breakdown` || 110011 ||
|| `tag` || 31 ||
|| `tag_assigned_to_transaction` || 125016 ||

Оваа количина на податоци е доволна за PostgreSQL optimizer-от да започне да прави различни планови за извршување и да користи дел од предложените индекси кога тие се поисплатливи од `Seq Scan`.

==== Предложени дополнителни индекси ====

Следните индекси се предложени затоа што колоните често се користат во `JOIN` и `WHERE` услови во аналитичките извештаи:

{{{#!sql
CREATE INDEX IF NOT EXISTS idx_transaction_date
ON "transaction"(date);

CREATE INDEX IF NOT EXISTS idx_transaction_account_user_id
ON transaction_account(user_id);

CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_transaction_id
ON transaction_breakdown(transaction_id);

CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_account_id
ON transaction_breakdown(transaction_account_id);

CREATE INDEX IF NOT EXISTS idx_tag_assigned_transaction_id
ON tag_assigned_to_transaction(transaction_id);

CREATE INDEX IF NOT EXISTS idx_tag_assigned_tag_id
ON tag_assigned_to_transaction(tag_id);
}}}

Индексот `idx_transaction_date` е наменет за извештаи кои филтрираат трансакции според временски период. Индексот `idx_transaction_account_user_id` е наменет за извештаи кои ги ограничуваат резултатите на конкретен корисник. Индексите на `transaction_breakdown` и `tag_assigned_to_transaction` се наменети за побрзо поврзување на трансакциите со сметки и тагови.

==== Сценарио 1: Трендови на трошење според тагови за корисник ====

'''Цел:'''
Овој извештај ја прикажува потрошувачката по тагови за конкретен корисник во последните шест месеци. Прашалникот е комплексен затоа што ги поврзува табелите `tag_assigned_to_transaction`, `transaction`, `transaction_breakdown`, `transaction_account` и `tag`, а дополнително врши филтрирање по корисник и датум, групирање по таг и месец, и сумирање на потрошената сума.

'''Анализиран SQL:'''

{{{#!sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT
tg.tag_name,
DATE_TRUNC('month', t.date) AS month,
SUM(tb.spent_amount) AS total_spent
FROM tag_assigned_to_transaction tat
JOIN "transaction" t
ON tat.transaction_id = t.transaction_id
JOIN transaction_breakdown tb
ON t.transaction_id = tb.transaction_id
JOIN transaction_account ta
ON tb.transaction_account_id = ta.transaction_account_id
JOIN tag tg
ON tat.tag_id = tg.tag_id
WHERE
ta.user_id = 5
AND t.date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS')
GROUP BY
tg.tag_name, month
ORDER BY
tg.tag_name, month;
}}}

'''Релевантни индекси за ова сценарио:'''

{{{#!sql
CREATE INDEX IF NOT EXISTS idx_transaction_date
ON "transaction"(date);

CREATE INDEX IF NOT EXISTS idx_transaction_account_user_id
ON transaction_account(user_id);

CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_transaction_id
ON transaction_breakdown(transaction_id);

CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_account_id
ON transaction_breakdown(transaction_account_id);

CREATE INDEX IF NOT EXISTS idx_tag_assigned_transaction_id
ON tag_assigned_to_transaction(transaction_id);

CREATE INDEX IF NOT EXISTS idx_tag_assigned_tag_id
ON tag_assigned_to_transaction(tag_id);
}}}

'''Пред додавање на индексите:'''

{{{#!sql
GroupAggregate  (cost=4898.54..4900.99 rows=98 width=52) (actual time=51.593..51.622 rows=7 loops=1)
Group Key: tg.tag_name, (date_trunc('month'::text, t.date))
Buffers: shared hit=4383
->  Sort  (cost=4898.54..4898.79 rows=98 width=26) (actual time=51.574..51.582 rows=81 loops=1)
Sort Key: tg.tag_name, (date_trunc('month'::text, t.date))
Sort Method: quicksort  Memory: 28kB
->  Hash Join  (cost=2498.81..4895.30 rows=98 width=26) (actual time=28.248..51.522 rows=81 loops=1)
Hash Cond: (tat.tag_id = tg.tag_id)
->  Hash Join  (cost=2497.11..4893.07 rows=98 width=18) (actual time=28.204..51.436 rows=81 loops=1)
Hash Cond: (tat.transaction_id = t.transaction_id)
->  Seq Scan on tag_assigned_to_transaction tat
(actual time=0.014..9.236 rows=125016 loops=1)
->  Hash
->  Nested Loop
->  Hash Join
Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
->  Seq Scan on transaction_breakdown tb
(actual time=0.012..8.429 rows=110011 loops=1)
->  Hash
->  Seq Scan on transaction_account ta
Filter: (user_id = 5)
Rows Removed by Filter: 500
->  Index Scan using transaction_pkey on transaction t
Index Cond: (transaction_id = tb.transaction_id)
Filter: (date >= date_trunc('month'::text, (CURRENT_DATE - '6 mons'::interval)))
->  Hash
->  Seq Scan on tag tg
Planning Time: 1.392 ms
Execution Time: 51.744 ms
}}}

'''По додавање на индексите:'''

{{{#!sql
GroupAggregate  (cost=2490.16..2492.59 rows=97 width=52) (actual time=6.936..6.963 rows=7 loops=1)
Group Key: tg.tag_name, (date_trunc('month'::text, t.date))
Buffers: shared hit=3273 read=76
->  Sort  (cost=2490.16..2490.40 rows=97 width=26) (actual time=6.921..6.928 rows=81 loops=1)
Sort Key: tg.tag_name, (date_trunc('month'::text, t.date))
Sort Method: quicksort  Memory: 28kB
->  Nested Loop  (cost=16.95..2486.96 rows=97 width=26) (actual time=0.155..6.835 rows=81 loops=1)
->  Nested Loop  (cost=16.80..2480.67 rows=97 width=18) (actual time=0.135..6.624 rows=81 loops=1)
->  Nested Loop  (cost=16.38..2443.39 rows=77 width=22) (actual time=0.104..5.104 rows=81 loops=1)
->  Nested Loop  (cost=16.09..2050.95 rows=1089 width=10) (actual time=0.087..0.458 rows=1000 loops=1)
->  Bitmap Heap Scan on transaction_account ta
Recheck Cond: (user_id = 5)
->  Bitmap Index Scan on idx_transaction_account_user_id
Index Cond: (user_id = 5)
->  Bitmap Heap Scan on transaction_breakdown tb
Recheck Cond: (transaction_account_id = ta.transaction_account_id)
->  Bitmap Index Scan on idx_transaction_breakdown_account_id
Index Cond: (transaction_account_id = ta.transaction_account_id)
->  Index Scan using transaction_pkey on transaction t
Index Cond: (transaction_id = tb.transaction_id)
Filter: (date >= date_trunc('month'::text, (CURRENT_DATE - '6 mons'::interval)))
->  Index Scan using idx_tag_assigned_transaction_id on tag_assigned_to_transaction tat
Index Cond: (transaction_id = tb.transaction_id)
->  Memoize
Cache Key: tat.tag_id
->  Index Scan using tag_pkey on tag tg
Index Cond: (tag_id = tat.tag_id)
Planning Time: 1.738 ms
Execution Time: 7.055 ms
}}}

'''Споредба:'''

||= Метрика =||= Пред индекси =||= По индекси =||
|| Planning Time || 1.392 ms || 1.738 ms ||
|| Execution Time || 51.744 ms || 7.055 ms ||
|| Подобрување на Execution Time || / || ~86.36% ||
|| Забрзување || / || ~7.33x ||
|| Тип на скенирање пред индекси || `Seq Scan` на големите табели || / ||
|| Тип на скенирање по индекси || / || `Bitmap Index Scan`, `Bitmap Heap Scan`, `Index Scan` ||
|| Дали новите индекси се користат || Не || Да ||

По додавање на индексите, PostgreSQL експлицитно ги користи:

* `idx_transaction_account_user_id` преку `Bitmap Index Scan`
* `idx_transaction_breakdown_account_id` преку `Bitmap Index Scan`
* `idx_tag_assigned_transaction_id` преку `Index Scan`

Индексот `idx_transaction_date` не е директно искористен во овој план, затоа што optimizer-от прво ги ограничува податоците преку корисничките сметки и breakdown записите, а потоа пристапува до трансакциите преку примарниот клуч `transaction_pkey`.

'''Заклучок:'''

Ова сценарио има најголемо подобрување. Времето на извршување се намали од 51.744 ms на 7.055 ms, што е приближно 86.36% подобрување, односно околу 7.33 пати побрзо извршување. Причината е што по додавање на индексите PostgreSQL повеќе не мора секвенцијално да ги чита `transaction_account`, `transaction_breakdown` и `tag_assigned_to_transaction`, туку директно ги лоцира релевантните записи преку индексите. Ова покажува дека индексите се корисни за извештаи кои филтрираат податоци за конкретен корисник и потоа ги поврзуваат трансакциите со тагови.

==== Сценарио 2: Сметки со највисоко вкупно трошење во изминатата година ====

'''Цел:'''
Овој извештај ги прикажува сметките со најголема вкупна потрошувачка во последната година. Прашалникот користи `JOIN` помеѓу `transaction_breakdown`, `transaction` и `transaction_account`, филтрирање по датум, `SUM` агрегација, `GROUP BY`, `ORDER BY` и `LIMIT`.

'''Анализиран SQL:'''

{{{#!sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT
ta.account_name,
COALESCE(SUM(tb.spent_amount), 0) AS total_spent
FROM transaction_breakdown tb
JOIN "transaction" t
ON tb.transaction_id = t.transaction_id
JOIN transaction_account ta
ON tb.transaction_account_id = ta.transaction_account_id
WHERE
t.date >= NOW() - INTERVAL '1 YEAR'
GROUP BY
ta.account_name
ORDER BY
total_spent DESC
LIMIT 10;
}}}

'''Релевантни индекси за ова сценарио:'''

{{{#!sql
CREATE INDEX IF NOT EXISTS idx_transaction_date
ON "transaction"(date);

CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_transaction_id
ON transaction_breakdown(transaction_id);

CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_account_id
ON transaction_breakdown(transaction_account_id);
}}}

'''Пред додавање на индексите:'''

{{{#!sql
Limit  (cost=4969.28..4969.31 rows=10 width=49) (actual time=73.712..73.717 rows=10 loops=1)
Buffers: shared hit=1540
->  Sort  (cost=4969.28..4970.54 rows=505 width=49) (actual time=73.711..73.714 rows=10 loops=1)
Sort Key: (COALESCE(sum(tb.spent_amount), '0'::numeric)) DESC
Sort Method: top-N heapsort  Memory: 26kB
->  HashAggregate  (cost=4952.06..4958.37 rows=505 width=49) (actual time=73.611..73.658 rows=113 loops=1)
Group Key: ta.account_name
->  Hash Join  (cost=2756.94..4883.26 rows=13760 width=23) (actual time=34.771..69.224 rows=13568 loops=1)
Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
->  Hash Join  (cost=2740.57..4830.47 rows=13760 width=10) (actual time=34.564..66.184 rows=13568 loops=1)
Hash Cond: (tb.transaction_id = t.transaction_id)
->  Seq Scan on transaction_breakdown tb
(actual time=0.012..8.780 rows=110011 loops=1)
->  Hash
->  Seq Scan on transaction t
Filter: (date >= (now() - '1 year'::interval))
Rows Removed by Filter: 87672
->  Hash
->  Seq Scan on transaction_account ta
Planning Time: 0.538 ms
Execution Time: 73.792 ms
}}}

'''По додавање на индексите:'''

{{{#!sql
Limit  (cost=3675.91..3675.93 rows=10 width=49) (actual time=47.820..47.826 rows=10 loops=1)
Buffers: shared hit=1540 read=36
->  Sort  (cost=3675.91..3677.17 rows=505 width=49) (actual time=47.818..47.822 rows=10 loops=1)
Sort Key: (COALESCE(sum(tb.spent_amount), '0'::numeric)) DESC
Sort Method: top-N heapsort  Memory: 26kB
->  HashAggregate  (cost=3658.68..3665.00 rows=505 width=49) (actual time=47.717..47.765 rows=113 loops=1)
Group Key: ta.account_name
->  Hash Join  (cost=1463.44..3589.80 rows=13776 width=23) (actual time=9.049..43.449 rows=13568 loops=1)
Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
->  Hash Join  (cost=1447.08..3536.98 rows=13776 width=10) (actual time=8.845..40.394 rows=13568 loops=1)
Hash Cond: (tb.transaction_id = t.transaction_id)
->  Seq Scan on transaction_breakdown tb
(actual time=0.011..9.164 rows=110011 loops=1)
->  Hash
->  Bitmap Heap Scan on transaction t
Recheck Cond: (date >= (now() - '1 year'::interval))
Heap Blocks: exact=834
->  Bitmap Index Scan on idx_transaction_date
Index Cond: (date >= (now() - '1 year'::interval))
->  Hash
->  Seq Scan on transaction_account ta
Planning Time: 0.654 ms
Execution Time: 47.902 ms
}}}

'''Споредба:'''

||= Метрика =||= Пред индекси =||= По индекси =||
|| Planning Time || 0.538 ms || 0.654 ms ||
|| Execution Time || 73.792 ms || 47.902 ms ||
|| Подобрување на Execution Time || / || ~35.08% ||
|| Забрзување || / || ~1.54x ||
|| Тип на скенирање пред индекси || `Seq Scan` на `transaction` || / ||
|| Тип на скенирање по индекси || / || `Bitmap Index Scan` + `Bitmap Heap Scan` на `transaction` ||
|| Дали новите индекси се користат || Не || Да ||

По додавање на индексите, PostgreSQL го користи `idx_transaction_date` преку:

{{{#!sql
Bitmap Index Scan on idx_transaction_date
Index Cond: (date >= (now() - '1 year'::interval))
}}}

Останатите табели, како `transaction_breakdown` и `transaction_account`, сè уште се читаат со `Seq Scan`, затоа што query-то пресметува агрегат за сите сметки, а не за еден конкретен корисник или една конкретна сметка.

'''Заклучок:'''

Времето на извршување се намали од 73.792 ms на 47.902 ms, што претставува приближно 35.08% подобрување. Ова подобрување е директно поврзано со индексот `idx_transaction_date`, бидејќи извештајот филтрира трансакции само од последната година. Пред индексот, PostgreSQL мораше да ја чита целата табела `transaction` и да отфрли 87672 редови преку filter. По додавање на индексот, PostgreSQL користи `Bitmap Index Scan` за побрзо да ги најде трансакциите од последната година.

==== Сценарио 3: Годишни трендови на трансакции за корисник ====

'''Цел:'''
Овој извештај ја прикажува потрошувачката по квартали за конкретен корисник во тековната година. Прашалникот користи `JOIN` помеѓу `transaction_breakdown`, `transaction` и `transaction_account`, филтрирање по корисник и временски период, `SUM` агрегации со `CASE WHEN`, `GROUP BY` и `ORDER BY`.

Во анализата условот за година е напишан како временски опсег, наместо само со `EXTRACT(YEAR FROM t.date)`, за B-Tree индексот на `date` да може поефикасно да се користи кога табелата ќе порасне.

'''Анализиран SQL:'''

{{{#!sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT
ta.account_name,
SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 1 THEN tb.spent_amount ELSE 0 END) AS q1_spent,
SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 2 THEN tb.spent_amount ELSE 0 END) AS q2_spent,
SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 3 THEN tb.spent_amount ELSE 0 END) AS q3_spent,
SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 4 THEN tb.spent_amount ELSE 0 END) AS q4_spent
FROM transaction_breakdown tb
JOIN "transaction" t
ON tb.transaction_id = t.transaction_id
JOIN transaction_account ta
ON tb.transaction_account_id = ta.transaction_account_id
WHERE
ta.user_id = 5
AND t.date >= DATE_TRUNC('year', CURRENT_DATE)
AND t.date < DATE_TRUNC('year', CURRENT_DATE) + INTERVAL '1 YEAR'
GROUP BY
ta.account_name
ORDER BY
ta.account_name;
}}}

'''Релевантни индекси за ова сценарио:'''

{{{#!sql
CREATE INDEX IF NOT EXISTS idx_transaction_date
ON "transaction"(date);

CREATE INDEX IF NOT EXISTS idx_transaction_account_user_id
ON transaction_account(user_id);

CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_transaction_id
ON transaction_breakdown(transaction_id);

CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_account_id
ON transaction_breakdown(transaction_account_id);
}}}

'''Пред додавање на индексите:'''

{{{#!sql
GroupAggregate  (cost=2511.77..2514.21 rows=5 width=145) (actual time=26.493..26.495 rows=1 loops=1)
Group Key: ta.account_name
Buffers: shared hit=3706
->  Sort  (cost=2511.77..2511.93 rows=67 width=31) (actual time=26.366..26.372 rows=70 loops=1)
Sort Key: ta.account_name
Sort Method: quicksort  Memory: 28kB
->  Nested Loop  (cost=11.67..2509.73 rows=67 width=31) (actual time=0.322..26.308 rows=70 loops=1)
->  Hash Join  (cost=11.38..2103.69 rows=1089 width=27) (actual time=0.301..21.727 rows=1000 loops=1)
Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
->  Seq Scan on transaction_breakdown tb
(actual time=0.019..8.175 rows=110011 loops=1)
->  Hash
->  Seq Scan on transaction_account ta
Filter: (user_id = 5)
Rows Removed by Filter: 500
->  Index Scan using transaction_pkey on transaction t
Index Cond: (transaction_id = tb.transaction_id)
Filter: ((date >= date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone))
AND (date < (date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) + '1 year'::interval)))
Rows Removed by Filter: 1
Planning Time: 0.495 ms
Execution Time: 26.571 ms
}}}

'''По додавање на индексите:'''

{{{#!sql
GroupAggregate  (cost=2458.99..2461.40 rows=5 width=145) (actual time=5.086..5.088 rows=1 loops=1)
Group Key: ta.account_name
Buffers: shared hit=3023
->  Sort  (cost=2458.99..2459.16 rows=66 width=31) (actual time=4.954..4.960 rows=70 loops=1)
Sort Key: ta.account_name
Sort Method: quicksort  Memory: 28kB
->  Nested Loop  (cost=16.38..2457.00 rows=66 width=31) (actual time=0.104..4.917 rows=70 loops=1)
->  Nested Loop  (cost=16.09..2050.95 rows=1089 width=27) (actual time=0.086..0.420 rows=1000 loops=1)
->  Bitmap Heap Scan on transaction_account ta
Recheck Cond: (user_id = 5)
->  Bitmap Index Scan on idx_transaction_account_user_id
Index Cond: (user_id = 5)
->  Bitmap Heap Scan on transaction_breakdown tb
Recheck Cond: (transaction_account_id = ta.transaction_account_id)
->  Bitmap Index Scan on idx_transaction_breakdown_account_id
Index Cond: (transaction_account_id = ta.transaction_account_id)
->  Index Scan using transaction_pkey on transaction t
Index Cond: (transaction_id = tb.transaction_id)
Filter: ((date >= date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone))
AND (date < (date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) + '1 year'::interval)))
Rows Removed by Filter: 1
Planning Time: 0.724 ms
Execution Time: 5.169 ms
}}}

'''Споредба:'''

||= Метрика =||= Пред индекси =||= По индекси =||
|| Planning Time || 0.495 ms || 0.724 ms ||
|| Execution Time || 26.571 ms || 5.169 ms ||
|| Подобрување на Execution Time || / || ~80.55% ||
|| Забрзување || / || ~5.14x ||
|| Тип на скенирање пред индекси || `Seq Scan` на `transaction_breakdown` и `transaction_account` || / ||
|| Тип на скенирање по индекси || / || `Bitmap Index Scan`, `Bitmap Heap Scan`, `Index Scan` ||
|| Дали новите индекси се користат || Не || Да ||

По додавање на индексите, PostgreSQL ги користи:

* `idx_transaction_account_user_id` преку `Bitmap Index Scan`
* `idx_transaction_breakdown_account_id` преку `Bitmap Index Scan`

Индексот `idx_transaction_date` не е директно искористен во овој план. Причината е што optimizer-от прво ги ограничува податоците преку конкретниот `user_id` и неговите сметки, а потоа пристапува до трансакциите преку `transaction_pkey`. Во овој случај филтрирањето по корисник е поселективно од самото филтрирање по датум.

'''Заклучок:'''

Времето на извршување се намали од 26.571 ms на 5.169 ms, што претставува приближно 80.55% подобрување, односно околу 5.14 пати побрзо извршување. Ова подобрување е резултат на тоа што PostgreSQL повеќе не ја чита целата табела `transaction_breakdown` и целата табела `transaction_account`, туку директно ги наоѓа сметките на конкретниот корисник и breakdown записите поврзани со тие сметки.

==== Финален заклучок од анализата ====

Првичната анализа со мал број податоци не покажа реална употреба на индексите, затоа што табелите беа премногу мали и PostgreSQL правилно избра `Seq Scan`. Поради тоа беше генериран поголем, логички поврзан сет на тест-податоци, кој содржи 100012 трансакции, 110011 breakdown записи и 125016 записи за доделени тагови.

По зголемување на количината на податоци, индексите почнаа да имаат значајно влијание врз execution plan-от и времето на извршување.

||= Сценарио =||= Execution Time пред индекси =||= Execution Time по индекси =||= Подобрување =||= Индекси искористени =||
|| Трендови на трошење според тагови за корисник || 51.744 ms || 7.055 ms || ~86.36% || Да ||
|| Сметки со највисоко трошење во изминатата година || 73.792 ms || 47.902 ms || ~35.08% || Да ||
|| Годишни трендови на трансакции за корисник || 26.571 ms || 5.169 ms || ~80.55% || Да ||

Најголемо подобрување се забележува кај извештаите кои филтрираат по конкретен корисник, затоа што индексите `idx_transaction_account_user_id` и `idx_transaction_breakdown_account_id` овозможуваат PostgreSQL директно да ги најде релевантните сметки и breakdown записи, наместо да ги чита целите табели.

Кај извештајот за сметки со највисоко трошење во последната година, најважен е индексот `idx_transaction_date`, затоа што условот по датум ја намалува количината на трансакции кои треба да се обработат.

Заклучокот е дека предложените индекси се оправдани и корисни за FEiN, особено кога базата содржи поголем број трансакции. Тие значително го намалуваат времето на извршување на аналитичките извештаи и ја подобруваат скалабилноста на системот.





=== 3. Интегритет и конзистентност
Со цел самата база да биде отпорна на грешки, имплементирани се стриктни ограничувања (`CONSTRAINTS`) и тригери кои ја заштитуваат финансиската историја на корисниците.

{{{#!sql
ALTER TABLE "user"
ADD CONSTRAINT user_email_unique UNIQUE (email);
}}}

- '''Надворешни клучеви:''' Релациите помеѓу табелите се реализирани преку надворешни клучеви (`FOREIGN KEY`). Со тоа се спречува внесување на записи кои референцираат непостоечки корисници, сметки, трансакции или тагови.
- '''Заштита на финансиска историја:''' За разлика од автоматско каскадно бришење, кај трансакциските сметки се користи тригер кој спречува бришење на сметка доколку таа има поврзани записи во `transaction_breakdown`. Ова е важно затоа што бришење на сметка со постоечка историја може да доведе до губење на финансиски податоци и неточни извештаи.
- '''Одржување на логичка конзистентност:''' Дополнително, системот користи тригер кој автоматски ја брише трансакцијата доколку по бришење на breakdown запис повеќе не постојат поврзани breakdown записи за таа трансакција. Со тоа се спречува постоење на „празни“ трансакции без финансиско значење.
- '''Ограничувања на уникатност:''' Корисничките мејлови се заштитени со `UNIQUE` ограничување, со што се гарантира дека во системот не можат да постојат два кориснички профили со иста емаил адреса.
- '''Типови на податоци за финансиски вредности:''' Сите монетарни вредности во системот се складираат со типот `NUMERIC(10,2)`. Овој пристап обезбедува фиксна прецизност до две децимали и ги елиминира грешките кои можат да настанат при користење на типови со подвижна запирка(`FLOAT` или `DOUBLE PRECISION`) во финансиски пресметки.

=== 4. Одржување на базата
- '''Зачувување на структурните промени:''' Секоја промена на структурата на базата се документира во Trac документацијата и се имплементира директно во PostgreSQL преку SQL наредби. На тој начин документацијата останува усогласена со реалната имплементација на системот.
- '''Чување на SQL скриптите:''' Секоја SQL скрипта извршена после првата DDL скрипта ја чувам хронолошки, за во случај да треба базата да се иницијализира повторно, да ги извршам редоследно и да ја добијам истата состојба која сум ја имал.
- '''Одговорност за инфраструктурата:''' FEiN е дизајниран да работи врз PostgreSQL сервер обезбеден од надворешна инфраструктура. Конфигурацијата на серверот, резервните копии и механизмите за обновување на податоците се надвор од опсегот на самата апликација и зависат од околината во која е поставена базата на податоци.