标签：#Postgres

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

Recently open-sourced by Microsoft, pg_durable is a PostgreSQL extension that enables durable workflows to run natively inside the database, eliminating the need for external orchestration systems. By Sergio De Simone

PostgreSQL Error 2201X: invalid row count in result offset clause PostgreSQL error code 2201X ( invalid_row_count_in_result_offset_clause ) is thrown when the value provided to an OFFSET clause is not a valid non-negative integer. This commonly surfaces in applications that implement pagination using dynamic queries or user-supplied parameters, where the offset value may be negative, NULL, or a non-integer type. Top 3 Causes 1. Negative OFFSET Value The most frequent cause is a miscalculated page offset. When computing (page - 1) * page_size , a bad page number can produce a negative result. -- Triggers error 2201X SELECT * FROM orders ORDER BY created_at DESC OFFSET - 5 LIMIT 20 ; -- ERROR: invalid row count in result offset clause -- Fix: Use GREATEST to clamp the value to 0 SELECT * FROM orders ORDER BY created_at DESC OFFSET GREATEST ( 0 , - 5 ) LIMIT 20 ; 2. NULL Passed as OFFSET When an application fails to initialize or bind the offset parameter, NULL gets passed to the query. This often happens with ORMs or query builders where optional parameters are not explicitly set. -- Triggers error 2201X SELECT * FROM products ORDER BY id OFFSET NULL LIMIT 10 ; -- ERROR: invalid row count in result offset clause -- Fix: Use COALESCE to provide a default value SELECT * FROM products ORDER BY id OFFSET COALESCE ( NULL , 0 ) LIMIT 10 ; -- Parameterized query with safe fallback SELECT * FROM products ORDER BY id OFFSET COALESCE ( $ 1 :: BIGINT , 0 ) LIMIT COALESCE ( $ 2 :: BIGINT , 10 ); 3. Non-Integer Type (Float or String) Passing a float or a string that cannot be cleanly cast to a whole number causes this error. This typically happens when Python float values or unvalidated user input strings are interpolated directly into a query. -- Triggers error 2201X SELECT * FROM employees ORDER BY last_name OFFSET 10 . 7 LIMIT 5 ; -- ERROR: invalid row count in result offset clause -- Fix: Use FLOOR and explicit cast to BIGINT SELECT * FROM employees ORDER BY last_name OFFSET F

Short answer first: use Supabase if you want Postgres plus auth, realtime, storage, and a dashboard as one managed bundle. Self-host Postgres – or use a managed Postgres – if you mostly need a database and your app already handles its own auth and logic. The choice is not really "Postgres vs Supabase". It's whether you need the extra layers Supabase puts on top of Postgres. Supabase is not a database Supabase runs on PostgreSQL, but it's a stack of services around it: Postgres – the actual database Auth – user signup, login, JWT tokens Realtime – live updates over websockets Storage – an S3-style file store Edge Functions – serverless functions Studio – dashboard + auto-generated REST/GraphQL API So "self-host Postgres or use Supabase" compares a plain database to a full backend. The honest question: do you need those extra layers, or just the database underneath them? A quick test: You use Supabase Auth, Storage, and Realtime → Supabase earns its place. You use one of them → it's replaceable. You use none and treat it as "Postgres with a nice dashboard" → you want plain Postgres. Side-by-side comparison Factor Supabase (managed) Self-hosted Supabase Plain Postgres (managed or self-hosted) Database engine PostgreSQL PostgreSQL PostgreSQL Built-in auth Yes Yes No (bring your own) Realtime / websockets Yes Yes No File storage Yes Yes No Dashboard + auto API Yes Yes No (use any SQL client) Backups Managed (limits by plan) You manage Managed or you manage Cost shape Metered, grows with usage Server cost + your time Database only Self-host effort None High (many containers) Low–medium Lock-in Medium–high Medium Very low The lock-in point decides it for many teams. Your data is standard Postgres in every option ( pg_dump portable). The lock-in is everything else: Auth tokens, Storage paths, Supabase-specific RLS policies, Edge Function code. The more Supabase-specific features you adopt, the harder the exit. When each option wins Pick managed Supabase when: You're startin

