Други теми - перформанси и безбедност

Мерна поставеност

Анализата на перформанси не може да се направи врз официјалните тест податоци од data_load.sql, бидејќи тие се премногу мали - 8 корисници и 20 предмети. Врз табела со 20 редови PostgreSQL речиси секогаш избира секвенцијално читање дури и кога постои совршен индекс, затоа што читањето на 20 редови е поевтино од пребарување низ индекс. Мерењето врз такви податоци не би покажало ништо.

Затоа е креирана посебна шема perf со иста структура како project, но со генерирани податоци од реален обем:

Табела	Редови
users	4.120 (4.000 студенти, 120 професори)
subjects	300
active_semesters	20
major_subjects	600
professour_subjects	6.000
enrolled_semesters	16.000
semesters_subjects	80.000
passed_subjects	56.000
payment	16.000

Упатството за Фаза 2 дозволува креирање дополнителни шеми за експерименти и тестови, па официјалната шема project останува недопрена.

Сите мерења се направени со EXPLAIN (ANALYZE, BUFFERS) и со ANALYZE извршен пред секое мерење, за статистиките на планерот да бидат свежи.

Забелешка за околината: мерењата се извршени врз локална инстанца PostgreSQL 18, а не врз доделената проектна база (PostgreSQL 17.11). Причината е што проектната база е достапна само преку SSH тунел кој во текот на работата постојано се прекинуваше, а мерење на времиња низ тунел и онака би ја мерело мрежата наместо базата. Плановите за извршување се споредливи, бидејќи истата шема, истите барања и истите податоци се употребени во двата случаја.

SQL перформанси

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

PostgreSQL автоматски креира индекс за примарен клуч и за секое UNIQUE ограничување, но никогаш не креира индекс за колоната која е надворешен клуч. Освен тоа, сложен индекс помага само на барања кои ја користат неговата прва колона.

Според тоа, во шемата недостасуваат индекси на следните места:

CREATE INDEX ix_semesters_subjects_subject   ON semesters_subjects (subjects_id);
CREATE INDEX ix_semesters_subjects_professor ON semesters_subjects (professor_id);
CREATE INDEX ix_enrolled_semesters_semester  ON enrolled_semesters (semester_id);
CREATE INDEX ix_enrolled_semesters_major     ON enrolled_semesters (major_id);
CREATE INDEX ix_payment_enrollment           ON payment (enrollment_id);
CREATE INDEX ix_major_subjects_subject       ON major_subjects (subject_id);
CREATE INDEX ix_professour_subjects_semester ON professour_subjects (active_semester_id, subject_id);
CREATE INDEX ix_user_documents_document      ON user_documents (document_id);
CREATE INDEX ix_token_user                   ON token (user_id);
CREATE INDEX ix_passed_subjects_enrolled_grade ON passed_subjects (enrolled_id, grade);

Колоните semesters_subjects.enrolled_semesters_id, enrolled_semesters.user_id, passed_subjects.enrolled_id, high_school.user_id и contact.user_id не се на списокот, бидејќи веќе се покриени - тие се првата колона на постоечко UNIQUE ограничување.

Кои прашалници се анализирани

Анализирани се седум прашалници. Шест од нив се точно извештаите од Фаза 6 (види AdvancedReports, скрипта reports.sql​), а седмиот е секојдневно барање на апликацијата, додаден за споредба.

#	Прашалник	Извор	Рутина во reports.sql
1	Проодност по предмет и семестар	Фаза 6, Извештај 1	rep_subject_pass_rate()
2	Досие на сите студенти	Фаза 6, Извештај 2	rep_student_dossier()
3	Најуспешен студент по програма	Фаза 6, Извештај 3	rep_top_student_per_major()
4	Најоптоварен професор по семестар	Фаза 6, Извештај 4	rep_busiest_professor()
5	Промена на активноста меѓу семестри	Фаза 6, Извештај 5	rep_semester_growth()
6	Досие на еден студент	Фаза 6, Извештај 2 со аргумент	rep_student_dossier(2500)
7	Дневник на професор	Апликација (не е од Фаза 6)	—

Извештај 2 е мерен двапати: еднаш без аргумент, кога враќа секој студент, и еднаш со p_user_id = 2500, кога враќа еден. Истата рутина со два различни аргумента покажува дека одлучувачки е селективноста, а не самото барање.

