标签：#sql

By the end of this page you can answer the question that stops most people the first week they write SQL: which row is the best one in each category. You will know OVER and PARTITION BY , the three ranking functions and how each treats a tie, a running total, and LAG for comparing a row to the one before it. It is about twenty-five minutes. Here is what to actually do with it. The next time you write GROUP BY genre and get back a best rating without the name attached to it, stop rewriting the GROUP BY . Add ROW_NUMBER() OVER (PARTITION BY genre ORDER BY rating DESC) to the plain query instead, then keep the rows numbered 1. That is the whole move, and it replaces a query most people never get working. The short version: a window function adds a calculated column to each row while leaving every row in place. Grouping collapses rows. A window looks at them. One idea decides everything else on this page, so it gets the picture. Both halves do the same arithmetic over the same four rows, and only one of them still has four rows at the end. The original carries a diagram here. In words: Two panels side by side, each starting from the same stack of four identical row shapes. The left panel is labelled GROUP BY. Its four rows funnel down through a single arrow into one row at the bottom, and the four original rows are shown faded to indicate they are gone from the result. Only one row remains. The right panel is labelled OVER. Its four rows stay exactly where they are, at full strength, and each one gains a small badge on its right hand side holding a number: one, two, three, four. Nothing funnels and nothing is faded. The contrast is the whole idea: the left panel ends with a single summary row and no way to say which original row it came from, while the right panel ends with all four rows still present, each carrying its own calculated value. The worked example is real. Every number on this page comes from a published portfolio project: finding the genuinely overlooked g

By the end of this page you can write a summary query and know its answer is real. You will know exactly what GROUP BY does to your rows, which columns you are allowed to select afterwards and why, where WHERE goes, where HAVING goes, and why swapping them is the difference between a finding and a number that means nothing. It is about twenty-five minutes. Here is what to actually do with it. On the next summary query you write, add one line setting a minimum group size before you read the ranking. One line, and it removes the most common way a summary query produces a confident wrong answer. The short version: WHERE filters rows before grouping. HAVING filters groups after. Without a HAVING floor, tiny groups float to the top of every ranking. One idea decides everything else here, so it gets the picture. Grouping happens in the middle of the query, and the two filters sit on opposite sides of it. The original carries a diagram here. In words: A left-to-right pipeline in four stages. Stage one is a column of eight individual row boxes. Stage two is a gate labelled WHERE, through which six rows pass and two are crossed out and stopped. Stage three shows the surviving six rows collapsing into three group boxes, one holding three rows, one holding two rows, and one holding a single row. Stage four is a second gate labelled HAVING, through which the group of three and the group of two pass, while the group holding only one row is crossed out and stopped. The result at the far right is two groups. The picture shows that WHERE acts on individual rows before any grouping exists, and HAVING acts on whole groups after they have been formed, which is why the two filters cannot be swapped. The worked example is real. Every number on this page comes from a published portfolio project: 82,956 games from the Steam catalogue, with review counts, ratings and genres. The queries run against the full dataset at Steam Hidden Gems on GitHub . If SELECT and WHERE are also new, start wi

By the end of this page you can say, out loud and in your own words, what every core piece of SQL does. What a table and a row really are. The six clauses, and the order they actually run in, which is not the order you type them. NULL , and why it breaks comparisons. Filtering, aggregation, GROUP BY and HAVING . Joins. CASE . Subqueries, CTEs and window functions. Keys and indexes. That list is most of what an analyst job, an interview, and a first real dataset will ask of you. Here is what to actually do with it. Go through once end to end without stopping, just for the shape. Then come back to the retrieval sheet near the bottom, cover the right-hand column, and try to say each answer before you read it. That second pass is where the learning happens, and there is measured evidence for it further down. The short version: SQL is one sentence with six parts, and every part answers a different question about your rows. Learn what each part does and where it runs, and the rest is vocabulary. One idea decides more of your SQL experience than any other, so it gets the picture. You write a query in one order. The database runs it in a different order. Almost every confusing SQL error is that gap. The original carries a diagram here. In words: Two columns of stacked boxes face each other. The left column, headed "you write", lists the clauses in typing order from top to bottom: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. The right column, headed "it runs", lists the same clauses in execution order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. Curved lines connect each clause on the left to the same clause on the right. Six of the seven lines run roughly straight across. One line, the one belonging to SELECT, is drawn in a strong accent color and sweeps steeply downward from the very top of the left column to the fifth position on the right, showing that SELECT is written first but runs almost last, after grouping has already happened. What this page