PostgreSQL Error 2200D: invalid escape octet The 2200D: invalid escape octet error occurs in PostgreSQL when a bytea value contains an invalid escape sequence. This typically happens with the legacy escape format for binary data, where octet values must be represented as three-digit octal numbers in the range \000 to \377 . If the escape sequence falls outside this range or uses non-octal digits, PostgreSQL raises this error immediately. Top 3 Causes 1. Out-of-range octal values in bytea escape literals The bytea escape format only accepts octal values from \000 to \377 (decimal 0–255). Using values like \400 or non-octal digits like \9 will trigger this error. -- BAD: \400 exceeds valid octal range (max is \377) SELECT E ' \\ 400' :: bytea ; -- ERROR: invalid escape octet -- BAD: \9 is not a valid octal digit SELECT E ' \\ 9AB' :: bytea ; -- ERROR: invalid escape octet -- GOOD: valid octal escape sequences SELECT E ' \\ 377' :: bytea ; -- decimal 255 SELECT E ' \\ 101' :: bytea ; -- 'A' character SELECT E ' \\ 000' :: bytea ; -- null byte 2. Using escape format strings with hex output format Since PostgreSQL 9.0, the default bytea_output is hex . Applications that mix hex -format output back into escape -format input processing can generate malformed escape sequences. -- Check current bytea output format SHOW bytea_output ; -- GOOD: Use hex format (recommended for all new projects) SELECT ' \x DEADBEEF' :: bytea ; SELECT ' \x 48656C6C6F' :: bytea ; -- 'Hello' -- GOOD: Use encode/decode for safe conversions SELECT encode ( ' \x DEADBEEF' :: bytea , 'hex' ); -- output as hex string SELECT encode ( ' \x DEADBEEF' :: bytea , 'base64' ); -- output as base64 SELECT decode ( 'deadbeef' , 'hex' ); -- hex string → bytea SELECT decode ( 'SGVsbG8=' , 'base64' ); -- base64 → bytea 3. Incorrect escaping during data migration or manual SQL When migrating binary data from other databases (Oracle, MySQL) or writing raw INSERT statements manually, developers often confuse octal and

Last year, I examined RUM indexes within this series on multi-key indexing, demonstrating that they cannot substitute MongoDB's compound indexes for sorted queries. A year later, Microsoft has fixed this in the DocumentDB extension for PostgreSQL with an Extended RUM index that preserves the ordering of the keys, allowing an ordered scan rather than a bitmap scan. Let's revisit our pagination query to see how it performs now. I start a container with the latest DocumentDB (version v0.112-0 from May 26, 2026): docker run -d --name documentdb-local -p 10260:10260 -p 9712:9712 ghcr.io/documentdb/documentdb/documentdb-local:latest --username franck --password franck --start-pg I can connect to PostgreSQL on port 9712, where many extensions are installed, including the extended RUM index: docker exec -it documentdb-local psql -p 9712 postgres psql ( 17.10 ( Debian 17.10-1.pgdg13+1 )) Type "help" for help. postgres = # \dx List of installed extensions Name | Version | Schema | Description ------------------------- +---------+------------+------------------------------------------------------------ documentdb | 0.112-0 | public | API surface for DocumentDB for PostgreSQL documentdb_core | 0.112-0 | public | Core API surface for DocumentDB on PostgreSQL documentdb_extended_rum | 0.112-0 | public | DocumentDB Extended RUM index access method pg_cron | 1.6 | pg_catalog | Job scheduler for PostgreSQL plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language postgis | 3.6.3 | public | PostGIS geometry and geography spatial types and functions tsm_system_rows | 1.0 | public | TABLESAMPLE method which accepts number of rows as a limit vector | 0.8.2 | public | vector data type and ivfflat and hnsw access methods ( 8 rows ) postgres = # I can also connect to the MongoDB-compatible API: docker exec -it documentdb-local mongosh -u franck -p franck 'mongodb://localhost:10260/?tls=true&tlsAllowInvalidCertificates=true' Current Mongosh Log ID: 6a0b3b537d2a1c3471d1a7ba Connecting to: mo

