Constraints: a Developer's Secret Weapon | PostgreSQL Conference Europe 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
2. WILL LEINWEBER @LEINWEBER CITUSDATA.COM
3. TINYURL.COM/ PGEUCONST
4. INTRO
5. Will Leinweber @leinweber CONSTRAINTS maybe not the most exciting topic just want DB to safely store&retrieve data stern parent saying "No!"
6. Will Leinweber @leinweber RAILS changed the landscape before: spaghetti or mountains of xml after: convention instead of configuration
7. Will Leinweber @leinweber RAILS embrace constraints separate code concerns pluralize table names primary key is called "id" …etc
8. Will Leinweber @leinweber RAILS "big dumb hash in the sky"
9. Will Leinweber @leinweber MEANWHILE… learning how to make web applications
10. Will Leinweber @leinweber LEARNING 3NF examples hard to extrapolate to my problem
11. Will Leinweber @leinweber
12. Will Leinweber @leinweber TODAY think about database schema first work backwards to models and api
13. Will Leinweber @leinweber MOTIVATION database is the last line of defense
14. Will Leinweber @leinweber MOTIVATION code change frequency >>> schema change frequency
15. Will Leinweber @leinweber MOTIVATION ~1 year old app 71 migrations 1203 releases after ~2 years 90 migrations 1454 releases
16. Will Leinweber @leinweber MOTIVATION logical corruption is more likely to come from app bug
17. Will Leinweber @leinweber BUGS bugs that can be caught by schema are particularly dangerous
18. Will Leinweber @leinweber BUGS delayed problem hard to find cause can last for months
19. Will Leinweber @leinweber CLEANUP
20. Will Leinweber @leinweber MOTIVATION don't write bad data in the first place
21. TRADITIONAL CONSTRAINTS
22. Will Leinweber @leinweber TRADITIONAL CONSTRAINTS NOT NULL
23. Will Leinweber @leinweber TRADITIONAL CONSTRAINTS CREATE UNIQUE INDEX 
 ON users (email);
24. Will Leinweber @leinweber PARTIAL UNIQUE CREATE UNIQUE INDEX 
 ON users (email)
 WHERE deleted_at IS NULL;
25. 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"
26. DATATYPES
27. Will Leinweber @leinweber DATATYPES not often thought of as constraints constrain what type of data gets in
28. Will Leinweber @leinweber DATATYPES numbers are actually numbers booleans are actually booleans …etc
29. Will Leinweber @leinweber DATATYPES …other databases
30. Will Leinweber @leinweber ENUMS CREATE TYPE state 
 AS ENUM ('creating', 'running'); 
 ALTER TYPE state 
 ADD VALUE 'deleting';
31. 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
32. Will Leinweber @leinweber RANGES int4range, int8range, numrange tsrange, tstzrange daterange
33. Will Leinweber @leinweber RANGES
34. Will Leinweber @leinweber SMALL OPTIMIZATION CREATE TABLE somename (
 aws_id text COLLATE "C" NOT NULL
 ); 
 -- i-0598c7d356eba48d7
35. Will Leinweber @leinweber OTHER TYPES uuid macaddr, inet, cidr array, hstore geometric
36. Will Leinweber @leinweber DATATYPES downside of using JSONB
37. FOREIGN KEYS
38. Will Leinweber @leinweber FOREIGN KEYS CREATE TABLE posts(
 user_id int NOT NULL
 REFERENCES users(id),
 …
39. Will Leinweber @leinweber OPTIONS REFERENCES foo ON DELETE/UPDATE NO ACTION / RESTRICT 
 CASCADE
 SET NULL / DEFAULT
40. Will Leinweber @leinweber PROBLEMS WITH TESTING transaction vs. truncation/deleting SET CONSTRAINTS all DEFERRED;
41. CHECK
42. Will Leinweber @leinweber CHECK custom logic
43. Will Leinweber @leinweber ENSURE POSITIVE NUMBER CREATE TABLE products(
 name text,
 price int CHECK(price > 0)
 );
44. 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) 
 );
45. Will Leinweber @leinweber PERCENTS scale float DEFAULT 1.0 NOT NULL,
 CHECK (scale >= 0 & scale <= 1)
46. Will Leinweber @leinweber MORE CHECK ((json_col->>'i_prop')::int > 0)
47. 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;
48. 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).
49. 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"
50. Will Leinweber @leinweber
51. EXCLUSION
52. Will Leinweber @leinweber && OVERLAP select '[ 1,10)'::int4range
 && '[15,20)'::int4range;
 f select '[1,10)'::int4range
 && '[9,20)'::int4range;
 t
53. Will Leinweber @leinweber EXCLUSION CREATE TABLE billings (
 formation_id uuid NOT NULL,
 validity_period tstzrange NOT NULL,
 price_per_month integer NOT NULL
 );
54. Will Leinweber @leinweber EXCLUSION ALTER TABLE billings ADD CONSTRAINT billings_excl EXCLUDE USING gist ( formation_id WITH =, validity_period WITH && );
55. Will Leinweber @leinweber ERROR MESSAGE ERROR: conflicting key value violates exclusion constraint "constraint name" DETAIL: Key (id, range)=(<new row>) conflicts with existing key (id, range)=(<existing row>).
56. RECAP
57. Will Leinweber @leinweber 3 TAKEAWAYS database is your last line of defense postgres has some really great constraints datatypes are constraints too
58. WILL LEINWEBER @LEINWEBER CITUSDATA.COM thank you