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

1. Безбедност на ниво на база

Имплементирани се следните безбедносни механизми директно при комуникацијата со базата, со што се намалува нападниот вектор независно од самиот NextJS сервер:

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

2. Перформанси и оптимизација

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

Во моментот на тестирањето, базата ја има следната количина на податоци:

Табела	Број на редови
user	3
transaction	12
transaction_account	5
transaction_breakdown	11
tag	11
tag_assigned_to_transaction	16

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

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

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

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:

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 = 1
AND t.date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS')
GROUP BY
tg.tag_name, month
ORDER BY
tg.tag_name, month;

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

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);

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

GroupAggregate  (cost=6.54..6.77 rows=9 width=53) (actual time=0.176..0.189 rows=7 loops=1)
Group Key: tg.tag_name, (date_trunc('month'::text, t.date))
Buffers: shared hit=5
->  Sort  (cost=6.54..6.57 rows=9 width=25) (actual time=0.162..0.167 rows=16 loops=1)
Sort Key: tg.tag_name, (date_trunc('month'::text, t.date))
Sort Method: quicksort  Memory: 25kB
->  Hash Join  (cost=4.99..6.40 rows=9 width=25) (actual time=0.119..0.142 rows=16 loops=1)
Hash Cond: (tat.tag_id = tg.tag_id)
->  Hash Join  (cost=3.74..5.10 rows=9 width=16) (actual time=0.086..0.099 rows=16 loops=1)
Hash Cond: (tat.transaction_id = t.transaction_id)
->  Hash Join  (cost=2.35..3.67 rows=10 width=16) (actual time=0.057..0.066 rows=16 loops=1)
Hash Cond: (tat.transaction_id = tb.transaction_id)
->  Seq Scan on tag_assigned_to_transaction tat
->  Hash
->  Hash Join
Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
->  Seq Scan on transaction_breakdown tb
->  Hash
->  Seq Scan on transaction_account ta
Filter: (user_id = 1)
Rows Removed by Filter: 2
->  Hash
->  Seq Scan on transaction t
Filter: (date >= date_trunc('month'::text, (CURRENT_DATE - '6 mons'::interval)))
->  Hash
->  Seq Scan on tag tg
Planning Time: 0.733 ms
Execution Time: 0.296 ms

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

GroupAggregate  (cost=6.54..6.77 rows=9 width=53) (actual time=0.154..0.166 rows=7 loops=1)
Group Key: tg.tag_name, (date_trunc('month'::text, t.date))
Buffers: shared hit=5
->  Sort  (cost=6.54..6.57 rows=9 width=25) (actual time=0.142..0.146 rows=16 loops=1)
Sort Key: tg.tag_name, (date_trunc('month'::text, t.date))
Sort Method: quicksort  Memory: 25kB
->  Hash Join  (cost=4.99..6.40 rows=9 width=25) (actual time=0.100..0.122 rows=16 loops=1)
Hash Cond: (tat.tag_id = tg.tag_id)
->  Hash Join  (cost=3.74..5.10 rows=9 width=16) (actual time=0.068..0.081 rows=16 loops=1)
Hash Cond: (tat.transaction_id = t.transaction_id)
->  Hash Join  (cost=2.35..3.67 rows=10 width=16) (actual time=0.040..0.049 rows=16 loops=1)
Hash Cond: (tat.transaction_id = tb.transaction_id)
->  Seq Scan on tag_assigned_to_transaction tat
->  Hash
->  Hash Join
Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
->  Seq Scan on transaction_breakdown tb
->  Hash
->  Seq Scan on transaction_account ta
Filter: (user_id = 1)
Rows Removed by Filter: 2
->  Hash
->  Seq Scan on transaction t
Filter: (date >= date_trunc('month'::text, (CURRENT_DATE - '6 mons'::interval)))
->  Hash
->  Seq Scan on tag tg
Planning Time: 0.921 ms
Execution Time: 0.242 ms

Споредба:

Метрика	Пред индекси	По индекси
Planning Time	0.733 ms	0.921 ms
Execution Time	0.296 ms	0.242 ms
Тип на скенирање	Seq Scan	Seq Scan
Дали новите индекси се користат	Не	Не

