Posts in Sql (20 found)
Farid Zakaria 1 weeks ago

Your executable is a SQLite database

I have been probably obsessed with two things in the last few years: Nix as a tool to explore innovative ideas that require the capability to rebuild the world and replacing ELF with SQLite as an executable format. You might have noticed that these two ideas are well suited to each other. I explored the idea during my PhD thesis but found feedback from others unmotivating. Radical ideas are hard to sell, as you are working against the inertia of the established solution. One of the end results of that exploration was sqlelf , a tool that lets you explore an ELF file declaratively using SQL. 1 instead of fiddling with and . It was remarkably simple by leveraging virtual tables over the ELF: however I found it to be a refreshing improvement to explore the ELF file format. I knew however that there is still something much bigger to be done. I never let the idea go and with the recent improvements with LLMs, I find it compelling to revisit these ideas to explore further. Specifically, can we replace ELF with SQLite as an executable format? 🤔 Not “a database that describes an executable”, but the actual file you and run. I developed a pretty fleshed out prototype. It is called SELF , the Structured Executable & Linkable Format , because I am unoriginal. It is on GitHub if you are interested. I’m surprised about all the interesting things that fall out of this idea. Working through my PhD, I realized something that bugged me. ELF is already a database. It just implements many database primitives by hand, along with a surprising number of data structures for performance, like a bloom filter for symbol lookup. If you ever have to analyze or parse ELF, the kernel, , binutils, LIEF, goblin, , you are re-implementing the same parser over and over again. Every producer re-implements the same serializer. The format itself is incredibly terse, designed for a world where disk space and network bandwidth was at an extreme premium. Modifying the format is hard, you often have to zero out sections and add new ones since it is packed so tightly. There is also no self-describing schema. ELF itself is a very generic format that supports sections of data that by convention are interpreted in specific ways but the format does not enforce it. SQLite is the counter-example. They are a self-describing format that is extremely stable. It is designed to be extended to support new features without breaking existing consumers and supporting a wide range of queries performantly. If we were to replace ELF with SQLite, what would fall out and can all of the necessary information be represented in a SQLite database? The answer is yes, and it is surprisingly simple. A SELF file needs two tables to run: is the ELF header as key/value pairs and is the load image, one row per program header with the bytes in a : A single table for the symbol table replaces many of the ELF sections and the index. It is a single table with a single index: Our capability to include an index is equivalent to and in ELF, but it is a proper b-tree index maintained by SQLite instead of a hand-rolled bloom filter. 2 Surprisingly a lot more falls out as well: is gone, because is and SQLite already interns strings, symbol versioning is a column, not the / contraption and there is no need for a table. Other tables exist as well for metadata which exist for tooling: , , . Delete them and the program still runs, which means is a transaction: All the tools that operate on ELF files for reading, reduce to queries over the database. Any tool that modifies an ELF file, like , can operate on the database within a transaction rather than performing fragile offset surgery: is a and . is an . Any information missing from the schema can be easily exposed via a view. For example, is a query over the table, which is a join of the table with the table to find the sonames of the libraries needed by the program. SQLite reserves a 4-byte at byte offset 68 of its header, for exactly this purpose. We stamp it , so an ordinary SQLite database never matches: We can now leverage binfmt_misc , the subsystem that allows you to invoke any binary as if it were native. We need only to register the magic to trigger on and an interpreter that will invoke our new file format. On NixOS the registration is a few lines matching the SQLite magic at offset 0 and at 68: For now, I have a small tool that converts an ELF file into a SELF file. It is a simple hook you can opt into per package on NixOS. The tool reads the ELF, extracts the program headers and symbol table, and writes them into the SQLite database. We could look at extending or to emit SELF directly, but for now this is a simple way to explore the idea. is the interpreter. It is a small C program linked against . Its implementation is remarkably similar to that of but it fetches the program headers and symbol table from the database instead of reading them from the ELF file. It maps the loadable segments into memory, relocates them, and jumps to the entry point. Note has to stay an ELF file. An interpreter that also matches the registration recurses straight into . Running a static program was quick and easy but boring and unimaginative. The interesting part is dynamic linking, which is where the database shines. I explored two different ways to do dynamic linking. The first is to keep and just replace the lookup with a SQL query via rtld-audit interface, to quickly iterate on the design. The second is to replace entirely with a new dynamic linker that does the entire lookup and binding in SQL. glibc’s rtld-audit interface lets an audit library intercept every shared object lookup ( ) before any filesystem search happens, included. The audit library can then answer the question “which library satisfies this symbol?” with a SQL query instead of walking the and . Stock maps and relocates it, so the full gamut of glibc features work: lazy PLT, IFUNCs, TLS and symbol versioning, while library storage are rows and library lookups are queries. I was curious what a fully SQL dynamic linker would look like, so I prototyped one. It is called and it is a small C program that implements the dynamic linker entirely in SQL. It is a proof-of-concept, but it works. It maps every object’s segments, publishes their exports, and for each relocation patches the GOT and jumps to the start. The two things that often matter when replacing a well-established format are size and latency. How much bigger is a SELF file than an ELF file, and how much slower is it to run? Size. A SELF file carries SQLite’s b-tree overhead and lands at roughly double the ELF. Similar to ELF binaries, most of that is recoverable, because the overhead is mostly the optional tables for debugging and tooling. Stripping them and deleting them is a transaction. A stripped SELF is 1,794,048 B against the ELF’s 1,768,632 B, that is within 1% . We will see though that there are interesting ways to amortise the overhead even more which I found very unique and interesting. Latency. I benchmarked various binaries from a 15 KiB to a 42 MiB linking 47 libraries: There is a fixed ~5 ms to open SQLite and start the interpreter, plus a copy proportional to the image. That copy is worse than it looks, because the b-tree pages are not mapped into memory. Two processes running the same SELF binary do not share text pages the way a normally- ‘d ELF does, because the bytes are copied out of the b-tree rather than mapped. 3 A SQLite database though need not merely be a single executable. It can be a closure , a single file that contains a program and all of its transitive dependencies. The output of a program is ambiguous: it only lists the sonames of the libraries it needs, not the specific files that satisfy those needs. Nix improves upon this by explicitly resolving every edge to a specific store path via the use of . 4 We can do the same in SELF by storing the resolved path of each edge in the database: packs a binary and its transitive dependencies into one database with those edges filled in. Shared library resolution stops being a guess and becomes a foreign key and becomes a 🤯: This single database is a closure of the executable and its five libraries: six objects, segment bytes and all, in one 4.8 MiB file. There is no soname ambiguity inside a closure, because a closure by construction contains exactly one provider per edge. I hope you’ve been with me so far, because this is where it gets really interesting. We can go even further and pack multiple closures into a single database. I pointed at every ELF binary on this system’s : 723 executables, which pull in 400 distinct shared libraries. 1,123 objects, 346,386 symbols, 3,808 dependency edges, all as one SQLite file . Turns out when you do that, the database is much smaller than you would expect. 611.9 MiB of database against 644.4 MiB of ELF files. The whole userland, as one queryable file, is smaller than the files it came from. The b-tree cost that doubled a single amortises to nearly nothing across 1,123 objects and is roughly 6% over the actual program bytes. The libraries and closure are shared across the executables very similar to how Nix might share them across multiple closures, if the store-path was the same. If every root shipped its own private closure (i.e. the AppImage model), the same 723 programs would come to 5.53 GiB but the deduplication of libraries and symbols falls out naturally from the database schema. Many common idioms we use in ELF immediately fall out of the database. For example, is a row in a table rather than an environment variable. The table is a list of objects to map last, so their exports win. This means that turning on and off is a transaction. We were able to accomplish an atomic across a whole userland in one file, “interpose a tracing everywhere, then ” is a single transaction. 😈 The format is done and round-trips between ELF and SELF losslessly. The tooling is done and can query, modify, and pack closures. Lookup through SQL works on unmodified glibc programs perfectly and the native-SQL loader works enough to explore it as a possibility for ideas. The whole thing is at fzakaria/selfdb . boots a NixOS VM where is a SQLite database. 🙌 Nix lets us explore radical ideas like this. We can rebuild the world down to the Linux kernel if needed. We need not be constrained by the existing decisions and constraints of the past. We can explore new ideas and see what falls out. I hope you find this idea as interesting as I do. I wrote a paper, arXiv:2405.03883 , that I failed to get published and a follow-up post on querying with it .  ↩ is a bloom filter plus bucket chains, laid out so can reject a miss without touching the chain during symbol discovery.  ↩ You might notice that (274 KiB, 27 libraries) starts slower than ELF (4.6 MiB, 5 libraries). That is doing work proportional to the number of objects rather than the number of bytes, which I have complained about before .  ↩ I have written about on Nix before such as making it redundant or speeding it up .  ↩ I wrote a paper, arXiv:2405.03883 , that I failed to get published and a follow-up post on querying with it .  ↩ is a bloom filter plus bucket chains, laid out so can reject a miss without touching the chain during symbol discovery.  ↩ You might notice that (274 KiB, 27 libraries) starts slower than ELF (4.6 MiB, 5 libraries). That is doing work proportional to the number of objects rather than the number of bytes, which I have complained about before .  ↩ I have written about on Nix before such as making it redundant or speeding it up .  ↩

0 views

Bonsai: Compiling Queries to Pruned Tree Traversals

Bonsai: Compiling Queries to Pruned Tree Traversals Alexander J Root, Christophe Gyurgyik, Purvi Goel, Kayvon Fatahalian, Jonathan Ragan-Kelley, Andrew Adams, and Fredrik Kjolstad PLDI'26 File this under: “so elegant, why wasn’t this discovered sooner?” This paper describes a generic language (Bonsai) and compiler for efficient tree traversals. The language is structured such that a simple compiler can quickly generate efficient code. The examples from the paper fall into two buckets: SQL-like queries and spatial data structures. Here is some example code from the paper which describes a SQL-like operation on a set of points: A is a structure with two elements ( , and ). The function returns the minimum associated with any point that has an x value within the range . And here is an example from the paper which describes a ray tracing function: The function finds all triangles which intersect a given ray and returns the one that is the closest to the origin of the ray. The key point of the paper is that both types of functions can be implemented as tree traversals. A key design point that makes Bonsai feasible is metadata stored in the trees which represent sets. Tree metadata is specified separately from queries. The following snippet from the paper contains a tree data structure represented as an algebraic data type: A set of points is represented as a tree. Leaf nodes hold points. Interior nodes have pointers to and children, and four pieces of metadata ( ). The keyword is used to describe the meaning of the metadata. The expression means that the value of all x fields in all points contained in the subtree lies within the range . The expression means that the minimum value of the field in any point in the subtree is . The Bonsai compiler takes a query and a description of the tree metadata as input and generates C++ code to perform the query via traversing the tree. There are three key properties of this process. The first is that the generated code for all filter and reduction operations are fused together. In other words, there are no intermediate trees (i.e., sets) produced during query execution. Secondly, tree metadata is used to accelerate both filtering and reduction operations. For filtering, the generated code checks tree metadata to determine if all data elements in a subtree will be accepted by the filter, or if all elements will be rejected by the filter. If all elements will be rejected, then there is no need to traverse the subtree. If all elements will be accepted, then there is no need for further evaluation of the filter expression for the subtree. In this case, traversal into the subtree can be skipped if the root of the subtree has metadata containing a pre-reduced value ( in the example above). Fig. 4 shows IR corresponding to two filtering examples. In both examples, there are two types of leaf nodes in the tree. nodes contain a single value whereas nodes contain an array of values. Fig. 4a shows the IR for a single filter expression . Here is the code, annotated with some comments to describe the semantics: Fig. 4b shows the IR for the logical and of two filter expressions: : Code generation tracks a symbolic interval associated with each expression. An interval is represented by two expressions: one that evaluates to the lower bound of the expression and one that evaluates to the upper bound. This interval analysis is used to produce the code that implements and . Interval analysis is fast to compile but can produce code that suffers from false positives. Symbolic interval analysis is general enough to handle spatial filters (e.g., those used in ray tracing). Fig. 7 compares Bonsai to a state-of-the-art library ( FCPW ) for geometric queries: Source: https://dl.acm.org/doi/10.1145/3808256 Fig. 8 compares Bonsai to relational databases for range joins. The range join logically computes the Cartesian product of two relations, and then removes elements from the result which are not near each other according to Manhattan distance . Source: https://dl.acm.org/doi/10.1145/3808256 Dangling Pointers A logical extension of this process would be to automatically determine what tree metadata would be most useful for a set of queries. Thanks for reading Dangling Pointers! Subscribe for free to receive new posts.

0 views
Martin Fowler 1 months ago

Fragments: July 21

