标签：#Postgres

My "serverless" database was billing me like it never slept. Neon has this great feature called scale-to-zero. Your Postgres compute suspends when it's idle, so you only pay for the queries you actually run. For a pre-revenue product, that should cost a few cents a month. Mine ran 24/7. The compute never once scaled to zero. The culprit wasn't my app logic. It was my database driver. I was using postgres-js, which holds a persistent connection open to the database. From Neon's side, an open connection looks like activity, so it never suspended. It just stayed awake and kept quietly billing me. The fix was basically one conceptual change: switch to @neondatabase/serverless, the HTTP driver. Instead of a long-lived connection, every query becomes a stateless one-shot HTTP request. The query fires, the connection closes, and the compute is free to suspend. Scale-to-zero finally worked. The lesson I keep relearning as a solo founder: "serverless" is a property of your whole stack, not a checkbox on one service. One persistent connection upstream and the whole cost model breaks.

You add an enum to an entity, slap @Enumerated on it, and move on. Five seconds. It is the kind of decision nobody writes a design doc for. Then six months later a row comes back as SHIPPED when it was PAID , no exception was thrown, no query failed, and you spend an afternoon learning that the default you never thought about has been silently rewriting history. Here is the order lifecycle we will use the whole way through: public enum OrderStatus { PENDING , PAID , SHIPPED , DELIVERED } Five ways to store it. They are not equivalent, and the gap between them only shows up under change. @Enumerated(ORDINAL): store the position This is the default. Leave the annotation bare and JPA stores the enum's ordinal, its index in the declaration order. @Enumerated ( EnumType . ORDINAL ) private OrderStatus status ; PENDING is 0, PAID is 1, SHIPPED is 2, DELIVERED is 3. The column is a tidy little smallint . Everything works. Until someone needs a new status and adds it where it reads well: public enum OrderStatus { PENDING , PAID , CANCELLED , // inserted here SHIPPED , DELIVERED } CANCELLED is now 2. SHIPPED is 3. DELIVERED is 4. Every row written before this change still holds the old integer, so every order that was SHIPPED (2) now reads back as CANCELLED . The database is correct. Your data is wrong. And nothing told you. If you are stuck with ORDINAL on a legacy schema, pin it with a test that fails the build the moment someone reorders: @Test void ordinalsAreFrozen () { assertEquals ( 0 , OrderStatus . PENDING . ordinal ()); assertEquals ( 1 , OrderStatus . PAID . ordinal ()); assertEquals ( 2 , OrderStatus . SHIPPED . ordinal ()); assertEquals ( 3 , OrderStatus . DELIVERED . ordinal ()); } New constants may only be appended. The test turns an invisible runtime corruption into a loud compile-time-ish failure. It is a guardrail, not a fix. @Enumerated(STRING): store the name Store the constant name instead of its position. @Enumerated ( EnumType . STRING ) private OrderS

I Built Commercial Redis CDC Source Drivers for Ruby — Here's What I Learned For the past couple of years I've been building a Change Data Capture (CDC) ecosystem for Ruby. Like many CDC projects, it started with PostgreSQL. PostgreSQL's Write-Ahead Log (WAL) is an excellent source of truth: durable, ordered, replayable, and well understood. It provides exactly the properties you want when you're building reliable event pipelines. But the deeper I went into distributed systems, the more I realized something important. Many systems don't observe change from PostgreSQL first. They observe it from Redis. Redis often sits at the front of modern architectures: Redis Streams carry application events. Pub/Sub distributes transient state changes. Keyspace notifications react to cache invalidation and key expiry. Redis Cluster routes events across multiple primaries. In many systems, Redis sees a change before PostgreSQL ever commits it. That raised an interesting question: Can Redis become a first-class Change Data Capture source? The obvious answer is "yes." The interesting answer is "yes—but not in the same way PostgreSQL does." That distinction eventually became cdc-redis-pro , a commercial Redis source driver for the Ruby CDC ecosystem. This article isn't a product announcement. It's an engineering write-up about the architectural decisions behind the project, the tradeoffs Redis forces you to make, and the execution model that ultimately emerged. Redis Doesn't Have One CDC Interface One misconception I frequently encounter is the assumption that Redis has an equivalent of PostgreSQL's WAL. It doesn't. Instead, Redis exposes several completely different mechanisms for observing change. Source Delivery Replay Streams At-least-once Yes Pub/Sub At-most-once No Sharded Pub/Sub At-most-once No Keyspace Notifications At-most-once No At first glance they all look like "events." Operationally they're completely different systems. Streams are durable. Pub/Sub isn't. Keyspace not

