标签：#Postgres

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

Let's consider a table user . When we execute a query to find Emily , we are actually going through each record , looking whether the name column equals Emily . Indexes comes in when you want to speed this up. Let's create an Index with the name idx_users_name (the name can be anything, and it doesn't matter functionally): CREATE INDEX idx_users_name ON users ( name ); Now when you run SELECT * FROM users WHERE name = 'Emily' ; Postgres will use the index we just created (not by the name, the name is just for us) to execute that query, and the time complexity is reduced from O(n) to O(log n) .

Something is slow. Maybe a page takes forever to load, maybe a migration is hanging, maybe your Supabase dashboard just spins. You suspect a query is stuck somewhere in your database, but you can't see what's happening — Postgres doesn't exactly surface this on its own. Turns out it does. You just need to ask. Seeing what's running Postgres keeps track of every active connection and what it's doing in a system view called pg_stat_activity . You can query it like any table: SELECT pid , state , query , age ( clock_timestamp (), query_start ) AS duration FROM pg_stat_activity WHERE state != 'idle' ORDER BY duration DESC ; That gives you every non-idle process — its process ID, current state, the SQL it's running, and how long it's been at it. If something has been running for minutes when it should take milliseconds, you've found your problem. A few things worth knowing about the columns: pid — the process ID, which you'll need if you want to kill it state — usually active (running right now), idle in transaction (sitting inside an open transaction doing nothing), or idle (waiting for work) query — the actual SQL text query_start — when the current query began If you want to include the user and database to narrow things down: SELECT pid , usename , datname , state , query , age ( clock_timestamp (), query_start ) AS duration FROM pg_stat_activity WHERE state != 'idle' ORDER BY duration DESC ; The dangerous one — idle in transaction An active query that's been running for a while is usually just slow. An idle in transaction connection is a different kind of problem — it means someone (or some code) opened a transaction and never committed or rolled it back. The connection is doing nothing, but it's still holding locks, which can block other queries from running. These are the ones that tend to cause cascading slowdowns. If you see one that's been sitting there for longer than expected, it's almost certainly a bug in application code — a missing COMMIT , an unhandled e

A complete solution: expressive range types in your domain layer, full PostgreSQL translation in your data layer - no compromises at either end The Two-Column Trap Almost every developer has written it at least once. An object with two date properties: public class MemberSubscription { public int Id { get ; set ; } public int MemberId { get ; set ; } public DateTime StartDate { get ; set ; } public DateTime EndDate { get ; set ; } } Imagine you need to answer a seemingly simple question in a booking system: "Is this subscription still active, and does it conflict with the proposed new one?" With two bare fields, that code ends up looking something like this: // With two bare DateTime fields — the check you always end up writing public static bool IsActive ( MemberSubscription sub , DateTime at ) => sub . StartDate <= at && ( sub . EndDate == default || sub . EndDate > at ); public static bool ConflictsWith ( MemberSubscription a , MemberSubscription b ) { // Partial overlap: a starts inside b if ( a . StartDate >= b . StartDate && a . StartDate < b . EndDate ) return true ; // Partial overlap: b starts inside a if ( b . StartDate >= a . StartDate && b . StartDate < a . StartDate ) return true ; // b is fully contained by a if ( a . StartDate <= b . StartDate && a . EndDate >= b . EndDate ) return true ; // What about open-ended subscriptions? What about same-day boundaries? // What about inclusive vs exclusive end dates? ... return false ; } It looks perfectly reasonable. But start asking questions — as Steve Smith (Ardalis) does in his essay on making the implicit explicit — and you notice how much invisible knowledge this design requires. Should EndDate ever precede StartDate ? The type system doesn't say. Can a subscription have a null end date meaning it never expires? Nothing in the model communicates that. Is a subscription that ends today still active at 11:59 PM? Ask three developers and get three answers. The EndDate == default sentinel for open-ended subsc

The problem: counting unique viewers per second is a row explosion A viewer scrubs to 4:12 of a 9-minute trending clip, watches for 40 seconds, jumps back to the intro, then bounces. Multiply that by the few hundred thousand sessions a day that hit a mid-size aggregator and you get the question every product person eventually asks: which parts of this video do people actually watch, and how many distinct people watched each part? The naive answer is a watch_events table: one row per (user, video, second) . It works until it doesn't. A 9-minute video is 540 seconds. One viewer who watches the whole thing generates 540 rows. A million viewers across our catalog generate hundreds of millions of rows per day , and the only query anyone runs against them is COUNT(DISTINCT user_id) GROUP BY second . That COUNT(DISTINCT) is a sort-or-hash over the entire partition every single time someone opens the analytics tab. At TopVideoHub we aggregate trending video across Asia-Pacific, so a single popular clip can spike from zero to half a million sessions in an afternoon when it lands in the JP and KR feeds simultaneously. We did not want a fact table that grew by hundreds of millions of rows a day to answer a question whose answer is approximately fine. "Roughly 41,000 unique viewers saw the hook at 0:08" is just as actionable as "41,287". That tolerance for approximation is exactly what HyperLogLog is built for, and Postgres has a battle-tested extension for it. This post is the design we landed on: fixed-size HLL sketches, one per (video, time_bucket) , that you can merge, slice, and union across regions in milliseconds. The main app is PHP 8.4 on LiteSpeed behind Cloudflare, with our search layer on SQLite FTS5; the analytics store is a separate Postgres instance, and HLL is what made that store affordable. Why HyperLogLog instead of COUNT(DISTINCT) HyperLogLog estimates the cardinality of a set using a fixed amount of memory regardless of how many elements you throw at it. Th

PostgreSQL Partitioning for Multi-Tenant Audit Logs: Querying 100M Events Without Table Scans I'll be direct: if you're running a SaaS with compliance requirements and your audit_logs table is approaching 50M rows, you're three months away from pain. I've watched audit queries go from 200ms to 8 seconds in production at 2am because someone ran a "give me all logs for tenant X" report. Partitioning isn't optimization theater—it's table-stakes infrastructure. At CitizenApp, we store 9 months of audit logs across 50+ tenants. Without partitioning, a single compliance query would full-table scan 100M+ rows. With it, we hit the same data in <100ms. This post is exactly how we do it. Why Partitioning Matters (The Reality Check) Most developers treat audit_logs like any other table. You add an index on tenant_id and created_at , call it done, and move on. Then your compliance officer runs a query like: SELECT * FROM audit_logs WHERE tenant_id = 'acme-corp' AND created_at >= '2024-01-01' ORDER BY created_at DESC ; At 50M rows, even with a composite index, PostgreSQL has to: Index scan → finds millions of matching rows Random I/O all over the table Spill to disk if sorting is large Hope the OS cache is warm Partitioning solves this by eliminating the data you don't need from day one . Instead of scanning a 100GB table and filtering it down, PostgreSQL can skip entire partitions. A query against January 2024 data simply ignores partitions for February–December. I prefer partitioning because it's native PostgreSQL—no external caching layer, no read replicas, no Redis gymnastics. It's boring infrastructure that works. The Partitioning Strategy: Composite Partitioning (Range + List) I use a two-level partitioning scheme: Range partition by month ( created_at ) — keeps each partition to ~5–10GB List subpartition by tenant — ensures compliance queries are single-partition scans This is deliberately opinionated. You could do range-only, but then a multi-tenant query still scans the

PostgreSQL Error 2200G: Most Specific Type Mismatch PostgreSQL error code 2200G ( most_specific_type_mismatch ) is a SQL-standard data exception that occurs when a value's type does not match the most specific (most derived) type expected in a context involving type hierarchies, XML schema types, or user-defined structured types. It most commonly appears when working with composite types, domain hierarchies, or XML processing functions where type inheritance or derivation is in play. While relatively rare in everyday CRUD operations, it can be a significant pain point in enterprise applications with complex type systems. Top 3 Causes and Fixes 1. Composite or Domain Type Hierarchy Mismatch When a function expects a specific domain or composite type but receives a parent/base type, PostgreSQL raises 2200G. Always cast explicitly to the most specific required type. -- Define types CREATE TYPE base_info AS ( name TEXT , value INTEGER ); CREATE DOMAIN specific_info AS base_info ; -- Function expecting the specific domain type CREATE OR REPLACE FUNCTION handle_info ( data specific_info ) RETURNS TEXT AS $$ BEGIN RETURN ( data ). name || ': ' || ( data ). value ; END ; $$ LANGUAGE plpgsql ; -- WRONG: passing base type causes mismatch -- SELECT handle_info(ROW('test', 42)::base_info); -- CORRECT: explicit cast to the most specific type SELECT handle_info ( ROW ( 'test' , 42 ):: specific_info ); 2. XML Type Processing Mismatch Using XML functions like XMLTABLE or XMLCAST without explicitly matching the expected schema type can trigger this error. Always declare column types explicitly. -- Correct: explicitly typed columns in XMLTABLE SELECT * FROM XMLTABLE ( '//product' PASSING XMLPARSE ( DOCUMENT ' <products> <product> <id>1</id> <price>29.99</price> </product> </products> ' ) COLUMNS product_id INTEGER PATH 'id' , price NUMERIC PATH 'price' ); -- Explicit XMLCAST to resolve type ambiguity SELECT XMLCAST ( XMLQUERY ( '//price/text()' PASSING XMLPARSE ( DOCUMENT '<data><pri