Mam aplikację (dane są przechowywane w PostgreSQL), w której większość pól w tabelach nie zawsze ma wartość NULL, ale schemat tych tabel nie wymusza tego. Na przykład spójrz na tę fałszywą tabelę:

CREATE TABLE "tbl" (
    "id" serial,
    "name" varchar(40),
    "num" int,
    "time" timestamp
    PRIMARY KEY ("id"),
    UNIQUE ("id")
);

Również name, num, timenie są wyraźnie zaznaczono NOT NULL, w rzeczywistości są, ponieważ egzekucja odbywa się na stronie aplikacji.

Mam wrażenie, że należy to zmienić, ale kontrapunktem jest to, że poziom aplikacji upewnia się, że wartości null nie pojawią się tutaj i nikt inny nie modyfikuje tabeli ręcznie.

Moje pytanie brzmi : jakie są zalety (wydajność, pamięć masowa, spójność, coś jeszcze) i wady (zakładając, że już sprawdziłem, że w tej chwili nie ma wartości zerowych, a z logiki biznesowej nie powinno być żadnych wartości zerowych) poprzez ustawienie wyraźne NOT NULLograniczenie?

Mamy dobry proces przeglądu kodu i dość dobrą dokumentację, więc możliwość, że jakaś nowa osoba popełni coś, co łamie to ograniczenie, nie jest tak naprawdę wystarczająca, aby uzasadnić zmianę.

To nie jest moja decyzja, dlatego właśnie szukam innych uzasadnień. Moim zdaniem, jeśli coś nie może być zerowe, a baza danych pozwala określić, że coś nie jest zerowe - zrób to. Zwłaszcza jeśli zmiana jest bardzo prosta.

Co się stanie, gdy pojawi się nowy programista i będzie musiał napisać aplikację na tej bazie danych? Nie wiedzą, że pole x musi być NOT NULL.

NOT NULLPowiedzmy, że inny program może założyć, że wszystkie pola x służą do wykonywania zliczeń, ale niektóre są teraz NULLspowodowane nowym programem, co prowadzi do niespójnych i trudnych do prześledzenia błędów.

IMHO zawsze najlepiej egzekwować zasady integralności danych tak blisko danych, jak to możliwe, tj. W bazie danych. W ten sposób nowe aplikacje i / lub programiści nie mogą zepsuć danych.

Programiści, aplikacje, języki i frameworki przychodzą i odchodzą. Dane i bazy danych zwykle się utrzymują. Baza danych to ostatnia linia obrony przed niespójnymi, potencjalnie błędnymi danymi.

Dokonaj maksymalnej stosowanie mechanizmów egzekwowania ograniczenia integralności bazy danych, nawet kosztem wydajności. Powolny system, który daje prawidłowe wyniki, jest nieskończenie lepszy od szybkiego, który nie działa poprawnie !

Jak już cytowali inni w komentarzach, dodanie NOT NULLdo specyfikacji tabeli może znacznie poprawić wydajność twoich zapytań (oprócz bardzo dobrych powodów metodologicznych podanych w innej odpowiedzi).

Powodem jest to, że optymalizator zapytań, wiedząc, że kolumna nie może mieć NULLwartości, może wykluczyć specjalne testy dla takich wartości, jak w przypadku NOT INvs. NOT EXISTSMożesz zobaczyć na przykład tego bloga , na którym pokazano, że niezadeklarowanie pola NOT NULL(gdy tabela zawiera zawsze wartości inne niż null) za pomocą określonego zapytania zwiększa czas wykonania o 500%. Wynik jest pokazany dla SQL Server, ale podobne zachowanie może występować w innych relacyjnych systemach DBMS, takich jak twoje (nie wspominając o tym, że twoja baza danych może być przeniesiona do innych systemów). Ogólną zasadą, którą można założyć, jest to, że gdy więcej informacji jest dostępnych dla optymalizatora zapytań, można stworzyć bardziej wydajne plany dostępu.

Implikacje dotyczące przestrzeni

