(2022-08-17) Udell Postgres Everywhere

Jon Udell on PostgreSql everywhere. SQLite is the world's most widely-deployed database engine. It’s in your phone, it’s in your browser, and if you search your computer you’ll find its .db files there too. SQLite was inspired by Postgres. Its author Richard Hipp has called SQLite a “conceptual fork” of Postgres.

Nowadays those distinctions have begun to blur

Steampipe and Yugabyte aren’t just compatible with Postgres; they actually are Postgres with extra capability (Steampipe’s foreign data wrappers for APIs, Yugabyte’s distributed storage). Users can connect to these products with psql, the interactive terminal for Postgres; they can write the same kinds of queries

We may not see billions of Postgres deployments anytime soon, as is astonishingly true for SQLite, but your devices are more than capable of running Postgres and increasingly, for one reason or another, they will.

Enhanced file systems

Microsoft’s 2003 Professional Developers Conference (PDC) reminded some observers of the same event in 1993... The hot topics this year were the WinFX managed APIs, a rough draft of a future version of NT code-named Longhorn, and ... Cairo. Now called WinFS, this vision of metadata-enriched storage and query-driven retrieval was, and is, compelling.

the SQL databases of today are far better equipped than their ancestors to enrich the file system more comprehensively than SQLite does on a per-application basis. The once-futuristic idea of objects as first-class citizens of the database, for example, is now made real in the form of JSON columns.

Between Richard Hipp’s two extremes — client/server database and application file format — there lies a middle ground: a local database.

The file system isn’t going away, but a local database can powerfully complement it. To do so ubiquitously, such a database has to be an open-source product.

Data synchronization

instances will need to synchronize with other instances in many different ways. Postgres offers a wealth of mechanisms for doing that.

A language-neutral application runtime

Database programmers of a certain age are familiar with the use of stored procedures that run inside the database, at database speed, with direct access to data. With Postgres’s support for procedures and functions written in modern languages, this now-unfashionable technique deserves a second look. SQL has always been a hybrid language: a relational core augmented with a library of functions.

Data science

If Postgres lives on your local machine and in your personal and team clouds, and hosts Python or R, you can query with SQL and use those languages (and their extensive libraries!) for the special machine-learning and statistical powers they bring to the table. (see Doing SQL Data Analysis As A PM)

A ubiquitous platform for working with data

The Steampipe instance of Postgres loads an extension that implements foreign data wrappers for APIs, provides a plugin manager to handle the growing suite of adapters that talk to APIs, and includes a dashboard server for visualizing queries of those APIs. ((2022-03-10) Steampipe Dashboards As Code With HCL + SQL)

Steampipe is just one the signposts pointing to a world in which Postgres runs everywhere. We call Postgres a database, and of course it is one, but it’s also becoming a platform that delivers access to all kinds of data and brings modern styles of computation directly to that data.