PostgreSQL can do that?

We will talk about some lesser known features of PostgreSQL and how it could be used while building our apps. We’ll talk about CTEs, Window functions, JSONB operators, full text search, schema search paths, aggregation with rollups/cubes and some psql tips.

PostgreSQL is the de-facto choice for many Rails developers. While we obsess about many architectural decisions about our Rails codebase and are enamoured by the expressiveness of Ruby we often fail to appreciate how much the database brings to the table. In this talk, we will be looking at some of the less know features of PostgreSQL and look at a few productivity tips dealing with psql.

• CTEs - Common Table Expressions allow you to write modular SQL code. We will also look at its recursive cousin to deal with tree or graph based models.
• Window Functions - Window functions allow you to operate over a window of partitioned data in a query without using subqueries. It can be used to compute aggregates within a partition or even access previous and next rows within a partition.
• JSONB - Sometimes generating JSON in postgreSQL can be more efficient than generating JSON from the application layer. We will be looking at an example of using array and row functions in combination with JSONB operators to generate JSON right from PostgreSQL.
• Full text search - PostgreSQL offers GIN indices and ts_vector and ts_query capabilities. This can be used to implement full text search with PostgreSQL without having to rely on Algolia, Solr or ElasticSearch.
• Schema Search Paths - Ever had the requirement to slightly change the schema for a customer while keeping a different schema for another customer in a SaaS app? Well schema search paths allow you to load different versions of the tables and keep the data separate without you having to rewrite queries.
• ROLLUP and CUBE - Grouping Sets, Rollup and cubes can provide pivot table like analytical capabilities right in PostgreSQL
• psql tips - Do you know what \x, \dt+, \timing, \pset format, \n, \d, \dt+ and more do?

Here are some of my blog posts that I wrote about my experience with PostgreSQL.

• Managing Big Enough Data with PostgreSQL
• Creating PivotTables with PostgreSQL

Here are some of my other conference talks.