标签：#sql

In Blog 5, we built a SQL parser. It can take this: INSERT INTO payments VALUES ( 500 , payment_1 , pending , 1 ) and turn it into a struct: InsertIntoTable { TableName : "payments" , ColumnValues : [] string { "500" , "payment_1" , "pending" , "1" }, } But this is still not enough for the storage engine. Our storage engine only knows how to store key-value pairs. It does not know what a table is. It does not know what a column is. It does not know that 500 is an integer, pending is a string, and 1 is a boolean. So, in this post we solve the missing bridge of persisting these in our key-value store. CREATE and INSERT are PUT operations This is the first major realisation. A key-value store is extensible to store literally anything. This is what we have been saying from the first post itself. But now we will be taking actual examples to prove that. CREATE TABLE Example Let's start with the create table example and see what should be the key and the value. Serialisation The key should be something that uniquely identifies the table, which is straightforward enough in this case as the table name . The value becomes everything else except the key, which is the schema of the table. So, in order to store the table name, we can append a reserved keyword as prefix like schema as a unique identifier. The structure of the key becomes _schema:<table_name> . The next question to answer is: How do we store a struct like below into our key value store where the value is always string? CreateTable { TableName : "payments" , ColumnDetails : [] Column { { ColumnName : "amount" , DataType : Int }, { ColumnName : "id" , DataType : String }, { ColumnName : "status" , DataType : String }, { ColumnName : "captured" , DataType : Bool }, }, PrimaryKeyColumnPosition : 1 , } One way is to serialise the entire struct into a string and store that directly. But in that case, deserialisation is a complex logic. JSON or struct serialisation and deserialisation is both space-heavy and compute inte

Building a personalization engine for a multi-format content feed, without machine learning, and the testing process that forced us to rebuild it. TL;DR We run a collaborative cultural curation platform (think: user-submitted recommendations for movies, books, games, music, and long-form posts, all mixed into one feed) on a fairly ordinary PHP + MySQL stack. Over about a year we went through two full generations of the feed ranking algorithm. The first version solved the obvious problem (stop being purely chronological) but quietly failed at real personalization. The second version fixed that by rethinking what "user taste" even means, moving scoring out of SQL and into application code, and adding a layer of post-ranking business rules. This post walks through both generations, why the second one had to happen, and how we actually tested and calibrated a feed ranking system without a data science team or an ML pipeline. No exact weights, table names, or formulas below — just the engineering story. The starting problem: one feed, five content shapes Before personalization is even on the table, a multi-format feed has a normalization problem. Movies, books, games, music, and editorial posts live in different tables, with different columns, different publishing cadences, and engagement numbers on completely different scales. "1,000 likes" on a music post and "1,000 likes" on a book review are not the same signal. So the very first architectural decision — before any ranking logic existed — was building a unification layer that maps every content type into a shared shape (type, author, title, cover, category, engagement counters, timestamp) before any scoring happens. Everything downstream depends on that layer being consistent. Generation 1: a weighted ranker living inside a single SQL query The first real version of the algorithm — internally we called it the hybrid model — had a modest goal: get away from a purely chronological feed without building anything resembl

The query every backend developer has needed and nobody enjoys writing In March 2016, npm removed an 11-line package called left-pad. Within minutes, builds began failing across the JavaScript ecosystem. It broke thousands of projects, including tools like Babel. Many developers didn't choose left-pad directly; it was hidden in their dependencies and went unnoticed until it vanished. That incident points at a question you have probably asked about your own stack: what is actually in my dependency tree? Not just the 30 packages in your package.json . Everything they pull in, and everything those pull in, all the way down. In a relational database, dependencies live in a self-referencing join table. "Everything, all the way down" means a recursive CTE. Here is that query on a snapshot of the npm registry. It finds the full runtime dependency tree of express : WITH RECURSIVE closure ( name , depth ) AS ( SELECT depends_on_name , 1 FROM dependencies WHERE package_name = 'express' AND dep_type = 'runtime' UNION SELECT d . depends_on_name , c . depth + 1 FROM closure c JOIN dependencies d ON d . package_name = c . name AND d . dep_type = 'runtime' ) SELECT count ( DISTINCT name ) AS transitive_deps , max ( depth ) AS max_depth FROM closure ; Here is the result: It works. Here it shows 63 packages, 11 levels deep. But it is twelve lines, and every line matters. There is an anchor part, a recursive part, and a UNION doing quiet work to remove duplicates. A graph asks the same question in two lines: MATCH ( :Package { name: 'express' }) - [ :DEPENDS_ON * ] -> ( dep ) RETURN count ( DISTINCT dep ) AS transitive_deps That is not a shortened excerpt. That is the whole query. It returns the same 63 packages. Cypher is Neo4j's query language. For a SQL developer, it is less a new language than a new notation for questions you already know how to ask. This article proves that claim with ten translations. They run from "this is just SQL with arrows" up to the query above, plus one

