Tweet
Tweet

Slide 1

Slide 1 text

G!"#$ b%&!#' ()% D*+#$! ORM ,"-"(+("!#. w"() P/($r%. @0r+"$1%r.("%#.

Slide 2

Slide 2 text

Postgres.app PSA: Macs

Slide 3

Slide 3 text

Why Postgres http://www.craigkerstiens.com/2012/04/30/why-postgres/ https://speakerdeck.com/craigkerstiens/postgres-demystified “Its the emacs of databases”

Slide 4

Slide 4 text

Datatypes Conditional Indexes Transactional DDL Foreign Data Wrappers Concurrent Index Creation Extensions Common Table Expressions Fast Column Addition Listen/Notify Table Inheritance Per Transaction sync replication Window functions NoSQL inside SQL Momentum TLDR

Slide 5

Slide 5 text

Limitations? Django attempts to support as many features as possible on all database backends. However, not all database backends are alike, and we’ve had to make design decisions on which features to support and which assumptions we can make safely.

Slide 6

Slide 6 text

D+(+(2%.

Slide 7

Slide 7 text

__________ < Postgres > ---------- \ ^__^ \ (oo)\_______ (__)\ )\/\ ||----w | || ||

Slide 8

Slide 8 text

Datatypes smallint bigint integer numeric float serial money char varchar text bytea timestamp timestamptz date time timetz interval boolean enum point line polygon box circle path inet cidr macaddr tsvector tsquery array XML UUID

Slide 9

Slide 9 text

Datatypes smallint bigint integer numeric float serial money char varchar text bytea timestamp timestamptz date time timetz interval boolean enum point line polygon box circle path cidr macaddr tsvector tsquery array XML UUID inet

Slide 10

Slide 10 text

Datatypes smallint bigint integer numeric float serial money char varchar text bytea timestamp timestamptz date time timetz interval boolean enum point line polygon box circle path cidr macaddr tsvector tsquery array XML UUID inet

Slide 11

Slide 11 text

Datatypes smallint bigint integer numeric float serial money char varchar text bytea timestamp timestamptz date time timetz interval boolean enum point line polygon box circle path cidr macaddr tsvector tsquery array XML UUID inet

Slide 12

Slide 12 text

_______ < SQLite > --------- \ ^__^ \ (oo)\_______ (__)\ )\/\ ||----w | || ||

Slide 13

Slide 13 text

Datatypes null real text blob integer

Slide 14

Slide 14 text

Datatypes smallint bigint integer numeric float serial money char varchar text bytea timestamp timestamptz date time timetz interval boolean enum point line polygon box circle path inet cidr macaddr tsvector tsquery array XML UUID

Slide 15

Slide 15 text

I#'3%.

Slide 16

Slide 16 text

________ < Postgres > ---------- \ ^__^ \ (oo)\_______ (__)\ )\/\ ||----w | || ||

Slide 17

Slide 17 text

Indexes Multiple Types B-Tree, Gin, Gist, KNN, SP-Gist CREATE INDEX CONCURRENTLY

Slide 18

Slide 18 text

_____ < MySQL > ------- \ ^__^ \ (oo)\_______ (__)\ )\/\ ||----w | || ||

Slide 19

Slide 19 text

Indexes They exist

Slide 20

Slide 20 text

Digging In

Slide 21

Slide 21 text

Arrays CREATE TABLE item ( id serial NOT NULL, name varchar (255), tags varchar(255) [], created_at timestamp );

Slide 22

Slide 22 text

Arrays CREATE TABLE item ( id serial NOT NULL, name varchar (255), tags varchar(255) [], created_at timestamp );

Slide 23

Slide 23 text

Arrays INSERT INTO item VALUES (1, 'Django Pony', '{“Programming”,”Animal”}', now()); INSERT INTO item VALUES (2, 'Ruby Gem', '{“Programming”,”Jewelry”}', now());

Slide 24

Slide 24 text

Arrays INSERT INTO item VALUES (1, 'Django Pony', '{“Programming”,”Animal”}', now()); INSERT INTO item VALUES (2, 'Ruby Gem', '{“Programming”,”Jewelry”}', now());

Slide 25

Slide 25 text

