The slides from my presentation, JSON Home Improvement at SCALE 14x, are now available.

More as an example than anything else, I wanted a function that would take two JSONB objects in PostgreSQL, and return how the left-hand side differs from the right-hand side. This means any key that is in the left but not in the right would be returned, along with any key whose value on the left is different from the

I gave a talk earlier this week about the new features in PostgreSQL 9.5. The headline feature, of course, is the new INSERT ... ON CONFLICT clause, aka “Upsert,” which allows an INSERT that has a key conflict to do an UPDATE (or just ignore the insert) instead.

A perfectly reasonable question was: “Does this work on COPY”?

How much and what to log in PostgreSQL is something that doesn’t really admit a single solution. Logging every connection can swamp a server, as can too low a setting of log_min_statement_duration. But there are two settings I always turn on: log_lock_waits, and log_temp_files (with log_temp_files being set to 0).

log_lock_waits will log any lock wait that

I’ll be presenting at the January San Francisco PostgreSQL Users’ Group meeting on what’s new in PostgreSQL 9.5. I hope you can join us!

At the point that there are two separate warnings advising you to turn off a configuration parameter in postgresql.conf, it’s probably a good idea to take the advice and disable it.

In theory, this parameter sets a maximum amount of data that will flow over an SSL connection before key renegotiation, to prevent an eavesdropping attacker from determining the

(First in a series of things I always do when setting up or configuring PostgreSQL.)

Since version 9.3, PostgreSQL has had the option to create new database clusters with data checksums. These are fast, simple checksums to verify that the data coming off of disk is what PostgreSQL expects it to be. The checksums don’t correct errors, but it

I’ll be speaking about “The PCI-Compilant Database” at PGConf Silicon Valley!

SERIAL (32 bit integer) or BIGSERIAL (64 bit integer) are the first choice for most people for a synthetic primary key. They’re easy, they’re comprehensible, and they’re transaction-safe. The values that come out of them are, at least to start, manageable and human-scale. They can also provide an easy sortation on creation order.

They’re not perfect, though: If you

I was honored to be invited to give a presentation at the Austin PostgreSQL Users’ Group Meetup, and the slides for my presentation Beyond the B-Tree are now available.