The future of PostgreSQL in Django Ponies like to play with elephants

PostgreSQL • Most popular database for Django projects • Favoured by most of the core team (including those who don’t get to use it!) • Wide feature set • Fast moving, true open source

PostgreSQL and Django • Multiple third party projects • Various levels support and maintenance • Some features hard due to restrictions in Django

Kickstarter • … • WOW • £14k raised, including all stretch goals • (except the hidden secret goal!)

THANK YOU!

THANK YOU! - Paul Andrew Hallett - David Cramer - Christian Bertschy - Ace Gopher - Akita Noek - Loic Bistuer - James Tauber - Nils Bunger - Jannis Leidel - Robert Coup - Fusionbox - adonm - Scott Nixon - Russell Keith-Magee - Max Arnold - Taifu - Mirumee Labs - Jon Evans - Michael Gerstle - jdelic - Senko Rašić - Gregory Favre - Omer Katz - Denis Cornehl - Robert Stein - Darrell Bishop - Aleksander Prokofiev - Nephila - Giuseppe Ciotta - Nelen & Schuurmans - Yves Serrano - Simon Riggs - Christophe Pettus - Streema, Inc. - Chris Heisel - Dan Davison - Rene Nejsum - evonove - Jay - Chris Clark - ORDOQUY Xavier - Chris Cogdon - Benjamin Wohlwend - Stefan Foulis - Markus Holtermann - Alex Gaynor - Darian Moody - Andrew Ingram - Jorge Bastida - Dan Fairs - John Carr - Andrew Godwin - Ryan Kaskel - Claire Reynaud - Tomek Paczkowski - Sander Steffann - Aymeric Augustin - Joseph Kocherhans - Sebastian vetter - Tom Eastman - João Paulo Dubas - jnphilipp - Baptiste Mispelon - Ludovic Havel - Craig Bruce - Joel Crocker - Daniel Sears - Tomas Vondra - Bertrand Bordage - Chris Streeter - Justin Wing Chung Hui - Omen - Nicolas Champsavoir - Ivor Bosloper - Keng Susumpow - lom276 - Hilbert Schraal - Rudy De Volder - Wolfgang Doll - Craig Labenz - Krille - Daan Vielen - Chris Ward - Angel Velásquez - Martin Brochhaus - W.B.Hill - Laurent Paoletti - hjwp - Goetz Buerkle - Bruno Renié - Arthur Haney - Dawnstar Australis - Chris Hunt - George Hickman - Aaron Bassett - Steve Jalim - Ulrich Petri - JLinden - steko - Brent Hagany - Chris Drackett - Scanner Luce - Josh Drake - Jure Cuhalev - Mathias Kahl - Bruce Stringer - palewire - vasa.chi - Joris - Derek Payton - Ruben - Timo-Pekka Yrjola - Brenton Cleeland - Jason Fehr - Ian Foote - Christoph Heer - Peter Kropf - Daniel Lindsley - Denis Iskandarov - Daniel Johansson - Jirka Vejrazka - Gert Van Gool - Sergey Kopylov - erroneousboat - Brian Rosner - Miguel Pilar - Julian Wachholz - Jim Ray - Mathieu Meylan - Faisal Akber - Eduardo Felipe Castegnaro - Kevin Way - Gheesh - Joseph Server - Anselm Lingnau - Patrick Craston - James Skarie - Patrick Taylor - Jacob Haslehurst - Jon Fedyk - Frederic Tschannen - Jesús Espino - Tomas Jacobsen - Andrés Moya - Anirudh Surendranath - loci - Tomas Sirny - fm 10.4 - Botify Labs - Markus Zapke- Gründemann - Petr Marhoun - Henrik Saksela - Алексей - Yousef Bonashi - iynaix - Martin - Matt Broach - John Roth - Vincent Hamilton - Bryan Veloso - John Pena - Anand Kumria - Patrick Arminio - José Sánchez Moreno - Peter Sanchez - Simon Hewitt - DavidBarragán Merino - Alasdair Nicol - Ales - histyleparadox - Chad Johnson - Yann Eves-Hollis - harijay - markpasc - Corey Farwell - Dave Gaeddert - Doug - Peppe Bergqvist - Ryan Pitts - marteinn - Stephen Newey - Colin Rolfe - Nicolas Dubois - Jeroen Dekkers - Humphrey Murray - Richard Zak - Andrew Gorcester - Daniel Greenfeld - Dave Lane - Paul Labedan - Ogre - Eleni Lixourioti - Sebastian Brandt - Peter Inglesby - Patryk Zawadzki - Sidney Richards - James Cleveland - Pedro-Juan Ferrer - Kevin Seelbach - Roy Firestein - Eugene - Athanassios Diacakis - George London - Nathaniel Stanley - elleryq - Tomas Thor Jonsson - Maziar Zamani - Solomon Matthews - Peter Hyben - Alexey Zotov - Jose Padilla - Matthew Peterson - Mikhail Mitrofanov - Arjen Vrielink - Koh Chuan Yeong - Luciano Ramalho - Denis Simonet - Danilo - Yohann - Colin Coghill - StartUp Genesis - Craig de Stigter - Alexander - Joshua Johanan - Jonathan Anders - Robert McHardy - garretraziel - Adam James Forster - Alejandro Alonso Fernández - John Sutherland - Mikhail Priver - Valder Gallo - Skomli - Ralph Broenink - Francesc Ortiz - Tom Christie - Dana Spiegel - Patryk Ściborek - Krzysztof Szularz - Rolf Erik - Eyad Toma - Adrian Smith - Christoph Schindler - Paul Davis - Ankhbayar - Ethan Glasser-Camp - Valentin V. Bartenev - Rooney - Kenneth Wee - Meir Kriheli - Will Boyce - Ramana Varanasi - Chris Horsley - Petar Bukvic - Richard Eames - Devang Mundhra - Kurt Ostergaard - Tom Atkins - kilobug - Christophe Narbonne - Anton Egorov - Ryan Blunden - Robert Dollinger - Casper S. Jensen - Christian Lange - dpwiz - Taymon - Andreas Madsack - Kevin McLaughlin - Charles Denton - Alexander Brandt - Monte Milanuk - Michael Miller - Rodolfo De Nadai - mike smith - Jayaseelan Yezhuaralai - James Walker - Ben Beecher - Alec Perkins - Joshua Bonnett - Kiwi - Andy Byers - Sebastian - Anthony Castillo - Dave Arter - Nimish Telang - Steve Mumford - cob Kaplan-Moss - Ahren Pfrehm - schwanksta - Ross Poulton - Михаил Федосов - Milo Casagrande - Stephan Huiser - Andreas Rieger - Anna Warzecha - HakTom - Kevin Samuel - Florian Apolloner - Rogelio - Jacek Kołodziej - Detlef Lannert - Florian Kaiser - Andrey Nelubin - Brendan Smith - Marwan Alsabbagh - Tomohiro Otsuka - Paul Solomon - Agostino - Jane Pronina - Nick - Anton Medvedev - Angel Freire - Les Dodd - Carsten Steckel - Kate Seamer - John O'Nolan - Fadi Samara - urijah - Rémy Sanchez - Andrew Kuchling - Nimrod Shkedy - Uzi Halaby-Senerman - Rob Messick - Jan W

