Posts in Database (20 found)

Detecting Full Table Scans With SQLite

I’m at RubyConf this week, and it’s great! I recently read that lobste.rs is now running on SQLite . One part from the post caught my attention: I wish we could say in a test, “Fail if you encounter any full table scans”. Which would have caught the perf issues we experienced during the first deploy. SQLite collects information about prepared statements and exposes those statistics though an API . The upshot of this is that we can tell whether a statement did a full table scan after executing the statement without using an . Here’s an example program that demonstrates detecting a query did a full table scan: Feels like we could integrate this in to Rails and warn or raise in test / development. I’m not sure if we’d want to check this all the time in production, but maybe it would be fine?

0 views

RABIT: Efficient Range Queries with Bitmap Indexing

RABIT: Efficient Range Queries with Bitmap Indexing Junchang Wang, Fu Xiao, and Manos Athanassoulis SIGMOD'26 This paper presents an optimization for range filtering (e.g., ). File this under: “so crazy it might actually work”. This paper builds on the concept of a bitmap index . If the cardinality of a column is (i.e., there are distinct values in the column), then a bitmap index stores additional 1-bit columns along with the original column. These additional columns are called point bitvectors . The value in additional column is 1 for a particular row if the value in that row is the th distinct value. Here is an example column with 6 rows and 4 distinct values (0, 1, 2, 6). The header indicates that this is the value column. And here is the same column with 4 additional 1-bit wide rows attached. The header indicates columns that hold point bitvectors. To find all rows which have a value of 2, simply read the 3rd (i.e., ) bitmap column. Similarly, to find all rows which have a value of 6, simply read the 4th bitmap column. To efficiently support range queries, the authors of this paper propose adding even more columns that hold 1-bit values. These columns are called cumulative bitvectors . A cumulative bitvector holds the bitwise-or of a set of point bitvectors. In the example above, let’s create a cumulative bitvector (named ) for the values 0 and 1. The value of this column for a given row will be 1 if the value contained in the row is either 0 or 1. In other words: . Similarly, we can create a cumulative bitvector for the values 2 and 6 using the equation: . Here is the full turkey: A range query can be executed by reading one or more cumulative bitvectors and possibly a few point bitvectors. For example, to find all rows that have a value < 2, all one needs to do is read the value of . Cumulative bitvectors do not add much value in this toy example because each cumulative bitvector only aggregates data for two values, but you can see how this could work well with more aggregation. This trick can even be made to work for range queries that partially overlap with a cumulative bitvector. This whole scheme relies on the fact that point and cumulative bitvectors are highly compressible. This paper assumes the use of WAH compression . The executive summary of WAH compression is to divide each bitvector into words (e.g., 32 or 64 bit). One bit of each word is metadata that determines if the word is a or a . The remaining bits of a literal word contain raw (uncompressible bits). The remaining bits of a fill word contain a value and a length (run-length encoding). Fig. 9 compares throughput of this scheme (labeled GE in the figure) to other work. means each cumulative bitvector aggregates data for 20 point bitvectors. Performance looks good even for columns holding 100K distinct elements. Source: https://dl.acm.org/doi/10.1145/3769819 Table 3 compares the storage requirements for this scheme versus other indexing schemes that support range queries, which seems too good to be true. Source: https://dl.acm.org/doi/10.1145/3769819 Dangling Pointers I wonder if this idea could be generalized to other types of filtering, such as string operations (e.g., . Thanks for reading Dangling Pointers! Subscribe for free to receive new posts.

0 views
ptrchm 2 days ago

Postgres Backups to S3 with WAL-G and Kamal

The Kamal setup guides I found online focus on S3 backups using . You don’t want that for a production database. A better solution is to set up your Postgres database for Point-In-Time Recovery (PITR) using WAL-G or pgBackRest. This means your database is continuously archiving WAL segments to an S3 bucket (roughly every 60 seconds), so you can restore to any point in time. With LLMs, it’s not that hard to set up. This quick guide focuses on WAL-G, because I’ve found it to be a lot easier to set up than pgBackRest.

0 views
Binary Igor 3 days ago

The Order of Data: defaults, performance, determinism & paging

How does the database decide on the order, when it is not specified? What about performance? Can returned pages overlap? Meaning: might item from page 1 suddenly appear on page 2, even if the underlying data stays the same?

0 views
Evan Hahn 5 days ago

Prefer STRICT tables in SQLite

In short: I prefer strict tables in SQLite because they avoid some datatype problems, such as putting text in number columns. SQLite has a feature that I think is underrated: strict tables . Strict tables help enforce rigid typing, preventing mistakes like putting text into integer columns. I like them, and wrote this post to promote their use! To make a strict table, add to the end of its definition. Like this: That’s it! But what does it do? Broadly, strict tables help enforce rigid types, like other SQL engines do. Most significantly, strict tables keep you from inserting the wrong type into a column. For example, SQLite normally lets you put text into an column, but not with strict tables. Personally, I think it’s a mistake to try to put text in an integer column, or vice-versa. I don’t want SQLite to let me make this error! The same validation happens for s, too. Notably, if a value can be losslessly converted, it will still be accepted. For example, the string can be perfectly converted to an integer, so it’s allowed. These two lines are equivalent, even for a strict table: By default, you can create columns with bogus types. For example, all of these work even though they aren’t valid SQLite datatypes: I think these aren’t what the developer intended. Some of these are typos, some of them are misunderstandings of which datatypes SQLite supports , and some are egregious mistakes. Appending to any of these statements makes them error. In my opinion, that’s the correct behavior! Only , , , , , and are allowed. Strict tables also require a column type, so you can’t do . If you still need a column to be flexible, you can use the datatype. As the name suggests, it allows anything—even in a strict table. I haven’t found a use for this, but maybe you will! I prefer strict tables but I must share a few cons. Not everything is better! I think it’s best to use strictness from the start, but that’s not always possible. Unfortunately, I don’t think there’s a way to a table to make it strict. I think you have to copy the data out of the non-strict table into the strict one. Something like this: Note that this could be tricky if the non-strict table has invalid data! For example, if the old data accidentally contains text in an integer column, you’ll get errors when doing the migration. You’ll probably need to clean the data or cast it . You could make a rule for your codebase that all new tables are strict. That might be useful—at least some of your tables are valid! But it might also mean you have inconsistent validation across your tables, which might be more surprising than having weak validation on all tables. It’s up to you to decide whether this is a good fit for you. SQLite has a whole page called “The Advantages Of Flexible Typing” , where they argue that SQLite’s flexible behavior is good, actually. I hesitate to wade into the controversy of static-versus-dynamic, but I disagree in most cases. I’ve personally encountered many bugs where an unexpected data type caused subtle headaches. I’d much rather these mistakes explode loudly. But it’s worth noting that SQLite’s developers seem not to share my preference for strict tables! They point out a few good uses for flexible tables, such as “a pure key-value store” or “a place to store miscellaneous attributes” of different types. They also mention that you might want to keep the invalid data in some cases, like if you’re directly importing a messy CSV and don’t want to lose any data. I still prefer strict tables, but acknowledge there are some reasonable cases for non-strict ones. (There’s also at least one comment in the SQLite source that calls non-strict tables “legacy” , but I trust that less than the official documentation.) SQLite introduced strict tables in version 3.37.0 , released November 2021. If you’re on an older version of SQLite, you can’t use strict tables. It’s worth noting that old versions of SQLite can’t read databases with strict tables. For example, if you create a strict table in the newest version of SQLite and then try to read that database in SQLite 3.36.0 (before strict tables were added), you’ll get an error—even if the strict table is already in the database. Strict tables are theoretically slower because they have to do a little extra work. For example, they check datatypes when doing an insert or update . But in practice, I don’t think this is an issue. I wrote a hacky script that inserted millions of rows into a table with 100 columns, and there was no obvious difference on multiple machines I tried. The file size on disk was also the same. I didn’t test this thoroughly, so maybe there’s something I missed, but I don’t think strict tables present a performance problem. In fact, one might expect better performance because you won’t be accidentally mismatching SQLite’s column affinities. But again, I haven’t tested this. Personally, I think the pros of strict tables outweigh the cons. I generally prefer when types are rigidly enforced. It squashes a class of mistakes, and help enforce good data integrity. They’re not a panacea, but they’re usually easy to add and go a long way. If there’s a SQLite feature you think is underrated, please tell me .

0 views
Simon Willison 1 weeks ago

sqlite-utils 4.0, now with database schema migrations