Точните наредби со кои се мерени се во queries.sql​.

Методологија на мерењето

За секој прашалник се снимени планови за извршување двапати:

1. врз шема без дополнителни индекси — датотеки before_*.txt
2. по извршување на indexes.sql​ и ANALYZE — датотеки after_*.txt

Сите четиринаесет излези се приложени кон оваа страна во целост. Подолу е даден извадок од секој план — точно оние јазли каде се гледа дали индексот се употребува или не.

Збирен преглед

#	Прашалник	Пред	По	Планот се промени?
1	rep_subject_pass_rate	146,0 ms	156,0 ms	Не - идентичен план
2	rep_student_dossier()	188,5 ms	179,4 ms	Не - идентичен план
3	rep_top_student_per_major	107,3 ms	94,0 ms	Не - идентичен план
4	rep_busiest_professor	121,9 ms	124,6 ms	Не - идентичен план
5	rep_semester_growth	124,0 ms	138,1 ms	Не - идентичен план
6	rep_student_dossier(2500)	2,19 ms	1,07 ms	Да - Seq Scan ⇒ Index Scan
7	Дневник на професор	16,3 ms	7,6 ms	Да - Seq Scan ⇒ Bitmap Index Scan

Последната колона е докажана подолу за секој прашалник посебно.

Прашалник 1 - проодност по предмет (Фаза 6, Извештај 1)

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM rep_subject_pass_rate();

План ПРЕД индексите (before_rep_subject_pass_rate.txt​):

->  Seq Scan on semesters_subjects ss  (rows=80000) (actual time=0.007..3.658 rows=80000.00)
->  Seq Scan on passed_subjects ps     (rows=56000) (actual time=0.011..4.648 rows=56000.00)
->  Seq Scan on enrolled_semesters es  (rows=16000) (actual time=0.011..2.191 rows=16000.00)
Execution Time: 146.018 ms

План ПО индексите (after_rep_subject_pass_rate.txt​):

->  Seq Scan on semesters_subjects ss  (rows=80000) (actual time=0.007..4.131 rows=80000.00)
->  Seq Scan on passed_subjects ps     (rows=56000) (actual time=0.014..5.114 rows=56000.00)
->  Seq Scan on enrolled_semesters es  (rows=16000) (actual time=0.011..2.333 rows=16000.00)
Execution Time: 156.015 ms

Заклучок: планот е идентичен — во него не се појавува ниту еден од новите индекси. Барањето агрегира врз целите табели, па планерот точно заклучува дека секвенцијалното читање е поевтино.

Прашалник 2 - досие на сите студенти (Фаза 6, Извештај 2)

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM rep_student_dossier();

План ПРЕД индексите (before_rep_student_dossier.txt​):

->  Seq Scan on users u                (rows=4000)
->  Seq Scan on semesters_subjects ss  (rows=80000) (actual time=0.032..5.444)
->  Seq Scan on passed_subjects ps_1   (rows=56000) (actual time=0.042..6.293)
->  Seq Scan on payment p              (rows=16000) (actual time=0.028..0.794)
->  Seq Scan on user_documents ud      (rows=4000)
Execution Time: 188.547 ms

План ПО индексите (after_rep_student_dossier.txt​):

->  Seq Scan on users u                (rows=4000)
->  Seq Scan on semesters_subjects ss  (rows=80000) (actual time=0.013..4.720)
->  Seq Scan on passed_subjects ps_1   (rows=56000) (actual time=0.012..5.112)
->  Seq Scan on payment p              (rows=16000) (actual time=0.028..0.788)
->  Seq Scan on user_documents ud      (rows=4000)
Execution Time: 179.370 ms

Заклучок: идентичен план. Извештајот бара досие за сите 4.000 студенти, па ix_payment_enrollment не помага — сите 16.000 плаќања и онака се потребни. Спореди со Прашалник 6, каде истата рутина го користи тој индекс.

Прашалник 3 - најуспешен студент по програма (Фаза 6, Извештај 3)

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM rep_top_student_per_major();

План ПРЕД (before_rep_top_student_per_major.txt​) и ПО (after_rep_top_student_per_major.txt​) — идентични јазли:

->  Seq Scan on semesters_subjects ss       (rows=80000)
->  Seq Scan on passed_subjects ps          (rows=56000)
->  Seq Scan on enrolled_semesters es       (rows=16000)
->  Index Scan using users_pkey on users u  (loops=6)

Пред: Execution Time: 107.303 ms
По:   Execution Time:  93.983 ms

Заклучок: единствениот Index Scan е по users_pkey, кој постоеше и пред измената. Ниту еден нов индекс не е употребен.

Прашалник 4 - најоптоварен професор (Фаза 6, Извештај 4)

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM rep_busiest_professor();

План ПРЕД (before_rep_busiest_professor.txt​) и ПО (after_rep_busiest_professor.txt​):

->  Seq Scan on semesters_subjects ss  (rows=80000)    <- исто пред и по
->  Seq Scan on passed_subjects ps     (rows=56000)
->  Seq Scan on enrolled_semesters es  (rows=16000)

Пред: Execution Time: 121.914 ms
По:   Execution Time: 124.648 ms

Заклучок: иако постои ix_semesters_subjects_professor, планерот не го зема, бидејќи извештајот групира по сите професори, не бара еден. Истиот индекс е одлучувачки кај Прашалник 7, каде има услов за еден професор.

Прашалник 5 - промена на активноста (Фаза 6, Извештај 5)

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM rep_semester_growth();

План ПРЕД (before_rep_semester_growth.txt​) и ПО (after_rep_semester_growth.txt​):

->  Seq Scan on semesters_subjects ss  (rows=80000)    <- исто пред и по
->  Seq Scan on passed_subjects ps     (rows=56000)
->  Seq Scan on enrolled_semesters es  (rows=16000)
->  Seq Scan on active_semesters a     (rows=20)

Пред: Execution Time: 124.040 ms
По:   Execution Time: 138.077 ms

Заклучок: ix_enrolled_semesters_semester не е употребен, иако извештајот групира по semester_id — пак затоа што ги чита сите 20 семестри.

Прашалник 6 - досие на еден студент (Фаза 6, Извештај 2 со аргумент)

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM rep_student_dossier(2500);

План ПРЕД индексот (before_dossier_one.txt​) — целата табела payment се чита:

->  Seq Scan on payment p  (cost=0.00..247.00 rows=16000 width=8)
                           (actual time=0.011..0.624 rows=16000.00 loops=1)
Execution Time: 2.190 ms

План ПО индексот (after_dossier_one.txt​) — истата табела се чита преку индекс:

->  Index Scan using ix_payment_enrollment on payment p
                           (cost=0.29..8.30 rows=1 width=8)
                           (actual time=0.090..0.090 rows=1.00 loops=4)
      Index Cond: (enrollment_id = es_3.id)
Execution Time: 1.067 ms

Заклучок: индексот ix_payment_enrollment е докажано употребен — неговото име стои во јазлот. Наместо 16.000 реда се читаат 4 (по еден за секој семестар на студентот), а времето паѓа од 2,19 на 1,07 ms.

Ова е најважната споредба во целата анализа: Прашалници 2 и 6 се истата рутина од Фаза 6. Разликата е само во аргументот, а тоа е доволно планот да се промени од Seq Scan во Index Scan.

Прашалник 7 - дневник на професор (барање на апликацијата)

SELECT u.surname, u.name, s.name AS subject, ss.signature, ps.grade
FROM semesters_subjects ss
JOIN enrolled_semesters es ON es.id = ss.enrolled_semesters_id
JOIN users u              ON u.id  = es.user_id
JOIN subjects s           ON s.id  = ss.subjects_id
LEFT JOIN passed_subjects ps ON ps.enrolled_id = ss.id
WHERE ss.professor_id = 47
ORDER BY s.name, u.surname;

План ПРЕД индексот (before_gradebook.txt​):

->  Seq Scan on semesters_subjects ss  (cost=0.00..1510.00 rows=664 width=13)
                                       (actual time=0.038..6.061 rows=684.00 loops=1)
      Filter: (professor_id = 47)
      Rows Removed by Filter: 79316
Execution Time: 16.332 ms

План ПО индексот (after_gradebook.txt​):