Te konsekwencje kosmiczne mowa w tym poście przez @Erwin Brandstetter

Krótko mówiąc, zapiszesz jeden totalColumns - 8bit zaokrąglony w górę do najbliższego bajtu (lub MAXALIGN), jeśli twoja baza danych ma

1. Ponad 8 kolumn
2. WSZYSTKIE kolumny w tabeli toNOT NULL

Wpływ na wydajność

Jednak w tym poście na SE @ Erwin Brandstetter mówi

1. „Ustawienie NOT NULL nie ma żadnego wpływu na wydajność. Kilka cykli sprawdzania - nie ma znaczenia.”
2. „... przez faktyczne użycie wartości NULL zamiast wartości zastępczych. W zależności od typów danych możesz zaoszczędzić dużo miejsca na dysku i pamięci RAM, tym samym przyspieszając… wszystko.”

@Renzo ma odpowiedź, która mówi o implikacjach dotyczących wydajności - zakładam, że żadna z nich nie dotyczy PostgreSQL . Nie mogę znaleźć niczego, co uzasadnia wszelkie tego jako istotne dla PostgreSQL. Jakichkolwiek zapisanych cykli nie da się określić ilościowo w nawet najbardziej podstawowych zapytaniach.

CREATE TABLE foo (
  a int,
  b int NOT NULL,
  x float,
  y float NOT NULL
);

INSERT INTO foo ( a, b, x, y )
SELECT x, x, x, x
FROM generate_series(1,1E7) AS X(x);

EXPLAIN ANALYZE SELECT 1/a FROM foo;
EXPLAIN ANALYZE SELECT 1/b FROM foo;
EXPLAIN ANALYZE SELECT 1/x FROM foo;
EXPLAIN ANALYZE SELECT 1/y FROM foo;

Ponadto przeprowadziłem kilka testów, aby sprawdzić, czy indeksy NULL były coraz szybsze i nie mogłem tego potwierdzić. Ten niezwykle przydatny wątek Scotta Marlowe'a można znaleźć na listach mailingowych, które mówią o tym, że planista zapytań w wersji 9.1 może używać częściowego indeksu w odmiennych klauzulach WHERE. Przetestowałem to, uruchamiając następujące

CREATE TABLE foo ( a int );
CREATE TABLE bar ( a int NOT NULL );
INSERT INTO foo
  SELECT null FROM generate_series(1,1e5) AS x
  UNION ALL
  SELECT 10
  UNION ALL
  SELECT null FROM generate_series(1,1e5) AS x
;
INSERT INTO bar
  SELECT 0 FROM generate_series(1,1e5) AS x
  UNION ALL
  SELECT 10
  UNION ALL
  SELECT 0 FROM generate_series(1,1e5) AS x
;

Teraz utworzyłem indeksy,

CREATE INDEX foobar ON foo(a) WHERE a IS NOT NULL;
CREATE INDEX barbar ON bar(a) WHERE a <> 0;

W obu przypadkach planista był w stanie użyć indeksu podczas wybierania = 10i używał skanu seq podczas wyszukiwania odpowiednio wartości NULL lub 0. Oba indeksy częściowe miały ten sam rozmiar. I pełne indeksy (niepokazane) miały ten sam rozmiar. Postępując zgodnie z tą samą metodologią, załadowałem tabelę jedną sekwencją 1..1e5, jedną wartością null / 0 i kolejną sekwencją 1..1e5. Obie metody były w stanie znaleźć wartość null / 0 z indeksem obejmującym całą tabelę.

TLDR; Podsumowanie

Nie mogę w żaden sposób uzasadnić w żaden sposób większości problemów z wydajnością, które moim zdaniem były warte przetestowania pod kątem niedoskonałości planisty. Korzyści z używania null do zapisywania pamięci RAM są realne. Miejsce na dysku zaoszczędzone przez nieużywanie wartości NULL jest znikome, a to jest zawyżenie tabel z jedną NULLABLEkolumną lub mniej niż 8 kolumnami. W takich przypadkach nie ma zapisanego miejsca na dysku.