标签：#Database

The short version NextAuth (now Auth.js) creates 4 tables in your database: users , accounts , sessions , and verification_tokens . The users and accounts tables have a one-to-one relationship via accounts.user_id . Sessions link to users via sessions.user_id . Verification tokens are short-lived and self-cleaning. The 4 tables users Column Type What it means id text / UUID Primary key. Generated by NextAuth. name text Display name from the OAuth provider (Google, GitHub, etc.) email text User's email. May be null if the provider doesn't share it. email_verified timestamp When the email was verified. Null if never verified. image text Profile picture URL from the provider. created_at timestamp When the user first signed in. updated_at timestamp Last profile sync from the provider. accounts This table links a user to an OAuth provider. One user can have multiple accounts (e.g., Google + GitHub). Column Type What it means id text / UUID Primary key. user_id text Foreign key → users.id . type text Always "oauth" or "oidc" . provider text "google" , "github" , "discord" , etc. provider_account_id text The provider's unique ID for this user. refresh_token text OAuth refresh token (encrypted in production). access_token text OAuth access token (encrypted in production). expires_at integer When the access token expires (Unix timestamp). token_type text Usually "Bearer" . scope text Permissions granted by the provider. id_token text OIDC ID token (if using OIDC). session_state text Provider-specific session state. sessions Active sessions for each user. NextAuth creates a new row here on every sign-in. Column Type What it means id text / UUID Primary key. session_token text The session token stored in the user's cookie. user_id text Foreign key → users.id . expires timestamp When this session expires. verification_tokens Short-lived tokens for email verification, password reset, etc. Self-cleaning old tokens are deleted automatically. Column Type What it means identifier te

I was testing out CH-Ops - an admin GUI for self-hosted ClickHouse - on a simple setup: 1 shard, 2 replicas. Stumbled onto the replication queue almost by accident. Here's what I did: I stopped one of the nodes (let's call it Node B), then inserted some data through the other one (Node A). Just wanted to see what would happen. Then, while Node B was still down, I checked it in CH-Ops. It had stuff sitting in its replication queue. My first assumption was: okay, this must be showing what's left to replicate across the cluster - the total pending replication work. So I switched over and checked Node A, the one that was actually up and had just received the insert. Its queue was empty. That didn't match what I expected at all. If the queue was a cluster-wide "here's what still needs to replicate" view, Node A should've shown something too - it was the one that had the fresh data now waiting to reach Node B. Instead it was Node B, the down one, sitting there with pending tasks. That mismatch is what sent me digging. Turns out the queue isn't cluster-wide at all - it's specific to each ClickHouse instance. Once I brought Node B back up, its queue drained in seconds and the data showed up. That whole experiment is basically the entire post in miniature. Here's the mental model I ended up with. A Queue Belongs to a Replica, Not to the Table This is the first thing to get straight. With a ReplicatedMergeTree table, you can have multiple replicas holding copies of the same data. It's tempting to think of replication as one shared pipe between them. It isn't. Each replica keeps its own local replication queue . So if you see: Replica 1 → queue_size = 0 Replica 2 → queue_size = 25 that doesn't mean 25 operations are waiting somewhere in the middle for both replicas to pick up. It means Replica 2, specifically, has 25 tasks it hasn't finished yet. Once that clicked for me, the rest of the system made a lot more sense. So Where Do These Tasks Come From? Replication in ClickHouse

Author: Ignacio Paz An AI agent is only as useful as what it can remember - and how well it can connect the things it remembers. Most teams hand their agent a memory by reaching for a vector store: embed everything, retrieve by similarity, hope the relevant context comes back. It works, until you notice the agent keeps surfacing things that are near the question but not actually connected to it. Cobrainer , a skills-intelligence company based in Munich, took a different route. They gave their AI agent a memory that lives in the database as a graph, where the agent builds the relationships between nodes as it goes. They did it without adding a graph database, a vector engine, or a search engine to their stack. It all runs on SurrealDB, alongside a Rust-native agentic graph RAG built on the same store. Here's how, and why a single engine made the difference. The problem with flat memory Cobrainer runs a skills-intelligence platform - the kind of system that reasons about how people, roles, skills, and capabilities relate to one another. That's an inherently graph-shaped problem. But their first retrieval setup wasn't graph-shaped at all. It pulled context through flat vector retrieval over an S3-and-OpenSearch pipeline, which carried two recurring costs: Accuracy . Flat vector matches returned context that was loosely related - semantically near, but not necessarily connected in any meaningful way. The team wanted the agent to follow real relationships between entities, so its answers were grounded rather than approximate. Tokens . Broad vector matches meant stuffing a lot of marginally relevant context into every prompt - expensive, and more so with every call. The team wanted to fetch only the context that mattered. The obvious fix - adding a graph database on top of the vector and search systems they already ran - would have meant more infrastructure to operate. For a startup moving fast, that fragmentation was the thing to avoid, not embrace. What they wanted inst

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

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

