Are you Postgres yet?

Postgres and Django - current situation and future plans.

Transcript

1. Are you Postgres yet? PyCon Belarus, January 31, 2015 Volodymyr Hotsyk
2. About me • Python developer at GetGoing • PyCon Ukraine organizer • github/twitter/gmail: hotsyk
3. Outline • Why Postgres • Python + Postgres • Django support • Range types • Array type • HStore • JSONType • Indexes • Pools
4. Why Postgres • Window functions • Flexible Datatypes • Functions • Custom Languages • Extensions • Full text search
5. Why Postgres • Foreign Data Wrappers • Conditional Constraints and Partial Indexes • Listen/Notify • Table Inheritance • Real NoSQL in SQL
6. Python + Postgres • Psycopg2 • pg8000 • Python3 + asyncio = aiopg
7. Django support • Django ORM designed to work with all supported DBs • No Postgres specific features • Third-party apps to add support of specific features
8. Django support • kickstarter.com/ projects/mjtamlyn/ improved- postgresql-support- in-django
9. contrib.postgres in 1.8 • Specific model fields • ArrayField • HStoreField • Range Fields • Specific form fields and widgets • SimpleArrayField • SplitArrayField • HStoreField • Range Fields • Widgets
10. contrib.postgres in 1.8 • Database migration operations • CreateExtension • HStoreExtension • UnaccentExtension • Specific lookups • Unaccent • Validators • KeysValidator • Range validators
11. Range types • int4range — Range of integer • int8range — Range of bigint • numrange — Range of numeric • tsrange — Range of timestamp without time zone • tstzrange — Range of timestamp with time zone • daterange — Range of date
12. Range types >CREATE TABLE reservation (room int, during tsrange); >INSERT INTO reservation VALUES (1, '[2015-01-31 14:30, 2015-01-31 15:30)');
13. Range types + Django • 1.8: django.contrib.postgres • 1.7: django-postgres
14. Range types + Django • IntegerRangeField • BigIntegerRangeField • FloatRangeField • DateTimeRangeField • DateRangeField
15. Range types >Event.objects.create(name='Meetup', ages=(18, 70)) Query >Event.objects.filter( ages__contains=NumericRange(20, 35)) >Event.objects.filter( ages__contained_by=NumericRange(0, 55)) >Event.objects.filter( ages__overlap=NumericRange(18, 22))
16. Range types Compare >Event.objects.filter( ages__fully_lt=NumericRange(31, 35)) >Event.objects.filter( ages__adjacent_to=NumericRange(20, 31))
17. Demo •github.com/hotsyk/djangopsqltest
18. Array type CREATE TABLE tictactoe (squares integer[3][3]); • Postgres 9.0+ • Django <=1.7: niwibe.github.io/djorm-pgarray • Django 1.7: bitbucket.org/schinckel/django- postgres/ • Django 1.8: django.contrib.postgres.fields.array
19. Array type from django.db import models from django.contrib.postgres.fields import ArrayField class Post(models.Model): name = models.CharField(max_length=200) tags = ArrayField(models.CharField(max_length=200), blank=True)
20. Array type >>> Post.objects.create(name='First post', tags=['thoughts', 'django']) >>> Post.objects.create(name='Second post', tags=['thoughts']) >>> Post.objects.create(name='Third post', tags=['tutorial', 'django'])
21. Array type >>> Post.objects.filter(tags__contains=['django']) [<Post: First post>, <Post: Third post>] >>> Post.objects.filter(tags__contains=['django', 'thoughts']) [<Post: First post>]
22. Demo •github.com/hotsyk/djangopsqltest
23. HStore • Key/value store • Postgres 9.1+ • >create extension hstore;
24. Django support • Django <= 1.7: django-hstore • Django 1.8: contrib.postgres
25. Demo •github.com/hotsyk/djangopsqltest/ hstore
26. JSONType • Postgres 9.4+ • Document DB in your SQL DB • Django 1.7: django-jsonfield, django-postgres • Django 1.9: contrib.postgres
27. JSONType CREATE TABLE json_test ( id serial primary key, data jsonb ); INSERT INTO json_test (data) VALUES ('{}'), ('{"a": 1}'), ('{"a": 2, "b": ["c", "d"]}'), ('{"a": 1, "b": {"c": "d", "e": true}}'), ('{"b": 2}');
28. JSONType SELECT * FROM json_test; id | data ----+-------------------------------------- 1 | {} 2 | {"a": 1} 3 | {"a": 2, "b": ["c", "d"]} 4 | {"a": 1, "b": {"c": "d", "e": true}} 5 | {"b": 2} (5 rows)
29. JSONType SELECT * FROM json_test WHERE data = ‘{"a":1}'; id | data ----+------ 1 | {"a": 1} (1 row) SELECT * FROM json_test WHERE data @> ‘{"a":1}'; id | data ----+-------------------------------------- 2 | {"a": 1} 4 | {"a": 1, "b": {"c": "d", "e": true}} (2 rows)
30. JSONType SELECT * FROM json_test WHERE data ?| array['a', 'b']; id | data ----+-------------------------------------- 2 | {"a": 1} 3 | {"a": 2, "b": ["c", "d"]} 4 | {"a": 1, "b": {"c": "d", "e": true}} 5 | {"b": 2} (4 rows)
31. JSONType SELECT * FROM json_test WHERE data #> '{b,c}' = '"d"'; Give me objects where element b has a child object that has element c equal to the string "d". Neat. id | data ----+-------------------------------------- 4 | {"a": 1, "b": {"c": "d", "e": true}}
32. JSONType SELECT * FROM json_test WHERE data #> '{b,c}' = '"d"'; Give me objects where element b has a child object that has element c equal to the string "d". Neat. id | data ----+-------------------------------------- 4 | {"a": 1, "b": {"c": "d", "e": true}}
33. Demo •github.com/hotsyk/djangopsqltest/ jsonfield
34. Indexes • Partial indexes • Multicolumn indexes
35. Indexes >python manage.py makemigrations --empty yourappname …… operations = [ migrations.RunSQL( "CREATE UNIQUE INDEX IDX1 " "ON Table1 (t1, t2) WHERE t2 IS NOT NULL"), ]
36. Demo •github.com/hotsyk/djangopsqltest/ indexes
37. Pools • Postgres database connections are expensive • Django 1.6+ - builtin pool • Django <1.6 - django-postgrespool, djorm-ext- pool, django-db-pool • Django -> PG Pool / PG Bounce -> Postgres
38. Pools
39. Pools performance • Django 1.7 • Django 1.7 + pool • 1.7 + pool+ pgPool • 1.7 + pool + pgBouncer
40. Questions: • hotsyk@hotsyk.com • twitter:@hotsyk • github:hotsyk