Kickstarter • Progress: Proposed incomplete version of first feature • Most work to take place over July and August 2014

Data types • Array • HStore • JSON • enum • UUID • Interval, Range

Array • Lists of just about any data type • Nested structures • No relational data

HStore • Key-value store • Strings only • Similar to well known django-hstore (especially the Django 1.7 based release)

JSON • Third party ecosystem messy - at least 5 different packages • json native data type very limited • jsonb planned for PG 9.4 • Likely Django 1.9 - jsonb only

enum • “Enumerated data type” • Choices enforced at database level • Field will provide python constants

UUID • For when ids are not sequential • Possible delegation

Interval & Range • Interval = timedelta • Range = start_foo and end_foo

Custom Lookups • New in Django 1.7 • Lookup or Transform • Currently only supported in .filter()

Custom Lookups • Date based lookups • Date based transforms! • Optimised queries

Custom Lookups • .filter(array__0=‘Hello’) • .filter(array__startswith=‘Hello’) • .filter(array__text__startswith=‘Hello’)

Indexes • B-tree, hash, GiST, GIN • Expression indexes • Partial indexes

Indexes class DjangoCon(Model): start_date = DateField() country = CharField() ! class Meta: indexes = ( BTree('start_date'), 'country__lower', )

Indexes class Attendee(Model): conf = ForeignKey(…) attending_sprints = BooleanField() departure_time = DateTimeField() ! class Meta: indexes = ( BTree('departure_time', where=Q(attending_sprints=True) ), )

Views • Table-like object you can query • Materialized views stored on disk

Views class Sprinters(View): class Meta: queryset = Attendee.objects.filter( attending_sprints=True ).values( 'name', 'departure_time', )

The future? • Constraints • Server side cursors • Prepared statements • Native table inheritance • “Clean” GIS implementation

Marc Tamlyn @mjtamlyn postgres.mjtamlyn.co.uk