Formowanie tabel

Jako żółtodziób w temacie baz danych mam kilka pytań.

1.) Jakie są wady/zalety poszczególnych rozwiązań takiego przykładowego problemu(proszę nie zwracać uwagi na ewentualne błędy składniowe:P)
a.) wariant A:
CREATE TABLE general_data(id integer primary key,value integer,factor real,changeset_id integer references changesets(id));
CREATE TABLE changesets(id integer primary key, author character(32), timestamp integer);

b.) wariant B:
CREATE TABLE author_jasio_data(id integer primary key, value integer, factor real);
CREATE TABLE author_jasio_changesets(id integer primary key, timestamp integer);

W wariancie 'A' wszystkie dane są w jednej tabeli, w wariancie drugim każdy 'twórca' ma własną tabelę. Czy ma to jakiś wpływ na wydajność/szybkość/zrównoleglanie/wielowątkowość zapytań?

2.) Czy jest możliwość ustawienia poziomu dostępu (tylko odczyt/tylko zapis/brak podglądu) dla danego użytkownika dla konkretnych wierszy danej tabeli? Np. użytkownik XXX może edytować tylko te wiersze w których wartość kolumny YYY jest równa lub większa od ZZZ.

3.) Jakie materiały polecilibyście do nauki PostgreSQL? (wyłączając wszelkie 'kursy na youtube')

4.) Jakie rozwiązanie preferujecie w takim przypadku:
a.) CREATE TABLE some_table(id integer primary key, multivalue integer[]);
b.) CREATE TABLE some_table(id integer primary key, count integer, first_index integer references some_table_valueslist(id));
CREATE TABLE some_table_valueslist(id integer primary key, value integer);

Czy array jako typ bezpośrednio w tabeli czy zestaw (ilość+indeks) i oddzielna tabela wspólna dla wszystkich rekordów z tabeli głównej?

PS. Mam nadzieję, że tym razem @olesio nie będzie na mnie krzyczał z powodu tagów ;)

tajny_agent napisał(a):

Jakie są wady/zalety poszczególnych rozwiązań takiego przykładowego problemu

 a.) wariant A:
    CREATE TABLE general_data(
      id integer primary key,
      value integer,
      factor real,
      changeset_id integer references changesets(id)
    );
    CREATE TABLE changesets(
       id integer primary key, 
       author character(32), 
       timestamp integer
    );

  b.) wariant B:
    CREATE TABLE author_jasio_data(
       id integer primary key, 
       value integer, 
       factor real
    );
    CREATE TABLE author_jasio_changesets(
       id integer primary key, 
       timestamp integer
    );

W wariancie 'A' wszystkie dane są w jednej tabeli, w wariancie drugim każdy 'twórca' ma własną tabelę. Czy ma to jakiś wpływ na wydajność/szybkość/zrównoleglanie/wielowątkowość zapytań?

Oczywiście że to ma wpływ na wydajność. Jaki wpływ? Byś musiał doprecyzować jeszcze z jakieś 15-30 szczegółów, aby dało się odpowiedzieć na to pytanie:

• jaki jest statystyczny rozkład zapisów w czasie?
• jaki jest statystyczny rozkład zapisów do poszczególnych twórców, głupi, aczkolwiek wymonwy przykład: 99% zapisów może być dla jasia.
• czy będziesz łączył w odczytach dużą ilość autorów?
• jakie filtry i jakie złączenai będą w zapytaniach?
• czy będziesz stosował replikację?
• jaką masz funkcję kosztu czasu zapytania? - zwykle nie jest liniowa.
• czy tabelki będą w różnych nośnikach?
• w postgresie jest partycjonowanie - może to jakoś ułatwia postgresowi optymalizację zapytań w takich przypadkach?

Generalnie, dlaczego pytasz o wydajność?

O partycjonowaniu tam coś napisali:
https://edu.pjwstk.edu.pl/wiki/e2c3275d0e1a4bc0da360dd225d74a45/Default.aspx?Page=Partycjonowanie%20w%20PostgreSQL&NS=&AspxAutoDetectCookieSupport=1

tajny_agent napisał(a):

2.) Czy jest możliwość ustawienia poziomu dostępu (tylko odczyt/tylko zapis/brak podglądu) dla danego użytkownika dla konkretnych wierszy danej tabeli? Np. użytkownik XXX może edytować tylko te wiersze w których wartość kolumny YYY jest równa lub większa od ZZZ.

