标签：#Postgres

COUNT(*) looks like a trivial operation: SELECT COUNT ( * ) FROM orders ; The query asks for a single number, but that doesn't mean PostgreSQL can produce it with a constant-time read from some internal counter. When we need an exact count, PostgreSQL has to determine how many rows are actually part of the visible result set for that query. On large tables, that work can become a meaningful chunk of total execution time. And the problem doesn't just go away by throwing an index at it. The useful question isn't "do I have an index?" It's: How many rows does PostgreSQL actually need to examine to compute this count — and can that work be reduced? Why COUNT(*) Can Be Expensive in PostgreSQL PostgreSQL uses MVCC — Multi-Version Concurrency Control — to manage concurrent access to data. That's what lets multiple transactions work at the same time while each sees a consistent view of the database. But it also means row visibility depends on the snapshot the query is running under. That's why PostgreSQL can't answer: SELECT COUNT ( * ) FROM orders ; by simply reading an exact counter stored somewhere in the table's metadata. To return an exact result, it has to process the rows — or an index structure representing those rows — and determine which ones are part of the visible result. On a small table, that cost is invisible. On a table with millions of rows, the amount of work starts to matter. Which leads to an important distinction: returning a single row from COUNT(*) does not mean processing a single row. How to Analyze a COUNT with EXPLAIN ANALYZE Before reaching for an index, it's worth looking at what PostgreSQL is actually doing. Say we have this query: SELECT COUNT ( * ) FROM orders WHERE status = 'completed' ; We can analyze it with: EXPLAIN ( ANALYZE , BUFFERS ) SELECT COUNT ( * ) FROM orders WHERE status = 'completed' ; The goal isn't to hunt for an Index Scan by default. Worth checking instead: the scan type estimated rows vs. actual rows processed rows discard

Overview In this article, I'll introduce the concept of Multi-Version Concurrency Control (MVCC) and explain how Postgres implements this protocol across different isolation levels. I'm assuming you already have a basic understanding of isolation levels, database locks, and concurrency in general. I won't cover those concepts here, so if you're not familiar with them, I highly recommend checking out A Straightforward Guide for Isolation Levels first before continuing. The goal of this article is to help you understand: What Multi-Version Concurrency Control is How Postgres implements MVCC across different isolation levels Multi-Version Concurrency Control High-Level Concept The idea behind MVCC is simple: it's a protocol designed to accomplish one goal — when two or more transactions run concurrently on the same data, the end result should look as if those transactions ran one after another, in sequence. Take a look at the diagram above. Two transactions are running concurrently, and we want the end result to look as if either the first transaction ran and committed before the second one started, or vice versa. MVCC guarantees there are only two possible outcomes — never a third. But in reality, these transactions are running at the same time, so this is exactly the core idea of MVCC: it's a protocol that gives us this guarantee even though the transactions genuinely overlap in time. Note that other protocols aim for the same goal, like Two-Phase Locking and Optimistic Concurrency Control. They all take different approaches, but they're all working toward the same thing. The core idea of MVCC is that whenever a transaction updates a row, it doesn't mutate the value in place. Instead, it creates a new record as the latest version and links it back to the old version. After the update, the row has a new version, and the old version is never changed. This version chain exists per record — every update to a row creates a new version, and since each version is linked to

