Structured Query Language, the standard language for interacting with relational databases. It covers querying data with SELECT, modifying it with INSERT, UPDATE, and DELETE, defining schemas with CREATE and ALTER, and controlling access with GRANT and REVOKE. SQL is the universal interface to databases like PostgreSQL, MySQL, SQLite, and SQL Server.
Relational databases have stored XML data for decades, but the mechanism for querying it is not SQL. XPath is a separate expression language with its own syntax, its own type system, and its own limitations. The SQL functions that expose XPath—PostgreSQL’s xpath() and xpath_exists(), Oracle’s XMLExists and XMLQuery, SQL Server’s xml methods—are bridges between two…
Vector search brings the core operation of modern AI applications—finding items that are semantically similar—into the relational database. An embedding is a high-dimensional vector of floating-point numbers that represents the meaning of text, an image, or any other data. Two embeddings that are close in vector space represent items with similar meaning. pgvector is the…
Change Data Capture is the pattern of detecting row-level changes—inserts, updates, and deletes—as they happen, and streaming them to downstream systems. The two dominant approaches in relational databases are log-based CDC, which reads the database’s transaction log, and trigger-based CDC, which installs database triggers that write to an audit table. Debezium is the de-facto open-source…
A Foreign Data Wrapper is PostgreSQL’s implementation of the SQL/MED standard: a library that lets the database treat data residing outside PostgreSQL as if it were a local table . The wrapper handles the protocol for connecting to the external source, fetching rows, and returning them to the PostgreSQL executor. The postgres_fdw extension is the…
Dynamic SQL is the construction of a SQL statement as a string at runtime, then executing it. It exists because some parts of a query cannot be parameterized: table names, column names, sort directions, and identifiers that the query planner needs to see as structure rather than data. The hazard is that the same mechanism…
Database auditing is not a single mechanism but a spectrum of approaches that differ in what they capture, where the record is stored, and whether the record can be tampered with by the same principals being audited. The choice of approach is determined by the compliance framework and the threat model, not by convenience. A…
Cross-database migration and schema evolution are two different problems that are often confused because both involve moving data and changing structure. A cross-database migration moves data from one database engine to another—PostgreSQL to SQL Server, MySQL to PostgreSQL, on-premises to cloud. Schema evolution changes the structure of a database while it is running, with the…
Database unit testing frameworks exist because the logic inside stored procedures, functions, and triggers is code, and code that is not tested accumulates defects silently. The challenge is that database code does not return values or throw exceptions in the way that application code does. A stored procedure communicates through result sets and output parameters….
Database benchmarking is the discipline of measuring a system’s performance under a workload that is defined, repeatable, and representative of the use case. The three tools that dominate the conversation serve different purposes. sysbench is a lightweight, multi-threaded benchmark tool for quick impressions of system performance, with built-in tests for CPU, memory, file I/O, and…
A database driver is a translator. The application speaks in function calls and typed objects; the database speaks in a binary protocol over a socket. The wire protocol is the contract between them: a sequence of messages with defined framing, a startup handshake that negotiates capabilities and authenticates the user, and a query phase that…