Structured Query Language 101 -15:00–16:20

Nov. 6, 2026 · 15:00 - 6:20

I will be teaching the basics of SQL at the 2026 Texas Linuxfest. The session is listed for only 100 minutes, but I wrote the materials for a 3-hour course. We will cover as much of those three hours as the audience can stand (sit?), or they send us off to Sixth Street.

Tickets are available, and this event is great for networking.  Ping me if you have questions about this session. 

Description:

SQL is a powerful language for working with relational databases such as MySQL, PostgreSQL, SQL Server, and Oracle. This is an 80-minute introduction to writing SQL database queries. Please load a copy of DBeaver Community Edition (free, open-source) from https://dbeaver.io/download/ on your Mac, Windows, or Linux laptop to work along with the presentation. We will use the sample database that is included with DBeaver. We will start with simple SELECT statements to retrieve data, INSERT to add data, use UPDATE to modify it, and DELETE to remove it. We will then move on to using WHERE to narrow your database searches, grouping & ordering for readability, and using built-in functions. This is a great way to learn how to use a relational database.

Elizabeth: I know a little bit of your history prior to joining the Postgres project. I think you did work on JPEG — the image specification. The internet thinks that you did some work on libjpeg, which was part of the Mars Perseverance camera work. Tell me a little bit about that and how that stuff affects your work in Postgres.

Tom: So I had nothing to do with the writing of the JPEG specification. But it came out and there were maybe about a dozen of us who were interested in this and said, let's sit down and write an open source implementation of it, which we did, and that became libjpeg. And I was — there was this flurry of activity at the very beginning with maybe about a dozen people involved. Then after that, it kind of went into maintenance mode. I was principal maintainer of it for five years or so, which is why my name is on it more than other people's.

When I got involved in Postgres, that became something that just sucked up all my time. And so I stopped working on libjpeg. I'm happy that some other people picked it up and ran with it, which they did eventually after I ignored it for long enough.

I know for a fact that the engineering cameras on Perseverance use libjpeg, because Joe Conway found an academic paper that said so. They've never been in any direct contact with me.

Elizabeth: Is there crossover between open image specifications and databases?

Tom: Not directly, but it definitely informs my thinking about things like software licenses. I think the fact that JPEG is absolutely everywhere today is 25% the fact that it was a really great standard that lets you make image files about 10 times smaller for the same quality as you could have before and 75% the fact that there was a free implementation that anybody could use. Without that, it would not have been put into the early web browsers and you would not be seeing it all over the net.

We made the right decision on that. And then when I came to Postgres again, the fact that it had a very liberal license was a

Yesterday I had the pleasure of speaking at PGDay UK 2026 in London, at the Cavendish Conference Centre. The talk was called 100,000 Lines of C Later: Re-architecting Enterprise Postgres Backups in Go. It was about the design of a Postgres backup tool: the rules such a tool has to obey, and what those rules look like when you rebuild the tool from scratch in Go, which is what pgSafe is. The slides are available here.

It was very well received, the feedback afterwards was positive, and people were intrigued by the possibilities. For those who weren't there, here is what I talked about.

What happened in April 2026

pgBackRest is one of the two de facto enterprise backup tools for Postgres, with its first stable release dating back to 2016. It started life as Perl and is now written in C, and it has a decade of production lessons incorporated into its code. In April 2026 it lost its corporate backing, and the maintainer archived the repository. A few weeks later, it turned out the project could survive, and it is being maintained again.

Nobody did anything wrong here: funding stopped, and somebody made a reasonable call. But for those few weeks, the question was: what do we back up with?

Why was that a problem?

pgBackRest is about 96,000 lines of dense C, and another 80,000 lines of test harness: manual memory management, custom networking, its own protocols. It is excellent code, maintained by very few people: not many can understand it well enough to work on it.

Critical infrastructure needs alternatives that more people can maintain.

So I started writing one.

Introducing pgSafe

pgSafe is written from scratch in Go. It shares pgBackRest's concepts and operational rules, and none of its code. The goal is functionality parity for the common deployments: full and incremental backups, point-in-time recovery, five storage backends (POSIX, S3, Azure Blob, GCS, SFTP), and PostgreSQL 13-18 support. I am happy to announce that the development team (just me for no

Percona Operator for PostgreSQL 3.1.0 takes on three things that decide whether a PostgreSQL platform passes review: is the data encrypted at rest, can it serve reads without straining the primary, and are the logs there when you need them. This release answers all three inside the custom resource, so none of them is a bolt-on you maintain yourself.