I'll be honest: I almost did multi-tenancy the wrong way. When I started building InspectIQ "a SaaS platform for Florida home inspectors" my first instinct was to add a tenant_id column to every table and filter it in the application layer. Every query would have a WHERE tenant_id = :current_tenant clause. Simple, familiar, done. Then I thought about what happens when you forget one. One missing WHERE clause. One endpoint that skips the filter. One inspector sees another inspector's client data. In a home inspection business, that's not just a bug — it's a HIPAA-adjacent nightmare and a trust-destroying moment with your first customer. So I did it properly from day one: Row Level Security at the database layer. What is Row Level Security? RLS is a PostgreSQL feature that lets you define policies directly on tables. When a user queries a table, the policy runs automatically, before your application code even sees the results. You can't forget to apply it. You can't bypass it with a careless JOIN. It's enforced at the lowest possible layer. For a multi-tenant SaaS, this is exactly what you want. How I implemented it Every table in InspectIQ has this pattern: ALTER TABLE inspections ENABLE ROW LEVEL SECURITY ; ALTER TABLE inspections FORCE ROW LEVEL SECURITY ; CREATE POLICY tenant_isolation ON inspections USING ( tenant_id = NULLIF ( current_setting ( 'app.current_tenant_id' , true ), '' ):: uuid ); The FORCE is important — it applies the policy even to the table owner. No superuser backdoor. The tenant context comes from the JWT. When an inspector logs in, their tenant_id is embedded as a custom Cognito claim. The FastAPI middleware extracts it and sets it at the start of every request: await session . execute ( text ( f " SET LOCAL app.current_tenant_id = ' { tenant_id } '" ) ) SET LOCAL scopes the setting to the current transaction. When the transaction ends, it's gone. No leakage between requests. Aurora PostgreSQL Serverless v2 I'm running this on Aurora PostgreSQ

Deploying a Spring Boot backend should be simple in theory. Build the JAR, set the environment variables, connect the database, and ship it. In practice, my deployment exposed several assumptions that worked locally but failed immediately in the cloud. I recently deployed a modular Spring Boot application to Render using Docker, Render Blueprint, PostgreSQL, Redis, Flyway migrations, Spring profiles, Hibernate/JPA, and environment variables. The application worked locally with MySQL and Redis, but deployment exposed several production-specific issues that were easy to miss in local development. This article documents the problems, why they happened, and how I fixed them properly. Who This Article Is For This article is useful if you are deploying a Spring Boot application to Render and your local setup uses MySQL, Redis, Flyway, Docker, or a multi-module Maven structure. It is especially relevant if you are moving from a local MySQL setup to PostgreSQL in the cloud. The Stack The backend was a Java 17 Spring Boot application with multiple Maven modules: alagbafo/ ├── api-contracts ├── core ├── users ├── orders ├── payments ├── wallet ├── notifications ├── admin ├── subscriptions └── app The app module was the actual Spring Boot entry point. Locally, the project used MySQL and Redis: spring.datasource.url = jdbc:mysql://localhost:3306/alagbafo spring.datasource.driver-class-name = com.mysql.cj.jdbc.Driver spring.data.redis.host = localhost spring.data.redis.port = 6379 For Render, the target setup was: Spring Boot app PostgreSQL database Redis-compatible Key Value store Docker deployment Flyway migrations Render Blueprint was the best fit because it allowed the infrastructure to be described in a render.yaml file. Step 1: Dockerfile for a Multi-Module Spring Boot App Because the project was a multi-module Maven application, the Dockerfile had to copy all module pom.xml files before copying the source code. This improves Docker layer caching because dependencies can b