AWS recently announced ExtendDB, a DynamoDB-compatible adapter that lets developers use the DynamoDB API with different storage backends, starting with PostgreSQL. The project supports existing SDKs and tools without modification, giving teams greater flexibility to run DynamoDB-style workloads outside of native DynamoDB while maintaining compatibility with current applications and workflows. By Renato Losio

APScheduler's Advisory Lock Failure: My Solo VM's Scheduler Died Permanently It started with a user report: "Content engine auto-publishing should put 3 posts on dev.to, but only 2 appeared, and then nothing worked." This is the kind of subtle bug that can fester, but the reality was far more systemic. My entire APScheduler setup had died. Not just for dev.to, but for *all* my scheduled tasks: content engine sweeps, daily top 3 analysis, profile analysis, model health checks, weekly reports – everything. The cron logs showed nothing for three days straight. This wasn't just a hiccup; it was a full-blown scheduler apocalypse on my single small VM. The immediate symptom was a lack of new posts on dev.to, but the root cause was a complete, permanent scheduler failure. The Wrong Turn: Relying on PostgreSQL Advisory Locks for Leader Election My approach to ensuring only one instance of my worker process ran scheduled jobs involved using PostgreSQL's pg_try_advisory_lock . The idea was that each worker would try to acquire this advisory lock. The one that succeeded would be the leader, responsible for running the jobs. Other workers would see the lock is held and stand down. However, in my specific environment – direct PostgreSQL connection (localhost:5432) without a connection pooler like pgbouncer, using asyncpg for dedicated connections – this mechanism proved fatally flawed. The lock was acquired, but immediately released. The worker thought it held the lock ( active=True ), but a check of pg_locks showed zero holders. This meant the singleton pattern was broken. Worse, the self-healing mechanism relied on the same flawed lock acquisition, meaning it couldn't recover. The situation was so unstable that I even observed a period where both my blue and green services (running on ports 8000 and 8001 respectively) thought they were the leader, resulting in a double execution of jobs. This was a clear sign the leader election was fundamentally broken. The Root Cause: Sessio

The LedgerSync pipeline was inserting 1.5 million rows into PostgreSQL using pandas.to_sql() . It took four minutes per run. I switched to psycopg2's COPY command and it dropped to 18 seconds. Same data, same schema, same machine. That is not an optimization tip. It is the difference between a pipeline that fits in an Airflow schedule and one that does not. This article is about patterns like that: the ones that matter when you are building pipelines that run on a schedule, not when you are writing ad-hoc queries. Loading Data: to_sql vs execute_values vs COPY There are three ways to write rows from Python into PostgreSQL, and the performance gap between them is significant. pandas to_sql issues one INSERT statement per row by default, or a multi-row INSERT with method="multi" . It is the easiest to write and the slowest for any serious volume. psycopg2 execute_values batches many rows into a single multi-row INSERT VALUES statement. About 5x faster than to_sql for medium-sized loads. psycopg2 COPY streams rows directly to PostgreSQL using its native bulk-load protocol. No statement parsing, no row-by-row overhead. For LedgerSync at 1.5M rows, this was the one that mattered. import psycopg2 import io import pandas as pd conn = psycopg2 . connect ( " host=localhost dbname=proj_db user=proj_user password=proj_pass " ) def bulk_copy ( df : pd . DataFrame , table : str , columns : list [ str ]): buf = io . StringIO () df [ columns ]. to_csv ( buf , index = False , header = False ) buf . seek ( 0 ) with conn . cursor () as cur : cur . copy_from ( buf , table , sep = " , " , columns = columns ) conn . commit () print ( f " Loaded { len ( df ) } rows into { table } " ) Use COPY for initial loads and large backfills. For incremental daily writes of a few thousand rows, execute_values is fine and gives you more control over conflict handling: from psycopg2.extras import execute_values def bulk_insert ( rows : list [ dict ], table : str ): if not rows : return columns = list