The three headline features are transparent data encryption with pg_tde, logical replicas, and persistent logging. pg_tde encrypts your data on disk, including the write-ahead log. Logical replicas add a read-only copy inside the cluster for reporting and analytics. Persistent logging keeps PostgreSQL and pgBackRest logs across Pod restarts.

The operator is open source and runs on any CNCF-certified Kubernetes distribution. This release also widens where it runs, adding official Rancher Kubernetes Engine (RKE2) support and full ARM64 images. Much of what shipped here comes from requests on forums.percona.com and the public issue tracker.

In this post, you’ll learn about:

• Transparent data encryption with pg_tde
• Logical replicas for read-only workloads
• Persistent logging for PostgreSQL and pgBackRest
• Other improvements worth knowing about
  

Transparent data encryption with pg_tde

Encryption at rest is usually the line item that blocks a database from going into a regulated environment. Storage-level encryption from the cloud provider covers the disk, but it does not protect a copied volume, a leaked backup, or a stray WAL segment, and auditors increasingly want encryption the database itself controls. This release adds transparent data encryption through pg_tde, Percona’s open source TDE extension for PostgreSQL.

Why it matters

With pg_tde, the data in tables, indexes, temporary tables, and the write-ahead log stays encrypted on disk, and PostgreSQL decrypts it only in memory for a session that holds the key. That closes the gaps storage encryption leaves open: a snapshot of the volu

Way back in 2015, I opened a PGXN API issue to allow links between documents rendered by PGXN to work. In 2024 I followed up with another issue to enable relative links to images to work. I mean, everyone wants this, right? It’s how your favorite source code sit works.

Now so does PGXN. As of today, links between documents and to images work. Check it out in the newly-released chdb extension docs, which both link to the chdb and chdb_hook docs and to some nice benchmark graphs.

Or see the Apache Age docs, which includes not only documentation images but also decorative SVGs for the headers.

I’m gradually reindexing all of the extensions so that any other documentation with such links work work; it should be done by tomorrow. Then, at along last, your extensions can look their very best.

We're happy to announce a new Postgres extension: chdb. This extension expands Postgres import and export features via the chDB library, an in-process ClickHouse engine, providing efficient, flexible conversion to and from a wide array of data formats living on your favorite cloud storage systems.