->  Bitmap Heap Scan on semesters_subjects ss  (cost=9.45..555.06 rows=666 width=13)
                                               (actual time=0.108..1.480 rows=684.00 loops=1)
      Recheck Cond: (professor_id = 47)
      ->  Bitmap Index Scan on ix_semesters_subjects_professor
                                               (cost=0.00..9.29 rows=666 width=0)
                                               (actual time=0.070..0.070 rows=684.00 loops=1)
            Index Cond: (professor_id = 47)
Execution Time: 7.615 ms

Заклучок: индексот ix_semesters_subjects_professor е докажано употребен. Пред индексот базата читаше 80.000 реда и отфрлаше 79.316 (Rows Removed by Filter); по индексот оди директно до 684-те реда. Цената паѓа од 1510 на 555, а времето од 16,3 на 7,6 ms.

Зошто петте агрегатни извештаи не станаа побрзи

Ова не е неуспех на индексите, туку очекувано однесување. Прашалници 1-5 се агрегатни - тие читаат сѐ од semesters_subjects, passed_subjects и enrolled_semesters, за да пресметаат проодност, просек или ранг. Кога барањето и онака мора да ги прочита сите редови, секвенцијалното читање е побрзо од пребарување низ индекс, бидејќи чита последователни блокови од дискот наместо да скока по индексот и потоа по табелата.

Дека разликите во времињата се шум, а не забавување, се гледа од повторените мерења на истото барање со исти индекси:

run 1: 164,0 ms
run 2: 151,5 ms
run 3: 162,7 ms
run 4: 179,9 ms

Распонот од околу 28 ms меѓу четири последователни извршувања е поголем од сите разлики „пред/по“ во збирната табела. Затоа единствениот сигурен доказ дали индексот се употребува е самиот план, а не измереното време — и токму затоа погоре е даден планот за секој прашалник посебно.

Дали индексите навистина се употребени

Бројачите од pg_stat_user_indexes по сите мерења:

Индекс	Големина	Пати употребен
ix_passed_subjects_enrolled_grade	1248 kB	1410
ix_payment_enrollment	368 kB	8
ix_semesters_subjects_subject	584 kB	8
ix_semesters_subjects_professor	576 kB	1
ix_enrolled_semesters_major	128 kB	0
ix_enrolled_semesters_semester	136 kB	0
ix_major_subjects_subject	32 kB	0
ix_professour_subjects_semester	152 kB	0
ix_token_user	8 kB	0
ix_user_documents_document	48 kB	0

Од десет предложени индекси, четири се навистина употребени, а шест ниту еднаш. Тоа е важен резултат: индекс кој не се употребува не е бесплатен - зазема простор и го забавува секој INSERT, UPDATE и DELETE врз таа табела, бидејќи и индексот мора да се ажурира.

Заклучок

• Индексите врз надворешни клучеви не ги забрзуваат агрегатните извештаи од Фаза 6, бидејќи тие и онака ја читаат целата табела. Секвенцијалното читање таму е правилниот избор на планерот.
• Истите индекси ги забрзуваат селективните барања - оние што ги користи апликацијата - околу два пати, со видлива промена на планот од Seq Scan во Index Scan.
• Бидејќи апликацијата работи со селективни барања (еден студент, еден професор), а извештаите се пуштаат ретко, индексите се исплатливи.
• Се задржуваат само четирите употребени индекси. Останатите шест се отстрануваат, со една резерва: индекс врз надворешен клуч помага и при бришење во родителската табела, па ix_enrolled_semesters_semester и ix_major_subjects_subject може да се задржат ако се предвидуваат такви операции.
• Мерењето врз 20 реда не докажува ништо. Секое тврдење за перформанси мора да се прави врз податоци со реален обем.

Безбедносни мерки

Во апликацијата

Спречување на SQL injection. Апликацијата воопшто не составува SQL од текст. Проверено врз целиот изворен код - нема ниту еден повик на FromSqlRaw, ExecuteSqlRaw, NpgsqlCommand ниту рачно поставен CommandText. Целиот пристап оди преку Entity Framework Core, кој секоја вредност ја испраќа како параметар. Единственото место со напишан SQL е проверката на врската:

.SqlQuery<string>($"SELECT version() AS \"Value\"")

Тоа е интерполиран стринг во C#, но EF Core таквите изрази ги претвора во параметризирани наредби, а не во спојување на текст - вредностите никогаш не влегуваат во телото на барањето.