At Twio we picked pg-boss for our job queue, ran into trouble when we went serverless, looked at Pub/Sub, and ended up on Google Cloud Tasks. This is what each queue got right, what it got wrong for our workload, and the rule we landed on for choosing between them. The workload Twio is an AI SaaS for loan brokers. The piece that needs a job queue is email processing: download an email, parse the body and attachments, OCR, classify with an LLM, write structured data, and index for RAG. One email with five attachments easily becomes 30+ background jobs. A batch upload becomes hundreds. Why pg-boss worked — until it didn't Our database was Postgres on Neon, so pg-boss was the obvious starting point. No extra infrastructure, and one feature we genuinely loved: transactional enqueue . Because jobs live in the same database as business data, you can create a job in the same transaction as the row that triggered it. No dual-write problem, no "DB succeeded but the queue API failed" inconsistency. It also gave us retries, delayed jobs, dead-letter queues, dedup keys, and full SQL visibility into stuck or failed jobs. For a Postgres-first app on always-on infra, it's an excellent tool. Then we moved heavy processing to Cloud Run, and the cracks showed up. pg-boss polls. Neon suspends. They want opposite things. pg-boss runs a query roughly every 1–2 seconds to look for the next job, plus maintenance queries. Neon autosuspends compute when nothing touches the database. If the queue is polling every second, Neon's idle timer never expires — you pay for always-on compute even when the queue is empty. Worse, when Neon did manage to suspend, the next poll had to wake it. That wake-up takes hundreds of ms to a few seconds, and queries that triggered it would fail with Connection terminated , ECONNRESET , or timeouts. Pooled connections made it worse: the pool kept sockets that the server had already closed during suspend, and the next polling cycle picked one up and broke. This isn

PostgreSQL LISTEN/NOTIFY for Real-Time Multi-Tenant Events: Ditching Polling and WebSocket Complexity I've shipped real-time features in CitizenApp using three different approaches: naive polling (embarrassing), Redis pub/sub (overkill), and now PostgreSQL's native LISTEN/NOTIFY. The third option is what I should have started with. Most teams reach for Redis or RabbitMQ the moment they need real-time updates. It's the conventional wisdom. But here's the truth: if you're already running PostgreSQL, you have a battle-tested pub/sub system sitting right there. It handles multi-tenancy correctly, scales to thousands of concurrent connections, and eliminates an entire infrastructure dependency—which matters when you're deploying to Render or Vercel where every added service is friction. Why LISTEN/NOTIFY beats the alternatives Polling is dead. HTTP requests every 2-5 seconds for "new notifications"? That's technical debt masquerading as simplicity. It wastes bandwidth, kills your database with unnecessary queries, and users see stale data. Redis is powerful but expensive. Not just in dollars—in operational overhead. You need to manage connection pools, handle failover, monitor memory usage, and keep another service running in production. At CitizenApp's scale (thousands of concurrent tenants), we were paying $50/month for Redis on top of Render just to broadcast notifications that PostgreSQL could handle natively. WebSockets without a broker are a nightmare. If you're running multiple FastAPI workers (and you should be), a WebSocket connection to Worker A doesn't know about events published by Worker B. You need a message broker to fan-out events across processes. Unless you use PostgreSQL LISTEN/NOTIFY, which handles that automatically. PostgreSQL's pub/sub is: Transactional. Notifications only fire after a transaction commits. Tenant-aware. Use channel names like tenant_123_notifications and broadcast only to the right subscribers. Zero extra infrastructure. It's part