Background : In migration projects involving different databases, incompatibility of SQL syntax is often encountered. Question : If there is a large amount of code that needs to be rewritten, manual processing would be time-consuming and prone to errors. Is it possible to achieve automatic conversion of code syntax in large quantities through tools? Solution : The open-source tool ZGLanguage can be utilized to perform automated conversion of SQL code in large batches. For example： Suppose SQL PIVOT function is as follows : SELECT * FROM ( select country , state , yr , qtr , sales , cogs from table111 ) PIVOT ( SUM ( sales ) AS ss1 , SUM ( cogs ) AS sc FOR qtr IN ( 'Q1' AS Quarter1 , 'Q2' AS Quarter2 , 'Q3' AS Quarter3 , 'Q4' AS Quarter4 ) ) tmp ; Using the ZGLanguage conversion rule, execute the conversion to obtain the result : SELECT * FROM ( select ### , ### , ### SUM ( case when qtr = 'Q1' then sales else null end ) AS Quarter1_ss1 , SUM ( case when qtr = 'Q2' then sales else null end ) AS Quarter2_ss1 , SUM ( case when qtr = 'Q3' then sales else null end ) AS Quarter3_ss1 , SUM ( case when qtr = 'Q4' then sales else null end ) AS Quarter4_ss1 , SUM ( case when qtr = 'Q1' then cogs else null end ) AS Quarter1_sc , SUM ( case when qtr = 'Q2' then cogs else null end ) AS Quarter2_sc , SUM ( case when qtr = 'Q3' then cogs else null end ) AS Quarter3_sc , SUM ( case when qtr = 'Q4' then cogs else null end ) AS Quarter4_sc from ( select country , state , yr , qtr , sales , cogs from table111 ) where qtr IN ( 'Q1' , 'Q2' , 'Q3' , 'Q4' ) group by ### , ### , ### ) tmp ; The conversion rule is as follows : __DEF_FUZZY__ Y __DEF_DEBUG__ N __DEF_CASE_SENSITIVE__ N __DEF_LINE_COMMENT__ -- __DEF_LINES_COMMENT__ /* */ __DEF_STR__ __IF_KW__ <1,100> [1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz [0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_ [NO] XXX __DEF_PATH__ __FROM_PIVOT_1_1__ 1 : frm @ %__IF_KW__ | from : tab @ | __TABLE_NAME__ : ssl @

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

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

Originally published at nlocoding.com 41% of enterprise database engineers already use AI tools daily to generate, optimize, or review SQL—up from just 14% in 2023 (Gartner, 2026). The new database arms race is invisible. Enterprises process 7.4x more data per person than they did five years ago. That’s not a typo. AI-assisted database development isn’t just about speed; it’s about not drowning in schema drift and query chaos. If you’re not automating, you’re lagging by $8,200 per developer per year (Forrester, 2026). 73%of data teams say AI reduced query errors (Redgate, 2026) AI-assisted database development is rewriting the rules in 2026 AI-assisted database development is now the backbone for 52% of Fortune 500 engineering departments, slashing schema build time by 48% on average (Stack Overflow Developer Survey, 2026). Developers no longer waste days hand-writing migration scripts or debugging malformed indexes. Instead, GPT-5-powered copilots like Tabnine and DataPilot draft DDL, suggest denormalization strategies, and catch performance anti-patterns before they hit production. The result: projects ship 23% faster, according to Fivetran’s 2026 benchmark. If you’re still relying on manual SQL, you’re not just slower—you’re more expensive. Find one workflow, automate it, and measure the delta. That’s how the best teams start. ⚠️ Common Mistake: Treating AI-generated schema suggestions as gospel. Blind trust leads to silent data loss or bloated tables. Always review before merging. Schema design is now a conversation, not a bottleneck Most people get this wrong: schema design is not just a technical hurdle—it’s a communication bottleneck. In 2026, 64% of product teams report that AI-driven schema prototyping (using tools like dbdiagram.io+AI Assist, $7/month) reduced handoff time between engineering and product by 58% (LinearB, 2026). Instead of four revision meetings, you get a Slack thread with three alternative schemas, clear tradeoffs, and a side-by-side diff

A database client supporting 90+ engines sounds like a dependency-management problem disguised as a UI. My late-night question was simpler: how much of that complexity does t8y2/dbx carry before the first connection? The interesting claim is its small footprint—around 20 MB—combined with desktop, CLI, Docker, AI, and MCP Server modes. That is a much different architecture from shipping one heavy client per database vendor. The real test is not today’s +420 stars; it is startup latency, resident memory, and whether an unused adapter stays out of the hot path. Under the Hood The likely execution model is a shared core with database-specific drivers around it. The desktop interface, CLI, Docker image, and MCP endpoint become different front doors to the same connection and query layers. That design has two useful consequences: Connection handling and query behavior can stay consistent across interfaces. New database support does not require duplicating authentication, result formatting, or export logic. The edge case is driver loading. If all 90+ integrations initialize eagerly, startup and memory usage will grow quickly. Lazy loading is therefore more important than the headline database count. A Minimal Measurement Pass After downloading a release binary, I used this deliberately boring check: chmod +x ./dbx /usr/bin/time -v ./dbx --help 2>&1 \ | grep -E 'Elapsed|Maximum resident' For a source checkout, the first useful inspection is: git clone https://github.com/t8y2/dbx.git cd dbx find . -maxdepth 2 \( -name 'go.mod' -o -name 'Cargo.toml' -o -name 'Dockerfile' \) -print This avoids guessing the build system and immediately exposes whether the advertised modes are separate binaries, containers, or wrappers. Trade-offs I Would Watch A compact binary does not guarantee a compact running process. TLS libraries, database drivers, schema introspection, query history, and result grids can dominate memory after startup. MongoDB and Redis also do not fit neatly into a relat

Background : In migration projects involving different databases, incompatibility of SQL syntax is often encountered. Question : If there is a large amount of code that needs to be rewritten, manual processing would be time-consuming and prone to errors. Is it possible to achieve automatic conversion of code syntax in large quantities through tools? Solution : The open-source tool ZGLanguage can be utilized to perform automated conversion of SQL code in large batches. For example： Suppose 'ORACLE START WITH CONNECT' syntax code（ start_with_connect.sql ）： SELECT * FROM tree START WITH id = 1 CONNECT BY NOCYCLE PRIOR id = parentid ; By configuring the conversion rules, the above code can be directly converted into the following code（convert to "with recursive" syntax）： with recursive wr_tree as ( SELECT id , parentid , 1 as level from tree where id = 1 union SELECT tree . id , tree . parentid , level + 1 from tree , wr_tree where tree . parentid = wr_tree . id ) SELECT * from wr_tree order by id ; Conversion rule (STATR_WITH_CONNECT_SQL_REPLACE.syn) is as follows： __DEF_FUZZY__ Y __DEF_DEBUG__ N __DEF_CASE_SENSITIVE__ N __DEF_LINE_COMMENT__ -- __DEF_LINES_COMMENT__ /* */ __DEF_STR__ __IF_KW__ <1,100> [1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz [0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_ __DEF_PATH__ __START_WITH_CONNECT__ 1 : sel @ %__IF_KW__ | select : cc @ | * : frm @ | from : srctab @ | __NAME__ : sta @ %__IF_KW__ | start : wth @ %__IF_KW__ | with : swp @ | __NAME__ : dy1 @ | = : int @ | __INT__ : str @ + __STRING__ : cnn @ %__IF_KW__ | connect : by @ %__IF_KW__ | by : ncy @ %__IF_KW__ CAN_SKIP | nocycle : prr1 @ %__IF_KW__ CAN_SKIP | prior : col1 @ | __NAME__ : dy @ | = : col2 @ | __NAME__ : end @ | ; ----------------------------------------------------------------------- 1 : sel @ | with : sel @ | recursive : sel @ | wr_ : srctab @ \ __NAME__ : sel @ STRING | as : sel @ | __\n__ : sel @ | ( : sel @ | __\n__ : sel @ | selec

Onur Satici explains how Vortex, an open-source columnar file format under the Linux Foundation, revolutionizes high-throughput data loading. He details how cascading lightweight encodings, layout-based segment pruning, and zero-copy memory pipelines eliminate CPU/NVMe bottlenecks to stream S3 data straight to GPUs at speeds up to 60 Gbps without requiring upfront data reprocessing. By Onur Satici

Hi Folks , In this article, I'm going to share my learnings in SQL . Install MySQL Workbench editor from **MySQL Downloads **found in internet. After Installation, Open MySQL Workbench Click on '+' icon to create new sql editor Click on import icon present on top in sql editor workbench After importing, See the data, schema tables present in left side panel We see the sql editor workspace, result Grid,output view. How to display data present in result table ? Use SELECT '*' as Keyword FROM table_name Ex: SELECT * FROM moviesdb.movies; Instead of moviesdb.movies we can use movies as name. Follow the steps below: Go to the left panel where movie data present, select movie data table -> Right click on default schema it highlights in bold -> Then, Write query as, SELECT * FROM movies Method - 2 USE moviesdb SELECT * FROM movies; Here we're using ' USE ' Keyword How can we check how many rows are present after writing query ? Just go to output view present in MySQL Workbench editor (present below to the result Grid) you can see there (or) Check with Excel sheet using filter option to check how many rows are present. Suppose we're writing query but we don't know recieved output is right or wrong. We can check with our excel sheet data using filter option if it matches with our output rows then our query is correct this is how we can debug and check ✅ output. There are some inbuilt clauses present in SQL 1.WHERE 2.COUNT 3.DISTINCT (for unique values) 4.LIKE 5.% - Wildcard Search Ex: %movies% Gives the movies data present anywhere in the sentence %movies - Returns movies data present starting at the sentence %movies - Returns of movies present at last in the sentence Ctrl + Scroll UP - Zoom in SQL Editor Database - Collection of rows and columns in tabular format with schema it has duplicate ids, unique ids in different formats like excel, .sql etc. How to save all written queries ? Write all queries at one place and highlight each query, do the next steps mentioned below,

Preventing Cache Penetration in Spring Boot Using Redis and Bloom Filters Cache penetration occurs when high-frequency requests query non-existent keys, bypassing the Redis cache completely and hitting the relational database directly. Here is how we set up a Bloom Filter guard layer in front of Redis and PostgreSQL. 1. The Bloom Filter Guard Concept A Bloom Filter is a space-efficient probabilistic data structure that tests whether an element is definitely NOT in a set or MIGHT be in a set. @Component public class CachePenetrationGuard { private final BloomFilter < String > accountFilter ; public CachePenetrationGuard () { // Expected insertions: 500,000, False positive probability: 0.01 (1%) this . accountFilter = BloomFilter . create ( Funnels . stringFunnel ( StandardCharsets . UTF_8 ), 500000 , 0.01 ); } public void registerKey ( String accountId ) { accountFilter . put ( accountId ); } public boolean mightContain ( String accountId ) { return accountFilter . mightContain ( accountId ); } } 2. Service Layer Verification Before querying Redis or PostgreSQL, verify with the Bloom Filter: @Service public class AccountService { private final CachePenetrationGuard guard ; private final RedisTemplate < String , AccountDto > redisTemplate ; private final AccountRepository repository ; public AccountDto getAccount ( String accountId ) { // Step 1: Bloom filter pre-check if (! guard . mightContain ( accountId )) { return null ; // Instant rejection, saves DB from unnecessary lookups } // Step 2: Redis lookup AccountDto cached = redisTemplate . opsForValue (). get ( "acc:" + accountId ); if ( cached != null ) return cached ; // Step 3: DB fetch and cache populate AccountDto dbResult = repository . findByAccountId ( accountId ); if ( dbResult != null ) { redisTemplate . opsForValue (). set ( "acc:" + accountId , dbResult , Duration . ofMinutes ( 30 )); } return dbResult ; } } 3. Summary Combining Bloom Filters with TTL jitter in Redis shields backend databases from cache

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

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

What building a warehouse management system taught me about the data operational software leaves behind — and the engineering it takes to make that data trustworthy. The second that outlives itself A picker holds a handheld scanner, points it at a carton, and pulls the trigger. There's a beep. They type 10, confirm, and move to the next location. The whole thing takes about a second. For a long time I thought of my job as making that second work. I built the screen, the endpoint behind it, the repository behind that. My definition of done was that the user completed the workflow, the API returned success, and the right rows landed in the database. What changed my thinking was noticing what was still there afterwards. The screen closes, the session ends, the app ships a new version, the picker changes jobs, the device is replaced. The row stays — and the row isn't a record of a UI interaction. It's a durable claim about the physical world: at this time, this person, on this device, ten units of this product moved. The application is the instrument. The data is the measurement. A measurement is only ever worth what the instrument's precision allows. This article is about the gap between those two definitions of done, and the specific decisions — retry semantics, timestamps, identity, status codes, conflict resolution — that determine which side of it you land on. Almost all of them get made by application developers, inside feature work, long before anyone tries to analyze anything. What warehouse owners actually do with this data now Worth being concrete about the stakes first, because "data quality matters" is the kind of statement everyone agrees with and nobody acts on. What's changed isn't that owners suddenly became analytical. It's that operational systems started producing enough granular, attributed, time-stamped movement data that previously unanswerable questions became answerable. Inventory accuracy is a working-capital decision. Stock you can't trust is s

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

Every value in my little embedded key-value store gets encrypted, then its ciphertext gets encoded as a string of A/C/G/T characters before it ever touches the filesystem. Open the file in a text editor and you'll see actual DNA-looking text - not because it's a gimmick, but because that's genuinely the storage format. This is mdc-lite , a ~348KB embeddable encrypted key-value store I built in Rust for places a server can't reach - a watch face, a phone app, a background service. It's part of a larger repo, ModelDB , that also includes MDC, a Python conversational data engine (query AI models, databases, images, and documents in plain English, no SQL) with its own DNA-inspired archival storage tier. The actual storage format Every put() call does this, in order: Pack [key_len][key_bytes][value_bytes] into one plaintext buffer. Encrypt the whole thing with XChaCha20-Poly1305 (a 256-bit key you supply - the crate never generates or stores key material itself; real key custody belongs to the platform's secure hardware, iOS Secure Enclave or Android Keystore). DNA-encode the resulting [nonce][ciphertext][tag] blob: 2 bits per base, 00→A 01→C 10→G 11→T . Every byte maps to exactly 4 bases, so there's no padding ambiguity on decode. Write the ACGT text to disk, atomically (temp file + rename). Filenames are keyed BLAKE3 hashes of the logical key, not the key name itself, so a directory listing alone leaks nothing - no key names, no values, no way to tell how many distinct keys exist versus how many files are on disk. rust pub fn put(&self, key: &str, value: &[u8]) -> Result<(), LiteStoreError> { let mut plaintext = Vec::new(); plaintext.extend_from_slice(&(key.len() as u16).to_le_bytes()); plaintext.extend_from_slice(key.as_bytes()); plaintext.extend_from_slice(value); let nonce = XChaCha20Poly1305::generate_nonce(&mut OsRng); let ciphertext = self.cipher().encrypt(&nonce, plaintext.as_ref())?; let mut record = nonce.to_vec(); record.extend_from_slice(&ciphertext); let ac

The second you hit Enter Friday night. You ask Cursor for a query: join orders to users, sort by last login. Three seconds later, an answer arrives with DBA-level confidence: SELECT o . id , o . amount , u . last_login_at FROM biz_order o JOIN sys_user u ON u . id = o . user_id ORDER BY u . last_login_at DESC ; Paste it into your client. Enter: ERROR: column "last_login_at" does not exist LINE 2: SELECT o.id, o.amount, u.last_login_at There is no last_login_at column. There never was. The model did not know — it just decided the column "should" exist. This failure has a name: invented column This is not "AI is not smart enough yet." It has a name — invented column : the model fabricates a plausible column name with no factual source, then writes it into a JOIN with unshakable tone. Invented columns are dangerous because they do not look like errors . last_login_at appears on 90% of user tables. Syntax is correct. Naming is conventional. Indentation is perfect. Mixed into ten correct JOINs, you will not catch it line by line. You find out in code review — or worse, in production logs. Three things you already tried A better prompt. "Do not invent column names; only use the schema I provide" — added to the system prompt. Works day one. By day three, long context and the model forgets. A prompt is a wish, not a constraint. @schema.sql . Export DDL and drop it into context. The most honest approach today — but two holes: it goes stale (last week's export does not know this week's column), and nobody maintains it (not in any approval flow; anyone can edit it; drift from the real database goes unnoticed). Live catalog MCP. Let the Agent query information_schema directly. Directionally correct — give the model a fact source instead of guesses. Tools like postgres-mcp and cloud vendor MCPs do solve half of "stop hallucinating column names." Worth acknowledging. Live catalog only gets you halfway Wire production into the IDE and you hit four walls: Permission-filtered inform