标签：#base

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

You type "k8s deployment troubleshooting" into your documentation search. The top result is a page about Kubernetes architecture that never mentions the word "troubleshooting." It is exactly what you need. BM25 would have missed it entirely. This is the promise of vector search: finding documents by meaning, not just matching words. In 2025 and 2026, vector search has moved from niche ML engineering to a core Elasticsearch capability. If you are building search for AI applications - RAG pipelines, semantic Q&A, recommendation systems - understanding how Elasticsearch handles vectors is no longer optional. I have spent the past year building RAG pipelines at Cloudera, and I have learned that vector search is powerful but easy to misuse. This post covers what works, what does not, and how to implement it in production. Why Vector Search Matters (And When It Does Not) BM25, which we covered in a previous post, is brilliant at matching exact terms. But it is fundamentally lexical. It does not understand that: "k8s" and "kubernetes" are the same thing "docker container" and "containerization" are related concepts "out of memory error" and "heap exhaustion" describe the same problem Vector search solves this by converting text into high-dimensional numerical vectors (embeddings) where semantically similar content lives close together in vector space. A query for "k8s deployment troubleshooting" gets embedded into a vector, and Elasticsearch finds the nearest document vectors - even if they do not share a single keyword. But vector search is not a replacement for BM25. It is a complement. BM25 is faster, requires no ML infrastructure, and excels at exact-term matching. Vector search is slower, requires embedding models, and shines at conceptual similarity. The best search systems in 2026 use both. How Elasticsearch Stores and Indexes Vectors Elasticsearch introduced the dense_vector field type in version 7.x and has dramatically improved it through 8.x and into 2026. Here

TL;DR — UUID v7 trie 13× plus vite que v4 en simulation B-tree (1M entrées), expose une empreinte mémoire identique, mais révèle son timestamp d'émission. UUID v4 reste le choix "zéro réflexion" pour les identifiants isolés. Le reste de cet article vous donnera les données pour décider. Introduction Les UUIDs sont omniprésents dans les systèmes modernes : clés primaires de bases de données, identifiants de sessions, tokens de traçabilité. Pourtant, le choix de la version impacte directement les performances en écriture et en lecture, la fragmentation des index, et — dans certains contextes — la confidentialité des données. UUID v4 (RFC 4122, 2005) est aujourd'hui la version par défaut de presque tous les ORM et frameworks. UUID v7 (RFC 9562, 2024) est son successeur moderne, conçu pour corriger son principal défaut : le désordre lexicographique. Dans cet article, nous allons mettre les deux en face avec des benchmarks réels sur des volumes de 100 000 à 10 millions d'UUIDs , analyser leur structure bit par bit, et vous donner une grille de décision claire. Structure interne : ce que contiennent ces 128 bits UUID v4 xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx Bits Contenu 0–47 Aléatoire 48–51 Version (0100 = 4) 52–63 Aléatoire 64–65 Variant (10) 66–127 Aléatoire 122 bits d'entropie pure. Aucune information temporelle. Chaque UUID est statistiquement indépendant des autres. UUID v7 019ed5c8-2a2f-7974-91f2-6ba1f313dcfa └──────────────┘ 48 bits = timestamp Unix en millisecondes Bits Contenu 0–47 Timestamp Unix (ms) 48–51 Version (0111 = 7) 52–63 Aléatoire (sub-ms ou compteur) 64–65 Variant (10) 66–127 Aléatoire 74 bits d'entropie + 48 bits de temps. Naturellement monotone : deux UUIDs générés dans la même milliseconde sont toujours distincts et ordonnés de façon cohérente. Lecture du timestamp (Python) : import uuid6 u = uuid6 . uuid7 () b = u . bytes ts_ms = int . from_bytes ( b [: 6 ], ' big ' ) # → 1781703125553 (ms depuis epoch Unix) Depuis la sortie de nos benchmarks : [00

