Database consulting and application development...

...because your data is your business

 
May 10, 2018, noon

PostgreSQL Replication at PerconaLive 2018

The slides from my talk on PostgreSQL Replication at PerconaLive 2018 are available.


Posted by Christophe Pettus
April 10, 2018, 6:36 p.m.

Change these: wal_level and wal_hint_bits

(An intermittent series on PostgreSQL parameters whose default settings you should change.) wal_level = logical wal_level controls how much information is written into the write-ahead log. Higher settings write more information, enabling more features, at the expense of (somewhat) larger write-ahead log volume. Just set it to logical, the highest setting. The incremental volume is […]


Posted by Christophe Pettus
April 6, 2018, 8 a.m.

Change these: Logging

(An intermittent series on PostgreSQL parameters whose default settings you should change.) Here’s my preferred logging configuration: log_destination = 'csvlog' logging_collector = on log_directory = '/var/log/postgresql' log_filename = 'postgresql-%Y%m%d-%H%M%S.log' log_rotation_size = 1GB log_rotation_age = 1d log_min_duration_statement = 250ms log_checkpoints = on log_connections = on log_disconnections = on log_lock_waits = on log_statement = 'ddl' log_temp_files = […]


Posted by Christophe Pettus
April 3, 2018, 1 p.m.

Change these: archive_mode and archive_command

(An intermittent series on PostgreSQL parameters whose default settings you should change.) By default archive_mode is set to off, and thus archive_command is ignored. Even if you are not going to be using WAL archiving, you should change these. You can use settings such as: archive_mode = on archive_command = '/bin/true' It requires a server […]


Posted by Christophe Pettus
March 30, 2018, 9:28 p.m.

Change This: wal_compression

(An intermittent series on PostgreSQL parameters whose default settings you should change.) Introduced in PostgreSQL 9.5, wal_compression is off by default, but you should probably turn it on. First, what does it do? The documentation helpfully explains: When this parameter is on, the PostgreSQL server compresses a full page image written to WAL when full_page_writes […]


Posted by Christophe Pettus
March 27, 2018, 6:12 p.m.

Mountpoints and the Single PostgreSQL Server

Ultimately, a PostgreSQL database is just files, and those files have to be stored somewhere. Absent tablespaces, they are all stored in a single directory structure, traditionally called PGDATA. While smaller instllations can just use the defaults associated with the packaging, larger databases are often installed on their own volume with its own mountpoint. Two […]


Posted by Christophe Pettus
March 20, 2018, 6:28 a.m.

Why PostgreSQL is Terrible? at Nordic PGDay

The slides from my talk at Nordic PGDay, Why PostgreSQL is Terrible, are now available.


Posted by Christophe Pettus
Jan. 2, 2018, 10 p.m.

A Replication Cheat-Sheet

So many types of replication, so little time! What kind of PostgreSQL replication should you use?


Posted by Christophe Pettus
Oct. 30, 2017, 2 p.m.

Commitment Issues

One of the essentials of any database system is that a transaction is either in progress, committed, or rolled back. But consider what happens if… BEGIN; UPDATE table SET money=money+100000; COMMIT; -- And you get an error that the server has disconnected Did that transaction commit, or didn’t it? You don’t really know, because you […]


Posted by Christophe Pettus
Oct. 27, 2017, 1:29 a.m.

Streaming replication stopped? One more thing to check.

We recently were asked by a client to look at a strange replication situation. Replication had simply stopped from a primary to a streaming replication secondary. Everything looked fine: The secondary was connected to the primary, as we could see in pg_stat_replication. The secondary was not receiving any queries, so delays in applying incoming page […]


Posted by Christophe Pettus
Oct. 27, 2017, 1:06 a.m.

“Humans do not have a primary key” at PGConf.EU

The slides for “Humans do not have a primary key” as presented at PGConf EU are available here. Three links from the presentation are: Personal names around the world Kudzu and the California Marriage Amendment General Data Protection Regulations in the EU


Posted by Christophe Pettus
Sept. 7, 2017, 10:19 a.m.

“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: Personal names around the world Kudzu and the California Marriage Amendment


Posted by Christophe Pettus
Sept. 5, 2017, 8:51 a.m.

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 […]


Posted by Christophe Pettus
Sept. 4, 2017, 1:18 a.m.

Blog Moved.

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


Posted by Christophe Pettus
June 9, 2017, 11:27 a.m.

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 […]


Posted by Christophe Pettus
June 7, 2017, 3:03 p.m.

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, […]


Posted by Christophe Pettus
April 28, 2017, 9 a.m.

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.


Posted by Christophe Pettus
March 31, 2017, 9:34 a.m.

“Corruption War Stories” from PGConf US 2017

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


Posted by Christophe Pettus
March 31, 2017, 9:33 a.m.

“Django and PostgreSQL” from PGConf US 2017

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


Posted by Christophe Pettus
March 29, 2017, 7:08 a.m.

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.


Posted by Christophe Pettus
Feb. 7, 2017, 2:14 a.m.

Corruption War Stories at PGDay FOSDEM 2017

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


Posted by Christophe Pettus
Jan. 4, 2017, 11 a.m.

Django: dictionary update sequence element #0 has length 1; 2 is required

dictionary update sequence element #0 has length 1; 2 is required usually means a missing name='' in a urlconf.


Posted by Christophe Pettus
Jan. 4, 2017, 9 a.m.

Estimates “stuck” at 200 rows?

If you are seeing the planner estimating 200 rows exactly from an aggregate operation, that might mean that a table is missing planner statistics.


Posted by Christophe Pettus
Jan. 1, 2017, 3:53 p.m.

Django: Site matching query does not exist

Site matching query does not exist means you forgot the record in djangosite, or your forgot SITEID.


Posted by Christophe Pettus
Dec. 30, 2016, 12:50 p.m.

The Multi-Column Index of the Mysteries

Multi-column indexes are more subtle than you might think.


Posted by Christophe Pettus