标签：#Database

GitHub malware advisories no longer stop at npm. Here's how we wired OpenSSF's malicious-packages data into the Advisory Database, and why we built the pipeline paranoid. The post How we took malware advisories beyond npm appeared first on The GitHub Blog .

If your files already live in Amazon S3, the pitch for storage that branches with your database is appealing but the word "migration" makes it sound like a project. It mostly is not. Neon's object storage speaks the S3 API, so the code you already wrote, the AWS SDK calls and presigned URLs, keeps working. What changes is how you point the client and where the bucket comes from, and that is a small, mechanical diff. The actual data move is a copy loop you can run once. The one thing to do up front is confirm the object operations your app actually relies on: the demo here exercises PutObject , GetObject , listing, and presigned URLs, and I flag the S3 features you should check for yourself further down. This post is the practical version: what stays identical, the exact config that changes, a script to copy the objects across, and an honest list of the S3 features that do not have an equivalent so you know what to check before you commit. The repo with the working client is at the end. TL;DR Neon object storage is S3-compatible. Your @aws-sdk/client-s3 code for the common operations, PutObject , GetObject , getSignedUrl , listing, works unchanged (these are what the demo verifies). Confirm anything beyond that, like multipart for large objects, against the current preview. The diff is the client config: point endpoint at the Neon storage endpoint, pin region: 'us-east-2' , set forcePathStyle: true . The bucket is declared in neon.ts instead of created in the console, and credentials are injected per branch. Move the data with a list-and-copy loop between two S3 clients (source AWS, destination Neon). What does not carry over: S3 bucket policies, event notifications and Lambda triggers, storage classes and Glacier transitions, and cross-region replication. Object CRUD and presigning do. The payoff is everything else in this series: once the files are on Neon, they branch with your database. Prerequisites An existing S3 bucket and credentials that can read it A Neon p

If your app stores files and you want real preview environments, you eventually hit the same wall: each preview needs its own storage, so you start provisioning a bucket per environment. That sounds cheap until you write it down. For every ephemeral environment you create a bucket, attach a policy, mint an IAM role or access keys, set CORS, add a lifecycle rule so it eventually cleans up, wire the credentials into the preview's config, and register a teardown step for when the PR closes. Then you find the orphaned buckets the teardown missed, months later, still billing. The reason this is painful is that the bucket is a separate resource from the database, so it needs its own lifecycle. Neon collapses that: the bucket is declared as part of the branch, so it is created and destroyed with the branch and needs no per-environment provisioning at all. This post compares the two approaches and shows the branch version working with no bucket-management code in sight. The repo is at the end. TL;DR Isolated storage per preview usually means provisioning a bucket per environment: policy, IAM, CORS, lifecycle, credential wiring, teardown. It is slow, it drifts, and it leaves orphaned buckets that keep costing money. On Neon the bucket is declared once in neon.ts . Creating a branch brings the bucket (with a copy-on-write copy of the files) and injects scoped credentials; deleting the branch removes it. There is no per-environment bucket to create, no IAM role to mint, and nothing to orphan. Copy-on-write means fifty preview buckets do not cost fifty times the storage, only what each one changes. Prerequisites A Neon project on the platform preview (object storage, us-east-2 ) The Neon CLI, and a CI system that opens/closes preview environments Familiarity with S3 buckets and IAM if you have done the manual version The per-environment bucket, written out Here is what "just give the preview its own bucket" actually expands to, per environment: Create a bucket with a unique nam

The naive way to accept file uploads is to POST them to your API, let the server read the bytes, and write them to object storage. It works until the files get large or the traffic gets real. Now every upload crosses your infrastructure twice, once from the client to your server and once from your server to storage, and your server holds the whole file in memory or on disk while it does. On a serverless function it is worse, because functions have request-size and duration limits that a big upload runs straight into. Presigned URLs are the standard fix, and they predate serverless by a decade. Your server does not move the bytes; it hands the client a short-lived, pre-authorized URL and the client uploads directly to object storage. The server only issues permission and records metadata. On a Neon Function this is the same AWS S3 SDK you already use, pointed at the branch's storage endpoint. This post builds it and tests the whole round trip. The repo is at the end. TL;DR Proxying uploads through a function sends the bytes across it, burning bandwidth and memory and hitting request-size limits. A presigned URL is a time-limited, pre-authorized link to one object key. The client PUTs the bytes straight to storage; the function never touches them. On Neon Functions you generate it with getSignedUrl from @aws-sdk/s3-request-presigner , the same code as any S3-compatible store. I tested the full flow: presign, the client PUT straight to storage returned 200 , a metadata record was saved, and downloading the object returned the exact bytes. One gotcha to pin: the injected AWS_REGION is the storage-cell host, not a region, so set region: 'us-east-2' on the client. Prerequisites A Neon project on the platform preview with a declared bucket (object storage, us-east-2 ) The AWS SDK: @aws-sdk/client-s3 and @aws-sdk/s3-request-presigner Familiarity with S3-style object storage and HTTP PUT Why not just proxy the upload Sending the file through the function has three costs that

