DEV Community: Devin Clark

Set Postgres System Timezone

Devin Clark — Tue, 20 Oct 2020 00:00:00 +0000

By default, Postgres uses the system timezone as the default. This usually isn't what you want for a database on your local dev machine. Generally, the timezone should be UTC. The database not being in the same timezone as production will cause bugs that don't exist locally but do exist on production.

The most common answer I find when I'm searching for how to do this for the thousandth time is this:

SET TIME ZONE 'UTC';

This works, but will not persist across restarts.

The more persistent way to accomplish this is to modify postgresql.conf. To find where the postgresql.conf file is on your system, you can run this query in psql:

SHOW config_file;

Once you have found the location of the file, you can find this line in the file, uncomment it, and change the value to be 'UTC'.

#timezone = '(defaults to server environment setting)'

This is what the line should look like after the change:

timezone = 'UTC'

After making the change, we need to reload Postgres so the config change will take effect. This can be done by either running pg_ctl reload on the command line, or running SELECT pg_reload_conf(); from psql.

Using pg_repack in AWS RDS

Devin Clark — Tue, 13 Oct 2020 00:00:00 +0000

As your database keeps growing, there is a good chance you're going to have to address database bloat.

While Postgres 13 has launched with some exciting features with built-in methods to rebuild indexes concurrently, many people still end up having to use pg_repack to do an online rebuild of the tables to remove the bloat. Customers on AWS RDS struggle figuring out how to do this. Ready to learn how?

Since you have no server to access the local binaries, and because AWS RDS provides no binaries for the versions they are using, you're going to have to build your own. This isn't as hard as one might think because the official pg repos have an installer (ie: sudo apt install postgresql-10-pg_repack). If you don't use the repos, the project itself, is an open source project with directions: http://reorg.github.io/pg_repack/

While you were getting up to speed above, I was spinning up a postgres 10.9 db on RDS. I started it yesterday so that it would be ready by the time you got to this part of the post. Lets create some data:

-- let's create the table
CREATE TABLE burritos (
id SERIAL UNIQUE NOT NULL primary key,
title VARCHAR(10) NOT NULL,
toppings TEXT NOT NULL,
thoughts TEXT,
code VARCHAR(4) NOT NULL,
UNIQUE (title, toppings)
);

--disable auto vacuum
ALTER TABLE burritos SET (autovacuum_enabled = false, toast.autovacuum_enabled = false);


-- orders up
INSERT INTO  burritos (title, toppings, thoughts, code)
SELECT
  left(md5(i::text), 10),
  md5(random()::text),
  md5(random()::text),
  left(md5(random()::text), 4)
FROM GENERATE_SERIES(1, 1000000) s(i);


UPDATE burritos SET toppings = md5(random()::text) WHERE id < 250;
UPDATE burritos SET toppings = md5(random()::text) WHERE id between 250 and 500;
UPDATE burritos SET code = left(md5(random()::text), 4) WHERE id between 2050 and 5000;
UPDATE burritos SET thoughts = md5(random()::text) WHERE id between 10000 and 20000;
UPDATE burritos SET thoughts = md5(random()::text) WHERE id between 800000 and 900000;
UPDATE burritos SET toppings = md5(random()::text) WHERE id between 600000 and 700000;

(If you are curious how Magistrate presents bloat, here is a clip of the screen:)

Much like a human that has had that much interaction with burritos... our database has quite a bit of bloat. Assuming we already have the pg_repack binaries in place, either though compilation or installing the package on the OS, we now need to enable the extension. We've put together a handy reference for installing extensions to get you going.

pg_repack has a lot of options. Feel free to check them out, but I'm going to start packing:

/usr/local/bin/pg_repack -U greataccounthere -h bloatsy.csbv99zxhbsh.us-east-2.rds.amazonaws.com -d important -t burritos -j 4
NOTICE: Setting up workers.conns
ERROR: pg_repack failed with error: You must be a superuser to use pg_repack

This might feel like game over because of the implementation of superuser on RDS, but the trick is to take a leap of faith and add another flag (-k) that skips the superuser check:

/usr/local/bin/pg_repack-1.4.3/pg_repack -U greataccounthere -h bloatsy.csbv99zxhbsh.us-east-2.rds.amazonaws.com -k -d important -t burritos -j 4
NOTICE: Setting up workers.conns
INFO: repacking table "public.burritos"
LOG: Initial worker 0 to build index: CREATE UNIQUE INDEX index_16449 ON repack.table_16442 USING btree (id) TABLESPACE pg_default
LOG: Initial worker 1 to build index: CREATE UNIQUE INDEX index_16451 ON repack.table_16442 USING btree (title, toppings) TABLESPACE pg_default
LOG: Command finished in worker 0: CREATE UNIQUE INDEX index_16449 ON repack.table_16442 USING btree (id) TABLESPACE pg_default
LOG: Command finished in worker 1: CREATE UNIQUE INDEX index_16451 ON repack.table_16442 USING btree (title, toppings) TABLESPACE pg_default

It works! The table is feeling fresh and tidy and your application has a little more pep in its step. When using Magistrate our platform matrix also knows when you have pg_repack installed and gives you the commands to run for tables it detects with high bloat percentage.

Storing Timezones in Postgres

Devin Clark — Thu, 01 Oct 2020 00:00:00 +0000

Storing a user’s timezone in Postgres can be an interesting task in apps. Often, this is accomplished by generating a hardcoded list of every timezone in the backend (or worse the frontend JavaScript) and storing an item from that list in the database for each user. This hardcoded magic list is both difficult to build accurately and hard to maintain. Timezones change more frequently than you would imagine ...but less frequently than time itself.

Postgres has a system view called pg_timezone_names that has the list of timezones used by Postgres internally. We can use this view to generate a dropdown or other input type for the user to select their timezone. We can filter out some of the timezones in your query (like all the ones that start with “posix/“) because this list is quite large by default.

select name from pg_timezone_names where name not like 'posix%' and name not ilike 'system%' order by name;

Now, we need to figure out the best way to store the selection from our user. We can do this by adding a column to our users table to hold the timezone. The column is a text type because we are only storing the name value from pg_timezone_names.

alter table users add column timezone text;

Next, we want to query the user’s events in the timezone we have for the user. We store the start_time for the events as UTC timestamptz columns. This solution leverages the timezone function in Postgres. AT TIME ZONE zone also works. The last join in this query is optional but it is there in case you need to know the UTC offset or if the timezone is in DST.

SELECT events.name, timezone(users.timezone, start_time)
FROM events
JOIN users ON events.user_id = users.id
JOIN pg_timezone_names ON users.timezone = pg_timezone_names.name;

This could be taken a step further by using the to_timestamp function in Postgres to format the dates, and keep the application from formatting dates at all.

This article was originally published on Magistrate. Head over there if you like this post and want to read others like it.

Postgres 2020 Q3 Release

Devin Clark — Wed, 19 Aug 2020 00:00:00 +0000

Another patch for postgres has landed. This patch moves the latest secure version to pg 12.4, 11.9, pg 10.14 and pg 9.6.19. The highlight of this release is fixing CVE-2020-14349 and CVE-2020-14350, which allowed unintended privilege escalation under certain conditions.

For more details, check out the official release.

To learn more about Postgres 12 specific changes, click here.

To learn more about Postgres 11 specific changes, click here.

To learn more about Postgres 10 specific changes, click here.

To learn more about Postgres 9.6 specific changes, click here.

If you are a Magistrate customer, just update your magistrate package in your package manager and you'll have the new code to inform you if your postgres server is patched or not.

Case-Insensitive Text Columns in Postgres with citext

Devin Clark — Sat, 15 Aug 2020 00:00:00 +0000

Recently we heard from a friend that they were curious if there was a better way to work with case-insensitive text columns in their Postgres database. They were frustrated because they found a bug where part of their application considered the column to be case insensitive and another part did not.

The column we were asked about was an email column that would be used lookup users and would certainly benefit from an index. This got us thinking about indexing case-insensitive text. Typically, the column would be defined as a text type, and everywhere it is referenced you would call lower() on the value.

That has a few drawbacks though. You have to remember to call lower() on the value, a unique constraint on the column will be case-sensitive, and it will not use an index (unless it is an index on lower(email)).

So, let's start with a typical user lookup query.

select * from user where lower(email) = lower(?);

We can improve this by changing up our table schema a bit. We are going to leverage a Postgres extension to get access to a new column type, citext (case insensitive text).

But first, we need to check to see if the extension is available on our database. This can be done by running the following query on our database or by looking at the extensions tab of our magistrate dashboard.