O ile wiem, to jest, samemu nigdy nie korzystałem z tego mechanizmu w postgresie, wolę po stronie aplikacji załatwić uprawnienia.

tajny_agent napisał(a):

4.) Jakie rozwiązanie preferujecie w takim przypadku:
a.) CREATE TABLE some_table(id integer primary key, multivalue integer[]);
b.) CREATE TABLE some_table(id integer primary key, count integer, first_index integer references some_table_valueslist(id));
CREATE TABLE some_table_valueslist(id integer primary key, value integer);

Czy array jako typ bezpośrednio w tabeli czy zestaw (ilość+indeks) i oddzielna tabela wspólna dla wszystkich rekordów z tabeli głównej?

Nie opisaleś przypadku. Jakich problemów z wydajnością się obawiasz? Baza nie wyrobi z zapisami, z odczytami? Jeden odczyt trwa za długo, czy sumaryczyn czas odczytów trwa za długo?

P.S.
Zadaj tak pytanie:
Mam taką strukturę bazy, takie i takie dane, takie i takie zapytania zajmują tyle i tyle czasu, a maksymalnie powinny zajmować tyle czasu. Co mam zrobić?

artur_bredzki napisał(a):

Oczywiście że to ma wpływ na wydajność. Jaki wpływ? Byś musiał doprecyzować jeszcze z jakieś 15-30 szczegółów, aby dało się odpowiedzieć na to pytanie:

• jaki jest statystyczny rozkład zapisów w czasie?
• jaki jest statystyczny rozkład zapisów do poszczególnych twórców, głupi, aczkolwiek wymonwy przykład: 99% zapisów może być dla jasia.
• czy będziesz łączył w odczytach dużą ilość autorów?
• jakie filtry i jakie złączenai będą w zapytaniach?
• czy będziesz stosował replikację?
• jaką masz funkcję kosztu czasu zapytania? - zwykle nie jest liniowa.
• czy tabelki będą w różnych nośnikach?
• w postgresie jest partycjonowanie - może to jakoś ułatwia postgresowi optymalizację zapytań w takich przypadkach?

Generalnie, dlaczego pytasz o wydajność?

O partycjonowaniu tam coś napisali:
https://edu.pjwstk.edu.pl/wiki/e2c3275d0e1a4bc0da360dd225d74a45/Default.aspx?Page=Partycjonowanie%20w%20PostgreSQL&NS=&AspxAutoDetectCookieSupport=1

Głównie chodzi mi o opinię osób siedzących w temacie baz danych, który sposób preferują i dlaczego, jakie napotkali trudności/ułatwienia przy danym rozwiązaniu. Wydajność to wątek poboczny(oczywiście fajnie jak coś śmiga bez mulenia).

artur_bredzki napisał(a):

Nie opisaleś przypadku. Jakich problemów z wydajnością się obawiasz? Baza nie wyrobi z zapisami, z odczytami? Jeden odczyt trwa za długo, czy sumaryczyn czas odczytów trwa za długo?

Jak wyżej. Chodzi mi o doświadczenia, przykłady z życia wzięte. Jak coś jest mało wydajne to zawsze można próbować to zmienić. Wiem, że nie ma złotych środków. Zanim jednak wybiorę sobie któryś ze sposobów to chciałbym przeczytać opinię wyjadaczy ;)

artur_bredzki napisał(a):

P.S.
Zadaj tak pytanie:
Mam taką strukturę bazy, takie i takie dane, takie i takie zapytania zajmują tyle i tyle czasu, a maksymalnie powinny zajmować tyle czasu. Co mam zrobić?

Nie mam struktury, nie mam danych, nie mam zapytań. Do baz dopiero się przymierzam i dlatego szukam, czytam i pytam w celu odpowiedniego nakierowania mózgownicy.

tajny_agent napisał(a):

Jako żółtodziób w temacie baz danych mam kilka pytań.

1.) Jakie są wady/zalety poszczególnych rozwiązań takiego przykładowego problemu(proszę nie zwracać uwagi na ewentualne błędy składniowe:P)
a.) wariant A:
CREATE TABLE general_data(id integer primary key,value integer,factor real,changeset_id integer references changesets(id));
CREATE TABLE changesets(id integer primary key, author character(32), timestamp integer);

b.) wariant B:
CREATE TABLE author_jasio_data(id integer primary key, value integer, factor real);
CREATE TABLE author_jasio_changesets(id integer primary key, timestamp integer);