Problem Description Merge multiple structurally similar tables with different column names into a wide table using full outer joins by common ID. Four tables have similar structures, each with two fields. The fields have the same meaning but different names (id, id2, id3, id4 all represent ID). The goal is to merge the four tables into single rows by ID, with each ID appearing in exactly one row. When an ID is absent in a table, the corresponding columns take NULL. Source Data T1 table: T2 table: T3 table: T4 table: * Expected Result * For example, ID=555 appears in both T1 and T2 but not in T3 or T4, so id, colA, id2, colB have values, while id3/colC/id4/colD are NULL. ID=222 only appears in T3, so only id3 and colC have values; all other columns are NULL. ID=10 appears in T2 and T4 but not in T1 or T3, so id2, colB, id4, colD have values; all other columns are NULL. SQLazy Step-by-Step Implementation Core Idea: First use derive to unify the ID column names of each table to ID_main, making subsequent merging easier. Then start from the first table and perform full outer joins one by one: use join to full outer join the current result with the next table on ID_main, then use derive and nvl to merge the new ID into the ID_main column, appending tables one by one to get the final result. [ Click to run this example online ] The steps are explained below. Steps 1-4: Unify ID Column Names Across Tables derive id as ID_main, id, colA Use derive on T1-T4 to rename their respective ID column names (id/id2/id3/id4) uniformly to ID_main. Step 5: Full Outer Join T1 and T2 join ID_main; with t2; ID_main; take id2, colB; full Use the join function to full outer join t1 and t2 on ID_main. Step 6: Merge NULLs in ID Column derive nvl(ID_main, id2) as ID_main, id, colA, id2, colB If a record comes from t2 but is not present in t1, its ID_main is NULL. This step assigns t2.id2 to ID_main in such records, ensuring the ID_main column always has a value. Different SQL implementations u

Continuando com a saga de siglas, encontrei de maneira simplista a versão oposta do ACID, o BASE....

In Parts 1-4, we built a transactional key-value store. It has WAL for durability, memtables and SSTables for storage, compaction to control file growth, and transactions for atomic multi-key writes. Now we move to the query layer where users can actually fire SQL queries like: CREATE TABLE payments ( amount INT , id STRING , status STRING , captured BOOL , PRIMARY KEY ( id )) INSERT INTO payments VALUES ( 500 , payment_1 , pending , 1 ) SELECT * FROM payments WHERE id = payment_1 In this blog and the next one we answer: How do we translate SQL strings into operations our key-value store already understands? This post focuses on the first half of that bridge, which is parsing SQL into structured commands. In the next post, we will take those commands and turn CREATE TABLE and INSERT into bytes on disk. The Core Idea: SQL Becomes Structured Data The storage engine does not understand SQL. It understands keys, values, WAL entries, memtables, SSTables, and transactions. So the SQL layer has two jobs: Parse a human-readable SQL string into a structured object. Translate that structured object into key-value operations. For example: CREATE TABLE payments (...) -> CreateTable{TableName: "payments", ColumnDetails: ...} INSERT INTO payments VALUES (...) -> InsertIntoTable{TableName: "payments", ColumnValues: ...} SELECT * FROM payments WHERE id = payment_1 -> SelectFromTable{TableName: "payments", QueryConditions: ...} Once we have these structs, the rest of the database can stop dealing with raw strings. Why Not Parse SQL Directly in the DB Layer? Imagine if db.CreateTable() directly walked through the SQL string and also updated storage. That would mix two very different responsibilities: parsing grammar, executing database operations. Keeping them separate makes the system easier to reason about. The parser validates syntax and builds an AST. The DB layer receives that AST and decides what to store. An AST, or Abstract Syntax Tree, is just a structured representation of

