$30 off During Our Annual Pro Sale. View Details »

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

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.

Citus Data

March 15, 2018
Tweet

More Decks by Citus Data

Other Decks in Technology

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