(re)Learning PostgreSQL

In the same way that Linux has become the dominant operating system for developers since its humble beginnings when it was initially released September?17, 1991; 32 years ago, PostgreSQL has also finally and rightfully become the favorite developers' database decades after its initial release on July 8th 1996.

My fascination with PostgreSQL has been going on for a long-time. In fact, I can still find one of my bug report dating back 20 years (2004) and I still remember trying to build a PostgreSQL-backed replacement for the then popular MongoDB in 2013 at a startup in Shanghai.

The persistence of this software in running systems and its popularity in new systems is a testament to the power of the relational model and still evolving SQL standards and its battle proven architecture and high-quality portable code.This puts an end to an era introduced by a then opensource but feature-incomplete MySQL during the dot.com era that made relational databases (previously dominated by unaffordable Oracle) accessible to all but pushed developers to move too many of the business logic into the backend due to the weakness of MySQL SQL standards compliance, forcing a detour into inefficient, complex and hard to maintain ORM (Object-Relational Models).

PostgreSQL exercises by Alisdair Owens was launched at the end of 2013 and subsequently open sourced on Github remains after all these years my go-to to review hands on PostgreSQL querying and data manipulation. From the very basics, the interactive exercises site takes you through joins and subqueries, modifying data, aggregates, date, string, recursive queries.

I've been recommending pgexercises.com to my team members over the years - in fact I had it listed as part of our TRAINING curriculum for incoming developers at the Canadian Food Inspection Agency Artificial Intelligence lab during my year long contract there. I myself have just spent the last few days (about 14 hours) redoing the exercises to refresh my knowledge of PostgreSQL.

In the last decade PostgreSQL has added immensely powerful and useful features; from becoming a super-fast document store with support for the JSON datatype to an extension enabling it to act as a vector database.

The latter has made it an essential part of modern semantic search and RAG (Retrieved Augmented Generation) to feed semantically meaningful documents to generative models where these documents are fed into "embeddings" models that enable them to be placed in the multidimensional semantic space.

I'm relearning PostgreSQL with an eye towards building application with Supabase (PostgreSQL-as-backend), Bytebase (database change management) and PostgresML (AI models in the database) some of the Open Source technologies gravitating around the modern PostgreSQL ecosystem and bound to become, as Linux and PostgreSQL, dominant in the next decade.