With this post, I’ll wrap up my notes from the second Future of Software Development Retreat . But before I do, I should note that the full Thoughtworks report on the retreat is now available . They have five headline findings: ❄                ❄ A session convened around the mismatch of views about using LLMs between engineers using it and the C-suite and boards that were calling for it. The concern is that boards are looking at promised productivity gains, and not concerned enough about the risks, particularly about security. This was illustrated by one tale of a company that used ML-trained software to optimize the replacement of air filters on their field equipment. They were pleased to see that they were able to change the air filters less frequently, saving them $50 million. But the problem was the ML models were trained on equipment used in the desert, while their equipment was used in the arctic. Air filters in the desert deal with dust, but in the arctic the thing to remove is mosquitoes. There’s an important difference here, mosquitoes rot, and enough decaying mosquitoes is a serious fire risk. Fires from such dead mosquitoes around infrequently replaced air filters cost the company $100 billion . Now such a tale could told of many situations without AI in the mix. Plenty of human situations have gone wrong when solutions are applied in a new context (which is why context is such a key word among pattern-writers). But the tale does remind us to be wary of an AI’s suggestions, and to always think of how to build sensors to provide rapid feedback. Engineers particularly worry about the risks when citizen developers start vibe coding . In many ways, of course, this isn’t new. I.T. folks often worry about how many important business decisions are based on spreadsheets, that are built with little control, testing, or assessment of data quality. Vibe-coding amplifies these concerns, so companies need a range of controls to guard against security breaches . Some folks have made a point of raising issues at board level, running threat modeling session with board members to introduce them to the risks. Vibe-coded applications need to be put in separate infrastructure, which deterministic controls over data access to tame the lethal trifecta . One company encouraged widespread vibe-coding from citizen developers but recoiled from the problems of the huge shadow IT that emerged - they are now looking to build a platform to help control this work without stifling the useful tools that were produced. Part of the problem here may be simple experience with LLMs. Many in management find LLMs do a decent job of preparing management reports. Or summarizing management reports prepared by other LLMs. Given this they naturally think LLMs must do a decent job of programming too. My anti-management self has to mention Kelsey Hightower’s observation: The less busy work you have the less appealing these Al tools are One possible antidote to this: get the legal department involved. They see LLMs doing a poor job, and appreciate the risks involved. ❄                ❄ Most folks I talk to, both at the retreat and outside, recognize we are in some form of bubble. Technological advances like this almost always come with economic bubbles, and in the future we will all look back at this, and shake our heads saying we knew there was so much froth. But while it’s easy to see that there is a bubble, it’s hard to see how long it will run or what will emerge after the pop. After all the dotcom bubble was clearly recognized as such… in 1995. We can happily point at those companies that failed (Webvan, pets.com) but need to then acknowledge those that survived (Amazon). Most of those at the retreat were old enough to have lived through the dotcom bubble and crash, but one such grey-hair pointed out an interesting difference. Back then we were excited about what the future would bring, and we saw lots of new things being built. There’s much less of that, this time around. Most people are wary of what the AI bubble is creating. Partly this may stem from the reality that followed the dotcom hope. Social media may be everywhere, but do we think it’s actually improved our lives that much, even if (especially if?) we use so much of it? We hear so much about the incredibly productive things we can do with agentic programming , but has anyone noticed a flood of wonderful applications built with it? Or have we noticed a significant improvement in common applications from the big AI boosters such as Google or Microsoft? This may be another factor in the board-vs-engineer divide. Most of what’s driving adoption of AI at the moment is cost-cutting, and it mostly the boards that get excited by cost-cutting. Perhaps the increasing concerns about token costs will temper the eagerness. ❄                ❄ Folks are finding LLMs helpful in operations: with a good event stream from observability tools, an agent finds anomalies much faster. One of the problems with citizen-developer apps, is that they often don’t provide good observability, since the citizen-developers don’t think to ask for it. The agents ability to look at the event stream does pose governance questions, as often such event streams contain a lot of sensitive information. Reinforcing what I’d heard in Utah, more people agreed that LLMs are valuable for operations folks to help them understand what the code does. Cross matching code and event traces helps them assist humans to find what happened when things go wrong. Agents are particularly handy with repeated incidents, as they can collate lots of information from different cases and present it to the human teams. Getting agents to auto-remediate moves us to the next level of capabilities and concerns. It’s vital that agents carefully document all their actions when they do fixes. We also need to ensure there is feedback to the development team so they can learn. Agents don’t learn, the best they can do is update the context. There was a sense that many people over-estimate the capability of agents to deal with incidents. Such people think of incident resolution as a simple, linear process. But it’s rarely that, instead there’s a lot of surprises and adaptation needed. Humans are good with that, but LLMs are not. One of the perils of agent-developed code is their habit of inserting features that were never asked for. One team spent three days trying to figure out such an unrequested feature, trying to figure out who had requested it and if anyone wanted to keep it. ❄                ❄                ❄                ❄                ❄ A group of law professors carried an interesting experiment to judge how well an LLM can provide short answers to student questions . They created a batch of forty questions in contract law and asked the professors, plus a couple of LLMs, to provide answers. To evaluate the LLM answers they showed professors pairs of answers - one human, one LLM - and asked them which response they would prefer to deliver to a student. Professors rated LLMs far higher than their peers (average win rate = 75.33%), with models performing similarly to the best instructor. LLM responses were also rarely flagged as harmful (3.53%, vs 12.06% for professors). This reminds me of the distinction I mentioned in a recent fragment between interactional and contributory expertise . ❄                ❄                ❄                ❄                ❄ A few days ago Unmesh Joshi published an article here about his experiences using DSLs to enable more reliable use of LLMs . Responses to this included a pointer to an article by Spender Nelson that related similar impressions . DSLs like this hit a lot of sweet spots for LLMs. You can make them extremely token efficient, and enforce hard security boundaries. You can translate high-level LLM intent into a ton of deterministic code, ensuring good behavior and guardrails at the (custom) compiler level. And Large Language Models are very good at learning and working with DSLs. Maybe this shouldn’t come as a surprise; they are language models after all. A small bit of documentation generally is enough to set them off and running, and reasonable error messages let them course-correct even when they go wrong. He describes a couple of examples from their use: a query language for data lakes that takes into account security and authorization issues, and a little expression language to make it easier to create safe SQL where clauses. One of the biggest barriers to using DSLs, particularly external DSLs , is building a parser and tooling. LLMs make this much easier. That said, my sense is that it’s the semantic model that underpins the DSL is what really matters, and the DSL is one projection of that model. LLMs may help us explore other ways to project that model in interesting ways. ❄                ❄                ❄                ❄                ❄ In recent weeks I’ve been noticing the stench of LLM-speak more and more. It’s not just the common tells, it’s a sense of LLM miasma that pervades the prose. I’ve noticed it’s increasingly eliciting a visceral reaction, after a couple of paragraphs I just want to dismiss the entire article out of hand. For some of these, it was necessary for me to hold my nose and wade through the whole text, but it was with an intellectual nausea which obscured the content, even increasing my desire to indulge in such an awful distraction as checking social media. I wonder - is this just me that’s reacting so negatively to LLM-speak? Or do other people have a reaction that leads them to toss aside any prose that sets off their LLM-alarm? One indicator that it’s not just me is this post from Jason Koebler that I highlighted a couple of months ago, where he observed how AI was breaking his brain : People think things that are fake are real, things that are real are fake. Much has been written about “AI psychosis,” the nonspecific, nonscientific diagnosis given to people who have lost themselves to AI. Less has been said about the cognitive load of what other people’s AI use is doing to the rest of us, and the insidious nature of having to navigate an internet and a world where lazy AI has infiltrated everything. Our brains are now performing untold numbers of calculations per day: Is this AI? Do I care if it’s AI? Why does this sound or look or read so weird? Does this person just write like this? Is this a person at all? A while ago, I was thinking that it was reasonable for folks who aren’t as committed to writing as I am to use an AI to help polish their prose. Now I’m turning to encouraging writers to reject it. That pervasive LLM-voice is just so common now, my sense is that it discredits the writing even before the reader has a chance to try to understand what is being said. I don’t think it’s good enough to ask the LLM to write a first draft and then tweak it. I’m not sure writers can edit the LLM-ness out of prose once it’s in there. I even worry about asking an LLM to suggest improvements, I think it’s just too easy to accept an LLM’s suggestions, and in the process trigger your readers’ LLM-antibodies. Of course like most problems, it’s also an opportunity. Those who can get a distinctive human voice will get more visibility and credibility. But the question remains of how we can coach people to let out their true personality into their writing. Academic and corporate writing both tended to stifle engaging prose, LLMs are good amplifiers, and they will amplify this stifling. This is an even greater challenge for those for whom English is their second language (or indeed for many of my colleagues, their third or fourth). It’s too easy for me to neglect to think about a difficulty that I’ve never been able to face. The most immediate advice I can give something I learned many years ago and shared last year - Say Your Writing . Once you’ve got a reasonable draft, read it out loud. By doing this you’ll find bits that don’t sound right, and need to fix. I always suggested this to help people get past sluggish prose, especially if they had spent too much time around academic or corporate writing. But now I think the need to Say Your Writing is even more important, in order to combat the insidious impact of AI. For most people, their speech patterns get closer to their real self, so verbalizing writing is the way to fight those forces that try to smooth away a writer’s individuality. Code generation is no longer the bottleneck — verification is. ‘Harness engineering’ is emerging as a distinct, ownable discipline. Organizations are colliding with a real apprenticeship crisis. The executive/engineer expectation gap is a bigger risk than any technical limitation. Legacy modernization is the clearest, most defensible near-term value pool.

0 views
Ludicity 1 months ago

AI Mania Is Eviscerating Global Decision-Making

