Will Postgres Live Forever?
BRUCE MOMJIAN

This presentation explains the long life of open source software, and the life cycle
differences between proprietary and open source software. Title concept from Renee
Deger
https://momjian.us/presentations

Creative Commons Attribution License

Last updated: August 2024

1 / 51

Outline

1. Forever
2. Software life cycle
3. Open source adoption
4. Postgres innovation
5. Community structure

2 / 51

1. Forever

https://www.flickr.com/photos/gsfc/
3 / 51Forever Is a Long Time• Age of the Universe: 13.7 billion years• Age of the Earth: 4.5 billion years• Age of civilization: 6,000 years• Civilized era vs. Earth years: 0.00001%• Digital era vs. Earth years: ~0%

4 / 51

Brief Digital History

1804: Jacquard loom
1945: ENIAC
1970: E. F. Codd Relational Theory
1974: System R
1977: Ingres
1986: University-based Postgres
1994: Postgres95
1996: Internet-based Postgres

5 / 512. Software Life Cycle

https://www.flickr.com/photos/tarynmarie/
6 / 51

Proprietary Software Life Cycle

1. Innovation
2. Market growth
3. Market saturation
4. Maximize profit, minimize costs (development, support)
5. Maintenance mode (no new features, no innovation)
6. End-of-life

7 / 51

Open Source Software Life Cycle

1. Parity with proprietary software, low cost
2. Market growth
3. Continue innovation or decline
4. Source code is always available to continue

8 / 51

Illustrative Example of Open Source Growth
One of the longest-developed computer games:
1984: Spectrum HoloByte began Falcon development
1998: MicroProse released Falcon 4.0
1999: MicroProse ended development
2000: Falcon source code leaked
2003: Benchmark Sims (BMS) released community modifications
2005: Lead Pursuit released Allied Force, which included BMS
mods
2015: GOG.com republished Falcon 4.0 (on Steam since 2016)
2021: BMS released version 4.35 U3, continued development
https://en.wikipedia.org/wiki/Falcon_4.0
9 / 51

Proprietary Development Flow
Sales
Developers

Users

Design Meetings

Receive Software

Work in Isolation

Acceptance Tests

Project Meetings

Resolve Problems

Testing/Retesting

Install

Release

Production

Fix Bugs

Resolve Issues

10 / 51

Open Source Development Flow
Developers

Users
Internet

Propose Feature

Discuss Feature

Patch Review

Patch Review

Apply / Testing

Testing

Beta Testing

Beta Testing

Release

ProductionFix Bugs

Resolve Issues

11 / 51

Rise of Open Source

Open Source
Features
Performance
Closed Source

ReliabilityTime
12 / 51

LinuxLinux attained feature parity with:• HP-UX• AIX• Solarisand then went on to innovate beyond them.

13 / 51

Postgres

Postgres nearing feature parity with:
1. Oracle
2. DB2
3. MS-SQL
4. Sybase
5. Informix
6. Ingres Corp.
and then going on to innovate beyond them.

14 / 51

Many Focuses

Keith Alsheimer, EDB, 201315 / 51

When Does Software Die?

• Proprietary software dies when the owner of the source code can no longer profit
from it.
• It declines long before death due to profit maximization.
• Open source cannot die in the same way.
• Open source remains active while it serves a purpose.
• It can always be resurrected if useful.
• Postgres was given new life in 1996.

16 / 51

Ideas Don’t Die

1. Ideas don’t die, as long as they are shared.
2. Ideas are shared, as long as they are useful.
3. Postgres will live, as long as it is useful.

17 / 51

3. Open Source Adoption

https://www.flickr.com/photos/99438314@N02/
18 / 51Open Source Survey, 2016

When the first survey launched 10 years ago, hardly anyone would have predicted that
open source use would be ubiquitous worldwide just a decade later, but for many goodreasons that’s what happened. Its value in reducing development costs, in freeinginternal developers to work on higher-order tasks, and in accelerating time to market isundeniable. Simply put, open source is the way applications are developed today.
Lou Shipley
President And CEO
Black Duck Software

https://www.slideshare.net/blackducksoftware/2016-future-of-open-source-survey-results19 / 51