Benchmark {#benchmark}

And boy howdy do we mean efficient! We compared chdb's performance importing the NYC Taxi dataset (1m rows, wide table) in a number of data formats to three other Postgres extensions, all reading from a regionally-colocated AWS S3 bucket. To the chart!

In order to minimize differences and to optimize for measurement of extension performance rather than infrastructure, the chdb, pg_lake, and pg_duckdb benchmarks ran on r8id.xlarge ClickHouse Managed Postgres services with 4 vCPUs and 32 GB RAM; the aws_s3 benchmark ran on a db.r8g.xlarge AWS RDS host, also with 4 vCPUs and 32 GB RAM. Results average three runs for each import. See the benchmark source code for details.

Implementing a zero-trust network model in Kubernetes requires shifting from the default-allow behavior to explicit, label-driven microsegmentation. This hands-on lab walks through securing a standard three-tier architecture (Frontend ⭢ Backend ⭢ Database) using Kubernetes NetworkPolicies, validating both ingress and egress restrictions.

Because standard Kubernetes requires a network plugin to actually enforce these rules, this lab environment uses Calico as the Container Network Interface (CNI). While the YAML manifests are standard Kubernetes API objects, it is the Calico CNI operating under the hood that intercepts the traffic and enforces both our ingress and egress restrictions.

The Lab Environment

We begin by establishing a baseline three-tier architecture in a dedicated namespace, leveraging specific labels to identify our workloads.

kubectl create namespace production-app
# CREATE FRONTEND POD
kubectl run frontend --image=nginx --labels=tier=frontend -n  production-app
# CREATE BACKEND POD
kubectl run backend --image=nginx --labels=tier=backend -n  production-app
# CREATE DATABASE POD
kubectl run database --image=postgres:18 --labels=tier=database -n  production-app \
  --env="POSTGRES_DB=myapp" --env="POSTGRES_USER=appuser" --env="POSTGRES_PASSWORD=securepass123"

Verify the pods and labels:

kubectl get pods -n production-app --show-labels

NAME       READY   STATUS    RESTARTS   AGE     LABELS
backend    1/1     Running   0          3h6m    tier=backend
database   1/1     Running   0          3h12m   tier=database
frontend   1/1     Running   0          3h6m    tier=frontend

Expose the pods so they can communicate via ClusterIP:

kubectl expose pod frontend --port=80 --target-port=80 -n production-app
kubectl expose pod backend --port=80 --target-port=80 -n production-app
kubectl expose pod database --port=5432 --target-port=5432 -n production-app

Establishing the Baseline

The foundation of Kubernetes network security is a namespace-wide d

The PostGIS Team is pleased to release PostGIS 3.7.0rc2! Best Served with PostgreSQL 19 Beta 3 , GEOS 3.15.0 , postgis_tiger_geocoder 2025.2 , and address_standardizer.

This version requires PostgreSQL 14 - 19beta3, GEOS 3.10 or higher, Proj 6.1+, and libgmp. To take advantage of all features, GEOS 3.15+ is needed. To take advantage of all SFCGAL features SFCGAL 2.3.0+ is needed. To use postgis_raster extension GDAL 3+ is required.

This release contains fixes since 3.7.0rc1 release.

• source download md5

• NEWS

• HTML Online en ja zh_Hans fr

• PDF docs: en ja, zh_Hans, fr

• Cheat Sheets:

  • postgis: en ja zh_Hans fr
  • postgis_raster: en ja zh_Hans fr
  • postgis_topology: en ja zh_Hans fr
  • postgis_sfcgal: en ja zh_Hans fr

This release is a release candidate of a major release, it includes bug fixes since PostGIS 3.6.4 and new features.

Context

At Swiss PG Day 2026, I had the privilege of moderating a Birds of a Feather (BoF) session. A BoF session is a community-driven event designed for focused, interactive discussion among participants with shared interests.

I gave a brief introduction to the topic "How about being a speaker", taking from my brief experience of giving talks at Postgres community conferences. This was as much about me learning from others as it was to encourage aspiring speakers to submit to the next Call for Papers.

The room filled up with around 20 participants, an ideal group size to keep the dialogue

On 26 August 2026, the Adelaide PostgreSQL User Group met, organized by Robins Tharakan. Shadab Mohammad and Robins Tharakan delivered a talk.

On 26 August, the Sydney PostgreSQL User Group met, organized by Shadab Mohammad. Rajesh Kandasamy and Shadab Mohammad delivered a talk.

On 3 September, the PostgreSQL Istanbul Meetup group met, organized by Devrim Gündüz, Gülçin Yıldırım Jelínek & Bilge Korkmaz Erdim. Volkan Çetin and Önder Kalacı delivered a talk.

PGConf Brazil happened from 2-4 September 2026.

Organized by:

• Charly Batista,
• Euler Taveira,
• Fábio Telles, Fabrizio Mello,
• Francisco Porfírio,
• João Foltran,
• William Ivansky

Talk Selection Committee:

• Barbara Leidens,
• Cammila Martins,
• Francisco Omar,
• Francisco Porfírio,
• Jean Pierre

Speakers:

• Alfredo Rodriguez,
• Andreas Scherbaum,
• Barbara Soares Lara,
• Breno de Araújo Baima,
• Bruce Momjian,
• Carlos Correa Silva Alves,
• Charly Batista,
• Dani Monteiro,
• Daniel Moya,
• Davy Alvarenga Machado,
• Dickson S. Guedes,
• Emanuel Carneiro,
• Emerson dos Santos Queiroz,
• Fabrízio de Royes Mello,
• Fernando Franquini,
• Fernando Laudares Camargos,
• Francisco Porfírio Ribeiro Neto,
• Fábio Telles Rodriguez,
• Gabriel Batista Menezes,
• Gabriele Fedi,
• Guilherme Barreto,
• Gustavo Lemos,
• Gustavo Oliveira,
• Iago Passos,
• Israel Barth Rubio,
• Joao Cosme,
• Joelcio Rizelo,
• Josiel Lemos Santos,
• João Marcelo Detomini,
• Jônatas Davi Paganini,
• Karin Keller,
• Marcelo Fernandes,
• Martín Marqués,
• Matheus Alcantara,
• Maurilio Pereira,
• Nathan Aguiar,
• Ozano Neto,
• Patrick de Alvarenga Siqueira,
• Pedro Luís Teixeira,
• Rafael de Alencar Segura,
• Raphael Max Brettas Vieira,
• Raul Oliveira,
• Robert Treat,
• Roberto Mello,
• Rodrigo Ibraim Teixeira, Shane Borden,
• William Ivanski,
• William Lino Oliveira,

An overnight load runs under session_replication_role = replica, the setting most of the popular answers describe as switching enforcement off for the session. It writes an order for customer 999, who does not exist, and then rejects the next row for having a negative amount.

SET session_replication_role = replica;
SET
INSERT INTO orders (customer_id, amount, tenant_id) VALUES (999, 10, 1);
INSERT 0 1
INSERT INTO orders (customer_id, amount, tenant_id) VALUES (1, -5, 1);
ERROR:  new row for relation "orders" violates check constraint "orders_amount_check"

Both statements ran one line apart in the same session, against the same table. The parameter governs which triggers and rules fire, and a foreign key is enforced by a pair of internal triggers that pg_trigger names RI_ConstraintTrigger_c_*, so the foreign key falls silent along with them. No trigger implements a CHECK constraint, which is why that one carries on rejecting rows.

Two more levers get recommended for the same job. ALTER TABLE ... DISABLE TRIGGER USER (or ALL) works on the table instead of the session, and PostgreSQL 18 added ALTER TABLE ... ALTER CONSTRAINT ... NOT ENFORCED, which works on a single constraint. All three went through one identical probe set below, on a schema built to carry every kind of rule at once.

Key Takeaways

• Under session_replication_role = replica a load still meets CHECK, NOT NULL, UNIQUE, identity columns and row-level security. Foreign keys, ON DELETE CASCADE, rules and event triggers go quiet, and a trigger marked ENABLE REPLICA fires for the first time in its life.
• Neither RESET nor ENABLE TRIGGER ALL reads a row on the way back, and VALIDATE CONSTRAINT against a foreign key the catalog already believes is valid answers ALTER TABLE while orphans sit in the table.
• NOT ENFORCED, added for foreign keys in PostgreSQL 18 and extended to CHECK constraints in 19, is the only lever that scans the table when you switch it back on, and a forced row-level security policy can hide rows from t

If you run PostgreSQL on EC2, you've had two real choices until now: build from source, or fall back to whatever version Amazon Linux itself carries in its base repos. Neither matches what the rest of the PostgreSQL community gets from yum.postgresql.org — the full extension ecosystem, day-one minor releases, and a consistent layout across distributions.



That gap is closed. Amazon Linux 2023 is now a first-class target of the PGDG YUM repository, with its own build root and its own package tree, right next to Enterprise Linux, Fedora, and SUSE.

Summary first:

The PostgreSQL documentation on numeric contains two statements that don't sit well together:"especially recommended for storing monetary amounts and other quantities where exactness is required" — and right away: "calculations on numeric values are very slow compared to the integer types, or to the floating-point types". So the type is recommended for storing monetary amounts, and in the same breath admitted to be rather expensive.For me, as a DBMS developer, that reads as a call to action. If operations on a type are noticeably slower than on bigint, a temptation arises: couldn't we store monetary amounts as an integer number of cents and round by the standard rule? That would save a fair amount of computing resources on our database servers, wouldn't it? And what if we went all the way and used double precision?But before optimizing the type or swapping it for an integer, it's worth understanding what is actually demanded of it: by law, by data interchange formats, by application platforms. Is the exact decimal type really the standard for financial applications, if only a de-facto one? Or is it engineering folklore that can safely be worked around?Rather than rely on survey literature, let's dig into the primary sources. This task has never been a simple one, but AI agents have made it much easier. So let's roll up our sleeves and get started. If the text feels overly dry or boring — well, that's because it is. Which is why there's a table of contents, so you can quickly jump to whatever you need.

Table of contents

• What the SQL standard says

• What law and regulators require

• Financial data interchange formats

• Payment systems: scale as an attribute of the currency

• What the TPC benchmarks require

• What vendors, authorities, and practitioners say

• What's going on in ERP systems

• Where the exact decimal type is heading

• Conclusions

1. What the SQL standard says

PostgreSQL 19 Beta 3 shipped on August 13, 2026, and the release notes have been filled in as of 2026-07-18 — still marked subject to change, and the GA date isn’t announced yet, but following the project’s usual September/October cadence general availability should land within the next few weeks. That makes now the right time to read through what’s changing, the same way I did for PostgreSQL 11 through 18 a few weeks ago.

This is not a changelog dump. It’s the subset of PG 19 I think is worth knowing about before you upgrade: a handful of compatibility breaks that will bite people who don’t read release notes, and the SQL-level additions I found genuinely useful once I started poking at them. Every query below ran against a real PostgreSQL 19 Beta 3 instance — no hand-waving about syntax that might work.

When dividing by zero, Postgres fails and declares you ran an illegal operation. Cast 'abc' to an integer, and get an error. Divide by NULL and the query still runs. NULL isn't even a value. It is a marker for “unknown.” When using NULL, the concept of being unknown propagates through comparisons, arithmetic, concatenation, aggregates, window functions, and WHERE clauses. The result is well-defined, but it may not be the result you had in mind.

The unofficial subtitle of this post could be: why NOT NULL constraints are serious business. One way to dodge the complications below is to never store NULL in the first place. If a column should always have a value, say so in the schema.

Let's start with a quiz: what does this return?

SELECT (NULL = NULL) = (NULL != NULL);

If you said NULL, you are right. Both NULL = NULL and NULL != NULL are unknown, so the outer = is comparing unknown to unknown, which is also unknown. Comparison operators (=, <>, and the rest) return NULL when either side is unknown. That is why SQL has IS NULL instead of = NULL: you cannot know whether two unknowns are equal, but you can test whether a value is unknown. (There is a legacy caveat! It is at the bottom.)

Keep that in mind as we walk through the rest. NULL is not a value. It is unknown.

Three-Valued Logic with OR and AND

Boolean expressions in SQL are not limited to TRUE and FALSE. Every predicate can also be NULL, meaning unknown. WHERE and HAVING keep only rows where the expression is true. Unknown is discarded the same way false is.

SELECT
  NULL = NULL              AS null_eq_null,    -- NULL
  TRUE  OR  NULL           AS true_or_null,    -- t
  FALSE OR  NULL           AS false_or_null,   -- NULL
  TRUE  AND NULL           AS true_and_null,   -- NULL
  FALSE AND NULL           AS false_and_null,  -- f
  NOT NULL::boolean        AS not_null,        -- NULL
  NULL::boolean IS UNKNOWN AS is_unknown;      -- t

OR can still be true if the other side is true. AND can still be false if the other side is fal

 Note: I am presenting a tutorial for those who want to learn SQL at this year's Texas Linuxfest (https://pretalx.com/txlf2026/talk/DQ3XW7/) on November 6th.  This is a great opportunity at a fantastic community event and I encourage you to attend.

Demo databases are good way to develop skills with Structured Query Language. Some are associate with one data store more than another, like Sakila and World with MySQL, or DVD with PostgreSQL. The Northwind database originated in the Microsoft sphere of influence but it is easy to obtain for PostgreSQL.

Step 1

Go to https://github.com/pthom/northwind_psql and download https://github.com/pthom/northwind_psql/blob/master/northwind.sql

This file has all you need.

Step 2

I am using DBeaver Enterprise Edition 26.1.0 and open the northwind.sql file. DBeaver is an amazing data tool and makes this type of project simple. 

Opening the northwind.sql file

Step 3

This is that northwind.sql file in all its glory. If you are new to SQL, take a moment and scroll through the file. This is a well structed example that you could use to model your future work (hint, hint).

The contents of the northwind.sql file

Step 4

Now we can execute the northwind.sql file to load the structure and data. 

Use Alt + X to execute the northwind.sql file

How did it go? Did it load properly. If it did, you should see something like the following report on the script's execution. 

Step 6

Try a sample query! What, you're new to SQL and don't have a sample handy? Try this:

Back on Friday, July 17th, I joined Courtney from Manning for another LinkedIn Live, this time on what to do when you inherit a bad database. The recording and the slides are now up, with a Q&A at the end.

The session was based on Chapter 11 of my book, PostgreSQL Mistakes and How to Avoid Them (Manning). This one was more of a fireside chat than a technical presentation, and if you know me, I tend to give the latter. It's a situation most people who work with data run into sooner or later, and the first thing worth saying about it is that you are not alone.

These databases come about for what we politely call historical reasons: no DBA on the team when the thing was built, rushed deadlines, organic growth, and various other reasons. There's also a thing I call architect disease, which is architectural arrogance: a data or software architect joins the team and says forget the best practices everyone keeps talking about, I know the perfect way to do this. What they build might work fine for the use case at the moment it rolls out, but people usually have trouble maintaining such designs afterwards. The symptoms are recognizable: improper database encodings, tables with a hundred columns because they were once spreadsheets, missing indexes, no constraints so the data is inconsistent, etc.

The part that matters is that assigning blame is not a strategy. What we covered instead:

• Don't panic. These are solvable problems, and never let a good crisis go to waste: you have effectively been given permission to fix things.
• Ask the humans what hurts. People are keen to complain once they find an outlet, so let them. Beware the XY problem while you listen: when someone asks for partitioning or a Kafka pipeline, find out what they are actually trying to solve before you build it.
• Examine everything: the schema with pg_dump, pgAdmin or DBeaver, and the data with exploratory queries. The configuration, and the behavior, through logs, pg_stat_activity and pg_stat_statements.