PostgreSQL Development History Revealed with PostgreSQL

I spend a lot of time reading PostgreSQL docs. It occurred to me just a few weeks ago that those versioned manuals are great opportunity to get an insight into PostgreSQL development history. Using PostgreSQL, of course.

TOP 5 functions with the most verbose docs in each version

SELECT
    version,
    string_agg(func, ' | ' ORDER BY letter_count DESC)
FROM (
    SELECT
        version,
        func,
        letter_count,
        row_number() OVER (PARTITION BY version ORDER BY letter_count DESC)
    FROM postgresql_development.data
) a
WHERE row_number &lt;= 10
GROUP BY version
ORDER BY version DESC

Seems like a huge comeback for CREATE TABLE.

VERSION	1st	2nd	3rd	4th	5th
10.0	CREATE TABLE	ALTER TABLE	REVOKE	GRANT	SELECT
9.6	REVOKE	ALTER TABLE	GRANT	CREATE TABLE	SELECT
9.5	REVOKE	ALTER TABLE	GRANT	CREATE TABLE	SELECT
9.4	REVOKE	GRANT	ALTER TABLE	CREATE TABLE	SELECT
9.3	REVOKE	GRANT	CREATE TABLE	ALTER TABLE	ALTER DEFAULT PRIVILEGES
9.2	REVOKE	GRANT	CREATE TABLE	ALTER TABLE	ALTER DEFAULT PRIVILEGES
9.1	REVOKE	GRANT	CREATE TABLE	ALTER TABLE	ALTER DEFAULT PRIVILEGES
9.0	REVOKE	GRANT	CREATE TABLE	ALTER TABLE	ALTER DEFAULT PRIVILEGES
8.4	REVOKE	GRANT	CREATE TABLE	ALTER TABLE	SELECT
8.3	REVOKE	CREATE TABLE	GRANT	ALTER TABLE	COMMENT
8.2	REVOKE	CREATE TABLE	GRANT	ALTER TABLE	SELECT
8.1	REVOKE	CREATE TABLE	GRANT	ALTER TABLE	SELECT
8	CREATE TABLE	REVOKE	GRANT	SELECT	ALTER TABLE
7.4	CREATE TABLE	REVOKE	ALTER TABLE	GRANT	SELECT
7.3	CREATE TABLE	SELECT	ALTER TABLE	REVOKE	GRANT
7.2	CREATE TABLE	SELECT INTO	SELECT	ALTER TABLE	CREATE TYPE
7.1	CREATE TABLE	SELECT INTO	SELECT	CREATE TYPE	ALTER TABLE
7.0	SELECT	SELECT INTO	CREATE TYPE	CREATE TABLE	COMMENT

Number of functions available in each version

SELECT
    version,
    count(func),
    sum(letter_count)
FROM postgresql_development.data
GROUP BY version ORDER BY version;

The most verbose docs in each version

SELECT DISTINCT ON (version)
    version,
    func,
    letter_count
FROM postgresql_development.data
ORDER BY version, letter_count DESC;

Poor REVOKE, the defeated champion.

VERSION	FUNCTION	LETTER COUNT
10	CREATE TABLE	3142
9.6	REVOKE	2856
9.5	REVOKE	2856
9.4	REVOKE	2856
9.3	REVOKE	2856
9.2	REVOKE	2856
9.1	REVOKE	2508
9	REVOKE	2502
8.4	REVOKE	2105
8.3	REVOKE	1485
8.2	REVOKE	1527
8.1	REVOKE	1312
8	CREATE TABLE	1251
7.4	CREATE TABLE	1075
7.3	CREATE TABLE	929
7.2	CREATE TABLE	929
7.1	CREATE TABLE	871
7	SELECT	450

CREATE TABLE docs evolution

SELECT
    version,
    letter_count
FROM postgresql_development.data
WHERE func = 'CREATE TABLE'
ORDER BY func, version;

Something’s going on in an upcoming 10.0 version.

All the data was obtained with the following Python script and processed inside the PostgreSQL database. Plots done with Bokeh, though I probably wouldn’t use it again, the docs site is absurdly sluggish and the info is just all over the place.