Advantages of Open Source
1. Innovation, competitive features
2. Freedom from vendor lock-in
3. Quality of solutions
4. Ability to customize and fix
5. Cost (initially #1)
6. Speed application development
7. Reduce development costs
8. Interoperability
9. Breadth of solutions

https://www.slideshare.net/blackducksoftware/2016-future-of-open-source-survey-results
20 / 51

Open Source Today

Open source today is unequivocally the engine of innovation; whether that’s powering
technology like operating systems, cloud, big data or IoT, or powering a new generationof open source companies delivering compelling solutions to the market.
Paul Santinelli
General Partner
North Bridge

https://www.slideshare.net/blackducksoftware/2016-future-of-open-source-survey-results
21 / 51

Open Source Usage, 2016

1. Operating Systems
2. Database
3. Development tools
Database didn’t appear in the top three the previous year’s survey (2015).

https://www.slideshare.net/blackducksoftware/2016-future-of-open-source-survey-results
22 / 51

Advantages of Open Source for Database Decision Makers

Managers
No vendor lock−in
Cost
Cheaper development

ChoosePostgres

Database
Administrators

Developers

QualityInnovationCustomize

Speed of development

Interoperability
Breadth of solutions

23 / 51

4. Postgres Innovation

https://www.flickr.com/photos/tomas_vondra/
24 / 51

Relational Innovation• E. F. Codd introduces relational theory• Row, column, table• Constraints• Normalization, joins• Replaces key/value data storage systems• Pre-Postgres

https://en.wikipedia.org/wiki/Edgar_F._Codd
https://www.youtube.com/watch?v=zSn8il5Mo5s

25 / 51

University Postgres Innovation• Michael Stonebraker creates university Postgres• Allows extendability via system table contents:• Data types• Indexing methods• Server-side languages

https://en.wikipedia.org/wiki/Michael_Stonebraker
26 / 51

Postgres Extendability
pg_databasedatlastsysoid
pg_conversion

pg_trigger
tgrelidpg_aggregate
aggfnoid

tgfoidaggtransfn
aggfinalfn
aggtranstype

pg_proc
prolangpg_constraint

conproc
pg_language
pg_castpg_rewrite
ev_class

castsource
casttarget
castfunc

pg_amproc
amopclaidamprocpg_amcontypid

amgettuple

prorettype
pg_opclass
opcdeftypeaminsert
ambeginscan
amrescanamendscan

pg_index
indexrelid
indrelid

pg_class
reltyperelam
relfilenodereltoastrelid
reltoastidxid

pg_typetyprelid

pg_operator
oprleftammarkpos

typelem

oprright

ambuildtypinput

oprresulttypoutput

oprcomambulkdelete
amcostestimate

typbasetype

oprnegate

amrestrpos

oprlsortop
oprrsortop
oprcodepg_inherits

pg_attribute

pg_attrdef

oprrestinhrelid
inhparent

attrelid
attnumadrelid

oprjoinadnum

pg_amopamopclaidamopopratttypid
pg_statistic
starelid
staattnum
pg_depend

pg_namespacestaop1

pg_authid

pg_description

pg_extension

27 / 51

Postgres Extension Data TypeCREATE EXTENSION isn;\dT
List of data types
Schema | Name |Description--------+--------+-------------------------------------------------public | ean13 | International European Article Number (EAN13)
public | isbn
| International Standard Book Number (ISBN)
public | isbn13 | International Standard Book Number 13 (ISBN13)
public | ismn
| International Standard Music Number (ISMN)
public | ismn13 | International Standard Music Number 13 (ISMN13)
public | issn
| International Standard Serial Number (ISSN)
public | issn13 | International Standard Serial Number 13 (ISSN13)
public | upc| Universal Product Code (UPC)

https://momjian.us/main/writings/pgsql/central.pdf
28 / 51

Postgres Server-Side Languages• PL/Java• PL/Perl• PL/pgSQL (like PL/SQL)• PL/PHP• PL/Python• PL/R (like SPSS)• PL/Ruby• PL/Scheme• PL/sh• PL/Tcl• PL/v8 (JavaScript)• SPI (C)https://momjian.us/main/writings/pgsql/central.pdf
29 / 51

Postgres Index Types• BRIN• BTree• Hash• GIN (generalized inverted index)• GiST (generalized search tree)• SP-GiST (space-partitioned GiST)

https://momjian.us/main/writings/pgsql/indexing.pdf
30 / 51

Postgres Innovation: Full Text Search• Supports full text search capabilities in a relational database• Whole-word, word prefix, and, or, and not searches• Stemming for 21 languages• Pg_trgm extension allows search of letter combinations and similarity• Specialized indexing, operators, and functions• Full transaction semantics

https://momjian.us/main/writings/pgsql/non-relational.pdf
31 / 51

Full Text Search

EXPLAIN SELECT line
FROM fortune
WHERE to_tsvector(’english’, line) @@ to_tsquery(’pandas’);QUERY PLAN------------------------------------------------------------------…
Bitmap Heap Scan on fortune (cost=12.41..94.25 rows=21 width=36)
Recheck Cond: (to_tsvector(’english’::regconfig, line) @@ to_ts…
-> Bitmap Index Scan on fortune_idx_ts (cost=0.00..12.40 rows…
Index Cond: (to_tsvector(’english’::regconfig, line) @@ t…

32 / 51

NoSQL• Supports NoSQL capabilities in a relational database• Mix structured and unstructured data in the same row and query; the best of bothworlds• Specialized indexing, operators, and functions• Full transaction semantics

https://momjian.us/main/writings/pgsql/yesql.pdf
33 / 51

NoSQLEXPLAIN SELECT data->‌>’last_name’
FROM friend2
WHERE data::jsonb @> ’{"first_name" : "Jane"}’ORDER BY 1;QUERY PLAN----------------------------------------------------------------…
Sort (cost=24.03..24.04 rows=1 width=139)
Sort Key: ((data ->‌> ’last_name’::text))-> Bitmap Heap Scan on friend2 (cost=20.01..24.02 rows=1 …
Recheck Cond: (data @> ’{"first_name": "Jane"}’::jsonb)-> Bitmap Index Scan on friend2_idx (cost=0.00..20.01 …
Index Cond: (data @> ’{"first_name": "Jane"}’::js…

34 / 51

Range Types• Combines start and stop times into a single field• Allows sophisticated indexing and comparisons• Allows automatic range overlap prevention

https://momjian.us/main/writings/pgsql/non-relational.pdf
35 / 51

Range Types

EXPLAIN SELECT *FROM car_rental
WHERE time_span @> ’2007-08-01 00:00:00’::timestamptz;
QUERY PLAN--------------------------------------------------------------------------------------Bitmap Heap Scan on car_rental (cost=4.27..28.35 rows=16 width=36)
Recheck Cond: (time_span @> ’2007-08-01 00:00:00-04’::timestamp with time zone)-> Bitmap Index Scan on car_rental_idx (cost=0.00..4.27 rows=16 width=0)
Index Cond: (time_span @> ’2007-08-01 00:00:00-04’::timestamp with time zone)

36 / 51

Geometric Types• Handle multi-dimensional data
• Points• Lines• Circles• Polygons• Multi-dimensional indexing and operators• Allows efficient nearest neighbor searches• Avoids using a separate geometric data store

https://momjian.us/main/writings/pgsql/non-relational.pdf
37 / 51

Geometric Types

EXPLAIN SELECT *FROM dart
ORDER BY location <-> ’(50, 50)’::point
LIMIT 2;QUERY PLAN---------------------------------------------------------------------------------Limit (cost=0.14..0.30 rows=2 width=28)-> Index Scan using darts_idx on darts (cost=0.14..80.14 rows=1000 width=28)
Order By: (location <-> ’(50,50)’::point)

38 / 51

GIS• PostGIS is a full-featured Geographical Information System (GIS)• Implemented as a extension• Independent development team and community

https://postgis.net/39 / 51

GIS

SELECT ST_Area(the_geom)/10000 AS hectares
FROM bc_municipality
WHERE name = ’PRINCE GEORGE’;
hectares-----------------32657.9103824927

40 / 51

Foreign Data Wrappers• 100+ interfaces to foreign data
• Read/write• Sophisticated push down of joins, sorts, and aggregateshttps://momjian.us/main/writings/pgsql/central.pdf
41 / 51

Foreign Data Wrappers

Postgres

Oracleora_tabmon_tabMongoDB
tw_tabTwitter
42 / 51

Data Analytics• SQL• aggregates, GROUPING SETS, ROLLUP, CUBE
• window functions• common table expressions (CTE)• server-side languages, e.g., PL/R

• Performance• optimizer, https://danolivo.substack.com/p/does-postgresql-respond-to-the-challenge• bitmap heap scans• BRIN and bloom indexes• materialized views• just-in-time compilation (JIT)• Large data sets
• data partitioning• tablespaces• parallelism• sharding (in progress)
http://www.slideshare.net/PGExperts/really-big-elephants-postgresql-dw-15833438
http://wiki.postgresql.org/images/3/38/PGDay2009-EN-Datawarehousing_with_PostgreSQL.pdf
43 / 51

Data Analytics

Primarypg_wal/Data Warehouse

Networkpg_wal/

44 / 51

Sharding• Allows multi-host databases• Uses existing functionality• Partitioning• Parallelism• Foreign data wrappers• Logical replication• Needs new functionality• Global transaction manager• Global snapshot manager

https://momjian.us/main/writings/pgsql/sharding.pdf
45 / 51

Sharding
SQL Queries

PG FDWSQL Queries

Foreign Server

Foreign Server

Foreign Server

46 / 51

5. Community Structure

https://www.flickr.com/photos/tomas_vondra/
47 / 51

Community Structure• BSD license guarantees software will be available forever, including for proprietaryuse.
• Development and leadership is diversified geographically, culturally, and ismulti-company.

48 / 51

Still Going Strong• 35+ years of development• 25+ years of annual major releases• ~180 features per major release• Quarterly minor releases• Most-loved relational database• https://insights.stackoverflow.com/survey/2018/#
technology-most-loved-dreaded-and-wanted-databases

49 / 51

PgLife

http://pglife.momjian.us
50 / 51

Conclusion

https://momjian.us/presentations

https://www.flickr.com/photos/pagedooley/
51 / 51