Wiz Research disclosed CosmosEscape, a chain that escaped Azure Cosmos DB's Gremlin sandbox and reached a platform-wide key granting read and write access to every database on the service. Microsoft blocked the entry point within two days but took until July 2026 to remove the key. Practitioners debated shared responsibility and what that rearchitecture actually cost. By Steef-Jan Wiggers

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

Exactly a year ago we released SurrealMCP, a server you had to install and run in order to connect...

Step-by-step Xero API integration: OAuth 2.0, tenant routing, paging, rate limits, the 2026 scope and pricing changes, plus a no-code path to PostgreSQL. By Ilshaad Kheerdali · 4 August 2026 The Xero API is well documented and pleasant to work with once it clicks, but the first integration always takes longer than people expect. There is an extra discovery step that most accounting APIs don't have, tokens expire faster than you'd guess, and 2026 brought two changes that alter how you scope and budget an integration. This guide walks the whole flow: creating an app, running OAuth 2.0, resolving which organisation you're actually talking to, making your first call, paging through results, staying inside the rate limits, and pulling incremental updates. At the end it covers what changed in 2026 and the shortcut if the plumbing isn't the part you want to own. Everything below targets the Xero Accounting API over OAuth 2.0. Xero retired OAuth 1.0a some years ago, so any tutorial you find that mentions consumer keys and signed requests is out of date. What the Xero API Is The Xero Accounting API is a REST API that returns XML by default and JSON if you ask for it. You read and write accounting entities: Invoices , Contacts , Payments , BankTransactions , Accounts , CreditNotes , Items , PurchaseOrders , ManualJournals and a few dozen more, plus a set of report endpoints. The thing that surprises most developers coming from Stripe or QuickBooks is the tenant model . A single Xero login can have access to many organisations: an accountant might be connected to two hundred client orgs. So authorisation and targeting are two separate concerns. Your token proves the user said yes, and a separate header tells Xero which organisation the call is for. That means every integration has a step that a Stripe integration simply doesn't: after you get a token, you have to ask Xero which tenants that token can reach. Step 1: Create a Xero App Sign in at the Xero Developer portal and cre

