PostgreSQL Workshop

Foreword

This repository contains a number of exercises about some not so well-known PostgreSQL features. It may be interesting for application developers and database administrators.

Authors

• Alberto Romeu · Full Stack developer at CARTO
• Jorge Sanz · Solutions engineer at CARTO

About CARTO

Founded in 2012 by a team of experts in geospatial development, big data analytics, and visualization techniques, CARTO is based in New York City and Spain, with additional locations in Washington DC, and Estonia. CARTO has a team of 100 employees, a portfolio of 1,200 customers including BBVA, BCG, NYC, and Twitter and more than 200,000 users over the globe. The company is backed by investors such as Accel and Salesforce Ventures.

CARTO leads the world of location intelligence, empowering any organization and individual to discover and predict key insights through location data. With CARTO’s intuitive location intelligence platform, analysts and developers build self-service location based apps that help optimize operational performance, strategic investments, and everyday decisions.

Contents

• Workshop set up
• psql tricks demo
• Visual EXPLAINs exercise
• PostGIS exercise
• Expression Based Indexes exercise
• Full Text Search exercise
• PL/Python - Python Procedural Language demo
• Non Relational Data exercise
• Declarative Table Partitioning exercise
• TOAST exercise

Further reading

When we brainstormed this workshop we came accross other topics that we decided to not include, this is the list of topics and some further reading resources we reserve for future editions of the workshop where they may be included.

• Foreign Data Wrappers
• LISTEN & NOTIFY
• Using RETURNING to get data from modified rows
• Window functions
• Continuous archiving of Postgres operations with wal-e
• Table inheritance
• A convenient connection pooling system with pgBouncer

Apart from those topics and the infinite number of resources you'll find out there we recommend also these resources:

• CARTO Engineering blog
• Paul Ramsey's blog, mostly about PostGIS
• Abel's excellent PostGIS/PostgreSQL tips & tricks
• Is PostgreSQL good enough?, great write up on many of the nice features we covered
• Commit 2016 talk: the ten most powerful queries (Spanish)
• Modern SQL, you probably already knew this one 😄

Data

You can get access to the datasets used on this workshop on this shared Google Drive folder. They are mostly stored in a format called Geopackage, which is a customized SQLite database you can manage with GIS software.

• Brooklyn MapPLUTO building footprints by the NYC Planning department
• Natural Earth countries and populated places by Nathaniel Vaughn Kelso et. al.
• The streets dataset is derived from the Spanish OpenStreetMap dump by geofabrik available here

Editions

This workshop has been delivered at:

• 2018-11-24 · CommitConf 2018 · Workshop page