Ein Blog

Aktuell mache ich ein bisschen Postgres und muss irgendwo festhalten, was mir so für Dinge auffallen, die ich aus MariaDB und SQLite nicht so kenne.

Heute: Domains

Man kann in Postgres eigene Datentypen anlegen und diese auch mit CHECKs versehen:

CREATE DOMAIN percentage
AS INTEGER
CHECK (VALUE BETWEEN 0 AND 100) NOT NULL;

Und sie dann ganz normal verwenden:

CREATE TABLE test (
    -- ...
    amount_finished percentage,
    -- ...
);

Es gibt auch noch CREATE TYPE. Der Unterschied wird hier erklärt.

Wo wir gerade bei Postgres waren: Postgres hat einen riesen-Haufen vordefinierter Typen: https://www.postgresql.org/docs/9.5/datatype.html

U. A. einen Typen für IP-Adresse und einen für IP-Netze. Außerdem kann eine Tabelle auch Array-Spalten enthalten. Außerdem auch noch Enums und geometrische Typen (Punkte, Pfade, etc).

Heutiger SQLite-Post: SQLAR, ein komprimiertes Archivformat, was gleichzeitig eine SQLite-Datenbank ist. Idee: Es ist eine normale SQLite-DB, die zusätzlich diese Tabelle hat:

CREATE TABLE sqlar(
    name TEXT PRIMARY KEY,  -- name of the file
    mode INT,               -- access permissions
    mtime INT,              -- last modification time
    sz INT,                 -- original file size
    data BLOB               -- compressed content
);

Rest dürfte selbsterklärend sein. Die Vorteile sind die, die man bei normalen SQLite auch hat: Transaktionen, man kann SQL-Querys auf Dateinamen/Attribute machen, die Datei ist inkrementell updatebar etc.

Lesser Known PostgreSQL Features.

Five Tips For a Healthier Postgres Database in the New Year

SQLite 3.38.0 ist da. Neu ist u. a. der JSON-Support, der jetzt per Default eingeschaltet ist. Dazu kamen auch noch JSON-Operatoren, die mit Postgres kompatibel sind.

Schaut Euch das mal an.

Ich hatte schon im Blog: SQLite im Browser mit HTTP-Range-Requests, sodass man in einigen Use-Cases ein Backend wegrationalisieren und durch eine statisch gehostete SQLite-Datei ersetzen kann.

Später kam dann darauf basierend ein statisch gehostetes Wikipedia mit Suchfunktion.

Das nächste Level ist jetzt die oben verlinkte Seite: Die SQLite-Datei nicht per HTTP servieren, sondern mit BitTorrent. Was viele nicht wissen: Torrents gehen mittlerweile auch im Browser, über WebRTC. libtorrent unterstützt das auch, damit auch die bekannten Clients wie Deluge.

Was macht man mit so einer dezentralen SQLite-DB? Die Demo ist jedenfalls eine Torrent-Suchmaschine, die Magnet-Links zurückgibt.

Ich bin von dieser Kreativität beeindruckt. Eingedampft hat man hier eine HTML-Seite mit ein bisschen JavaScript, die man statisch hosten kann. Bekommen tut man eine Torrent-Suchmaschine.

Cloudflare hat für ihre Workers jetzt eine Datenbank: D1.

Was mich freut, ist, dass die auf SQLite basiert. Das zeigt, wie ausgewachsen SQLite ist.

In dem Beispiel ihres Use-Cases sieht man, dass sie Raw-SQL für die DB-Abfragen benutzen. Für genau diesen Use-Case baue ich gerade an einer prototypischen Lösung: sequelts. Die Idee: Man nutzt die Turingvollständigkeit vom TS-Typsystem, um SQL-Querys auf Typebene zu parsen und den Rückgabetypen zu inferieren. Bald wird es als proof-of-concept noch mit sowas wie sql-template-strings kombiniert. Wenn man das hat, hat man die Richtung für’s Abfragen von Daten typsicher (und mit erzwungenen Prepared Statements auch “secure”). Danach schaue ich weiter, in wie weit man sicherstellen kann, dass es keine Veränderungen an den Daten gibt, die nicht zulässig sind. Das steht aber noch in den Sternen.