Note: This has been cross-posted to my company's blog, in case you think there is some use in sharing with someone in a format that looks more authoritative. Link here . I strongly believe there are entire companies right now under heavy AI psychosis and it’s impossible to have rational conversations with it about them. I can’t name any specific people because they include personal friends I deeply respect, but I worry about how this plays out. – Mitchell Hashimoto, of HashiCorp and Ghostty fame Over the past year, I’ve run point on all of our company’s sales, led the technical components of all but two of our engagements, and over the lifetime of this blog have had something like 300 catchups with professionals from around the world. This has ranged from people on the ground in niche service industries to executives at Fortune 500 companies 1 . Because of this, I've had a front-row view to our collective institutions across both the private and public sector undergoing breath-taking mass psychosis. This essay is an attempt to describe the bizarre dynamics that are currently at play, as I am in the rare position where my wellbeing is not contingent on paying lip service to madness, and to reassure the people trying to survive amidst all of this that they are not crazy. The reality is thus: the people in charge either have no plan, or see no path forwards other than keeping their heads down. Not at banks, not at hospitals, not in our government institutions. The world’s organisations have been captured by people in the throes of frothing excitement, and saner people who now live in a state of constant commingled fear and frustration. Reading this while working for a division that pivoted to provide interfaces for agentic workflows, only to discover that only ten users had ever touched the products we made for agents, only to pivot again to support for agentic workflows, which has a lot of competition because every company has to do something agentic now and there's only like four things you can do in that space, is bracing. – An editor of this essay Are companies actually seeing massive productivity gains from their AI adoption? Does any of this sordid affair make sense ? This should be an easy question, but it is surprisingly hard to get a straight answer to it. Executives that tell the press that their company has gone insane will quickly find themselves removed from their positions. Employees who are honest will find themselves fired in short-order, or “randomly” selected for a round of layoffs. In fact, it is in the interests of almost every actor in the space – boards, executives, employees, vendors, consultants – to obfuscate and misrepresent the success rate of AI projects. Many publicly traded companies are putting out announcements about their AI productivity gains when I know for a fact that the businesses have done nothing other than purchase Copilot licenses and declare victory. Yet we need to know if these projects are panning out – if the total focus on AI as a core tenet of business strategy is succeeding at a reasonable rate, then a discussion about the relative risk and reward is warranted. Unfortunately, we live in a dark timeline. All of the AI projects we have observed as a team are failing. Every single one – we have seen 0% success in a year and a half, not only amongst projects we have been asked to participate in 2 , but even within projects that we have observed in passing while doing totally unrelated work. Even if you grant that AI tooling accelerates specific workloads, the method and scale of the current investments is senseless. Frequently the failure is not related to AI itself, but rather that companies are terminally bad at running software projects effectively, and as I have remarked previously , AI projects are subject to all the failure modes of normal projects plus you can get everything right and then still fail because of the method's novelty. Very few companies are so good at shipping software that they can afford the extra risk profile. Often enough, though, it’s an actual failure in what LLMs can accomplish. The most common version of this, being rolled out across businesses around the world, is the internally-facing chatbot, or for the more daring company, the customer-facing chatbot. The story is always the same. For the former, I’ve never seen substantial internal uptake from inside a business. Employees don’t use internal chatbots because companies tend to have low-quality documentation and an LLM is not psychic – it can only know things that have been written down and made accessible. For the latter customer-facing applications, I have rarely had a pleasant experience as a consumer, with perhaps the exception of live transcription during medical appointments – hardly something worth pivoting an entire organisation around. In both cases, project leaders are very careful to avoid tracking basic metrics, such as whether the tools are being used at all, or they track metrics that are easily gamed. For example, my last consumer interaction was attempting to get help from Mitsubishi following an automotive failure, where a very polite robot asked me to describe the problem and that I’d receive a call back as soon as someone was available. This was the single most competent implementation of such a project I’ve seen in the wild, in that the voice was natural sounding, responded quickly, was clearly “live” in production, and promised a swift resolution. That was six months ago, and I did not, in fact, get a call back. When Mitsubishi did not call me back, what happened? Did that request just go into the void, showing one less incident for the year? Does it appear that the phone bot resolved my query without the need for human intervention? All we know is that it didn’t show up as an error, or I’d have received a call. I’m sure it looks great in all sorts of ways except the one that matters, which is that I was planning to buy a car and decided not to buy another one of theirs. For this reason, our team has quickly learned while on an engagement not to ask anything about ongoing AI projects in any context – by the time that project has started, it is too late for the management team, and intervention is not possible until a crisis point is inevitably reached. There is no conceivable positive outcome. The failure rate is so high that even basic inquiry leaves us in an untenable position. Any coherent question about how it’s going, what the goal is, who is using it, constitutes an inadvertent attack on the chain of command responsible for the work because there are no good answers to anything . Even in rare cases where my interlocutor has stated that things are going well (usually while the project is still mid-flight and failure has not had a chance to manifest), it is generally obvious that they are doomed, but at least in these cases I can simply agree and then go home to scream into a pillow for six hours straight 3 . All of this is to say that I am very confident that almost every report at a company about “massive AI productivity gains” is untrue as a matter of brute fact. Even if some companies are seeing clear gains, this is the exception, not the norm. With that assumption in place, we can talk about the dynamics at play, and how it has become impossible for many organisations to stay focused on things that actually matter to their long-term (or even short-term) health. It has become outright dangerous to even raise the possibility that AI might not be the solution to a problem, let alone be the sole focus of a company’s entire strategy. In every sufficiently large business we have observed (say, with 500+ employees), we have noted that continued advancement, and increasingly continued employment, has started to require repeated professions of belief in the transformative power of AI for said business. I am not talking about providing ideas about how to use AI in the business – I mean religious profession, declarations of faith. Overwhelmingly these statements are made by non-technicians, though it is not uncommon for technicians to emit deranged statements to curry favour. There have been several occasions where I have seen someone, apropos of nothing, blurt out almost word-for-word “AI is changing everything”, only to concede moments later that their organisation does not currently use LLMs for anything, and indeed, that they cannot name a single thing that has changed other than they get some use out of ChatGPT (frequently the free-tier). In one extreme case, I have seen an executive confess that they had never even used ChatGPT or any AI tool in their life, immediately after producing a technical strategy for an organisation with $2B+ in revenue which was entirely centered around AI. Initially these statements were so absurd on their face that I thought it was some cynical ploy to achieve thought leader status, and there are certainly some people doing this – I have had it admitted to me. But the broader reality is so much worse: people who have no background in the technology at all actually believe what they are saying . As a general rule you should avoid getting into business with a liar, but if you must , you can at least reason with them even if only in private. A true believer is much more threatening because they are impervious to even inducement by self-interest. The turning point in my belief was watching someone with a spectacular amount of money on the line fire their highest performers because they were achieving that performance without LLMs. When an employer publicly talks about AI innovation, we have to ask ourselves if they’re simply trying to manipulate the market or customers. When they privately commit to strategies like this with their own money at stake, with no attempt to communicate that strategy to external clients, I can only assume they really mean what they’re saying. A while ago, I wrote “Contra Ptacek’s Terrible Article On AI” , which was focused on the fact that many of Ptacek’s points in his own essay “My AI Skeptic Friends Are All Nuts” were internally inconsistent 4 . But on the crux of the matter, we are actually in total agreement, because he opens his essay with this: Tech execs are mandating LLM adoption. That’s bad strategy. Which is to say that we can sidestep arguments about the precise utility of LLMs entirely and we’re left in a very simple place – it is entirely obvious to both myself and Ptacek, two people that are coming at this from fairly opposed views, that people are being really, really stupid about this, and that organisations are demanding bizarre workflow constraints from their specialist staff. 5 These mandates have led to extremely strange places. Several of my peers now “AI-wash” their work, meaning that even when they can perfectly competently execute on their jobs to the satisfaction of their management teams, said managers are unhappy if the engineers haven’t used AI in the work… so now they’re lying about using LLMs even in contexts where their professional judgement is that they aren’t the appropriate tool. They just do the work, the same way they have for decades, and say Claude did it. Others are being measured on their AI bills with “token leaderboards”, where higher is better because I have evidently fallen into the pocket of Hell where the demons torment me by doing elaborate impressions of absolute fucking morons, so the people hired for their freakish ability to perform system optimisation do the obvious thing. They set the LLMs prompting themselves in a semi-plausible loop in case someone inspects the token consumption and then they watch Netflix. Not a single one has been caught, even when their own assessment of the output is that it isn’t suitable for deployment. Checking out a parallel copy of our Go repository and telling the AI to rewrite the whole thing in Zig while I work on something else just so I can keep my job. I hate this shit so much. My job has usage tracking and quotas. I don’t use it for actual work, I just spin it up and disregard the output. – An actual software engineer In fact, the only people I know of to be fired over this whole thing are people that have expressed visible doubt about this organisational strategy, which again, even Ptacek thinks is transparently dumb. The net result is that everyone has learned very quickly to praise executives on their visionary AI prowess, or they will be gunned down in the proverbial streets. Bless me, Father, for I have sinned. It has been ∞ days since my last confession. I accuse myself of the following sins: One of the main pieces of infrastructure we deploy at our clients is an analytics-focused database called Snowflake – for a typical business, the bill is tiny because it’s a pay-as-you-go situation and we can process all their data in one minute a day, you get a very hands-off deployment, and in short it has many characteristics that are very pleasant for our work. One of the features in Snowflake that we don’t use is called Cortex. Cortex is their AI chatbot layer, with the ability to plug into metadata (for non-nerds, descriptions of your data, like what a column in a spreadsheet means) and query a company’s database autonomously. In theory, you can ask a question like “What was our revenue for last week?” and it will spit out an answer. It is not really suitable for production usage. From memory, the last time I was given a presentation on it, by actual Snowflake staff, they reported that ideal configuration results in something like ~92% accuracy due to the complexity of data at a large business (see: probably best-in-class for these tools, but imagine your CFO having one in every ten of their numbers be outright wrong) and there were serious issues with managing deployments. Nonetheless, it can be used to produce some very flashy demonstrations. On several occasions, we’ve been exposed to folks that have been sort of lukewarm on our main offerings, but they really, really wanted to use AI to perform a natural language query on their data. And we thought “Okay, if you really want to see it, maybe we can caveat this appropriately and show you what it might look like.” This was a terrible mistake . It backfired in the most predictable way imaginable – every lukewarm client that saw the chatbot in action, even with us telling them that it was not going to accomplish what they wanted , wanted to buy it immediately. Every other consideration, including millions of dollars that we could plausibly help them achieve by non-AI means, was swept aside. It was like a dark and terrible force seized control of their limbs, plunged their hands into their own chests, and presented their still-beating credit cards to us in grim supplication. We were so mortified by the inexplicable shift in energy that we (wisely) declined to take the money and ended the sales process, and soon thereafter removed Cortex from our list of demonstrations. It would have been too irresponsible to exploit this gap in their reasoning, and frankly, it was already irresponsible to have even run the demonstration – doctors don’t walk around showing off cool pills that they’d never prescribe. Watching the total 180°, that shift from ice-cold to red-hot buying frenzy, was a deeply unsettling experience. It was personally uncomfortable to see people that clearly didn’t gel with us interpersonally suddenly dying to enter an ongoing relationship, but more broadly uncomfortable because for a brief moment I began to understand what is happening in sales meetings around the world. There was no warning I could have given that would have made them refuse to buy the damn thing – their appetite was as large as their budget could stretch, and some part of me wonders if this is because they knew that their ravenous hunger would be present in their own customers. They’d just buy it from us, then pivot right to a larger company and mind control their leadership team until the buck finally stops with the loser that needs to justify the expense. The main protection against this seems to be that the median vendor is so bad at their jobs that we had presented the first even somewhat-working products these people had seen, and this included an ASX-listed company that was already bragging about their AI usage . It took our team two hours to produce something that was frankly not that good – basically just typing text descriptions of data into a web browser – and it was still better than anything the leads had seen because they had nothing to show for all the investment. In fact, we have been forced to opt out of every sale where the lead has expressed anything beyond the most fleeting curiosity in the use of AI in their business. I don’t mean that we’ve heard that they’re interested in AI and elected to drop the contract on moral grounds. I mean that, over the course of the engagement, these people have exhibited a pattern of behavior that has made it near-impossible to sell to them without incurring reputational and legal risk, and are furthermore crafting management environments that I can only describe as cultish, ineffective, and “please dear God, do not let it be on earth as it is on LinkedIn”. The good news is, CISOs are used to having to protect the business from their hare-brained initiatives, and this one isn’t really that different, except that there’s a cult-like atmosphere to it that you didn’t see with, say, the cloud. It almost doesn’t matter whether you embrace the initiative or not; there’s work to be done to manage the risk, so that’s what you do. From talking to CISOs everywhere, I would say most of them are quietly skeptical but afraid to speak up. – Career CISO and well-known speaker that asked to remain anonymous Despite the substantial prevalence of true believers, many of the people running large AI initiatives, or making public statements about them, do not believe what they are saying. There are “heads of AI” who read this blog, at companies with $1B+ in annually recurring revenue, who have written in to say they believe their job is totally fraudulent but it was the only promotion pathway remaining at the organisation. On a trip overseas, I had the privilege of a meeting with one of the Fortune 500 executives mentioned at the beginning of the post, who will remain anonymous so that they are not executed by firing squad by their board. As we were chatting, it became clear that they were very switched-on and technically competent, and they also happened to be at a company that had committed to the usual battery of exorbitant claims about their recent innovations – we’ve 100x’d our productivity, AI is the future of everything, I am but a vessel for OpenAI to make love to my wife. You know, normal things. But since I had them there without any microphones around, I asked why this was being repeated without opposition. Was it just sales fluff? The answer was a lot more interesting. It was partially ridiculous sales material being delivered to an easily excitable audience, but this was not the dominant factor constraining honesty. Executives at their customers were saying absurd things about achieving 100x productivity, and this meant that if any executive at the vendor said that these gains were not plausible, it would undermine the credibility of the customer’s executive, be perceived as an attack (or heresy), and possibly result in an enterprise contract cancellation. And getting enterprise contracts cancelled because you wanted to opine on something that doesn’t really matter to your organisation’s mission is a great way to get fired. But this company was also a major player, of the kind that signs enormous enterprise contracts with other companies. So presumably there is another vendor that has sold to them, and their CEO is worried that saying something sane will contradict this executive, and very quickly we can see how we can have executives around the world nervously pointing guns at each other, not wanting to be shot first but also watching everything gradually spiral out of control 6 . This is to say that we’re facing a coordination problem around executives being honest around the AI gains they’ve witnessed – if they co-operate, they keep their jobs. If they defect, they will possibly be fired by their embarrassed peers (who have now been implicitly called liars, cowards, or incompetents) and then replaced with someone that will toe the line anyway. If they could all admit the truth at once there might be some hope, but there is no way to coordinate that event. This sounds deeply concerning, but it is worth noting that it means that some executives who are emitting nonsensical statements are not as dull as they might seem at first – they’re in a fraught political environment, where they are surrounded by many people that are gunning for their roles, and subject to the whims of a board that is undergoing similar pressure. Against all the dictates of reason, I have presented on navigating AI hype to people on S&P 500 boards 7 and they are in exactly the same situation – the main comments I remember from the session were board members admitting they were skeptical, but expressing anxiety that their positions were contingent on demanding AI investment. One of them commented “investing this early seems like risk without much upside”. About two years later, I can see now that their decade-old multi-billion dollar organisation is now branded as “AI-native”, whatever the hell that means. All of the above converges on the state that we find ourselves in now, where effective decisionmaking has ground to a halt. Collectively, what started as a few people undergoing either destabilising psychological events or being caught up in hype has now resulted in an environment where leaders cannot speak honestly about their beliefs on how best to guide organisations, for fear of being removed, creating a sort of distributed government by assassination. This means that the least sensible recommendations are going totally unchallenged, resulting in employees being evaluated on totally gameable metrics such as “money spent on AI”, and those employees must play along to avoid being terminated. This has also created an insatiable appetite for purchasing “AI” solutions, which target both true believers that will believe implausible claims, and also non-believers that cannot decline the purchases without having their commitment to the cause coming into question. This means that all offers that are subject to internal politics at an ideologically captured organisation must include AI alignment, even if the value proposition is patently ambiguous. My assessment of the market so far is that a substantial component of the outburst of AI projects are actually non-AI projects with an AI element slapped on after the fact to pass the purity test. For example, I recently witnessed an organisation handling a database migration from an Oracle database to Snowflake – instead of handling the migration directly, the vendor bolted on a preliminary phase which involved trying to get an LLM to automate the translation of the Oracle-flavored SQL to Snowflake-flavored SQL. When the project failed (due to issues getting enough permissions to automate the work, not because an LLM can’t do something that easy), the vendor simply started handling the translation by hand but the company billed it as an AI-driven success because some inconsequential portion of the SQL had been translated by AI before being pasted over. What was actually purchased? A totally standard database migration to help an executive meet the strategic deliverable of decommissioning a system prior to license renewal. What was sold to their superiors? “I allocated a substantial percentage of my budget to AI and it helped me accomplish my mandate.” True AI projects, of the kind that is driven by an LLM as the sole mechanism underlying it, where the project can clearly fail to deliver specific numbers, are actually very rare. We mostly see them in the context of startups, and frankly we have stopped engaging with them because we kept getting to the end of the sales conversation and finding out they wanted us to build the product that they were marketing as completed . However, some projects simply do not have an easy way to tack on the AI label, or the person advocating for them either does not want to lie or has not understood that lying has become necessary. In all cases, this either kills the request for funding outright, or adds a pervasive and intractable drag on all communications, as every request must be worked and re-worked until it is “AI enough”. Failure to comply will either result in denial or, in many cases, a demand from a true believer to know why the extra work “can’t be done with AI”. Many companies have actively publicized that this is their new hiring policy – when a member of staff requests additional headcount, they must demonstrate that they have tried to use AI first. The part that’s being left out is that if you say you used AI and still need the help, you will be labelled “bad at AI” and potentially laid off. The net result of this is that almost every large organisation that I am aware of is no longer able to focus on anything important, unless they are one of the (very) few organisations where AI happens to address their highest priorities. They cannot buy sensible software, hire competent talent, communicate honestly with executives about the state of projects, or undertake any sort of sensible initiative. An emptiness falls through you As you realize what this means You're starting to feel what I feel Now you've seen what I've seen – So Sick , Domesticated Incels This is an unfortunate situation to be in, but it will pass eventually. I’ve learned a lot about the latent insanity that we have inculcated in our leadership strata, and unfortunately those traits will persist long past the current bubble, merely awaiting another similar reactivation trigger – and some organisations will stay captured until they have totally collapsed, in the way that not everyone has successfully moved away from the dreadful blockchain affair. That’s something to write about for another time. What I wanted to get to were some thoughts on surviving the immediate crisis, either by directly making systemic improvements or by holding onto your sanity. I’ll start with the “making improvements” part, because that’s the situation I find myself in the most frequently. We’re going to do a lot of sucking it up and smiling here. This section assumes that you are trying to achieve some goal that isn't repairing the organisation's manic stance, but either trying to course-correct a specific project (and possibly risk getting fired as either a leader or consultant) or achieve some totally unrelated goal. This is for people that are just waiting for the bubble to burst and trying not to go nuts. Fight the good fight, and don’t let the bastards grind you down. Godspeed. Also, and this is 100% true, Matt Mullenweg once asked me for coffee because he read the AI piledrive essay , and in context probably enjoyed it, but had to cancel because he hadn’t realized he had a flight later the same day. I am willing to pay a competent witch to hex him for this slight.  ↩ We have rejected all AI implementation work. It is absolutely a gigantic bubble and we have minimized our exposure to it – every single one of our current contracts would be totally unaffected by OpenAI collapsing, save for perhaps some second-order effects such a recession causing a client to become unable to pay us. And there’s nothing we can do to insulate ourselves from that anyway.  ↩ One of the most valuable rules I’ve heard, from Gerry Weinberg, is that consulting is influencing people at their request . Unless someone has indicated that they want us to stick my nose in, usually by explicitly saying they want guidance on general data strategy, we just let the projects fail in peace. You can barely recognize me, I’m so calm these days.  ↩ We have since kissed and made up in private, though I don’t think we’ve budged at all on the core points of our viewpoints. I maintain that Thomas is a very talented writer with a lot of good advice who just happened to blow it massively that one time because he takes Hackernews commenters too seriously. We all have our weaknesses. Mine is people telling me that “Scrum is good if you do it right”.  ↩ This is always baffling to me as a matter of being a responsible adult. If I was somehow CEO at a hospital or civil engineering firm, I would not for a second think it’s my place to start mandating specific procedures or building techniques without explicit agreement from the professionals on staff – how fucking clueless are the non-technicians who have attended a few talks and are now making mandates about how their extremely expensive professionals are doing their jobs?  ↩ If you’re an executive, board member, or anyone in charge of an “AI project” that feels trapped, I would love to hear from you. I will file the serial numbers off any stories very carefully, as I’ve done here and in every other article.  ↩ This sounds very fancy, but I think it was secretly one of those compulsory professional development things and half the audience were just like, making dinner. Truly, HR and professional bodies make victims of us all.  ↩ Where possible, when raising issues, do not have conversations about the state of AI projects in group settings, as this creates a dynamic where each individual member of the group is worried about outing themselves in front of their peers. Arrange for one-on-one settings. Make it clear that you are willing to countenance that the current AI environment is frothy, and that you will keep opinions unidentifiable when raising them elsewhere. Be extremely aware that the most outspoken people can be identified by their peers, so take care to avoid exposing your sources by, e.g. direct quotes. In the event that only a small minority (say, one person in a group of six people) is willing to speak out, it might be worth giving up and moving on to a patient that has better chances. For ongoing projects, an effective trick that I believe I picked up from Secrets of Consulting is the anonymous poll, where you can ask individuals to rate their opinion of an AI project’s success chances on a scale of 1 to 10. The typical split I have observed is half of those involved rating the project at a 3/10 and others at around an 8/10 – a clear bimodal split on a project that was already three years late . Bringing this data to a CEO can be an effective method of pointing out that some information is clearly being hidden from them on the state of the project. Always involve people on the ground. The only source of data on whether projects are succeeding or the investment is going anywhere are the people that use it for their day-to-day activity. Care must be taken to bring them into the environment where they are treated with respect (all sufficiently large companies have people that view subordinates as not-quite-real-people). It is not uncommon to uncover worldview-shaking information in short order – with one client, we uncovered that staff were totally unaware they had been given licenses for AI tooling, which cast into doubt all productivity claims. Do not question the broadest claims about AI. I cannot emphasize this enough. If someone says “AI is changing everything”, just let it pass if your goal is to fix an object-level problem rather than challenge the reality at the institution. The challenge can only come after you have gained the trust of the most senior person involved. Trust is gained over a meal in private where you assuage their anxieties, not by embarrassing them in front of peers. Remember that you do not know what statements have been emitted prior to entering a room. There will sometimes be people that have publicly committed to statements like “I am 100x more productive than I was last year”, and some may even wish they hadn’t said that but are too embarrassed to walk it back. In an untested room, common sense like “LLMs should not be allowed to deploy code without human review” can kill your chances to make an impact before you’ve even started. My practice requires me to maintain an honest relationship with my clients or the whole thing falls apart, so I can’t do this – but honestly, if you work in the fire service and need money to stop a puppy from catching fire, just lie. It’s fine. History will forgive you. Add a $10,000 AI chatbot to your project, exclusively discuss that part in meetings, whatever . Save that puppy. I have bad news – accept that you are probably not going to meaningfully push back on any of this. This is not a feature of AI, it’s a feature of dysfunctional companies. If you feel like you’re going absolutely nuts, consider switching over to contracting. I’ve advocated for contracting many times over full-time employment, but you’ll get paid a lot more and be left out of most internal politics. Also when you run into a really intolerable situation, you’ll know that you’ve got a fixed end-date. I do my best to limit my uptake of AI-related news, as it is pretty crazy-making and unproductive to consume. I no longer visit Hackernews, Reddit, or really anywhere where I am going to be drip-fed nonsense, though I allow myself exceptions for very funny things like Apple suing OpenAI over alleged corporate espionage . Consume exactly the amount you need to feel like you aren’t going insane, then stop . Ditto for complaining with friends – and tell them that’s why you’re talking about it, which buys a lot of tolerance. When someone tells me they are using AI for something when they really shouldn’t be, I smile and nod as long as they are unlikely to get themselves killed. Even family. Especially family. When someone asks me for my opinion of AI as a programmer, I recommend saying “Oh, that stuff is pretty overblown” and then changing the topic, unless they are in a position where their opinion might influence something important. Non-programmers need this guidance the most. If you’re being asked to review huge volumes of terrible AI code, just assume that the organisation is going to burn you out and fire you. You will not convince the person drowning you in 2000 line PRs to stop. Start looking for a new job as if you have already been fired. I have seen this happen many times now, and it always plays out the same way – do the job search while you have energy. Don’t worry if your speed drops or management gets annoyed at you. There is no way to avoid that, you can simply choose whether it happens now because of your job search, or later because you are too depressed to work anymore. If your manager is responding to you with clearly AI-generated text, use AI to respond to save your sanity and then look for a new job. Many people assume they will get in trouble for being that obviously rude. You will not, this particular behavior is exhibited only by true believers, and they actually like that you’ve clearly not bothered to engage with them. I know, it’s fucking wild. If you’re being asked to max out on token usage, look for a new j – okay look, you get it, right? Go find a job that isn’t going to wrench reality from your tenuous grasp. They do exist, largely at companies so small that they don’t turn up on job platforms. It might take months to find one, so start now. Also, and this is 100% true, Matt Mullenweg once asked me for coffee because he read the AI piledrive essay , and in context probably enjoyed it, but had to cancel because he hadn’t realized he had a flight later the same day. I am willing to pay a competent witch to hex him for this slight.  ↩ We have rejected all AI implementation work. It is absolutely a gigantic bubble and we have minimized our exposure to it – every single one of our current contracts would be totally unaffected by OpenAI collapsing, save for perhaps some second-order effects such a recession causing a client to become unable to pay us. And there’s nothing we can do to insulate ourselves from that anyway.  ↩ One of the most valuable rules I’ve heard, from Gerry Weinberg, is that consulting is influencing people at their request . Unless someone has indicated that they want us to stick my nose in, usually by explicitly saying they want guidance on general data strategy, we just let the projects fail in peace. You can barely recognize me, I’m so calm these days.  ↩ We have since kissed and made up in private, though I don’t think we’ve budged at all on the core points of our viewpoints. I maintain that Thomas is a very talented writer with a lot of good advice who just happened to blow it massively that one time because he takes Hackernews commenters too seriously. We all have our weaknesses. Mine is people telling me that “Scrum is good if you do it right”.  ↩ This is always baffling to me as a matter of being a responsible adult. If I was somehow CEO at a hospital or civil engineering firm, I would not for a second think it’s my place to start mandating specific procedures or building techniques without explicit agreement from the professionals on staff – how fucking clueless are the non-technicians who have attended a few talks and are now making mandates about how their extremely expensive professionals are doing their jobs?  ↩ If you’re an executive, board member, or anyone in charge of an “AI project” that feels trapped, I would love to hear from you. I will file the serial numbers off any stories very carefully, as I’ve done here and in every other article.  ↩ This sounds very fancy, but I think it was secretly one of those compulsory professional development things and half the audience were just like, making dinner. Truly, HR and professional bodies make victims of us all.  ↩

