The Build

7 September 2017

10:19

“Humans do not have a primary key” at PGOpen SV

I’ll be presenting “Humans do not have a primary key” at PGOpen SV today. Two links from the presentation are:

5 September 2017

08:51

A brief reminder about casting TIMESTAMPTZ

I spent a few minutes worrying I had lost my mind: Results coming back to my application were different from those obtained by running psql directly on the database server, for identical queries.

As is often the case, the problem was time zones.

In particular, when casting a TIMESTAMPTZ to a DATE, the current client time zone setting is used. So:

xof=# BEGIN;
BEGIN
xof=# set time zone 'UTC';
SET
xof=# select now()::date;
    now     
------------
 2017-09-05
(1 row)

xof=# set time zone 'Australia/Lord_Howe';
SET
xof=# select now()::date;
    now     
------------
 2017-09-06
(1 row)

xof=# COMMIT;
COMMIT

(Extra credit for knowing why I wrapped this example in a transaction block!)

Just a reminder to be very careful when converting items from time zone-aware formats (like TIMESTAMPTZ) to non-aware ones (like TIMESTAMP or DATE). And always store data as a time zone-aware type, unless there is a very compelling reason to do otherwise.

4 September 2017

01:18

Blog Moved.

This is probably no big deal to anyone, but the blog has moved to a new server. Whee!

9 June 2017

11:27

shared_buffers is not a sensitive setting

There’s a lot of mystery-cult information floating around out there about what to set shared_buffers to in PostgreSQL. Most of it is, at best, overthinking a fairly simple setting.

You can get 90% of the way there, on 95%+ of PostgreSQL systems, just by setting it to 25% of total system memory, to a maximum of 32GB. (It used to be 8GB, and then 16GB; as processors get faster, the number tends to go up.)

In particular, the difference between 24GB and 32GB, for example, will almost certainly be unmeasurable, on a system with 128GB or more memory.

It can be of benefit to lower shared_buffers on systems that have a high write rate, to reduce the total amount of data flushed on one checkpoint. I don’t have analytic values for that (shame on me!), but I’ve seen benefits by reducing it to as low as 2GB on a very high write-rate system.

But don’t agonize.

7 June 2017

15:03

Resetting the postgres user’s password

For some reason, this is something that even experienced PostgreSQL people don’t know about, which is: What do you do if you’ve forgotten the postgres user’s password, and you have no other superuser available?

The answer is: Bring up PostgreSQL in single-user mode, reset the password, bring it back up in standard mode.

Of course, this requires access to the shell on the machine running PostgreSQL.

28 April 2017

09:00

A PostgreSQL Response to Uber

The slides from my talk at Percona Live 2017, A PostgreSQL Response to Uber, which I also gave as “Why Uber Was (Mostly) Wrong” at PGDay Nordic 2017, are now available.

31 March 2017

09:34

“Corruption War Stories” from PGConf US 2017

The slides from my presentation, Corruption War Stories, are now available.

09:33

“Django and PostgreSQL” from PGConf US 2017

The slides from my presentation, Django and PostgreSQL, are now available.

29 March 2017

07:08

PostgreSQL When It’s Not Your Job

My slides from my PGConf US 2017 tutorial, PostgreSQL When It’s Not Your Job, are available now.

7 February 2017

03:14

Corruption War Stories at PGDay FOSDEM 2017

The slides for my talk Corruption War Stories are now available.

« Older Entries