PostgreSQL 8.3, released on 4 February 2008, brings two functions that used to live outside into the engine — full-text search and aggressive space reuse over dead tuples. The choice says more about the project’s direction than any benchmark.

Context

The PostgreSQL codebase began at Berkeley in 1986, from Michael Stonebraker’s POSTGRES project, conceived as the heir to Ingres in order to study one specific theme: extensibility. User-defined data types, a rule system, indexes you can write to measure. In 1994 Andrew Yu and Jolly Chen replaced the POSTQUEL language interpreter with one for SQL, and Postgres95 was born. In 1996 the project became PostgreSQL, passed to a distributed community of developers, and adopted the permissive BSD licence.

Since then the line has held: add functions without disturbing the central invariant, namely an engine where a new data type or a new indexing method counts as much as those shipped by default. 8.3 deserves attention because it is the release in which part of what the community had built as external contrib code finally enters the server installed by default.

Architecture: MVCC as the foundation

PostgreSQL’s concurrency control rests on MVCC (Multi-Version Concurrency Control). Each transaction sees a snapshot of the data as it stood at a precise instant, regardless of the current state of the underlying rows. The practical consequence, as the 8.3 documentation describes it, is that reads do not block writes and writes do not block reads, and locks come into play only when two transactions want to modify the same row.

This model has a structural cost. An UPDATE does not overwrite the row: it creates a new version of the tuple and leaves the old one in place until it becomes invisible to every still-open transaction. A DELETE does not free the space straight away. Dead tuples pile up and have to be reclaimed later, usually with VACUUM. On heavily updated tables the result is bloat: occupied space, indexes to rewrite, and I/O that climbs as changes grow frequent.

The critical point: HOT and full-text in the core

8.3 attacks the cost of MVCC with HOT (Heap-Only Tuples). When an UPDATE touches no indexed columns, PostgreSQL can write the new tuple version on the same page and chain it to the old one without adding index entries. Dead-tuple space is reclaimed as early as the next INSERT or UPDATE, not only during a VACUUM. On a workload made of frequent updates to non-indexed columns — a status table, a session row, a counter — this cuts both the I/O and the index growth.

The second move concerns text search. Up to 8.2, full-text search lived in the contrib/tsearch2 module: community-maintained code, installed separately, with a syntax all of its own. In 8.3 its functions enter the server, installed by default, with a few small syntax changes. The tsvector and tsquery types become engine types rather than extension types. And this is where extensibility pays off: full-text search leans on two indexing methods that already exist, GiST and GIN, which PostgreSQL offers as generic infrastructure rather than code written specifically for text.

The difference between the two indexes is documented without ambiguity. GiST is lossy: it can return false matches that must be checked against the real row, it has slower lookups but faster updates, and it copes well with dynamic data. GIN is an inverted index, essentially non-lossy, with faster lookups (the 8.3 documentation estimates roughly three times GiST) but much slower build and update, suited to more static data. Choosing between them depends on the workload, not on features.

Implications

That tsearch2 moves from contrib into the core is not one line among many in the release notes. It is the sign of a mechanism: a function starts life as an extension, gets tested against real workloads by those who maintain it outside the server, and once stable enough it migrates inside. Extensibility serves the end user who defines a custom type, and it serves the project itself as a proving ground for deciding what to promote into the engine.

The same applies to 8.3’s other additions. Asynchronous commit delays the writes to the WAL (Write-Ahead Log) during commit, trading durability for throughput on short transactions — a choice the caller makes knowingly. Checkpoint spreading distributes disk writes to smooth the I/O spike. The planner learns to re-plan cached queries when table definitions or statistics change. These are all changes to the engine, not layers that sit on top of it.

For anyone designing a system the practical lesson is simple: PostgreSQL tends to internalise. A function solved today with an external service — text indexing, in many cases — may already belong inside the database, with the same transactional semantics as the rest of the data. It is worth checking before adding a component.

Limits

HOT helps only when the UPDATE touches no indexed columns; with many indexes on frequently updated columns the advantage vanishes, and index planning weighs in again. Full-text search in the core covers the common cases of document search, but does not replace a specialised engine where sophisticated ranking, advanced linguistic analysis or scale over huge corpora are required. And the MVCC model stays what it is: space still has to be reclaimed, and a poorly tuned VACUUM strategy produces bloat regardless of HOT. None of these functions removes the tuning work: it only shifts it elsewhere.

https://www.postgresql.org/docs/8.3/release-8-3.html https://www.postgresql.org/docs/8.3/mvcc-intro.html https://www.postgresql.org/docs/8.3/textsearch-indexes.html https://www.postgresql.org/about/news/postgresql-83-released-918/ https://www.postgresql.org https://www.noze.it/en/insights/postgresql-open-source/

Cover image: Michael Stonebraker, the computer scientist behind Ingres and Postgres, in a red polo shirt — photo by Dcoetzee, CC0 — https://commons.wikimedia.org/wiki/File:Michael_Stonebraker_2.jpg