Heute lernte ich: Die SQLite-CLI kann ZIP-Dateien öffnen. Der Inhalt ist dann in Form einer virtuellen Tabelle sichtbar.

Weiter unten wird auch ein Command vorgestellt, das einem Indexe empfiehlt.

How the SQLite Virtual Machine Works.

Wir hatten ja schon SQLite nach WASM kompiliert im Browser. Jetzt gibt es auch Postgres im Browser.

Index Merges vs Composite Indexes in Postgres and MySQL.

SQLite Internals: Pages & B-trees .

SQLite hat aktuell einen concurrent writer. Aktuell arbeiten sie an einer Erweiterung des DB-Formats, womit diese Limitierung entfällt: HC-tree Der Multi-Process-Support war deshalb immer etwas hakelig. Das zu verbessern ist jetzt eine Designziel.

Hier sind noch ein paar Benchmarks.

Situation: Man hat eine Tabelle mit vielen Einträgen und möchte aber einen großen Teil (z. B. alte Einträge) löschen. Problem: Ein einfaches DELETE FROM logs WHERE created_at < '2023-04-01' kann sehr lange dauern und damit das Schreiben in die Tabelle blockieren. Ein Index hilft da leider nur bedingt.

Eine mögliche Lösung: Partitionieren der Tabelle. Indem man die Tabelle nach dem Löschkriterium partitioniert, kann man das Droppen von Einträgen deutlich beschleunigen.

Beispiel aus Postgres dazu:

CREATE TABLE logs (
  -- more columns...
  created_at timestamptz NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE logs_2023_01 PARTITION OF logs FOR VALUES
  FROM ('2023-01-01 00:00:00+00') TO ('2023-01-31 23:59:59+00');
CREATE TABLE logs_2023_02 PARTITION OF logs FOR VALUES
  FROM ('2023-02-01 00:00:00+00') TO ('2023-02-28 23:59:59+00');
CREATE TABLE logs_2023_03 PARTITION OF logs FOR VALUES
  FROM ('2023-03-01 00:00:00+00') TO ('2023-03-31 23:59:59+00');
CREATE TABLE logs_2023_04 PARTITION OF logs FOR VALUES
  FROM ('2023-04-01 00:00:00+00') TO ('2023-04-30 23:59:59+00');

ALTER TABLE logs DETACH PARTITION logs_2023_01 CONCURRENTLY;

DROP TABLE logs_2023_01;

Dieses Beispiel hab ich aus SQL-for-Devs übernommen. Dort gibt’s auch noch eine detailliertere Beschreibung sowie ein MySQL-Beispiel, wer mehr wissen möchte.

Ein paar Footguns bei Postgres. Hier ein Rechner für ein paar Postgres-Parameter.

Postres-Wiki-Seite zum Thema Don’t do this.

Auf der orangen Seite gibt es noch mehr.

Understanding database Indexes in PostgreSQL.

The Many Faces of DISTINCT in PostgreSQL.

SQL 2023 ist da. Der Standard kostet natürlich Geld. Hier gibt es eine Übersicht über die neuen Features. Interessant sind “Property Graph Queries”. Beispiel aus dem verlinkten Post:

CREATE TABLE person (...);
CREATE TABLE company (...);
CREATE TABLE ownerof (...);
CREATE TABLE transaction (...);
CREATE TABLE account (...);

CREATE PROPERTY GRAPH financial_transactions
    VERTEX TABLES (person, company, account)
    EDGE TABLES (ownerof, transaction);

SELECT owner_name,
       SUM(amount) AS total_transacted
FROM financial_transactions GRAPH_TABLE (
  MATCH (p:person WHERE p.name = 'Alice')
        -[:ownerof]-> (:account)
        -[t:transaction]- (:account)
        <-[:ownerof]- (owner:person|company)
  COLUMNS (owner.name AS owner_name, t.amount AS amount)
) AS ft
GROUP BY owner_name;

Es gibt in SQL ein SELECT FOR UPDATE (MariaDB, Postgres), was man in Transaktionen benutzen kann, um schon frühzeitig zu sagen, dass man die entsprechenden Zeilen updaten wird, damit sie schon direkt gelockt werden können. Dadurch kann verhindert werden, dass Transaktionen unnötig neu versucht werden müssen.

Man kann sich mit einer SQL-DB ja eine Task-/Message-Queue für Arme bauen. Das ist besonders dann eine mögliche Lösung, wenn man noch kein ein Queuing-System hat oder die Anzahl an Messages pro Sekunde überschaubar ist. In diesem Fall kann man nicht nur SELECT FOR UPDATE verwenden, sondern auch zusätzlich noch SKIP LOCKED. Durch das SELECT FOR UPDATE wird ja nicht verhindert, dass sich ein anderer Worker die Nachricht zusätzlich noch holt. Mit SELECT FOR UPDATE SKIP LOCKED kann man die Zeilen überspringen, die gerade in einer Transaktion sind.

Die CLI von Postgres hat ein Watch-Command.

SQL reserved words – An empirical list.

Tief einatmen: MySQL Introduces Javascript Support in Preview

Postgres kann Indizes mit Bloom-Filter.

Use-Case:

In the case of an index access method, it allows fast exclusion of non-matching tuples via signatures whose size is determined at index creation. […] This type of index is most useful when a table has many attributes and queries test arbitrary combinations of them. A traditional btree index is faster than a bloom index, but it can require many btree indexes to support all possible queries where one needs only a single bloom index. Note however that bloom indexes only support equality queries, whereas btree indexes can also perform inequality and range searches.

Common DB schema change mistakes (in Postgres).

Postgres 17.

SQLite Index Visualization

Reads causing writes in Postgres.

A faster way to copy SQLite databases between computers.

Manchmal hat man in Datenbanken ein paar Felder, die Ranges darstellen und verschiedene Zeilen sich in den Ranges jeweils nicht üershcneiden dürfen, z. B. bei Reservierungen. Postgres hat da was für, um auf Schema-Ebene zu garantieren, dass man da keine Überlappungen bekommt: Time Ranges without Overlapping.

Grundlage dafür sind Range Types in Postgres.

Making Postgres 42,000x slower because I am unemployed

Postgres hat ja LISTEN/NOTIFY als Pub/Sub für Menschen, die sich für ein kleines Feature kein Redis hinstellen wollen.

Damit gibt’s auf großem Scale ein Problem: Wenn NOTIFY in einer Transaktion ausgeführt wird, lockt es die gesamte Datenbank.

Die Orange Seite hat noch ein paar Tipps, was man stattdessen benutzen kann.

Postgres 18 ist da. Neu sind: Async IO, virtual generated columns, uuidv7(), OLD/NEW bei RETURNING und noch ein paar andere Sachen.

Why TigerBeetle is the most interesting database in the world.

Node.js hat ja schon SQLite eingebaut bekommen. In der neuen Node.js 24.9 gibt es dafür jetzt eine Erweiterung. Die API ist ähnlich zu der von Bun oder den tagged-template-strings, die ich mal als PoC statisch typisiert habe.

Aussehen tut das so:

import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
const sql = db.createSQLTagStore();

db.exec('CREATE TABLE users (id INT, name TEXT)');

// Using the 'run' method to insert data.
// The tagged literal is used to identify the prepared statement.
sql.run`INSERT INTO users VALUES (1, 'Alice')`;
sql.run`INSERT INTO users VALUES (2, 'Bob')`;

// Using the 'get' method to retrieve a single row.
const id = 1;
const user = sql.get`SELECT * FROM users WHERE id = ${id}`;
console.log(user); // { id: 1, name: 'Alice' }

// Using the 'all' method to retrieve all rows.
const allUsers = sql.all`SELECT * FROM users ORDER BY id`;
console.log(allUsers);
// [
//   { id: 1, name: 'Alice' },
//   { id: 2, name: 'Bob' }
// ]

Die Querys werden intern prepared und in einem LRU-Cache abgelegt. In den Docs steht leider nicht, wie man Arrays oder Sub-Querys übergibt. Das wird sicher bald bei den Typen nachgereicht.