0 views
James O'Claire 1 months ago

How to stay in the coding flow using LLMs

We all know that moving to LLMs and agents has caused the feeling of losing touch with parts, or maybe even all, of a code base. This isn’t just something that is problematic for managing and handling the translation from business logic to implementation it is a problem because it feels exhausting . I’ve had coding sessions that lasted 12 hours and afterwards felt great. Meanwhile I’ve done LLM prompting for a few hours and felt exhausted or unsure of what I did. Lately I’ve been keeping this in mind and have been looking for a few ways in which I can maintain a flow state and take advantage of LLMs. Remember back in 2025 when this was the default way of using them? I actually still find this to be my preferred way. Using LLMs with code harnesses in projects injects so much unnecessary information that asking simple questions gets out control. For example, here I’m exploring some data, and I wanted a quick regex, I turned over to my VSCode chat window, and forgot that it was an agent, and asked it the question. It proceeds to start looking at the files, wanting to run code etc. All off target of what I need . So next I switched VSCode to “Ask” instead of agent, again the LLM is flooded with context about my project and proceeds to output a massive amount of distracting and off topic code suggestions. Switch to a browser chat window which has little to no context about what you’re working on and ask it my specific question, boom it spits out a few quick regexes for my Python list comprehension that are exactly what I need . Is this bad advice? Well, maybe. But was this what you’re already doing, definitely. But the point here is to multitask coding on more than one thing at a time. I’ve found that this keeps me in the flow state much better than if I let myself browse the news. So instead of switching from your agent -> browse social media switch between multiple projects. This depends on how your code / work is structured, but depending on the scope this means either switching between several agents in the same project or having several projects open at once. Types of positive multitasking to stay in the zone: For me, working on AppGoblin’s free ASO and mobile app ecosystem data , I have certain areas that *I* need to understand what is happening, for those reasons I do not let AI write anything more than boiler plate code. The clearest example of this I can give is SQL, where a lot of my most important relational logic exists. Sure, I can let an LLM one shot a complicated SQL and it will “work” but come weeks (or months!) later and I’ll find a complicated bug that slipped in. It’s not even necessarily about who was right/wrong in this situation, it’s that *I* need to know what’s going on in certain parts of the codebase. Something that ‘looks fine’ is a terrible feeling that later it was not what I wanted. This last one is probably best suited for other data crunchers out there, but it’s where I find a great sweet spot for staying in the zone. My favorite way to write code has always been to write code in an editor and send line to a REPL. This is also more or less how SQL gets written as well where you build queries in your SQL editor by slowly making changes to the data, checking values / assumptions and eventually getting to your final SQL query. With the LLMs, I find myself using this flow lately: It’s more or less the same as I did before, just a lot less writing and let’s me hold onto the difficult concepts longer. If you’re actually in the flow of editing code, the best way to augment your coding is with code completion. I’ve found this to be the most powerful in that I don’t even have to start letting my mind wander for how to do some boiler plate code, it just pops up automatically. I love this because it helps me think at a high level in the code without the distractions of trying to remember how to do something when the how is not the important part. Probably the only issue with this is that code completion can be quite annoying and distracting in some situations. For example, writing free form and handling imports at the top of a file are examples where LLM ‘helpful’ code completion is just not helpful. If you enjoyed this feel free to share. Working on related projects File and project cleanup. LLMs generate many extra files and code and it’s best to stay on top of that yourself. Go through and delete extra files. Try asking LLMs for advice on what to remove, but do be careful with this idea. Tell LLM to write new code for processing data Step through the code my self line by line, checking the hotspots where I know assumptions / tricky data might be

0 views
Evan Hahn 1 months 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 .

1 views
Simon Willison 1 months 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
David Bushell 1 months ago

Behold the perfect algorithm!

1984, Minority Report, Black Mirror — bedtime stories compared to the horrors the UK Government publish, am I right? I’m led to believe “Watch this space” is the latest propaganda piece from His Majesty’s Nanny State . I haven’t read past the title but according to gaming site Dexerto, YouTube lawyers read it and YouTube ain’t happy. Poor little YouTube. The government is consulting on options, considering whether to make public service news easier to discover on sites like YouTube and TikTok, with greater prominence and with more visibility during periods of major public importance. It also seeks to discuss misinformation and online viewing habits. YouTube urges creators to fight proposed UK algorithm changes - Matthew Benson, Dexerto I glossed over the Dexerto article too. This whole thing is something about kids being hooked on Skibidi and not paying their racketeering license fee . Minecraft Let’s Plays will be spliced with a BBC impartiality report on what some fascist gammon thinks. Should the proposal become law, of course. This is somewhat of a dilemma for a guy like me. If there’s one thing I hate more than a meddling GOV.UK, that might just be Big Tech . The thought of Google et al being ruffled warms my heart like a hot cup of tea on the summer solstice. That was too many words on something I never read so I’ll get to the lede. I’m about to reveal the secret sauce that Big Tech has tried to suppress. The one true algorithm, which ironically might be their saviour. Only one parameter is required in the perfect algorithm: who I choose to follow. I’m literally providing the exact data needed to curate my feed. I know what defenders of the deceptive arts are thinking: but algorithms are proven to increase engagement! — I know, Sherlock. Do you enjoy your doomscrolling misery? Not every metric needs to be min-maxed at the expense of human health. Modern apps sucks. Modern media sucks. Stick your “algorithm”. † It’s been decades since I studied SQL and database normalisation so please have mercy. Thanks for reading! Follow me on Mastodon and Bluesky . Subscribe to my Blog and Notes or Combined feeds.

0 views
Simon Willison 1 months ago

sqlite-utils 4.0rc2, mostly written by Claude Fable (for about $149.25)

I wrote about the sqlite-utils 4.0rc1 release a couple of weeks ago. Since we only have Claude Fable on our Max subscriptions for a few more days, I decided to see if it could help me get to a 4.0 stable release that I felt truly comfortable about, since I try to keep to SemVer and like my incompatible major versions to be as rare as possible. I started with this prompt, in Claude Code for web on my iPhone: Here's that initial report it created for me. There were some significant problems that I hadn't myself encountered yet - 5 that Fable categorized as "release blockers". Here's the worst of the bunch: 1. never commits and poisons the connection (data loss) ( ) runs its DELETE via a bare with no wrapper — compare at , which wraps correctly. The connection is left , so every subsequent call takes the savepoint branch ( ) and never commits either. Reproduced end-to-end: That's a really bad bug! Very glad I didn't ship that, although at least it would have been a bug I could fix in a 4.0.1 point release, not a design flaw that would force a 5.0. Over the course of 37 prompts, 34 commits and +1,321 -190 code changes over 30 separate files, we worked through the entire set of feedback in turn, making several other design improvements along the way. A weird thing about coding agents is that harder tasks like this one actually provide more opportunity to do other things at the same time, since the agent sometimes needs 10-15 minutes to churn away on a new task. I went out to enjoy the Half Moon Bay 4th of July parade, occasionally checking in and prompting the next step for Fable from my phone. Full details in the PR and this shared transcript . I switched to my laptop for the final review, which I conducted through GitHub's PR interface. The most significant changes relate to transaction handling, which was the signature new feature in the earlier RC . The new RC now includes comprehensive documentation on the new transaction model, the intro to which I'll quote here in full: Every method in this library that writes to the database - , , , , , , , , and the rest - runs inside its own transaction and commits it before returning. Your changes are saved to disk as soon as the method call finishes: The same applies to raw SQL executed with db.execute() - a write statement is committed as soon as it has run. You never need to call , and you do not need to close the database to persist your changes. There are exactly two situations where you need to think about transactions: You want to group several write operations together, so they either all succeed or all fail - use db.atomic() . You are managing a transaction yourself with , in which case nothing is committed until you commit - the library will never commit a transaction you opened. In reviewing Fable's documentation - I find that reviewing the documentation edits first is an excellent way to build an initial understanding of what has changed - I spotted this detail : and the automatic per-method transactions are designed for connections in Python's default transaction handling mode. Connections created with the Python 3.12+ or options are not supported, because and behave differently on those connections. I admit I hadn't thought about how would react to the more recent autocommit setting , added in Python 3.12. It turns out "behave differently on those connections" equated to almost the entire test suite failing, so I worked with the model to ensure that this difference would not break how the library works. I used to think that the idea of having one model review the work of another was somewhat absurd - it felt weirdly superstitious. The problem is it really does work - I've started habitually having Anthropic's best model review OpenAI's work and vice versa, because I've had that turn up interesting results often enough to be valuable. I prompted Codex Desktop and GPT-5.5 xhigh with the following: Which was enough to turn up two issues worth investigating: I pasted that into a fresh Fable session, which ran some experiments to confirm the problem: Both findings were confirmed. called first, which auto-commits writes, and only then checked — so committed the update before raising . And the commit lived at the end of the returned generator, so it never fired unless you exhausted the iterator — or an un-iterated call left the transaction open, contradicting what the changelog and docs promise. Here's the PR with the fix, and the full Claude Code transcript . Reviewing this code helped me build a better mental model of the edge cases of SQLite transaction semantics! I upgraded to the Claude Max $200/month plan (I was previously on $100/month) to increase my Fable allowance for the remaining time until the July 7th Fablepocalypse , when even Claude Max subscribers will have to pay full API cost for the model. I was curious as to how much this would have cost me if I had been paying those costs directly. At first I thought those numbers weren't available to me since I had run the work remotely using Claude Code for web, and then I realized I could run AgentsView inside that existing session to get that cost estimate! Claude figured out how to use the command and came out with the following: I'm very glad I'm on that subscription! I really should have followed my own advice and leaned more heavily into subagents with cheaper models. Here's what claude.ai/settings/usage is showing me right now: I have several other major Fable-driven projects on the go right now as well, with the goal of hitting 100% on that Fable bar just in time for the price increase. Here are the full release notes for the RC. I had Fable add these to an "Unreleased" section of the changelog as each change landed, reviewing them as it went. This has the neat side effect that the commit history of the changelog acts as a concise summary of each of the changes that went into the release. In the past I've had a policy of writing release notes by hand, but honestly these are better than I would have created myself. Release notes are a great example of writing that I'm OK to outsource to agents because they need to be boring, predictable and accurate. Breaking changes: Everything else: 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 . You want to group several write operations together, so they either all succeed or all fail - use db.atomic() . You are managing a transaction yourself with , in which case nothing is committed until you commit - the library will never commit a transaction you opened. [P1] sqlite_utils/db.py:663 now rejects non-row statements only after calling , and sqlite_utils/db.py:705 auto-commits those writes first. So raises but the update is already committed. That is a surprising side effect for a method documented as “can only be used with SQL that returns rows.” [P1] sqlite_utils/db.py:672 through only commits after the returned generator is fully exhausted. without iteration, or common usage, leaves the transaction open and the write can be rolled back on close. This contradicts docs/changelog.rst:15 and docs/python-api.rst:232 , which say it takes effect without iteration. Write statements executed with are now committed automatically, unless a transaction is already open in which case they join it. Previously they opened an implicit transaction that stayed open until something committed it - writes appeared to work when read on the same connection but were silently rolled back when the connection closed. Code that relied on rolling back uncommitted writes should use the new method to open an explicit transaction first. The transaction model is documented in full at Transactions and saving your changes . now executes its SQL as soon as it is called, rather than waiting until the returned generator is first iterated. Rows are still fetched lazily during iteration. SQL errors are now raised at the call site, statements such as are executed and committed immediately without needing to iterate over their results, and passing a statement that returns no rows - previously a silent no-op - now raises a recommending instead. A statement rejected this way is rolled back before the error is raised, so it has no effect on the database. Python API validation errors now raise instead of . Previously invalid arguments - such as with no columns, on a table that does not exist, or passing both and - were rejected using bare statements, which are silently skipped when Python runs with the flag. Code that caught for these cases should catch instead. and now raise if a record is missing a value for any primary key column, or has a value of for one. Previously such records - which can never match an existing row - were quietly inserted as brand new rows, or triggered a confusing after the insert had already taken place. and now raise a if called while a transaction is open. Previously they would silently commit the open transaction as a side effect of changing the journal mode, breaking the rollback guarantee of and of user-managed transactions. The class no longer has an method. It existed only to raise , since full-text search is not supported for views - calling it now raises instead, and the method no longer appears in the API reference. The command shows a clean error when pointed at a view. The no-op flag has been removed from the and commands. Type detection has been the default for CSV/TSV data since 4.0a1, so the flag did nothing - invocations using it should simply drop it. remains available to disable detection. now raises a if passed a connection created with the Python 3.12+ or options. and behave differently on those connections, which previously caused every write made by the library to be silently discarded when the connection closed. Fixed a bug where , and did not commit their changes, leaving the connection inside an open transaction. Their work - and any subsequent writes - could then be silently rolled back when the connection was closed. All three now use , consistent with the other write methods. The command now refuses to drop a view, and refuses to drop a table. Previously each would silently drop the wrong type of object if the name matched. Both now exit with an error suggesting the correct command to use. Migrations applied by the new migrations system now run inside a transaction, together with the record of the migration having been applied. If a migration raises an exception its changes are rolled back and it stays pending, so it can be safely re-applied after the error is fixed. Migrations that cannot run inside a transaction, such as those executing , can opt out using - see Migrations and transactions . and now detect the primary key or compound primary key of an existing table, so the argument is no longer required when upserting into a table that already has a primary key. can now be used to insert a row consisting entirely of default values into an existing table, using . ( #759 ) Improvements to the command: values that do not match any known migration are now an error instead of being silently ignored, now works correctly with migration files that still use the older class, and is now a read-only operation that no longer creates the database file or the migrations tracking table. now returns migrations in the order they were applied. New , and methods for taking manual control of transactions, as an alternative to the context manager. New documentation: Transactions and saving your changes describes how transactions work and when changes are committed, and a new Upgrading page details the changes needed to move between major versions.

