By Andrew Kiellor
At Earnest, we use Postgres a lot. The original codebase we lovingly call the “Monolith” was built with it, new microservices use it, and some internal services have been migrating to it. This is largely due to the variety of features Postgres offers to organize, validate, and query our data. In this post, we’ll go through some of our favorite Postgres features.
Enum Types are a really underrated feature which allows you to list all the permissible values a column can contain.
The previous queries create a new type called `product_category` and a table `products` which has an `id` and `category` column.
Using the `product_category` enum instead of a text type informs any readers of the database with certainty that there are only three possible values present in the category column. Also, building services on top of the database is much simpler as there are less possible states for which the service needs to account.
Common Table Expressions
Common Table Expressions (or CTE’s) are an abstraction allowing you to bind the results of a query to a labeled temporary result set. This is really useful when building out a larger query as an alternative to subqueries:
The CTE statement above (the part starting with the `WITH` keyword) creates a temporary `avg_price_by_category` result set. This named result set is then referenced again when joining the average with each record from products with a matching category.
Common Table Expressions are an abstraction to help organize the query into named sections. This allows you to reason about different parts of the query independently.
Window Functions are a neat way to perform aggregations over a dataset without changing the number of rows in the dataset. This is performed by creating a window over which the aggregation is applicable and including the aggregated value for each record in the window. The following is a refactoring of the query from Common Table Expressions:
The above queries create a products table where each product has a category and price. The SELECT query uses a Window Function to construct the column `category_avg_price`, in this example the window is being created around each category and the average price is taken from the window and added to each record.
Window Functions allow you to write queries which associate aggregations constructed with any of Postgres aggregation functions to individual records in a performant manner.
Check Constraints allow you to encode restrictions or validations on your data in the database. These restrictions can drastically reduce the allowable states permitted by the database, which reduces the problem space programs using the database have to deal with.
Common check constraints could be:
- Ensure a number is always positive
- Ensure a text field is non-empty
- Ensure a column value is present if another value is defined
The above query creates a products table where the product price must always be positive. If any query tried to insert a record less than zero, the insert would fail. This provides a guarantee for any program working with the table that it will always receive a positive product price.
Domains are in some ways very similar to Check Constraints, but instead of attaching the constraint to a table, a new type is created with the constraint applied implicitly. Using domains avoids the problem of having to define the same constraint logic on every table a particular type of data will reside. The following is a refactoring of the example previously used above for Check Constraints:
Then we could use this type in any table requiring a money column, for example:
The use of domains in this case allows us to abstract the constraint logic away behind a type, both making our intent clearer and consolidating the logic to a single place.
Postgres has an extensive list of features and in this post we’ve gone through just a few we have come to use on a regular basis. At Earnest, we are constantly learning new ways to get the most out of our database and are always down to share the knowledge, whether it be through our internal #sql slack channel, pairing, code review, or blogging.