Dotaz: PgSQL: Efektivní opakované hledání záznamů, na které nevede cizí klíč

[Aleš Janda]

Chtěl bych se zeptat, jak nejlépe navrhnout databázi pro toto:

V tabulce mám řádky, které jsou výsledkem nějakého postupného bádání. Jak bádání pokračuje, můžou být některé řádky zastaralé a nahrazeny jinými řádky. Například mám řádky A, B a C a nějakým algoritmem řeknu „z řádků A a C vyplývá nějaká nová informace D“, a nahradím řádky A a C jedním řádkem D (nebo i více řádky). Z řádků B a D třeba vyplyne něco dalšího, tak je nahradím řádkem E.

No jo, ale občas si řeknu „tenhle algoritmus byl blbost“, smažu všechny řádky vyrobené tímto algoritmem, a měly by se smazat všechny řádky vyrobené tímto algoritmem, ale i všechny řádky, které vznikly na jejich základě. Například když smažu D, smaže se i E (a zase budou platné A, B a C).

Na tohle můžu použít cizí klíč (foreign key) s ON DELETE CASCADE.

No jo, ale jak efektivně hledat na aktuálními daty? Tedy nad daty, které nejsou referencovány jinými řádky (resp. nebyly překryty nějakým dalším algoritmem)? Jediné řešení, co jsem našel, je použít LEFT JOIN přes všechny řádky, ale to mi přijde strašně neefektivní; potřebuju jenom poslední verzi řádků (hodněkrát), a ty překryté jenom když se vracím k předchozím verzím (málokdy).

Je tedy nějaký způsob indexu „tento řádek je indexován jiným“? Nebo celá ta úvaha je nesmyslná a měl bych to udělat jinak?

Dělám v PostgreSQL.

Díky.

Odpovědi

[OldFrog {Ondra Nemecek}]

Tak předně tabulka může referencovat sama sebe a problém s tím není. A dále mi (letmým pohledem) přijde, že ta data chcete modelovat:

• buď jako strom (jeden záznam může mít více potomků)
• nebo jako revize (kdy jeden záznam má více revizí, z nichž platí ta s nejvyšším číslem)
• anebo jako nějaký obecný graf, pokud má být více záznamů nahrazeno jediným záznamem

Existují speciální grafové databáze, anebo k tomu můžete použít i sql. Záleží, jak hodně se nad těm daty bude operovat a kolik těch dat je. Hledejte něco jako "store graph data in sql database", "revisions model for data in sql database" nebo "graph database".

[Aleš Janda]

Díky. Spíš se k tomu blíží nejvíc ten obecný graf, ale acyklický: novější záznamy ukazují na ty starší, ale starší nemůžou odkazovat na novější.

Hledal jsem různé metody (třeba to číslo revize je super jednoduché, ale to nemůžu použít), ale moc jsem řešení zatím bohužel nenašel

Grafovou databázi použít asi můžu, ale PostgreSQL už mám na všechno ostatní, tak se mi nechce zavádět do projektu další závislosti (a další vrstvy komplexity).

[gnat]

Pokud to má být optimalizováno na čtení, tak bych novou a tudíž poslední platnou informaci nastavil jako parenta těch zdrojových řádků. Znamená to při insertu i update zdrojových řádků, ale při selectu pak čtu jen to, co má parent_id null, což je levná podmínka.

[Aleš Janda]

Jo, tohle je docela dobrý hack. To parent_id má tedy být spíše child_id, trochu potíž je v tom, že jeden záznam může vygenerovat více různých, takže ty vazby musí být v tabulce vedle (ale to asi musí být tak jako tak).

Není to optimální řešení z toho pohledu, že bych mohl napsat DELETE FROM … WHERE algoritmus = 'nefunguje' a ono to samo upravilo i ty sloupce child_id, ale tak lepší než nic

[Josef Kufner]

Pokud algoritmů není mnoho, mohl bys u každého záznamu mít bitmapu značící algoritmy, které daný řádek ovlivnily. Při odvození nového řádku bys provedl OR bitmap všech použitých řádků a přidal bit aktuálního algoritmu.

Pokud chceš indexovat, můžeš použít místo bitmapy samostatné indexované sloupečky. V takovém případě asi bude rozumné použít samostatnou tabulku s 1:1 relací na datovou tabulku.

[logik]

Pokud je "dětí" víc, tak si ukládej k rodičům pouze počet dětí (updatuj to třeba triggerem) a hledej ty s nulou dětí.