This morning I released sqlite-utils 4.0 , the 124th release of that project and the first major version bump since 3.0 in November 2020. In addition to some small but significant breaking changes (described in this upgrade guide ), this version introduces three major features: database migrations , nested transactions (via a new method), and support for compound foreign keys . Schema migrations define a sequence of changes to be made to a SQLite database, plus a mechanism for tracking which migrations have been applied and applying any that are found to be pending. Migrations are defined in Python files using the sqlite-utils Python library , which includes a powerful method providing enhanced alter table capabilities that are not supported by SQLite's statement. ( implements the pattern recommended by the SQLite documentation - create a new temporary table with the new schema, copy across the data, then drop the old table and rename the temporary one in its place.) Here's an example migration file which creates a table called , adds an additional column to it in a second step, then changes the types of two of the columns in a third: Save that as and run it against a fresh database like this: Then if you check the schema of that database: You'll see this SQL: The table is used to keep track of which migration functions have been run. The table above is the schema after all three migrations have been applied. To see a list of migrations, both pending and applied, run this: If you don't specify a migrations file, the command will scan the current directory and its subdirectories for files called and apply any instances it finds in them. You can also execute migrations from Python code using the method, which is useful for building tools that manage their own database schemas over multiple versions. My own LLM tool has been using a version of this pattern for several years now, as shown in llm/embeddings_migrations.py . My favorite implementation of this pattern remains Django's Migrations , developed by Andrew Godwin based on his earlier project South . Fun fact: Andrew, Russ Keith-Magee, and I presented our competing approaches to schema migrations for Django on the Schema Evolution panel at the very first DjangoCon back in 2008! My attempt was called dmigrations , developed with a team at Global Radio in London. Django's migrations can be automatically generated from model definitions and include the ability to roll back to a previous version. The approach is deliberately simpler: unlike Django, encourages programmatic table creation rather than a model definition ORM, so there isn't anything we can use to automatically generate migrations. I decided to skip rollback, since in my experience it's a feature that is rarely used. With a SQLite project, an easy way to achieve rollback is to create a copy of your database file before you apply the migrations! The design of migrations is three years old now - I had originally released it as a separate package called sqlite-migrate , which never quite graduated beyond a beta release. I've used that package in enough places now that I'm confident in the design, so I've decided to promote it to a feature of to make it available by default to all of the other tools in the growing sqlite-utils/Datasette/LLM ecosystem. I made one last release of , which switches it to depend on and replaces the file with the following: Any existing project that depends on should continue to work without alterations. Here are the release notes for this version, with some inline annotations: The 4.0 release includes some minor backwards-incompatible fixes (hence the major version number bump) and introduces three major new features: I think of migrations as the signature new feature, hence this blog post. has long had a confused relationship with database transactions, partly because when I started designing the library back in 2018 I didn't yet have a great feel for how those worked in SQLite itself. Adding migrations to the core library made me determined to finally crack this nut, since transactions make migration systems a whole lot safer and easier to reason about. I ended up building this around a context manager which looks like this: SQLite supports Savepoints , and as a result can be nested to carry out transactions inside of transactions. It's pretty neat! This came about when I asked a coding agent to review all open issues and PRs for things that should be included in a 4.0 release since they would represent breaking changes if I added them later, and it correctly identified that compound foreign keys were exactly that kind of feature. I started with a breaking change to the table.foreign_keys introspection method, and then decided to see if Claude Fable 5 could handle the more fiddly job of integrating compound foreign key creation into the library. The API design it helped create felt exactly right to me - consistent with how the rest of the library worked already. Other notable changes include: This was the change that first pushed me to consider a breaking-change 4.0 version bump. I built this to help support sqlite-chronicle , which uses triggers to keep track of rows in a table that have been inserted, updated or deleted. Probably the most disruptive breaking change - I've had to update a few places in my own code to switch from to as a result. The flag was a later addition to allow column types (text, integer, real) to be automatically detected based on the data in a CSV. It should be the default, and releasing a 4.0 means I can make it so. The oldest issue addressed by this release - the underlying bug was opened (by me) in October 2020. See Upgrading from 3.x to 4.0 for details on backwards-incompatible changes. The detailed release notes for the features and fixes shipped during the 4.0 pre-release cycle are available in 4.0a0 , 4.0a1 , 4.0rc1 , 4.0rc2 , 4.0rc3 and 4.0rc4 . The upgrade guide was entirely written by Claude Fable 5, Claude Opus 4.8 and GPT-5.5. The same is true of the release notes. This is the kind of documentation I've slowly become comfortable outsourcing to the robots. It doesn't need to convince people of anything, or express any opinions - its job is to be as accurate and detailed as possible. I've reviewed the release notes closely and can confirm they are accurate and comprehensive. I released the first alpha of sqlite-utils 4.0 over a year ago . I've been dragging my heels on the stable release because of the amount of work it would take to track down and clean up the many other minor design flaws that a major version number allowed me to take on. Assistance from Claude Fable 5 (and to a lesser extent Opus 4.8 and GPT-5.5) gave me just the boost I needed to overcome inertia and make the most of the time I could afford to spend on this library. Fable has really good taste in API design, and is relentlessly proactive if you give it a more open goal. My most successful prompt was a review task that I issued against what I thought was the last release candidate: I tried this with GPT-5.5 xhigh in Codex Desktop and Fable 5 in Claude Code. GPT-5.5 wrote 5 Python scripts and didn't turn up anything particularly interesting - its final report is here . Fable 5 wrote 12 scripts , identified 4 release blockers and 10 additional issues in its report , and built a neat combined repro script , which, when run, output the following: I found myself agreeing with almost all of them. Here's the PR with 16 commits where we worked through them in turn. There's no doubt in my mind that sqlite-utils 4.0 is a significantly higher-quality release than if I had built it without the assistance of the latest frontier models. You are only seeing the long-form articles from my blog. Subscribe to /atom/everything/ to get all of my posts, or take a look at my other subscription options . Database migrations , providing a structured mechanism for evolving a project’s schema over time. ( #752 ) Nested transaction support via , plus numerous improvements to how transactions work across the library. ( #755 ) Support for compound foreign keys , including creation, transformation and introspection through table.foreign_keys . ( #594 ) Upserts now use SQLite’s syntax, detect existing table primary keys automatically and reject records that are missing required primary key values. ( #652 ) now executes immediately and rejects statements that do not return rows; use for writes and DDL. CSV and TSV imports now detect column types by default, while inserts into existing tables preserve those tables’ column types. ( #679 ) and no longer create lookup table records for all- values. ( #186 )

0 views

Accelerating Stream Processing Engines via Hardware Offloading

Accelerating Stream Processing Engines via Hardware Offloading Zhengyan Guo, Mingxing Zhang, Yingdi Shan, Kang Chen, Jinlei Jiang, and Yongwei Wu SIGMOD'26 This paper describes a trick to offload partitioning from CPU to NIC via clever use of RSS. The context of the paper is distributed systems for processing streaming queries, but the trick seems applicable to databases in general. Hash partitioning is a common divide-and-conquer technique to implement joins and aggregations. Here are some posts about papers that use this partitioning: SPID-Join: Skew-Resistant In-DIMM Joins Breaking Through the Memory Wall of OLTP Systems with PIM High-Performance Query Processing with NVMe Arrays: Spilling without Killing Performance Efficiently Processing Joins and Grouped Aggregations on GPUs RSS is a NIC feature whereby the NIC hashes select fields from incoming packet headers and uses the result to determine which CPU core to send the packet to. This enables efficient load balancing across CPU cores without reordering packets within a given flow (i.e., connection). Here is a previous post describing a clever way to extract more value out of RSS in cloud VMs: Enabling Fast Networking in the Public Cloud If you have many nodes cooperating to process a query, then the hash partitioning may span many nodes. For example, node A could hash the join/aggregation key of each row and then forward the row to either node B, C, or D, E depending on the hash value. This enables the join/aggregation work to be split across nodes B, C, D, and E. This is all fine and dandy from the perspective of node A. However, nodes B, C, D, and E likely have multiple CPU cores. How can one of these nodes execute their join/aggregation in parallel? The answer is recursive: partition the incoming rows again (using a different hash function) and have each CPU core process one of these smaller partitions. The paper focuses on the cost of partitioning the dataset, which can cost just as much as the partition join/aggregation step that follows it. The key insight is that the partition algorithm looks a lot like the RSS load balancing algorithm present in the NIC hardware. Here is the punchline: establish multiple network connections (using different ports) between node A and each of nodes B, C, D, and E. When node A partitions rows, it determines a specific connection (not node) to send each row to. This doesn’t improve performance at the sender, but it dramatically helps the receivers. Each receiver configures RSS such that all connections are spread across the CPU cores on the receiver. The NIC then distributes received packets to the appropriate CPU cores without any partitioning work on the receiving nodes. The one downside to this approach is load imbalances that occur due to data skew. If some join/aggregation keys are more common than others, then some CPU cores may be assigned more work than others. The paper proposes to dynamically monitor load imbalance at each receiver and reconfigure the RSS settings of the NIC to move connections off hot cores. Section 5 of the paper describes synchronization necessary to move a connection between cores in the middle of the query. This is a good mitigation, but as we’ve seen in this paper , RSS configuration is not uniformly exposed on cloud VMs. Fig. 8 has performance results across a number of benchmarks: Source: https://dl.acm.org/doi/10.1145/3769754 Dangling Pointers The solution is great, but asymmetric. I wonder if there is a way to get similar benefits on at the sending node (send side scaling)? Thanks for reading Dangling Pointers! Subscribe for free to receive new posts.

0 views
matduggan.com 2 weeks ago

Clickhouse is winning the Observability Wars