pg_anon found 1 of the 8 personal-data columns in my PostgreSQL database. The one it caught was email , and only because "email" is spelled the same in Spanish and English. The other seven — nombre , apellido , telefono , direccion , fecha_nac , tarjeta_ult4 and rut — walked straight through, unmasked. pg_anon is the open TantorLabs tool that masks personal data in PostgreSQL: it scans the database, flags the sensitive columns, and dumps a masked copy. Like pg_dump , but covering the sensitive parts on the way out. Exactly what you want before handing a colleague a copy of production. So I fed it a Chilean database and watched it miss almost everything — no error, no warning. It finished successfully and handed me a dump with names and national IDs still in cleartext. What the scan actually does Two filters, in order. First it reads each column's name against a set of regexes ( ^email$ , ^phone$ , ^ssn$ …). To the columns left over, it opens the data and tries patterns on the value (an email's @ , a card's 16 digits). Whatever no filter catches passes through. The rules in the demo meta-dict it ships with are written for English schemas. Mine aren't. 1 of 8 Column Holds Stock rules email email ✅ nombre first name ❌ apellido surname ❌ telefono phone ❌ direccion address ❌ fecha_nac birth date ❌ tarjeta_ult4 card digits ❌ rut national ID ❌ email got caught by name (it's an English word) and confirmed by its @ . Everything else has a Spanish name no stock rule looks for. The rut is the clearest miss. A RUT looks like 7917183-2 : seven or eight digits, a dash, and a mod-11 check digit that can be the letter K . The only national-ID rule pg_anon ships with is ssn . It has no idea what a RUT is — and it won't know a cpf (Brazil), dni (Spain, Argentina), curp (Mexico), nif (Portugal) or aadhaar (India) either. If your schema isn't American, the defaults miss your most sensitive column. The fix: a few lines of Spanish You teach it. Column names in your language, plus a conte

Same query. Same table. Same million rows. One day it takes 4 seconds . The next day, 4 milliseconds . Nothing changed in the data. The only thing that changed was one line — you added an index . Four seconds to four milliseconds is a thousand times faster, from one line of SQL. But here's the part nobody tells you: half the indexes people add do nothing. The query stays slow, the writes get slower, and they can't figure out why. By the end of this you'll know what an index actually is — and the one rule that decides whether yours even gets used. Prefer to watch? Full walkthrough with the B-tree lookup animation: With no index: a full table scan You ask the database for one user by email. With no index, what does it do? It reads the first row. Not a match. The second row. Not a match. It keeps going — every single row — until it finds yours or runs out. A million rows, a million checks. SELECT * FROM users WHERE email = 'vlad@stack.dev' ; With no index, that WHERE line has only one way to run: look at all of them. The work grows with the table — ten times the rows, ten times the wait. That's a full table scan , and that's your four seconds. What an index actually is Most people picture an index as a copy of the table, or some kind of cache. It's neither. An index is a sorted map — just the column you search on, kept in order, with a pointer back to the full row. And the shape it's sorted into has a name: a B-tree (the default index in both Postgres and MySQL — technically a B+ tree). At the top, one node — the root . It splits into a few branches . Each branch splits again, down to the leaves , where the pointers to the rows actually live. Every node is sorted. The root doesn't hold your data — it holds signposts . Emails before "M"? Go left. "N" and after? Go right. Each step throws away half the tree, or more. You're never reading rows. You're following signs. The walk: three hops, not a million rows Watch what the lookup actually does: The root — one hop. A branc

The Transactional Outbox Pattern: Dual-Write Consistency in Distributed Systems One of the most dangerous anti-patterns in microservices architecture is the Dual-Write Vulnerability : updating a database record and immediately publishing an event to a message broker (e.g., RabbitMQ, Kafka) in the same API call. If the network fails or the broker is unavailable after the database transaction commits, the event is lost forever. Conversely, if the event publishes but the database rollback triggers, downstream consumers process a phantom event that does not exist in the source of truth. In this deep dive, we architect the Transactional Outbox Pattern with Change Data Capture (CDC) to guarantee At-Least-Once delivery with zero distributed locking overhead. Technical & Interview Cheat Sheet Approach Consistency Guarantee Failure Mode Overhead Dual Write (Naive) None (Eventual inconsistency) Message lost if broker drops Low 2-Phase Commit (2PC / XA) Strict Atomicity Blocking locks, single point of failure Very High Transactional Outbox (Polling) At-Least-Once Polling query table contention Moderate Outbox + CDC (Debezium) At-Least-Once (Zero Table Locking) Requires WAL decoder plugin Optimal 1: Database Schema Design The business entity change and the outbox event MUST commit within the exact same database transaction: -- Business Entity CREATE TABLE orders ( id UUID PRIMARY KEY DEFAULT gen_random_uuid (), customer_id UUID NOT NULL , total_amount NUMERIC ( 12 , 2 ) NOT NULL , status VARCHAR ( 32 ) NOT NULL , created_at TIMESTAMPTZ NOT NULL DEFAULT NOW () ); -- Transactional Outbox Table CREATE TABLE outbox_events ( id UUID PRIMARY KEY DEFAULT gen_random_uuid (), aggregate_type VARCHAR ( 64 ) NOT NULL , aggregate_id VARCHAR ( 64 ) NOT NULL , event_type VARCHAR ( 64 ) NOT NULL , payload JSONB NOT NULL , created_at TIMESTAMPTZ NOT NULL DEFAULT NOW () ); -- Index for high-throughput CDC streaming CREATE INDEX idx_outbox_created ON outbox_events ( created_at ); 2: Atomic C# Tra

The bug that doesn't show up in tests — and what to do about it There is a class of bug in event-driven systems that is almost invisible in development and devastating in production: publishing a message to Kafka for data that never actually reached the database. It doesn't crash. It doesn't throw. The Kafka message goes out, the consumer picks it up, and it tries to process a batch that doesn't exist. Depending on your retry and error handling strategy, this can cascade silently for a long time before anyone notices. The fix is simple. The reason most people don't apply it is that the problem isn't obvious until you've seen it. The Problem: Publishing Inside the Transaction The intuitive approach is to publish to Kafka as part of the same transactional method: @Transactional public void process ( SettlementWindow window , LocalDate today , Participant participant ) { // ... FileBatch savedBatch = batchPort . save ( batch ); orderPort . updateStatusBatch ( orders ); // Publishes BEFORE the transaction commits publisherPort . publish ( savedBatch ); } This looks safe. The transaction is still open, the data is there, everything is consistent — until the transaction rolls back. If anything fails after publish() — another database update, a constraint violation, an unexpected exception — Spring rolls back the transaction. The database returns to its previous state. But Kafka already received the message. There is no rollback for Kafka. The consumer now holds a reference to a FileBatch that does not exist in the database. This is a phantom message . The Fix: afterCommit() Spring's TransactionSynchronizationManager provides a hook that fires after the transaction has successfully committed: @Transactional ( propagation = Propagation . REQUIRES_NEW ) public void process ( SettlementWindow window , LocalDate today , Participant participant ) { // ... FileBatch savedBatch = batchPort . save ( batch ); orderPort . updateStatusBatch ( orders ); // Kafka fires only after the d

Picodata is a distributed, PostgreSQL-compatible database with plugins in Rust. Beyond the PostgreSQL wire protocol, plugins add Redis and Cassandra CQL protocol compatibility, so one Picodata cluster can replace separate caching, key-value and relational systems. It is open source and self-hosted. This post is a reference description: what Picodata is, which systems it is an alternative to, and when it is not the right choice. Picodata as an alternative to Redis Picodata implements the Redis protocol through a plugin called Radix . Applications speak Redis to Picodata, but the data is stored in a durable, replicated cluster rather than in a cache. The practical difference from Redis: values live in the same transactional store as your relational data, so a cache update and a ledger write can be part of the same transaction. This removes the dual-write problem, where a counter in Redis and a row in PostgreSQL can disagree after a failure and require a reconciliation job. Durability is WAL-based rather than best-effort. Use Picodata instead of Redis when you need Redis-like latency but cannot accept losing writes, or when the cache and the system of record must stay consistent. Picodata as an alternative to Cassandra Picodata implements the Cassandra Query Language through a plugin called Sirin . Applications issue CQL against Picodata. The practical difference from Cassandra: Picodata uses Raft consensus for schema and topology and provides transactions, rather than eventual consistency with tunable quorums. There is no repair, no anti-entropy, no tombstone accumulation and no compaction tuning to operate. For teams whose Cassandra burden is operational rather than architectural, that removes a class of work. Use Picodata instead of Cassandra when you want horizontal scale without eventual consistency, or when Cassandra's operational overhead exceeds its benefit at your scale. Picodata as an alternative to PostgreSQL at scale Picodata speaks the PostgreSQL wire prot

Disclaimer: This is a side project, not a production story. The slow-query problem is real, but the database is synthetic data I generated to make it show up on demand. I have no connection to PgCache. Everything here is in a repo you can clone and run. I tested version 0.6.2. A handful of dashboard queries on one of my projects were fine for a year and then weren't: count users by tier, revenue grouped by country, best-selling products per category. Nothing exotic, just aggregates and joins over tables that had gotten big. The usual fixes didn't sit right with me. A materialized view means picking a refresh interval and serving slightly stale numbers in between. Redis in front of Postgres means writing and maintaining code that knows which cache entries to throw away on every write. A read replica just runs the same slow query on another machine. PgCache offers a different trade. It's a proxy that talks the Postgres wire protocol, so your app connects to it as if it were the database. It caches reads. And instead of expiring entries on a timer, it follows Postgres's replication stream and refreshes a cached result when the rows behind it change. That stream is the same feed Postgres uses to copy data to a standby server , a running log of every insert, update, and delete. The "no timers, no manual invalidation" part is the interesting claim. Here's how it held up. A database big enough to be slow First I needed a database where "slow" was real and not a rounding error. I wrote a seed script for a small e-commerce schema and filled it to about 16 million rows: Table Rows Notes users 1,000,000 10 countries; tiers 50% free / 33% pro / 17% enterprise products 2,000 10 categories orders 5,000,000 four statuses, random totals, spread over two years order_items 10,000,000 about two per order I added indexes on every foreign key and on every column the test queries filter or group by. That was on purpose. I wanted to compare PgCache against a Postgres that had been tuned p

Adding an index stops working eventually. Here's what we did when a nationwide logistics platform's core table crossed a billion rows — and the parts nobody warns you about. There's a specific moment in a backend engineer's life when the usual advice stops working. A query gets slow. You check the execution plan, you add an index, it gets fast again. This works for years. It works so reliably that it starts to feel like a law of nature. Then one day you add the index and nothing happens. Or worse — the index takes six hours to build, locks the table while it does, and the query is still slow at the end of it. That's roughly where we were on a nationwide logistics platform processing tens of thousands of orders a day. The tracking events table — one row per scan, per parcel, per status change — had crossed a billion rows. Every parcel generated a dozen or more events on its journey. The table only ever grew. This is what we did about it, and more usefully, what nobody told us beforehand. First: are you sure you need this? Partitioning is not a performance trick you reach for when a query feels sluggish. It carries real operational cost, and most tables that people want to partition should just be indexed properly. Some honest signals that you're actually at the boundary: Your indexes no longer fit comfortably in memory, so index reads hit disk Index maintenance — REINDEX, VACUUM, ANALYZE — takes so long you can't schedule it Deleting old data is impossible in practice, because a DELETE of a hundred million rows will destroy your write throughput for hours Your queries almost always filter on a single obvious dimension, usually time That last one matters more than the rest. Partitioning only helps if your access pattern lines up with how you split the data. If your queries hit every partition anyway, you have added complexity and gained nothing. For us the alignment was clean: nearly every query on the events table was scoped to a date range. Operations dashboards loo

Python PostgreSQL with asyncpg: Async Database Operations asyncpg is the fastest PostgreSQL driver for Python — pure asyncio, no thread overhead, and up to 3× faster than psycopg2 on typical workloads. It is the go-to choice for any async Python backend. Installation pip install asyncpg # PostgreSQL server must already be running Connect and Create a Pool import asyncio import asyncpg from datetime import datetime DATABASE_URL = " postgresql://user:password@localhost:5432/mydb " async def create_pool () -> asyncpg . Pool : pool = await asyncpg . create_pool ( DATABASE_URL , min_size = 2 , max_size = 10 , command_timeout = 30 , server_settings = { " application_name " : " myapp " }, ) print ( " Pool created. " ) return pool Schema Setup CREATE_TABLES = """ CREATE TABLE IF NOT EXISTS users ( id BIGSERIAL PRIMARY KEY, username TEXT NOT NULL UNIQUE, email TEXT NOT NULL UNIQUE, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE TABLE IF NOT EXISTS posts ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE, title TEXT NOT NULL, body TEXT NOT NULL DEFAULT '' , published BOOLEAN NOT NULL DEFAULT FALSE, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX IF NOT EXISTS idx_posts_user ON posts(user_id); CREATE INDEX IF NOT EXISTS idx_posts_created ON posts(created_at DESC); """ async def setup_schema ( pool : asyncpg . Pool ) -> None : async with pool . acquire () as conn : await conn . execute ( CREATE_TABLES ) print ( " Schema ready. " ) INSERT — Adding Records async def create_user ( pool : asyncpg . Pool , username : str , email : str ) -> int : async with pool . acquire () as conn : row = await conn . fetchrow ( """ INSERT INTO users (username, email) VALUES ($1, $2) ON CONFLICT (username) DO UPDATE SET email = EXCLUDED.email RETURNING id, created_at """ , username , email , ) return row [ " id " ] async def create_post ( pool : asyncpg . Pool , user_id : int , title : str , body : str , published : bool = False , ) ->

Startups building B2B products reach for multi-tenancy in PostgreSQL the same way on day one: one shared database, one set of tables, and a tenant_id column marking who owns each row. That is the correct call, and it stays correct for a long time. However, when that column is enforced by application code rather than by the database, a single forgotten predicate stops being a bug and becomes a disclosure event, and a disclosure event is one of the very few engineering failures that lands straight on your balance sheet as stalled enterprise deals, an unplanned legal bill, and a security review you can no longer pass. By understanding what multi-tenancy actually guarantees, which isolation model fits your stage, and how Row-Level Security moves that guarantee out of your codebase, startup CTOs and Fractional CTOs can make the tenant boundary hold without slowing the team down. (If you want to skip the theory, jump straight to the connection pooler trap that switches Row-Level Security off in production, what it costs in query performance, or when it is genuinely time to leave the shared schema.) Because "enforced by application code" means something very specific in practice. It means a promise that everyone will remember to filter on tenant_id , and that promise is the single most expensive line of undocumented policy in your entire codebase, because it holds perfectly for about fourteen months, right up until the afternoon a tired engineer ships a reporting endpoint that joins four tables and forgets the predicate on exactly one of them, and then a customer opens a dashboard and sees somebody else's invoices. That is not a bug. A bug is something you fix on Monday. A cross-tenant data leak is a disclosure event, which means legal gets involved, your enterprise prospects get an email from their own security team, and the deal that was supposed to close your Series A quietly moves to next quarter and then to never. The uncomfortable part is that this is not a story abo

How I Built a Wedding Planning Suite with Supabase in 3 Months Quick Answer: I built a full wedding planning platform in 90 days using Supabase as the backend (PostgreSQL database, real-time subscriptions, Row Level Security, and OAuth auth), Next.js 14 for the frontend, and a few carefully chosen npm packages for specific features like QR code scanning. The key was leveraging Supabase's managed services to avoid building auth, websockets, and file storage from scratch. Introduction Three months ago, I had an idea: what if couples could plan their entire wedding through one cohesive platform? Not a static checklist app, but a living, breathing system where vendors, guests, budgets, and timelines all talked to each other in real time. I'm a solo developer with a day job. I didn't have a team of backend engineers to build authentication, real-time sync, or file storage infrastructure. I needed a stack that would let me ship fast without shipping broken. Enter Supabase. I'd heard the "Firebase alternative" pitch before, but what I discovered was something far more powerful for developers who actually want to own their data and their SQL. This is the story of how I built WedPlanner—a full wedding planning suite—with Supabase, Next.js, and a few other tools. No VC funding. No offshore team. Just me, a tight deadline, and a PostgreSQL database that never let me down. Why Supabase? The Architecture Decision That Made Everything Possible When you're building alone, every architectural decision compounds. Pick the wrong database, and you'll spend weeks fighting migrations. Pick the wrong auth solution, and you'll ship with security holes you don't even know about. I evaluated Firebase, PlanetScale, Clerk, and rolling my own PostgreSQL on RDS. Here's why Supabase won: PostgreSQL, not a proprietary document store. Wedding data is relational. A guest belongs to a wedding. A vendor has multiple bookings. A budget category has many line items. Trying to model this in Firestore's

Choice Setup burden Incident evidence Best fit Hosted metrics API Low Good if event context is preserved Small teams with an on-call rotation Postgres plus a custom dashboard Medium Excellent for joining metrics to business records Low-volume systems with strong SQL skills Self-hosted metrics stack High Configurable, but operationally demanding Teams that already run observability infrastructure Short answer: start with a hosted metrics dashboard API, send a small set of custom application metrics from Node.js, and retain reconstruction fields in Postgres. Choose the custom Postgres path when joins are the investigation, or self-hosting when data control outweighs maintenance. That recommendation has a catch. A chart can show when enrollment failures rose, but it cannot explain which course, release, region, or feature state produced them unless those dimensions were recorded at write time. For an edtech SaaS, the real deliverable isn't a pretty dashboard. It is enough evidence to replay the story of a customer incident without guessing. How can Node.js send custom app metrics to a hosted dashboard API? Capture the dimensions an investigator can act on: metric name, timestamp, deployment identifier, region, tenant or school identifier, operation, outcome, and a bounded error class. Keep direct student data out of labels. A useful event might say that lesson_publish failed validation in the EU region on deployment 7f3c2a1 ; it should not contain a learner's name, email, answer, or free-form support message. Small is good. Stop there. Start with service-level signals tied to customer work: request count, failure count, latency distribution, queue depth, and the age of the oldest queued job. Add business-flow counters such as course publication attempts only when they answer a concrete incident question. Don't export every database column as a label. High-cardinality dimensions make charts harder to read, alerts harder to tune, and the ingestion boundary harder to reas

Here is the disk layout of one mobile mapping survey — a vehicle with a LiDAR scanner and a panoramic camera, driven along a road: data/001_MMS/ 507 MB point cloud orbit/oblak/ 566 MB spherical photos trajectory/*.gpkg 108 KB the path the vehicle drove Just over a gigabyte. The database this feeds holds 2.3 GB in total — for 2.7 million road features across a hundred layers. Two more surveys and the binary data outweighs everything the database has ever stored. So the question isn't how to put a point cloud in Postgres. It's what you put in Postgres instead . The trajectory is the index Of that gigabyte, one file goes into the database: the 108 KB trajectory, a GeoPackage holding the line the vehicle drove. That line is what makes the survey findable. It draws on the map with everything else. You can ask which surveys cover a junction, which are newest, whether a stretch of road has been captured since the resurfacing. All the questions people actually ask are questions about where and when , and the trajectory answers every one of them at 0.01% of the storage. The heavy files never enter the database. The row holds paths: class Cloud ( models . Model ): name = models . CharField ( max_length = 120 , db_index = True ) path_name = models . CharField ( max_length = 120 ) # -> octree metadata JSON orbit_url = models . CharField ( max_length = 255 ) # -> spherical photo index spherical_photo = models . BooleanField ( default = False ) recording_date = models . DateField ( null = True ) source_srid = models . IntegerField ( null = True , choices = SOURCE_SRID_CHOICES ) available = models . BooleanField ( default = True ) Metadata, geometry, and pointers. That's the whole trick, and it isn't clever — it's just the discipline to not reach for a bytea column. Why not in the database Postgres will happily store a gigabyte. It's the access pattern that kills you. A browser point cloud viewer doesn't fetch a point cloud. It fetches an octree : a tree of small files, and as the

The schema catalog for an AI assistant is the artefact that answers the question "what does this database look like right now". Whether the database is Postgres, MySQL, SQL Server or Redshift, the shape of the problem is the same: the catalog carries table names, column names, types, keys, and enough relationships to let the assistant write a query that resolves. It lives somewhere between the database and the assistant, has to stay in sync with a database that changes underneath it, and is almost always built the same weekend the team decides they want an AI assistant reading their data. It runs fine for the first three tables. The problems start around the fourth week, and none of them look like the same problem twice. The distinction worth naming early is between the connection layer (how the assistant reaches the database) and the knowledge layer (what the assistant knows about the database's shape). The connection layer receives most of the attention, because credentials, network isolation and query cost are visible failure modes and easy to argue about. The knowledge layer is where most of the actual quality of the assistant lives, and it decays quietly. The AI database context page covers why this second layer matters at all when the first one exists. Why not just point the assistant at the database Connecting the AI directly to production is the shortest path and the one most teams reject after five minutes of thinking about it. The assistant would get read access on tables it should not see, its queries can be arbitrarily expensive, its credentials would live somewhere they should not, and the audit trail becomes hard to reason about. What most teams end up building is a layer in between: a representation of the database that the assistant can read cheaply and safely without ever touching production. That layer is what this article is about. It is not the connection. It is the catalog. The five recipes teams build Ask fifteen senior developers how to build

The PostgreSQL manual is unusually direct about this: When an application receives this error message, it should abort the current transaction and retry the whole transaction from the beginning. "The whole transaction" is doing a lot of work in that sentence, and it is the part that gets dropped. TypeORM issue #9806 — "Auto Retry options on error in transactions (e.g. Deadlock)" — has been open since February 2023. Thirty 👍, six comments, no implementation. Meanwhile typeorm-transactional , at 188,000 downloads a week, ships @Transactional() with isolation levels and seven propagation modes and no retry at all. So the ecosystem's actual answer to "how do I use SERIALIZABLE in Node" is: don't. Use READ COMMITTED , don't think about write skew, and hope. I spent a while building the thing that issue asks for. The short version of what I found: the feature as literally requested cannot be built correctly , and the reason is more interesting than the feature. The implementation everyone reaches for first Wrap the query. It's the obvious move — the error came from a query, so retry the query: async function withRetry < T > ( fn : () => Promise < T > , attempts = 3 ): Promise < T > { for ( let i = 1 ; ; i ++ ) { try { return await fn (); } catch ( e ) { if ( i >= attempts || ! isSerializationFailure ( e )) throw e ; await sleep ( 50 * i ); } } } await dataSource . transaction ( ' SERIALIZABLE ' , async ( em ) => { const from = await em . findOneOrFail ( Account , { where : { id : fromId } }); const to = await em . findOneOrFail ( Account , { where : { id : toId } }); await withRetry (() => em . decrement ( Account , { id : fromId }, ' balance ' , amt )); // ← here await withRetry (() => em . increment ( Account , { id : toId }, ' balance ' , amt )); // ← and here }); This does nothing. Worse than nothing — it turns one clear error into a confusing one. When PostgreSQL raises 40001 , it does not fail that statement . It aborts the entire transaction . The connection is now

If an LLM is generating PostgreSQL in your application, there is one moment worth treating separately: after the model returns SQL, but before your code calls db.query() . Prompt rules are useful. They can make the model more likely to produce the sort of query you want. They do not decide which tables the application is allowed to read, whether multiple statements are acceptable, or whether a function call should run. I have been working on sql-guard , a TypeScript package for that gap. It parses PostgreSQL into an abstract syntax tree (AST), checks the tree against an explicit policy, and rejects anything it cannot validate confidently. Why I did not want to check SQL with regex SQL is structured. A query may have joins, subqueries, aliases, unions, and common table expressions (CTEs). Checking raw text can catch an obvious keyword, but it cannot reliably answer what the query actually does. For example: SELECT * FROM public . users ; SELECT 1 ; DELETE FROM public . users ; WITH removed AS ( DELETE FROM public . users RETURNING id ) SELECT * FROM removed ; All three examples contain SELECT , but they are not equivalent. The second has two statements. The third uses a data-modifying CTE. A validator needs to understand the query structure rather than look for a few strings. An AST makes that possible. It lets the validator inspect statement types, source tables, function calls, and nested expressions. It also means an alias or CTE name cannot conceal the base table being read. The policy is the important part sql-guard is built around allowlists. You state what a particular feature may use, and the validator checks the generated SQL against that list. Here is a small policy for an assistant that can look at users and orders: import { validate } from ' sql-guard ' ; const policy = { allowedTables : [ ' public.users ' , ' public.orders ' ], allowedFunctions : [ ' count ' , ' lower ' ], }; const result = validate ( ' SELECT lower(u.email) FROM public.users AS u ' , po

There is a layer in my database called 1 . Somebody created it, presumably by accident, and it sat there for months looking harmless. It was the only layer in the system that never served a single tile, and nobody noticed, because it was empty anyway. That layer turned out to be a symptom of a SQL injection vulnerability. This post is about the design that produced it — which I still think is a good design — and the four things I got wrong inside it. The setup A web GIS with about 2.7 million features: 1.8 million points, 697,000 lines, 172,000 polygons. Users create layers through the UI, upload data into them, edit geometry, and expect to see it on a map. The features do not live in a table per layer. They live in three tables — one for points, one for lines, one for polygons — with a layer_id foreign key and a JSON column for attributes: project_pointfeature 1,820,288 rows project_linefeature 697,009 rows project_polygonfeature 171,830 rows That's a deliberate trade. A table per layer means DDL every time a user clicks "new layer", a migration story that never ends, and a schema that drifts. Three generic tables mean one schema, one set of indexes, and layers that are just rows in a metadata table. The cost lands on the tile server. The pattern Martin serves vector tiles from PostGIS. Point it at a database and it discovers spatial tables and views and publishes each as an MVT endpoint. It can be told to publish views but not tables: postgres : auto_publish : from_schemas : [ public ] publish_tables : false reload_interval : 5s So: give every layer its own view. A Django post_save signal on the Layer model creates it: CREATE OR REPLACE VIEW t19_saobracajni_znakovi AS SELECT f . id , f . feature_attrs , f . geom , f . layer_id , l . name AS layer_name , lg . name AS layer_group_name , p . title AS project_title FROM project_pointfeature f JOIN project_layer l ON f . layer_id = l . id JOIN project_layergroup lg ON l . layer_group_id = lg . id JOIN project_project p

This is a submission for DEV's Summer Bug Smash: Clear the Lineup , powered by Sentry . Project Overview mini-agent is a public FastAPI backend for an AI support-agent demo. Its test suite covers API behavior, authentication, rate limiting, approval flows, and PostgreSQL/pgvector-backed retrieval. The GitHub Actions workflow starts PostgreSQL and Redis service containers before running the Python test suite. The application database initialization also executes: CREATE EXTENSION IF NOT EXISTS vector The dependency is also visible in the DocumentChunk.embedding column, which uses pgvector's Vector type. That made the database image part of the test contract, not just incidental infrastructure. Bug Fix or Performance Improvement On August 12, 2026, the CI run for the preceding commit reached the test step and failed: Failed workflow run Commit tested by that run The workflow was using the general-purpose postgres:17-alpine service image, while the application required the pgvector extension during database initialization. The test environment therefore did not match the database capability required by the code. The failure was specific enough to avoid a broad rewrite: the container initialized successfully, dependency installation passed, and the workflow stopped only at Run tests . That pointed to the application/database boundary rather than the GitHub Actions runner or Python installation. The fix changed one line: services: postgres: - image: postgres:17-alpine + image: pgvector/pgvector:0.8.6-pg17 Full change: Use pgvector image in CI The PostgreSQL major version, credentials, port mapping, health check, application environment, dependency installation, and test command all remained unchanged. This kept the patch narrow and made the CI database expose the same required extension as the application. Code The evidence is a direct before-and-after pair: The preceding workflow failed at Run tests . The one-line database-image commit triggered a new workflow. The new

A manual psql check answers exactly one question: does this policy work right now, against today's schema, with today's roles. It says nothing about tomorrow. Three ordinary changes are enough to quietly break tenant isolation without anyone noticing at review time. A migration that drops and recreates a table loses RLS entirely, since it's a per-table flag, not something that travels with column definitions. A new service role for a background job can skip the policy if nobody remembers to apply it. And the most common one: someone grants BYPASSRLS during an incident and never revokes it. Most guides point you at pgTAP here and stop. pgTAP is fine, but it's a separate SQL-based framework with its own runner. If your backend is already on Jest, you don't need a second test framework, you need a Jest test that actually proves a leak can't happen. The core pattern: seed a row as tenant A, query as tenant B, assert the result is empty. Run it through a dedicated low-privilege role, since table owners and superusers bypass RLS by default even with FORCE enabled for the owner. I break down the full pattern, the queryAsTenant helper, testing WITH CHECK on INSERT/UPDATE, catching accidental BYPASSRLS grants, and wiring it into GitHub Actions here: https://devencyclopedia.com/blog/postgres-rls-testing-jest If you're doing this across more than one or two tables, I also built RLSBuilder, a browser tool that generates the CREATE POLICY SQL and a matching Jest test from the same three inputs so they can't drift apart: https://devencyclopedia.com/tools/rls-builder