0 views
Aran Wilkinson 1 months ago

Owning the Harness

Over the past year I've started noticing something in the conversations I have with others when talking about AI usage at work. Ask someone how their company is handling AI tooling and you'll almost always get one of two answers. Either they've gone all in. Every engineer gets a Copilot or Cursor or Claude licence, token budgets don't exist, and leadership has basically said "spend what it takes, we'll figure out the ROI later." Or they're in the other camp: tight budgets, a handful of engineers with access, everything scoped to a pilot project with measurable outcomes before anyone else gets a look in. There isn't much middle ground. Companies pick a lane and commit to it, and that choice says more about their culture than any AI strategy document ever could. But the longer I watch this play out, the more I think both camps are arguing about the wrong number. One side doesn't track cost at all and the other tracks it obsessively. Yet the thing that actually decides the bill, and whether any of this is sustainable, is one most of them never touch: the harness their models run through, and who controls it. That's what I want to get into here. The all-in companies aren't subtle about it. Every engineer gets a Copilot, Cursor, Claude or ChatGPT Enterprise seat as part of onboarding. There's no token budget to track, no approval chain to navigate. The philosophy is straightforward: AI is the biggest productivity shift since the internet, and the companies that embed it deepest and fastest will pull ahead. Worrying about per-seat costs right now is like worrying about electricity bills in 1890. In practice this looks like engineers using AI for everything. PR reviews get run through a model before a human sees them. Architecture discussions start with an AI-generated proposal that the team then critiques. Onboarding documentation gets drafted by pasting the codebase into a chat window and asking for a summary. Some teams are running AI agents that pick up tickets, open pull requests, and write their own tests, with varying degrees of success. The cultural shift is the part that interests me most. When everyone has unlimited access, the conversation changes from "should we use AI for this?" to "why wouldn't we?" That's a genuine acceleration. Junior engineers who might have spent a week figuring out a Kafka consumer are shipping in a day because they've got a model walking them through it step by step. Senior engineers are spending less time on boilerplate and more time on the hard problems they actually enjoy. The downside, of course, is that unlimited access doesn't come with unlimited judgement. I've written before about what happens when you run AI-generated SQL without understanding it , and that problem scales with the size of the organisation. When everyone's moving fast and the AI is confidently wrong about something subtle, the blast radius gets bigger. The other camp is just as deliberate, but their starting assumption is different. They see AI as a tool that needs to prove its value before it gets rolled out broadly. So they scope a pilot: maybe five engineers on a single project, maybe a specific workflow like test generation or documentation. Someone owns the budget spreadsheet. Someone else is tracking which prompts produced useful output and which ones burned tokens on nothing. The philosophy here is that cost optimisation is a first-class concern, not something you figure out after the invoices land. These companies want to know what they're getting for their money before they commit to a hundred seats. They're not anti-AI (most of them are genuinely interested), but they're treating it like any other tooling investment rather than a cultural transformation. What this looks like in practice is more restrained. A small team experiments, reports back, and leadership decides whether to expand. Token budgets are real and sometimes tight enough that engineers think twice before asking the model to rephrase a function they wrote six months ago. Project selection is careful: you pick something where the AI's contribution can actually be measured, so you have data to justify the next round of spending. I understand the logic. If you're running a team where every pound matters, you don't hand out AI subscriptions like stickers at a conference. But there's a cost to caution too, and I think it shows up in ways that don't appear on the budget spreadsheet. Here's the part both camps tend to underestimate: a lot of the cost was never theirs to control in the first place. The two biggest levers (which model you run and which harness you run it through) sit largely with the providers, not with you. The model is the obvious one. Running everything through the frontier model versus a cheaper, smaller one can be an order of magnitude difference on the invoice, for output that's often good enough either way. But the harness matters just as much and gets talked about far less. The same task, on the same model, can burn wildly different amounts of tokens depending on the tool wrapping it. A harness that re-reads the entire codebase on every turn, or pads each request with a bloated system prompt, will quietly cost you several times what a leaner setup would for an identical result. You can watch this happen in the tools you already use. Something like Claude Code doesn't send a fixed system prompt. It assembles one on the fly for every request, pulling in your project instructions, the definition of every tool it can call, the skills you've enabled, the MCP servers you've connected, and a pile of environment context on top. Add more skills, connect more servers, write more project config, and that prompt grows, and it gets sent again on every turn of the conversation. Under per-token pricing that's a standing cost most people never see, because the harness assembles it for you and never shows you the system prompt it built. Caching softens the repeated static part, but it doesn't make it free, and the parts that change from request to request aren't cached at all. That has an awkward implication for the cautious camp. You can count seats and cap budgets all you like, but if the real spend is being driven by model choice and harness efficiency, you're optimising the wrong variable. And it's just as awkward for the all-in camp: you're building on pricing and tooling decisions the provider can change under you at any time. A model gets more expensive, a harness gets chattier in an update, and your costs move without you having touched a thing. This is getting harder to ignore as the billing model shifts. The industry is quietly moving from flat-fee subscriptions to consumption-based pricing, and the early numbers are startling. In a June 2026 report , Gartner found that nearly a quarter of technology leaders are already spending between $200 and $500 per developer each month on tokens, with around 6% over $2,000. It also projects that by 2028 AI coding costs will overtake the average developer's salary. That headline deserves a caveat: Gartner's "average" is a global one, pegged to roughly $2,000 a month, not a senior Western salary. But the direction of travel is the point. When a harness can quietly burn 50,000 tokens on a single test-suite run, your spend is set by tooling decisions the provider can change under you, not by how many seats you bought. I've watched this play out up close. When the bill lands on the company card rather than your own, spending thousands of dollars a month on tokens stops feeling like spending at all. I've seen engineers burn through more in a month than their whole tooling budget used to be for a year and not think twice about it, because it simply isn't their money on the line. That's fine while the models hold their price, but they don't. Each new frontier model tends to arrive more capable and more expensive than the last, often close to double, and usage only ever climbs. A way of working that already looks careless at today's prices doesn't become sustainable when the underlying cost doubles. It becomes less. Neither approach is cost-free. The all-in camp gets speed and cultural momentum, but they're burning through budget on something whose ROI is genuinely hard to measure, and they're building a dependency on tools that might change pricing or disappear tomorrow. The cautious camp has control and cost visibility, but they risk falling behind competitors who are iterating faster, and they're potentially demoralising engineers who see peers elsewhere shipping with better tools. The risk I think about most with the cautious approach isn't the money they're saving, it's the talent cost. Good engineers know what tooling is available elsewhere. If they're stuck on a team that's still "evaluating" AI while their friends at other companies are shipping with it daily, that's a retention problem with a price tag that doesn't show up on the AI budget line. I don't think this has to be binary. The answer that makes the most sense to me is structured experimentation: give engineers access, but with guardrails that create visibility without creating friction. Let people use the tools, but measure what's actually working and redirect effort towards the patterns that produce results. That's closer to how I work personally. I use AI heavily, but never as a black box. I write a PRD first, break the work into small, reviewable tasks, and treat the AI's output as a draft that I'm responsible for, not a solution I'm rubber-stamping. The model generates, I review. That separation, with generation and judgement living in different hands, is the part that keeps me from repeating the mistakes I made when I lost that database. It's also why I've spent time building my own tooling and harnesses rather than living entirely inside off-the-shelf ones. Earlier I said the harness is one of the levers the provider controls. Building your own is how you take some of it back. When you own the loop, you decide how much context gets sent on each turn, which tools and MCP servers the model can actually reach, and where a task should stop and hand back to you. That control shows up directly on the invoice: the same work, on the same model, costs a fraction of what it does through a harness that reloads the world on every request. It's more effort up front, but it turns token spend from something that happens to you into something you decide. Owning the loop also means you stop paying frontier prices for work that doesn't need them. Not every step of a task wants the same model. The expensive, heavy-thinking models earn their keep on high-level work such as planning an approach, breaking a problem into reviewable steps, and weighing up an architecture. But once the plan exists, most of the implementation is narrow, well-specified work that a cheaper, faster model handles perfectly well. Route the thinking to the expensive model and the grunt work to the cheap one, and the bill drops again with no real hit to the output. That kind of routing is hard to pull off inside an off-the-shelf tool that runs everything through whichever single model it defaulted to. You can see the payoff in the wild. Mitchell Hashimoto recently described running exactly this kind of split, using one model as a planner and architect, a different one as the coder, and then the first model again as a judge to check the work. The numbers are the striking part. At API pricing he put the planning and judging steps in the region of a few dollars, against the $50 or more that a single full round trip through one frontier model would typically cost. It's an early experiment, and he's the first to say the longevity isn't proven, but the shape of the saving is hard to argue with. Same work, broken across the right models at the right price points, for a fraction of the bill. I'm not the only one going down this road. There's a small but growing group of engineers doing the same, a lot of them building on Pi , a deliberately minimal coding-agent harness you're meant to reshape around your own workflow rather than bend yourself to fit. That framing is the whole point. People are shaping harnesses to match the way they actually work, and tuning them to get the best out of the specific models they're driving. That's exactly the control the off-the-shelf tools don't hand you. There's enough here for its own article, which I'll write separately. For now the point is simply that owning the harness isn't hypothetical. People are already doing it, and it changes both what the tools cost and how well they fit. The approach I landed on isn't complicated: write a plan, break it into tasks, review every piece of AI output before it touches anything real. The structure matters more than the specific tool or model you're using. Without it, you're just hoping the AI doesn't lead you off a cliff. I don't see why that pattern couldn't scale to a team or a company. Give people access. Expect them to use it. But also expect them to own what they ship, to understand the code they're committing, and to stay curious enough to catch the model when it sounds certain and isn't. The guardrail isn't a token budget. It's a culture of reviewing output before trusting it. I don't think either extreme gets it right. The all-in approach risks the kind of blind trust that cost me a database. The cautious approach risks paralysis dressed up as prudence, and in an industry that moves as fast as ours, that's its own kind of expensive. The answer is almost certainly somewhere in between. Give people access to AI tools. They're genuinely useful and they're not going away. But teach them to use those tools with judgement. Create a culture where questioning the AI's output isn't seen as a lack of skill but as a basic professional reflex, the same way you'd review a colleague's pull request even if they were the best engineer on the team. What matters isn't how much you spend or how many tokens you burn. It's whether that spend is something you control or something that just happens to you. The teams that come out of this well won't be the ones with the biggest budgets, or the strictest ones. They'll be the ones who own the harness their models run through, route the right model at the right cost to the right work, and keep a human in the loop whose judgement can tell a right answer from one that only sounds right. Spend you can't see or steer is the real risk. Spend you own is just a tool doing its job. That ownership, not the size of the invoice, is what decides whether AI makes your team better or just makes them faster at being wrong.

0 views
matduggan.com 2 months 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
Simon Willison 2 months 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
Lalit Maganti 2 months 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 2 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
Stratechery 2 months ago

An Interview with Microsoft CEO Satya Nadella About Finding Core Competencies

