Getting time zones right when setting up PostgreSQL monthly partitioning

Mon Nov 28, 2016 by Manuel Reithuber in other

The world's time zones ()

I’m currently working on an event queue + log for account/device events.

For the event queue I’m using redis’ pub/sub commands. But for persistence, I want to use PostgreSQL (since SQL offers more query options).

Now since it’s pretty much log data (i.e. time based, it’s append only, old data will be purged), I’ll be partitioning the log data into monthly tables.

There are tons of examples on the internet on how to do that in PostgreSQL (for example this one on the official PostgreSQL wiki), but pretty much all of them use local time (timestamp without time zone, or timestamp for short), which causes trouble if you have clients using different time zones.

In this blog post I’ll describe:

the problem with timestamp without time zone
how partitoning is usually done
and how we can improve things by using timestamptz

Feel free to skip ahead to the third section if you’re just looking for the final implementation.

with time zone vs. without time zone

PostgreSQL has two timestamp types (and time types, for details see the PostgreSQL documentation on date/time types):

timestamp (= timestamp without time zone): stores local time (and assumes clients won’t ever use different time zones)
timestamptz (= timestamp with time zone): always stores UTC and returns local time (with a UTC offset suffix, e.g. +01)

So here’s the problem: The default timestamp data type doesn’t know or care about time zones at all. And that’s actually a requirement in the SQL standard (how that could’ve happened is beyond me…)

Let’s look at a quick example:

