Django + Postgres = ?

Current situation of Postgres support in Django, why it matters and what will change in future Django releases.

Transcript

1. Django + Postgres = ? WeBukovel, 28/Feb/2015 Volodymyr Hotsyk
2. About me • Python developer at GetGoing • PyCon Ukraine organizer • github/twitter/gmail: hotsyk
3. Outline • Why Postgres • Python + Postgres • PL/Python • Django support • Range types • Array type • HStore • JSONType • UUIDField • Indexes • Fulltext search • 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. Python in Postgres CREATE FUNCTION funcname (argument-list) RETURNS return-type AS $$ # PL/Python function body $$ LANGUAGE plpythonu;
8. PL/Python CREATE FUNCTION pyreplace(x text) RETURNS text AS $$ return x.replace('123', '256') $$ LANGUAGE plpythonu;
9. PL/Python psqltest=# select * from pyreplace('123 123’); pyreplace ----------- 256 256 (1 row)
10. Django support • Django ORM designed to work with all supported DBs • No Postgres specific features • Third-party apps to add support of specific features
11. Django support • kickstarter.com/ projects/mjtamlyn/ improved- postgresql-support- in-django
12. Already in 1.8
13. Plans for 1.9
14. contrib.postgres in 1.8 • Specific model fields • ArrayField • HStoreField • Range Fields • Specific form fields and widgets • SimpleArrayField • SplitArrayField • HStoreField • Range Fields • Widgets
15. contrib.postgres in 1.8 • Database migration operations • CreateExtension • HStoreExtension • UnaccentExtension • Specific lookups • Unaccent • Validators • KeysValidator • Range validators
16. 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
17. Range types CREATE TABLE reservation (room int, during tsrange); INSERT INTO reservation VALUES (1, '[2015-01-31 14:30, 2015-01-31 15:30)');
18. Range types + Django • 1.8: django.contrib.postgres • <=1.7: external apps
19. Range types + Django • IntegerRangeField • BigIntegerRangeField • FloatRangeField • DateTimeRangeField • DateRangeField
20. Range types from django.contrib.postgres import fields from django.db import models class Event(models.Model): name = models.CharField(max_length=50) ages = fields.ranges.IntegerRangeField()
21. Range types Event.objects.create(name='Meetup', ages=(18, 70)) Event.objects.create(name='Meetup 2', ages=(10, 30)) events = Event.objects.filter( ages__contains=NumericRange(20, 35)) print([e.name for e in events]) >> [u'Meetup']
22. Range types events = Event.objects.filter( ages__contained_by=NumericRange(0, 55)) print([e.name for e in events]) >> [u'Meetup 2'] events = Event.objects.filter( ages__overlap=NumericRange(18, 22)) print([e.name for e in events]) >> [u'Meetup', u'Meetup 2']
23. Range types events = Event.objects.filter( ages__fully_lt=NumericRange(31, 35)) print([e.name for e in events]) >> [u'Meetup 2'] events = Event.objects.filter( ages__adjacent_to=NumericRange(70, 80)) print([e.name for e in events]) >> [u'Meetup']
24. Booking example
25. Booking example from django.contrib.postgres import fields from django.db import models class Hotel(models.Model): title = models.CharField(max_length=200) class Room(models.Model): hotel = models.ForeignKey(Hotel) title = models.CharField(max_length=200) class Booking(models.Model): room = models.ForeignKey(Room) dates = fields.ranges.DateRangeField()
26. Booking example from psycopg2.extras import DateRange hotel = Hotel.objects.create(title=‘hotel') room = Room.objects.create( hotel=hotel, title=‘room') day = datetime.timedelta(days=1) date1 = (datetime.datetime.now() + datetime.timedelta(days=7)).date() date2 = date1 + day
27. Booking example Booking.objects.create( room=room, dates=(date1, date2)) Booking.objects.create( room=room, dates=(date1, date2 + day)) print([b for b in Booking.objects.filter( dates__overlap=DateRange( date2, date2 + day, bounds='[)'))]) >> [<Booking: Booking object>]
28. Array type CREATE TABLE tictactoe (squares integer[3][3]); • Postgres 9.0+ • Django <=1.7: djorm-pgarray, other apps • Django 1.8: contrib.postgres.fields.array
29. Array type from django.contrib.postgres import fields class Post(models.Model): name = models.CharField( max_length=50) tags = fields.array.ArrayField( models.CharField( max_length=20))
30. Array type Post.objects.create(name=‘First', tags=['thoughts', 'django']) Post.objects.create(name=‘Second’, tags=[‘thoughts']) Post.objects.create(name=‘Third', tags=['tutorial', 'django'])
31. Array type print([p.name for p in Post.objects.filter( tags__contains=['django'])]) >> [u'First', u'Third'] print([p.name for p in Post.objects.filter( tags__contains=[‘django', 'thoughts'])]) >> [u'First']
32. HStore • Key/value store • Postgres 9.0+ =# create extension hstore;
33. Django support • Django <= 1.7: django-hstore • Django 1.8: contrib.postgres
34. HStore type from django.contrib.postgres import fields from django.db import models class Location(models.Model): name = models.CharField( max_length=50) location = \ fields.hstore.HStoreField()
35. HStore type Location.objects.create(name='Lviv', location={'lat': '49.85', 'long': '24.0166666667'}) Location.objects.create(name='Kyiv', location={'lat': '50.4501', 'long': '30.523400000000038'})
36. HStore type locations = Location.objects.filter( location__lat__contains='50') print([l.name for l in locations]) # >> [u'Kyiv'] locations = Location.objects.filter( location__contained_by={'lat': ’49.85', 'long': '24.0166666667','timezone': 'any'}) print([l.name for l in locations]) # >> [u'Lviv']
37. JSONType • Postgres 9.4+ • Document DB in your SQL DB • Django 1.7, 1.8: bitbucket.org/schinckel/django- postgres/ • Django 1.9: contrib.postgres
38. JSONType from django.db import models from postgres.fields import json_field class MyJson(models.Model): json = json_field.JSONField()
39. JSONType MyJson.objects.create( json={'a': 1, 'b': {'c': 2, 'd': 3}}) MyJson.objects.create( json={'d': 3, 'b': {'c': 4, 'f': 5}})
40. JSONType print([m.json for m in MyJson.objects.filter( json__has_all=['a', 'b'])]) >> [{u'a': 1, u'b': {u'c': 2, u'd': 3}}] print([m.json for m in MyJson.objects.filter( json__path_b_c__gt=2)]) >> [{u'b': {u'c': 4, u'f': 5}, u'd': 3}]
41. UUID field • UUIDField - new in Django 1.8 • With PostgreSQL —> uuid datatype • Otherwise —> char(32).
42. UUID field import uuid from django.db import models class MyUUIDModel(models.Model): id = models.UUIDField( primary_key=True, default=uuid.uuid4, editable=False)
43. Indexes B-tree, Hash, GiST and GIN CREATE UNIQUE INDEX IDX1 ON Table1 (t1, t2) WHERE t2 IS NOT NULL; CREATE INDEX CONCURRENTLY IDX2 ON Table1(t2, t3); CREATE INDEX name ON table USING hash (column);
44. FullText Search class Page(models.Model): name = models.CharField(max_length=200) description = models.TextField() search_index = VectorField() objects = SearchManager( fields=('name', 'description'), config='pg_catalog.russian', search_field='search_index', auto_update_search_field = True )
45. FullText Search Page.objects.create(name='Главная', description='главная') Page.objects.create(name='неглавная', description='неглавная') pages = Page.objects.search("главная") assert len(pages) == 1 pages = Page.objects.search( query="главное | неглавное", raw=True) assert len(pages) == 2
46. 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
47. Pools
48. Code github.com/hotsyk/djangopsqltest
49. Questions: • hotsyk@hotsyk.com • twitter:@hotsyk • github:hotsyk