Listen to this post: Good morning, This week’s Stratechery Interview is with Microsoft CEO Satya Nadella . I have previously interviewed Nadella in May 2024 , October 2022 , April 2020 , and May 2019 . As I noted yesterday , I spoke to Nadella shortly after the conclusion of his keynote at Build , Microsoft’s annual developer conference . One notable thing about the keynote was the fact that Nadella was — outside of product demos — the sole presenter; one gets the sense he has shifted into a much more hands-on role at Microsoft over the last year. The reasons why are clear: my first question to Nadella was if he was happy about where Microsoft was currently positioned as a company. We talk about the reasons for that question, the status of the company’s partnership with OpenAI, and whether Microsoft has invested sufficiently in AI infrastructure. Then we talk about the future of software, Microsoft’s business model in the age of AI, and if they can operate independently from the leading edge models. At the end we talk about Project Solara and whether Microsoft will ever pay residents to build data centers. One note, with regards to a misunderstanding towards the end of the interview: there is no documentation I could find about being able to use Copilot Cowork with non-Anthropic models; Microsoft’s own documentation fits my understanding. As a reminder, all Stratechery content, including interviews, is available as a podcast; click the link at the top of this email to add Stratechery to your podcast player. On to the Interview: This interview is lightly edited for clarity. Satya Nadella, welcome back to Stratechery. SN: It’s great to be with you, Ben. So first off, I don’t know if you realize this, but at least according to my daughter, the defining word for the real grinders in Gen Z — first off, LinkedIn is like the social network. SN: That’s great! Number two, the word they all use is “build”, “I’m building, I’m building”, so who knew when I was at the first Build, I think, in 2010? Or was it 2011? Who knew you were such a trendsetter? SN: (laughing) There you go, I’m thrilled that your daughter is building and is on LinkedIn. Yeah, well, I’m not sure if she’s on there, she’s more making fun of people, so we’ll see how it works. We last talked the summer of 2024 after Build, this was up in Seattle. To say a lot has changed since then is an understatement. I had a bunch of questions I wanted to ask you about the business as a whole, things going on, I’m going to start with those, then I have questions about the presentation at the end. But relative to that, I want to ask you one simple question: Are you happy with Microsoft’s current competitive position? SN: You know, always this is the trickiest thing, you can sit here and say, “I’m happy” — that means you’re not ambitious enough and when you say, “If you’re not competitive, what the heck are you doing?”. And plus you have like 57 different product lines. SN: I’d say the thing in these platform shifts in particular is to, one, get the conceptual model of, “Where is the opportunity for us as a company?” — most people measure competitive position as if it’s a complete zero-sum game, and it’s never been the case. Which is, it is not the case with the cloud, it is not the case in client-server, and so to me, “What is Microsoft uniquely capable of doing in this new world” — that’s the key thing that we have to answer before we even get to the competitive position. In that context, “What is it that we really have a shot at?”, which is we can be a trusted purveyor of a platform, which is what we’ve always done, that allows people to create more value on top of a platform, which is again the DNA we have. Even in a world where these frontier models seem to have no limit— A very large appetite. SN: They have large appetite. That is what I feel even this Build , this conference, we are at that state where we can now really turn this from any one frontier model to saying, “Hey, there is actually a way for a frontier ecosystem to emerge where there are many stakeholders who all actually are operating with their own frontier intelligence”, that is a place where I think we have a unique shot, a unique competitive angle, and most importantly, brand permission. This is the other thing I’ve learned, Ben, which is every company thinks they can do everything, and then they realize that the world doesn’t need them to, the world wants them to do the one thing. Is that a lesson that you had to learn? SN: Yeah, absolutely. I’ve always said this, at Microsoft we are at our best when we do what the world expects us to do, we are at our worst when we do things out of envy, which is just because somebody else had some cool hit, somewhere, doesn’t mean we should go do that. But enough about the Zune, right? SN: (laughing) Yeah, Zune was a great device, but the world didn’t need Zune from us, and so that was the end of it. This identification of your unique capabilities, is that one of the changes over the last two years where that has emerged? SN: Yeah, in fact, it has emerged and also the world’s kind of gotten to it. Has it been forced on you to an extent? SN: Yeah, even my own conceptual understanding, I started by thinking of, “What are models?”, models are kind of like some stateless APIs, then I adjusted and said, “Oh, maybe there’ll be like databases” — they’re really more than that. I don’t remember talking about this with you, but last time I talked to [Microsoft CTO] Kevin [Scott], we analogized it to processors at some point, and you actually did make a comparison in terms of the partnership to your partnership with Intel. SN: Exactly. So the question now is, it’s a better conceptual model to think of what we’re doing is you have to really build a learning machine, and any company has to build a learning machine, so what I want to build is essentially a multi-tenant learning system that allows everybody to have their own hill-climbing machine . So that conceptual idea, now I’ve turned what is essentially frontier is not about any frontier model — I want to build whatever you did with M365 or with Azure into a platform which allows everybody to basically build their own hill-climbing machine right because the future of a firm at a foundational level they’ll have human capital they’ll have token capital and for the token capital they need their own hill-climbing machine. All right, so I’ll jump to the end, you released seven new models, you emphasize the work you’ve done to build these models from scratch, not with distilling, not with using other models as teachers — so did you just articulate what the ambitions are with these models? SN: Yeah, there are two sets of things. One is we wanted to build from ground up with clean lineage, the models that we will have that we can license and allow enterprises to continuously hill-climb, so that’s why we want that model. By the way you talked about distillation — the point is to not use distillation during any of our own hill-climbing but at the very end, in fact some of the things that we are doing is, after all, we have all the OpenAI IP, in fact some of the performance gains we get is by doing RKLD, which is reverse knowledge distillation , and RL on top of it. So we have effectively two frontiers, we have our own, we have the OpenAI, and we’re going to use these things to eval match. And the clock is ticking to get to the right state you need to be while you still have that access . SN: Yeah, and there’s five years of it. But the bottom line is at any given point in time, I want to make sure that I’m using the best, most efficient model for whether it’s in coding, whether it’s in security, making sure also in our case, we’ll have a harness that’s independent of these models, we have the GitHub Copilot harness that’s used everywhere across Microsoft. Our goal is to make sure we have a model lineage, which we control end-to-end, we then use OpenAI IP, even with all of the capability it has — ultimately, the tests are going to be the evals for us and our customers. In the long run, the way it was framed today, and I thought it was very compelling, and it speaks to what you just said, was this idea of enterprises being able to take these models and in their own RL environments incorporate their data at a much deeper level than sort of a slap-on RAG implementation or basic post-training. Is that the end goal, though? SN: Yeah, the end goal for me is the following, which is I go back and say, let’s say that they’re a generalist model — if you go back even, Windows could have a release, then another release, and Adobe and Autodesk could keep building and keep going up, what’s the moral equivalent of that? That is the thing. And then in the first time, we said fine-tuning, it kind of didn’t work because we didn’t have the tools, we didn’t have the data collection regime, none of that. But now we have it. So let’s say the generalist models keep getting better, MAI models, let’s say, or OpenAI models, then you have this RLE. Right, but this deep customization of the models you’re talking about is only possible with MAI models. SN: That’s correct, but the thing that we want to start getting everyone on is this multi-tenant hill-climbing system — so if you think about it, we literally turned your use of M365, which already is a multi-tenant system, into a hill-climbing system for you. Okay, I’m gonna have to stop you, I’m going to give you an ELI5 opportunity, explain hill-climbing to the audience. SN: Hill-climbing is basically when you think about, “What does AI do?” — AI is all about taking an objective and continuously learning how to go predict and create that output that is the representation of that objective, and do so continuously. So that’s why a metaphor of hill-climbing is the best way to describe learning. And you want everybody to do this individually on their own hill. SN: Individually on their own. As opposed to like, hitching along. SN: What is your moat as a company? Your moat as a company is your tacit knowledge. In a world where AI exists, and network effects of AI exist, you need your own hill-climbing machine in which the models are learning. So the first thing we want you to do is, people don’t talk enough about this, but the private outputs, the evals, as I think about as, maybe the most important IP a firm creates are these private benchmarks and the private evals where you are tastefully recognizing what’s the output, the quality. And by the way, today’s failure cases are informing you to change the benchmark continuously, it’s not a static thing, that’s kind of how the evals work. And so if you have your private evals, then you have your own reinforcement learning environment that you’ve created, then you invite all the models to show up, and then you say, “Model A, generate the output that is maxing this eval using my environment and my trajectories and model B…”, and I can switch. In that context, the MAI models is one more lineage that you can put into,c and what we proved today was even a very efficiently trained reasoning model or a coding model can hill-climb using your traces and that will be more token-efficient and it will be fundamentally a great advantage. Exclusive to you the customer. SN: Yeah, that’s right. But is that just for now? If you fast-forward, is your vision that actually MAI models are fully competitive on the frontier with the other general models? SN: They are. Even today, when you start saying that — the world will keep getting better in general.** Well, I guess this goes back to, is this about how you need to do what you’re good at? SN: Correct. One, what we’re good at and also what’s the equilibrium of the world? Which is, if you believe there are only going to be two firms in the world, then of course, they only need two frontier models, but if you fundamentally believe that there are going to be as many firms as there are today and more, then what is the firm in the age of AI? It’s going to have human capital and token capital, how did that token capital get created? It’s not a bunch of API calls, it’s actually some set of weights even they have. Right. And so do you want to accrue that advantage or do you want to give it to OpenAI and Anthropic? Well, speaking of the OpenAI partnership, I mentioned you referred to it like the Microsoft-Intel partnership, and sometimes partnerships are the only way to get ahead. How do you think about that partnership now? SN: I still think that it’s — I’m very proud of the fact that we came together, you remember the circumstances in which we came together were very different and the fact that there is a company now that may go public and be a trillion-dollar company— This is my question — how long were the knockdown, drag out fights between in this corner, there’s Satya Nadella, the operator, and in this corner, there’s Satya Nadella, the investor, tussling over what to do? SN: (laughing) At the end of the day, we are an operating company, investment is just more of an accident. Yeah, but the shareholders are ultimately those investors! SN: I’m glad and it’s a fantastic outcome for our shareholders too and what have you. But I think the way I came at this, Ben, is to say genuinely I’ve always approached it as, if there’s a partner that we can partner with and ourselves innovate, and they’re also successful, that’s fantastic. I always go back to the story of having built SQL Server with SAP. SAP was successful, we were successful, we also then went on to do other things. And so therefore, I think OpenAI, I’m glad we worked with them, we’re working with them, they continue to be a premier partner. As I said, until 2032, we still have a lot as a customer of theirs, them as a customer of ours, as an IP partner. So every day OpenAI does well, Microsoft does well. Is there a bit where everyone thought you were so far ahead because of your partnership with OpenAI, and now when we talk about things like your MAI models, it’s like actually “We got a little bit lulled to sleep because we offloaded too much to them, and now we’re having to recalibrate”? SN: Lots of things, one is, like all things, there’s a lot more competition, there is OpenAI, there is Anthropic, there’s Google, there is tons of folks who are in there. And so I think for us, the beginning, it was great that we got started with OpenAI. Think about where we were in 2018 to where we are in 2026, here we are competing with Google and a bunch of people whose names I wouldn’t have known in 2018, and so that itself proves that to your very first question, “How competitive is Microsoft?” — I’m glad Microsoft took that shot. Here we are competing with a bunch of new people, a bunch of old people, and we have our own game. So we already talked about Satya Nadella, the operator, and Satya Nadella, the investor. What about Satya Nadella, the capital allocator ? There were a lot of reports in about early 2025 about Microsoft pausing and a reconsidering some data center investments, you guys have sort of spun that as, “Lots of speculative stuff”, “We’re streamlining”, etc. — but at the same time, your percentage of free cash flow committed to CapEx lags fairly significantly behind your peers. Four months ago, that was a compliment. Now, is it a diss? How are you feeling about that? SN: The last time I checked, my free cash flow is getting allocated pretty well to capital return that makes sense. Is there a case that you’ve underinvested? SN: Not really. I think the key thing that at least we wanted to make sure is we were not upside down on building — we have a hyperscale busines, we have our own application business, and we have our own research compute to allocate, there are three buckets, we wanted to allocate with great discipline on all three. So take the hyperscale business. Hyperscale businesses are about having a few big customers, but also having a massive long tail, so you can’t have a book of business that is just a few model companies — in fact, one model company — that was the fundamental decision. And you wanted to get out of that business. SN: Not just get out. They’re still there, they’re a major tenant. SN: They’re a major tenant. But, let’s face it, Anthropic over time or OpenAI over time will build their own, it makes sense. They would use — I’m not saying that they won’t use other cloud providers. So to me, it was clear as day that, what I wanted to do was not allocate all my compute only to one player and so that was the adjustment. And once you make that adjustment, you can’t build 10 gigawatts in Texas and say, “That’s it”, you’ve got to build a plant that is spread around the world, around the United States, and that adjustment is what we want to do on hyperscale. The other thing that I have to do is make sure we’re doing also the long-term thing for our investors, which is, “Let’s invest in ourselves”, which is inference compute has exploded, whether it’s in GitHub or whether it’s in M365 and we needed to make sure we fund our own applications. And then our own research compute, these MAI models. So I just took the approach of putting these three, we will definitely want to allocate as we see progress on all this and we’ll see how it all shakes out. But to me, I’m not literally matching quarter-to-quarter. By the way, the other interesting thing is the catch-up, we started early. You were early, and you got a lot of the good spots, a lot of the good power generation. SN: Yeah, and also two years of cash flow. Yeah, for sure. Well, speaking of the balance between the three, in January 2026 , you missed Azure earnings by like 0.1%, so it was very small, and you said on the call , you allocated more compute to internal R&D and applications. Setting aside the earlier question about whether or not you erred by the total amount of capacity, you talked in that call about having a portfolio approach in terms of investment, balancing Azure, and those two other businesses. That’s all well and good, but if there is a constraint, you do have to choose, do you think you made the right choice then? And is that the choice you’ll make going forward? Where you are at the end of the day, you have a higher lifetime value, higher margin on your own businesses, and that’s going to be number one. SN: Yeah, and also research compute. Ben, I think that for all of us, quite frankly, we have to really, at the end of the day, that’s why I think quarterly earnings are interesting, which is, of course, The Street should hold every one of us very accountable for “What did you do for me lately?”. But was that a very particular, annoying, being held accountable for the wrong thing? SN: It’s their job, everyone’s got to do their job, and so I can’t accuse them of them asking, “Hey, what did you do for me this quarter?”, that’s the question they rightfully should ask. And the right answer for me is, “I’ve done enough for you this quarter, and we’re also making sure that 10 quarters from now, Microsoft’s continuing to thrive”, and that’s the job, and sometimes there’s a little bit of disconnect on it. But when I look at the three things, you just have to be disciplined that you’re doing what you can add value, it can’t be, “Oh, I’m misallocated”. To your point, you get punished if you do things where you’re not producing. So that’s why research compute, here is now an MAI model output. Today, it’s just not a model output as an academic thing, that’s now in differentiating our Foundry where we now are able to license it, it’s going to grow Foundry revenue. And so as long as I’ve felt that as long as Microsoft can continue to invest in ways that show results, then we will have the ability to do the right thing in the long run and in the short run deliver results. For the last quarter, was there a bit of, “Let’s give a little bit more compute to Azure?” SN: Last quarter, no. In fact, that one was just a little more of the compute — we are supply-constrained. I know, but that’s what makes it so interesting. SN: We are not at all, like at this point, if anything, the thing that we do not want to do is to disappoint especially our enterprise customers on Azure. That was the question, right? Because if they look at that quarter and they’re like, “Hmm, Microsoft’s saying we’re supply-constrained and also we’re prioritizing our higher margin, higher lifetime value businesses, where does that leave me? I’m competing against my supplier”. SN: That’s one of the reasons why we had to make some very hard choices around, for example, raw GPUs. We’re not selling raw GPUs to a bunch of Neolabs, for example. I wish I could add more Neolabs on Azure, we just cannot. And so therefore, we are being very disciplined on some business that we turn away. Were those some of the conversations you had to have? SN: Yeah, and so to me, in a world where you have constraints, you want to basically make sure you’re building for both what the world expects and the customers who have trusted you in the longest and so we will definitely make sure that Azure has capacity, it’s just that we are not going to go for what I’ll call in this context, “easy money”. Which is, you can always, in today’s day and age, if you want to have short term Azure revenue, it’s pretty easy. Oh yeah, we’ve seen that , to say the least. SN: Yeah, all you gotta do is turn up, you know, and go sell it to a Neolab. So when it comes to AI infrastructure specifically, as you look out in the long run, you mentioned it may very well be rational for the frontier labs to build their own hardware, for example. You have all these Neolabs, you have whatever controls [Nvidia CEO] Jensen [Haung]’s allocation of GPUs, you have different ASICs, what is your true differentiation as a hyperscaler? Is it just lower cost of capital? SN: First of all, think of our hyperscale business as this portfolio, everything from what we are trying to get done is build a system which we have to be competitive in when it comes to tokens-per-dollar-per-watt, that’s one side of it. We can unpack that and what our thesis is there. Well, I just noticed when you were talking about some of your chips, sometimes it was tokens-per-watt, sometimes it was tokens-per-dollar. SN: Yeah, I think of all three, right? It’s like tokens as a function of both power and dollars and so that’s a systems thing that we have to be world class at and be competitive at. And I would be able to claim, and that’s where I think [Microsoft AI CEO] Mustafa [Suleyman] talked about it, like unless and until you build your own model, you can’t, there’s no point. I believe that you don’t want to build accelerators without building a model, you kind of have to co-design. In the long run, the only way to be super efficient on that is to think about, the network is a great example, which is you want the network, the model, all to come together in ways that make sense, so therefore that’s one side. Then the other side for us is the differentiation has to come from, “If I’m building agents on top of this infrastructure, what agents does Microsoft produce?”. I have three domains in which we are going to try and major on: coding, security, and knowledge work. Luckily these are three massive domains where tokens make sense — I’m not saying there won’t be others, science is another one we will enable but I think there will be others who will do great work in there. But to me the three primary domains in which all this is going to be exercised use. So when I think about the portfolio of building a system plus model plus these three domains, then I feel like that’s where our differentiation will come from. But is that just a re-articulation of circling back to, in the long run, our true differentiation is from our higher margin, our own businesses, higher LTV? Where does that leave just customers who— SN: I think it’s not higher margin. The overall margin dollars from our infrastructure business may be higher. In fact, they already are getting close to being higher than our total margin dollars from our high margin businesses. So I think that Microsoft has always benefited from having a portfolio of businesses, and we’ve been comfortable managing through it, where it’s not one margin profile. But in aggregate, we will have high ROIC, and we will make sure that we have an infrastructure business that’s got ROIC that’s commensurate with an infrastructure business, and we have a business that builds on top of it, which I’d like call it like the new apps are agents. So we’ll have agent businesses in security, in coding, in knowledge work, as the three big domains. We’ll get to agents in a little bit, but I didn’t expect to ask this question, big news this week, will you ever issue equity to fund this build out ? SN: Yeah, I just saw the news, I think Google just did it. Were you as surprised as everyone else? SN: I’m not sure, exactly, I’ve not studied it, it came last night, I think, so I’ve got to go understand what’s happening. But, it’s like maybe it’s the thing to do is everybody is going public or reissuing equity, maybe that’s the season. Gobble up some of the money. Is software dead? SN: I think software is alive, but the way I think this entire meme has come about is, like, if you take the SaaS question in particular, right? We built in a particular way where I had a data model, and then I had a business logic tier, and then I had a UI tier, I coupled the three, then had a business model. Integration is a beautiful thing. SN: Look at this, Ben, right now, we took what is the database that no one knows about underneath Microsoft 365 and said, “Oh, WorkIQ is available , it’s just a skill/MCP, and it’s out there”, and suddenly people are falling in love with, “I can now interrogate and have an agent continuously hit this database to reason over and plan over, act over from any place”. By the way, it requires a new business model. So, for example, when Cowork is using WorkIQ, that’s going to be a usage-based business model, so I think what needs to happen is we now need to take what we built, rebuild it for the agent era and change the levers of the business model such that you have a per-user business model and you have a consumption business model. So the hybrid business model, you do think that is going to be the future? SN: 100%. And once you have that then I think what happened between servers — even I had not understood it when we moved to the cloud, even I was a little worried about, “Oh man, we move to the cloud, we’ll sell the same servers”, and it turned out we sold a lot more subscriptions because people who never bought servers from us were buying subscriptions. I think that’s what’s happening already with agents, I see that on GitHub, I see that on M365, I see that on security, because everyone is building these agent systems that are continuously “working” and so what we built and thought of as the end-user compute is completely getting rebuilt. Is there a bit where, if you have to zoom out a hybrid system where a combination of per-seat but also usage, where does E7 fit in this idea, it’s like double the price, it seems it’s an attempt to respond to maybe a secular decrease in seats by increasing ARPU? Is that the right way to think about it? SN: The way you think about this is, see per-seat is a very important element still because what is per-seat? Per-seat is basically a set of usage entitlements, so anyone who is budgeting really will push you. That’s right, people don’t like usage, we’re seeing that right now , it could explode . SN: Exactly, so therefore you just want to take packaging or bundling of usage into proceeds so that there’s some way for people to budget. So I kind of think about the E7, E5, these things will continue and then you’ll always have the outcall consumption. People also talk about, “Hey, maybe people want outcome-based pricing”. Outcome-based pricing, we’ll be thrilled about some of that, but remember, outcome-based pricing is also called royalty. When a customer has a great outcome, they necessarily don’t want to share their outcome so I think what is really being thought about is, ultimately, there is real marginal cost to software, that’s kind of what it is, and that’s going to be priced through. When did that really click for you, the implications of that? SN: I think that I would say agents. Before agents, if it is still human interaction— Right, you can imagine a world where just like basic inference got super cheap and easy. SN: Exactly, the Moore’s Law itself. Like, if you think about it, if I just used Moore’s Law, get software efficiency, I used software for efficiency and drive that home for customers to have more functionality. In fact, I used to always think about, “Hey, how much more value did we add in M365 and not raise price?” — we didn’t raise prices for a decade plus. That’s all thanks to the software efficiencies on top of hardware. But now where you are, and if you have a thousand autonomous agents that are all working continuously 24/7 hitting Work IQ, then that is a lot and so that is where I think, and so the real test for me Ben is, that’s why evals, outcomes — no customer will use consumption or their seats if it’s not creating value for them. Therefore, they now are going to be a lot more disciplined on, “What exactly did this stuff do for me?”, “How do I measure it?”, “How do I get into the efficient?”. And if you think back to going back to the 80s or 90s, where back then it’s like, “Don’t waste time on optimization, the next processor will come out and solve all your problems”, is that now totally the wrong paradigm? SN: In some sense, you want that to happen, but you can’t just count on that. It will happen, but your prices will explode. SN: Exactly, and more importantly, you will be found out if you don’t optimize. Take that example we showed with Land O’Lakes today, which is, here’s an agent, and there is an outcome you care about, I was able to use a model that is using 500B, I was able to use a 5B, and have it really deliver the same outcome, why would I not use that? That does seem to be a very different thing about this period. It seems clear that’s going to be a huge thing in enterprise going forward, using the right model, optimizing, it’s like we didn’t get to the optimization stage of the PC era. SN: That’s right. I don’t think we ever did get there. SN: We never got there. Stuff’s still bloated as ever, because everyone just assumes it’s going to get faster, it’s going to be fine. SN: Exactly, because things were not priced for it. Once you have consumption, everyone will optimize. For E7, it does seem like the real lure there is Cowork . It’s like this new capability, it’s super powerful, it’s taking Anthropic’s Cowork, which is on your PC, now it’s in the cloud, has all the niceties around that, permissions, controls, all those sorts of things. Is that why it’s there? Is that the hook? SN: Yeah, there’s also the Agent 365 , so there’s a whole lot. Like always, these things, we’re going to take everything from what I’ll talk about as what is an end-user thing and an IT thing, bring it all together. You guys know bundling. SN: And security. Yeah, definitely, and they’re all about, ultimately, how do we get the value equation right such that the customer can cover, because right now, it’s kind of fascinating. You have an agent, you immediately say, “Oh, I’ve got to secure it, I’ve got to have observability on it, I need a sandbox for it”. So it’s just that if you don’t bundle, you kind of are sending the customer down the chase of five different things. With that, though, the reason I find that striking is you’ve talked a lot about — to what extent do you think the point of integration that really matters is it does seem to be increasingly between the models and the harness themselves ? You’ve talked about things like your CoreAI initiative and GitHub Copilot, a lot of which is, “We’re going to build the harness and you can slip the models in and out”, and that works right now for Copilot and you can choose your model and even then, from what I’ve heard, not quite as easy as you might think it might be, but it’s still there, the selector’s there. Cowork seems like, “Yeah, that’s right, it has to be the whole package and it’s important for us to have a selling point on E7” — that this feels like maybe it’s not easily substitutable. SN: No, it is. The same thing on Cowork. In fact, right now, the Cowork that I’m using is already mostly defaulted GPT. Okay, so it is going to be fully interchangeable? SN: We’re using the same harness that we use in GitHub and the same thing in security, too. So we have the same harness that’s a multi-model harness in which we will rotate through — obviously MAI by default gets trained in our harness, but we will have GPT, we will have Anthropic in there and any open weight model. We will allow anyone to take any of the models they fine-tune or build. In fact, they can take an open weight model from Fireworks, tune it, put it into Copilot, no problem. All right, so I am misinformed, so I will take the L on that. Explain what is Cowork then and what is the connection with Anthropic as far as that product goes? SN: Cowork, to me, it’s kind of like Copilot. I took the term Cowork, it’s part of there and it’s definitely got the Anthropic models in there. Cowork is — think of it as a form factor, the best way to describe it is we built a chat interface first for Copilot, then we now have built Cowork for Copilot, and now we’re building autopilots, as I described it there, think of it as the enterprise-grade OpenClaws. So basically, I think of these as different form factors of agents — chat was the first thing, Cowork is the next thing and in fact, you can even go back to the developer thing. Developers, how did we start? We started with code completions first, then we went to— I get all this, but I’m genuinely confused here, because I go back to the blog post . It says, “Working closely with Anthropic, we took what they’ve done with Cowork…”. SN: Yeah, that’s what we launched first. All I’m saying is it’s evolved. It’s kind of like, Copilot today. Got it, which started out with ChatGPT. SN: ChatGPT, now it has both Opus and GPT models. Got it, okay. SN: So, they’re going to be all over. All right. So, I wasn’t completely off the reservation. SN: That’s right. I failed to catch up, I will accept that. [ Editor’s Note: the FAQ for Cowork still says it uses Anthropic models, just like the original blog post ] SN: Every product of ours, you’ll have both Anthropic and OpenAI models, and MAI models, and your ability to put your own models, and that, I think, is the fundamental promise. Oh, by the way, I should mention this. The amount of auto — I don’t know how much you’re doing selection, I’m mostly auto — and so then one of the biggest pieces of work at Microsoft is all the training models to do auto-routing. That, by the way, is perhaps one of the biggest continuous learning things.** It’s interesting because I probably approach it more from a consumer perspective, so I just literally choose the app that I want to do something in or call from the CLI. What happened to Github Copilot? You’re talking about it very positively, but I think a negative spin would be two or three years ago, you were first to market with autocomplete, everyone assumed you got there, you won, and now it’s like, “We’re going to catch up with GitHub Copilot”. SN: I think what happened is this is one of those classic cases — remember, it was a tools business before, and now it is the business, who would have thought that coding is everything? Right, it should have been everything, but it seems like for some period of time, it wasn’t? SN: For us, I think what has happened is we have continued — there are two things that are happening in GitHub, before I even talk about Copilot, I should talk about GitHub. All these coding agents have shown up to work, and where have they shown up? In GitHub. And so the first thing that, quite frankly, I wish we had anticipated better, was the amount of agenting. The whole GitHub reliability thing is like one thing, but for Copilot specifically. SN: I’ll say the first thing, that’s kind of, at some level I take that job seriously, because job number one before you want to get to Copilot is go make sure that we are scaling, so let’s leave that alone. There’s a lot of people very unhappy about that. SN: Yeah, and we’re going to work it and they should have higher expectations of us and we need to deliver for them. Then the next thing is on the Copilot side, you’re absolutely right, we started by saying, “This must be just a code completions thing in the IDE”, we added chat, we added tasks, and guess what? Let’s give credit where it needs to be given. Anthropic showed up with a model. Well, this is like Cursor’s story , they ate your lunch even before Anthropic did. Or you’re saying that that was also an Anthropic story? SN: Not really, I mean it’s kind of like Cursor/Microsoft, it’s like Borland v us , it’s not like that was not the end all be all. It was really the Anthropic coming in with a completely different approach, a more agentic approach. SN: That’s right, with a different approach. With a model and what they’ve done there, and essentially the agent loop is what the change was. In fact, if you look at it, Cursor never, total volume-wise— They got eaten by the same thing, they’re facing the same challenges. SN: Also even the market share and so on — Cursor did fantastic, they forked VS Code, did a good job, lots of credit to them. But the real thing was agentic coding became real and now the good news is the agentic coding really drives — people want choice, we will be there, we will have our own models. GitHub itself and Copilot itself will have both the Anthropic and Claude. In fact, the rubber duck feature is my most favorite feature , which is I can use it to check the others. The headline announcement from this week, I guess is these new Nvidia-based PCs running Windows . However, the announcement I found much more interesting — or not an announcement, preview — Project Solara , viewing these devices as ways to access agents in the cloud, totally different center of gravity. I don’t know if it was you that said it or the presenter, something which I thought was really compelling, which is a limitation of wearables is if you have to interact with them continuously, they get very tiring, so their utility is fundamentally limited. But if you can ask an agent to do something, then you can go do something else and meanwhile, it’s running in the background. Super compelling. I guess the question is, this feels totally different than Windows — it was weird to start this keynote talking about Windows and the AI PC, and that’s nice, and local inference, but this is like, “Actually, what if everything was in the cloud?”. SN: Yeah, I always find this frame back from 2014 of ubiquitous computing and ambient intelligence and it’s becoming more and more real each day. First of all, the first part of it was, “I’m so thrilled to have these Windows machines”, and the fact that Jensen had that beautiful slide, the picture of him with all the desktops, I was like “God, yes, I’ve been waiting for it”, which is it’s great, so I think because it makes sense, it makes logical sense to have powerful silicon systems with power that really have it with unmetered intelligence. When I worked at Windows, I had to like furtively hide my iPhone and then it was okay to show up on campus with an iPhone, now I’m here with a MacBook Air — next time I interview you do I have to feel bad that I don’t have an Nvidia AI PC? SN: You will always have choice, Ben, and I hope you choose the right thing. I’m excited about that stuff because I think there’s unmetered intelligence, even there was one little feature that we showed, which is that ability to have eight agents running continuously, analyzing logs and so on, but all of them were unmetered. Right, but that feels like it’s a side project, side quest. SN: It’s kind of like a billion users all having that, that’s not a side quest. To me, it’s as fundamental as like I think the people are going to want for their knowledge work, for their security work, for their coding work, machines— They’ll want for themselves. Is this actually the new consumer/enterprise separation? SN: The enterprise — the business model, we had this long conversation about enterprises continuously optimizing — in fact, I think the biggest value prop of a Windows machine in the enterprise will be unmetered intelligence. So people are going to say, “Oh wow, instead of having my cloud bill keep going up, I’m going to have Windows machine and amortize it that way”, so I think that there is going to be a real value to — because in a world where you have infinite amount of tokens you want to consume, you want to optimize, and why would I not optimize using everything? I don’t know, I just feel like — as you know, I’ve been very impressed with the job you’ve done with Microsoft, ending the stranglehold Windows had on the company, I still remember I was actually in the Bay Area, I was sitting at the bar at The Westin by the airport typing The End of Windows , recounting all these things you did to not kill Windows, but not make it the center of gravity for the company. SN: And that I think is what goes to Solara. I don’t think Windows, we are trying to make Windows— SN: Solara, to your point, I thought it was a great question, because the thing that I want us to take a shot at is the following which is, “Can you think of a platform and platform rules, by the way, which are built for the agent era?” — because right now, what is everyone else who are “platform owners” who will try to move from the phone to this wearables will try to bring their apps to the same game, right? I want to open that up, so I would like, for example, like what we were able to do with Teams devices , and that’s where we built some of this sort of distribution capability, so I want to use that connected to this agent world so I’m excited I’m in MediaTek, Qualcomm. Well I have a great analogy for you, I think. So there’s a bit where I think you just circle back to the great job you’ve done as CEO — this is the butter-up portion of the interview — there is a bit where I think you benefited from following the follower as it were. Steve Ballmer’s one that had to go after Bill Gates and he for better or worse created the conditions for you to succeed, I think is one way to put it, is it possible that for this, your opportunity device space — like can Apple ever really make an agent that works everywhere as long as they’re stuck on the phone? SN: That’s a great question. That is the question for all of us which is you know the reality is it’s easy to say for someone who’s been so successful with something that in face continues to have a lot of success and say, “I’m going to burn it all down and build something else”. But to the point, the way they’re architectured, everyone’s vertical. SN: Exactly, it’s not natural. Like you think about it, we’re saying, “Building agents is easy”, the SOCs are jumping out everywhere, they’re there, the silicon is easy, the system is easy, the operating system is built, and now you’re telling me that I have only one choice for an ambient thing in a hotel, in a restaurant, in a healthcare setting? It makes no sense. So therefore, I imagine that building these ambient devices using Project Solara will be as easy — if you’re successful a year from now, everybody, even in the enterprise, is going to say, “Oh, I’m just going to order a bunch of these things from a no-name ODM who just built it for me”. I think it’s super smart to start at the enterprise only. Do you have dreams that maybe this will eventually spill over? SN: Right now, I want us to again do what I think is natural, like where am I seeing people— Well, that’s where you have the Microsoft 365 environment, you have all the context there. SN: And also the agents, where would people build agents? The thing is, the consumer one will be like, “I need the one agent I want”, so it’s not like I’m not building a Copilot device, I’m building an agentic platform where the healthcare provider can have their own agent, so that’s the right place for Microsoft to start, let’s see how it goes. One last question. You had a data center segment appropriately focused on communities, you talked about things like paying your way for electricity, not using water, building up the tax base, education, etc. Why not just pay the residents ? Just pay them a dividend? SN: I’m open to all ideas here, I’m not close-minded at all because at the end of the day, I think the fundamental thing you’re asking about is, “How does this industry, including Microsoft, have permission to do what we’re doing in terms of infrastructure build out?”. My theory is we get to everything backwards in the US, this is how we back into UBI [Universal Basic Income], is we’re just paying people to build data centers. SN: Yeah. And I mean, one thing that I have an issue with things like UBI and so on are the— I’m anti-UBI. That’s how you get there while being anti-UBI. SN: I want people and communities to have control, have agency, humans to have real dignity in their work and you’re 100% right in saying, “Look, we have to do what it takes to get that permission”. And so right now, there’s so much about our industry that’s so glorious, so good, so great. What about the you’re going to lose your job part? SN: Yeah, that’s the problem. Self-obsession about our own glory and our own — if you’re not creating opportunity, why would anybody want you to succeed? That’s the fundamental memo that needs to be re-sent to everyone across our industry, and then we have to live up to it. Satya Nadella, great to talk to you again. SN: Thank you so much, Ben, as always. This Daily Update Interview is also available as a podcast. To receive it in your podcast player, visit Stratechery . The Daily Update is intended for a single recipient, but occasional forwarding is totally fine! If you would like to order multiple subscriptions for your team with a group discount (minimum 5), please contact me directly. Thanks for being a supporter, and have a great day!