We opened Chapter 1 with a single line, SELECT * FROM users WHERE id = 1 . For that line to leave the client and come back as a result row, the PostgreSQL backend went through five stages. First it decided which processing path the message should take; then the parser and analyzer turned the text into a tree and gave it meaning from the catalog. The rewriter expanded views and injected policies to transform the tree, the planner weighed the possible execution paths by cost and picked the cheapest one, and the executor followed that plan, pulling up one tuple at a time and sending them back to the client. Chapter 1 was a story about how a query is processed . What tree a given SQL becomes, what plan it turns into, in what order it runs. From start to finish, a chain of logical transformations. But what every one of those stages ultimately deals with is data. The executor pulls up tuples, yet where on disk those tuples lie and in what shape, how they come up into memory, Chapter 1 never asked. When the planner judged an index scan cheaper than a sequential scan, it never opened up what that index physically is. Chapter 1 followed only the logical journey of a query, leaving untouched the substance of the data that journey stands on. Chapter 2, Storage & Access Methods, opens up that substance. In what unit data sits on disk (page), where disk and memory meet (buffer manager), where and how a row survives (heap), and how that row is found quickly (B-tree and the specialized indexes). The very tuple the planner weighed by cost and the executor pulled up in Chapter 1, where it actually came from and how it came to be there, is what Chapter 2 reveals. If Chapter 1 was the logical life of a query, Chapter 2 is the physical dwelling of data. We now look at how the data a query reaches for actually lives on disk.

A scan node sits at the leaf of the tree and pulls rows from a single table. A join node sits in the middle and brings together the rows that its two children send up. It takes one row from users , one row from orders , checks whether they belong to the same user, and if they match, emits the combined row. PostgreSQL has three nodes for this one job: NestLoop, HashJoin, and MergeJoin. The reason a single task splits into three nodes is much like the reason scans did. There is more than one way to find matching pairs from two inputs, and which way is cheapest depends on the size of the inputs and the shape of the join condition. Deciding which way is cheapest, by costing the alternatives, was the planner's job in an earlier chapter. This section looks at what those three nodes actually do when they execute. Given the same two tables, the three find matches in completely different ways, and that difference in approach is exactly what tells them apart. How the three nodes route requests All three join nodes are internal nodes with two children. One child is called the outer, the other the inner. All three run on the Volcano model's pull framework: when the parent asks for the next row, the join node takes rows from its two children, builds one matched row, and sends it up. The only difference is the order and manner in which it routes pull requests to its two children. NestLoop pulls the inner from the start all over again for each outer row it receives. HashJoin slurps the inner in one pass to build an index in memory, then takes outer rows one at a time and probes that index. MergeJoin, on the assumption that both sides are sorted in the same order, advances both sides one step at a time in lockstep. NestLoop: rescan the inner for every outer row The simplest method is NestLoop. As the name says, the loops are nested. The outer loop takes one row from the outer; the inner loop scans the inner from beginning to end, looking for inner rows that match that outer row. Wh

