Neues vom PostgreSQL Planet

The `port` parameter does double duty: it names the TCP socket and the Unix socket file too. (I consult on PostgreSQL through PGX Inc..)

Three ways to create a read-only user in PostgreSQL 17, measured: what each still allows, from a write through a function to password verifiers and RLS.

What raised 'must be owner of table' in PostgreSQL 17, measured: which statements need ownership, which a grant covers, and how tables end up with mixed owners.

BYPASSRLS, table owners, FORCE and superusers in PostgreSQL 17, measured: who sees every row, what role membership passes on, and what RLS never covers.

The same LIMIT query on an idle Postgres: 78 ms, then 0.07 ms, with every page already in shared_buffers. Ten runs with a standard deviation and a per-run sample on each plan node found the cause (synchronize_seqscans), a control proved it, and the same statement returned different rows with nothing writing. Why one EXPLAIN ANALYZE is a sample of one, and how to read dispersion on a plan.

ALTER DEFAULT PRIVILEGES in PostgreSQL 17, measured: whose new tables it covers, why a per-schema revoke does nothing, what counts as a table, how to list it.

What raised 'permission denied for table', schema and sequence in PostgreSQL 17, measured: the privilege each statement needs, and why default privileges miss.

What current_setting and set_config do with a custom setting in PostgreSQL 17, measured: NULL or empty string, how long a value lasts, defaults, who may set it.

A table that shrinks in rows does not shrink on disk. Everyone who has run PostgreSQL for a while knows this in general. I wanted the actual numbers, because "VACUUM reclaims space" means something narrower than it sounds.

Short answer: I deleted every second row of a 2,000,000-row table. The table was 137 MB before, 137 MB after the DELETE, 137 MB after VACUUM, and 68 MB after VACUUM FULL.

Some advice gets repeated in code review because it sounds like it should matter. I picked four I have seen argued about and measured them. I expected at least two clear winners. I got three ties and one result that went the opposite way from the advice.

Setup

PostgreSQL 18.6, default settings. 2020 13-inch MacBook Pro, Intel i5-8257U, 8 GB RAM. A 2,000,000-row table u with an id, an email, a status and a timestamp. Times are execution times from EXPLAIN (ANALYZE, TIMING OFF), warm cache, first run discarded, median of the rest.

Ten enterprise customers own 80% of the orders. On uniform test data the query runs in ~1ms; on that real skew it runs ~1,000x slower. The fix doesn't transfer between engines: Postgres is rescued by one free session setting, SQL Server can't be touched by it and needs a completely different access-path fix. Measured on Neon and Azure SQL Database using ExoBench.

Row-level security for a multi-tenant SaaS on PostgreSQL 17, result by result: what leaks, what it costs, how to set the tenant, and where each test is.

Setting the tenant for PostgreSQL row-level security in Go with pgx 5.11, measured: pgxpool, PrepareConn hooks, transactions, batches and PgBouncer.

On August 25, Robert Haas opened a pgsql-hackers thread like this: I asked Claude to evaluate which v19 patches were the scariest based on the number and type of bugs fixed post-freeze. Results below, with a few particularly cutting remarks from the LLM edited out. The subject line was “scary p… (I consult on PostgreSQL through PGX Inc..)

pg_statviz can now ask Claude, a local Ollama model, OpenAI or Gemini to explain your PostgreSQL statistics on every distro we ship it for. Getting there took three days and a few dozen new Python packages

pg_statviz can now ask Claude, a local Ollama model, OpenAI or Gemini to explain your PostgreSQL statistics on every distro we ship it for. Getting there took three days and a few dozen new Python packages

On 7 October, the London PostgreSQL Meetup group met.

Organizers:

• Valeria Kaplan
• Chris Ellis
• Alastair Turner

Speakers:

• Elizabeth Christensen
• Alex Kasko
• Jimmy Angelakos

On 8 October the PostgreSQL England North Meetup Group. Joseph Bharath Reddy Allam delivered a talk.

Override PostgreSQL's prepared statement plan choice when its cost estimates go wrong on skewed data. (I consult on PostgreSQL through PGX Inc..)

For anyone with an interest in the long-term health and longevity of the broader ecosystem of Postgres extensions and tools, we’d like to hear your ideas and inspirations for its assurance.

Most exploratory questions against a large table only need a rough answer, but without an index on status, even "roughly how many shipped orders" costs a full scan. On the two-million-row orders table, Postgres has to read every single 8 kB page, all 18,085 of them (141 MB), just to count matching rows.