A generated image looks finished until review starts. Someone approves the first version. Someone else crops it. A branded copy goes out. Another edit changes the prompt. A week later, the useful question is simple: which prompt, model, seed, size, parent image, and publishing settings produced the version on screen? In a content studio, I put those answers in the PostgreSQL row that stores the image. Logs explain what happened during a run, then rotate away. Object storage keeps the bytes and forgets why they exist. The row is the only one of the three that survives edits, review, and publishing. 1. The row is the receipt The table in apps/api/src/database/init-ai-images-table.js treats generated and edited images as one record type. An original image gets its own row. An edit gets another row, with original_image_id pointing back to the parent. CREATE TABLE IF NOT EXISTS ai_generated_images ( id SERIAL PRIMARY KEY , image_url TEXT NOT NULL , -- what produced it prompt TEXT NOT NULL , model VARCHAR ( 100 ) DEFAULT 'fal-ai/imagen4' , model_version VARCHAR ( 100 ), seed BIGINT , width INTEGER , height INTEGER , -- how it derives from another row is_edited BOOLEAN DEFAULT FALSE , original_image_id INTEGER REFERENCES ai_generated_images ( id ), edit_prompt TEXT , edit_strength DECIMAL ( 3 , 2 ), -- what actually shipped branded_url TEXT , branding_options JSONB , metadata JSONB , tags TEXT [], created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); That self-reference is the design choice. It makes the image table append-only-ish: new variants are inserted as new rows instead of overwriting the earlier state. The cost is more rows and more discipline at write time. The benefit is editable history that product screens and debugging queries can follow. flowchart TD original["Original row: prompt, model, seed, dimensions"] editA["Edited child: edit_prompt, edit_strength, edit_steps"] editB["Edited child: edit_prompt, edit_guidance_scale"] brandedA["Branded output: branded_url,

100 城时区页给跨区调度当速查，DST 自动算 作者是 数据管道 / 跨时区调度 方向的开发者。这篇不是广告，是踩坑记录 + 顺手做的工具。 背景 做 数据管道 / 跨时区调度 时，时间戳转换是最常被低估的雷区。16 个时间戳工具(Unix 转换/时区/ISO8601/Cron/Duration…) 已覆盖日常；但每个语言/框架的坑都不一样，所以又补了 30 个语言/框架时间戳页(python/javascript/java/sql/…)，每页含 6 个真实坑。 我踩过的坑（举几个） 秒 vs 毫秒：前端 Date.now() 是毫秒，后端常存秒，混用差 1000 倍。 时区不是字符串：存 UTC、展示本地，别把本地时间当 UTC 落库。 2038 问题：32 位系统 time_t 在 2038-01-19 溢出，老系统要提前查。 夏令时：一年有两次重复/缺失的本地时间，跨区调度尤其坑。 我顺手做的东西 转换速查页： https://gotimestamp.com/timezone/new-york 相关语言页： https://gotimestamp.com/timezone/london 开源 MCP： https://github.com/caresotin/tsforge-mcp —— 把时间戳转换/校验直接接进 LLM 工作流，不用手算。 小结 时间戳没那么简单，但工具到位就省心。上面都是免费、开源、可直接用的，希望对同样踩坑的人有帮助。

Half the market is arguing about whether RAG or a semantic layer is the right foundation for enterprise AI. They are not competing. They answer different questions, and most teams need both. Two shapes of question Every question an agent receives breaks into one of two forms: "What did we say about X?" — lives in contracts, policies, tickets, docs. Unstructured. RAG was built for this. "What is true about X?" — lives in your warehouse and governed metrics. Structured. A semantic layer was built for this. Treating them as rivals is how teams end up with a system that can quote the pricing policy but cannot tell you this quarter's realised price. Where each one breaks RAG Semantic layer Good at Retrieving relevant prose Resolving definitions and joins Fails on Aggregation, math, current state Anything not modelled as data Permissions Flattened at ingest, rebuilt at query time Compiled per person, per query Answer stability Varies with retrieval ranking Identical by construction Audit story Cites a chunk Reproduces the exact SQL The permissions row is the one that ends pilots. A retrieval index that ingested everything has, by construction, assembled your most sensitive object — and reconstructing entitlement at query time is guesswork. The layer that actually decides Neither a document chunk nor a metric definition is worth much until something compiles it into a governed query and runs it. That is the piece most architectures are missing: intent → context resolution → constrained planning → governed execution . RAG can feed the first step. It cannot perform the last three. Point an agent at raw tables and the best models score in the low teens on real enterprise data. Give the same model compiled, governed context and it clears the high nineties. The retrieval quality was never the bottleneck. The full breakdown — the precise division of labour, why hybrid architectures win, and how compile-time governance closes the gap RAG cannot — is here: 👉 RAG vs. Semantic Layer

Vector databases have become the backbone of AI applications, powering semantic search, RAG systems, recommendation engines, and multi-modal AI. With the market maturing rapidly in 2026, choosing the right vector database impacts everything from query latency to operational costs. We ranked the 8 best options based on performance, scalability, ease of use, and enterprise readiness. TL;DR: Ranked 8 best vector databases: Pinecone leads for managed simplicity, Weaviate for flexibility, Milvus for open-source scale, and pgvector for PostgreSQL-native teams. Selection depends on your scale, latency requirements, and existing infrastructure. Key Evaluation Criteria for Vector Databases Modern vector databases must excel across multiple dimensions. We evaluated each option on: query performance (latency at various scales, index types supported), scalability (horizontal scaling, multi-tenancy), data type support (dense vectors, sparse vectors, multi-modal embeddings), integration ecosystem (SDKs, LangChain/LlamaIndex support), and operational maturity (hosting options, backup, monitoring). Query performance: P95 latency at 1M, 10M, and 100M vector scales Scalability: Horizontal scaling, sharding, multi-tenancy Data type support: Dense vectors, sparse vectors, binary vectors, multi-modal Integration: SDK availability, LLM framework support, MCP compatibility Operations: Self-hosted vs. managed, backup, monitoring, compliance Ranking: The 8 Best Vector Databases for AI Applications ### 1. Pinecone Pinecone remains the most popular fully-managed vector database, known for its simplicity and reliability. The 2026 release adds sparse-dense hybrid search, serverless tier with sub-millisecond P99 latency, and namespace-based multi-tenancy. Its serverless pricing model makes it cost-effective for variable workloads. * **Best for:** Teams wanting fully managed vector search without operational overhead * **Pros:** Zero operations, excellent performance, simple API, strong ecosystem

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

User segmentation requirement Imagine you need to send a push notification to users who satisfy all of the following conditions: Push notification is enabled User is a VIP Active within the last 30 days Following the Voucher Hot category The traditional approach is to query multiple tables: SELECT DISTINCT u . id FROM users u JOIN user_configs c ON c . user_id = u . id JOIN devices d ON d . user_id = u . id JOIN follows f ON f . user_id = u . id WHERE c . push_optin = 1 AND c . mute = 0 AND d . fcm_token IS NOT NULL AND f . category = 'voucher_hot' AND u . last_active >= NOW () - INTERVAL 30 DAY ; As your user base grows into the millions, every campaign requires joining multiple large tables, filtering millions of records, and repeatedly computing the same audience. Query latency increases significantly, making real-time segmentation increasingly difficult. A Different Approach Instead of querying the database every time, we precompute each boolean attribute as a bitmap. Think of a bitmap as a huge array containing only 0 and 1, where the index corresponds to the user ID. For example, a bitmap representing whether a user has enabled push notifications: User ID : 0 1 2 3 4 5 6 7 Bitmap : 1 0 1 1 0 0 1 1 To check whether user 123 has enabled notifications, simply read bit 123. 1 → enabled 0 → disabled Each bitmap represents exactly one boolean property: bitmap:push_optin bitmap:vip bitmap:active30 bitmap:follow:voucher_hot Memory Usage Bitmap is extremely memory efficient. Each user requires only one bit. For 1 million users: 1.000.000 bits ~ 125.000 bytes ~ 122 KB That means every segment only consumes about 122 KB of Redis memory. Even 100 different segments require only around 12 MB . Finding Intersections Suppose you want all users that are: VIP AND Push Opt-in AND Active30 AND Following Voucher Hot Redis can calculate the result with a single command: BITOP AND result vip push_optin active30 follow_voucher_hot Need the number of matched users? BITCOUNT result No

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

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

Olá, comunidade DEV! Meu nome é Beth Rodrigues e este é o meu primeiro post por aqui. Durante muito tempo, minha relação com tecnologia esteve ligada ao uso das ferramentas no dia a dia de trabalho. Planilhas, sistemas, relatórios e processos faziam parte da minha rotina, mas em algum momento comecei a enxergar algo diferente: por trás dos dados existiam histórias, oportunidades de melhoria e decisões que poderiam ser mais inteligentes. Foi assim que comecei minha transição para a área de Dados. Minha jornada está sendo construída passo a passo: estudando SQL, Excel, Power BI, Python e conceitos de análise de dados. Também tenho explorado ferramentas de inteligência artificial, automações e formas de transformar problemas reais em soluções. Uma das coisas que mais me motivam é perceber que dados não são apenas números em uma tabela. Eles podem ajudar uma empresa a entender seus clientes, melhorar processos e encontrar caminhos mais eficientes. Atualmente estou criando pequenos projetos para praticar, aprender e construir meu portfólio. Quero compartilhar por aqui meus aprendizados, erros, descobertas e experimentos nessa caminhada. Acredito muito que aprender tecnologia é como montar um grande quebra-cabeça: no começo algumas peças parecem não fazer sentido, mas aos poucos a imagem começa a aparecer. Espero trocar experiências com pessoas da comunidade, aprender com quem já está nessa estrada e também contribuir compartilhando minha evolução. Que venha essa nova fase! 🚀

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