---
title: "Things to avoid doing when using Postgres"
publication: "Postgres Weekly"
issue: 306
published_at: "2019-05-22"
canonical_url: "https://postgresweekly.com/issues/306"
---

The latest Postgres news, articles, tools, and projects.       

#306 — May 22, 2019 

[Read on the Web](https://postgresweekly.com/issues/306)   

Postgres Weekly 

[![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/v1558468462/t1lhkqvr0rd77n10qwn5.jpg)](https://wiki.postgresql.org/wiki/Don%27t_Do_This)   

[Don't Do These Things in Postgres](https://wiki.postgresql.org/wiki/Don%27t_Do_This) — An interesting page on the official Postgres wiki that attempts to round up a variety of ‘common mistakes’ in using Postgres, such as _“Don’t use `char(n)`”_ and _“Don’t use `serial`“_. Some of these are opinionated, but are well backed up with reasons. 

Postgres Wiki   [![\](https://copm.s3.amazonaws.com/21bcbf9c.png)](https://postgreslondon.org/) 

[PostgresLondon 2019 Schedule Out Now 🇬🇧 🐘](https://postgreslondon.org/) — Join us in London for the evolution of PGConf UK to learn more about recent achievements & exciting future developments in PostgreSQL. You can also participate in hands-on training with experts covering: Performance Tuning, PostgreSQL Security, & Multi-master Replication. 

2ndQuadrant PostgreSQL Events sponsor   

[Using `pg_cron` to Automatically Schedule Database Tasks](https://fluca1978.github.io/2019/05/21/pgcron.html) — [pg_cron](https://github.com/citusdata/pg_cron) is a Postgres extension that runs a cron-like job scheduler _within_ a running Postgres instance allowing you to run commands of your choice at defined intervals or times. 

Luca Ferrari   

[A Look at RUM Indexes](https://habr.com/en/company/postgrespro/blog/452116/) — A few weeks ago we linked to [an amazing introduction to GIN indexes](https://habr.com/en/company/postgrespro/blog/448746/), now the same author is back with a similar deep dive into the world of RUM, essentially an extended type of GIN index for specific use cases. It’s not a built-in index type, however. 

Egor Rogov   

[Abusing `SECURITY DEFINER` Functions](https://www.cybertec-postgresql.com/en/abusing-security-definer-functions/) — Functions defined with `SECURITY DEFINER` have the privileges of the user that _created_ the function which, naturally, has security consequences. Here’s a look at how even a seemingly ‘harmless’ function can go awry. 

Laurenz Albe   

[A Health Check Playbook for Your Postgres Database](https://www.citusdata.com/blog/2019/03/29/health-checks-for-your-postgres-database/) — As data comes in and queries are executing, the composition of your database will change - here, Craig suggests some things you can do frequently to ensure the long term health of your database. 

Craig Kerstiens   

[Getting The Most Out of Your Postgres Indexes](https://pgdash.io/blog/postgres-indexes.html) — Quick examples and tips around covering indexes, partial indexes, multi-value indexes, and more. 

RapidLoop   

[Driving Time-Series Database Performance with PostgreSQL & TimescaleDB](https://severalnines.com/blog/advanced-database-monitoring-management-timescaledb?utm_campaign=DevOps_Campaign_MAY19&utm_content=pgweekly&utm_medium=Paid_Search&utm_source=banner) — Learn how ClusterControl can monitor, manage, and ensure the performance of your time-series data. 

Severalnines sponsor   

[Considering Multi-Column Indexes](https://medium.com/pgmustard/multi-column-indexes-4d17bac764c5) — A look at some of the downsides to multi-column indexes and how to consider if they fit your use case. 

David Conlin   

[How to Deploy Postgres to a Docker Container with ClusterControl](https://severalnines.com/blog/how-deploy-postgresql-docker-container-using-clustercontrol) 

Sebastian Insausti (Severalnines)   

[pgBackRest: A Year of Growth for a 'Great Backup Solution'](https://www.percona.com/blog/2019/05/10/pgbackrest-a-great-backup-solution-and-a-wonderful-year-of-growth/) — _“pgBackRest addresses many of the must-have features that you’ll want to look for in a PostgreSQL backup solution.”_ 

Jobin Augustine       supported by [![GitPrime\](https://res.cloudinary.com/cpress/image/upload/v1554813939/kwagarrsergds4sxgjr9.png)](https://resources.gitprime.com/books/20-patterns/?utm_source=nl%28pgw%29&utm_medium=email-nl&utm_campaign=nl%28pgw%29)  

💡 Tip of the Week 

Domains: A way to bundle up check constraints and defaults into simple types 

A _domain_ is a lightweight data type that has its own optional default value and constraints and is an SQL:2003 feature that has been supported in Postgres for several years now. 

For example, let's say you wanted to store an adult age (>18) on a table, but rather than use an integer and a check constraint, you could create your own 'domain' to the same ends:  

CREATE DOMAIN adult_age AS INTEGER CHECK (VALUE >= 18);   

This new `adult_age` type/domain can now be used in a table in the usual way:  

CREATE TABLE adults (name text, age adult_age);
 
 INSERT INTO adults VALUES ('Ahmed', 17);
 → ERROR: value for domain adult_age
 violates check constraint
 "adult_age_check"   

You can add constraints or default to a domain (using [`ALTER DOMAIN`](https://www.postgresql.org/docs/9.3/sql-alterdomain.html)). For example, to set a default age to our type:  

ALTER DOMAIN adult_age SET DEFAULT 18;   

There's more information on [the domain feature in Postgres' docs.](https://www.postgresql.org/docs/9.5/sql-createdomain.html) 

This week's tip is sponsored by _[GitPrime](https://resources.gitprime.com/books/20-patterns/?utm_source=nl%28pgw%29&utm_medium=email-nl&utm_campaign=nl%28pgw%29)_. Get your copy of their new field guide **['20 Patterns to Watch for in Engineering Teams'](https://resources.gitprime.com/books/20-patterns/?utm_source=nl%28pgw%29&utm_medium=email-nl&utm_campaign=nl%28pgw%29)**, filled with actionable insights to help debug your development process with data.      

🗓 Upcoming Postgres Events 

- [PGCon 2019](https://www.pgcon.org/2019/) (May 28-31 in Ottawa, Canada)
- [PostgreSQL Ibiza](https://www.pgibz.io/index.html) (Jun 19-21 on Ibiza) — Spain's famous _party island_ promises you both "Postgres on the beach" while still having talks and training in a neat looking venue.
- [Postgres Vision 2019](https://postgresvision.com/) (June 24-26 in Boston, MA)
- [PostgresConf Beijing 2019](https://postgresconf.org/conferences/Beijing) (July 3-6 in Beijing, China)          

Curated by [Cooperpress](https://cooperpress.com/) and [Craig Kerstiens](https://twitter.com/craigkerstiens). 

[Change your email address](https://postgresweekly.com/edit_subscription/*|UID|*) or[Stop getting this newsletter](https://postgresweekly.com/leave/*|UID|*) 

© Cooper Press Ltd · Fairfield Enterprise Centre, Louth, LN11 0LS, United Kingdom