Changes between Version 2 and Version 3 of OtherTopics


Ignore:
Timestamp:
06/26/26 08:42:26 (42 hours ago)
Author:
211101
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • OtherTopics

    v2 v3  
    88
    99=== 2. Перформанси и оптимизација
    10 - '''Стратегија на индексирање:''' Базата користи B-Tree индекси кои PostgreSQL автоматски ги креира за примарните клучеви(`PRIMARY KEY`) и уникатните ограничувања(`UNIQUE`). На тој начин е овозможен брз пристап до записите при пребарување по идентификатори на ентитетите, како и при проверка на единственоста на корисничките е-пошти. Индексите моментално постојат над колоните `user.user_id`, `user.email`, `transaction.transaction_id`, `transaction_account.transaction_account_id`, `transaction_breakdown.transaction_breakdown_id`, `tag.tag_id` и `tag_assigned_to_transaction.tag_assigned_to_transaction_id`.
    11 - '''Оптимизација преку релациски ограничувања:''' Сите врски помеѓу табелите се реализирани преку надворешни клучеви(`FOREIGN KEY`), со што PostgreSQL може ефикасно да го одржува интегритетот на податоците при вметнување, ажурирање и бришење на записи.
    12 - '''Подготвеност за понатамошна оптимизација:''' Со растот на количината на податоци, можно е дополнително индексирање на колони кои често се користат при филтрирање и поврзување на податоците, како што се `transaction.date`, `transaction_account.user_id`, `transaction_breakdown.transaction_id` и `tag_assigned_to_transaction.transaction_id`. Ваквата оптимизација е особено корисна кај статистичките извештаи и аналитичките прашалници кои обработуваат поголем број трансакции.
     10Анализата е направена со `EXPLAIN (ANALYZE, BUFFERS)`, при што се споредува планот за извршување пред и по додавање на дополнителните индекси. Во почетната состојба базата ги содржи само индексите кои PostgreSQL автоматски ги креира за `PRIMARY KEY` и `UNIQUE` ограничувања. Овие индекси не се третираат како дополнителна оптимизација, туку како дел од основната релациска структура на базата.
     11
     12Во моментот на тестирањето, базата ја има следната количина на податоци:
     13
     14||= Табела =||= Број на редови =||
     15|| `user` || 3 ||
     16|| `transaction` || 12 ||
     17|| `transaction_account` || 5 ||
     18|| `transaction_breakdown` || 11 ||
     19|| `tag` || 11 ||
     20|| `tag_assigned_to_transaction` || 16 ||
     21
     22Поради малата количина на податоци, очекувано е PostgreSQL во повеќе случаи да избере `Seq Scan`, бидејќи целосното читање на мала табела е поевтино од пристап преку индекс. Затоа, во анализата не се гледа само времето на извршување, туку и дали индексот навистина се користи во execution plan-от.
     23
     24==== Предложени дополнителни индекси ====
     25
     26Следните индекси се предложени затоа што колоните често се користат во `JOIN` и `WHERE` услови во аналитичките извештаи:
     27
     28{{{#!sql
     29CREATE INDEX IF NOT EXISTS idx_transaction_date
     30ON "transaction"(date);
     31
     32CREATE INDEX IF NOT EXISTS idx_transaction_account_user_id
     33ON transaction_account(user_id);
     34
     35CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_transaction_id
     36ON transaction_breakdown(transaction_id);
     37
     38CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_account_id
     39ON transaction_breakdown(transaction_account_id);
     40
     41CREATE INDEX IF NOT EXISTS idx_tag_assigned_transaction_id
     42ON tag_assigned_to_transaction(transaction_id);
     43
     44CREATE INDEX IF NOT EXISTS idx_tag_assigned_tag_id
     45ON tag_assigned_to_transaction(tag_id);
     46}}}
     47
     48Индексот `idx_transaction_date` е наменет за извештаи кои филтрираат трансакции според временски период. Индексот `idx_transaction_account_user_id` е наменет за извештаи кои ги ограничуваат резултатите на конкретен корисник. Индексите на `transaction_breakdown` и `tag_assigned_to_transaction` се наменети за побрзо поврзување на трансакциите со сметки и тагови.
     49
     50==== Сценарио 1: Трендови на трошење според тагови за корисник ====
     51
     52'''Цел:'''
     53Овој извештај ја прикажува потрошувачката по тагови за конкретен корисник во последните шест месеци. Прашалникот е комплексен затоа што ги поврзува табелите `tag_assigned_to_transaction`, `transaction`, `transaction_breakdown`, `transaction_account` и `tag`, а дополнително врши филтрирање по корисник и датум, групирање по таг и месец, и сумирање на потрошената сума.
     54
     55'''Анализиран SQL:'''
     56
     57{{{#!sql
     58EXPLAIN (ANALYZE, BUFFERS)
     59SELECT
     60tg.tag_name,
     61DATE_TRUNC('month', t.date) AS month,
     62SUM(tb.spent_amount) AS total_spent
     63FROM tag_assigned_to_transaction tat
     64JOIN "transaction" t
     65ON tat.transaction_id = t.transaction_id
     66JOIN transaction_breakdown tb
     67ON t.transaction_id = tb.transaction_id
     68JOIN transaction_account ta
     69ON tb.transaction_account_id = ta.transaction_account_id
     70JOIN tag tg
     71ON tat.tag_id = tg.tag_id
     72WHERE
     73ta.user_id = 1
     74AND t.date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS')
     75GROUP BY
     76tg.tag_name, month
     77ORDER BY
     78tg.tag_name, month;
     79}}}
     80
     81'''Релевантни индекси за ова сценарио:'''
     82
     83{{{#!sql
     84CREATE INDEX IF NOT EXISTS idx_transaction_date
     85ON "transaction"(date);
     86
     87CREATE INDEX IF NOT EXISTS idx_transaction_account_user_id
     88ON transaction_account(user_id);
     89
     90CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_transaction_id
     91ON transaction_breakdown(transaction_id);
     92
     93CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_account_id
     94ON transaction_breakdown(transaction_account_id);
     95
     96CREATE INDEX IF NOT EXISTS idx_tag_assigned_transaction_id
     97ON tag_assigned_to_transaction(transaction_id);
     98
     99CREATE INDEX IF NOT EXISTS idx_tag_assigned_tag_id
     100ON tag_assigned_to_transaction(tag_id);
     101}}}
     102
     103'''План пред додавање на индексите:'''
     104
     105{{{#!sql
     106GroupAggregate  (cost=6.54..6.77 rows=9 width=53) (actual time=0.176..0.189 rows=7 loops=1)
     107Group Key: tg.tag_name, (date_trunc('month'::text, t.date))
     108Buffers: shared hit=5
     109->  Sort  (cost=6.54..6.57 rows=9 width=25) (actual time=0.162..0.167 rows=16 loops=1)
     110Sort Key: tg.tag_name, (date_trunc('month'::text, t.date))
     111Sort Method: quicksort  Memory: 25kB
     112->  Hash Join  (cost=4.99..6.40 rows=9 width=25) (actual time=0.119..0.142 rows=16 loops=1)
     113Hash Cond: (tat.tag_id = tg.tag_id)
     114->  Hash Join  (cost=3.74..5.10 rows=9 width=16) (actual time=0.086..0.099 rows=16 loops=1)
     115Hash Cond: (tat.transaction_id = t.transaction_id)
     116->  Hash Join  (cost=2.35..3.67 rows=10 width=16) (actual time=0.057..0.066 rows=16 loops=1)
     117Hash Cond: (tat.transaction_id = tb.transaction_id)
     118->  Seq Scan on tag_assigned_to_transaction tat
     119->  Hash
     120->  Hash Join
     121Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
     122->  Seq Scan on transaction_breakdown tb
     123->  Hash
     124->  Seq Scan on transaction_account ta
     125Filter: (user_id = 1)
     126Rows Removed by Filter: 2
     127->  Hash
     128->  Seq Scan on transaction t
     129Filter: (date >= date_trunc('month'::text, (CURRENT_DATE - '6 mons'::interval)))
     130->  Hash
     131->  Seq Scan on tag tg
     132Planning Time: 0.733 ms
     133Execution Time: 0.296 ms
     134}}}
     135
     136'''План по додавање на индексите:'''
     137
     138{{{#!sql
     139GroupAggregate  (cost=6.54..6.77 rows=9 width=53) (actual time=0.154..0.166 rows=7 loops=1)
     140Group Key: tg.tag_name, (date_trunc('month'::text, t.date))
     141Buffers: shared hit=5
     142->  Sort  (cost=6.54..6.57 rows=9 width=25) (actual time=0.142..0.146 rows=16 loops=1)
     143Sort Key: tg.tag_name, (date_trunc('month'::text, t.date))
     144Sort Method: quicksort  Memory: 25kB
     145->  Hash Join  (cost=4.99..6.40 rows=9 width=25) (actual time=0.100..0.122 rows=16 loops=1)
     146Hash Cond: (tat.tag_id = tg.tag_id)
     147->  Hash Join  (cost=3.74..5.10 rows=9 width=16) (actual time=0.068..0.081 rows=16 loops=1)
     148Hash Cond: (tat.transaction_id = t.transaction_id)
     149->  Hash Join  (cost=2.35..3.67 rows=10 width=16) (actual time=0.040..0.049 rows=16 loops=1)
     150Hash Cond: (tat.transaction_id = tb.transaction_id)
     151->  Seq Scan on tag_assigned_to_transaction tat
     152->  Hash
     153->  Hash Join
     154Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
     155->  Seq Scan on transaction_breakdown tb
     156->  Hash
     157->  Seq Scan on transaction_account ta
     158Filter: (user_id = 1)
     159Rows Removed by Filter: 2
     160->  Hash
     161->  Seq Scan on transaction t
     162Filter: (date >= date_trunc('month'::text, (CURRENT_DATE - '6 mons'::interval)))
     163->  Hash
     164->  Seq Scan on tag tg
     165Planning Time: 0.921 ms
     166Execution Time: 0.242 ms
     167}}}
     168
     169'''Споредба:'''
     170
     171||= Метрика =||= Пред индекси =||= По индекси =||
     172|| Planning Time || 0.733 ms || 0.921 ms ||
     173|| Execution Time || 0.296 ms || 0.242 ms ||
     174|| Тип на скенирање || `Seq Scan` || `Seq Scan` ||
     175|| Дали новите индекси се користат || Не || Не ||
     176
     177'''Заклучок:'''
     178Времето на извршување се намали од 0.296 ms на 0.242 ms, што претставува намалување од приближно 18.2%. Сепак, execution plan-от и по додавање на индексите користи `Seq Scan` над сите релевантни табели, а не `Index Scan` или `Bitmap Index Scan`. Затоа подобрувањето не може да се припише директно на индексите. Најверојатната причина е малата количина на податоци и фактот што сите блокови се веќе во shared buffers. Индексите остануваат логични за идно зголемување на бројот на трансакции, тагови и breakdown записи.
     179
     180==== Сценарио 2: Сметки со највисоко вкупно трошење во изминатата година ====
     181
     182'''Цел:'''
     183Овој извештај ги прикажува сметките со најголема вкупна потрошувачка во последната година. Прашалникот користи `JOIN` помеѓу `transaction_breakdown`, `transaction` и `transaction_account`, филтрирање по датум, `SUM` агрегација, `GROUP BY`, `ORDER BY` и `LIMIT`.
     184
     185'''Анализиран SQL:'''
     186
     187{{{#!sql
     188EXPLAIN (ANALYZE, BUFFERS)
     189SELECT
     190ta.account_name,
     191COALESCE(SUM(tb.spent_amount), 0) AS total_spent
     192FROM transaction_breakdown tb
     193JOIN "transaction" t
     194ON tb.transaction_id = t.transaction_id
     195JOIN transaction_account ta
     196ON tb.transaction_account_id = ta.transaction_account_id
     197WHERE
     198t.date >= NOW() - INTERVAL '1 YEAR'
     199GROUP BY
     200ta.account_name
     201ORDER BY
     202total_spent DESC
     203LIMIT 10;
     204}}}
     205
     206'''Релевантни индекси за ова сценарио:'''
     207
     208{{{#!sql
     209CREATE INDEX IF NOT EXISTS idx_transaction_date
     210ON "transaction"(date);
     211
     212CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_transaction_id
     213ON transaction_breakdown(transaction_id);
     214
     215CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_account_id
     216ON transaction_breakdown(transaction_account_id);
     217}}}
     218
     219'''План пред додавање на индексите:'''
     220
     221{{{#!sql
     222Limit  (cost=3.85..3.86 rows=5 width=40) (actual time=0.110..0.114 rows=3 loops=1)
     223Buffers: shared hit=3
     224->  Sort  (cost=3.85..3.86 rows=5 width=40) (actual time=0.108..0.111 rows=3 loops=1)
     225Sort Key: (COALESCE(sum(tb.spent_amount), '0'::numeric)) DESC
     226Sort Method: quicksort  Memory: 25kB
     227->  HashAggregate  (cost=3.73..3.79 rows=5 width=40) (actual time=0.097..0.101 rows=3 loops=1)
     228Group Key: ta.account_name
     229->  Hash Join  (cost=2.47..3.67 rows=11 width=12) (actual time=0.059..0.069 rows=11 loops=1)
     230Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
     231->  Hash Join  (cost=1.36..2.51 rows=11 width=8) (actual time=0.032..0.038 rows=11 loops=1)
     232Hash Cond: (tb.transaction_id = t.transaction_id)
     233->  Seq Scan on transaction_breakdown tb
     234->  Hash
     235->  Seq Scan on transaction t
     236Filter: (date >= (now() - '1 year'::interval))
     237->  Hash
     238->  Seq Scan on transaction_account ta
     239Planning Time: 0.403 ms
     240Execution Time: 0.191 ms
     241}}}
     242
     243'''План по додавање на индексите:'''
     244
     245{{{#!sql
     246Limit  (cost=3.85..3.86 rows=5 width=40) (actual time=0.100..0.104 rows=3 loops=1)
     247Buffers: shared hit=3
     248->  Sort  (cost=3.85..3.86 rows=5 width=40) (actual time=0.098..0.101 rows=3 loops=1)
     249Sort Key: (COALESCE(sum(tb.spent_amount), '0'::numeric)) DESC
     250Sort Method: quicksort  Memory: 25kB
     251->  HashAggregate  (cost=3.73..3.79 rows=5 width=40) (actual time=0.086..0.090 rows=3 loops=1)
     252Group Key: ta.account_name
     253->  Hash Join  (cost=2.47..3.67 rows=11 width=12) (actual time=0.060..0.070 rows=11 loops=1)
     254Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
     255->  Hash Join  (cost=1.36..2.51 rows=11 width=8) (actual time=0.030..0.036 rows=11 loops=1)
     256Hash Cond: (tb.transaction_id = t.transaction_id)
     257->  Seq Scan on transaction_breakdown tb
     258->  Hash
     259->  Seq Scan on transaction t
     260Filter: (date >= (now() - '1 year'::interval))
     261->  Hash
     262->  Seq Scan on transaction_account ta
     263Planning Time: 0.451 ms
     264Execution Time: 0.166 ms
     265}}}
     266
     267'''Споредба:'''
     268
     269||= Метрика =||= Пред индекси =||= По индекси =||
     270|| Planning Time || 0.403 ms || 0.451 ms ||
     271|| Execution Time || 0.191 ms || 0.166 ms ||
     272|| Тип на скенирање || `Seq Scan` || `Seq Scan` ||
     273|| Дали новите индекси се користат || Не || Не ||
     274
     275'''Заклучок:'''
     276Времето на извршување се намали од 0.191 ms на 0.166 ms, што претставува намалување од приближно 13.1%. Сепак, планот за извршување не користи ниту еден од новите индекси. PostgreSQL продолжува со `Seq Scan` на `transaction`, `transaction_breakdown` и `transaction_account`, затоа што табелите имаат многу мал број редови и читањето на целата табела е поевтино од пристап преку индекс. Поради тоа, малото подобрување не се смета како директен ефект од индексите. Индексот на `transaction.date` сепак е оправдан за идно зголемување на бројот на трансакции, бидејќи извештајот филтрира според временски период.
     277
     278==== Сценарио 3: Годишни трендови на трансакции за корисник ====
     279
     280'''Цел:'''
     281Овој извештај ја прикажува потрошувачката по квартали за конкретен корисник во тековната година. Прашалникот користи `JOIN` помеѓу `transaction_breakdown`, `transaction` и `transaction_account`, филтрирање по корисник и временски период, `SUM` агрегации со `CASE WHEN`, `GROUP BY` и `ORDER BY`.
     282
     283Во анализата условот за година е напишан како временски опсег, наместо само со `EXTRACT(YEAR FROM t.date)`, за B-Tree индексот на `date` да може поефикасно да се користи кога табелата ќе порасне.
     284
     285'''Анализиран SQL:'''
     286
     287{{{#!sql
     288EXPLAIN (ANALYZE, BUFFERS)
     289SELECT
     290ta.account_name,
     291SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 1 THEN tb.spent_amount ELSE 0 END) AS q1_spent,
     292SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 2 THEN tb.spent_amount ELSE 0 END) AS q2_spent,
     293SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 3 THEN tb.spent_amount ELSE 0 END) AS q3_spent,
     294SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 4 THEN tb.spent_amount ELSE 0 END) AS q4_spent
     295FROM transaction_breakdown tb
     296JOIN "transaction" t
     297ON tb.transaction_id = t.transaction_id
     298JOIN transaction_account ta
     299ON tb.transaction_account_id = ta.transaction_account_id
     300WHERE
     301ta.user_id = 1
     302AND t.date >= DATE_TRUNC('year', CURRENT_DATE)
     303AND t.date < DATE_TRUNC('year', CURRENT_DATE) + INTERVAL '1 YEAR'
     304GROUP BY
     305ta.account_name
     306ORDER BY
     307ta.account_name;
     308}}}
     309
     310'''Релевантни индекси за ова сценарио:'''
     311
     312{{{#!sql
     313CREATE INDEX IF NOT EXISTS idx_transaction_date
     314ON "transaction"(date);
     315
     316CREATE INDEX IF NOT EXISTS idx_transaction_account_user_id
     317ON transaction_account(user_id);
     318
     319CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_transaction_id
     320ON transaction_breakdown(transaction_id);
     321
     322CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_account_id
     323ON transaction_breakdown(transaction_account_id);
     324}}}
     325
     326'''План пред додавање на индексите:'''
     327
     328{{{#!sql
     329Sort  (cost=4.16..4.17 rows=3 width=136) (actual time=0.141..0.144 rows=3 loops=1)
     330Sort Key: ta.account_name
     331Sort Method: quicksort  Memory: 25kB
     332Buffers: shared hit=3
     333->  HashAggregate  (cost=4.08..4.14 rows=3 width=136) (actual time=0.126..0.131 rows=3 loops=1)
     334Group Key: ta.account_name
     335->  Hash Join  (cost=2.35..3.85 rows=7 width=20) (actual time=0.070..0.089 rows=11 loops=1)
     336Hash Cond: (t.transaction_id = tb.transaction_id)
     337->  Seq Scan on transaction t
     338Filter: ((date >= date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone))
     339AND (date < (date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) + '1 year'::interval)))
     340->  Hash
     341->  Hash Join
     342Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
     343->  Seq Scan on transaction_breakdown tb
     344->  Hash
     345->  Seq Scan on transaction_account ta
     346Filter: (user_id = 1)
     347Rows Removed by Filter: 2
     348Planning Time: 0.402 ms
     349Execution Time: 0.242 ms
     350}}}
     351
     352'''План по додавање на индексите:'''
     353
     354{{{#!sql
     355Sort  (cost=4.16..4.17 rows=3 width=136) (actual time=0.131..0.135 rows=3 loops=1)
     356Sort Key: ta.account_name
     357Sort Method: quicksort  Memory: 25kB
     358Buffers: shared hit=3
     359->  HashAggregate  (cost=4.08..4.14 rows=3 width=136) (actual time=0.117..0.122 rows=3 loops=1)
     360Group Key: ta.account_name
     361->  Hash Join  (cost=2.35..3.85 rows=7 width=20) (actual time=0.063..0.082 rows=11 loops=1)
     362Hash Cond: (t.transaction_id = tb.transaction_id)
     363->  Seq Scan on transaction t
     364Filter: ((date >= date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone))
     365AND (date < (date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) + '1 year'::interval)))
     366->  Hash
     367->  Hash Join
     368Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
     369->  Seq Scan on transaction_breakdown tb
     370->  Hash
     371->  Seq Scan on transaction_account ta
     372Filter: (user_id = 1)
     373Rows Removed by Filter: 2
     374Planning Time: 0.500 ms
     375Execution Time: 0.212 ms
     376}}}
     377
     378'''Споредба:'''
     379
     380||= Метрика =||= Пред индекси =||= По индекси =||
     381|| Planning Time || 0.402 ms || 0.500 ms ||
     382|| Execution Time || 0.242 ms || 0.212 ms ||
     383|| Тип на скенирање || `Seq Scan` || `Seq Scan` ||
     384|| Дали новите индекси се користат || Не || Не ||
     385
     386'''Заклучок:'''
     387Времето на извршување се намали од 0.242 ms на 0.212 ms, што претставува намалување од приближно 12.4%. Сепак, планот останува ист во однос на пристапот до табелите: PostgreSQL користи `Seq Scan`, а не новите индекси. Причината е малата количина на податоци: `transaction` има 12 редови, `transaction_breakdown` има 11 редови, а `transaction_account` има 5 редови. За ваква големина, секвенцијалното читање е поевтино од пристап преку индекс. Индексите сепак се соодветни за идно зголемување на базата, особено затоа што извештајот филтрира по `user_id` и по временски период.
     388
     389==== Финален заклучок од анализата ====
     390
     391Во сите три сценарија, PostgreSQL продолжи да користи `Seq Scan` и по додавање на предложените индекси. Тоа значи дека индексите не беа реално искористени во моменталниот execution plan.
     392
     393Ова однесување е очекувано поради малата количина на податоци во тест базата. Во моментот на тестирањето, најголемите релевантни табели имаат само 12 трансакции, 11 breakdown записи и 16 tag-assignment записи. За вакви табели, PostgreSQL правилно проценува дека целосно скенирање на табелата е поевтино од користење индекс.
     394
     395Иако execution time се намали во сите три сценарија, тоа не може директно да се припише на индексите, бидејќи execution plan-от не покажува `Index Scan`, `Bitmap Index Scan` или `Index Only Scan`. Малите разлики во времето најверојатно се резултат на кеширање, повторно користење на податоци во shared buffers и нормална варијација при извршување.
     396
     397Сепак, предложените индекси остануваат оправдани како подготовка за поголема количина на податоци. Со раст на бројот на корисници, трансакции, сметки, тагови и breakdown записи, индексите на `transaction.date`, `transaction_account.user_id`, `transaction_breakdown.transaction_id`, `transaction_breakdown.transaction_account_id`, `tag_assigned_to_transaction.transaction_id` и `tag_assigned_to_transaction.tag_id` ќе овозможат поефикасно филтрирање и поврзување на податоците во аналитичките извештаи.
     398
    13399
    14400
    15401=== 3. Интегритет и конзистентност
    16 Со цел самата база да биде отпорна на грешки, имплементирани се стриктни ограничувања (`CONSTRAINTS`) кои се компатибилно пресликани и преку типовите во [source:/app/lib/definitions.ts]:
    17 {{{#!sql
    18 ALTER TABLE transaction_account
    19 DROP CONSTRAINT transaction_account_user_id_fkey;
    20 
    21 ALTER TABLE transaction_account
    22 ADD CONSTRAINT transaction_account_user_id_fkey
    23 FOREIGN KEY (user_id)
    24 REFERENCES "user"(user_id)
    25 ON DELETE CASCADE;
    26 }}}
    27 
    28 {{{#!sql
    29 ALTER TABLE transaction_breakdown
    30 DROP CONSTRAINT transaction_breakdown_transaction_id_fkey;
    31 
    32 ALTER TABLE transaction_breakdown
    33 ADD CONSTRAINT transaction_breakdown_transaction_id_fkey
    34 FOREIGN KEY (transaction_id)
    35 REFERENCES "transaction"(transaction_id)
    36 ON DELETE CASCADE;
    37 }}}
    38 
    39 {{{#!sql
    40 ALTER TABLE transaction_breakdown
    41 DROP CONSTRAINT transaction_breakdown_transaction_account_id_fkey;
    42 
    43 ALTER TABLE transaction_breakdown
    44 ADD CONSTRAINT transaction_breakdown_transaction_account_id_fkey
    45 FOREIGN KEY (transaction_account_id)
    46 REFERENCES transaction_account(transaction_account_id)
    47 ON DELETE CASCADE;
    48 }}}
     402Со цел самата база да биде отпорна на грешки, имплементирани се стриктни ограничувања (`CONSTRAINTS`) и тригери кои ја заштитуваат финансиската историја на корисниците.
    49403
    50404{{{#!sql
    … …  
    52406ADD CONSTRAINT user_email_unique UNIQUE (email);
    53407}}}
    54 - '''Надворешни клучеви и каскадно бришење:''' Сите релации помеѓу ентитетите се заштитени со надворешни клучеви(`FOREIGN KEY`). За зависните записи е конфигурирано каскадно бришење(`ON DELETE CASCADE`), со што се спречува појава на невалидни или неповрзани податоци. При бришење на корисник автоматски се отстрануваат неговите сметки и поврзаните записи за распределба на трансакции. На ист начин, при бришење на трансакција или таг, автоматски се бришат и зависните записи кои се поврзани со нив.
     408
     409- '''Надворешни клучеви:''' Релациите помеѓу табелите се реализирани преку надворешни клучеви (`FOREIGN KEY`). Со тоа се спречува внесување на записи кои референцираат непостоечки корисници, сметки, трансакции или тагови.
     410- '''Заштита на финансиска историја:''' За разлика од автоматско каскадно бришење, кај трансакциските сметки се користи тригер кој спречува бришење на сметка доколку таа има поврзани записи во `transaction_breakdown`. Ова е важно затоа што бришење на сметка со постоечка историја може да доведе до губење на финансиски податоци и неточни извештаи.
     411- '''Одржување на логичка конзистентност:''' Дополнително, системот користи тригер кој автоматски ја брише трансакцијата доколку по бришење на breakdown запис повеќе не постојат поврзани breakdown записи за таа трансакција. Со тоа се спречува постоење на „празни“ трансакции без финансиско значење.
    55412- '''Ограничувања на уникатност:''' Корисничките мејлови се заштитени со `UNIQUE` ограничување, со што се гарантира дека во системот не можат да постојат два кориснички профили со иста емаил адреса.
    56413- '''Типови на податоци за финансиски вредности:''' Сите монетарни вредности во системот се складираат со типот `NUMERIC(10,2)`. Овој пристап обезбедува фиксна прецизност до две децимали и ги елиминира грешките кои можат да настанат при користење на типови со подвижна запирка(`FLOAT` или `DOUBLE PRECISION`) во финансиски пресметки.