PostgreSQL

A feature of PostgreSQL that most people don’t even know exists is the ability to export and import transaction snapshots.

The documentation is accurate, but it doesn’t really describe why one might want to do such a thing.

First, what is a “snapshot”? You can think of a snapshot as the current set of committed tuples in the

PostgreSQL has two autovacuum-age related settings, autovacuum_freeze_max_age, and vacuum_freeze_table_age.

Both of them are in terms of the transaction “age” of a table: That is, how long it has been since the table has been scanned completely for “old” tuples that can be marked as “frozen” (a “frozen” tuple is one that no open transaction can cause to

The slides for my talk, “Breaking PostgreSQL at Scale” at FOSDEM 2019 are available.

Upon occasion, you want to get the current value of all the sequences in the database. For example, you may have fields that are integer rather than bigint, and you’re concerned how close you are to overflowing one of them (since sequences are bigint and will happily crash through the size of a 32-bit integer).

Unfortunately, currval()

The slides from my presentation, Securing PostgreSQL at PDXPUG PostgreSQL Day 2018 are now available.

In addition to the familiar text types VARCHAR and TEXT, PostgreSQL has a type CHAR. It’s little used… and that’s for a reason. It has some very unusual behaviors, which can be quite a surprise if you are not expecting them.

First, CHAR is a fixed-width type. When character data is stored in it, it’s padded out

It’s not uncommon that an application needs to serialize access to one or more resources. The temptation is very high to use the LOCK TABLE SQL statement to do this.

Resist the temptation.

There are many issues with using LOCK:

• It blocks autovacuum, which can cause bloat or even transaction ID wraparound in extreme cases.

pg_rewind is a utility included with PostgreSQL since 9.x. It’s used to “rewind” a server so that it can be attached as a secondary to a primary. The server being rewound could be the former primary of the new primary, or a secondary that was a peer of the new primary.

In pg_rewind terminology, and in this post, the

PostgreSQL has a variety of ways of telling time: now(), statement_timestamp(), and clock_timestamp(). Each has a different sense of when “now” is:

• now() is the time at the start of the transaction; it never changes while the current transaction is open.
• statement_timestamp() is the time that the current statement started running. It changes from

(This is another intermittent series of small things that are easy to forget, and cause irritation in the PostgreSQL world…)

Since version 1.7, pgbouncer has had built-in TLS support. This is very handy, since the previous versions required stunnel or something similar to provide TLS.

Frequently, either for test purposes or because you’re in an environment where checking