The lost-update bug that quietly corrupts homegrown wallet balances — and the five disciplines we used across PayWithToken to make money movement correct under concurrency. There is a bug that lives in a large share of the world's homegrown wallet systems. It doesn't throw an error. It doesn't show up in tests. It surfaces months later as a balance that is quietly, inexplicably wrong — and in a payments system, a wrong balance is either a customer who has lost money or a company that has given it away. This is the story of that bug, why the "obvious" wallet code causes it, and the handful of disciplines we used across PayWithToken to make money movement correct under concurrency. The bug: lost updates Here is wallet code almost everyone writes first. Credit a user's balance: // DON'T do this $row = $db->query("SELECT balance FROM users WHERE id = $id")->fetch(); $new = $row['balance'] + $amount; $db->exec("UPDATE users SET balance = $new WHERE id = $id"); Read the balance, add to it in PHP, write it back. It works perfectly — until two things happen at the same time. Picture a wallet at ₦1,000. Two credits of ₦500 arrive simultaneously — say a bank webhook and the user tapping "confirm" on their phone: Request A reads balance = 1000. Request B reads balance = 1000 (A hasn't written yet). A computes 1500, writes 1500. B computes 1500, writes 1500. Two credits landed; the balance rose by ₦500. ₦500 vanished. This is a lost update, and it is a race condition, which means it is invisible until you have real concurrent traffic — exactly when you can least afford it. The debit version of the same bug lets a balance go negative or double-spends a token. Fix #1: let the database do the arithmetic The read-modify-write happened in PHP, across three round trips, with a gap where another request could interleave. The fix is to make the update a single atomic statement and let the database's row lock serialise it: // DO this — one atomic statement $db->prepare("UPDATE users SET

Originally published at wostal.eu . TL;DR : My Hetzner k3s lab quietly became a platform. Dozens of operators with leader-election leases hammered the default datastore — SQLite via kine — until compaction entered a death-spiral: 1.36M rows, a 13.8 GB WAL that wouldn't checkpoint, CPU pinned at 99%, load average 79 on 8 cores. I stopped the bleeding by truncating the WAL, then migrated the control plane to embedded etcd (7.5 GB SQLite → 313 MB etcd, load 79 → 5). This is the full postmortem — and the lessons. This is a war story, not a tutorial. It's about the moment a homelab stops being a homelab and starts behaving like production — without ever announcing it. The cluster in question, homelab , is the Hetzner k3s setup I wrote about previously . It started small. It did not stay small. In this post I'll cover: How an overgrown lab broke the default datastore — the kine/SQLite compaction death-spiral The firefight — measuring instead of guessing, and the fix that actually worked The permanent fix — migrating the control plane to embedded etcd, and the honest caveats The meta-lesson — how to recognize when your lab has become a platform A diagnostic runbook — so next time it's minutes, not hours There's a companion piece to this incident. The CI pipeline that ran this etcd migration was itself freshly — and badly — migrated, and debugging it cost me hours over a single missing newline. I split that into its own post: I Let an AI Re-Platform My CI Pipeline. Here's What Broke. Context: it's "just a homelab" — except it isn't homelab began like any homelab: one k3s node on Hetzner, a few things to play with. The problem is that over months it quietly became a platform . A single master node ( cx43 , 8 vCPU / 16 GB, untainted, and also carrying Longhorn and workloads) now runs: ArgoCD, Kargo, Crossplane/Upbound, CloudNativePG, EMQX, Longhorn, trivy-operator, kubescape, Gatekeeper, Goldilocks/VPA, VictoriaMetrics, Loki, OpenTelemetry, Argo Workflows/Events/Rollouts, kga