For roughly the last ten years, a meaningful percentage of my working hours have been spent thinking about observability. If you're not familiar with the term, "observability" is what we call it now that "monitoring" doesn't sound expensive enough. The actual work is unglamorous in that you collect a lot of logs, some metrics, a few traces, and then you give them to people. I generally like my job. I like that we're always trying new ideas and approaches. I like the fact that when things go wrong, the answer is almost always sitting there in the data, waiting to be found by whoever is patient enough to look. But I want to be honest with you: in ten years of doing this work, across a half-dozen companies and every observability platform you've heard of and a few you probably haven't, logs have never stopped being the worst part of the job. They were the worst part when I started. They are the worst part today. I fully expect them to be the worst part of this job forever until the robots rise up and rip my head off in one clean sweep. I've written about why logs are terrible before , so I'll spare you the full lecture and give you the short version. Every developer's expectations for logs are set by a single formative experience: the syslog box. Or a container running locally. Or tail -f on a production server they probably shouldn't have SSH'd into. The point is that at some early, tender moment in their career, they had an experience with logs that was flawless. They ran and something useful came back. They piped it into jq and got exactly what they needed. This experience is the observability equivalent of a first kiss. It ruins them for everything that comes after. Because here is the thing about that flawless experience: it works because the system is small, the volume is trivial, and the person querying is the same person who wrote the log line. There is no schema drift, no cardinality explosion, no cross-team consumer with dashboard expectations, no VP asking why the "revenue events" graph has a gap in it. Then there are forty services. Now there are four hundred. Now the logs are being consumed not just by developers but by customer service, who need to look up a specific user's failed checkout from Tuesday. And by the data team, who are quietly building a business-critical dashboard on top of a log line that a backend engineer is about to refactor without telling anyone. And by the on-call, who at 3 AM does not want to learn a new query language, does not want to think about index patterns, and would like the search bar to just work. So you have a technical problem — the volume is enormous, the shape is inconsistent, the queries are unpredictable — sitting on top of an expectations problem, which is worse. Developers want logs instantly, they want to run arbitrary operations on them, and they will not commit to a schema. Meanwhile the less-technical consumers of that same data want the dashboards to be stable forever, the UI to be forgiving, and the whole thing to feel like a normal product. These two audiences are, in most practical respects, at war with each other, and you are the diplomat. ClickHouse came out of Yandex, where it was built to chew through analytical queries against absurd volumes of clickstream data. It was not designed for observability. It just happens to be shockingly good at it, because clickstream data and observability data have a lot in common: high volume, append-heavy, time-ordered, mostly read in aggregate, and every so often you need to reach in and find one specific needle. You can run it yourself with Helm charts. You can point Grafana at it via the ClickHouse plugin, or use their own web UI, or bring your own frontend. Their docs are actually good, which I mention because it's rare enough to be worth flagging. I've never used their ClickStack setup though, so YMMV. For observability specifically, the OpenTelemetry Collector has a ClickHouse exporter, which means you can pipe OTLP data straight in and let it manage the initial schema for you. ClickHouse is designed to scan billions of rows and ingest an amount of data that, when you first see the numbers, makes you assume they're lying. They're not lying. You query it with SQL, which is a language that already exists and was not created by a startup two weeks ago. I'm ranting about logs and then I'm explaining why I like to administer Clickhouse more. Let me take a second and explain why Clickhouse is really good at logs at scale. Logs, as a data shape, have some peculiar properties. They're append-only. You never update a log line, and you almost never delete a single one, though you delete a lot of them at once when retention kicks in. They arrive roughly in time order, though never actually in order. They're read in bursts where nobody looks at logs for days, and then during an incident somebody wants to scan a billion of them in seconds. They're highly compressible, because most of the bytes in your logs are repeated: the same service names, the same hostnames, the same error strings, the same JSON keys, over and over and over again. And critically, when you query them, you almost always want either a narrow time range across all fields or an aggregation across a wide time range with a few filters. You very rarely want "give me one specific row by ID" the way you would from a transactional database. (There are exceptions when its something like GDPR or compliance logging which is its own subgenre of nightmares). In a row-oriented database — Elasticsearch, Postgres, MySQL — the data for a single log line is stored together on disk. If your log has 40 fields and your query only cares about 3 of them, tough luck, you're reading all 40 from disk anyway. The database will filter it in memory, but the disk I/O has already happened. ClickHouse stores each column separately. If your query says SELECT service, status_code, count() FROM logs WHERE timestamp > now() - INTERVAL 1 HOUR GROUP BY service, status_code, ClickHouse reads exactly three columns off disk: timestamp, service, and status_code. The other 37 columns in your schema might as well not exist. On observability data, where you often have dozens of attributes but any given query touches three or four, this is the difference between scanning 800GB and scanning 40GB. This is also why the compression numbers look absurd. Columnar data compresses far better than row-oriented data because the values within a single column are, by nature, similar to each other. A column of service_name values might have a hundred distinct strings across a billion rows. ZSTD eats that for breakfast. You'll routinely see 10–14x compression ratios on real observability data, compared to 2–3x for Elasticsearch. The amazing thing is that ClickHouse scales without changing shape. I don't know how else to say this. Every other observability backend I've worked with mutates as it grows. The architecture at 1 TB a day and the architecture at 10 TB a day are recognizably different systems, with different failure modes, different ops burdens, and different mental models. ClickHouse at 10 TB a day looks like ClickHouse at 1 TB a day with more shards. That's it. That's the pitch. That's the whole reason I'm writing this. Let me show you what I mean. At 1 TB a day, every modern observability stack is roughly okay. If you're at this scale, you can pick almost anything and be productive. The differences below are real but they're not yet painful. Here is the honest truth: at 1 TB a day, ClickHouse is not less complicated than its peers. It's roughly the same. Maybe slightly more, if you count the schema design work you have to do up front. You get 10–14x compression with ZSTD and proper codecs, the Altinity Operator handles keeper coordination and the whole thing runs in about seven pods. But you do have to design your schemas. ORDER BY keys matter enormously. There is no native PromQL, so metrics workflows go through the Grafana plugin or through chproxy and an adapter. Roughly $1.5–2.5K/month. If you took the diagrams at this tier and squinted, you'd say they're all in the same weight class. And you'd be right. Now watch what happens next. This is where the exponential curve kicks in for everybody except one of these. You'll notice, if you look at the diagram, that I basically just added shards. That's it. That's the change. Same operator, same query engine, same query language, same mental model. Rebalancing after adding shards is manual, which is a real trade-off — most teams pre-provision or use weighting on Distributed tables to sidestep it. Materialized views for dashboard rollups shift from "nice to have" to "essential." Roughly $7–11K/month. The gap between ClickHouse and everything else opens up here. It doesn't close. This is where most solutions genuinely stop working, in the sense that even a well-staffed internal team cannot keep up with the operational load. If you've read this far, the point is probably already obvious, but I want to say it directly. Every observability stack works at 1 TB a day. If you're small, pick whatever your team already knows. Life is short. We're all just waiting for the robots to kick our heads off like soccer balls. The question is not which stack works today. The question is which stack still resembles itself two years from now, when your data volume has 5x'd and your team has 2x'd and the person who originally designed the whole thing has left the company. Elasticsearch mutates. LGTM mutates. Datadog stays operationally simple but mutates financially into something that requires its own dedicated team of accountants and pipeline engineers just to keep the bill from spiraling. ClickHouse just gets wider. You add shards. That's the whole trick. There is a real cost to this: you have to eat the schema-design and query-engine complexity up front, at a scale where the other options are objectively easier. You will be, briefly, the one making things harder for your developers. They will not always appreciate this. But the trade you're making is that their experience — and yours — remains roughly the same as the data grows by an order of magnitude, and the next order of magnitude, and probably the one after that. I have spent ten years watching observability stacks change shape underneath me while I tried to keep them running. ClickHouse is the first one that hasn't and that has been able to actually scale with me . That's pretty incredible. A relatively vanilla Elasticsearch cluster with Logstash providing some buffer between ingest and the Lucene indexes. Users get full-text search, which is genuinely good — this is the thing Elasticsearch is actually best at, and at this scale it delivers. Mapping explosions are already a background risk with mixed data, so dynamic mapping needs to be disabled or carefully templated from day one. ILM policies (hot → warm → delete) are non-optional even at this size, because forgetting to set them is how you get paged on a Saturday about disk pressure. Roughly $6–9K/month. Nothing too crazy. Alloy (formerly Grafana Agent, RIP) unifies the collection story into a single daemon, which is nice. Loki works well as long as you spend some time educating developers on how to attach useful labels — a conversation you will have many times, with many people, for the rest of your career. Mimir and Tempo largely do what it says on the tin. Roughly $3.5–5K/month. At 1 TB a day, Datadog is genuinely great. This is the scale it was built for, and it shows. You install the agent, you look at dashboards, you go home. There is almost nothing to think about, which is the entire point. You can already see the shape of the cost problem lurking in the diagram — the metered pipelines, the indexed-vs-ingested logs distinction, the custom metrics cardinality tax — but at this scale it's manageable. Roughly $45–75K/month, though negotiated pricing varies enough that I'd take that number with a grain of salt the size of a fist. Datadog's whole pricing philosophy is that they save you a full-time engineer. I think that framing is somewhat deranged, but they are extremely rich and I am not, so consider your source. Kafka is no longer optional. At 5 TB a day, direct writes into Elasticsearch cause bulk-reject storms and backpressure that will absolutely take your cluster down during a traffic spike. So now you're running Kafka, which means you're either running Kafka well or you're about to have a second, entirely different set of problems. Shard math becomes critical — at 50GB target shards, you're minting ~200 shards a day counting replicas, and your cluster state size becomes its own concern. You almost certainly need Elastic's commercial license for searchable snapshots and the frozen tier. Roughly $40–55K/month before licensing. That but Kafka You are now in microservices mode, whether you wanted to be or not. That means 65+ pods across three separate systems, each with its own compaction pipeline, its own hash ring, its own memcached tier. The gossip/memberlist ring becomes a real operational concern; ingester rollouts require careful -ingester.autoforget-unhealthy tuning, and if you get it wrong you either lose data or duplicate it. Roughly $22–32K/month. The operational complexity is still low, in that you don't run any servers. But you now need a full pipeline team whose entire job is reducing your Datadog bill. Exclusion filters, sampling rules, cardinality caps, tag allow-lists, the whole apparatus. This is what I call the "you build a system to avoid using the system you're paying for" trap, and once you're in it, you are in it forever. Roughly $180–350K/month, depending on how aggressive the pipeline team gets. This is also where you are basically fighting with your SaaS provider all the time, pouring over their billing documentation to figure out how to reduce costs. It's a hostile relationship and one I don't enjoy. You are now running three separate Elasticsearch clusters — one for logs, one for metrics, one for APM — federated through Cross-Cluster Search. Hot-tier NVMe cost dominates the bill. This is the scale at which teams start seriously evaluating alternatives, and where a lot of the recent migrations to ClickHouse have originated. Roughly $95–140K/month plus commercial licensing. You need people who are legitimate experts on Elasticsearch. Now thankfully Elastic just laid a ton of those people off, so they're probably possible to get, but still. Running this thing at this size is very complicated . Around 180+ pods, zone-aware everything, split-and-merge compaction, per-tenant limits, shuffle sharding to prevent noisy neighbors. You almost certainly have a dedicated observability platform team of three to five engineers at this point. If you don't, get ready for a bad fucking time. Roughly $55–85K/month. Still very easy to run, in the strict sense that you don't run anything. But your bill is now measured in six or seven figures a month, and the org has almost certainly built a pre-processing pipeline team whose entire existence is dedicated to reducing that bill. Most companies at this scale have gone hybrid: Datadog for APM and high-value metrics, self-hosted (increasingly ClickHouse) for logs. The complexity paradox at this scale is that you now have Datadog's simplicity plus your pipeline complexity plus a second self-hosted stack. Pricing is all over the goddamn place. You might be over a $1 million a month here. Look at the diagram and then look back at the 1 TB diagram. It's the same diagram. There are more shards. That's the difference. Materialized views for rollups are now mandatory rather than optional. Schema design mistakes you made two years ago will start to hurt, so hopefully you didn't make many. Rebalancing after adding shards is still manual; most teams pre-provision or use clickhouse-copier or a dual-write migration when they need to grow the cluster. Kafka starts to become useful as a buffer for very bursty ingest, though it's not required. Roughly $18–28K/month.

0 views

F3: The Open-Source Data File Format for the Future