ondevice=# CREATE TABLE foo (
ondevice(#     ts TIMESTAMP,
ondevice(#     tstz TIMESTAMPTZ
ondevice(# );
CREATE TABLE

ondevice=# SHOW timezone;
   TimeZone    
---------------
 Europe/Vienna
(1 row)

ondevice=# INSERT INTO foo VALUES (now(), now()) RETURNING *;
            ts              |              tstz              
----------------------------+-------------------------------
 2016-11-27 23:18:31.408163 | 2016-11-27 23:18:31.408163+01
(1 row)

INSERT 0 1

I’ve created a simple table with one timestamp and one timestamptz field and inserted now() into both of them (while being in Austria).

Let’s pretend I traveled to New York:

ondevice=# SET SESSION TIME ZONE 'EST';
SET

ondevice=# SELECT * FROM foo;
            ts              |              tstz              
----------------------------+-------------------------------
 2016-11-27 23:18:31.408163 | 2016-11-27 17:18:31.408163-05
(1 row)

The timestamp field still prints the same value (timestamp basically means local time wherever you are right now), while the timestamptz stored the value as expected (the only difference is that it’ll display it in its EST form now).

Ok, so as long as we’re using ___ with time zone types, we’re fine?

Not quite. All of PostgreSQL’s date functions give you local time results, no matter if you’re using timestamptz as parameter.

To work around that, we’ll have to use the timezone('utc', tstz) function.
It converts timestamp to timestamptz (and back).

Let’s test that with a query:

# SELECT ts,
timezone('utc', ts) AS utc,
to_char(ts, 'YYYY-MM-DD') AS local_date,
to_char(timezone('utc', ts), 'YYYY-MM-DD') AS utc_date,
date_trunc('day', ts) as local_midnight,
timezone('utc', date_trunc('day', timezone('utc', ts))) as utc_midnight
FROM generate_series(now(), now()+ interval '1 day', '1 hour') AS ts
WHERE date_part('hour', timezone('utc', ts)) in (22,23,0,1,2);
              ts               |            utc             | local_date |  utc_date  |     local_midnight     |      utc_midnight      
-------------------------------+----------------------------+------------+------------+------------------------+------------------------
 2016-11-27 22:05:51.976332+01 | 2016-11-27 21:05:51.976332 | 2016-11-27 | 2016-11-27 | 2016-11-27 00:00:00+01 | 2016-11-27 01:00:00+01
 2016-11-27 23:05:51.976332+01 | 2016-11-27 22:05:51.976332 | 2016-11-27 | 2016-11-27 | 2016-11-27 00:00:00+01 | 2016-11-27 01:00:00+01
 2016-11-28 00:05:51.976332+01 | 2016-11-27 23:05:51.976332 | 2016-11-28 | 2016-11-27 | 2016-11-28 00:00:00+01 | 2016-11-27 01:00:00+01
 2016-11-28 01:05:51.976332+01 | 2016-11-28 00:05:51.976332 | 2016-11-28 | 2016-11-28 | 2016-11-28 00:00:00+01 | 2016-11-28 01:00:00+01
 2016-11-28 02:05:51.976332+01 | 2016-11-28 01:05:51.976332 | 2016-11-28 | 2016-11-28 | 2016-11-28 00:00:00+01 | 2016-11-28 01:00:00+01
(5 rows)

Ok, that query was a mouthful, let’s break it down:

generate_series(from,to, interval) is kinda like a for loop. We use it to step over the next 24 hours in hourly intervals.
the WHERE clause throws away anything but the few hours around midnight UTC
ts: the raw timestamp with timezone (timestamptz), expressed in your local time with a timezone offset suffix (in my case +01)
utc: the UTC time (expressed as timestamp without time zone)
local_date: shows the default behavior of PostgreSQL’s date functions (to act on local time)
utc_date: that’s the behavior we want for table partitioning (we get that by converting the timestamptz to it’s UTC timestamp equivalent)
local_midnight: again the default behavior, this time for date_trunc()
utc_midnight: the behavior we want for our check constraint. We simply convert to a UTC timestamp, set the time to 00:00:00.000000 and convert back to a timestamptz

As you can see, utc_date and utc_midnight behave the way we want.

So as long as we use timestamptz wherever possible and convert to timestamp at time zone 'UTC' when calling date functions, we should be fine.

Partitioning in PostgreSQL (the ‘common’ way)

There’s a great article in the official PostgreSQL documentation describing month based table partitioning. But again, it uses timestamp without time zone and date which makes it vulnerable to the issues descibed in the last chapter.

Here’s my version of their example code (I restructured it a bit to improve readability).

CREATE TABLE foo (
  id bigserial PRIMARY KEY,
  ts TIMESTAMP
);


CREATE OR REPLACE FUNCTION foo_create_partitions( DATE, DATE )
returns void AS $$
DECLARE
  partition_table text;
  month_start DATE;
BEGIN
  FOR partition_table, month_start IN SELECT
      'foo_' || to_char( d, 'YYYY_MM' ),
      date_trunc('month', d)::date
    FROM generate_series( $1, $2, '1 month' ) AS d
  LOOP
    EXECUTE format($query$
      CREATE TABLE IF NOT EXISTS %I (
        CHECK (ts >= '%s'::date AND ts < '%s'::date + interval '1 month')
      ) INHERITS (foo)
    $query$, partition_table, month_start, month_start);
    EXECUTE format($query$
      CREATE INDEX IF NOT EXISTS %I ON %I (ts)
    $query$, partition_table||'_ts', partition_table);
  END LOOP;
END;
$$
language plpgsql;


CREATE OR REPLACE FUNCTION foo_trigfn()
RETURNS TRIGGER AS $$
BEGIN
  EXECUTE format('insert into %I VALUES ($1, $2)', 'foo_'|| to_char(NEW.ts, 'YYYY_MM'))
    USING NEW.id, NEW.ts;
  RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER foo_partition_trigger
  BEFORE INSERT ON foo
  FOR each ROW EXECUTE PROCEDURE foo_trigfn();

It basically boils down to three parts:

create a parent table (the one we’ll insert to and query from, but that won’t contain any actual data)
a helper function to create partitions when needed (has to be invoked manually)
a trigger function to redirect the INSERT to that month’s partition.

In case you’ve tried out above code, here’s the cleanup code:

DROP TABLE foo CASCADE;
DROP FUNCTION foo_create_partitions(DATE,DATE);
DROP FUNCTION foo_trigfn();

Partitioning in PostgreSQL (the ‘right’ way)

Do do things properly, we’ll need to move our partition boundaries from midnight local time to midnight UTC (well, in you can use any time zone, as long as you stay consistent).

And we have to make sure our CHECK constraints match our trigger function, or we’d run into constraint violation issues. To save you the headache (and because I needed it anyway), I’ll write it all up here.

The first modification (creating the table) is easy. Simply use timestamptz:

CREATE TABLE foo (
  id bigserial PRIMARY KEY,
  ts TIMESTAMPTZ
);

–

The create_partitions function is a bit trickier. We’ll have to convert between local and UTC timestamps a couple of times. And instead of the DATE parameters, I’ll simply use timestamptz as well (generate_series() returns timestamps anyway).

And to be on the safe side, I recommend always creating one partition in advance (i.e. calling it with foo_create_partitions(now() - interval '1 month', now() + interval '1 month'))

CREATE OR REPLACE FUNCTION foo_create_partitions(TIMESTAMPTZ, TIMESTAMPTZ)
returns void AS $$
DECLARE
  partition_table text;
  month_start TIMESTAMPTZ;
BEGIN
  FOR partition_table, month_start IN SELECT
      'foo_' || to_char(timezone('utc', d), 'YYYY_MM'),
      timezone('utc', date_trunc('month', timezone('utc', d)))
    FROM generate_series( $1, $2, '1 month' ) AS d
  LOOP
    EXECUTE format($query$
      CREATE TABLE IF NOT EXISTS %I (
        -- your foreign keys/constraints go here
        CHECK (ts >= '%s'::timestamptz AND ts < '%s'::timestamptz + interval '1 month')
      ) INHERITS (foo)
    $query$, partition_table, month_start, month_start);
    EXECUTE format($query$
      CREATE INDEX IF NOT EXISTS %I ON %I (ts)
    $query$, partition_table||'_ts', partition_table);
  END LOOP;
END;
$$
language plpgsql;

–

And to update the trigger function:

CREATE OR REPLACE FUNCTION foo_trigfn()
RETURNS TRIGGER AS $$
BEGIN
  EXECUTE format('insert into %I VALUES ($1, $2)', 'foo_'|| to_char(timezone('utc', NEW.ts), 'YYYY_MM'))
    USING NEW.id, NEW.ts;
  RETURN NULL;
END;
$$
LANGUAGE plpgsql;

The CREATE TRIGGER call remains the same:

CREATE TRIGGER foo_partition_trigger
  BEFORE INSERT ON foo
  FOR each ROW EXECUTE PROCEDURE foo_trigfn();

So far so good, but does it work?

I’m writing this on 2016-11-28 01:07:18.287326+01, so a new UTC-month is just a little less than 3 days away.

So let’s put some data into the table and check if our code works:

ondevice=# SELECT foo_create_partitions(now() - interval '1 month', now() + interval '1 month');
foo_create_partitions
-----------------------

(1 row)

ondevice=# INSERT INTO foo(ts) VALUES (now() + interval '3 days');
INSERT 0 0
ondevice=# INSERT INTO foo(ts) VALUES (now() + interval '2 days 23 hours');
INSERT 0 0
ondevice=# INSERT INTO foo(ts) VALUES (now());
INSERT 0 0

ondevice=# select tableoid, *, timezone('utc', ts) as utc from foo ORDER BY ts;
 tableoid | id |              ts               |            utc             
----------+----+-------------------------------+----------------------------
   215037 |  4 | 2016-11-28 01:07:18.287326+01 | 2016-11-28 00:07:18.287326
   215037 |  3 | 2016-12-01 00:07:09.349506+01 | 2016-11-30 23:07:09.349506
   215043 |  2 | 2016-12-01 01:06:59.838776+01 | 2016-12-01 00:06:59.838776
(3 rows)

Well, as you can see, only id=2 is stored in the foo_2016_12 partition.

Some notes

You’ll probably want to add code to delete old partitions to you foo_create_partitions() function
You could call the foo_create_partitions() function from within the trigger to avoid having to call it manually (e.g. in a scheduled task), but there’s a small performance penalty for calling it on every inserted row.
There’s no point in adding foreign keys or other constraints to the parent table. Put them where I’ve put the your foreign keys/constraints go here placeholder instead.

Why bother, you ask?

Well it’s true, most applications out there will work fine if you use the regular timestamp type, but as soon as you use a client that’s in a different time zone than you’d expect (whether you expand operations to another country or simply forgot to configure a new server’s time zone), you might put your data in an inconsistent state (and break the assumption that time only ever moves in one direction).

Pretty much all guides on how to do time based partitioning in PostgreSQL simply use local times, when a proper, time zone aware solution doesn’t add complexity but might avoid serious headaches in the future.

And the with/without time zone issue doesn’t just affect table partitioning, (as we’ve seen in the first chapter) so I urge you to use the with time zone types wherever possible.

Image credit: User TimeZonesBoy on Wikimedia Commons - CC-BY-SA-4.0