From the outside, social features seem like something straightforward: follow a user, like a post, see a feed, but when you attempt to implement them at any real scale you find that every single one is something you've got to be aware of, a pattern, a well-documented query pattern with all its failure modes. Here's a step-by-step look at four of them: adjacency list, fan-out feed, mutual-friends join, and some graph traversal that SQL wasn't really designed for. The adjacency list and why "who do I follow" isn't free A follow relationship is usually modeled as a plain adjacency table: follows(follower_id, followee_id, created_at) . That's the whole schema, and it's deceptively adequate for a long time. The first place it breaks is when you need "who do I follow that also follows them", mutual connections, because that's a self-join against the same table: SELECT f2 . followee_id FROM follows f1 JOIN follows f2 ON f1 . followee_id = f2 . follower_id WHERE f1 . follower_id = : user_id AND f2 . followee_id != : user_id ; This query is ok if the fan-out is low. It feels like it's no longer fine when a few accounts have a few hundred thousand joins, because join now needs to walk a correspondingly large intermediate result set per request. The typical solution isn't a better query, it's a better index and a cap. composite indexes on (follower_id, followee_id) and (followee_id, follower_id) so both directions of the join hit an index-only scan, plus a LIMIT applied early so the planner doesn't materialize more rows than the response will ever use. The fan-out feed problem The “social systems” hard problem is the timeline: display to me my feed of what people I follow posted recently, in order. There are two ways to construct it and they are both right - anything else and outages happen. Fan-out on write means that when a user posts, you insert a row into every follower's feed table immediately. Reads are then a trivial SELECT * FROM feed WHERE user_id = :id ORDER BY creat

Originally published at nlqdb.com/blog The standard agent-memory build is an afternoon of work: embed every fact worth keeping, upsert it into a vector store, and before each reply pull the top-k most similar memories back into context. And for what it's built for, it works. Ask "what did this user say about the Berlin migration" and the right snippets come back, ranked by cosine distance. Recall is solved enough that it feels like memory is solved. Then the agent has been running for a month, and you ask its memory a different kind of question: "how many users asked about pricing this month?" "Average deal size per stage?" "Top 10 topics I logged, ranked by count?" The store dutifully returns the twenty memories most similar to the question text , the LLM eyeballs them, and you get a confident, specific, wrong number. Recall is similarity. Reporting is aggregation. Nothing malfunctioned — the two questions want different machines. A vector store's primitive is nearest-neighbour search: embed the query, rank stored vectors by distance, return the top-k, optionally narrowed by a metadata filter. That is the whole contract. There is no COUNT , no GROUP BY , no JOIN , no HAVING — a similarity engine ships no query planner, and even the metadata filter only narrows candidates around the approximate search, so what comes back is still a ranking of similar items, never a computed result set. "How many" has to touch every matching row . If the agent logged 4,000 memories and top-k is 20, the context the LLM sees is structurally incapable of producing the count — and an LLM doing arithmetic over a retrieved sample is a hallucination generator, not a query engine. The failure is quiet, too: the answer arrives fluent and plausible, and nothing flags that it was computed from half a percent of the data. -- "top topics this month, ranked by count" is not a similarity query. -- It's this — and it must scan every matching row, not the top-k: SELECT topic , count ( * ) AS mentions

