Constraints: A Developer's Secret Weapon | pgDay Paris 2018 | Will Leinweber

Of all my regrets while building database-backed applications, not fully embracing database constraints sooner is one of the bigger ones. For far too long, I treated the db as a little more than a dumb key-value store with joins.

Strong constraints turn your database into the last line of defense against bad data. Applications change several orders of magnitude more often than db schemas, and each change is a chance for corruption.

Constraints in Postgres come in many forms, from literally using the CONSTRAINT keyword to a large choice of datatypes. You'll come away from this talk understanding the pros and cons of type of constraint, and be ready to transform your application into a safer and more resilient system.

Transcript

1. CONSTRAINTS
   A DEVELOPER'S
   SECRET WEAPON
   PG Day Paris 2018-03-15
   

   View Slide

2. WILL LEINWEBER
   @LEINWEBER
   CITUSDATA.COM
   

   View Slide

3. INTRO
   

   View Slide

4. Will Leinweber @leinweber
   CONSTRAINTS
   maybe not the most exciting topic
   just want DB to safely store&retrieve data
   stern parent saying "No!"
   

   View Slide

5. Will Leinweber @leinweber
   RAILS
   changed the landscape
   before: spaghetti or mountains of xml
   after: convention instead of configuration
   

   View Slide

6. Will Leinweber @leinweber
   RAILS
   embrace constraints
   separate code concerns
   pluralize table names
   primary key is called "id"
   …etc
   

   View Slide

7. Will Leinweber @leinweber
   RAILS
   "big dumb hash in the sky"
   

   View Slide

8. Will Leinweber @leinweber
   MEANWHILE…
   learning how to make web applications
   

   View Slide

9. Will Leinweber @leinweber
   LEARNING 3NF
   examples hard to extrapolate to my problem
   

   View Slide

10. Will Leinweber @leinweber
    

    View Slide

11. Will Leinweber @leinweber
    TODAY
    think about database schema first
    work backwards to models and api
    

    View Slide

12. Will Leinweber @leinweber
    MOTIVATION
    database is the last line of defense
    

    View Slide

13. Will Leinweber @leinweber
    MOTIVATION
    code change frequency >>> schema change frequency
    

    View Slide

14. Will Leinweber @leinweber
    MOTIVATION
    ~1 year old app
    71 migrations
    1203 releases
    after ~2 years
    90 migrations
    1454 releases
    

    View Slide

15. Will Leinweber @leinweber
    MOTIVATION
    logical corruption is more likely to come from app bug
    

    View Slide

16. Will Leinweber @leinweber
    BUGS
    bugs that can be caught by schema are particularly dangerous
    

    View Slide

17. Will Leinweber @leinweber
    BUGS
    delayed problem
    hard to find cause
    can last for months
    

    View Slide

18. Will Leinweber @leinweber
    CLEANUP
    

    View Slide

19. Will Leinweber @leinweber
    MOTIVATION
    don't write bad data in the first place
    

    View Slide

20. TRADITIONAL
    CONSTRAINTS
    

    View Slide

21. Will Leinweber @leinweber
    TRADITIONAL CONSTRAINTS
    NOT NULL
    

    View Slide

22. Will Leinweber @leinweber
    TRADITIONAL CONSTRAINTS
    CREATE UNIQUE INDEX 

    ON users (email);
    

    View Slide

23. Will Leinweber @leinweber
    PARTIAL UNIQUE
    CREATE UNIQUE INDEX 

    ON users (email)

    WHERE deleted_at IS NULL;
    

    View Slide

24. Will Leinweber @leinweber
    ENFORCE ASSUMPTIONS
    "This property should always
    "Should never be…"
    be positive"
    be present"
    be between 0 and 1"
    be a fibonacci number"
    

    View Slide

25. DATATYPES
    

    View Slide

26. Will Leinweber @leinweber
    DATATYPES
    not often thought of as constraints
    constrain what type of data gets in
    

    View Slide

27. Will Leinweber @leinweber
    DATATYPES
    numbers are actually numbers
    booleans are actually booleans
    …etc
    

    View Slide

28. Will Leinweber @leinweber
    DATATYPES
    …other databases
    

    View Slide

29. Will Leinweber @leinweber
    ENUMS
    CREATE TYPE state 

    AS ENUM ('creating', 'running');
    

    ALTER TYPE state 

    ADD VALUE 'deleting';
    

    View Slide

30. Will Leinweber @leinweber
    RANGES
    SELECT i, i <@ '[1,10)'::int4range included

    FROM (VALUES (1),(5),(10)) as v(i);
    i | included
    ----+----------
    1 | t
    5 | t
    10 | f
    

    View Slide

31. Will Leinweber @leinweber
    RANGES
    int4range, int8range, numrange
    tsrange, tstzrange
    daterange
    

    View Slide

32. Will Leinweber @leinweber
    RANGES
    

    View Slide

