27 October 2017
7 September 2017
I’ll be presenting “Humans do not have a primary key” at PGOpen SV today. Two links from the presentation are:
5 September 2017
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
DATE). And always store data as a time zone-aware type, unless there is a very compelling reason to do otherwise.
4 September 2017
This is probably no big deal to anyone, but the blog has moved to a new server. Whee!
9 June 2017
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
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
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
The slides from my presentation, Corruption War Stories, are now available.
The slides from my presentation, Django and PostgreSQL, are now available.