Disclosure: I build Schemity , a desktop ERD tool - this post is from our blog and uses it for the examples. TL;DR: Database views carry real responsibilities - reporting layers, security boundaries, API surfaces - but ERD tools either leave them out entirely (DBML has no view support despite requests since 2022) or draw them as if they were ordinary tables. Schemity displays views and materialized views as read-only entities with italic names and a bold view or mview token in the entity footer, so derived relations are distinguishable from base tables at a glance, and they can be imported into context views like any other entity. A database view belongs in your ERD, but not disguised as a table: it is a derived, read-only relation, and the diagram should say so at a glance. Schemity draws views and materialized views as read-only entities with italic names - present on the canvas, visually distinct from the base tables they are built on. That sentence would be unremarkable if the rest of the tooling world agreed with it. Mostly, it does not. In most ERD tools your views are simply absent, and in the rest they are dressed up as something they are not. The reporting layer your diagram pretends does not exist Views are not decoration. They are where schemas put their public face: the reporting layer that joins five tables into one readable relation, the security boundary that exposes a subset of columns to an application role, the compatibility shim that survives a refactor. On Supabase , views are how you shape what PostgREST exposes as an API. A materialized view may be the single most performance-critical object in an analytics schema. Whoever reads your diagram to understand the system needs to see them. Yet the diagram usually cannot show them. DBML - the schema language behind dbdiagram.io - has no syntax for views at all: a user proposed designing views with join definitions in December 2022, others were still upvoting the request in July 2024, and there has be