select * from pg_available_extensions where name = 'citext';

We got a result from running that query so we know it is available on our database. Now we can install the extension to our database with this query.

create extension if not exists citext;

Now that we have all of that set up we can change email to use the citext type we just added.

alter table users alter column email type citext;

Now our user lookup can be simplified to this query because the citext type is handling the lower() calls.

select * from user where email = ?;

This change squashed our friend's bug, and now our friend knows about citext for future case-insensitive text needs!

citext is just one of the many Postgres extensions we love. If you liked this blog post, and want to learn more about what Magistrate has to offer, sign up for our mailing list at magistratehq.com.

Branching Out Into Hierarchy Data

Devin Clark — Wed, 15 Jul 2020 00:00:00 +0000

ltree is a Postgres extension that can give you and your application a lot of options around complex hierarchal relationships.

At some point in an applications development lifecycle, you can end up having to figure out how to represent hierarchal data. In hierarchal data, a root or 'parent' node can have 'child' nodes attached to it. For example, A (parent) -> B, C (children) or grilled cheese (parent) -> bread, cheese, mayo (children).

While these relationships can be stored in a variety of ways, such as raw strings, arrays, lookup tables, bitmasks, and so on... Postgres has a dedicated type and extension for this type of data called ltree.

To get started with ltree, check to see if it is available in your setup:

select * from pg_available_extensions where name = 'ltree';

If you see it listed as an available extension, you can install it:

create extension if not exists ltree;

Confirm installation:

select * from pg_extension where extname='ltree';

Note: Magistrate makes your available extension list just a click away.

Now we can build our first hierarchy:

create table menu (
  id serial,
  title text,
  ingredients ltree
);

insert into menu (title, ingredients) values ('basic', 'tortilla.cheese'), ('starter', 'tortilla.cheese.carne'), ('extra', 'tortilla.cheese.carne.pollo.guac'), ('setup', 'chips.salsa');

If I am curious what on the menu has a tortilla as the parent ingredient:

select * from menu where 'tortilla' @> ingredients;

If want something that has a tortilla and cheese:

select * from menu where 'tortilla*.cheese.*' ~ ingredients;

If I want something that has a tortilla and cheese, but not guac because of an allergy or preference:

select * from menu where 'tortilla.cheese.!guac' ~ ingredients;

One of the reasons you might lean towards a dedicated type for this problem is all the supporting functions and query options that go along with ltree.

With the simple examples above and tiny data set, we could any solution would likely work fine. However, with more complex relationships and datasets, using ltree can give you a lot more options including more intelligent indexing for performance, recursion on your queries, and more confident precision with lquery stanzas. Using ltree can also get you out of the trap of thinking you need to build your own handler for when you leave the trivial examples.

While Magistrate can't solve all your hierarchal data needs, it can certainly get you started in making sure the extension is available and helping you determine when you have a performance issue with your lquery work.

Gravatars and Postgres 12 using Generated Columns

Devin Clark — Wed, 08 Jul 2020 00:00:00 +0000

Generated columns are an exciting new feature of Postgres. They allow you to replace some of your existing trigger logic in a faster and easier to read way.

create table users (
  id serial primary key,
  name text not null,
  email text not null,
  avatar_url text generated always as ('http://gravatar.com/avatar/' || md5(email)) stored,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now());

The interesting part of this block of code is where the avatar_url column is being created. It is creating a new column that is generated and stored on disk using the value of another column (email). That also means if a user's email is updated, the avatar_url will also be updated with the correct Gravatar url. This functionality was added in Postgres 12.

Let's say we decide to show an avatar that is more optimized for our app. Gravatar allows you to pass a size parameter in pixels for the returned image. This can be accomplished with Gravatar by adding s=200 as a query parameter. All this takes is altering the column.

alter table users drop avatar_url;
alter table users add column avatar_url text generated always as ('http://gravatar.com/avatar/' || md5(email) || '?s=200') stored;

You might be wondering why you would store this value in the database instead of generating it in your app. Computing MD5 hashes isn't a very fast process (by design), especially when it needs to be done anytime an avatar needs to be displayed. Storing the calculated value will certainly speed things up.

For more information about the possible configuration of Gravatar URLs, see this article from the Gravatar documentation.