0 views
Evan Schwartz 2 months ago

Scour - May Update

Hi friends, In May, Scour scoured 865,266 posts from 28,671 feeds (1,766 of which were newly added), and 260 new users signed up to bring it across the 3,000 user mark! Here's what's new in the product: Scour is now better at finding posts that match your interests. You should see more relevant content and far fewer off-topic articles in your feed. (This sounds simple, but it represents at least a full month's effort 😅.) The way this works under the hood was one of the single biggest changes I've made to Scour's core ranking system since I started working on it. At a high level, scoring now combines Scour's original fuzzy concept matching (embedding vector distance) with how much the article uses relevant vocabulary (lexical search). While these ingredients are well-established, I think the exact way Scour implements them might be a somewhat novel system design. The reason this was so complex to build was that existing approaches to lexical search did not work for Scour. For example, every Scour user has between a handful and hundreds of interests (I have 642), each of which might have 3-10+ relevant keywords. This means that every "search" is actually a search for thousands of terms (for my feed, it's around 5,000). Most search systems are built for individual queries with a handful of terms. The even more tricky issue is that lexical search algorithms like BM25 do not produce scores that are comparable across queries, because they are designed for ranking (ordering results for a specific query), not scoring . Scour, however, needs to know which of your interests a given post is most related to and it sorts the posts in your feed by how relevant they are for any of your interests. I believe that the custom scoring and indexing system Scour now uses provides both cross-query score comparability and efficient lookup for thousands of parallel queries. Stay tuned for more details! 🙏 Help me out! Please like, dislike, and report posts as off-topic as you're browsing. These signals help me tune the system and figure out the edge cases where it could be improved. Scour bolds keywords in the post titles to make the feed easier to skim. The new lexical scoring layer discussed above makes it easier to bold exactly the words related to your interest. Two other small changes let you peek under the hood of the new scoring system. On desktop, hovering over a post's title will show you the score breakdown between semantic and lexical. Separately, if you click on an interest tag and go to the single-interest page, there is now an Advanced link that will show you the terms the lexical scoring system is using to find and rank posts. Here were some of my favorite posts that I found on Scour in May (you can tell from the topic concentration where my mind has been!): Happy Scouring! Latent Terms: Dense Retrievers Contain Trivially Extractable BM25-ready Zipfian Vocabularies Rethinking Agentic Search with Pi-Serini: Is Lexical Retrieval Sufficient? Re-autoresearching MSMARCO BM25, on Vespa How we made a SQL query optimization agent 59% more accurate using autoresearch and LLM Observability Your Vector Database Doesn't Know What Similar Means My Plan with RSS Agentic Coding is a Trap

0 views
Dangling Pointers 2 months ago

Yield Not Thy Core

Yield Not Thy Core Achilles Benetopoulos, Peter Alvaro, Andi Quinn, and Robert Soule EUROSYS’26 This paper describes a solution to the placement problem in distributed systems. If you model a computation as a directed graph, how do you optimally distribute the graph among a set of cooperating computers? The authors propose a dynamic placement system and implement it in Magpie . One common solution to the placement problem is to ship data over the network. For example, a set of compute nodes could access data via network requests to a separate set of nodes running Redis servers. At the opposite end of the spectrum, code can be shipped over the network. The canonical example is expressing computation as a SQL query which is sent to the node(s) that hold the relevant data. Magpie proposes a more fluid solution, where both code and data can move dynamically. In Magpie, an object represents data that is operated on. What makes Magpie objects unique is that pointers to data stored in an object are encoded as tuples. This allows Magpie to dynamically move objects around the system without invalidating pointers. The downside of this approach is that it prevents traditional libraries (that rely on raw pointers) from being used in user code. Magpie assumes a high degree of inter-object locality, so any given object is stored by exactly one node (i.e., a single object is never split between multiple nodes). User code is expressed in terms of nanotransactions and epics . A nanotransaction runs to completion on a single node and accesses a pre-specified set of objects. The Magpie runtime ensures that all objects accessed by a given nanotransaction are resident on a single node before executing the nanotransaction. The code for a nanotransaction is simple, because there is no need to query data over the network, and there is no need to deal with locking. If a hazard is present between two nanotransactions, they will execute serially. In Magpie, nanotransactions are written in Rust. An epic is a computation graph where each vertex is a nanotransaction and each edge is a data dependency. In contrast to nanotransactions, a single epic can be distributed across multiple nodes. Magpie schedules nanotransactions once all data dependencies are satisfied. Conflicts between concurrently running epics are handled via snapshot isolation . Any particular epic has a consistent view of each object and may abort in the event of a conflict. Scheduling and data movement are implemented hierarchically. A worker node can locally determine if it has ownership of all dependencies required for a nanotransaction. If this is the case, then the worker node executes the transaction immediately. Otherwise, the worker node uses a local ownership cache to try to determine if another node has all required dependencies and communicates with that node if possible. Failing that, scheduling is performed by a global orchestration node. Fig. 9 compares Magpie to memcached executing a workload that involves a user-specified read-modify-write operation: Source: https://dl.acm.org/doi/10.1145/3767295.3803616 Magpie is able to offer a lower latency because it is able to ship the entire read-modify-write operation to the server that holds the relevant data, rather than requiring multiple roundtrips. Some applications may benefit from being able to indicate that an object is rarely changed and thus can be distributed among multiple nodes at the same time. Thanks for reading Dangling Pointers! Subscribe for free to receive new posts.

0 views
Farid Zakaria 3 months ago

Leaving performance on the table

I have been working with LLVM at , and I have gotten to become familiar with the benefits of optimizing your workloads. I tend to think of optimizing my binaries as thinking about whether I have attached to my compiler flags; maybe if I’m particularly advanced that day I’ll sprinkle in some (link time optimziation) and call it a day. Turns out though that’s leaving lots of performance on the table. Compilers work under the assumption that every branch is is equally taken, unless you are hints like ( ref ). If we can feed the compilers more information about the likely path that our workloads often take, then they can produce much more performant code. There are two primary ways to optimize a binary: instrumented or statistical. When we instrument our binary, we run our workload with an instrumented binary and capture the exact paths that are executed. We will then optimize the binary perfectly tuned to that workload. If our workloads however are varied, we can collect profiles via over a length of time and create an optimized binary based on the statistical occurence of call graphs. Both approaches have their benefits however let’s start with the instrumented variant first, as it’s a little easier to follow and understand. Let’s look at a very simple benchmark. We will calculate fibonocci using SQL in sqlite3 . This is an ideal workload because it’s purely CPU-bound and ripe for optimizing. We will compile from source by downloading it. We can compile a “traditional” optimized binary that merely has and also a version that has LTO enabled since I was also keen to see how much LTO itself adds. Ok, so it looks like our program takes roughly 14-15 seconds to run. Sounds ok? How much better can we do…. 🤔 Next, we compile our program again but we instrument the binary , which effectively injects counters into the program to count invocations of functions. We get very accurate counts of our calls but the binary itself now runs much slower, which can be a problem if your workload was already very slow. Luckily for us, we are in a time domain (~15 seconds), where that is ok. After we have our instrumented binary, we run our workload again to generate the profile data and rebuild the binary with that data. The last step will be to optimize with BOLT, which is a post-link optimizer, which requires us to keep relocations so I’ve also added . When we run our workload with the final optimized binary, we see massive improvement already! 🤯 We’ve cut our workload time down to ~10 seconds which is a nearly a 1.5x improvement. Now let’s optimize the final binary with LLVM’s BOLT . BOLT is a post-link optimizer designed for “large applications”. What this means, is that it largely works by shuffling code around the binary to keep code-paths that have high temporal locality near each other (spatial locality). This can have positive impact on performance due to the instruction cache for instance. Looks like it was a little faster but not much. That makes sense since itself is a pretty small binary (~6MB), but nontheless was good to run through. Running a more thorough benchmark with we can get a final tally of our results. Looks like the I got from the Fedora ecosystem was the slowest . When all the optimizations were applied I was able to get a maximum of 1.38x faster than what was available. These optimizations would be even more dramatic for code-bases that are a sprawl and can heavily vary. Don’t worry also about getting the profile perfectly tuned to your workloads. I have a coworker who often cites that even poor profiles are still much better than no profile at all.

0 views
Simon Willison 3 months ago

Datasette Agent

We just announced the first release of Datasette Agent , a new extensible AI assistant for Datasette. I've been working on my LLM Python library for just over three years now, and Datasette Agent represents the moment that LLM and Datasette finally come together. I'm really excited about it! Datasette Agent provides a conversational interface for asking questions of the data you have stored in Datasette. Add the datasette-agent-charts plugin and it can generate charts of your data as well. The announcement post (on the new Datasette project blog) includes this demo video : I recorded the video against the new agent.datasette.io live demo instance, which runs Datasette Agent against example databases including the classic global-power-plants by WRI , and a copy of the Datasette backup of my blog. The live demo runs on Gemini 3.1 Flash-Lite - it's cheap, fast and has no trouble writing SQLite queries. A question I asked in the demo was: when did Simon most recently see a pelican? Which ran this SQL query : And replied: The most recent sighting of a pelican by Simon was recorded on May 20, 2026 . The observation included a California Brown Pelican, along with a Common Loon, Canada Goose, Striped Shore Crab, and a California Sea Lion. Here's that sighting on my blog , and the Markdown export of the full conversation transcript. My favorite feature of Datasette Agent is that, like the rest of Datasette, it's extensible using plugins. We've shipped three plugins so far: Building plugins is really fun . I have a bunch more prototypes that aren't quite alpha-quality yet. Claude Code and OpenAI Codex are both proving excellent at writing plugins - just point them at a checkout of the datasette-agent repo for reference and tell them what you want to build! I've also been having fun running the new plugin against local models. Here's a one-liner to run the plugin against gemma-4-26b-a4b in LM Studio on a Mac: Datasette Agent needs reliable tool calls and the ability for a model to produce SQL queries that run against SQLite. The open weight models released in the past six months are increasingly able to handle that. Datasette Agent opens up so many opportunities for the LLM and Datasette ecosystem in general. It's already informed the major LLM 0.32a0 refactor which I'm nearly ready to roll into a stable release, maybe with some additional "LLM agent" abstractions extracte from Datasette Agent itself. I've been exploring my own take on the Claude Artifacts, which is shaping up nicely as a plugin. I'm excited to use Datasette Agent to build my own Claw - a personal AI assistant built around data imported from different parts of my digital life, which is a neat excuse to revisit my older Dogsheep family of tools. We'll also be rolling out Datasette Agent for users of Datasette Cloud . Join our #datasette-agent Discord channel if you'd like to talk about the project. 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 . datasette-agent-charts , shown in the video, adds charts to Datasette Agent, powered by Observable Plot . datasette-agent-openai-imagegen adds an image generation tool to Datasette Agent using ChatGPT Images 2.0 . datasette-agent-sprites provides tools for executing code in a Fly Sprites persistent sandbox.

0 views
Aran Wilkinson 3 months ago

How I lost a database and learned to actually use AI

I ran AI-generated SQL without reading it properly and lost a database. The experience changed how I work with AI tools, replacing freeform chat sessions with a structured process built around PRDs, small tasks, and frequent commits.

0 views