Search has got more powerful, but also more complicated It used to be that database queries were simple enough. Either you had an index, or you didn't, and either way you had some kind of optimizer (cost or rule) that turned your SQL statement into a viable plan for finding and returning your data. We now live in a world where in addition to the Boolean logic of traditional RDBMS queries, we also have: Geospatial queries Full text search queries, using BM25 . Vector Search And if that weren't enough, we have to consider that instead of a traditional application issuing the query, it might be an MCP server, and most importantly of all, the business need might be for two or more of these searches to happen at the same time, on the same data. For example: "An MCP server that uses a single, combined geospatial + full text query to identify towns in Bavaria with castles mentioned in text descriptions." How does CrateDB help with this? CrateDB is one of the limited number of products that not only supports all of these search types but is also capable of storing arbitrarily large quantities of data. This is important, as if you have to hit multiple different database servers to solve your business question, not only is your environment much more complicated, but you risk getting incorrect answers as your multiple databases may be out of sync. In this two-part example, based on our playable IOT Analytics scenario, we will show two things: Using Geo + Text to search a weather/tourism database Using CrateDB as a '360 view' for your MCP server If you want to follow along with this post, the setup is based on our IoT Analytics scenario. You can also just skip this blog post and jump straight to the scenario. Using Geo + Full text to search a weather/tourism database The table we're going to use is called ' German Regions ': CREATE TABLE IF NOT EXISTS demo . german_regions ( region_name TEXT PRIMARY KEY , geo_coords GEO_SHAPE , tourism_info TEXT INDEX USING FULLTEXT WITH ( anal

You can load-test search latency with a script and a graph. Relevance has no such gauge by default, so most teams ship a new ranking rule, eyeball a handful of queries, and hope nothing important regressed. The fix is a small, boring relevance test suite: a fixed set of queries, human-judged expected results, and a metric you compute the same way every time — so "did this ranking change help?" becomes a number you can diff, not an argument you have in Slack. This post is a build guide. By the end you'll have a judgments file, a scorer that outputs precision@k, MRR, and nDCG, and a before/after comparison you can wire into CI. The examples use Postgres full-text search, but the harness is engine-agnostic — Elasticsearch, Meilisearch, or a vector store all slot into the same shape. Why can't I just load-test relevance the way I load-test latency? Latency is a property of the system. Relevance is a property of the match between a query and what a human expected to see — and that judgment lives outside the database. A commenter on an earlier post about running Postgres search in production put it well: latency can be load-tested, but quality needs query sets, expected result buckets, bad-query examples, and a way to compare changes before shipping a new ranking rule. That's the whole job, and none of it comes for free with your index. The trap is thinking a passing query proves relevance. SELECT ... WHERE tsv @@ query returning rows tells you the index matched. It says nothing about whether the right rows landed in the top 5, which is all a user ever sees. The takeaway: relevance is measured against human judgments, not row counts — so the first artifact you build is the judgments, not the query. Building the golden query set Start with 20–50 real queries. Pull them from your search logs if you have them (the head terms plus a long tail of specific ones), or write them from real user intents if you don't. For each query, mark which documents should come back and how rel

Canva redesigned session revocation infrastructure to support 100M active sessions while reducing database lookups. The architecture uses Amazon S3 for durable revocation records and distributes compact, in-memory indexes to application gateways. Canva said the design improved deployment speed, reduced database infrastructure requirements, and cut the revocation cache memory footprint by 87.5%. By Leela Kumili

COUNT looks like the simplest function in SQL, and it is the one that quietly trips up the most people in interviews and on the job. The confusion is almost always the same: COUNT(*) , COUNT(column) , and COUNT(DISTINCT column) look nearly identical but count three different things. Once you can say out loud what each one counts, a lot opens up. You can verify a data migration, find duplicates, and measure how complete a column is, all with the same little function. This guide is that explanation, with lots of small examples you can copy. The one-sentence version. COUNT(*) counts rows . COUNT(column) counts rows where that column is not NULL . COUNT(DISTINCT column) counts how many different non-NULL values that column has. Everything below is just that sentence, slowed down. The three forms of COUNT and what each one counts Picture one small table, customers , with a region column where two rows were never filled in: id name region 1 Maya North 2 Jordan South 3 Alex North 4 Sam NULL 5 Taylor NULL Now run the three forms on it: SELECT COUNT(*) AS all_rows, COUNT(region) AS rows_with_region, COUNT(DISTINCT region) AS different_regions FROM customers; all_rows rows_with_region different_regions 5 3 2 COUNT(*) = 5. Every row, no exceptions. The * means "the row itself," so NULLs never matter. COUNT(region) = 3. Only the rows where region has a value. Sam and Taylor are skipped because their region is NULL. COUNT(DISTINCT region) = 2. The different values are just North and South . The two Norths collapse to one, and NULL is not counted. The NULL rule that makes them disagree Predict it first. A table has 100 rows. Twenty of them have no email address. What does counting the email column give you? Say the number before you read on. Here is the whole trick in one line: COUNT(*) counts rows. COUNT(something) counts non-NULL values of that something. So the moment a column has any NULLs, COUNT(column) comes back smaller than COUNT(*) . That gap is not a bug, it is informat

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