Time-series data is one of the most common types of data generated by modern applications. Every log entry, API request, metric, transaction, sensor reading, or user interaction is recorded with a timestamp, making time the primary dimension for analysis. As organizations collect billions of these records, efficiently storing and querying them becomes increasingly challenging. This is where ClickHouse® excels. Although ClickHouse is not a dedicated time-series database, its columnar storage architecture, vectorized query execution, high compression ratios, and massively parallel processing make it an excellent choice for time-series analytics at scale. It is capable of ingesting large volumes of data while delivering analytical queries in milliseconds. The article begins by explaining the fundamentals of time-series data and highlighting common real-world use cases such as application monitoring, IoT sensor data, financial market analysis, server metrics, user activity tracking, and business analytics. These workloads typically involve continuous data ingestion, time-based filtering, aggregations, and trend analysis. One of ClickHouse's biggest strengths is its optimization for analytical workloads. Since data is stored column-wise rather than row-wise, only the required columns are read during query execution. Combined with compression and vectorized processing, this significantly reduces I/O and improves query performance over massive datasets. The article also demonstrates how to create an optimized table for time-series workloads using the MergeTree engine. Proper partitioning by month and ordering data by dimensions and timestamps help ClickHouse prune unnecessary partitions and efficiently locate relevant data during queries. Several practical SQL examples are covered, including: Filtering records within a specific time range Aggregating metrics by hour, day, week, or month Calculating averages, sums, minimums, and maximums Grouping events over time Working wi

Lately I keep reaching for SQLite where, before, I'd have reached for Postgres without thinking. It started with small services, then a bigger question: could a multi-tenant SaaS actually run on SQLite? And for AI agents specifically, isn't a local, embedded database the more natural home for their state? Turso is the version of this stack I've found most compelling so far, especially when paired with Cloudflare. I wish D1 would reach embedded-replica parity with Turso, and that AWS offered a managed SQLite-style service the way it offers RDS for Postgres. This isn't a "Postgres is over" argument. I still use Postgres more often than SQLite. And it isn't advice. It's just where my thinking has drifted recently — written down mostly so I can find out where it's wrong. Read it as one person's notes, not a recommendation. Where I've landed for now (and expect to keep revising): SQLite isn't replacing Postgres. For work state , it's increasingly my first reach, not my last. AI agents push this harder: their state is high-churn, local, and mostly private. The answer isn't all-local. It's a local workbench plus a central ledger . Why the old default existed For years, "where does the data live?" had one practical answer: a server, behind an API, in a shared Postgres. A lot of that wasn't architecture — it was the cheapest shape available. SQLite was already everywhere, but it lacked the operational layer that makes a database viable as SaaS infrastructure: networking, replication, managed backups, and a way to run many small databases without drowning in tooling. So centralizing was the path of least resistance, and a tenant_id column in shared Postgres became the reflex. What changed isn't SQLite. It's that the ecosystem grew the missing parts — and for a growing class of workloads, the thing doing the most frequent writing moved onto my own machine. The constraint that's lifting SQLite itself is, by design: Embedded, not networked — a library, nothing listens on a port.

Data privacy regulations such as GDPR, LGPD, and HIPAA demand that organizations know exactly where Personally Identifiable Information (PII) lives inside their databases. Yet in practice, most teams rely on manual inventories, tribal knowledge, or external scanning tools that require data to leave the database engine — a process that itself creates privacy and security risks. This article presents an MVP that takes a different approach: it runs PII detection inside InterSystems IRIS using Embedded Python, analyzing data where it lives and never exporting it to an external process. The result is a lightweight, non-intrusive utility that scans your tables, identifies PII using AI, and produces a structured CSV report — all without data ever leaving the IRIS process. The Problem: PII You Don't Know You Have Organizations today face a painful blind spot. A typical IRIS instance may contain hundreds of tables across dozens of schemas, some holding decades of accumulated data. Columns named ContactInfo , Notes , or Description might silently contain social security numbers, email addresses, or government IDs — sometimes intentionally, sometimes as a side effect of free-text fields that capture whatever users type in. Traditional approaches to PII discovery share a common flaw: they require data extraction. You export samples, send them to an external service, or pipe them through a standalone tool. Every step in that pipeline is an additional attack surface and a potential compliance violation. The principle of data sovereignty — keeping data within its jurisdiction and under controlled access — suggests a better path: bring the analysis to the data, not the data to the analysis. This is not just a technical preference; it is a governance requirement: GDPR (EU) — Article 28 requires that any processing of personal data by a third-party processor be governed by a binding contract covering subject-matter, duration, purpose, data types, and obligations [ Art. 28 GDPR ]. Art

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.

