Как релационната сложност забавя таблата в реално време

Когато броят на операциите по четене от буферната памет (Shared Buffer Hits) надхвърли 100 000 за едно просто опресняване на таблото, вие сте ударили стената на мащабирането. Заявка, която преди е отнемала 100 милисекунди, вече изисква 1,5 секунди. Добавили сте индекси, настроили сте буферния пул, но подобренията са временни. При таблици с над 500 милиона реда, сложните връзки (joins), които някога са изглеждали елегантни, се превръщат в тежест за цялата система.

Според технически анализ на TigerData [1], основният проблем се корени в т.нар. „експлозия на съединенията“ (Join Explosion). В PostgreSQL и други релационни бази данни, нормализираните схеми фрагментират данните в множество таблици, което изисква огромни изчислителни ресурси за сглобяването им при всяка заявка.

Изображение: Svetni.me / Авторско изображение

Данъка върху метаданните

Релационните схеми работят отлично при малки пилотни проекти, но се затрудняват, когато таблата за анализ в реално време трябва да съединяват десет таблици стотици пъти в секунда върху милиарди редове. Всяко съединение принуждава базата данни да преминава през множество B-tree индекси и да зарежда разпръснати страници в паметта.

Решението е преместване на разходите за съединяване от момента на четене (read-time) към момента на запис (write-time). Вместо да сглобявате таблици при всяка заявка, вие „изравнявате“ данните чрез денормализация – предварително свързване на метаданните към суровите записи още при постъпването им в системата.

От релационен към плосък модел

Преходът към плосък модел означава преминаване от пасивен модел на съхранение към активен модел на обработка. Вместо лека фаза на поглъщане, последвана от скъпо четене, вие изпълнявате логиката на съединяване точно веднъж.

Съхраняването на резултата в една широка таблица позволява на базата данни да извършва едно сканиране на индекс вместо многостранно съединяване, което драстично намалява I/O натоварването.

Стъпка 1: Дефиниране на плоската структура

Започнете с изграждане на таблица, която включва метаданните като собствени колони:

CREATE TABLE readings_flattened (
   ts TIMESTAMPTZ NOT NULL,
   sensor_name TEXT,
   building_name TEXT,
   region TEXT,
   value DOUBLE PRECISION
);
CREATE INDEX idx_flattened_ts_region ON readings_flattened (ts DESC, region);

Стъпка 2: Пакетно мигриране

Преместването на милиард реда наведнъж може да блокира базата данни. Използвайте пакетен подход за миграция на историческите данни:

INSERT INTO readings_flattened (ts, sensor_name, building_name, region, value)
SELECT r.ts, s.sensor_name, l.building_name, l.region, r.value
FROM readings r
JOIN sensors s ON r.sensor_id = s.id
JOIN locations l ON s.location_id = l.id
WHERE r.ts > now() - interval '30 days';

Измерване на разликата

За да оцените ползите, използвайте метриката BUFFERS в плана на заявката, за да наблюдавате физическите входно-изходни операции.

EXPLAIN (ANALYZE, BUFFERS)
SELECT ts, sensor_name, building_name, value
FROM readings_flattened
WHERE ts > now() - interval '1 hour' AND region = 'North';

При правилно изпълнение ще забележите между 70% и 90% спад в Shared Hits. Това намаляване на броя докоснати блокове данни е основата, която позволява на едно табло да се мащабира до стотици едновременни потребители без претоварване на процесора.

Въпреки че денормализацията въвежда излишък (например при преименуване на сграда старата информация остава в историческите записи), при телеметрията и индустриалния интернет на нещата (IIoT) метаданните обикновено са статични. Огромната печалба в скоростта почти винаги надхвърля цената на рядкото обновяване на исторически данни [2].

Източници:

[1]: How Relational Complexity Crushes Real-Time Dashboards - TigerData
[2]: PostgreSQL as a Real-Time Analytics Database - TigerData