F3: The Open-Source Data File Format for the Future Xinyu Zeng, Ruijun Meng, Martin Prammer, Wes McKinney, Jignesh M. Patel, Andrew Pavlo, and Huanchen Zhang SIGMOD'26 F3 is a file format for columnar data (e.g., Parquet ) that is designed to be efficient and extensible. The optimizations make sense, the extensibility mechanism is ingenious , dangerous , fascinating. The key assumption made by this paper is that the hardware and software will continue to improve. It is hard to argue with that. The trouble is that interoperable formats like Parquet take a snapshot of the state-of-the-art and freeze it in a specification. Some innovations that are invented after the format is frozen are incompatible with existing formats because they require a different data layout. Section 1 of the paper refers to many examples related to compression, indexing, and filtering. The goal of F3 is to be general enough to allow seamless incorporation of future innovations without changing the F3 spec nor F3 decoder implementations. Fig. 2 illustrates an F3 file: Source: https://dl.acm.org/doi/10.1145/3749163 A file consists of a metadata and a set of row groups. A specific row group contains data for all columns and a subset of rows. F3 contains incremental improvements over existing columnar formats, for example: F3 metadata supports random access, which is important for operations that only need to access a smaller percentage of all columns. F3 decouples file I/O from a row group storage. The rows associated with a given column in a row group are further subdivided into , which are actually stored. This allows row groups to be sized for efficient row-group level filtering, while the IO unit size is tuned to minimize working set while also amortizing the fixed costs associated with file I/O. F3 allows flexible . Each IO unit can contain a dedicated dictionary, or multiple IO units can share a dictionary. Columns with low cardinality will benefit from smaller dictionary scopes, whereas columns with high cardinality do better with larger dictionary scopes. The stand-out feature for F3 is the yellow block in the block. The idea is that an F3 file can contain within it the WebAssembly code needed to decode the encoded values in an IO unit. If someone invents a brilliant new encoding method that works well with some data sets, they can ship the decoder right along with the data set. Storage of the WASM code shouldn’t be too much of an issue, because the storage cost is amortized across all row groups. The big questions are performance and security. Section 6.2 has some comments on this. In theory, the WASM specification is air-tight, and a bug-free implementation should be able to securely run arbitrary WASM code in-process. WASM also supports performance optimizations like parallel compilation and SIMD instructions. Something I don’t see in the paper is a discussion about how filtering interacts with WASM decoding. I suppose the extensibility could only be used for decoding, and filtering could be hard coded into F3, but that seems against the extensible spirit of F3. Fig. 11 shows the working set reduction from decoupling IOUnit size from row group size: Source: https://dl.acm.org/doi/10.1145/3749163 Table 3 shows how flexible dictionary scopes allow one to trade encoding time for compression ratio (lower relative CR numbers mean smaller files on disk): Source: https://dl.acm.org/doi/10.1145/3749163 Fig. 15 quantifies WASM overhead by comparing decode time for hard coded F3 decoder implementations vs the same algorithms expressed in WASM: Source: https://dl.acm.org/doi/10.1145/3749163 Fig. 16 shows potential savings associated with using WASM extensibility to implement a custom decoder from the literature. Source: https://dl.acm.org/doi/10.1145/3749163 Dangling Pointers I wonder how well WASM decoders can be implemented on other hardware architectures. Is WASM the ideal language for expressing this, or convenient standard that already exists? Thanks for reading Dangling Pointers! Subscribe for free to receive new posts. Source: https://dl.acm.org/doi/10.1145/3749163 A file consists of a metadata and a set of row groups. A specific row group contains data for all columns and a subset of rows. F3 contains incremental improvements over existing columnar formats, for example: F3 metadata supports random access, which is important for operations that only need to access a smaller percentage of all columns. F3 decouples file I/O from a row group storage. The rows associated with a given column in a row group are further subdivided into , which are actually stored. This allows row groups to be sized for efficient row-group level filtering, while the IO unit size is tuned to minimize working set while also amortizing the fixed costs associated with file I/O. F3 allows flexible . Each IO unit can contain a dedicated dictionary, or multiple IO units can share a dictionary. Columns with low cardinality will benefit from smaller dictionary scopes, whereas columns with high cardinality do better with larger dictionary scopes.

0 views
Jack Vanlightly 3 weeks ago

Can We Agree on a Storage/Workload Architecture Taxonomy?

The lines between transactional systems, analytical systems, hybrid systems, and shared storage architectures are getting blurry. This post proposes a small taxonomy for describing the different ways systems, workloads, storage tiers, visibility, and durable copies relate to each other. OLTP, OLAP, HTAP, and now LTAP? We can think of the first two as two types of workload which have specialized query engines and storage systems to support them. OLTP such as the RDBMS like Postgres and MySQL use row-based storage engines. OLAP, such as Clickhouse, cloud data warehouse and the lakehouse use column-based storage. HTAP is a hybrid workload system: one system -> both transactional and analytical workloads. The HTAP system therefore has specialized storage and specialized query engine to stitch together the row-based and columnar data. So far, we’re dealing with a single system. A Postgres (OLTP), a Clickhouse (OLAP), a SingleStore or TiDB (HTAP). So what is the recent Databricks’ LTAP announcement? LTAP is the two workloads (OLTP and OLAP) but also two systems (e.g. Postgres and lakehouse/Spark) and some blend of two different storage systems. As well single single vs multi-system, single vs multi-workload, there are other relevant concepts such as tiering and materialization: A single system can tier (move) data from hot to cold storage (for cost efficiency). One system, one copy, two tiers. Hot and cold might be the same storage format (both row-based or both columnar), or might be different formats (hot is row-based, cold is columnar). We can have two systems share the same storage tier. System A tiers (move) hot data to the storage of System B. Two systems, one copy, though System B doesn’t see the newest data yet which only exists on A. Materializing One system can materialize (copy) data into another system. Two systems, two copies. Note when I say “copy of the data”, I mean durable copy, so caching doesn’t count. If the number of copies really matters to you as a metric, then maybe caching does count, depending on how much cached data you need to make it work? If only life were simpler. It would be nice to have some shared vocabulary around this, so we can talk about system architecture more easily. So I defined some terms last year for this, and expanded it as seen below. Vis means Visibility (when is data available in the other workload). The broad classification scheme: Single tier, one system, one workload. Example: Postgres with SSD, single tier CockroachDB, standard Kafka cluster. Internal Tiering, one system, one workload, commonly tiers from hot to cold storage for cost efficiency, e.g. hot=SSD, cold=S3. Though tiering could also serve other purposes than cost. Example: Apache Kafka tiered storage, ClickHouse MergeTree tiered storage. Hybrid-Sync (aka HTAP), one system, two workloads, two or more storage with potentially different formats/tiers, e.g. hot row-based data on SSD, long-term columnar data on S3. Data is immediately available to both workloads (e.g. OLTP queries and OLAP queries). Example: SingleStore and TiDB (Pingcap). Hybrid-Async , one system, two workloads. Like Hybrid-Sync except hot row-based data is asynchronously tiered to long-term columnar format. OLAP queries do not see the very newest data. Example: Snowflake Hybrid tables. Materializing , two workloads, two systems, two copies. System A copies data to System B. Each system is dedicated to one workload, with specialized query engine and storage. Example: ETL in general, many Kafka-compatible services have automatic Iceberg materialization of topics e.g. Confluent Tableflow, Databricks Synced tables asynchronously materialize from lakehouse to lakebase (Postgres). Shared Tiering , two workloads, two systems. one copy across hot tier + shared colder tier (e.g. hot row-based data on SSD for System A, colder columnar data on S3 for System A + B). Example: Apache Fluss tiers hot data (Fluss servers) to lakehouse (lakehouse is a shared tier), LTAP. Potentially, a 7th and 8th category could hypothetically exist: Shared-Sync-RR and Shared-Sync-MM. Two systems, two workloads, one synchronous storage (each write is immediately visible in the other system. Read-replica (RR) variant has one master system and one read-only system (e.g. writes to Postgres are immediately visible for reads in lakehouse). Multi-master (MM) allows both systems to write (hard!!). At the time of writing the details on LTAP are scarce, but it seems like LTAP will fall into Shared Tiering. The thing that differentiates HTAP from LTAP is that HTAP is a single hybrid system which makes data visible to both transactional and analytical queries at the same time. LTAP is a way of unifying the data of two different systems (each targeting a different workload) and sharing the colder data such that there is no (durable) data copy required. It is fundamentally asynchronous: hottest data is only in System A and the remaining colder data is stored in System B but made available to System A (as it’s cold tier). Of course LTAP could potentially move towards the hypothetical category Shared-Sync-RR , given both systems exist in the same platform, then it gets murky again because its one platform, its veering towards HTAP (Hybrid-Sync). One thing that the marketing material of unified OLTP-OLAP system commonly glosses over are the different data models used in each, such as Third Normal Form (3NF) common in OLTP and Kimball (star and snowflake schema) common in analytics. This adds another dimension, on top of query engine, storage layout and storage substrate. If you want 3NF for OLTP and Kimball for analytics, then it’s probably going to be Materialization (as star schema is not viable as a cold tier for 3NF). What you you think of this broad classification scheme? Find on me social media :) ps, some thoughts on data copies… With Shared Tiering, you can think of the data-copy question as a dial: Dial it to no-copies-at-all means evicting data as soon as it has been tiered. Lower storage cost, but maybe it would be good to hang onto to the hot data a little longer for performance. Dial it to lots-of-data-overlap means aggressively tiering to System B but hanging onto the data in System A for the better performance profile, at the additional storage cost. And technically it would now count as cached data which might not count as a data copy, depending on how you define that. However, the data-copy question is also murky with Materialization. Because we have two (or more) independent systems, each can potentially use independent data expiration policies. For example, in Kafka, it might store 7 days, but in the lakehouse, it might store 7 years. In that case, while theoretically it is a two-copy system, the total duplication would only be 0.0027%. I generally dislike the whole “zero-copy” or “one-copy” thing, it’s too much marketing. Focusing on how many copies you have is just weird as a primary design point when you’re building data systems, the real world is more nuanced. Tiering A single system can tier (move) data from hot to cold storage (for cost efficiency). One system, one copy, two tiers. Hot and cold might be the same storage format (both row-based or both columnar), or might be different formats (hot is row-based, cold is columnar). We can have two systems share the same storage tier. System A tiers (move) hot data to the storage of System B. Two systems, one copy, though System B doesn’t see the newest data yet which only exists on A. Materializing One system can materialize (copy) data into another system. Two systems, two copies. Single tier, one system, one workload. Example: Postgres with SSD, single tier CockroachDB, standard Kafka cluster. Internal Tiering, one system, one workload, commonly tiers from hot to cold storage for cost efficiency, e.g. hot=SSD, cold=S3. Though tiering could also serve other purposes than cost. Example: Apache Kafka tiered storage, ClickHouse MergeTree tiered storage. Hybrid-Sync (aka HTAP), one system, two workloads, two or more storage with potentially different formats/tiers, e.g. hot row-based data on SSD, long-term columnar data on S3. Data is immediately available to both workloads (e.g. OLTP queries and OLAP queries). Example: SingleStore and TiDB (Pingcap). Hybrid-Async , one system, two workloads. Like Hybrid-Sync except hot row-based data is asynchronously tiered to long-term columnar format. OLAP queries do not see the very newest data. Example: Snowflake Hybrid tables. Materializing , two workloads, two systems, two copies. System A copies data to System B. Each system is dedicated to one workload, with specialized query engine and storage. Example: ETL in general, many Kafka-compatible services have automatic Iceberg materialization of topics e.g. Confluent Tableflow, Databricks Synced tables asynchronously materialize from lakehouse to lakebase (Postgres). Shared Tiering , two workloads, two systems. one copy across hot tier + shared colder tier (e.g. hot row-based data on SSD for System A, colder columnar data on S3 for System A + B). Example: Apache Fluss tiers hot data (Fluss servers) to lakehouse (lakehouse is a shared tier), LTAP. Dial it to no-copies-at-all means evicting data as soon as it has been tiered. Lower storage cost, but maybe it would be good to hang onto to the hot data a little longer for performance. Dial it to lots-of-data-overlap means aggressively tiering to System B but hanging onto the data in System A for the better performance profile, at the additional storage cost. And technically it would now count as cached data which might not count as a data copy, depending on how you define that.

