标签：#database

There's a moment every developer hits the first time they connect an AI assistant to a real database: it works beautifully, the model writes a clean SELECT , you get your answer in seconds — and then a small, cold thought arrives. What if it had written DELETE instead? That worry is healthy. An AI agent that can query your production database is also, by default, an AI agent that can UPDATE , DROP , and TRUNCATE it. Large language models are probabilistic. They hallucinate. They misread a vague prompt like "clean up the test users" as an instruction to actually delete rows. You don't want the only thing standing between a confused model and your orders table to be good intentions. The fix isn't to keep AI away from your data. It's to make write operations structurally impossible — read-only by design, enforced at layers the model can't talk its way past. This post walks through how to do that properly, from the database grant all the way up to query-level guardrails. Why "just prompt it to be careful" fails The tempting shortcut is to add "only run SELECT queries, never modify data" to your system prompt and call it a day. Don't rely on this. Prompt instructions are suggestions, not enforcement. A cleverly worded user request, an injected instruction hidden in some data the model reads, or a plain misunderstanding can all lead the model to generate a destructive statement anyway. Real read-only access is enforced below the model — in places where no amount of clever text can override it. Think of it as defense in depth, with at least three independent layers: Layer What it stops Enforced by Database permissions Any write reaching the engine SQL GRANT / REVOKE Connection / replica Writes even being routed to a writable node Read replica, read-only transaction Query parser / broker Non-SELECT statements before they run SQL parsing, allowlists Any one of these is decent. All three together mean a write has to defeat your database engine, your routing, and your parser s

The database platform Harper advocates for a single-runtime architecture that keeps application code and data together, with its benchmark against a Vercel-based stack reporting significantly better performance on live, personalized-data workloads. Harper recently released version 5.2, with a new record cache and more throughput per node. By Renato Losio

Last week I turned my data audit into a build step : a check that runs before anything else and fails the build when the database and any static copy of my travel site's legal-status data disagree. It ended the era of the site contradicting itself. It did nothing about the site agreeing with itself on something false. That's not a hypothetical. The most expensive error the whole project found was a country whose law changed in January while every copy on my site — database, data files, search index — kept saying the old thing in perfect unison. Internal consistency was the camouflage . No diff between my own sources could ever have caught it, because every internal source was equally behind the world. A build gate proves agreement. Agreement is not truth. Something has to look outside. You can't diff against the world, but you can sample it The naive version of "look outside" is another audit — a human session checking primary sources jurisdiction by jurisdiction. I've done three of those now, and I know exactly what they're worth: they're correct the day they ship and they decay from that morning on. Laws don't change on my audit schedule. So the outside check became what the inside check became: a scheduled job. Once a week, a script asks a web-connected model — one that searches and cites, not one answering from training memory — for the current legal status of about fourteen jurisdictions, and compares each answer to the corresponding database row. Fourteen, not all 271, because the selection is doing the real work: A hot list is checked every single run: the highest-traffic pages plus the jurisdictions with active legislative motion — the places where being a month stale costs the most. Everything else sits on a rotating cursor : eight per run, round-robin, so every row on the site gets sampled roughly twice a year without any run costing more than a few cents. The whole thing runs on about seven cents a week. Two rules were non-negotiable, both inherited from