33. Will Leinweber @leinweber
    SMALL OPTIMIZATION
    CREATE TABLE somename (

    aws_id text COLLATE "C" NOT NULL

    ); 

    -- i-0598c7d356eba48d7
    

    View Slide

34. Will Leinweber @leinweber
    OTHER TYPES
    uuid
    macaddr, inet, cidr
    array, hstore
    geometric
    

    View Slide

35. Will Leinweber @leinweber
    DATATYPES
    downside of using JSONB
    

    View Slide

36. FOREIGN
    KEYS
    

    View Slide

37. Will Leinweber @leinweber
    FOREIGN KEYS
    CREATE TABLE posts(

    user_id int NOT NULL

    REFERENCES users(id),

    …
    

    View Slide

38. Will Leinweber @leinweber
    OPTIONS
    REFERENCES foo ON DELETE/UPDATE
    NO ACTION / RESTRICT 

    CASCADE

    SET NULL / DEFAULT
    

    View Slide

39. Will Leinweber @leinweber
    PROBLEMS WITH TESTING
    transaction vs. truncation/deleting
    SET CONSTRAINTS all DEFERRED;
    

    View Slide

40. CHECK
    

    View Slide

41. Will Leinweber @leinweber
    CHECK
    custom logic
    

    View Slide

42. Will Leinweber @leinweber
    ENSURE POSITIVE NUMBER
    CREATE TABLE products(

    name text,

    price int CHECK(price > 0)

    );
    

    View Slide

43. Will Leinweber @leinweber
    REFERENCE OTHER COLUMN
    CREATE TABLE products(

    name text,

    price int CHECK(price > 0),

    sale_p int CHECK(sale_p > 0),

    CHECK (price > sale_p) 

    );
    

    View Slide

44. Will Leinweber @leinweber
    PERCENTS
    scale float DEFAULT 1.0 NOT NULL,

    CHECK (scale >= 0 && scale <= 1)
    

    View Slide

45. Will Leinweber @leinweber
    MORE
    CHECK ((json_col->>'i_prop')::int > 0)
    

    View Slide

46. Will Leinweber @leinweber
    USER DEFINED FUNCTIONS
    CREATE OR REPLACE FUNCTION is_fib(i int) RETURNS boolean AS $$

    DECLARE

    a integer := 5*i*i+4;

    b integer := 5*i*i-4;

    asq integer := sqrt(a)::int;

    bsq integer := sqrt(b)::int;

    BEGIN

    RETURN asq*asq=a OR bsq*bsq=b;

    end

    $$ LANGUAGE plpgsql IMMUTABLE STRICT;
    

    View Slide

47. Will Leinweber @leinweber
    USER DEFINED FUNCTIONS
    # CREATE TABLE onlyfib( i int CHECK (is_fib(i)) );

    CREATE TABLE
    # insert into onlyfib values (5), (8);

    INSERT 0 2
    # insert into onlyfib values (6);

    ERROR: new row for relation "onlyfib" violates
    check constraint "onlyfib_i_check"

    DETAIL: Failing row contains (6).
    

    View Slide

48. Will Leinweber @leinweber
    DOMAINS
    # CREATE DOMAIN fib AS int CHECK (is_fib(VALUE));

    # CREATE TABLE onlyfib(i fib);
    # insert into onlyfib values (5),(8);

    INSERT 0 2
    # insert into onlyfib values (6);

    ERROR: value for domain fib violates check
    constraint "fib_check"
    

    View Slide

49. Will Leinweber @leinweber
    

    View Slide

50. EXCLUSION
    

    View Slide

51. Will Leinweber @leinweber
    && OVERLAP
    select '[ 1,10)'::int4range

    && '[15,20)'::int4range;

    f
    select '[1,10)'::int4range

    && '[9,20)'::int4range;

    t
    

    View Slide

52. Will Leinweber @leinweber
    EXCLUSION
    CREATE TABLE billings (

    formation_id uuid NOT NULL,

    validity_period tstzrange NOT NULL,

    price_per_month integer NOT NULL

    );
    

    View Slide

53. Will Leinweber @leinweber
    EXCLUSION
    ALTER TABLE billings
    ADD CONSTRAINT billings_excl
    EXCLUDE USING gist (
    formation_id WITH =,
    validity_period WITH &&
    );
    

    View Slide

54. Will Leinweber @leinweber
    ERROR MESSAGE
    ERROR: conflicting key value
    violates exclusion constraint
    "constraint name"
    DETAIL: Key (id, range)=(row>) conflicts with existing key
    (id, range)=().
    

    View Slide

55. RECAP
    

    View Slide

56. Will Leinweber @leinweber
    3 TAKEAWAYS
    database is your last line of defense
    postgres has some really great constraints
    datatypes are constraints too
    

    View Slide

57. WILL LEINWEBER
    @LEINWEBER
    CITUSDATA.COM
    thank you
    

    View Slide