Django pip install djorm-ext-pgarray pip install djorm-ext-expressions models.py: from djorm_pgarray.fields import ArrayField from djorm_expressions.models import ExpressionManager class Item(models.Model): name = models.CharField(max_length=255) tags = ArrayField(dbtype="varchar(255)") created_at = models.DateTimeField(auto_now=True)

Slide 26

Slide 26 text

Django pip install djorm-ext-pgarray pip install djorm-ext-expressions models.py: from djorm_pgarray.fields import ArrayField from djorm_expressions.models import ExpressionManager class Item(models.Model): name = models.CharField(max_length=255) tags = ArrayField(dbtype="varchar(255)") created_at = models.DateTimeField(auto_now=True)

Slide 27

Slide 27 text

Django i = Item( name='Django Pony', tags=['Programming','Animal']) i.save() qs = Item.objects.where( SqlExpression("tags", "@>", ['programming']) )

Slide 28

Slide 28 text

Django i = Item( name='Django Pony', tags=['Programming','Animal']) i.save() qs = Item.objects.where( SqlExpression("tags", "@>", ['programming']) )

Slide 29

Slide 29 text

dblink hstore citext ltree isn cube pgcrypto tablefunc uuid-ossp earthdistance trigram fuzzystrmatch pgrowlocks pgstattuple btree_gist dict_int dict_xsyn unaccent Extensions

Slide 30

Slide 30 text

NoSQL in your SQL CREATE EXTENSION hstore; CREATE TABLE item ( id integer NOT NULL, name varchar(255), data hstore, );

Slide 31

Slide 31 text

NoSQL in your SQL CREATE EXTENSION hstore; CREATE TABLE item ( id integer NOT NULL, name varchar(255), data hstore, );

Slide 32

Slide 32 text

NoSQL in your SQL CREATE EXTENSION hstore; CREATE TABLE item ( id integer NOT NULL, name varchar(255), data hstore, );

Slide 33

Slide 33 text

NoSQL in your SQL INSERT INTO items VALUES ( 1, 'Pony', 'rating => "4.0", color => “Pink”', );

Slide 34

Slide 34 text

NoSQL in your SQL INSERT INTO items VALUES ( 1, 'Pony', 'rating => "4.0", color => “Pink”', );

Slide 35

Slide 35 text

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() def __unicode__(self): return self.name

Slide 36

Slide 36 text

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() def __unicode__(self): return self.name

Slide 37

Slide 37 text

Django Item.objects.create( name='Django Pony', data={'rating': '5'}) Item.objects.create( name='Pony', data={'color': 'pink', 'rating': '4'})

Slide 38

Slide 38 text

Django Item.objects.create( name='Django Pony', data={'rating': '5'}) Item.objects.create( name='Pony', data={'color': 'pink', 'rating': '4'})

Slide 39

Slide 39 text

Django colored_ponies = Product.objects.filter(data__contains='color') print colored_ponies[0].data['color'] favorite_ponies = Product.objects.filter(data__contains={'rating': '5'}) print colored_ponies[0]

Slide 40

Slide 40 text

Django colored_ponies = Product.objects.filter(data__contains='color') print colored_ponies[0].data['color'] favorite_ponies = Product.objects.filter(data__contains={'rating': '5'}) print colored_ponies[0]

Slide 41

Slide 41 text

Q4%4%"#$

Slide 42

Slide 42 text

No content

Slide 43

Slide 43 text

Pub/Sub?

Slide 44

Slide 44 text

Postgres a great Queue

Slide 45

Slide 45 text

Postgres a great Queue Not With Polling

Slide 46

Slide 46 text