0 views
Simon Willison 3 weeks ago

sqlite-utils 4.0rc1 adds migrations and nested transactions

sqlite-utils is my combined Python library and CLI tool for working with SQLite databases. It provides an extensive set of higher-level operations on top of Python's default sqlite3 package , including support for complex table transformations , automatic table creation from JSON data and a whole lot more. I released sqlite-utils 4.0rc1 , the first release candidate for sqlite-utils v4. The major version bump indicates some (minor) backwards incompatible changes, so I'm interested in having people try this out before I commit to a stable release. There are two significant new features in this RC compared to the previous 4.0 alphas. The first is support for database migrations . This isn't a completely new implementation - it's a slightly modified port of the sqlite-migrate package I released a few years ago. I think that package has proved itself over time, so I'm now ready to bundle it with directly. Here's what a set of migrations in a file looks like: This defines a set of two migrations, one creating the table and another adding a column to it. You can then run those migrations either using Python: Or with the command-line command: The system is deliberately small: it doesn't provide reverse migrations, so any mistakes you make should be fixed by deploying a fresh migration to undo them. Its predecessor has been used by LLM and various other projects for several years, so I'm confident that the design is stable and works well. The new migrations feature is documented here . This feature is a lot less exercised than migrations, so it deserves more attention from testers. Previously, mostly left transaction management up to its users, via a construct that reused the mechanism directly. SQLite supports nested transactions in the form of savepoints, so I wanted an abstraction that could make those as easy to use as possible. I borrowed the terminology "atomic" from Django and Peewee. Here's what the new API looks like: More details in the documentation . The backwards incompatible changes in v4 were described in the alpha release notes. For 4.0a0 : And for 4.0a1 : You can install the new RC like this: Or try the CLI version directly with like this: Come chat with us about it in the sqlite-utils Discord channel , or file any bugs in GitHub Issues . You are only seeing the long-form articles from my blog. Subscribe to /atom/everything/ to get all of my posts, or take a look at my other subscription options . Upsert operations now use SQLite's syntax on all SQLite versions later than 3.23.1. This is a very slight breaking change for apps that depend on the previous followed by behavior. ( #652 ) Python library users can opt-in to the previous implementation by passing to the constructor, see Alternative upserts using INSERT OR IGNORE . Dropped support for Python 3.8, added support for Python 3.13. ( #646 ) is now provided by the sqlite-utils-tui plugin. ( #648 ) Test suite now also runs against SQLite 3.23.1, the last version (from 2018-04-10) before the new syntax was added. ( #654 ) Breaking change : The method now only works with tables. To access a SQL view use instead. ( #657 ) The and methods can now accept an iterator of lists or tuples as an alternative to dictionaries. The first item should be a list/tuple of column names. See Inserting data from a list or tuple iterator for details. ( #672 ) Breaking change : The default floating point column type has been changed from to , which is the correct SQLite type for floating point values. This affects auto-detected columns when inserting data. ( #645 ) Now uses in place of for packaging. ( #675 ) Tables in the Python API now do a much better job of remembering the primary key and other schema details from when they were first created. ( #655 ) Breaking change : The and mechanisms no longer skip values that evaluate to . Previously the option was needed, this has been removed. ( #542 ) Breaking change : Tables created by this library now wrap table and column names in in the schema. Previously they would use . ( #677 ) The CLI argument now accepts a path to a Python file in addition to accepting a string full of Python code. It can also now be specified multiple times. ( #659 ) Breaking change: Type detection is now the default behavior for the and CLI commands when importing CSV or TSV data. Previously all columns were treated as unless the flag was passed. Use the new flag to restore the old behavior. The environment variable has been removed. ( #679 )

0 views
Simon Willison 3 weeks ago

Datasette Apps: Host custom HTML applications inside Datasette

Today we launched a new plugin for Datasette, datasette-apps , with this launch announcement post on the Datasette project blog. That post has the what , but I'm going to expand on that a little bit here to provide the why . Datasette Apps are self-contained HTML+JavaScript applications that run in a tightly constrained sandbox hosted on your Datasette application. They can use JavaScript to run read-only SQL queries against data in Datasette, and can run write queries too if you configure them with some stored queries . Here's a very simple example and a more complex custom timeline example - the latter looks like this: Apps are allowed to run JavaScript and render HTML and CSS. They are limited in terms of access - the they run in prevents them from accessing cookies or localStorage and they also have an injected CSP header (thanks to this research ) which prevents them from making HTTP requests to outside hosts, preventing a malicious or buggy app from exfiltrating private data. Datasette Apps started out as my attempt at building a Claude Artifacts mechanism for Datasette Agent , but I quickly realised that the sandboxed pattern is interesting for way more than just adding custom apps to the interface surface and promoted it to its own top-level concept within the Datasette ecosystem. They're also a fun way to turn my multi-year experiment in vibe-coded HTML tools into a core feature of my main project! You can try out Datasette Apps by signing in with GitHub to the agent.datasette.io demo instance. Since the very first release, Datasette has offered a flexible backend for creating custom HTML apps via its JSON API. One of my earliest Datasette projects was an internal search engine for documentation when I worked at Eventbrite - it worked by importing documents from different systems into SQLite on a cron and then serving them through a Datasette instance with a custom HTML+JavaScript search interface that directly queried the Datasette API. I had client-side JavaScript constructing SQL queries, which originally was intended as an engineering joke but turned out to be a really productive way of iterating on the app! That project, combined with my experience building my HTML tools collection and my experiments with Claude Artifacts , has convinced me that adding a Datasette-style backend to a self-contained HTML frontend is an astonishingly powerful combination. Imagine how much more useful Claude Artifacts could be if they had access to a persistent relational database. That's what I'm building with Datasette Apps! Here are a few of the ideas and patterns I've figured out building this which I think have staying power. This is the magic combination that makes Datasette Apps feasible in the first place. I need to run untrusted HTML and JavaScript on a highly sensitive domain - an authenticated Datasette instance can contain all sorts of private data. The attribute lets me run that untrusted code in a way that cannot interact with the parent application - it can't read the DOM, or access cookies, or steal secrets from . It can however use and friends to load content (or exfiltrate data) from other domains. But... it turns out if you start an HTML page with a header you can set additional policies that lock down access to other domains. I was worried that malicious JavaScript would be able to update or remove that header but it turns out that doesn't work - once set, the CSP policy is immutable for the content of that frame. Having locked down those iframes to the point that they couldn't do anything interesting at all, the challenge was to open them back again such that they could run an allow-list of operations, starting with read-only SQL queries against specified databases. I built the first version of this with , which allows a child iframe to send messages to the parent window. I created a simple protocol for requesting that the parent run a SQL query - the parent could then verify it was against an allow-listed database before executing it. One of the LLM tools, I think it was GPT-5.5, suggested that on its own can be exploited if the iframe somehow loads additional code from an untrusted domain. I don't think that applies to Datasette Apps, but I also believe in defense in depth, so I had GPT-5.5 help me port to a MessageChannel() based transport instead. has the advantage that if a page navigates to somewhere else the channel closes automatically, removing any chance of executing commands sent from an untrusted external page. If you navigate to the timeline demo and search for the string you'll pull in some search results that embed images from the domain. This domain is not in the CSP allow-list, so it trips an error. Those errors are captured and transmitted back to the parent frame, where they can be displayed in a useful error log. This is meant to make hacking on apps more productive by surfacing otherwise-invisible problems. I built an experiment demonstrating that you can even turn this into a one-click-to-allow mechanism for building the CSP allow-list based on what breaks, but I haven't integrated that idea into just yet. SQL queries are also visibly logged - scroll to the bottom of the timeline page to see that in action. I want apps to be able to conditionally write to the database, but this is an even more dangerous proposition than SQL reads! My solution involves Datasette's stored queries feature, rebranded from "canned queries" and given a major upgrade in the recent Datasette 1.0a31 - work that was directly inspired by Datasette Apps. Users can create a stored write query that performs an insert or update, then allow-list that specific query for an app to use. Usage from code inside an app looks like this: I'm only just beginning to explore the possibilities this unlocks myself, but my goal is to support full read-write applications built safely as Datasette Apps. The Datasette Apps plugin has no dependency on LLMs at all, but these self-contained apps are the perfect shape to be written by a modern LLM. The create app form includes a copyable prompt at the end. This prompt has everything a model needs to know to build a new app, including the schema of any selected databases. This means you can click "copy", paste it into ChatGPT or Claude or Gemini, tell it what you need, and there's a good chance the model will spit out the code necessary to build the app. If you have Datasette Agent installed your AI assistant will also gain tools to both create new apps and edit existing ones, Claude Artifacts style. Datasette Apps started life back in April as datasette-agent-artifacts , a plugin I have since renamed to keeping only its editing tools . I built that as one of the first plugins for Datasette Agent , to help get the plugin hooks into the right shape. That first prototype was mainly built using Claude Opus 4.6 in Claude Code. When I switched track to Datasette Apps I started with a plan constructed using Codex Desktop and GPT-5.5 xhigh, based on extensive dialog and feeding in both and other prototypes I had built. Most of the work that followed stuck with Codex, but in the few short days that we had access to Claude Fable 5 I had it run a security evaluation of the product (an ability that would get it banned by the US government shortly afterwards) and it found a very real problem. I was allowing users to allow-list CSP hosts for their apps, but Fable pointed out the following attack: That's clearly unacceptable. I fixed it by restricting the ability to allow-list any domain to a new permission, which is intended just for trusted staff. Site administrators can also configure Datasette with a list of , which regular users can then select. This means you can do things like allow and your users will be able to build apps that load extra JavaScript libraries from the cdnjs CDN. I've reviewed Datasette Apps extremely closely, especially the security-adjacent parts of it. The critical sandbox and CSP configuration are based on multiple AI-assisted prototypes and tests. I'm really pleased with this initial release. Datasette is growing beyond its origins as an application for serving read-only data into a much richer ecosystem of tools for doing useful things with that data once it has been collected. Datasette's roots are in data journalism. I've always been interested in the question of what comes next after a journalist gets their hands on a giant dump of data about the world. Datasette supports exploring and publishing it. Datasette Agent adds interrogating it with AI assistance. Now Datasette Apps expands that to building custom interfaces and visualizations to help unlock the stories that are hidden within. You are only seeing the long-form articles from my blog. Subscribe to /atom/everything/ to get all of my posts, or take a look at my other subscription options . A less privileged user with permission creates an app that queries SQLite for all available tables and selects and exfiltrates all of the data to a host they had allow-listed via CSP. They then trick an administrator user with access to private data into visiting their app. ... and the app can now run queries as that user and steal their private data!

0 views
Lalit Maganti 4 weeks ago

syntaqlite 0.6: SQLite dot commands and pyodide

Since my original launch post for syntaqlite, I’ve been quietly working away on it in the background. A lot of the work has been fixing correctness bugs which I discovered as I integrated it into production as the parser for PerfettoSQL in the Perfetto trace processor: as I wrote previously, this has been my dream for over 8 years so it’s amazing to see it finally realized. Just today, I released syntaqlite 0.6 , the most interesting release since the original launch, so I wanted to talk about what’s new. The biggest step forward for real world applicability is that we now support SQLite dot commands : SQLite scripts are very common in the wild and in the past we would simply error on dot commands like and , causing spurious warnings and an inability to format files like this properly. Now, these lines will be silently ignored while still parsing, formatting and validating the SQL inside!

0 views
Hugo 1 months ago

The Transactional Outbox Pattern with PostgreSQL and RabbitMQ

How do you write to two different systems, for example RabbitMQ and PostgreSQL? You might say it's pretty straightforward. But what happens if there's an error, if the SQL transaction fails? You end up with a message published but for an operation that never actually took place. This is a relatively common problem and we're used to handling it with 2 Phase commit transactions. The idea being to introduce a transaction across all operations to external systems, including RabbitMQ here. It's a somewhat complex mechanism that requires an additional coordinator to ensure everything goes well before validating the write across all systems. But there are several problems. The first is that it's not possible to do 2PC transactions with RabbitMQ (also called XA transactions). Which, you'll agree, is already a relatively significant problem in itself. We could use ActiveMQ which supports 2PC transactions. But on the other hand, it would be a shame to write an article about PostgreSQL and RabbitMQ just to conclude that you need to use ActiveMQ instead, right? The second problem is that in any case, a 2PC transaction penalizes the overall system performance. To synchronize two or more systems, not only will the total operation time never be less than the minimal time of the slowest system, but you also add a cost related to coordination. The last problem is that you degrade overall availability. Availability being the multiplication of the average availability of each system. For example with two systems with 99% availability, the total system achieves 98% availability. $0.99 \times 0.99 = 0.9801$ And that was basically the state of my knowledge until yesterday. I had already used XA transactions, I had already dealt with distributed systems issues and I've worked on plenty of mitigation scenarios for this type of problem. Yesterday I added RabbitMQ to Writizzy's stack and I used another solution: the Transactional Outbox pattern. While the underlying concept isn't necessarily new, this specific pattern was popularized by Chris Richardson between 2014 and 2016. The concept is "simple": The code becomes We now have only a single transaction , and the guarantee that the event is published only if the transaction succeeds . Event that will be sent to RabbitMQ later. Now, to actually send to RabbitMQ, you need a job to read the table: Note the use of SchedulerLock here which allows the use of schedulers in a multi-node context, to avoid simultaneous reading by multiple applications. This allows replacing Quartz which I frequently used until now. And you need the publication service code Note that we've enabled RabbitMQ's publisher confirms mode and we're waiting for write confirmation: But you could say to me: "you have the problem of double write to two systems again". And... you're not wrong. But it's simpler here. I only have 3 cases: The 3rd case is important, it will happen so you need to handle it. This implies that each consumer must be able to be idempotent on message reception. That is, each consumer must be able to accept the same event twice without incident. Here strategies are multiple: But there's an issue, if RabbitMQ goes down, if it's unreachable, if the disk is full, we're going to log the exception and with a batch running every 500ms I'll let you imagine the astronomical amount of logs that will produce. So we need a Circuit Breaker . Conveniently, we can use resilience4j . With a circuit breaker, we'll cut off sending to Rabbit in case of error and wait a bit before retrying. I'll leave the code here but won't detail it, that would be the subject of another article. As I said earlier, I was well acquainted with the XA transaction mechanism and the state of my knowledge for solving this problem stopped there. I wondered with curiosity if Claude would propose a more elegant implementation and I was rather surprised. In this specific kind of case, it was the perfect opportunity to try learning with AI instead of just suffering code we don't understand. Letting AI write code without oversight, unsurprisingly it's rarely good. You need an expert eye and ultimately the code produced remains our responsibility and we must be able to understand it. So you need to find a middle ground between letting it do its thing and micro-managing the AI. By giving it my constraints (stemming from my experience), it was the agent that came up with the Transactional Outbox pattern proposal. And while I was initially a bit skeptical, I tried to understand each part of the code to make it my own by asking multiple questions. The code wasn't perfect, it was following these exchanges that was added: So yes, it wasn't a smooth plan but it was a good way for me to force myself to get updated, do some research and in short, learn new things. Beyond the technical aspect of this post, I mainly wanted to illustrate the method I use to code with an agent, which allows me to combine productivity AND craft. rely only on the database to publish an event (step 1) read the table in an asynchronous job (step 2) publish to the message queue from the job (step 3) Everything goes well, nothing to say, it's perfect. RabbitMQ fails => we go into the exception, we log, we exit, the message stays in the table and will be retried The PostgreSQL transaction fails. We go into the exception but the message is already sent!! read the message ID and store it somewhere to check that we don't do the operation twice, ideal for calls to external services trigger idempotent operations (Example: set status = pending, even if we do it twice, it doesn't pose a problem) the publish confirm pattern resilience with resilience4j the scheduler lock to avoid multi-node issues the addition of messageid in headers (for deduplication) claude had added a state management on messages that wasn't relevant

