PostGIS: Spatial, Special, and Something Else

Regina Obe

Buy our books! https://postgis.us/page_buy_book

Latest books

Ingres: The predecessor of Postgres

1974 the INteractive Graphics REtrieval System (Ingres) is born

Intended and Granted purpose:

Prototype for geographical and graphical (e.g. CAD) database systems

BUT

Geographic and graphic databases are hard and niche.
Let's build this more useful thing: a relational database with bleeding edge data language called QUEL. But lets throw in some polygon, point, lineseg datatypes to satisfy our grant requirements.

Ingres was impressive

Parallel queries
QUEL the language closest to Relational Father Edgar F. Codd's relational algrebra
Distributed data, distributed execution
Not much graphic retrieval or geographic in it though

Ingres DNA in several proprietary databases

Latest version of Ingres is closed source but still alive.

Ingres, NonStop SQL (now owned by HP), Sybase->Microsoft SQL Server

Postgres starts circa 1986

Doesn't use any code of Ingres. It is a complete reimagining.

Minimalist system to allow bolting on of new data types such as CAD objects and pictures and their supporting functions. Have graphic types but have them built using the new extendable way. Hello my petri friends polygon, point, lineseg
POSTQUEL - a data language largely patterned after QUEL
No-overwrite storage manager - the heart of Time Travel

https://deepai.org generated image

What is in a name: Postgre(S)(QL)

SEQUEL (Second Edition (Structured English) QUEL?) -> SQL	SQL predates Postgres, but Postgres did not offer SQL until much later.
Early years research project with interface called POSTQUEL	Post Graphical Retrieval System but the G just stands for G because it was inherited from InGRES which spent its grant money on not the G
The crashing years	PostGRES95 but at least we have an SQL interpreter
The lets not crash so much years and flesh out SQL	PostgreSQL - Now that we can understand SQL real good, lets put SQL in our name, get rid of the year, and be somewhat stable
We are here	What's our name? PostgreSQL, Postgres, PostGIS, Post Gee eye Ess, Post Geese?

The "let's not crash so much" years

Stablize code
Make things faster
- Time travel makes things slow
Get rid of complexity - We don't need these?
- Extended types adds complexity
- Time Travel adds complexity

Hold the hatchet on extensibility - There is PostGIS

A geographical database management system built on PostgreSQL. Makes good on the promise Ingres made 25 years ago. Finally that g in the name is deserving of a capital G

PostGIS needs extensibility to survive. Extensibility is kept and expanded

Even if we just consider spatial, that's a rich list of extensions

What about Time Travel?

Time travel is ripped out 7.0, but the remnants still lurk in the guts of Postgres

Time travel deprecated in Postgres v6.2 and last version to have it v6.5 https://www.postgresql.org/docs/6.5/advanced23236.htm

Elephant walking up a Time Machine door that reads 'RIP Time Travel'

AI image generated by https://gemini.google.com

Time travel in the early years of Postgres

Excerpted from Stonebraker et al: Implementation of POSTGRES

Lastly, POSTGRES supports the notion of time travel. This feature allows a user to run historical queries. For example to find the salary of Sam at time T one would query:

retrieve (EMP.salary)
using EMP[T]
where EMP.name = "Sam"

Time travel SQL circa Postgres 6.2

SELECT EMP.salary
FROM EMP['1995-01-10', '1995-01-11']
WHERE EMP.name = 'Sam';

Time travel as Postgres were

Many databases have time travel today that at the time was very novel when Postgres had it. cough cough Snowflake is one of the databases that has time travel.

Flashback reporting
Easy recovery from user error deletion / updating
Detection of cooking the books.

ISO SQL defines Time Travel (Temporal Tables)

ISO SQL specs define Time Travel which is how many databases implement it today. PostgreSQL extension periods https://github.com/xocolatl/periods implements the standard minus the SQL syntax.

CREATE TABLE ... PERIOD FOR SYSTEM_TIME (column_name_start,column_name_end]..

SELECT salary 
 FROM EMP
   AS OF TIMESTAMP (CURRENT_TIMESTAMP - INTERVAL '1' DAY)
WHERE name = 'Sam';

The remnants of Time Travel

Elephant with a bubble caption that reads 'never forget' and a time piece in the background

AI image generated with https://deepai.org/

Those secret columns you explicitly have to select are part of time travel plumbing, but they serve other purposes too: https://www.postgresql.org/docs/current/ddl-system-columns.html

SELECT ctid, cmax, xmin, xmax, *
FROM sometable

 ctid  | cmax |   xmin   | xmax  ..
--------+------+----------+------
 (0,1)  |    4 | 16676649 |    0
 (0,2)  |    4 | 16676649 |    0
 (0,3)  |    4 | 16676649 |    0
 (0,4)  |    4 | 16676649 |    0
 (0,5)  |    4 | 16676649 |    0
 (0,6)  |    4 | 16676649 |    0
 (0,7)  |    4 | 16676649 |    0
 (0,8)  |    4 | 16676649 |    0
 (0,9)  |    4 | 16676649 |    0
 (0,10) |    4 | 16676649 |    0
 :
 :

Vacuum and Wrap-Around

Blame Time travel, because dead-tuples in the day of time travel could be visible when asking for that era in time.

PostgreSQL, PostGIS, and Beyond

Elephant sleeping in a room with a blanket engraved with words Extensions, Time Travel holding a baby elephant on its back that is holding a big globe with planets circling

AI image generated with https://deepai.org

The many shapes of Time Travel today

Time travel is mostly dead in PostgreSQL core, but it's alive in extensions and for many more purposes than the original time travel and time travel will likely come back in PostgreSQL core to adhere to the ISO SQL spec.

Temporal range types stepping stones to time travel?

PostgreSQL temporal range types

tsrange, tsmultirange
tstzrange, tstzmultirange
daterange, datemultirange

Can PostGIS do time travel?

What does ST stand for? Spatio-Temporal or Spatial Type and What is that M for? We do it because SQL/MM Part 3: Spatial does it

Is mobilitydb time travel?

Extension for managing trajectories: https://mobilitydb.com