PostgreSQL HA Risks, Replication Internals, & Rapid Branching Today's Highlights Today's highlights include critical insights into Patroni's replication slot management, an architectural deep dive into PostgreSQL's synchronous commit behavior, and a look at achieving sub-second database branching for enhanced developer workflows. When Patroni Silently Deletes Your Replication Slots (Planet PostgreSQL) Source: https://postgr.es/p/9lM This article uncovers a critical operational pitfall when using Patroni, a popular high-availability solution for PostgreSQL, with logical replication. It details how Patroni, under specific failure scenarios or configuration changes, can silently remove replication slots without warning. Replication slots are vital for ensuring that standbys or logical replication consumers do not miss any changes, making their deletion a potentially severe data integrity issue. The author explains the underlying reasons for this behavior, often related to how Patroni manages pg_basebackup or restores, and how it might not re-create logical replication slots automatically. The post provides concrete scenarios where this can occur, such as when a new primary is elected and old slots aren't re-established, or during certain recovery operations. It emphasizes the importance of diligent monitoring of replication slot status and proposes strategies to mitigate the risk of silent deletion, including careful Patroni configuration and robust alerting mechanisms. This insight is crucial for database administrators and developers relying on Patroni for resilient PostgreSQL deployments, highlighting a subtle but dangerous interaction between these two powerful components. Comment: This is a must-read for anyone running Patroni with PostgreSQL, especially if using logical replication. Understanding this specific behavior of Patroni deleting replication slots silently is essential to prevent unexpected data loss or integrity issues in production. Why Postgres Doesn'

I had a write that spanned two physically separate databases. A rename that had to propagate across several tables in one database and a couple of tables in another, and the two had to stay consistent. No distributed transaction coordinator was available to me. So I did the obvious thing: opened a transaction on each, did the work, and committed them one after the other inside a try/catch with rollbacks on both sides. It felt safe. It compiled. It passed tests. Then I drew the failure on a whiteboard, and the safety evaporated. The window that ruins everything Here's the structure, simplified: await using var txA = await dbA . Database . BeginTransactionAsync (); await using var txB = await dbB . Database . BeginTransactionAsync (); await DoWorkOnA ( dbA ); await DoWorkOnB ( dbB ); await txA . CommitAsync (); // <-- succeeds await txB . CommitAsync (); // <-- what if this throws? Two transactions do not make one atomic operation. CommitAsync is a point of no return, and there are two of them. Between the first commit returning and the second one starting, there is a window. If txB fails in that window — the connection drops, the process is killed, the database hiccups — then A is permanently committed and B never happens. Your rollback in the catch block is useless: you can't roll back txA , it's already durable. The two databases now disagree, and nothing in your code will heal that on its own. This is the dual-write problem , and it's not a bug you can fix by being more careful with try/catch. The atomicity you want simply isn't available from two independent commits. Ordering them, nesting them, wrapping them — none of it closes the window, because the window is inherent to having two commit points. Why "it's never failed" isn't reassurance The seductive thing about this pattern is that the window is small, so in practice it almost never triggers. You can run it for a year and never see an inconsistency. That's exactly what makes it dangerous: it trains you to tr

