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

PSA #2 http://postgresweekly.com

Slide 4

Slide 4 text

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

Slide 5

Slide 5 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 6

Slide 6 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 7

Slide 7 text

D+(+(2%.

Slide 8

Slide 8 text

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

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 inet cidr macaddr tsvector tsquery array XML UUID

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

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 13

Slide 13 text

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

Slide 14

Slide 14 text

Datatypes null real text blob integer

Slide 15

Slide 15 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 16

Slide 16 text

I#'3%.

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

Indexes They exist

Slide 21

Slide 21 text

Digging In

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 CREATE TABLE item ( id serial NOT NULL, name varchar (255), tags varchar(255) [], created_at timestamp );

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

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

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

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

Slide 30

Slide 30 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 31

Slide 31 text

NoSQL in your SQL

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 CREATE EXTENSION hstore; CREATE TABLE item ( id integer NOT NULL, name varchar(255), data hstore, );

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

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

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 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 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 Item.objects.create( name='Django Pony', data={'rating': '5'}) Item.objects.create( name='Pony', data={'color': 'pink', 'rating': '4'})

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

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 42

Slide 42 text

No content

Slide 43

Slide 43 text

JSON

Slide 44

Slide 44 text

JSON w/ PLV8

Slide 45

Slide 45 text

JSON

Slide 46

Slide 46 text

SELECT '{"id":1,"email": "craig.kerstiens@gmail.com",}'::json; JSON

Slide 47

Slide 47 text

SELECT '{"id":1,"email": "craig.kerstiens@gmail.com",}'::json; JSON V8 w/ PLV8

Slide 48

Slide 48 text

SELECT '{"id":1,"email": "craig.kerstiens@gmail.com",}'::json; JSON V8 w/ PLV8 create or replace function js(src text) returns text as $$ return eval( "(function() { " + src + "})" )(); $$ LANGUAGE plv8;

Slide 49

Slide 49 text

SELECT '{"id":1,"email": "craig.kerstiens@gmail.com",}'::json; JSON V8 w/ PLV8 create or replace function js(src text) returns text as $$ return eval( "(function() { " + src + "})" )(); $$ LANGUAGE plv8; JS Injection in DB: Bad Idea

Slide 50

Slide 50 text

Q4%4%"#$

Slide 51

Slide 51 text

No content

Slide 52

Slide 52 text

Pub/Sub?

Slide 53

Slide 53 text

Postgres a great Queue

Slide 54

Slide 54 text

Postgres a great Queue Not With Polling

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

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

Slide 57

Slide 57 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 58

Slide 58 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 59

Slide 59 text

T3( S%+r0)

Slide 60

Slide 60 text

Searching Text

Slide 61

Slide 61 text

Searching Text Lucene Sphinx Elastic Search Solr

Slide 62

Slide 62 text

Searching Text Lucene Sphinx Elastic Search Solr Postgres

Slide 63

Slide 63 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 64

Slide 64 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 65

Slide 65 text

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

Slide 66

Slide 66 text

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

Slide 67

Slide 67 text

Django Post.objects.search("documentation & about") Post.objects.search("about | documentation")

Slide 68

Slide 68 text

Django Post.objects.search("documentation & about") Post.objects.search("about | documentation")

Slide 69

Slide 69 text

I#'3%.

Slide 70

Slide 70 text

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

Slide 71

Slide 71 text

Indexes Which do I use?

Slide 72

Slide 72 text

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

Slide 73

Slide 73 text

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

Slide 74

Slide 74 text

Generalized Search Tree (GIST) Full text search Shapes PostGIS

Slide 75

Slide 75 text

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

Slide 76

Slide 76 text

G%!Sp+("+,

Slide 77

Slide 77 text

GeoDjango https://speakerdeck.com/pyconslides/location-location- location http://geodjango.org/

Slide 78

Slide 78 text

R%+' O#,&

Slide 79

Slide 79 text

R%+' O#,&

Slide 80

Slide 80 text

Django pip install django-db-tools settings.py MIDDLEWARE_CLASSES = ( # ... 'dbtools.middleware.ReadOnlyMiddleware', # ... ) READ_ONLY_MODE = True

Slide 81

Slide 81 text

Django pip install django-db-tools settings.py MIDDLEWARE_CLASSES = ( # ... 'dbtools.middleware.ReadOnlyMiddleware', # ... ) READ_ONLY_MODE = True

Slide 82

Slide 82 text

Django pip install django-db-tools settings.py MIDDLEWARE_CLASSES = ( # ... 'dbtools.middleware.ReadOnlyMiddleware', # ... ) READ_ONLY_MODE = os.environ[‘READ_ONLY_MODE']

Slide 83

Slide 83 text

Django pip install django-db-tools settings.py MIDDLEWARE_CLASSES = ( # ... 'dbtools.middleware.ReadOnlyMiddleware', # ... ) READ_ONLY_MODE = os.environ[‘READ_ONLY_MODE']

Slide 84

Slide 84 text

O#% M!r%

Slide 85

Slide 85 text

Connections

Slide 86

Slide 86 text

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

Slide 87

Slide 87 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 88

Slide 88 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 89

Slide 89 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 90

Slide 90 text

P/($r%. Its great

Slide 91

Slide 91 text

D*+#$! ORM Its not so bad

Slide 92

Slide 92 text

5+#1.! http://www.speakerdeck.com/craigkerstiens