@Service public class SimpleUserService { @Autowired private JooqTemplate jt ; public List < user > selectUserInDept ( UserParam param ) { //If deptIDs==null or deptIDs. isEmpty automatically ignores this query condition // SELECT * FROM user_table WHERE name LIKE '%?%' AND dept_id IN (?,?...); return jt . queryv ( "user_table" , User . class , "name%" , param . getName (), "dept_id:in" , param . getDeptIds ()); } public List < user > selectUserNotInDept ( UserParam param ) { // SELECT * FROM user_table WHERE name LIKE '%?%' AND dept_id NOT IN (?,?...); return jt . queryv ( "user_table" , User . class , "name%" , param . getName (), "dept_id:notin" , param . getDeptIds ()); } }

ICE collected nearly a million DNA samples last year.

In my last post I wrote about an error that cost me a full evening: my pom.xml had the MongoDB starter, but my code was still full of JPA annotations. The compiler kept saying cannot find symbol: class Entity . That post was about the error. This post is about the fix — every single line I had to change to move my Task Manager project from MySQL to MongoDB. If you are planning the same switch, this is the checklist I wish I had. 1. The dependency Before (MySQL + JPA): <dependency> <groupId> org.springframework.boot </groupId> <artifactId> spring-boot-starter-data-jpa </artifactId> </dependency> <dependency> <groupId> com.mysql </groupId> <artifactId> mysql-connector-j </artifactId> <scope> runtime </scope> </dependency> After (MongoDB): <dependency> <groupId> org.springframework.boot </groupId> <artifactId> spring-boot-starter-data-mongodb </artifactId> </dependency> One starter replaces two dependencies. And this is exactly where my problem started — I added the new one but never removed the old one, so half my code still compiled and half did not. Remove the JPA starter completely. If you leave it in, the jakarta.persistence annotations still resolve, and you will not notice you are mixing two worlds until something breaks at runtime. 2. application.properties Before: spring.datasource.url = jdbc:mysql://localhost:3306/taskmanager spring.datasource.username = root spring.datasource.password = yourpassword spring.jpa.hibernate.ddl-auto = update spring.jpa.show-sql = true After: spring.data.mongodb.uri = mongodb://localhost:27017/taskmanager Five lines became one. No ddl-auto because MongoDB has no schema to create. No dialect because there is no SQL being generated. The database and the collection are created automatically the first time you insert a document. 3. The model class This is where most of the work was. Here is my actual Task class after the migration: package com.taskmanager.task_manager ; import com.fasterxml.jackson.annotation.JsonIgnore ; import org.

If you build with Prisma, you already know Prisma Studio. Run one command and you get a clean, visual way to browse and edit rows in your database. It's genuinely useful, and I reach for it every day while developing. But somewhere between "I need to look at my data" and "I need to let a support agent safely edit a customer's record in production," Prisma Studio quietly stops being the right tool. It was never trying to be that tool. It's a database viewer. An admin panel is something else, and the gap between the two is exactly the part that matters once real people and real permissions are involved. I ended up building a small package to fill that gap for my own Express + Prisma apps. Writing it forced me to be precise about what an admin panel actually adds on top of a database browser. Here's the distinction as I now understand it. A database browser shows rows. An admin panel governs them. Prisma Studio connects to your database and shows you everything. That's the point of it, and it's also why you'd never hand it to a non-engineer or expose it in production. It has no concept of who is looking, what they're allowed to do, or which rows they're allowed to touch. An admin panel's whole job is those three questions. The package I built mounts a React UI at /admin and a guarded JSON API under /admin/api/* on your existing Express app. Every single request through that API runs the same pipeline, in the same order: authentication → permission check → tenant scope → validation → Prisma mutation/query → optional audit event That ordering is the entire difference. A database browser skips straight to the mutation. An admin panel refuses to run the mutation until it knows the request is authenticated, permitted, scoped to the right tenant, and valid. Permissions and scope are two different questions This was the design decision I care most about, because collapsing these two into one is how data leaks happen. Permissions decide which actions a role may take. Can an ed

I got an AWS question and implemented it to make sure that the option is correct. A critical financial application runs on RDS for PostgreSQL. The requirements are tight: 1-second RPO, 60-second RTO, and database credentials rotated every 30 days without taking the application offline. Two independent problems. Two independent solutions. Prerequisites Check these before running terraform apply : RDS Proxy availability RDS Proxy is not available on all instance types. It requires instances with at least 2 vCPUs. db.t3.micro is not supported. db.t3.medium and above work. Terraform executor permissions The IAM principal running Terraform needs, at minimum: rds:CreateDBInstance rds:CreateDBProxy rds:CreateDBProxyTargetGroup rds:RegisterDBProxyTargets rds:ModifyDBInstance iam:CreateRole iam:AttachRolePolicy iam:PutRolePolicy iam:PassRole secretsmanager:CreateSecret secretsmanager:PutSecretValue secretsmanager:RotateSecret lambda:CreateFunction lambda:AddPermission ec2:CreateSecurityGroup ec2:AuthorizeSecurityGroupIngress ec2:CreateDBSubnetGroup AdministratorAccess on the account covers all of these. Lock it down after the initial setup. VPC requirements RDS Proxy runs inside your VPC. You need at least two private subnets in different Availability Zones. The rotation Lambda also runs inside the VPC so it can reach the RDS instance directly during the credential update step. The problem Database failure recovery RPO of 1 second means almost no data loss is acceptable. RTO of 60 seconds means the application must resume within a minute of a failure. A standard single-instance RDS setup fails both requirements: there is no automatic failover, and restoring from a backup takes far longer than 60 seconds. Credential rotation Rotating credentials on a schedule sounds simple until you factor in application downtime. If you update a password and the application still holds connections authenticated with the old one, those connections fail. The rotation mechanism needs to handle

In this article, we'll explore the mechanisms to solve the coordination problem. 8. Introducing Leases To address the problem of permanent ownership, distributed systems typically replace it with temporary ownership. This concept is known as a lease . Instead of granting indefinite control over a resource, the coordination service assigns ownership for a limited period of time. Rather than stating, “You own this resource until you explicitly release it,” the system instead says, “You own this resource for the next 30 seconds.” This changes the interaction model significantly. Acquire Lease | v Execute Work | v Renew Lease | v Continue Processing As long as the application remains healthy, it periodically renews the lease to maintain ownership. If the application crashes or becomes unresponsive, it can no longer renew the lease. Once the lease duration expires, ownership is automatically revoked. At that point, another application becomes eligible to acquire the lease and continue the work. Leases solve a critical problem in distributed systems: they prevent abandoned locks from blocking progress indefinitely . The system can recover automatically without manual intervention. However, while leases improve availability, they also introduce a new class of subtle and more complex problems. Leases Depend on Time To understand the next challenge, assume the lease duration is thirty seconds. Application A successfully acquires the lease. Lease Granted Duration = 30 seconds After twenty seconds, the JVM begins a long Full Garbage Collection cycle. This pause lasts forty seconds, significantly longer than the lease duration. The timeline now becomes problematic. Lease Granted | | Processing | | GC Pause (40 sec) | | Lease Expires While Application A is paused, the lease expires. During this time, another application requests access to the same resource. The coordination service observes that the previous lease has expired and therefore grants ownership to Application B. Appl

Introduction Your program keeps opening transactions. A signup writes a new user row. A checkout debits one account and credits another. A form submission updates three related tables at once. You wrap it all in BEGIN and COMMIT and move on, trusting that the database will handle whatever happens in between. Most of the time it does. But what is it actually promising you when it handles that? And what does it have to do behind the scenes to keep that promise? Say a user transfers ₹1,000 from Account A to Account B. The application runs two updates: subtract 1,000 from A, add 1,000 to B. Now say the server crashes right after the first update runs but before the second one does. Account A: -₹1,000 Account B: +₹0 That money didn't move. It vanished. No error message fixes that, and no user accepts "the server restarted" as an explanation for their missing balance. This is the exact problem a set of guarantees called ACID was built to solve. Most developers can recite the acronym, Atomicity, Consistency, Isolation, Durability, without being able to explain what any of the four words actually promise, or what the database has to do internally to keep those promises. This article tries to fix that. -- 1. What Is a Transaction? Before ACID makes sense, you need to understand what a transaction actually is. A transaction is a group of one or more database operations treated as a single logical unit of work. Either the whole group succeeds, or none of it does. The bank transfer above is a textbook transaction: two updates that only make sense together. In SQL, a transaction usually looks like this: BEGIN ; UPDATE accounts SET balance = balance - 1000 WHERE id = 1 ; UPDATE accounts SET balance = balance + 1000 WHERE id = 2 ; COMMIT ; BEGIN tells the database "everything from here on is one unit." COMMIT tells it "we're done, make it permanent." If something goes wrong in between, a constraint violation, a crash, the application deciding to cancel, the database can issue a RO

This is the data appendix for Posts 1–4 . The narrative and takeaways live in the main posts. This page is pure measurement. Eval set: 22 questions (P1) → expanded to 31 questions (P2 onward) Corpus evolution: P1/P2: ~3,700 docs (raw threads + code chunks) P3/P4: 16,315 docs (distilled threads + bursts + code) Quick comparison (same 31-question set) Metric Vector P2 Hybrid P2 Vector P3 Hybrid P3 Hybrid + Rerank (P4) recall@1 0.68 0.61 0.52 0.39 0.87 recall@3 0.84 0.65 0.71 0.65 0.94 recall@10 0.90 0.90 0.81 0.94 0.94 MRR 0.77 0.67 0.63 0.57 0.90 Takeaway: Hybrid alone never beat pure vector on this corpus. Hybrid + LLM rerank is the first clear win. P1 — Naive vector baseline Corpus: 3,000 raw issue threads + 687 code chunks Embeddings: BGE-M3 (1024d), max_seq_length=1024, HNSW cosine Numbers (22 questions) Metric Score recall@10 1.00 (22/22) recall@3 0.95 recall@1 0.77 (17/22) Main k=1 misses Exact error pastes ( TypeError: Object of type int64... , AttributeError: 'Depends'... ) — ranked 4–5 instead of 1 jsonable_encoder code chunk outranked by issues about the function API key header implementation (code vs similar issues) Paraphrase questions (dependency injection outside routes, custom 404) Pattern: Dense search is strong on recall@10 but weak when the query has a sharp lexical signal. Ops notes Ingest wall time ~40 min (GitHub API is the bottleneck) BGE-M3 OOM on Apple Silicon fixed by capping max_seq_length=1024 Python 3.13 + uv editable install issue fixed by pinning 3.12 P2 — Hybrid (vector + FTS + RRF) Corpus: Same size as P1, with better comment pagination and symbol-based code IDs Eval set: Expanded to 31 questions (added exact error pastes + rare identifiers) Numbers Metric Vector FTS Hybrid recall@1 0.68 0.42 0.61 recall@3 0.84 0.48 0.65 recall@10 0.90 0.65 0.90 MRR 0.77 0.47 0.67 Headline: Hybrid is not a strict win over vector-only. Where hybrid helped Exact error pastes (e.g. TypeError: int64 is not JSON serializable ) → moved from rank 5 → 1 Near-d

You deleted the row. The secret is gone from the app, the queries return nothing, and the dashboard is clean. In SQLite — the database behind most session stores, browser profiles, and agent state files — that delete is a fiction. The bytes are still in the file. Three ways deleted data survives 1. Free pages. SQLite doesn't zero out the space a deleted row occupied. The page is marked free and added to the freelist; the old bytes stay until they're overwritten by a future write. A file that's been deleted-from is a forensics goldmine: recover the freelist pages and the "deleted" rows come back. 2. FTS virtual tables. If the database uses SQLite's full-text search (FTS5), the FTS index keeps its own copies of the indexed text, maintained separately from the source tables. Delete the row from the source table and the FTS index still contains the tokens — searchable. This is the one that catches people: their app shows the secret is gone, and the FTS index still has it. 3. WAL and journal files. In WAL mode, recent writes live in the -wal file; transactions in the -journal file. Both can retain pre-delete content until checkpointed or cleaned. "Deleted" in SQLite means "no longer referenced", not "no longer present". What erasure actually requires Making a secret physically disappear from a SQLite database takes three operations, in order: Replace the value everywhere it lives. Known secret values get replaced across all tables; pattern matches (API key formats) get masked. Two layers, because you can't enumerate every secret that leaked. Rebuild the FTS indexes. INSERT INTO t(t) VALUES('rebuild') style rebuilds, or drop/recreate the virtual tables — so the index no longer contains the old tokens. Run VACUUM. VACUUM rewrites the entire database file, copying only live data into a fresh file — free pages with old bytes are discarded in the process. After VACUUM, the file's raw bytes no longer contain the secret. (Note: VACUUM doesn't shrink WAL files; those need a chec

Nikolay Samokhvalov has developed PGSimCity, an open-source educational tool that visualises PostgreSQL mechanics as a 3D spatial simulation in the browser. It assists backend developers and site reliability engineers in understanding SQL and the dynamics of kernel execution. The project is available on GitHub and aims to enhance understanding of database architecture through interactive elements. By Olimpiu Pop

Adapted from the SQL Essentials Companion Guide . You write a query to find every customer with no phone number on file. WHERE phone = NULL looks obviously correct — and it returns zero rows, even though you can see NULL sitting right there in the column. Nothing crashes. No error. The query just quietly lies to you about what's in the table. This isn't SQL being broken. It's SQL being consistent about something most languages don't force you to think about: NULL doesn't mean "nothing," it means "unknown." And you can't compare something to unknown with = and expect a real answer. What's actually happening Take this table: -- customers | id | name | phone | | ----|-------------|------------| | 1 | Jordan Lee | 555 - 0142 | | 2 | Sam Rivera | NULL | | 3 | Alex Chen | 555 - 0198 | SELECT name FROM customers WHERE phone = NULL ; -- returns 0 rows SQL doesn't evaluate conditions as just true or false — it has a third result: unknown . phone = NULL asks "does this unknown value equal this other unknown value?" There's no way to answer that, so SQL returns UNKNOWN for every single row, including Sam Rivera's. And WHERE only keeps rows where the condition is TRUE . UNKNOWN doesn't qualify, so the row gets filtered out — the exact same as if it had evaluated to FALSE . This is true even for the row that "should" match. NULL = NULL isn't TRUE — it's also UNKNOWN . NULL never equals anything, not even another NULL . That's the whole rule, and it applies uniformly, which is why = can't be patched into working here — it's not almost right, it's answering a different question than the one you're asking. The fix, step by step Recognize the symptom : a query that runs cleanly but returns fewer rows than it should — especially zero rows when you can see matching data — with a NULL column somewhere in the WHERE clause. Swap = for IS NULL (or != for IS NOT NULL ). These are dedicated operators built specifically to test for absence, not comparison operators being asked to do somethin

There is no manual for TronSoft. No API reference, no schema diagram, no forum thread explaining why a comanda refuses to close. If you want to understand it, you open the database and start pulling threads until something makes sense. That's exactly what I did — for months, on top of my actual job. The problem nobody wrote down I'm the Operations Manager at a restaurant in Itaúna, a mid-sized town in Minas Gerais, Brazil. I'm also the only person there who writes software. Not because I was hired to — because the restaurant runs on a Brazilian ERP called TronSoft, built on a Firebird database, and Firebird doesn't come with the kind of ecosystem you get around Postgres or MySQL. No Stack Overflow flood of answers. No official docs beyond a thin operator manual. Vendor support exists, but it's slow, and it doesn't scale to "I want to automate this specific internal workflow at 11pm on a Tuesday." So when I needed to automate payment reconciliation, close out comandas without touching the vendor's fragile UI, and trigger fiscal document emission (NFC-e) reliably, I didn't have a spec to follow. I had a live production database and a lot of curiosity. Learning a system by watching it think I started the way you'd expect: opening tables, guessing at relationships, breaking things in a test environment until I understood why they broke. Over time that turned into something more systematic — I ended up documenting 390 tables and 514 foreign keys across roughly 40 functional modules, entirely from observation. No vendor documentation, no source code access. Just structure, inference, and a lot of trial and error. Some of what I learned only reveals itself under pressure: Firebird's SQL dialect has its own quirks — FIRST 1 instead of LIMIT , for one. Small thing, but it breaks every query you copy-paste from a Postgres tutorial. Primary keys aren't auto-incrementing in the way you'd assume. They're driven by generators ( GEN_ID ), and if you write a record without syncing

Last week we kicked off a series covering the (data/storage/network) announcements that, in my view, had the most impact. This week I'm bringing 5 more recent launches to run through the fine-tooth comb. Description, the pain point it solved, and my critical take on each one. 1. Aurora DSQL lands in five more regions What it is: Aurora DSQL, AWS's distributed, serverless SQL database, is now available in Asia Pacific (Hong Kong), Asia Pacific (Mumbai), Asia Pacific (Singapore), Europe (Stockholm), and South America (São Paulo). With this launch, it now covers 19 regions total. Official link: https://aws.amazon.com/about-aws/whats-new/2026/05/amazon-aurora-dsql-five-additional-aws-regions/ How it used to be (the pain point): Until now, if you wanted to run Aurora DSQL with decent latency for users in Brazil, there was no regional option here. You either accepted the latency of hitting us-east-1 (an ugly phrase for a transactional application to hear) or gave up on DSQL entirely and went with a traditional Aurora PostgreSQL, purely for geographic proximity. My take: region expansion is the kind of announcement that isn't sexy, but it's what decides whether a service actually goes into production or stays a showcase item. With São Paulo on the list, the main adoption blocker for DSQL around here (latency) drops significantly. That said, it's worth remembering DSQL is still a relatively young product, with compatibility limitations against full PostgreSQL (extensions, certain data types, certain transactional features). Regional presence solves latency, it doesn't solve feature-set maturity. Those are two separate conversations. 2. Aurora DSQL gets a native PHP connector What it is: launch of the Aurora DSQL connector for PHP (PDO_PGSQL), which handles IAM authentication natively: generates a token per connection, guarantees the valid token gets used, and keeps full compatibility with the PDO_PGSQL that the PHP ecosystem already knows. It also brings retry with exponent

A contact table is easy to build. A usable CRM is an operations system. The difficult part is not storing a name and email address. It is preserving context as work moves between people, organizations, deals, tasks, notes, imports, reports, and follow-up. That requires deliberate data modeling and product decisions. Model relationships, not isolated records A customer may belong to an organization. An organization may have several contacts. A deal can involve multiple people, tasks, notes, and status changes. If those relationships are flattened into one spreadsheet-like table, duplicate data and contradictory updates appear quickly. Define stable identifiers and explicit relationships early. Treat activity history as a product feature Users need to know what changed, when it changed, and who changed it. Audit history supports troubleshooting and accountability. It also makes bulk operations safer: after an import or mass edit, an administrator should be able to understand the result rather than guessing which rows moved. Decide which actions deserve history, how long it is retained, and who can see it. Avoid collecting sensitive data simply because the schema allows it. Design imports for failure CSV import is where clean demos meet messy reality. A useful import flow should provide: column mapping; required-field validation; duplicate-handling rules; a preview before committing; clear row-level errors; an exportable error report; idempotent or recoverable behavior where practical. Never assume the first row contains perfect headers or that dates, phone numbers, and booleans use one format. Make views part of the workflow Saved filters and views let different roles focus on their work without changing the underlying data. A sales view may emphasize open deals and next actions. An operations view may emphasize overdue tasks. An administrator may need import history and permission context. This is more than UI convenience: it is a way to keep one shared system useful

Part 3 of the Building the AI Memory Stack series After finishing Part 2, I noticed something. The...

Managed databases are the cloud cost line people quietly stop looking at. Compute gets rightsized, storage on the instances gets cleaned, but the RDS, Aurora, or Azure SQL bill just grows, because a database feels too load-bearing to touch. It is not. Here is how I have cut managed database spend without a maintenance window, in the order of least risk to most. The theme throughout: databases give you more no-downtime levers than people assume, and the biggest wins are usually storage and rightsizing, not some exotic re-architecture. Start with the free win: reclaim dead storage Storage is where the surprise terabytes hide, and most of it comes off with zero downtime. Drop what nobody reads. Old audit tables, soft-deleted rows that were never purged, expired sessions, staging data that got promoted to prod years ago. A DELETE in batches plus a purge job is the boring, safe first move. Reclaim space after deletes. On Postgres, deleted rows leave bloat until vacuumed. Run VACUUM (and check pg_stat_user_tables for dead tuples). On SQL Server / Azure SQL, rebuild or reorganize fragmented indexes to reclaim pages. This is where the "reclaimed terabytes" headlines actually come from. Kill redundant indexes. Unused and duplicate indexes cost storage and slow writes. Postgres pg_stat_user_indexes (look for idx_scan = 0 ) and SQL Server's missing/unused index DMVs tell you which ones earn their keep. Dropping an unused index is online. Right-size your storage type. On AWS, moving from gp2 to gp3 lets you provision IOPS and throughput independently and usually costs less for the same performance. The modify is applied without downtime. None of the above requires a window. It is pure hygiene, and on a neglected database it is often the single biggest line-item drop. Rightsize the instance (yes, without downtime) The reflex fear is that resizing a database means an outage. With a Multi-AZ deployment it usually does not. Check if you are oversized first. Pull 30 days of CPU, fre

You've seen the headlines by now. An agent in Cursor wiped a company's production database, backups and all, in about nine seconds. Replit's agent nuked another company's prod. Same shape every time: the agent was sure of itself, the SQL was valid, and nobody was in the loop to say wait. Those are the loud failures. The fix for them is boring and you already know it. Don't hand an agent write access to prod. Read-only by default, propose instead of apply, keep a human on the button. But there's a quieter version that a permissions policy won't catch, and that's the one I want to talk about. Your agent is probably doing it right now. It looks completely fine in the diff. The migration that passes review and still takes the site down Ask an agent to make an email column unique. It writes: ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE ( email ); Correct SQL. Does exactly what you asked. It sails through review because there's nothing to see. Then on a users table with any real size, it grabs an ACCESS EXCLUSIVE lock and scans every row to build the unique index, and for the whole length of that scan nothing else can read or write the table. The API starts timing out. The connection pool fills. Now you're in an incident over a one-line migration that everybody approved. The agent didn't do anything a decent junior engineer wouldn't have done. That's the trap. The danger isn't the SQL, it's the lock the SQL takes, and you can't see a lock by reading a statement. You'd have to know Postgres locking cold: which DDL grabs which lock, and for how long, and what it shuts out while it holds. And you'll still miss one at 2am. I got tired of missing them. So I measured one. What the lock actually costs I ran the same schema change two ways against a real Postgres 18. Fifty million rows, twenty connections doing ordinary traffic. The unsafe version was a plain SET NOT NULL , which also scans under ACCESS EXCLUSIVE . The safe version was the NOT VALID then VALIDATE da