Sometimes you genuinely need the set of columns to update to be data, not code. An operator maps configuration fields to database columns, and you want to honor that mapping without redeploying every time it changes. The naive solution — build an UPDATE string from those column names — is also one of the easiest ways to hand-write a SQL injection vulnerability. This is how to get the flexibility without the hole. We'll build it up in three layers: make it work, make it safe, then count the cost. Layer 1: The dynamic update, the wrong way The tempting version concatenates column names into SQL: // DO NOT do this. var sql = $"UPDATE products SET { columnName } = { value } WHERE id = { id } " ; If columnName comes from configuration that an operator can edit, you've just made your schema writable by whoever controls that config. A value of name = 'x'; DROP TABLE products; -- is now your problem. Even "trusted" config is an injection surface the moment it flows into a SQL string. Layer 2: The same feature with EF.Property EF Core's ExecuteUpdateAsync lets you set a property by name without ever building SQL yourself. EF.Property<T> takes the property name as a string, and EF parameterizes the value and validates the property against the model: await db . Products . Where ( p => p . Id == id ) . ExecuteUpdateAsync ( setters => setters . SetProperty ( p => EF . Property < float ?>( p , columnName ), value )); This is already a different security posture: the value is a parameter, not interpolated text, and EF will throw rather than emit SQL if columnName isn't a real mapped property. But "EF will throw" is a runtime backstop, not a policy. We want to reject bad names before they reach the database, fail closed, and control exactly which columns are writable. Layer 3: Reflection as a whitelist The guard is to validate every incoming column name against the entity's actual properties, using reflection, and to keep an explicit blacklist of fields that must never be touched d

Paginated queries with automatic total count calculation. Supports specifying result fields. public < E > LimitResult < List < E >, E > query ( Class < E > cls , LimitSelect limitSelect ) public < E > LimitResult < List < E >, E > query ( Class < E > cls , LimitSelect limitSelect , LimitRange range ) public < E > LimitResult < List < E >, E > query ( Class < E > cls , LimitSelect limitSelect , List resultFields ) public < E > LimitResult < List < E >, E > query ( Class < E > cls , LimitSelect limitSelect , LimitRange range , List resultFields ) Returns: LimitResult — contains getResult() (data list) and getTotal() (total count). Example: // Define pagination query LimitSelect limitSelect = new LimitSelect () { public SelectOrderByStep from ( SelectSelectStep select ) { return select . from ( T ( "user_table" )) . where ( jt . conditions ( "name%" , name , "birthday>=" , beginDate )); } public List < OrderField > orderBy () { return Arrays . asList ( F ( "birthday" ). desc ()); } }; // Mode 1: return all data, no total count LimitResult res1 = jt . query ( User . class , limitSelect ); // Mode 2: return limit rows, no total count LimitResult res2 = jt . query ( User . class , limitSelect , LimitRange . of ( 20 )); // Mode 3: paginate (offset starts at 0), calculate total count LimitResult res3 = jt . query ( User . class , limitSelect , LimitRange . of ( 20 , 0 )); // res3.getResult() returns data, res3.getTotal() returns total count // Mode 4: specify result fields LimitResult res4 = jt . query ( User . class , limitSelect , LimitRange . of ( 20 , 0 ), Arrays . asList ( "id" , "name" )); // LimitRange.all(): return all data, no total count LimitResult res5 = jt . query ( User . class , limitSelect , LimitRange . all ()); // Access results List < User > data = res3 . getResult (); int total = res3 . getTotal (); About the LimitSelect interface: // LimitSelect is a interface: public interface LimitSelect { // Build the FROM clause; the select parameter allows specifyi

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