By the time 1.4 ends, the planner has produced one PlannedStmt. Inside it is an execution tree built from Plan nodes, frozen into a form you can follow step by step, something like "go into the primary key index on users, fetch the one matching row, then output that whole row." But that is still only a blueprint. Reading actual pages off disk, picking out the rows that match the condition, handing results back to the caller: none of that has happened yet. The stage that takes that blueprint and produces actual rows is the executor. The difference between the planner and the executor is the difference between deciding and doing. The planner was the stage that weighed "which index, in what order, with what join method" by cost and chose . The executor takes the chosen approach and carries it out as is . There is nothing left to choose. It just runs the nodes baked into the plan tree and pulls rows out of them. To run it, the executor takes the Plan tree it received and turns it into a PlanState tree. The Plan tree is the static blueprint the planner made, and it does not change during execution. But to actually run, each node needs state that changes as execution proceeds: which row it is reading now, whether the hash table is fully built, what tuple it has buffered from a child. So when execution begins, a PlanState tree with the exact same shape as the Plan tree is created. The blueprint Plan tree is left untouched, and the running state lives in that PlanState tree instead. How the executor produces result rows is the heart of the stage. The executor does not build the entire result set at once and stack it up. Instead, it asks the topmost node of the tree for "the next row," and that request travels down the tree to the leaves. When a leaf scan node reads one row from a page and passes it up to its parent, that row climbs up one level at a time through joins and filters until it reaches the top. The top sends that single row to the caller (the client, or the targe

Everything from 1.4.1 through 1.4.9 happened inside a single function, standard_planner() . Building paths, costing them, searching for a join order, estimating cardinality from statistics: all of it runs inside that one function. Yet PostgreSQL does not call standard_planner() directly. It puts another function, planner() , one step ahead of it, and has planner() call standard_planner() . And planner() can be made to call some other function instead of standard_planner() . That replacement is what the planner hook enables. When pg_stat_statements measures per-query planning time, or pg_hint_plan rewrites a plan according to hints, it all goes through this hook. Let's look at how PostgreSQL provides a way to observe or change planning behavior without touching a single line of the core, and how external code plugs into it. All planner() does is check the hook The body of planner() is essentially this. if ( planner_hook ) result = ( * planner_hook ) ( parse , query_string , cursorOptions , boundParams ); else result = standard_planner ( parse , query_string , cursorOptions , boundParams ); planner_hook is a global function pointer. Its default value is NULL , in which case standard_planner() is called right away. A plain PostgreSQL build always takes this path: planner_hook is empty, so the incoming query goes straight to standard_planner() . The key here is the type of planner_hook . typedef PlannedStmt * ( * planner_hook_type ) ( Query * parse , const char * query_string , int cursorOptions , ParamListInfo boundParams ); This signature is identical, down to the character, to that of planner() and standard_planner() . It takes the same Query and returns the same PlannedStmt (the execution plan). So external code only has to write a planner function matching this type and store its address in planner_hook . Let's call this function, written by external code to register in planner_hook , a custom planner function. The moment its address is stored, every planning reque

In the earlier posts of this series, we looked at practical query tuning tips and how to read and interpret query plans . A recurring theme in both was: "add an index here." But "add an index" is a bit like saying "use the right tool" — the interesting part is which one. PostgreSQL ships with several index types, each tuned for a different kind of data and query. Picking the wrong one means PostgreSQL quietly ignores your index and goes back to a sequential scan. In this post, we'll walk through the main index types, when each shines, and the special index variations (composite, partial, covering, expression) that often matter more than the type itself. Setting the Scene: Schema and Sample Data We'll reuse the same schema from the previous posts, with one small addition — a metadata JSONB column and a tags array on orders , so we can explore the more exotic index types. CREATE TABLE customers ( id SERIAL PRIMARY KEY , customer_name VARCHAR ( 255 ), email VARCHAR ( 255 ), created_at TIMESTAMPTZ DEFAULT NOW () ); CREATE TABLE orders ( id SERIAL PRIMARY KEY , customer_id INT REFERENCES customers ( id ), order_date TIMESTAMPTZ DEFAULT NOW (), total_amount NUMERIC ( 10 , 2 ), status VARCHAR ( 20 ), tags TEXT [], metadata JSONB ); -- Insert sample customers INSERT INTO customers ( customer_name , email ) SELECT 'Customer ' || i , 'customer' || i || '@example.com' FROM generate_series ( 1 , 1000000 ) AS s ( i ); -- Insert sample orders INSERT INTO orders ( customer_id , order_date , total_amount , status , tags , metadata ) SELECT ( RANDOM () * 1000000 ):: INT , NOW () - interval '1 day' * ( RANDOM () * 365 ):: int , ( RANDOM () * 500 + 20 ), ( ARRAY [ 'pending' , 'shipped' , 'delivered' , 'cancelled' ])[ FLOOR ( RANDOM () * 4 + 1 )], ARRAY [( ARRAY [ 'gift' , 'priority' , 'fragile' , 'bulk' ])[ FLOOR ( RANDOM () * 4 + 1 )]], jsonb_build_object ( 'channel' , ( ARRAY [ 'web' , 'mobile' , 'store' ])[ FLOOR ( RANDOM () * 3 + 1 )]) FROM generate_series ( 1 , 1000000 ) AS s ( i

pgrac is an open attempt to rebuild Oracle RAC's core machinery (shared-everything storage, multiple active nodes all writing one database, a cluster-wide change number) on top of PostgreSQL 16. Build log #1 laid out the four problems that fight back. This one is about the problem that turns a node failure into silent data corruption, and the first, deliberately modest, layer pgrac ships against it. The failure mode In a shared-nothing cluster an evicted node is mostly harmless: it owns its own disks, so the cluster routes around it. In a shared-everything cluster the same event is dangerous, because every node writes the same storage. Picture the classic split: node 2 misses heartbeats, the cluster declares it dead and remasters its work elsewhere, but node 2 is not actually dead. It is frozen on a long GC pause, or its interconnect NIC flaked, and it is about to wake up and finish the write it started. Now two nodes believe they own the same blocks, and shared storage will accept both writes. That is not a crash. It is corruption you find three days later. Oracle RAC's answer is I/O fencing: before remastering a dead node's resources, you make certain it can no longer touch the storage, with STONITH, SCSI-3 persistent reservations, or a hardware watchdog. The node is fenced at a layer below its own software, because the whole point is that you cannot trust the dead node's software to behave. That hardware layer is real work, and it is not what pgrac built first. What it built first is the layer above it: an in-process cooperative write-fence, now default-ON. The rest of this is precise about what that does and does not buy you, because "we have fencing" is the kind of claim that is worth less than nothing if it is overstated. A fence needs an authority everyone can agree on You cannot fence on local opinion, because the whole problem is that the dead node disagrees about being dead. Authority has to live on durable, shared, quorum-backed storage. pgrac writes a sm

PostgreSQL Error 22036: non numeric sql json item PostgreSQL error code 22036 ( non numeric sql json item ) occurs when a SQL/JSON path expression attempts to perform a numeric operation on a JSON item that is not a number — such as a string, boolean, array, or object. This error was introduced alongside the SQL/JSON Path feature in PostgreSQL 12 and typically surfaces in queries using jsonb_path_query , jsonb_path_exists , or the @@ and @? operators. Top 3 Causes 1. Numeric Values Stored as Strings The most common cause is JSON data where numbers are stored as quoted strings (e.g., "price": "100" instead of "price": 100 ). This frequently happens with data from external APIs or legacy systems that don't enforce type consistency. -- Triggers 22036: "price" is a string, not a number SELECT jsonb_path_query ( '{"price": "100"}' , '$.price + 50' ); -- ERROR: non numeric SQL/JSON item -- Fix: Use the .double() conversion method in JSON Path SELECT jsonb_path_query ( '{"price": "100"}' , '$.price.double() + 50' ); -- Result: 150 -- Alternative fix: Cast at the SQL level SELECT ( data ->> 'price' ):: numeric + 50 FROM ( SELECT '{"price": "100"}' :: jsonb AS data ) t ; -- Result: 150 2. Arithmetic Applied Directly to Arrays or Objects Developers sometimes write JSON Path expressions that target an entire array or object instead of individual elements, then attempt arithmetic on the result. -- Triggers 22036: $.scores returns an array, not a number SELECT jsonb_path_query ( '{"scores": [80, 90, 70]}' , '$.scores + 10' ); -- ERROR: non numeric SQL/JSON item -- Fix: Target a specific array index SELECT jsonb_path_query ( '{"scores": [80, 90, 70]}' , '$.scores[0] + 10' ); -- Result: 90 -- Fix: Use wildcard to apply operation to each element SELECT jsonb_path_query ( '{"scores": [80, 90, 70]}' , '$.scores[*] + 10' ); -- Results: 90, 100, 80 -- Fix: Use unnest for aggregation use cases SELECT elem :: numeric + 10 FROM jsonb_array_elements ( '{"scores": [80, 90, 70]}' :: jsonb ->

Here's the thing nobody warns you about when you put Supabase behind a "real" backend. My stack is React + FastAPI + Supabase Postgres. Every write goes through FastAPI. Every endpoint checks the user, the role, the ownership. I audited that backend HARD — rate limits, JWT validation, RLS, the whole thing. I was proud of it. And none of it mattered for the two holes I actually shipped. Because the Supabase anon key lives in the browser. It HAS to — that's how supabase-js talks to your project. Which means every logged-in user is holding a key that talks to Postgres directly . Not through my FastAPI. Around it. That anon key is a SECOND API. And I'd spent months hardening the first one while the second one sat there, wide open, the whole time. Hole #1 — the answers were just... readable Quiz questions live in quiz_options , one is_correct boolean per option. My backend never sends is_correct to a student before they submit. Obviously. But the browser doesn't have to ask my backend. // any logged-in student, straight from the console: const { data } = await supabase . from ( ' quiz_options ' ) . select ( ' question_id, label, is_correct ' ) // <- the answer key. all of it. The RLS policy said "authenticated users can read quiz_options ." Totally true for the rows. It just also handed back the column that decides the grade. The answer key. To anyone with a login and ten seconds of curiosity. Fix: column-level REVOKE SELECT from the client role, and let the backend be the only thing that ever reads is_correct . (PR #775.) Hole #2 — they could WRITE things they shouldn't Same class of bug, bigger blast radius. The default Postgres grants let the client role insert/update far more than I'd realized — including a path toward forging a certificate. Nobody did it. But "nobody did it yet" is not a security model! So I stopped patching table by table and flipped the whole thing: -- kill the client's entire write surface, then grant back the ONE thing it needs ALTER DEFAULT PRI

PostgreSQL 19 Beta has been announced, with general availability expected in September, following the project's yearly major-release cadence. This release introduces native SQL Property Graph Queries (SQL/PGQ), concurrent table repacking to reclaim storage without downtime, and a broad set of performance, observability, and administration improvements. By Renato Losio

A CloudNativePG cluster that sits in Setting up primary forever, with zero error events on the Cluster resource and a perfectly healthy operator, is one of the more frustrating ways to spend an afternoon. The operator says it's working. The pods never appear. And the actual cause has nothing to do with the database at all. Running stateful databases on Kubernetes used to be the thing everyone told you not to do. CloudNativePG (CNPG) changed that calculus for a lot of people, including me. It's a proper operator: it handles failover, backups, connection routing, and rolling upgrades through native Kubernetes primitives instead of bolting Postgres onto a StatefulSet and praying. If you run a hardened cluster with admission controllers, network policies, and least-privilege RBAC, this post is about the friction you'll hit that the quickstart never mentions. Who should care If your cluster is vanilla, kubectl apply the operator and a Cluster manifest, and you're done in ten minutes. The CNPG docs are genuinely good for that path. This is for the rest of us: people running Kyverno or OPA Gatekeeper, self-signed cert chains, and the kind of policy-as-code setup where every workload has to justify its existence. That's where CNPG stops being a ten-minute install and starts being an integration project. What I tried first The first instinct, when a CNPG cluster hangs, is to assume you got the database config wrong. So you go read your Cluster manifest line by line. You check the storage class. You check that the PVC bound. You bump the operator log level and watch it cheerfully report that it's reconciling, over and over, with no complaints. Here's the trap: the CNPG operator doesn't run initdb itself. It creates a Kubernetes Job to bootstrap the primary. That Job spawns a Pod. And in a hardened cluster, the Pod is where everything dies, because your admission controller is judging it against policies the operator's own Pods were exempted from but the bootstrap Job was not.

Cómo un backend de FastAPI + asyncpg comparte un solo Postgres chiquito con sus propios trabajadores en segundo plano y un segundo servicio, por qué el techo de conexiones, no el CPU, es lo que de verdad le pone tope a nuestro autoescalado, la caída en el cambio de hora que nos enseñó la cuenta, y una mirada honesta a RDS Proxy como la válvula de escape (incluyendo la trampa de asyncpg que lo puede dejar sin hacer nada). TL;DR Ajuste Valor Por qué Driver postgresql+asyncpg asíncrono hasta el fondo pool_size / max_overflow 8 / 12 (20 por proceso) subido desde 3/5 después de una caída pool_pre_ping True mata los sockets muertos tras un reinicio de RDS / inactividad pool_recycle 1800 s techo duro que el pre-ping no puede cubrir Tope de conexiones de RDS ~87 (t3.micro, menos las reservadas) la verdadera restricción de todo Pruebas NullPool sin pooling entre event loops en pytest La lección que replanteó todo el problema: en un RDS chico, tu cuenta máxima de tareas la fija max_connections , no el CPU ni la memoria. Un autoescalado que ignora el presupuesto de conexiones va a escalar directito hacia QueuePool limit reached , o peor, FATAL: too many connections del mismo Postgres. El pool, y la cuenta escondida adentro de él engine = create_async_engine ( settings . DATABASE_URL , connect_args = { " ssl " : " prefer " }, # negocia TLS en RDS, texto plano en local pool_pre_ping = True , pool_size = 8 , max_overflow = 12 , # 8 + 12 = 20 conexiones por proceso pool_recycle = 1800 , ) Veinte conexiones por proceso se ven modestas hasta que las multiplicas por cada capa entre ellas y la base de datos: pool_size + max_overflow = 20 por proceso de Python × 2 trabajadores de uvicorn = 40 por tarea de Fargate × 2 durante un despliegue rolling = 80 (tarea vieja drenando + tarea nueva arrancando) + servicio de inteligencia (3 + 7) = 10 sobre la misma base de datos + alembic / ad-hoc / psql ≈ unas pocas ---------------------------------------- ≈ 87 ← el techo de la t3.micro, con ~0 de

Quorum is an incident command plane built on Amazon Aurora DSQL. The failover story lives in another post. This one is about a narrower question that turned out to be the foundation: when several responders write to the same incident at the same moment, across regions, during the worst minutes of an outage, how do you guarantee the record never forks into two conflicting truths. The answer is two design choices that are really one choice seen from two angles: event sourcing, and DSQL's optimistic concurrency control. The data model is append-only Quorum is event-sourced across four tables. Every state change is an immutable event appended to a log, not an in-place update. The current state of an incident is a fold over its events. There is no UPDATE incidents SET status = ... ; there is an acknowledged event, a note event, a resolved event, and the status you render is computed from them. The event's UUID is its primary key and its idempotency key at the same time. A retried write carrying the same UUID cannot double-apply: the insert collides on the primary key and becomes a no-op. That property sounds minor until you remember what kind of system this is. A tool designed to survive network failure retries writes constantly, and "the responder tapped resolve twice because the first response was slow" must not produce two resolutions. Append-only also suits the domain directly. For an incident system the audit trail is the product, not a side effect. "Who acknowledged this, at what time, and what did the timeline look like at 02:14" is a first-class question for the post-incident review and a compliance requirement in regulated environments. Event sourcing gives you that for free. It also gives DSQL a write pattern it likes, which matters more than you would expect. The stack, briefly TypeScript end to end. Kysely as a typed query builder rather than an ORM, because I wanted type safety without surrendering control of the SQL: on a distributed database the exact shap

MVCC, WAL, vacuum, and replication slots under sustained delete load - and how to delete billions of rows without your database noticing Most "how to delete a lot of rows" articles stop at "batch it and delete children before parents." That advice is correct, it's table stakes, and everyone already knows it. This article is about everything after that - the parts that actually decide whether your cleanup runs quietly in the background for a week or pages you at 3 a.m. with a full disk and a replica that's six hours behind. The thesis: at scale, your DELETE statement is the easy part. The adversaries are the subsystems a delete feeds - MVCC tuple versioning, the write-ahead log, autovacuum, and the replication machinery. Bulk deletion is really an exercise in flow control across those subsystems . Get the SQL right and the systems wrong, and you'll still take production down. We'll assume PostgreSQL (the internals are PG-specific), a live OLTP primary with at least one physical replica and one or more logical/CDC consumers, and a target of hundreds of millions to billions of rows across many related tables. The one paragraph of "basics," so we can move on: delete in dependency order (referencing rows before referenced rows); collect parent keys once; never rely on ON DELETE CASCADE for huge deletes because you can't throttle a cascade. Done. Now the real material. 1. What a DELETE actually costs A delete is not "remove a row." Under MVCC it's "mark a row version dead and write that fact everywhere." For each deleted tuple, PostgreSQL: Sets xmax on the heap tuple to your transaction id. The row is still physically present; it becomes a dead tuple once your transaction commits and no snapshot can still see it. Writes a WAL record for the heap change. If this is the first modification of that page since the last checkpoint, it also writes a full-page image (FPI) - potentially 8 KB of WAL for a single-row change. Touches every index. Index entries aren't removed at delet

PostgreSQL Error 22P01: Floating Point Exception PostgreSQL error code 22P01 is raised when a floating-point operation produces an exceptional result that cannot be represented as a valid number. This typically occurs during division by zero on float types, operations involving NaN (Not a Number), or arithmetic that yields Infinity . It is most commonly encountered in analytics, financial calculations, and data pipelines processing external or sensor data. Top 3 Causes 1. Division by Zero on Float Types Unlike integer division (which raises 22012 ), dividing a float by zero triggers a floating-point exception. This is especially common in ratio and rate calculations where the denominator can become zero at runtime. -- Problematic query SELECT total_sales :: float / total_orders :: float AS avg_order_value FROM daily_stats ; -- Safe fix using NULLIF SELECT date , total_sales :: float / NULLIF ( total_orders , 0 ):: float AS avg_order_value FROM daily_stats ; 2. NaN Values in Arithmetic Operations Data ingested from external systems, CSVs, or APIs may silently introduce NaN values into float columns. Once NaN participates in arithmetic, results become unpredictable and can trigger exceptions downstream. -- Detect NaN values (NaN is the only value not equal to itself) SELECT id , value FROM sensor_readings WHERE value != value ; -- Replace NaN with NULL safely UPDATE sensor_readings SET value = NULL WHERE value != value ; -- Filter NaN in aggregations SELECT device_id , AVG ( value ) FILTER ( WHERE value = value ) AS clean_avg FROM sensor_readings GROUP BY device_id ; 3. Infinity Arithmetic Conflicts Storing 'Infinity'::float or '-Infinity'::float is valid in PostgreSQL, but performing certain operations on them produces mathematically undefined results (e.g., Infinity - Infinity = NaN ), which can cascade into a floating-point exception. -- Check for Infinity values SELECT id , measurement FROM raw_data WHERE measurement IN ( 'Infinity' :: float , '-Infinity' :: float

When people say they are "adding RAG" to a workflow, the conversation often jumps too quickly to infrastructure choices. Should this use a vector database? Should there be a reranker? Should everything go into a knowledge graph? Those are valid questions, but they are usually not the first question. The first question is narrower: What approved knowledge should the workflow be allowed to retrieve before an AI decision happens? That is why my first retrieval layer for operational AI workflows starts in Postgres, not in a standalone vector database. The Workflow Problem In operations-heavy systems, the model usually should not answer from raw memory or from a giant prompt dump. The useful context already exists somewhere else: approved response rules; handoff criteria; product or service notes; source or campaign guidance; operational decisions that were already made by humans. The hard part is not generating fluent text. The hard part is retrieving the right approved context, showing which source influenced the decision and refusing when no safe source exists. Why Postgres First For this kind of workflow, most of the surrounding data is already relational: leads or conversations; workflow names; stages and owners; human review outcomes; source metadata; trace logs; document versions. So the first technical choice is not "where do vectors live in the abstract?" It is: Where can I keep retrieval close to the operational data model? Where can I log the retrieval path and the final decision together? Where can I evolve the schema without creating a second system too early? Postgres plus pgvector is a good first answer to that set of questions. It lets me keep: documents and chunks; metadata such as allowed use and approval requirements; retrieval traces; cost estimates; human review outcomes in one place. What The First Version Needs The first version does not need to be broad. It needs to be inspectable. My narrow retrieval scope looks like this: approved response rules