Заклучок: Времето на извршување се намали од 0.296 ms на 0.242 ms, што претставува намалување од приближно 18.2%. Сепак, execution plan-от и по додавање на индексите користи Seq Scan над сите релевантни табели, а не Index Scan или Bitmap Index Scan. Затоа подобрувањето не може да се припише директно на индексите. Најверојатната причина е малата количина на податоци и фактот што сите блокови се веќе во shared buffers. Индексите остануваат логични за идно зголемување на бројот на трансакции, тагови и breakdown записи.

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

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

Анализиран 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;

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

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);

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

Limit  (cost=3.85..3.86 rows=5 width=40) (actual time=0.110..0.114 rows=3 loops=1)
Buffers: shared hit=3
->  Sort  (cost=3.85..3.86 rows=5 width=40) (actual time=0.108..0.111 rows=3 loops=1)
Sort Key: (COALESCE(sum(tb.spent_amount), '0'::numeric)) DESC
Sort Method: quicksort  Memory: 25kB
->  HashAggregate  (cost=3.73..3.79 rows=5 width=40) (actual time=0.097..0.101 rows=3 loops=1)
Group Key: ta.account_name
->  Hash Join  (cost=2.47..3.67 rows=11 width=12) (actual time=0.059..0.069 rows=11 loops=1)
Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
->  Hash Join  (cost=1.36..2.51 rows=11 width=8) (actual time=0.032..0.038 rows=11 loops=1)
Hash Cond: (tb.transaction_id = t.transaction_id)
->  Seq Scan on transaction_breakdown tb
->  Hash
->  Seq Scan on transaction t
Filter: (date >= (now() - '1 year'::interval))
->  Hash
->  Seq Scan on transaction_account ta
Planning Time: 0.403 ms
Execution Time: 0.191 ms

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

Limit  (cost=3.85..3.86 rows=5 width=40) (actual time=0.100..0.104 rows=3 loops=1)
Buffers: shared hit=3
->  Sort  (cost=3.85..3.86 rows=5 width=40) (actual time=0.098..0.101 rows=3 loops=1)
Sort Key: (COALESCE(sum(tb.spent_amount), '0'::numeric)) DESC
Sort Method: quicksort  Memory: 25kB
->  HashAggregate  (cost=3.73..3.79 rows=5 width=40) (actual time=0.086..0.090 rows=3 loops=1)
Group Key: ta.account_name
->  Hash Join  (cost=2.47..3.67 rows=11 width=12) (actual time=0.060..0.070 rows=11 loops=1)
Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
->  Hash Join  (cost=1.36..2.51 rows=11 width=8) (actual time=0.030..0.036 rows=11 loops=1)
Hash Cond: (tb.transaction_id = t.transaction_id)
->  Seq Scan on transaction_breakdown tb
->  Hash
->  Seq Scan on transaction t
Filter: (date >= (now() - '1 year'::interval))
->  Hash
->  Seq Scan on transaction_account ta
Planning Time: 0.451 ms
Execution Time: 0.166 ms

Споредба:

Метрика	Пред индекси	По индекси
Planning Time	0.403 ms	0.451 ms
Execution Time	0.191 ms	0.166 ms
Тип на скенирање	Seq Scan	Seq Scan
Дали новите индекси се користат	Не	Не

Заклучок: Времето на извршување се намали од 0.191 ms на 0.166 ms, што претставува намалување од приближно 13.1%. Сепак, планот за извршување не користи ниту еден од новите индекси. PostgreSQL продолжува со Seq Scan на transaction, transaction_breakdown и transaction_account, затоа што табелите имаат многу мал број редови и читањето на целата табела е поевтино од пристап преку индекс. Поради тоа, малото подобрување не се смета како директен ефект од индексите. Индексот на transaction.date сепак е оправдан за идно зголемување на бројот на трансакции, бидејќи извештајот филтрира според временски период.

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

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

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

Анализиран 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 = 1
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;

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

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);

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