The thesis Quorum is built on is uncomfortable and true: the tools a team uses to coordinate an incident often live in the same region as the thing that is failing. When the region goes, the incident response goes with it. You are now coordinating a region outage over a status page that the region outage took down. Quorum is an incident command plane designed to survive a region loss. This post is about how the failover works, what the live demo does and does not prove, and where the survival story currently ends, because a database audience will ask all three and they deserve a straight answer. What DSQL gives you A multi-region DSQL cluster in the US set is three regions: two full regions, which for Quorum are us-east-1 and us-east-2, and a log-only witness in us-west-2 that has no cluster endpoint of its own. Both full-region endpoints present a single logical database with strong consistency, and the architecture is designed for 99.999% multi-region availability with no single point of failure and automated failure recovery . The behavior that matters for an incident tool is stated plainly in the GA announcement : applications can keep reading and writing with strong consistency even when they are unable to connect to a region's cluster endpoint, and the third region acts as a log-only witness with no cluster resource or endpoint. The survivor keeps serving; the witness holds the log so the surviving region keeps commit quorum. Quorum is, in effect, a live demonstration of that reference behavior with an incident-command product wrapped around it. Quorum's failover layer AWS's guidance for multi-region DSQL is to put routing in front of the endpoints: either DNS-based routing with Route 53, or application-level routing logic, so traffic redirects automatically when an endpoint becomes unreachable. This is laid out in Implement multi-Region endpoint routing for Amazon Aurora DSQL . Quorum, a Next.js app on Vercel, does the application-level version: it detects an

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

Six months ago I moved my RAG pipeline from Pinecone to self-hosted Qdrant. My vector search bill went from $210/month to $6.50/month. Same latency. Same recall. Here's exactly how. The Setup My app does document Q&A for legal contracts. The numbers: 5.2 million vectors (1536-dim, OpenAI embeddings) ~800K queries/month P99 latency requirement: < 50ms On Pinecone Serverless, this cost me roughly $210/month — storage plus read units plus write units for daily ingestion of new documents. What I Moved To A single Hetzner CX32 server: 4 vCPU, 8 GB RAM, 80 GB SSD €8.50/month (about $9.20) Qdrant running in Docker Automated daily backups to S3-compatible storage ($0.50/month) Total: ~$10/month. That's a 95% cost reduction. The Migration Was Easier Than Expected bash# Export from Pinecone (I used their scroll API) python export_pinecone.py --index legal-docs --output vectors.jsonl Start Qdrant docker run -d -p 6333:6333 -v ./storage:/qdrant/storage qdrant/qdrant Import python import_qdrant.py --input vectors.jsonl --collection legal-docs The whole migration took an afternoon. The Qdrant Python client is straightforward, and the API is surprisingly similar to Pinecone's. Performance Comparison I ran the same 10,000 test queries against both setups: MetricPinecone ServerlessQdrant Self-HostedP50 latency23ms4msP99 latency89ms12msRecall@100.970.97Monthly cost$210$10 The self-hosted Qdrant is actually faster because the data sits in memory on the same machine. Pinecone Serverless loads data from object storage on demand, which adds cold-start latency. When Self-Hosting Is a Bad Idea I want to be honest about the trade-offs: Don't self-host if: You have zero DevOps experience and no one on the team does You need 99.99% uptime SLA for enterprise customers Your vector count is growing unpredictably (10M one month, 100M the next) You're a team of 1-2 and every hour on infra is an hour not building product Do self-host if: Your scale is predictable (you know roughly how many vectors

Every backend engineer has seen this happen, you build an application on a relational database like MySQL, handling thousands of concurrent transactions effortlessly. Then, the business asks for a real time analytics dashboard. But when you run an aggregation query over historical data, suddenly the database that effortlessly managed live traffic starts thrashing, evicting your working set, and dragging application performance down. This isn't a tuning problem, a missing index, or a badly written query. It’s a fundamental architectural collision. OLTP (Online Transaction Processing) OLTP encompasses nearly every concurrent digital interaction triggered across a distributed system. A user downloading a PDF, a microservice firing an automatic maintenance log, a comment on a social feed these are all transactions. Data engineers rely on OLTP systems (like MySQL or PostgreSQL) to capture these concurrent streams of interactions for creating , updating and deleting records. The Tree Based In-Place Engine To reliably capture massive volumes of transactions without corrupting data or locking up the application, OLTP systems rely on a highly optimized, row oriented architecture built around the B+ Tree. Because they must provide immediate, atomic updates to existing records, transactional databases manage state through a strict sequence of physical tree traversal and in-memory page mutation: The B+ Tree Indexing: When a transaction reads or updates id: 1, the engine traverses a B+ Tree from the root, through the branch nodes, directly to the specific physical leaf node holding that row. This O(\log n) traversal guarantees a fast, isolated point-lookup. It ensures the application always hits the single version of the row without scanning irrelevant data. The Buffer Pool & In-Place Updates: OLTP systems perform in place updates. The database pulls the exact page containing id: 1 from the physical disk into memory (the Buffer Pool). The specific row is mutated directly in RAM