Лозинки. Не се чуваат во читлива форма. При регистрација се хешираат со BCrypt, а при најава се споредуваат со Verify:

PasswordHash = BCrypt.Net.BCrypt.HashPassword(registerDto.Password)
BCrypt.Net.BCrypt.Verify(loginDto.Password, user.PasswordHash)

BCrypt носи сопствена сол и намерно е бавен, што го отежнува пробувањето лозинки.

Автентикација и авторизација. Пристапот е со JWT токен со краток рок; подолгата сесија се одржува со refresh токен кој се чува во табелата token и се поништува при одјава (is_valid = FALSE) наместо да се брише, за да остане трага. Улогата се чита од потпишаниот токен, не од барањето, па повикувачот не може да си ја додели.

Авторизацијата е во самото барање, не покрај него. Наместо прво да се провери правото, па потоа да се изврши барањето, условот е дел од наредбата:

INSERT INTO passed_subjects (enrolled_id, grade, date_passed)
SELECT ss.id, '9', now()
FROM semesters_subjects ss
JOIN enrolled_semesters es ON es.id = ss.enrolled_semesters_id
WHERE ss.professor_id = 4 AND ss.subjects_id = 2 AND es.user_id = 1;

Ако професорот не го предава тој предмет на тој студент, наредбата не менува ниту еден ред. Нема временски прозорец меѓу проверката и дејството.

Проверено со барања од туѓа улога: студент кон професорски краен точки враќа 403, професор кон административни 403, барање без токен 401, а професор кој се обидува да оцени предмет што не го предава добива одбивање без никаква измена во базата.

Во базата

Правила кои важат и при директен пристап. Проверките од Фаза 7 се тригери во базата, а не само во апликацијата: предметот мора да е во студиската програма, професорот мора да го предава тој семестар, предусловите мора да се положени, оценка не смее да има датум во иднина. Кој и да пишува во базата - апликацијата, скрипта или човек преку DBeaver - правилата важат.

Домени. Форматите се дел од типот на колоната, па невалидна е-пошта или ЕМБГ не може да влезе ниту преку директен INSERT.

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

Отворено прашање. Апликацијата се поврзува со корисникот кој е сопственик на шемата, а сопственикот ги заобиколува политиките за безбедност на ниво на редица, освен ако не се употреби FORCE ROW LEVEL SECURITY. Значи политиките во моментов се напишани и точни, но не се активни за апликацијата. Правилното решение е посебна улога со ограничени права:

CREATE ROLE iknow_app LOGIN PASSWORD '...';
GRANT USAGE ON SCHEMA project TO iknow_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA project TO iknow_app;
-- без DROP, без ALTER, без права врз други шеми

Ова не е спроведено, бидејќи доделениот кориснички налог на проектната база нема право да креира нови улоги.

Лозинки во конфигурација. Лозинката за базата не е во складиштето на кодот

• таа е во appsettings.Development.json, кој е во .gitignore, а во складиштето

постои само appsettings.Development.json.example со празно место.

Други развојни теми

Материјализиран преглед. Статистиката по предмети (Фаза 7) е пресметка врз целата база - точно оној вид агрегатно барање кое, како што покажа мерењето погоре, не може да се забрза со индекс. Затоа е материјализирана и се освежува еднаш дневно, со уникатен индекс кој овозможува освежување со CONCURRENTLY, односно без заклучување за читање.

Pool на конекции. Документиран во Фаза 8; тука е релевантен затоа што базата е зад SSH тунел, па секоја нова конекција е нов канал низ тунелот и отворањето е поскапо од вообичаеното.

Историјат

Верзија 1 - Прва верзија: мерење на перформансите на петте извештаи од Фаза 6 пред и по воведување индекси врз шема со 80.000 запишани предмети, анализа на плановите за извршување, проверка кои индекси навистина се употребуваат, и документирање на безбедносните мерки во апликацијата и во базата.

Верзија 2 - По забелешки: експлицитно е наведено дека шест од седумте анализирани прашалници се извештаите од Фаза 6, со име на рутината во reports.sql за секој. За секој прашалник е документиран планот за извршување пред и по додавањето на индексите, со извадок од јазлите во кои се гледа дали индексот се употребува. Додадена е скриптата queries.sql со точните мерени наредби.

Статус

Во тек