Sort  (cost=4.16..4.17 rows=3 width=136) (actual time=0.141..0.144 rows=3 loops=1)
Sort Key: ta.account_name
Sort Method: quicksort  Memory: 25kB
Buffers: shared hit=3
->  HashAggregate  (cost=4.08..4.14 rows=3 width=136) (actual time=0.126..0.131 rows=3 loops=1)
Group Key: ta.account_name
->  Hash Join  (cost=2.35..3.85 rows=7 width=20) (actual time=0.070..0.089 rows=11 loops=1)
Hash Cond: (t.transaction_id = tb.transaction_id)
->  Seq Scan on transaction t
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)))
->  Hash
->  Hash Join
Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
->  Seq Scan on transaction_breakdown tb
->  Hash
->  Seq Scan on transaction_account ta
Filter: (user_id = 1)
Rows Removed by Filter: 2
Planning Time: 0.402 ms
Execution Time: 0.242 ms

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

Sort  (cost=4.16..4.17 rows=3 width=136) (actual time=0.131..0.135 rows=3 loops=1)
Sort Key: ta.account_name
Sort Method: quicksort  Memory: 25kB
Buffers: shared hit=3
->  HashAggregate  (cost=4.08..4.14 rows=3 width=136) (actual time=0.117..0.122 rows=3 loops=1)
Group Key: ta.account_name
->  Hash Join  (cost=2.35..3.85 rows=7 width=20) (actual time=0.063..0.082 rows=11 loops=1)
Hash Cond: (t.transaction_id = tb.transaction_id)
->  Seq Scan on transaction t
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)))
->  Hash
->  Hash Join
Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
->  Seq Scan on transaction_breakdown tb
->  Hash
->  Seq Scan on transaction_account ta
Filter: (user_id = 1)
Rows Removed by Filter: 2
Planning Time: 0.500 ms
Execution Time: 0.212 ms

Споредба:

Метрика	Пред индекси	По индекси
Planning Time	0.402 ms	0.500 ms
Execution Time	0.242 ms	0.212 ms
Тип на скенирање	Seq Scan	Seq Scan
Дали новите индекси се користат	Не	Не

Заклучок: Времето на извршување се намали од 0.242 ms на 0.212 ms, што претставува намалување од приближно 12.4%. Сепак, планот останува ист во однос на пристапот до табелите: PostgreSQL користи Seq Scan, а не новите индекси. Причината е малата количина на податоци: transaction има 12 редови, transaction_breakdown има 11 редови, а transaction_account има 5 редови. За ваква големина, секвенцијалното читање е поевтино од пристап преку индекс. Индексите сепак се соодветни за идно зголемување на базата, особено затоа што извештајот филтрира по user_id и по временски период.

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

Во сите три сценарија, PostgreSQL продолжи да користи Seq Scan и по додавање на предложените индекси. Тоа значи дека индексите не беа реално искористени во моменталниот execution plan.

Ова однесување е очекувано поради малата количина на податоци во тест базата. Во моментот на тестирањето, најголемите релевантни табели имаат само 12 трансакции, 11 breakdown записи и 16 tag-assignment записи. За вакви табели, PostgreSQL правилно проценува дека целосно скенирање на табелата е поевтино од користење индекс.

Иако execution time се намали во сите три сценарија, тоа не може директно да се припише на индексите, бидејќи execution plan-от не покажува Index Scan, Bitmap Index Scan или Index Only Scan. Малите разлики во времето најверојатно се резултат на кеширање, повторно користење на податоци во shared buffers и нормална варијација при извршување.

Сепак, предложените индекси остануваат оправдани како подготовка за поголема количина на податоци. Со раст на бројот на корисници, трансакции, сметки, тагови и 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 ќе овозможат поефикасно филтрирање и поврзување на податоците во аналитичките извештаи.

3. Интегритет и конзистентност

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

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 сервер обезбеден од надворешна инфраструктура. Конфигурацијата на серверот, резервните копии и механизмите за обновување на податоците се надвор од опсегот на самата апликација и зависат од околината во која е поставена базата на податоци.