The remaining two of the three default_transaction_* parameters, completing the set default_transaction_deferrable began. Each sets the session default for one of the three properties a transaction carries — isolation level, access mode, deferrability — and for all three the rule is the same: the GUC scope is rarely the one you want. The interesting content is what the values mean

default_transaction_deferrable sets the default value of the DEFERRABLE transaction attribute for new transactions. The default is off, the context is user, and the attribute it defaults does nothing at all unless two other things are also true — which is what makes this parameter worth a few hundred words rather than a sentence.

default_toast_compression sets which algorithm PostgreSQL uses to compress values bound for TOAST — the out-of-line storage that holds field values too big to sit inline in an 8KB heap page. The choices are pglz and lz4; the default, on every released version as of today, is pglz; the context is user, and it can be overridden per

default_text_search_config names the text search configuration that PostgreSQL’s full-text-search functions use when you don’t hand them one explicitly. The default is set by initdb from the cluster’s locale; the context is user. A text search configuration bundles a parser (which breaks text into tokens) with a chain of dictionaries (which fold tokens into lexemes — stemming “running” to “run”,

The mechanics first, because they’re brief: when default_tablespace is anything but an empty string, it supplies an implicit TABLESPACE clause for every CREATE TABLE and CREATE INDEX that lacks an explicit one — including the index behind a UNIQUE or PRIMARY KEY constraint. The default is the empty string, which means “use the database’s own tablespace,” and the context is

A small knob on a very large door. default_table_access_method names the table access method — the storage engine, to use the word everyone actually means — that new tables get when their CREATE TABLE doesn’t say otherwise. The default is heap. The context is user. And for the overwhelming majority of clusters, the value has never been anything

default_statistics_target is one of the most-recommended and least-explained parameters in PostgreSQL. Tuning guides say “raise it to 500 for data warehouses” with the confidence of scripture and rarely a word about what the number is. The default is 100, the range is 1 to 10,000, the context is user, and what the number actually controls is the

Statistics are the input. Planning is what the database does with them: it takes a declarative query, which describes what you want and says nothing about how, and turns it into an executable plan, which is nothing but how. There are two jobs inside that. First, rewrite the query into a logically equivalent but more tractable shape, which is where

Twelve parameters share the debug_ prefix, and the prefix is load-bearing: these are PostgreSQL’s own development and QA apparatus, exposed as runtime settings so the buildfarm and the core developers can exercise code paths without recompiling. Pavlo Golub, writing about one of them, summed up the correct posture: “I’ll never-ever touch a runtime option with a ‘debug’ prefix on my

The name is a small lie. deadlock_timeout is not how long PostgreSQL tolerates a deadlock before breaking it — deadlocks are broken the instant they’re found. It’s how long a process waits on a lock before PostgreSQL bothers to look for a deadlock at all. The default is 1s, the context is superuser, and the gap between what