Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Advanced Postgres patterns and Django

Advanced Postgres patterns and Django

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

Avatar for Volodymyr Hotsyk

Volodymyr Hotsyk

June 08, 2013
Tweet

More Decks by Volodymyr Hotsyk

Other Decks in Programming

Transcript

  1. Advanced Postgres patterns and Django Volodymyr Hotsyk Python developer @

    GetGoing Inc [email protected], 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. > CREATE TABLE articles (..., tags varchar(255) [], ...); >

    INSERT INTO articles VALUES (..., '{“Ukraine”, ”Lviv”}', ..); > INSERT INTO articles VALUES (..., '{“Python”, ”Lviv”}', ..); Arrays 5 Sunday, June 9, 13
  4. Using of arrays in Django > pip install djorm-ext-pgarray >

    pip install djorm-ext-expressions 6 Sunday, June 9, 13
  5. 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
  6. 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
  7. 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
  8. 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
  9. 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
  10. # SELECT int4range(10, 20) * int4range(15, 25); ?column? ---------- [15,20)

    # SELECT isempty(numrange(1, 5)); isempty --------- f (1 row) 12 Sunday, June 9, 13
  11. 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
  12. 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
  13. HStore CREATE EXTENSION hstore; CREATE TABLE item ( id integer

    NOT NULL, name varchar(255), data hstore, ); 16 Sunday, June 9, 13
  14. INSERT INTO items VALUES (1, 'Cup', 'size => "1l", color

    => “White”', ); 17 Sunday, June 9, 13
  15. 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
  16. >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
  17. 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
  18. 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
  19. 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
  20. 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
  21. GetGoing, Inc We are hiring :) Contact us at Lviv.py

    or @gotsyk 26 Sunday, June 9, 13