Trunk pip install celery trunk psql < sql/*.sql celery worker -A tasks --loglevel=info ipython -i tasks.py >>> add.delay(2, 2)

Slide 47

Slide 47 text

Trunk pip install celery trunk psql < sql/*.sql celery worker -A tasks --loglevel=info ipython -i tasks.py >>> add.delay(2, 2)

Slide 48

Slide 48 text

Trunk from celery import Celery celery = Celery('tasks') celery.config_from_object({ 'BROKER_URL': 'trunk.transport.Transport:// localhost/trunk', }) @celery.task def add(x, y): return x + y

Slide 49

Slide 49 text

Trunk from celery import Celery celery = Celery('tasks') celery.config_from_object({ 'BROKER_URL': 'trunk.transport.Transport:// localhost/trunk', }) @celery.task def add(x, y): return x + y

Slide 50

Slide 50 text

T3( S%+r0)

Slide 51

Slide 51 text

Searching Text

Slide 52

Slide 52 text

Searching Text Lucene Sphinx Elastic Search Solr

Slide 53

Slide 53 text

Searching Text Lucene Sphinx Elastic Search Solr Postgres

Slide 54

Slide 54 text

Full Text Search CREATE TABLE posts ( id serial, title varchar(255), content text, tags varchar(255)[], post_text tsvector ); CREATE INDEX posttext_gin ON posts USING GIN(post_text); CREATE TRIGGER update_posttext BEFORE INSERT OR UPDATE ON posts FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger( ‘PostText’,‘english’,title, content, tags);

Slide 55

Slide 55 text

Full Text Search CREATE TABLE posts ( id serial, title varchar(255), content text, tags varchar(255)[], post_text tsvector ); CREATE INDEX posttext_gin ON posts USING GIN(post_text); CREATE TRIGGER update_posttext BEFORE INSERT OR UPDATE ON posts FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger( ‘PostText’,‘english’,title, content, tags);

Slide 56

Slide 56 text

Django from djorm_pgfulltext.models import SearchManager from djorm_pgfulltext.fields import VectorField from django.db import models class Page(models.Model): name = models.CharField(max_length=200) description = models.TextField() search_index = VectorField() objects = SearchManager( fields = ('name', 'description'), config = 'pg_catalog.english', search_field = 'search_index', auto_update_search_field = True )

Slide 57

Slide 57 text

Django from djorm_pgfulltext.models import SearchManager from djorm_pgfulltext.fields import VectorField from django.db import models class Page(models.Model): name = models.CharField(max_length=200) description = models.TextField() search_index = VectorField() objects = SearchManager( fields = ('name', 'description'), config = 'pg_catalog.english', search_field = 'search_index', auto_update_search_field = True )

Slide 58

Slide 58 text

Django Page.objects.search("documentation & about") Page.objects.search("about | documentation", raw=True)

Slide 59

Slide 59 text

Django Page.objects.search("documentation & about") Page.objects.search("about | documentation", raw=True)

Slide 60

Slide 60 text

I#'3%.

Slide 61

Slide 61 text

Indexes B-Tree Generalized Inverted Index (GIN) Generalized Search Tree (GIST) K Nearest Neighbors (KNN) Space Partitioned GIST (SP-GIST)

Slide 62

Slide 62 text

Indexes Which do I use?

Slide 63

Slide 63 text

Indexes B-Tree Generalized Inverted Index (GIN) Generalized Search Tree (GIST) K Nearest Neighbors (KNN) Space Partitioned GIST (SP-GIST)

Slide 64

Slide 64 text

Generalized Inverted Index (GIN) Use with multiple values in 1 column Array/hStore

Slide 65

Slide 65 text

Generalized Search Tree (GIST) Full text search Shapes PostGIS

Slide 66

Slide 66 text

Indexes B-Tree Generalized Inverted Index (GIN) Generalized Search Tree (GIST) K Nearest Neighbors (KNN) Space Partitioned GIST (SP-GIST)

Slide 67

Slide 67 text

G%!Sp+("+,

Slide 68

Slide 68 text

GeoDjango

Slide 69

Slide 69 text

O#% M!r%

Slide 70

Slide 70 text

Connections

Slide 71

Slide 71 text

Connections django-postgrespool djorm-ext-pool django-db-pool

Slide 72

Slide 72 text

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' }

Slide 73

Slide 73 text

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' }

Slide 74

Slide 74 text

Limitations? Django attempts to support as many features as possible on all database backends. However, not all database backends are alike, and we’ve had to make design decisions on which features to support and which assumptions we can make safely.

Slide 75

Slide 75 text

D*+#$! ORM Its not so bad

Slide 76

Slide 76 text

P/($r%. Its great

Slide 77

Slide 77 text

5+#1.!