W wariancie 'A' wszystkie dane są w jednej tabeli, w wariancie drugim każdy 'twórca' ma własną tabelę. Czy ma to jakiś wpływ na wydajność/szybkość/zrównoleglanie/wielowątkowość zapytań?

Unikam jak ognia tego typu kombinacji, choć w post są dozwolone na partycjonowanie tabel.
Ale np. zrobienie analizy porównawczej kto ile razy coś robił, to dla wariantu B zaczyna być problemem.
Nie mówiąc już o tym, że po dodaniu usera trzeba tę tabelę znormalizowaną zmieniać...

tajny_agent napisał(a):

2.) Czy jest możliwość ustawienia poziomu dostępu (tylko odczyt/tylko zapis/brak podglądu) dla danego użytkownika dla konkretnych wierszy danej tabeli? Np. użytkownik XXX może edytować tylko te wiersze w których wartość kolumny YYY jest równa lub większa od ZZZ.

Można nadac uprawnienia na poziomie VIEW i dać uprawnienia tylko do VIEW.
Np. CREATE VIEW v_tabela AS SELECT * FROM tabela WHERE user=current_user AND ....

tajny_agent napisał(a):

3.) Jakie materiały polecilibyście do nauki PostgreSQL? (wyłączając wszelkie 'kursy na youtube')

4.) Jakie rozwiązanie preferujecie w takim przypadku:
a.) CREATE TABLE some_table(id integer primary key, multivalue integer[]);
b.) CREATE TABLE some_table(id integer primary key, count integer, first_index integer references some_table_valueslist(id));
CREATE TABLE some_table_valueslist(id integer primary key, value integer);

Czy array jako typ bezpośrednio w tabeli czy zestaw (ilość+indeks) i oddzielna tabela wspólna dla wszystkich rekordów z tabeli głównej?

postgreSQL bardzo zgrabnie działa z tablicami. Możesz ich użyć, gdy spełnione będą dwa warunki:

• nie zależy ci na kolejności elementów (ciężko to się sortuje, można ale ciężko)
• nie będziesz za często zmieniał dużych tablic.

tajny_agent napisał(a):

Głównie chodzi mi o opinię osób siedzących w temacie baz danych, który sposób preferują i dlaczego, jakie napotkali trudności/ułatwienia przy danym rozwiązaniu. Wydajność to wątek poboczny(oczywiście fajnie jak coś śmiga bez mulenia).

Osobiście nie używałem nigdy. W tym linku co wkleiłem trochę wyjaśnili. Cytuję:
[
Zalety:
czytelna i przejrzysta architektura
łatwość definiowania przy użyciu niemal standardowych operacji DDL, nie wprowadza dodatkowej składni
możliwość rozdzielnej optymalizacji każdej partycji z osobna
Wady:
odkryta struktura może być przypadkowo zmodyfikowana lub usunięta
ilość partycji musi być stała (możliwe do ominięcia przez stosowanie dynamicznego SQLa i tworzenia partycji w locie, jednak może to być kosztowne)
konieczność duplikacji logiki partycjonowania w ograniczeniach i triggerze
brak możliwości aktualizacji wartości w kolumnach partycjonujących
narzędzia działające na tabelach (jak np. vaacum) nie uwzględniają partycjonowania stworzonego w taki sposób
optymalizator działa jedynie dla wartości wyliczanych w czasie kompilacji (np. stałych), wartości runtime nie spowodują zastosowania optymalizacji
duża ilość partycji wydłuża czas generowania planu wykonywania zapytania
]

Przy czym ta optymalizacja to chyba jest zauważalna dopiero gdy każda tabela leży na innym nośniku.

Marcin.Miga napisał(a):

postgreSQL bardzo zgrabnie działa z tablicami. Możesz ich użyć, gdy spełnione będą dwa warunki:

• nie zależy ci na kolejności elementów (ciężko to się sortuje, można ale ciężko)
• nie będziesz za często zmieniał dużych tablic.

Generalnie zgadzam się, że postgres zgrabnie działa z tablicami. Mało tego, do tablic jest specjalny zestaw operatorów i można je indeksować. Miałem z tym bardzo dobre efekty. Pytanie tylko, czy tak prosty przypadek użycia jest częto potrzebny w praktyce?

W praktyce częściej są przydatne indeksy na pola heterogenicznie. Gdy ja zakładałem indeksy heterogeniczne, np. na array i dwa inty, to już nie widziałem żadnego przyspieszenia. Może ja coś źle robiłem, ale raczej nie.