"Chat with your PDF / your notes / your docs" is everywhere. Today we build it from scratch and you'll see it's just three moves : retrieve, then generate — with one prompt trick that stops the hallucinations. This is Day 46 of TechFromZero. Yesterday (Day 45) we built the retrieval half with pgvector. Today we add the answer half and host it on Supabase. RAG in one line Find the relevant chunks of your documents, paste them into the prompt, and tell the model to answer using only those. That's Retrieval-Augmented Generation. The "augmented" part is just stuffing real context into the prompt so the model isn't guessing from memory. 1. Storage: Supabase is Postgres, so pgvector is one click Supabase is hosted Postgres with an auto-generated API. Because it's just Postgres , vector search needs no separate database: create extension if not exists vector ; create table documents ( id bigserial primary key , content text , embedding vector ( 384 ) ); -- one RPC the app calls to get the closest chunks create function match_documents ( query_embedding vector ( 384 ), match_count int ) returns table ( id bigint , content text , similarity float ) language sql stable as $$ select id , content , 1 - ( embedding <=> query_embedding ) as similarity from documents order by embedding <=> query_embedding limit match_count ; $$ ; 2. Ingest: chunk → embed → store Split your docs into paragraph-sized chunks, embed each with a free local model (all-MiniLM-L6-v2 via Transformers.js — no key, nothing leaves your machine), and insert the row + vector: const embedding = await embed ( chunk ); // 384 numbers await supabase . from ( " documents " ). insert ({ content : chunk , embedding }); Chunk size matters: too big buries the answer in noise, too small loses meaning. A few hundred characters is a good start. 3. Retrieve + Generate (the payoff) Embed the question with the same model, ask Supabase for the closest chunks, then hand them to the LLM: const query_embedding = await embed ( que

Traditional databases just can't keep up with high concurrency and low latency at the same time. The term "real-time" has become kind of meaningless. Everyone claims it, from batch-oriented cloud data warehouses to transactional database extensions. This makes picking the right architecture really hard without expensive trial and error. The best real-time analytics database in 2026 depends entirely on your workload shape. Key takeaways Real-time analytics (in this guide) = sub-second p95/p99 analytical queries on billions of rows, high concurrency , and milliseconds-to-seconds freshness . Best overall in 2026 for most workloads: ClickHouse (ingest throughput, query speed at scale, compression/TCO). Best for strictly predefined query paths via star-tree indexes: Apache Pinot . Best for time-series operational dashboards and observability: ClickHouse . ClickStack is its full observability offering for logs, metrics, and traces. Best for rigid ingestion-time roll-up aggregations: Apache Druid . Best for unified OLTP + real-time analytics: ClickHouse paired with its managed Postgres offering and native sync to ClickHouse , giving you a purpose-built OLTP engine and a purpose-built OLAP engine without rolling your own CDC pipeline. SingleStore is an alternative if you prefer a single HTAP engine for both. Traditional Data Warehouses: Snowflake and BigQuery are fine for batch BI if you already have one, but face latency, concurrency, and cost challenges under sub-second, high-concurrency workloads. Evaluate using 4 axes: ingest/freshness, latency under concurrency, TCO, operational complexity. What 'real-time analytics' means (and why warehouses and OLTP databases fail) Strict engineering thresholds define true real-time OLAP : sub-second query latency on complex aggregations, the ability to serve tens to thousands of concurrent queries per second (QPS), and data freshness measured in milliseconds to seconds. Traditional cloud data warehouses like Snowflake and BigQuery a