0 views
Dangling Pointers 1 months ago

Accelerating Transactional Execution via Processing-In-Memory

Accelerating Transactional Execution via Processing-In-Memory André Lopes, Daniel Castro, and Paolo Romano EUROSYS'26 This paper describes a way to implement OLTP for a processing-in-memory architecture. As with other academic research, it uses UPMEM ( here are two summaries of papers that rely on UPMEM). Something I found surprising in this work is conflicts can cause transactions to abort, even if all transactions only access data in the same UPMEM bank. A UPMEM DIMM is like a DRAM DIMM, but each bank contains a multi-threaded in-order core which can access data from the bank it is co-located with. This paper calls these processors DPUs (some other papers call them IDPs). The only way for two DPUs to communicate with each other is for the host CPU to read data from one bank and write it into another. The system described in this paper is called PIM-TIDE. It assumes that transactions come pre-sliced into computational graphs comprising subtransactions. A single subtransaction only accesses data within one bank (and thus executes on a specific DPU). Users of PIM-TIDE do not need to know exactly which data words a subtransaction will access, but they do need to be able to restrict a subtransaction to only access data from a specific bank. This works well on TPC-C style transactions where most of the database can be partitioned on . The host CPU groups transactions into batches and sends work to the DPUs one batch at a time. Within a batch, transactions are categorized into two groups: Local transactions execute entirely within a DPU Distributed transactions execute across multiple DPUs All subtransactions associated with distributed transactions within a batch are assigned a unique sequence number, which determines the order in which the subtransactions will commit. Local transactions are not preassigned to a commit order. DPUs first process all distributed subtransactions in a batch and then execute all local subtransactions. Algorithm 3 illustrates PIM-TIDE’s concurrency control scheme for dealing with intra-DPU conflicts between subtransactions. is stored in fast on-chip memory and is indexed by a hash of the word address. If a transaction aborts, state can be rolled back and the transaction is retried. All transactions assigned to a DPU will commit eventually. Inter-DPU conflicts are handled by deterministic concurrency control (i.e., the pre-assigned sequence numbers). Source: https://dl.acm.org/doi/10.1145/3767295.3803621 Results Fig. 3 compares performance of PIM-TIDE vs a CPU baseline for a mix of TPC-C transactions, wow that is a significant speedup. I believe all transactions/subtransactions are written by hand in C code that is compatible with UPMEM DPUs. Source: https://dl.acm.org/doi/10.1145/3767295.3803621 Dangling Pointers TPC-C is easy to partition; I wonder how well PIM-TIDE does on workloads that are not as partitionable. Also, this scheme doesn’t seem to allow for interactive transactions, how important are those in the real world? Thanks for reading Dangling Pointers! Subscribe for free to receive new posts. Local transactions execute entirely within a DPU Distributed transactions execute across multiple DPUs

0 views
blog.philz.dev 1 months ago

Page on the client, really, even for 1M rows

Too often do we find ourselves clicking, and clicking, and clicking "Next" through trivial amounts of data--a few thousand rows--or fighting a search box that doesn't really work. Though the DOM genuinely doesn't seem to like tens of thousands of rows, a little virtualization (e.g., via the excellent DataTables library) goes a long way. If you're up for some DuckDB-Wasm fun, you can page through a million row just fine. Obligatory demo below. (Demo vibe-coded, unlike this text.) Thanks, OpenStreetMap data. → Open the demo full-screen

0 views
sunshowers 1 months ago

iddqd, or the hardest kind of unsafe Rust