Manticore Search 28.6.6 has been released. The headline additions are UUID document IDs for real-time tables and ordered, limited GROUP_CONCAT() for grouped queries. The release also includes 16 fixes for backups, replication, query processing, SQL compatibility, and secondary indexes. This post covers everything shipped from 28.4.5 through 28.6.6 . Upgrade notes There are no new mandatory data migrations in this release. UUID IDs are an opt-in table definition: existing numeric-ID tables keep working as they are. If you want UUID identifiers, create a real-time table with id uuid ; ALTER TABLE cannot convert an existing table between numeric and UUID IDs. Two fixes are particularly useful for production installations. Successful backups now always unfreeze real-time tables when they finish (previously in rare cases they didn't), rather than leaving writes blocked. And authenticated replication can again add an existing populated RT table with ALTER CLUSTER ... ADD . UUID document IDs for real-time tables Applications often already have UUID identifiers from the system of record. Until now, using them with Manticore Search meant maintaining a separate numeric ID mapping. Real-time tables can now use UUID document IDs directly: CREATE TABLE products_uuid ( id uuid , title text , price int ); Manticore accepts an explicit UUID string, or generates one when id is omitted from an insert or replace. UUID equality and IN filters work in queries, and UUID IDs can be used with REPLACE , UPDATE , and DELETE . This is currently a real-time-table capability, including columnar and replicated RT tables. Plain, percolate, and sharded tables continue to use their existing ID models. Ordered and limited GROUP_CONCAT() Grouped results often need a compact preview of the most relevant values in each group. GROUP_CONCAT() can now sort values and retain only the requested number of them in explicit SQL GROUP BY queries: SELECT category , GROUP_CONCAT ( title ORDER BY price DESC SEPARA

Databricks is expanding its Lakebridge toolkit by introducing an agentic code conversion feature designed to help organizations migrate from legacy data warehouses. This new capability uses Genie Code to rewrite complex SQL scripts, allowing customers to transition their workloads to the Databricks lakehouse environment with higher efficiency and less manual intervention. Advanced Automation for Complex Code Translation The core of this update is the agentic code converter, a system that utilizes AI subagents to manage the heavy lifting of migration projects. These agents perform a variety of tasks including deep analysis of source code and the parallel conversion of multiple files. They also validate translated SQL and can autonomously retry sections that fail during the initial pass. This iterative approach is a significant step forward from traditional methods that often require human developers to step in when software hits a wall. By allowing developers to set specific migration rules for unique enterprise SQL structures, the tool provides a level of customization that previous automated systems lacked. The Lakebridge suite already offers several transpilation engines, such as the pattern-based BladeBridge technology and the compiler-based Morpheus engine. However, the addition of agentic AI introduces a reasoning layer that these older technologies do not possess. This reasoning is vital for moving beyond simple syntax mapping and into the realm of complex logic. Traditional transpilers like Morpheus are excellent at handling standard syntax mapping. They easily manage date functions and basic join commands. Problems arise when these tools encounter control-flow reasoning, cursors, or dynamic SQL that is generated at runtime. These complex elements often differ significantly across platforms like Oracle or Teradata. Industry experts note that these difficult sections usually represent about 15 percent of a codebase but consume the vast majority of manual labor

Introduction When building a Flutter application, you'll often need to store data on the user's device. For example: Saving user preferences Storing login information Caching API responses Creating offline applications Building note-taking or to-do apps While there are several local storage solutions available, Hive is one of the fastest and easiest local storage for Flutter developers. In this tutorial, you'll learn Hive from scratch by building a simple example. No prior database knowledge is required. What is Hive? Hive is a lightweight, NoSQL database written entirely in Dart. It stores data directly on the device, making it perfect for Flutter applications. Why use Hive? Extremely fast Works offline No native platform code required Simple API Easy to learn Great for small and medium-sized applications Think of Hive as a collection of boxes where each box stores your application's data. Hive ├── User Box ├── Settings Box ├── Notes Box └── Products Box Each Box is similar to a table in traditional databases. Step 1: Create a Flutter Project Create a new Flutter project. flutter create hive_demo Open the project. cd hive_demo Step 2: Install Hive Open pubspec.yaml and add the following packages. dependencies : flutter : sdk : flutter hive : ^2.2.3 hive_flutter : ^1.1.0 Then install them. flutter pub get Step 3: Initialize Hive Before using Hive, initialize it inside main() . import 'package:flutter/material.dart' ; import 'package:hive_flutter/hive_flutter.dart' ; void main () async { WidgetsFlutterBinding . ensureInitialized (); await Hive . initFlutter (); await Hive . openBox ( 'settings' ); runApp ( const MyApp ()); } Here we open a box called settings . Step 4: Understanding Boxes A Box is where Hive stores data. Imagine this box: Settings Box theme -> dark username -> Alex loggedIn -> true Keys are on the left. Values are on the right. Step 5: Save Data Saving data is incredibly simple. var box = Hive . box ( 'settings' ); box . put ( 'username' , 'John' );

On May 20, 2026, AWS ended support for DMS Fleet Advisor. Fleet Advisor answered a question every migration team asks first: what is actually in my database estate, and how hard will it be to move? It was free. It was fully managed. It was backed by the largest cloud provider on earth. It still lost. AWS's official notice says only: "After careful consideration, we decided to end support for AWS DMS Fleet Advisor." No reason given. But you don't need one — the documentation tells you. Here is what Fleet Advisor required before it would tell you a single thing about your databases: Install a standalone data collector in your local environment Create an Amazon S3 bucket Create IAM policies, roles, and users — via CloudFormation, which was the recommended path Create database users with the minimum required permissions on every source Establish network access from the collector to each database server Then you'd meet the ceilings: recommendations for up to 100 databases at a time, one-to-one target mapping only, no multitenant server support. Now picture running that gauntlet inside a bank. You are a Business Solution Architect. You have been asked to scope a migration. You do not yet have approval for the migration — that approval is what the assessment is for . And to produce the assessment, you must first request production database credentials, get an agent binary through software approval, provision an S3 bucket, and get an IAM stack past a security review. That is a six-week procurement conversation to answer a question you were hoping to answer this week. AWS's replacement recommendation is Migration Evaluator — a consulting-led engagement. Read that as the finding it is: AWS looked at self-serve migration assessment, and concluded that humans and services do it better than a product. I think they were half right. And the half they got wrong is the interesting part. The lesson: friction is a competitor, and it usually wins We talk about developer tools as if the

Before we start This is a series about the intermediate results of an ongoing effort, not a finished story. It isn't an academic paper — it's a record of real engineering work and the insights that emerged along the way. Also, it's not about AI generating code. The AI angle here is about investigation and research — a careful, governed use of AI as a tool, not an autopilot — something I'll come back to in the final part. A word on why now, with the project still unfinished: details fade — the small technical decisions, the intermediate observations, the context in which a given call was made. Writing this down while the work is still ongoing is partly how I keep that context from slipping away. And that context matters: it's a reminder that every past decision, mine or anyone else's, was made for reasons that made sense at the time. One more note: none of this happened instead of product work. All of it ran alongside building new features and fixing bugs — the roadmap never paused for it. On confidentiality: I don't name the Customer, and I avoid any personal data or details a competitor could use. For the same reason, I don't mention anyone by name and refer to colleagues only by role. I won't name them, but I want to acknowledge up front that much of what follows was only possible thanks to the people I work with. The numbers are approximate and rounded — the point is the order of magnitude and the reasoning, not the exact figure. And a framing to carry through the series: at this scale, optimization is less a sprint than a marathon — yes, probably the most overused metaphor around, but here it genuinely fits: steady pacing beats sprinting, and you get there one careful step at a time. How I ended up here I'm a software engineer, and I've spent most of my career close to backends and databases. I've also led teams as a technical team lead — though over time I've deliberately shifted back toward more hands-on technical roles, which is where I'm most effective and m

INTODUCTION Creating Greenwood academy database is essential for managing the students, subject and exam results efficiently. PostgreSQL, a powerful open-source relational database system, offers the perfect foundation for such a project. The main areas areas in SQL covered in this projects are : 1. DDL (Data Definition Language) DDL commands define, modify, and change the physical structure of database objects like tables and schemas. The first step is to create a greenwood academy schema using the create command. create schema greenwood_academy ; set search_path to greenwood_academy ; Next is to crete tables in the schema; The schema has 3 tables students,subject and exam results. create table greenwood_academy . students ( student_id INT PRIMARY key , first_name VARCHAR ( 50 ) NOT null , last_name VARCHAR ( 50 ) NOT null , gender VARCHAR ( 1 ), date_of_birth DATE , class VARCHAR ( 10 ), city VARCHAR ( 50 ) ); create table greenwood_academy . subject ( subject_id INT PRIMARY key , subject_name VARCHAR ( 100 ) NOT null unique , department VARCHAR ( 50 ), teacher_name VARCHAR ( 100 ), credits INT ); create table greenwood_academy . exam_results ( result_id INT PRIMARY key , student_id INT NOT null , subject_id INT NOT null , marks INT NOT null , exam_date DATE , grade VARCHAR ( 2 ) ); ALTER - This command changes the structure of tables in a database. Core Actions You Can Perform Add columns : Insert a new column and its data type into a table. The school realised that the nthey forgot to add phone numbers in the students table. The following command is used to add the data alter table greenwood_academy . students add column phone_number VARCHAR ( 20 ); Rename colums : Change the name of a table or a column. The column credit has to be changed to credit hours alter table greenwood_academy . subject rename column credits to credit_hours ; Drop columns : Delete an unwanted column from a table. Later the school relised that the phone number column is nolonger needed. a

SQL is quite flexible, making it easy to write a single query that works for two situations: one without a parameter and a WHERE clause, and another with a parameter for filtering, all in the same SQL query. For example, I came across a benchmark comparing MongoDB and PostgreSQL that shows how to handle pagination effectively—by avoiding OFFSET and instead using the last value to fetch the next set of results. The first page includes a WHERE clause along with ORDER BY and LIMIT, while the following pages add an extra WHERE condition. In the MongoDB version of this benchmark, the filter is handled within the application, which leads to two separate queries for these scenarios. export async function getOrders ( cursor ) { const match = cursor ? { timestamp : { $gt : new Date ( cursor ) } } : {}; const rows = await orders . aggregate ([ { $match : match }, { $sort : { timestamp : 1 } }, { $limit : PAGE_SIZE }, ]) We can do the same in PostgreSQL using a single prepared statement. SQL is such a powerful language that it often feels tempting to write it this way: SELECT * FROM orders WHERE $ 1 :: timestamptz IS NULL OR "timestamp" > $ 1 ORDER BY "timestamp" ASC LIMIT $ { PAGE_SIZE } If $1 is NULL, it skips the second condition in the OR clause and retrieves all rows without filters, resulting in a broad fetch. When $1 has a value, it filters the results using that specific value, enabling a more targeted search. However, using a generic query can sometimes lead to a less-than-ideal execution plan that's not perfectly tailored for each specific situation. I gave it a try: drop table if exists orders ; create table orders ( order_id text primary key , "timestamp" timestamptz not null ); create index idx_orders_timestamp on orders ( "timestamp" ); insert into orders select 'ORD-' || g , '2025-01-01' :: timestamptz + g * interval '1 minute' from generate_series ( 1 , 5000000 ) as g ; analyze orders ; prepare getorders ( timestamptz , int ) as select * from orders where $ 1 :

I've always been amazed by how Oracle Database handles updates to a unique column—performing set-based operations that don't violate the unique constraint, yet when executed row by row, it temporarily permits duplicates. SQL > create table franck ( val int unique ); Table created . SQL > insert into franck values ( - 1 ) , ( 1 ) ; 2 rows created . SQL > select val from franck ; VAL ---------- - 1 1 SQL > update franck set val =- val ; 2 rows updated . SQL > select val from franck ; VAL ---------- 1 - 1 From a SQL perspective, this is expected behavior, but not all databases support it without raising an error: Db2 , SQL Server , and Oracle handle it without error. PostgreSQL raises ERROR: duplicate key value violates unique constraint "franck_val_key", DETAIL: Key (val)=(1) already exists. This works with a deferred constraint. MySQL or MariaDB raise Duplicate entry '1' for key 'franck.val' SQLite raises { "code": "SQLITE_CONSTRAINT_UNIQUE" } MongoDB raises E11000 duplicate key error collection: test.franck index: val_1 dup key: { val: 1 } db . franck . createIndex ({ val : 1 }, { unique : true }); db . franck . insertMany ([ { val : - 1 }, { val : 1 } ]); db . franck . updateMany ({},[ { $set : { val : { $multiply :[ " $val " , - 1 ]} } } ]); MongoServerError : Plan executor error during update :: caused by :: E11000 duplicate key error collection : test . franck index : val_1 dup key : { val : 1 } This is surprising because Oracle unique indexes store the indexed columns as the B-tree key and the ROWID as the associated data. Non-unique indexes add the ROWID to the physical key and are required for a deferrable unique constraint to allow temporary duplication before the end of the transaction. So how do non-deferrable unique indexes allow duplication during a single update statement? In this simple example, I would expect: The initial index entries are: (-1): row #1 and (1): row #2 Updating the first row deletes the first entry (-1): row #1 and adds one with (1):

The moment that started it A colleague of mine was cleaning up some old records in a staging environment. Same query he'd run a dozen times before, except this time he was connected to production. He hit F5. No WHERE clause. 47,000 rows gone in milliseconds . We recovered from a backup, lost about two hours, and nobody got fired. But it stuck with me:** SSMS will let you delete an entire production table with the same amount of friction as running a SELECT 1**. No pause, no confirmation, nothing. The tool that DBAs and backend developers spend all day in has zero built-in protection against the single most common way people destroy data. So I built one. What SQL Guard does SQL Guard is a free SSMS extension (18 through 22) that inspects your query the moment you press F5, and pauses execution if it matches a known destructive pattern: DELETE without WHERE UPDATE without WHERE TRUNCATE TABLE DROP TABLE / DROP DATABASE / DROP PROCEDURE ALTER DATABASE Dangerous EXEC calls MERGE without a filter When one of these fires, you get a dialog showing exactly what object would be affected, with three options: cancel, run anyway, or run and ignore for the rest of the session. The point isn't to block you — it's to make the action conscious. Most accidental damage happens because muscle memory took over, not because someone genuinely meant to wipe a table. How the detection works Nothing exotic here, and honestly that's by design. SQL Guard runs a lightweight pattern-matching pass over the query text before it hits the connection. No parsing tree, no round-trip to the server, no measurable latency — it runs in under a millisecond, so you never notice it on normal queries. The core idea is simple: certain statements are only safe when scoped by a WHERE clause, and the extension checks for that clause's absence rather than trying to understand the full semantics of the query. That keeps false positives low and means it works the same way whether you're on SQL Server 2016 or the la