Database consulting and application development...

...because your data is your business

 
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
Dec. 28, 2016, 7:24 p.m.

A Cheap and Cheerful Replication Check

Don't calculate log positions unless you have to.


Posted by Christophe Pettus
Nov. 3, 2016, 6:51 a.m.

Securing PostgreSQL at PGConf EU

The slides for my talk, Securing PostgreSQL at PGConf EU 2016 are now available.


Posted by Christophe Pettus
Nov. 2, 2016, 3:03 a.m.

Unclogging the VACUUM at PGConf EU

The slides for my presentation Unclogging the VACUUM at PGConf EU in Tallinn, Estonia are now available.


Posted by Christophe Pettus
June 4, 2016, 9:34 p.m.

Recent Slides

Slides from my recent presentations at PGCon and PyCon.


Posted by Christophe Pettus
Feb. 16, 2016, 10:51 a.m.

Always Do This #5: The Elements of postgresql.conf Style

I won't say that I know the one true way of editing postgresql.conf, but I do.


Posted by Christophe Pettus
Feb. 11, 2016, 7:53 p.m.

Indexes Are Not Cheap

Don't go crazy with indexes.


Posted by Christophe Pettus
Feb. 9, 2016, 10:46 a.m.

Django 1.8/1.9 and PostgreSQL: An Ever-Closer Union

I’ll be speaking about Django and PostgreSQL at PyCon US 2016.


Posted by Christophe Pettus