Mirrored from the canonical version on the Oxide blog . I’m the main author of , a Rust library for maps (named after the Doom cheat code ) where keys are borrowed from values. At Oxide we use it extensively in Omicron , our control plane—the software that sits at the heart of every Oxide rack, provisions resources like compute and storage for our customers, and ensures the rack stays up and running over time. maintains in-memory indexes of the kinds of large records that show up everywhere in a system like that, such as disks or sled inventories . As a result, it must be correct: if it misbehaves, our control plane can malfunction in ways that are unpredictable and hard to diagnose. consists of a fair amount of unsafe code underneath. There’s been some recent concern over the amount of unsafe code in Rust rewrites, so I thought I’d write about some of the unsafe code in and how we try to tame it. With Rust’s standard library maps, keys are stored separately from values. Let’s say you want to store a map of records keyed by an email address. With , you might write something like: This approach has what I consider to be a pretty major downside: the key (email) is not stored in the same struct as the value (the rest of the record). How would you handle this? One way is to pass around both the email and the user, for example with : As an extension, you could maybe have a struct which combines both at fetch time: In practice, this gets quite unwieldy when you have lots of different types of records that need this kind of treatment. Alternatively, you could duplicate the email across the key and the value: But that has the risk that the emails stored in the key and the value fall out of sync. provides a better alternative. With , you can write something like: At Oxide, this has proven to be an invaluable pattern: many of the records in our control plane are quite large (think database lookups), and is a great fit for them. It also comes with several other features that directly address pain points we’ve dealt with at Oxide. A few worth calling out: Like many of our other crates, is built for Oxide’s needs but is generally useful to the Rust community. You’re welcome to use it in your own projects as well. Before I move on, I want to talk about what it means for to exist in a memory-safe language. The big concern is undefined behavior (UB): a program behaves in an unpredictable way because core assumptions made by the language or compiler have been violated. Rust calls an abstraction sound if no safe code can use it to cause UB, and unsound otherwise. The vast majority of Rust code is safe, which (assuming that any unsafe used by the safe code is sound) means that no UB can occur. However, due to the fundamental undecidability of static analysis (see Rice’s theorem ), it is impossible for the Rust compiler—or any kind of algorithm that terminates—to accept all programs without UB and reject all programs with it. Therefore, when writing such an algorithm, its authors have to make a decision: do they reject some programs without UB, accept some programs with UB, or both? The Rust compiler does the first: within the context of safe Rust, it rejects all programs with UB but also some without UB. (This is the correct choice!) What if your program is in the no-man’s-land where it gets rejected even though you know it doesn’t have UB? To express those kinds of programs, the Rust compiler provides an escape hatch: the keyword. By writing it, the author takes responsibility for soundness: they vouch that no safe code can use this to cause UB, and that the compiler should trust them. What does it mean for some unsafe code to not have UB? Informally, it must follow the rules of Rust —all the rules that the compiler proves for safe Rust. Some examples of these rules are: The rules of Rust are very hard to reason about! I consider them to be significantly harder than C, for example, particularly due to the rules around mutable aliasing 2 . So judicious use of unsafe Rust usually involves encapsulating it behind a safe abstraction. In all but the simplest of cases, it is not possible to be sure that such an abstraction is sound without also reasoning about the surrounding safe Rust. In this section, I’ll walk through three examples in increasing order of difficulty. The first example is the method on slices. This method splits a mutable slice into two separate parts. is a safe method, but safe Rust lacks the ability to express this kind of partitioning of a slice. Thus, requires unsafe Rust. The implementation is roughly: To be sure of the correctness of the unsafe block, you also have to consider several things about the surrounding safe code: If any of these invariants (all of which are safe Rust) are not upheld, the safe abstraction becomes unsound. The next level up is that in many cases you also have to analyze the entire module the unsafe code is present in. Consider this vector type: The soundness of relies on every other method in the module, safe and unsafe, ensuring that is correct and in bounds. Encapsulation and privacy ( cannot be mutated from outside the module) are load-bearing here. The hardest situations, though, are when unsafe code is generic and calls back into user-supplied code. In those cases, the fundamental principle is: Safe Rust code, no matter how pathological or adversarially written, must never cause unsafe code to exhibit undefined behavior. In other words, you no longer have the luxury of analyzing just the safe code you see; you must put yourself in the shoes of an attacker, considering every safe Rust program one could write, and be resilient to all of them. For example, it is possible for user code to misbehave in a way that leads to internal tables or data structures being corrupted. Safe but buggy Rust may cause unsafe Rust to be slower, produce incorrect results, or even panic, but it must not cause UB! (Why assume the worst? You might argue that well-meaning Rust developers are quite unlikely to write adversarial code. One reason for having a firm rule is that guarding against adversarial code means guarding against innocent mistakes as a corollary; a bright line makes it easier to assign responsibility for protection against UB.) An example is the trait, which has a method that returns the exact size of an iterator. A developer might be tempted to take advantage of this in unsafe Rust: This code does not cause UB if the iterator is well-behaved. But there’s no guarantee of such behavior! It is entirely possible to write a broken implementation that returns a bogus result, all in safe Rust: will write into unallocated memory in this case, so it is unsound in general 3 . Another example is the trait , where a safe but buggy implementation can return the wrong number of bytes read. See Rust RFC 2930 for more discussion about this. is in exactly this situation: it consists of generic data structures which call into user-supplied code, so it is working at this highest level of difficulty 4 . ’s architecture is pretty straightforward: it uses a combination of a list of items (internally called an ) and a table with indexes into the slots. For example, consists of: For example, an holding three items might look like the diagram below. The top row is the hash table over ; the middle row is the . Adding a new item consists of checking to see if any slots are available. If so, we read the index out of that slot, overwrite the slot with , then set to the value we read. Otherwise, we push a new slot to the end. Finally, the hash table is updated, fetching the key and computing the hash, then recording the new index in the hash table. Starting from the state above: Let’s say we want to insert a new item . Calling recycles slot from the free chain, advances to , and records the new hash entry: Removing an item given a key involves this process in reverse: first, consult the hash table using the hash computed from the key, and remove the found index from the hash table. With the index in hand, replace the with , setting the internal to the current value of . Finally, set to the index that was just replaced with . Continuing from the post-insert state above: Calling drops the hash entry for , marks slot as with its pointing at the old ( ), and updates to : This pattern generalizes to the other map types: has a few different kinds of unsafe Rust within it. Most uses of unsafe follow well-established patterns also used within Rust’s standard library —these are not dependent on user code, and a sufficiently smart borrow checker would make these uses of unsafe unnecessary. But there are a few that stand out. Perhaps the most challenging one to reason about is mutable iteration over items. For , iteration is ordered by the key. One might start off by writing something like: But this would immediately return an error: The reason this occurs is that Rust’s iterators require that the returned values do not borrow from the iterator itself 6 . Many uses of iterators do not need this capability—in particular, most for loops only operate on one item at a time: But Rust’s iterators also let you create values that outlive the iterator, via e.g. the combinator : cannot outlive itself, but can outlive the iterator returned by . Crucially, this relies on knowing that all of the returned by the iterator are disjoint—or, in other words, that the iterator never yields the same value twice. (Remember that one of the rules of Rust is that there must never be multiple references to the same memory.) But the Rust borrow checker just sees a succession of accesses into a list, and it has no way of knowing that the indexes are all different. If the human writing the code has knowledge that all of the indexes are different, though, they can use an unsafe pattern called lifetime extension to tell the borrow checker to let this code through: In the previous section, we saw how unsafe Rust acts as an escape hatch for facts that the Rust compiler cannot establish by itself—in this case, that the indexes returned by are all different. But are the indexes actually different ? Based on the storage scheme described above, it would seem like that is the case. But this is generic code which calls into user-provided functions, so there’s a chance that sufficiently pathological user code can trick the map into storing duplicate indexes. Let’s take one such example we recently found and fixed. Suppose you have an called storing five items with integer keys 0 through 4, inserted and stored in order. Here, the top row is the B-tree storing indexes in key order, while the bottom row is the : Now suppose you use the Entry API (a standard Rust data structure pattern) to fetch an item by index 0: This will successfully look up the item stored at index 0 and return an . The internally stores the fact that the item was found at index 0. Now, at this point, let’s deliberately switch the implementation for to always return , no matter the values inside. There are a few different ways to do this in safe Rust, most of which use some form of interior mutability . (This is unlikely in ordinary Rust, but that doesn’t matter : unsafe generic Rust must handle arbitrarily pathological Rust!) Now remove the entry from the map using : At this point, would attempt to remove index 0 from the B-tree, descending into the tree again but comparing only by key 7 . Because we made comparisons by key always return , the comparison would short-circuit at the first element it compares against. For example, if the first comparison lands on the B-tree entry , then that entry would be removed. But the was carrying , so it would also remove item 0 from the item set: The map is no longer in a consistent state. The index 0 is still in the B-tree index but the corresponding slot in the is vacant; meanwhile, item 2 in the does not have any pointers to it. At this point, though, the invariant that there aren’t duplicate indexes hasn’t been violated. Where it does get violated is with this next step: suppose the implementation now switches back to being honest 8 , and you insert a new entry with key 1000. Since points to item 0, will attempt to reuse that slot. It is this step that results in duplicate indexes: There are now duplicate indexes pointing to the same item, and becomes unsound. This was fixed through a combination of two things: When descending into the B-tree, also check equality against the index. In other words, if we’re looking for a key with a known index in the B-tree, compare against both and . As covered in the example above, previously, we would just check that the key is the same; the pathological implementation returned , tricking the map into believing that the entry at was the one to remove. We changed this to additionally use the index as a tie-breaker. The comparison against now resolves to (since ), so the search doesn’t spuriously match here. By construction, the only way the search can now succeed is if the stored index actually equals the one we’re looking for. If there are no matches, fall back to a linear scan. The tie-breaker eliminates spurious matches on the wrong entry, but it doesn’t guarantee that the search finds the right entry. (The B-tree is sorted by key , while the tie-breaker compares by index , and in general these two orderings are independent.) If the tree search yields no results, we must still keep the tree and the in sync. In that case, we remove the index from the B-tree by doing a linear scan without calling into user code. The remove operation then takes linear rather than logarithmic time, but that is an acceptable tradeoff, since buggy user code is the only way to encounter the fallback. This is the kind of long-range reasoning that is sometimes required to establish the soundness of a safe abstraction. The overall system relies on all of these moving parts working correctly across arbitrarily complex series of operations. Because is such a foundational data structure at Oxide, we go through great lengths to validate its correctness, including the soundness of its abstractions. There is a great deal of analytical reasoning performed by experienced Rust authors and reviewers, as described in the section above. But we also empirically validate along several different dimensions. None of the layers is sufficient by itself, but by rigorously doing all of them we can be more confident that is correct. The layers of validation we apply are: In this section, I’ll provide a brief overview of each layer. Click through the links to see more details and code samples. All blocks and patterns of unsafe Rust have been analyzed by at least one human reviewer, and up to three. Thanks to several of my Oxide colleagues for lending their time and expertise to these reviews—these discussions helped sharpen our reasoning considerably. The reasoning for each unsafe block is captured in a comment above the block. We use Clippy’s lint to verify that these comments are present. The simplest kind of empirical validation is with example-based tests : create a map, perform some operations, and ensure that the results of those operations are as expected. has unit tests for its internals and integration tests for the public API . These also live as doctests, where they double as documentation. also has a battery of pathological tests which supply buggy implementations of and other traits. In CI, both regular and pathological tests are run under the Miri interpreter , which can detect many classes of undefined behavior. (If you’re curious about the details, we run with Miri under both Stacked and Tree Borrows .) But note that some classes of UB can be detected in the regular test environment as well. For example, in the previous section we established that the tables not containing duplicate indexes is a necessary condition for soundness; this invariant can be verified outside the Miri context as well. uses two layers of randomized testing: model-based comparison against a known-correct oracle, and fault injection on top of it. For data structures, example-based tests alone are generally considered insufficient. A much stronger kind of testing is model-based testing , also known as stateful property-based testing (stateful PBT): random sequences of operations are applied to an instance of the type, and the results are compared against an oracle that is known to be correct. has extensive model-based tests against a oracle that is inefficient but clearly correct. (I’ve talked about this style of testing before on Oxide and Friends .) An extension of model-based testing is fault injection , where bugs are randomly inserted into user code. For , a fruitful avenue for fault injection has been panic safety (or unwind safety ): user code panicking in the middle of an operation must not cause the map’s invariants to be violated 9 . We systematically explore fault injection by generating random sequences of map operations, where each operation is associated with a (randomly selected) panic countdown. Each call into user code decrements the countdown by 1; if it reaches 0, the code panics. Randomized panic safety testing found several subtle bugs in ( example ), including some that escalated into unsoundness. The model-based tests also verify internal invariants after each operation, such as the no-duplicate-index condition described in the previous section. We’ve found in practice that model-based tests are too slow to run under Miri, so we verify the invariants on which soundness (and correctness) is known to be dependent instead. Another kind of validation that’s recently become available is LLM-driven adversarial review . Current-generation frontier models 10 found several ways to write pathological implementations of user code that would corrupt the map. In one notable case , an LLM constructed a way for the map’s invariants to break on a custom allocator panicking and unwinding. This is a panic safety issue (and a failure mode I hadn’t thought about before), but distinct from the existing panic safety tests that only covered panics in regular user code like an implementation. LLMs can sometimes produce plausible-but-wrong soundness claims. An effective way to guard against that is red-green TDD , using Miri as an oracle: For a soundness bug, first have the LLM write a test case demonstrating the bug, running it under Miri to show undefined behavior—the red phase. Then, after fixing the bug, rerun the test to show that it now passes—the green phase. To formally verify , one’s first thought would be to use a model checker like Kani to establish that the maps don’t violate internal invariants. But is unfortunately a bit too complicated for Kani to handle, and the required proofs blow up beyond what is feasible for the tool. The Creusot deductive verifier can help Rust developers prove their code is free of panics and other errors, but as of this writing it’s unable to prove invariants that must hold even if user code panics or otherwise misbehaves. The infeasibility of proofs is a common problem with applying formal methods to implementations, so they are often applied to a higher-level specification instead. For , the closest thing to a specification is , but it can be easily observed to be correct without needing a formal proof. How do we ensure that the implementation matches the specification? Model-based testing does a lot of heavy lifting here. While they aren’t a formal proof that matches , running model-based tests thousands of times in CI provides fairly high confidence that it does. We keep an eye on developments in this space; the in-development , which lets you embed Lean soundness proofs alongside unsafe Rust as doc comments, looks quite interesting. If you’re working on formal verification tooling, is a great candidate to benchmark against because of its crisp invariants and relatively constrained yet non-trivial scope. We’d be especially interested in proofs that hold over arbitrary trait implementations, and of refinement between and . Please reach out by filing an issue or emailing me ! Writing unsafe generic Rust is extremely difficult. Each invariant that the unsafe code relies upon has to be carefully upheld over arbitrary trait implementations, including adversarial ones. This post covers one such example: how an implementation could be carefully written to trick the map into creating mutable aliases to the same memory, and how we fixed it. No single technique can hope to catch all bugs, which is why uses several layers of validation. Humans carefully reasoning about every line of unsafe code goes a long way; example-based, pathological, and randomized tests provide empirical evidence; and frontier models can find new and surprising ways to break code that humans might not have thought of. This is a lot of machinery, but at Oxide we believe that for foundational infrastructure, this level of rigor is justified. And if you agree, we’re hiring ! Diagrams courtesy Ben Leonard. Discuss on Hacker News and Lobsters . also supports integer keys; it serializes and deserializes them as strings.  ↩︎ In C, the aliasing rules are about accesses : multiple pointers to the same memory are fine, as long as you don’t have a data race on them or violate strict aliasing . In Rust, it is UB for multiple references to the same memory to ever exist , even if they’re not actively being mutated simultaneously.  ↩︎ An exercise for the reader. There is a second, entirely different, kind of bug in : it can leak memory with some user-supplied iterators. This isn’t a soundness bug, but it is a bug nonetheless. Try working out how this can happen and why this isn’t a soundness bug. (Hint: think about user code panicking.)  ↩︎ For this discussion we’re restricting ourselves to single-threaded Rust, since iddqd is not a concurrent data structure. Concurrency amplifies the difficulty of reasoning about Rust several-fold further.  ↩︎ The index is built on top of the standard library . This required making work with an external comparator through some further unsafe trickery.  ↩︎ For a proposed version of an iterator which does let you borrow values from the iterator, see .  ↩︎ Is this second descent necessary? ’s own stores a cursor into the map to avoid a second descent, and could be restructured to hold one. But there are other ways to trigger the same issue without using , for example by using a chaotic implementation that randomly returns at times. I’m using here as the clearest way to demonstrate the bug.  ↩︎ Restoring is necessary here because, with , the duplicate-detection step at the start of would short-circuit and reject the insert. The point is that user code is free to misbehave in arbitrary ways, including changing its mind from one call to the next.  ↩︎ Rust binaries can be configured to either unwind (be recovered from ) or abort (tear down the process). Panic safety only matters with . At Oxide, we ship our system software with . However, libraries cannot assume and must work with .  ↩︎ Claude Opus 4.7 and GPT-5.5.  ↩︎ First-class support for complex keys that borrow from more than one field, without having to resort to workarounds like dynamic dispatch . Maps with two or three keys per item, each independently indexing the same record, without the usual pattern of maintaining multiple maps by hand. Serde implementations that serialize as sequences rather than maps, so that non-string keys can be serialized in JSON 1 . Importantly, these implementations reject duplicate keys. (For backwards compatibility, serialization as maps is also supported .) There are no data races. There must be no reads of uninitialized or freed memory. There must not ever be multiple aliases of references to the same region of memory. Immutable data must not be mutated. that the function was provided a , and not, for example, a the ensuring that is in bounds the fact that is , and not, for example, an , which (similar to the crate ) internally has: a , where is an enum with two variants: (where represents an integer index into the ) a , which points to either the most recently freed slot, or is a sentinel (marked as in the diagrams below) indicating that no free vacant slots are available. (The combination of and the slots forms a free chain .) a over ( is the fast hash table implementation used by the standard library) follows a similar structure, except the hash table is replaced with a B-tree index 5 . and store two and three hash tables respectively. When descending into the B-tree, also check equality against the index. In other words, if we’re looking for a key with a known index in the B-tree, compare against both and . As covered in the example above, previously, we would just check that the key is the same; the pathological implementation returned , tricking the map into believing that the entry at was the one to remove. We changed this to additionally use the index as a tie-breaker. The comparison against now resolves to (since ), so the search doesn’t spuriously match here. By construction, the only way the search can now succeed is if the stored index actually equals the one we’re looking for. If there are no matches, fall back to a linear scan. The tie-breaker eliminates spurious matches on the wrong entry, but it doesn’t guarantee that the search finds the right entry. (The B-tree is sorted by key , while the tie-breaker compares by index , and in general these two orderings are independent.) If the tree search yields no results, we must still keep the tree and the in sync. In that case, we remove the index from the B-tree by doing a linear scan without calling into user code. The remove operation then takes linear rather than logarithmic time, but that is an acceptable tradeoff, since buggy user code is the only way to encounter the fallback. also supports integer keys; it serializes and deserializes them as strings.  ↩︎ In C, the aliasing rules are about accesses : multiple pointers to the same memory are fine, as long as you don’t have a data race on them or violate strict aliasing . In Rust, it is UB for multiple references to the same memory to ever exist , even if they’re not actively being mutated simultaneously.  ↩︎ An exercise for the reader. There is a second, entirely different, kind of bug in : it can leak memory with some user-supplied iterators. This isn’t a soundness bug, but it is a bug nonetheless. Try working out how this can happen and why this isn’t a soundness bug. (Hint: think about user code panicking.)  ↩︎ For this discussion we’re restricting ourselves to single-threaded Rust, since iddqd is not a concurrent data structure. Concurrency amplifies the difficulty of reasoning about Rust several-fold further.  ↩︎ The index is built on top of the standard library . This required making work with an external comparator through some further unsafe trickery.  ↩︎ For a proposed version of an iterator which does let you borrow values from the iterator, see .  ↩︎ Is this second descent necessary? ’s own stores a cursor into the map to avoid a second descent, and could be restructured to hold one. But there are other ways to trigger the same issue without using , for example by using a chaotic implementation that randomly returns at times. I’m using here as the clearest way to demonstrate the bug.  ↩︎ Restoring is necessary here because, with , the duplicate-detection step at the start of would short-circuit and reject the insert. The point is that user code is free to misbehave in arbitrary ways, including changing its mind from one call to the next.  ↩︎ Rust binaries can be configured to either unwind (be recovered from ) or abort (tear down the process). Panic safety only matters with . At Oxide, we ship our system software with . However, libraries cannot assume and must work with .  ↩︎ Claude Opus 4.7 and GPT-5.5.  ↩︎

0 views

SQLAlchemy 2 In Practice - Solutions to the Exercises

To conclude with my SQLAlchemy 2 in Practice series, this article contains the solutions to all the exercises. If you'd like to support my work, I encourage you to buy this book, either directly from my store or on Amazon . Thank you!

0 views