Rob Conery shared another use of generated columns using json columns that is worth checking out.

This is just one reason we love Postgres, but there are so many other things to love about it! If you liked this blog post, and want to learn more about what Magistrate has to offer, sign up for our mailing list!

Knex.js and Postgres

Devin Clark — Thu, 02 Jul 2020 00:00:00 +0000

Knex.js is a SQL query builder based on Laravel Query Builder. It supports a handful of different databases, most importantly Postgres. It has been my first choice for database interaction in JavaScript for years.

Connecting to Postgres

To start, we need to make a connection to our Postgres database.

const knex = require('knex');

const db = knex({
  client: 'pg',
  connection: {
    host: 'localhost',
    user: 'postgres',
    password: 'hunter3',
    database: 'bigdata03',
  },
});

You can pass a connection string instead like this (passing an environment variable would be better).

const db = knex({
  client: 'pg',
  // connection: 'postgresql://postgres:hunter3@localhost/bigdata03',
  connection: process.env.DB_CONNECTION,
});

Queries

Here is a typical select query to retrieve a record from our meal table.

knex
  .select('*')
  .from('meal')
  .where('id', 1)
  .then(function (meals) {
    console.log(meals);
    // [ { id: 1, description: 'Burrito', ... } ]
  });

Here is a more complex query that returns a few specific fields from our meal table, joins with the user table, filters down the data, sorts it by date, and then limits the results to 5 rows.

knex
  .select(['id', 'description', 'queso', 'user.name'])
  .from('meal')
  .join('user', 'user.id', 'meal.user_id')
  .where({
    queso: true,
    'user.id': 5,
  })
  .orderBy('created_at', 'desc')
  .limit(5);

If you ever need to see what SQL will be generated from a Knex query, you can append .toString() to the Knex query and it will return the SQL query as a string. This is very useful for debugging.

Transactions

Transactions are a way of keeping your data consistent. A transaction groups your queries together into a unit that can either be committed or rolled back. Committing a transaction writes the data to the database. Rollback aborts the update and does not write anything to the database. Knex has two different ways to use transactions. I generally prefer the following way so I don't have to manually call trx.commit and trx.rollback manually.

const meals = [
  { description: 'Burrito', queso: true },
  { description: 'Churro', queso: false },
  { description: 'Chips', queso: true },
];

knex.transaction(function (trx) {
  return Promise.all(
    meals.map(function (meal) {
      return trx.insert(Object.assign(meal, { user_id: 4 })).into('meal');
    })
  );
});

In this example, we are bulk adding a list of meals and if one of them fails, we want to rollback so we can try again. If any of the promises throw, Knex will rollback the transaction for us.

Inserts and Updates

Knex handles inserts and updates with a nice JavaScript flavor on the SQL syntax. Insert a meal into our database and return the id.

knex.insert({ description: 'Burrito', queso: false, user_id: 4 }).into('meal').returning('id');

Update the meal we just created because we definitely had queso.

knex.table('meal').where({ id: 20 }).update({ queso: true });

Migrations

Migrations are a great way to version your database structure changes. You will be able to run a command that runs all the necessary migrations to get your database schema up to date.

To start, you can run knex init to generate a knexfile. The knexfile is a config file that contains various settings, like your database credentials.

Next, you can run knex migrate:make add_meal to generate a migrate file. The generated file will look similar to the file below. There are two functions exposed, up and down. The up function is called when a migrate is run. The down function is called when a migrate is rolled back.

exports.up = function () {};

exports.down = function () {};

We can add to our migrate file by calling Knex’s createTable function in the up function. We also add id, description, queso, created_at, and updated_at columns to our meal table. In the down function, we do the opposite and drop the burritos table.

exports.up = function () {
  return knex.schema.createTable('meal', function (table) {
    // add an autoincrementing id column (serial type in Postgres)
    table.increments();
    // add a string column called description
    table.string('description');
    // add a boolean column to indicate whether the meal had queso.
    table.boolean('queso');
    // add created_at and updated_at columns with appropriate default values.
    table.timestamps();
    // create a foreign key that references the id column of the user table
    table.integer('user_id').references('user.id');
  });
};

exports.down = function () {
  return knex.schema.dropTable('meal'’);
}

To run the migrations and make the schema changes on the database, you run knex migrate:latest. If you need to rollback a migration, you can use knex migrate:rollback.