Advanced Postgres patterns and Django

Talk from Lviv.py#2, Jun 8, 2013

Transcript

1. Advanced Postgres patterns and Django Volodymyr Hotsyk Python developer @ GetGoing Inc gotsyk@gmail.com, hotsyk@github Lviv.py#2, Jun 08, 2013 1 Sunday, June 9, 13
2. Why Postgres •Replication, •Window functions, •Datatypes, •Functions, •Custom Languages, •Extensions, •NoSQL, •Indexes, •Foreign Data Wrappers, •and many others ... 2 Sunday, June 9, 13
3. Let’s see • Datatypes • Full-text search • SQL Functions 3 Sunday, June 9, 13
4. Datatypes • Arrays • Range types • JSON/HStore 4 Sunday, June 9, 13
5. > CREATE TABLE articles (..., tags varchar(255) [], ...); > INSERT INTO articles VALUES (..., '{“Ukraine”, ”Lviv”}', ..); > INSERT INTO articles VALUES (..., '{“Python”, ”Lviv”}', ..); Arrays 5 Sunday, June 9, 13
6. Using of arrays in Django > pip install djorm-ext-pgarray > pip install djorm-ext-expressions 6 Sunday, June 9, 13
7. from djorm_pgarray.fields import ArrayField from djorm_expressions.models import ExpressionManager class Article(models.Model): ... tags = ArrayField( blank=True, null=True, dbtype="text", dimension=1) objects = ExpressionManager() 7 Sunday, June 9, 13
8. Expressions with arrays from djorm_expressions.base import SqlExpression new_article = Article.objects.create( title='Some article', tags=['Ukraine','Lviv']) qs = Article.objects.where( SqlExpression("tags", "@>", ['Lviv']) ) 8 Sunday, June 9, 13
9. class ArrayExpression(object): def __init__(self, field): self.field = field def contains(self, value): return SqlExpression(self.field, "@>", value) def overlap(self, value): return SqlExpression(self.field, "&&", value) > qs = Article.objects.where( ArrayExpression("tags").contains(["Lviv", "python"]) ) 9 Sunday, June 9, 13
10. Range types • int4range - range of integer • int8range - range of bigin • numrange - numeric range • tsrange - range of timestamp • tstzrange - range of timestamp with time zone • daterange - range of dates 10 Sunday, June 9, 13
11. Ranges in Postgres # SELECT int4range(10, 20) @> 3; ?column? ---------- f (1 row) # SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0); ?column? ---------- t (1 row) 11 Sunday, June 9, 13
12. # SELECT int4range(10, 20) * int4range(15, 25); ?column? ---------- [15,20) # SELECT isempty(numrange(1, 5)); isempty --------- f (1 row) 12 Sunday, June 9, 13
13. Daterange # CREATE TABLE rooms (room int, during daterange); # INSERT INTO rooms VALUES (1, '["2013-06-08","2013-06-10")'); # SELECT * FROM rooms WHERE during && '["2013-06-09","2013-07-15")'; room | during ------+------------------------- 1 | [2013-06-08,2013-06-10) (1 row) # SELECT * FROM rooms WHERE during && '["2013-06-10","2013-07-15")'; room | during ------+-------- (0 rows) 13 Sunday, June 9, 13
14. JSON • JSON Field (9.2 and upper) • HStore 14 Sunday, June 9, 13
15. JSON field • New in 9.2 • Built-in json transformation functions: • No query operators (promised to be in 9.3) 15 Sunday, June 9, 13
16. HStore CREATE EXTENSION hstore; CREATE TABLE item ( id integer NOT NULL, name varchar(255), data hstore, ); 16 Sunday, June 9, 13
17. INSERT INTO items VALUES (1, 'Cup', 'size => "1l", color => “White”', ); 17 Sunday, June 9, 13
18. HStore in Django > pip install django-hstore from django.db import models from django_hstore import hstore class Item(models.Model): name = models.CharField(max_length=250) data = hstore.DictionaryField(db_index=True) objects = hstore.Manager() 18 Sunday, June 9, 13
19. >Item.objects.create( name='Cup 1', data={'color': 'white', 'size': '1l'}) >Item.objects.create( name='Cup 2', data={'color': 'yellow', 'logo': 'Python'}) >python_cups=Item.objects.filter( data__contains={'logo': 'Python'}) >print python_cups[0].data['color'] 19 Sunday, June 9, 13
20. Full-text search CREATE TABLE articles (id serial, title varchar(255), content text, post_text tsvector); CREATE INDEX posttext_gin ON articles USING GIN(post_text); CREATE TRIGGER update_posttext BEFORE INSERT OR UPDATE ON articles FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger('PostText', 'english', title, content); 20 Sunday, June 9, 13
21. from djorm_pgfulltext.models import SearchManager from djorm_pgfulltext.fields import VectorField from django.db import models class Article(models.Model): title = models.CharField(max_length=255) content = models.TextField() post_text = VectorField() objects = SearchManager( fields = ('title', 'content'), config = 'pg_catalog.english', search_field = 'post_text', auto_update_search_field = True ) > Article.objects.search("Lviv.py & Davydenko") 21 Sunday, June 9, 13
22. SQL Functions from djorm_expressions.base import SqlFunction class BitLength(SqlFunction): sql_function = "bit_length" qs = Article.objects.where( SqlExpression(BitLength("title"),">",20) ) 22 Sunday, June 9, 13
23. DB Connections Django is not pooling db connections django-postgrespool djorm-ext-pool django-db-pool 23 Sunday, June 9, 13
24. django-postgrespool import dj_database_url import django_postgrespool DATABASE = {'default':dj_database_url.config()} DATABASES['default']['ENGINE'] = 'django_postgrespool' SOUTH_DATABASE_ADAPTERS = { 'default': 'south.db.postgresql_psycopg2' } DATABASE_POOL_ARGS = {'max_overflow': 30, 'pool_size': 10, 'recycle': 60*60} 24 Sunday, June 9, 13
25. Thank you! Questions? github: @hotsyk twitter: @gotsyk gotsyk@gmail.com http://hotsyk.com 25 Sunday, June 9, 13
26. GetGoing, Inc We are hiring :) Contact us at Lviv.py or @gotsyk 26 Sunday, June 9, 13