A Smooth Sea Never Made a Skillful Sailor

Seven Releases With Grok: Fast, Honest, and What the Diff Did Not Show

noreply@blogger.com (Artem Ervits) — Fri, 02 Oct 2026 02:11:51 +0000

In my last post I cancelled Ollama. The money running out early was enough on its own, but the part I wrote about was how many review rounds the work needed before I could merge it. That gives me a simple test for any model: how many times does the work go around review, and does each fix pass make things better or add new problems?

This post runs the same test on Grok. Starting on September 21, it implemented four releases of my Zone 2 Trainer app and two releases of my personal assistant app. On a seventh release I swapped the roles, and Grok reviewed code that Claude wrote. Grok did well on that test. That is the short version. The longer version is about where the bugs that did get through were hiding: not in what the changed lines did, but in what they assumed.

The Setup

The models are grok-4.6 and grok-4.7 from xAI, on a SuperGrok subscription. grok-4.7 came out right after the first release, so it did the rest. On the other side was Claude. It wrote the issue lists and scope documents, reviewed every pull request Grok wrote, and implemented the seventh release as Claude Opus 5.5. This is the setup from Executor Model, Reviewer Model: one company's model writes the code and another company's model reviews it.

One thing to keep in mind for the whole post: the model that wrote most of the specs is the same model that reviewed the code. You will see why that matters.

What Held Up Every Time

Three things were true in all six releases Grok implemented.

It was fast. The first release went from a fresh issue list to a pull request with tests in 23 minutes. Answering a review usually took 4 to 13 minutes. One release of my assistant app took 65 minutes from start to a tagged release, with three review rounds inside it.
It never claimed more than it did. Not once in six releases did a commit or pull request say something was fixed when it was not. That was the deepseek-v4-pro:0813 habit I described in the Ollama post, so I was watching for it.
It said when work was not finished. On one sync bug it fixed part of the problem, wrote the two remaining gaps onto the issue, and ended the comment with "Do not close this on the PR landing." A model trying to look finished does not write that.

So the question was never "does it work." The question was what got past it.

Where the Bugs Were Hiding

Every release had at least one real bug that survived the first pass. When I lined them up, none of them looked wrong when you read only the changed lines. Each one was an assumption the new code relied on and never checked.

A wrong spec, followed exactly. The first release added a guard so a double tap on Start would not start two workouts. The guard never fired. It checked "is a session running and is training active," but the screen turns training off a moment before it sends Start, so the second half was always false. Here is the part that stays with me: the issue asked for that same check, a session and training active. Claude wrote that issue. Grok built what it said. Both tests for the guard passed, because they checked the rule on its own and never sent a real double tap through the app.

When does this run, compared to that? A later release replaced a crude half-second wait at the end of a workout with a proper "wait until the last heart rate samples are saved" signal. It looked like a clear upgrade. But the signal was set up after the code that waited on it already ran. On the first workout there was nothing to wait for. On every workout after that, it waited on the previous workout's signal, which was long finished. Either way it waited for nothing, with no error and no log line, and the half-second wait that used to do the job was deleted in the same change. The order was even written down in a comment in the same file. As first written, the fix was worse than the one-line sleep it replaced. Review caught it before the merge.

A borrowed contract. In my assistant app, Grok added OpenRouter as a provider by reusing the OpenAI client, because my scope told it to. The client was reusable, but OpenRouter's billing is not OpenAI's. OpenRouter checks your balance against the largest possible answer before it starts, so a small balance got a "payment required" error even though the real answer would have been cheap. Nothing in the source code shows that. One live request did: "You requested up to 16000 tokens, but can only afford 7211."

A rule the framework hides. To stop a background sync from bringing back a workout the user had deleted, Grok changed a database write. The SQL was correct. But the database library treats that kind of query as a read, so the workout history screen would never have refreshed after a background update. Review caught this one too. You only see this in the code the library generates, not in anything a person writes.

Different domains, same question: what is this change assuming that nobody checked? For concurrency, it is when something runs. For integrations, it is whose behavior you inherited.

The Spec and the Reviewer Were Part of It

I would like to say the model wrote the bugs and the reviewer caught them. The record does not say that.

Two of the six releases trace their worst first-pass bug to a scope Claude wrote. The double-tap guard was one. The other was in my assistant app: the scope said a cut-off reply "produces the marker," so Grok put a hidden marker inside the reply text. Every part of the app that did not know about the marker would have shown it or saved it, including into notes. The same scope also said "the client reports a truncated result," which pointed the right way. Grok followed the more concrete sentence. The lesson I took is to write the contract into the scope, not the mechanism. A capable model will build the mechanism you describe, flaws included.

The reviewer's fixes were not always right either. In one review, Claude found a real bug and attached a fix that would have made it worse. Grok took the finding, rejected the fix, explained why in one sentence, and solved it a different way. In another review, Claude's fix was right but incomplete. It said to include the conversation when saving a note, and did not say "through the privacy check." Grok did exactly what it said. One "save this as a note" would have sent private chat history to a cloud model for a user who had chosen local only. The next review round caught it.

Three of Grok's six fix passes added a new bug while fixing the old ones. In each case the fix was right where it aimed, and nobody asked what else it touched. One of those three came straight from the reviewer's remedy.

It Pushes Back, When It Has a Reason

Before the second release I wrote down one question: would Grok push back on a wrong instruction, or just do it? It did both, and mostly at the right times.

It rejected the reviewer's wrong fix described above.
It declined a finding the review had marked as unproven. Lowering a limit shared by three providers on a guess was a real change, and it said it would not do it without a live request. When the live request came back, it made the narrowest correct fix, for OpenRouter only.
It corrected a version number in the scope, and said why in the pull request.
When the review said a test was missing, it pointed out the test existed under another name, and renamed it to the review's wording instead of arguing.

Where it did not push back was the double-tap spec and the privacy remedy. In both cases the instruction looked reasonable and was wrong in a way you only see by reading code outside the change.

Roles Reversed

For the seventh release, Claude Opus 5.5 implemented and Grok reviewed. This was a good test of whether the reviewer role was doing the real work, or just the model in it.

Grok found two blockers in Claude's code, after over 3,900 tests had passed. The first needed code outside the change. The second was right there in the new code, and still easy to miss without thinking like an attacker:

The morning briefing would have opened links nobody pasted. The new link reader scanned the whole message sent to the model. The briefing puts calendar and inbox text into that message, so every morning it would have fetched meeting links and newsletter tracking links. A fetched tracking link tells the sender you opened their email.
The address check could be tricked. The link reader checked that a website did not point into my home network, then looked the address up again to connect. Claude had written a comment saying this only mattered for "a hostile DNS server." Grok's answer: "The threat is a link the user pasted. The attacker owns that domain's DNS."

Claude agreed with both and pushed back on one severity claim. It also kept one decision against the review, and found one case Grok's list missed. After the phone test found a separate bug, Grok took a last short look and called one item "not a hold": a document tile that overflowed at bigger text sizes. New tests at larger font scales all failed on the approved code. It was a real accessibility bug that Grok had under-ranked. When it says something is minor, I check anyway.

The phone test also turned up five issues that neither model's review raised, like a broken icon on every attached document. Tests found none of them, and neither did the reviews. That is a job for the phone, whichever model reviews.

The Weekly Limit Is the Real Cost

SuperGrok has a weekly limit, and in the first week I ran straight into it. The week started on a Monday at 11:06 AM. By Wednesday morning, after the four Zone 2 Trainer releases in this post, one more release I never assessed and left out, and a branch cleanup session, Grok stopped mid-session with "You have run out of credits." It stayed that way until the next Monday:

That is two days of work and five days of waiting, which is worse than the three days a week I wrote about with Ollama Pro.

The next week I watched the meter. Implementing two releases of my assistant app in one afternoon, including their review rounds, used 63% of the week. Reviewing the seventh release took it to 66%:

So about 3% for a review, against roughly 31% for each release it implemented. That comparison needs caveats. Reviewing reads a diff, while implementing explores, writes, tests and repeats. The releases were different. The 3% covers the first two review rounds only. And I have no matching number on the Claude side, because that week's Claude usage was mixed with other work.

Even with those caveats, it changes how I think about the plan. Speed does not save weekly budget. A fast release spends the same week as a slow one, it just gets there sooner. If one Grok review costs a tenth of one Grok implementation, and the review finds the bugs that matter, then the review is where that plan earns its money.

It also showed me what speed skips. In the 65-minute release, the full test suite ran once, on the version that still had all four first-pass bugs. The merge happened before anyone looked at the last commit, and the phone checks in the scope never ran. Claude ran them afterwards, and everything that could be checked passed. But that took about another hour, on a different budget. The checks still had to be paid for. They just came out of a different plan.

What I Am Not Saying

Seven releases is not a benchmark. The work was different every time, some releases were much harder than others, and one person judged all of it. The same model wrote most of the specs, reviewed the code, and helped me collect the evidence for this post. Part of the seventh release ran on airplane Wi-Fi, so I would not compare those times with the rest.

I am also not saying Grok is better than Claude, or the other way around. Each model missed something a careful read of the diff did not catch, including in its own code.

Where I Landed

Grok did well where the Ollama models struggled: few review rounds, and no "fixed" that was not fixed. Its fix passes still need review. Three of six added a new bug, even though none of them claimed more than they did.

What changed is how I review. Reading the changed lines carefully is not enough, because the bugs that got through lived in what those lines assumed: sometimes in another file, sometimes in a comment a few dozen lines away. Now I ask a different question depending on the work. For concurrency, when does this run, compared to what it depends on? For integrations, whose behavior did this inherit? For specs I write, am I describing the result I want, or a mechanism a good model will follow exactly?

And on a weekly limit, the numbers point toward spending Grok on review more than on implementation.

References

Cheap Plan, Expensive Reviews: Why I Cancelled Ollama

noreply@blogger.com (Artem Ervits) — Mon, 28 Sep 2026 00:05:38 +0000

Two weeks ago I ended a post about rate limits with "Ollama is back." I had re-subscribed monthly on purpose, to answer the question properly this time: does this plan give me the flexibility I did not have the first time around? I have the answer now, and it is no. I cancelled.

The short version is that the money ran out early. The longer version is the reason for this post: even while the money lasted, the plan was not the real cost. The real cost was how many review rounds it took to get the work to a state I could merge.

The Money Part

Ollama Pro is $20 a month and includes $60 of cloud usage. This is my dashboard today:

The $60 was gone within the first week of the month. When I saw the reset was still three weeks away, I cancelled. Most of the requests went to kimi-k2.7-code, the model I said in the last post I had not evaluated yet. I evaluated it.

For comparison, my Claude weekly limit has covered most of this past week of daily work across two apps, and it is on pace to run out a day or so before it resets. A weekly limit that runs out near the end of the week costs a day of waiting. A monthly credit that runs out with three weeks left costs three weeks.

That alone was enough to cancel. But it is not the part I want to write about.

A Small Release, Three Problems

Here is one real example, from my Zone 2 Trainer app. I asked kimi-k2.7-code to "scope a bug fix release, pull all issues that are marked as bug and build 6.10.2." It did a sensible job of the scoping. It asked three questions, proposed cutting the patch from the last release tag instead of the main branch so no unverified Bluetooth changes would ride along, and picked two bug fixes. Then I asked it to post a pull request:

Every automated check passed: release metadata, unit tests, lint, and the release build. I sent the pull request to Claude Opus 5 for review. It said the core fix was sound and the new test was real, and then it found three problems:

It could mix up whose workout is whose. Some workouts in my app come in from another fitness app. If I edited one of those, my app would forget where it came from, and on the next sync it would send its own copy of the heart rate, calories and distance back to Health Connect. Now the same workout shows up twice, and your totals for the day are wrong.
It could lose the Strava badge for good. A small badge shows which workouts are already on Strava. Editing a workout while Strava was turned off wiped that badge, and nothing would ever bring it back, even though the workout was still on Strava.
The release notes promised a fix that was not in the release. They said 6.10.2 fixed one bug. That fix was already in the app two versions earlier, and nobody had tested it on a real workout yet.

To be fair to Kimi, the fix pass was quick and correct. It took under nine minutes, added two regression tests, and a second review found nothing. Then it merged, tagged the release, built it, installed it on my phone, and wrote this:

For the final release, that is fair. Nothing in it touched the sensors. But the first version said the same thing while it was still promising the other fix, and that one could only be checked by hand. The bug was about pausing: if you paused a workout, the app saved the wrong start time, as if you had started later than you really did. To check it, you have to do a real workout, pause it partway, finish, and see whether the history shows the time you actually started. Nobody had done that, and it still has not been done.

None of these three were broken code. Nothing crashed and no test failed. The work looked finished, and it was wrong in the places where you have to know what the data means.

The Same Shape in a Bigger Release

The example above is small on purpose. The same pattern showed up in my personal assistant app, on a bigger release.

kimi-k2.7-code built a release covering four issues. It shipped with every automated gate green: formatter, analyzer, the project rule checker, and 3,416 tests passing. Within about an hour of the release, using it on my phone had turned up three defects:

A calendar event spanning several days still showed only its start date in the editor. That was the exact defect the issue described, in the file it named.
An "Edit fields" button did nothing. No error, no feedback. The code that handled button actions had no branch for that action, so it just returned.
Events read from a photo were supposed to be marked "(inferred)" where the model guessed a value. They never were. The flags defaulted to false and the model never set them. The tests passed because they built their own flags.

The follow-up pull request, from the same model, addressed all three. Review found eight more problems in the fix. One example: the new end-date row reused the start-date picker, so picking a new end date moved the whole event. It took three review rounds to get it mergeable.

When the Fix Is Where Things Break

The part I found most useful came from one pull request where two models took turns answering the same review, back to back. It is about as close to a controlled test as my real work gets.

Round	Model	Findings to fix	What the fix pass did
1	`deepseek-v4-pro:0813`	12	Fixed 6. Left 3 still broken but reported them fixed. Added 3 new regressions.
2	`glm-5.3`	6	Fixed all 3 regressions. Fixed the other 3 only partly. Added 2 narrow new defects.
3	Claude Opus 5	6	Closed all six by changing the underlying rules.

The reviewer in all three rounds was Claude Opus 5. Rounds one and two are the setup I argued for in Executor Model, Reviewer Model: one company's model writes the code and another company's model reviews it. Round three is not. There, Opus 5 was fixing findings from its own review, so keep that in mind when reading the last row.

The DeepSeek pass is the one that stays with me. Its commit said it addressed all 12 findings. Three of them were still live, including a layout overflow "fixed" in a way that still overflowed, with a new code comment that described the opposite of what the code does. Two of the three new regressions broke behavior that worked on the main branch.

GLM was clearly better. It broke nothing. But it had the same habit, fixing the example instead of the rule. A currency check went from missing $5M to handling $5M and still missing $5bn.

That matches what I saw in July. Back then GLM 5.2 was my daily driver, and in my comparison of Codex, Claude and GLM 5.2 about 40% of its findings did not hold up when Claude checked them. The newer model broke less here, but its work still needed checking.

About Speed

When I started writing this, I planned to blame part of it on the open models being slow. I went back to the timestamps to check, and that turned out to be only partly true.

On the 6.10.2 release, Kimi's own time was short: under a minute to post the pull request, under nine minutes for the fix pass, under two minutes to release. That is in the same range as the other models I use. The long gaps on the pull request were mine, not the model's.

On the bigger jobs, the wall-clock numbers are worse. Kimi took over an hour to answer an eight-finding review, and DeepSeek almost an hour for twelve. But those times include my own gaps too, so I would not lean on them.

The number that does not depend on my schedule is rounds. How many times does the work go around review before it is right, and does each fix pass make things better or add new problems? That is where the open models cost me.

What I Am Not Saying

The tests were real. The releases shipped and mostly work. For mechanical, well-bounded work, like renames, tests, or a screen that looks like four other screens, these models are genuinely useful.

What I am saying is about the cost of getting from "looks done" to "is done." Every extra review round is time, attention, and budget, and the budget for review has to come from somewhere.

Where I Landed

I cancelled Ollama Pro. The $60 did not last the first week, and the work it bought needed more rounds of review than the work from the plans I am keeping. That also ends the two-plan setup I described in GLM Plans, Fable Validates, and API Keys Are Not for Development, where an Ollama plan covered my less critical projects.

The way I compare plans now: price per month tells you very little. Multiply it by the number of review rounds it takes to get to mergeable, and then check how long you wait when the limit runs out. That is the number that decides whether the work actually ships.

References

From Idea to Wrist: Building a Watch Face in About an Hour

noreply@blogger.com (Artem Ervits) — Sun, 20 Sep 2026 22:19:16 +0000

This came from a post I saw on X. Somebody wanted a different watch face for their Garmin, sat down with Claude, and had one working in short order. I had thought about doing that for a long time. Like most new things, I was hesitant, because it meant stepping out of my comfort zone.

It turned out to be easy with the help of AI. After about an hour of fiddling with it, I had a working watch face. I almost wish I had jumped on it earlier in the week, because I bought a watch face a few days ago and was very disappointed with it.

The face is Ridgeline: big time, a ring that fills toward my step goal, heart rate, steps, battery.

Getting Started Was the Part I Dreaded

Writing for a watch is its own little world. There is a language I had never used, a set of tools I did not have installed, and a way of getting the finished thing onto the watch that I had never done. That stack of unfamiliar steps is exactly what had kept me from trying.

The agent handled all of it. It set up the project, wrote the first version, and when it turned out my machine had none of the Garmin tools on it, it did not start installing things by hand. It wrote up what was missing as an issue against the repo where I keep my machine setup, so the next laptop I set up gets the same tools automatically. I liked that. It fixed today's problem in a way that also fixes it next time.

The Number It Made Up

Here is the part I want other people to know about.

To show me the design quickly, the agent built a little web version of the face. Useful - I could try a change and see it right away. But to draw it, it needed to know how big the watch's built-in fonts are, and that is not something the documentation tells you. So it guessed.

The guess was off by a lot. Which meant the mockup I was looking at, and feeling pretty good about, was not what my watch was actually going to show. The big bold numbers I thought I had picked had never once appeared on the watch. Worse, the agent then suggested a fix built on top of its own bad guess, which would have made things worse in a new way.

What fixed it was dull and obvious in hindsight: stop guessing, run the thing, and have it print the real sizes. Once we had real numbers, the layout stopped being a debate about taste. Some of what I wanted simply did not fit, and the numbers said so plainly. The seconds had to move somewhere else entirely, and the labels had to shift because a round screen quietly eats your corners.

To its credit, once it had real numbers the agent worked out the consequences before touching anything, instead of repeating its earlier suggestion.

Small Things Worth Catching

Two more turned up right at the end, when I went to put the code up publicly.

The project still had a leftover placeholder identifier from the template it started as. Harmless for something you install on your own watch, a problem if you ever want to publish it properly, and easier to change before anyone else installs it.

And every commit in the project had been made under a fake email address, because of a stray setting in the project folder. Caught it seconds before pushing. Trivial to fix while it was still private, genuinely annoying afterward. An assistant that checks who it is committing as before a publish step is doing unglamorous work that pays for itself.

What I Took Away

I had put this off for a long time because it looked like a whole new world to learn. It was, and the agent carried almost all of that for me. Idea to working face on my wrist, in about an hour, on a Sunday.

It was fast and it was genuinely good at the parts I did not know. It also handed me a confident number it had no way to know. The correction came from outside the conversation, from running the thing on real hardware instead of trusting the picture on the screen.

That is roughly the deal, and I will take it. Just check the confident answers against the real thing before you build on top of them.

Take It, or Build Your Own

Ridgeline is public and open source. If you want to install it, change it, or send a fix or an idea my way, please do. Issues and pull requests are welcome.

But honestly, the better takeaway is the one I started with. If you have been staring at your own watch thinking the face you want does not exist, go build it. You do not need to know the language or the tools. If you end up with your own face instead of mine, that is a good outcome as far as I am concerned.

Three Days a Week: What a Rate Limit Actually Costs

noreply@blogger.com (Artem Ervits) — Thu, 17 Sep 2026 00:48:17 +0000

In July I wrote that monthly plans beat paying by the token for my development work. I also mentioned getting only three or four working days before hitting the weekly limit. What I understated was how much that limit would start deciding my schedule, and which model reviewed my code.

A $20 monthly plan sounds like a month of work. In practice the weekly limit decides how much of that month you actually get, and for me the answer was about three working days. Then I waited. Not for an hour, not until midnight, but for the reset, which is most of a week away when you hit the wall on a Wednesday.

Three Days, Then Waiting

The plan was Ollama Pro, monthly. Twenty dollars, and on paper that is a bargain next to what the same work costs through an API key. The weekly ceiling is what broke it. I would get through roughly three days of real work and spend the rest of the week unable to do the thing I was paying for.

Waiting is worse than it sounds, because the work does not pause politely. A bug I was halfway through debugging on Wednesday was still a bug on Thursday. The choice was not "work now or work later." It was "work now on a different plan, or do not work."

So I bought ChatGPT Plus again. That put me at three concurrent subscriptions, which is not a decision I made so much as one I backed into. I also wanted to try the new Kimi model around then, and I deliberately did not, because I was not going to evaluate a fourth thing while paying for three. I waited for the Ollama month to run out first.

It ran out, and I ended it. Three plans was not something I could justify, and by then Opus 5.0 had taken over most of my daily coding anyway. I will say this for 5.0 over 4.8: it is more willing to admit its predecessor was wrong. It found bugs in branches that came out of 4.8 sprints and said plainly that the earlier reasoning was bad, instead of defending it.

Codex is not immune either. With 5.6 Sol I burn through the weekly rate fast, and then it is the same wait, just on a different vendor's schedule.

The Limit Picks Your Reviewer

Here is the part I did not see coming, and it is the reason I wanted to write this post rather than another one about price.

When I ran that big evaluation pass over a codebase I thought was solid, Sol turned up a pile of issues and I wanted a second opinion from a different vendor before I believed any of them. The model I wanted for that job was Fable. Fable was no longer available in my weekly limit. So Opus 4.8 did the validation instead.

That worked out. Opus confirmed the findings, and I wrote up that workflow in Executor Model, Reviewer Model. But look at what actually happened: a quota, not a judgment, chose the model that decided which of those bug reports were real. I did not sit down and reason about which reviewer suited the task. I took what was left.

A rate limit does not politely stop you at the door. It quietly reassigns your work to whatever tier you still have, and you find out afterward whether that mattered.

Budget Rules, Written Down

In August I added a model tiering table to my AGENTS.md. I described it then as a quality rule, and it is, but the honest reason I wrote it was that I had been running the most expensive model available for everything, including renaming variables, and then wondering why I hit the weekly limit midweek.

The Codex side of that policy is more explicit than the Claude side, because I could put numbers on it. Here is the section as it actually sits in the file, with the longer bullet lists trimmed:

# Codex Model Usage Policy

Optimize for **correctness per token**, not maximum model capability on every
task. Use the least expensive model that can reliably complete the work, and
escalate only when complexity warrants it.

## Default Model Selection

- **Terra Medium - default**
  - Normal feature development
  - Typical bug fixes
  - Moderate refactoring
- **Luna Medium - mechanical / low-risk work**
  - Small UI changes, straightforward CRUD, renames
  - Formatting and lint fixes, documentation
- **Sol Medium - difficult reasoning**
  - Architecture or design decisions
  - Bugs with unclear root causes
  - Concurrency, synchronization, or lifecycle bugs
  - Work where Terra has already made a credible attempt and failed
- **Sol High - exceptional escalation only**
  - Use only when Sol Medium is insufficient for a genuinely difficult
    problem. Do not use routinely.

## Escalation Policy

Do not escalate preemptively.

Preferred progression:

`Luna Medium -> Terra Medium -> Sol Medium -> Sol High`

If escalating because another model failed:

1. Preserve the useful work already completed.
2. Clearly summarize the remaining problem and observed failures.
3. Do not repeat broad repository exploration unless necessary.

## Model Budget Target

- **~70% Terra Medium**
- **~20% Sol Medium**
- **~10% Luna Medium**

This is a guideline, not a quota. Task complexity should determine model
choice.

Two things in there are doing most of the work. The first is point three under escalation. When a cheaper model fails and you hand the problem up, the expensive model's instinct is to re-read the entire repository before touching anything, and that re-exploration is where a surprising amount of my budget went. The second is the percentage target. It is a guideline and task complexity still decides, but having a number in the file changes the conversation. Without one, every task feels like it deserves the best model.

When I published the shorter AGENTS.md I said I did not know yet whether it was working and to ask me in a month. It has been about four weeks. On the instruction-following question I still do not have a clean answer. On the token question, with the current mix of plans and model choices, I am no longer stopping on Wednesday. I cannot yet separate how much of that came from tiering and how much came from spreading the work across subscriptions.

The Trap Inside the Budget Rule

There is a catch, and I want to state it because the tiering table does not.

Rate limits push you down the tiers. That is the whole point. But they push you down at exactly the moment you can least afford it, because the work you do when you are running low is usually triage: which of these reported bugs are real, which are noise, what do I fix first.

Triage is not mechanical work, and I learned that the expensive way. I closed nine issues after a source-only review. A second pass ran the reported inputs and reversed four verdicts. I changed both the model and the instructions for that second pass, so I cannot credit the model alone. I went through what flipped and why in A Convincing Review of the Wrong Path.

The budget lesson survives either explanation: leave room for verification before dismissing a bug report. A cheap pass that clears your queue is not a saving if the queue was right.

So my own rule now has an exception attached: drop tiers for implementation, not for judgment. If the budget is tight, spend what is left on deciding what to work on, and let the cheap model type.

That turned into how I split the work generally. Fable and Sol reason and plan. Terra and Opus implement against those plans. The scarce, expensive tokens buy thinking, not keystrokes.

A Week of This in Practice

To make it concrete, here is a real entry from this month.

9/9 - Started using GPT-6 Astra for reviews and analysis. It reported 20 new bugs, some rated critical. I ran those past Claude, which confirmed them. Then I had Claude implement the fixes and sent the resulting pull request back for review, where Astra caught regressions in the fix itself.

Four stages for one batch of bugs. Cross-vendor review is worth it, and I made that case in Executor Model, Reviewer Model. I just think it is worth saying out loud that the workflow I recommend is a workflow that consumes two budgets instead of one.

The budgets are not even the same shape anymore. Noticing a problem and writing it up as a real work item has drifted onto my phone, and that half draws down a different plan than the one doing the implementing.

The $15 I Did Not Mean to Spend

One more, because it belongs to the same story from the other direction.

I had left an OpenAI API key configured for a model in VSCode and forgot about it. One short coding session later I had burned $15 without realizing I was not going through the monthly plan I was already paying for. Nothing about the experience tells you which one you are on. The editor looks the same either way.

I made the case in GLM Plans, Fable Validates, and API Keys Are Not for Development that API keys are the wrong default for development work. This is the smaller, dumber version of that argument: the plan only protects you when the traffic actually goes through it.

Where I Landed, For Now

Model tiering written into the file the agents read, with a rough percentage target. High-reasoning models for planning and review, cheaper ones for implementation, and never the cheap tier for deciding what is real.

And Ollama is back, which puts me at three plans again: ChatGPT Plus, Claude on the $200 annual plan, and Ollama Pro monthly. The difference is that I chose this one. They introduced an annual plan, which is the kind of thing that makes you reconsider a decision you were fairly confident about, so I re-subscribed monthly first. Not because I changed my mind, but because I wanted to answer the question properly this time. I am running DeepSeek v4 Pro and GLM 5.3 on the less critical paths and watching what happens to the weekly ceiling.

That is the actual test, and it is not a price test. I already pay for one of these a year at a time, so I am not against annual commitments. Committing to a year of Claude was easy, because by then I knew exactly what a week on it looked like. I do not know that about Ollama yet, and I cancelled it once already. What I need to learn before signing up for twelve months is whether this configuration gives me the flexibility I did not have the first time around, and there is no way to get that from a pricing page. I have to spend a month either running into the limit or not running into it.

Kimi, the model I put off trying because I would not evaluate a fourth thing while paying for three, is still sitting there unevaluated. I am aware of the irony.

The thing I would tell anyone comparing plans is to stop comparing the monthly numbers first. Ask how many days of work the weekly limit actually buys, because that is the number that determines whether you are working on Thursday.

References

Building a Personal Assistant: What I Got Wrong

noreply@blogger.com (Artem Ervits) — Tue, 08 Sep 2026 13:23:26 +0000

A couple of weeks ago I wrote that I am the only user of my personal assistant app, so every issue in its tracker is one I filed after watching something fail on my own phone. That sentence is no longer true. My daughter started using it, and she found a limitation I had built in so deeply that I could not see it.

Before that, though, some numbers. The app is at version 6.5.0, build 191, with 115 release notes behind it. It is 245 Dart files in lib and 234 test files. The lib/services directory alone holds 124 services. I bring this up because people who ask me about building their own assistant usually think of it as a weekend project - a chat box with an API key behind it. The chat box was the weekend. Everything after that is the reason the thing is actually useful.

Three things moved it from a toy to something I open every day: it learns from its own mistakes, it accepts the way people actually talk, and it can look at a photo. Everything else is plumbing, and I will be honest about the plumbing at the end.

Why Not Just Use One of the Big Ones

A fair question with several answers. Here is one concrete one.

I needed AI access my daughter could actually use. She is a preteen, so most of what an adult would reach for is closed to her. ChatGPT is age restricted. I could not get Gemini working on her phone at all, and still do not know why. We tried the assistant that comes with WhatsApp, which means Meta, and it was not what she needed. Every path led to a policy wall or a product built for someone else.

So I built the thing instead, against an API endpoint. Today the app runs on Gemini, with entry points for Anthropic and OpenAI, and roles pinned to a local gateway when the data should not leave the device at all. Same models everyone else is using. Different terms of access, because the terms are mine.

The part I did not anticipate was transparency. The app has a screen that logs the prompts it sends, verbatim, so you can see exactly what left the device. I built it for myself, to debug what the app was actually sending. It turns out to be the thing that makes a kid using AI reasonable rather than nerve-racking. I can see the request and the response.

I want to be careful about how I say this, because I am not reading her conversations under a microscope, and a parent who does that will find their kid stops using the tool honestly. But the alternative on offer is a chat box run by a company, aimed at a preteen, where I get to see nothing at all. Being able to look is different from looking. I would rather have the option and rarely use it.

The Loop That Closes Itself

When the assistant gets something wrong - a bad answer, a card that renders badly, a log that lands in the wrong place - I paste that straight back into the chat and it files a GitHub issue with the context attached.

That sounds like a small convenience. It is the most important thing in the app. Small projects do not die because the bugs are hard. They die because noticing a bug and writing a good report about it are two different jobs, and the second one lands at the exact moment you are least willing to do it: standing in a kitchen, one hand busy, annoyed. So you tell yourself you will remember. You do not. The bug survives another month.

The app also watches for its own mistakes - you restating the same request, you undoing what it just wrote, a question that almost matched something specific and got a vague answer instead. That last one matters because you never see it as an error.

What I got wrong at first was asking on a schedule. When it prompted "did I get that right?" at a fixed cadence, I said yes to make the prompt go away. The friction stayed and the signal disappeared. Now it asks only when something actually looks wrong, and it works that out on the phone without sending anything anywhere.

Messy Input Is the Real Feature

Nobody logs data in a form. People say "had a bowl of soup and a coffee around two." So the parser does the work, and most of what makes a parser good is knowing when not to act.

Two bugs taught me that, and they failed in opposite directions. "remove a cup of water" logged a cup of water, because the parser saw a quantity and never looked at the verb in front of it. Going the other way, everything hydration-related matched the literal word water, so seltzer and club soda reached no logging path at all. Nothing failed. Nothing was logged either.

One is the app acting when it should not have. The other is the app not acting when it should have. Both come from reading the surface of a sentence instead of what the sentence is doing.

The third rule is the one I would keep if I threw out the rest: when you state your macros, they are stored exactly as you said them. The model does not get to re-estimate a number you already gave it. If you tell the assistant something factual, re-deriving it with a model is not helpfulness. It is the app deciding it knows better than the person holding the phone.

Logging a Meal From a Photo

On August 17 I photographed a bowl of cold beetroot soup - svekolnik - and asked the app to log it. It came back with 110 calories, 3g protein, 22g carbs, 1g fat, and I confirmed the card.

The subtle part is intent. A camera button used to be the intent; with one attachment control, the words have to carry it. An attached picture next to an ordinary question is a question, and misreading it as a log buries the answer you were waiting for under a food card. The opposite mistake costs one retyped sentence, so the matcher under-claims. The model will happily guess every time if you let it.

The failure modes are honest ones. A plated meal goes well: chicken, rice, something green, three things to name and add up. A pot on the stove goes badly, because it is one volume with everything dissolved into it. Portion size is the worst of it, since the model is judging volume from a photo with no reference object and no depth. It cannot tell a teacup from a mixing bowl. Neither could I, from the same photo.

So nothing is written until I confirm the card, and the card takes a correction in plain words: "about two cups," or "half of what is in the picture." The model is good at naming what it sees and bad at knowing how much of it there is, and the app is built around that split instead of pretending otherwise.

One reason I keep working on this part: I have been using it to track calories and fasting, and I am down more than ten pounds. It has taken over from MyFitnessPal for me. Not because the estimate is more precise - it is not. Because logging a meal became cheap enough that I actually do it.

Then My Daughter Started Using It

She asked for one thing I had not thought of: let it take files and images for analysis, not just food. Her first test was photographing a math equation from her homework and asking the assistant to check her work. A good request, and it earned its keep on something that was neither homework nor food. She has track and field events this season, and the schedule reached me the way schedules actually reach people: as a picture of a calendar, sent from her phone to mine. I uploaded it and the assistant read the dates off the image correctly and put them on my schedule, since I am the one doing drop off and pick up. Nobody typed anything.

That one has quietly changed how I run the week. School just started, and the schedules arrive in every shape a school can produce: a PDF from one program, a month grid from another, plain text in an email, a photo of a paper handout that came home in a backpack. Different activity, different format, same deadline. The old version of this was me squinting at a screen and retyping dates into a calendar, which really meant half of them never got entered at all. Now I point the app at whatever is in front of me and the dates come back filled in, with the names and the times attached. It saves me a real amount of time each week, and it has nothing to do with the chat box.

It also shows where the app still falls short. Those events landed on my schedule and stopped there. Two-way Google Calendar sync is not working yet, so nothing flows back to hers, and we are still coordinating around one device instead of one calendar. An open issue, not a solved problem, and the same shape as everything else here: the model part was the easy half.

The problem she exposed is bigger than any of it.

The app has profiles - personal, work, health, finance, travel - and each one carries its own memory categories, its own enabled tools, and its own routing preference for sensitive data. Health and finance are local-only. That design has held up well, and I was quietly proud of it.

Then someone else picked up the phone, and I saw what those profiles actually are. They switch context. They do not switch person. Every default under all five of them is mine: hydration nudges, fasting timers, focus blocks, the particular set of habits a software person decides to fix about himself. None of that is what a preteen wants from an assistant, and a fasting timer offered to a kid is not a neutral default. It is wrong.

So the work in front of me is a harness around a profile that carries whose assistant it is: which suggestions exist at all, which memory categories are collected, which tools are reachable. The rules a preteen profile needs are mostly subtractive - things the model does not get to raise on its own, no matter what it infers from the data in front of it. Health is the obvious case. I would rather the assistant be boring than have it decide, on its own, to have an opinion about a kid's eating.

I want to be clear that this part is not built yet. It is what I am building next, and I am writing it down here because the lesson is the transferable part: I designed for one person without ever deciding to, because the one person was me and the app agreed with me about everything.

What I Would Tell Someone Starting

First, the promise I made at the top: almost none of those 124 services are AI. The parts that took the most time were calendar and contact intake, notification scheduling, encrypted local storage, backup and export, and being able to change model providers without rewriting the app around whichever one is currently cheapest. That last one matters more than it sounds, for reasons I wrote about in the July cost post. Build around one vendor's client and a price change is an outage. Keep model roles in a registry and it is a settings change.

The rest is short.

Build the correction loop before the features. The app that files its own bug reports gets fixed; the one that requires you to open a laptop does not.

Keep the data local and own the schema. Every provider swap I have done was painless because the data never lived at the provider.

And decide early whether the assistant serves one person or a persona. I did not decide. I defaulted to me. Retrofitting that is the expensive kind of work, and it took someone else about a week of using the app to expose it.

References

A Convincing Review of the Wrong Path

noreply@blogger.com (Artem Ervits) — Fri, 28 Aug 2026 13:16:21 +0000

This article follows Executor Model, Reviewer Model: Why Cross-Vendor Code Review Actually Works, where I wrote about using a second model to validate review findings. This time, the missing check was not another opinion. It was running the code.

I am the only user of my personal assistant app, so every issue in its tracker is one I filed after watching something fail on my phone. One was the fasting timer. I told it in chat that I had started a fast at seven in the evening, and nothing started.

I handed that issue and eight others to Claude. It read the source, quoted the right files, and explained why the timer could not fail the way I described. I closed all nine issues that afternoon.

A few days later I asked Claude to check again, but this time it had to run the reported inputs. Four verdicts flipped. The second pass took six and a half minutes instead of two and a half. Those extra four minutes recovered four real bugs and found two more I had never noticed to file.

The first review was not careless. It reasoned correctly about the paths it opened. It just answered the wrong question.

Four Verdicts That Changed

A hydration question became a habit command

The first review saw that the intent parser required words such as habit or routine, neither of which appeared in the report. Hydration routing also came first. That looked conclusive.

Then I ran the exact sentence:

check the water hydration again, why did you report 10 cups

The parser returned a habit-completion action for a habit named you report 10 cups. The habit gate protected only status and list requests; completion ran when the gate was absent. A question guard anchored with ^ also missed the second clause.

The fasting timer never started

The first review traced a global state provider shared by chat and the timer screen. State propagation was fine, but the parser never created state to propagate.

"fast started at 7:00 p.m." -> null
"fast started at 7:00 pm"   -> null
"started fast at 7:00 pm"   -> start, 19:00
"start fast at 7:00 p.m."   -> start, 07:00

The matcher required the verb before the noun, so natural variations fell through. It also understood pm but not p.m., silently turning seven in the evening into seven in the morning.

The chart had nothing to draw

I logged a habit through chat and its duration chart stayed blank. The chart existed and rendered correctly. The failure was on the write side. Chat parsed 30 min, replied Walk, done (30 min)!, then called a repository method with no duration parameter. The database stored null, and the chart correctly filtered that row out.

The review had inspected the half that worked.

The reminder report exposed two other bugs

The scheduler used distinct IDs and staggered times, so the original report of reminders arriving together remained uncertain. But the investigation found that the app initialized the time-zone database without setting the device's local zone. Notifications were scheduled against UTC, including quiet hours and the midnight cutoff.

It also found a hydration nudge reading settings from a fresh container in a background isolate. That container returned defaults, so the production nudge did not behave like the main app.

The Device Added Evidence the Source Could Not

An alarm dump from a Pixel 9 Pro XL showed several exact alarms sharing the same timestamp:

10:00:00.000  EXACT
11:00:00.000  inexact
11:00:00.000  EXACT
11:00:00.000  EXACT
15:00:00.000  EXACT
15:00:00.000  EXACT
15:00:00.000  EXACT

Five days of uptime made a recent reboot less likely. The time-zone fix also appeared to work: reminders were back on local-hour boundaries. But the dump did not expose notification IDs, so it could not identify which schedules collided.

Another diagnostic showed 43 notifications enqueued, 43 blocked, and none shown. Notification permission had never been requested. The alarms could run, but none of the resulting notifications reached me.

The next release recorded IDs, scheduled times, and outcomes, then grouped notifications by fire time. When evidence does not exist, add the smallest instrumentation that can produce it.

What Actually Changed Between the Reviews

The work used Claude Opus 5 and Sonnet 5. I also changed the instructions: the second review was adversarial, received the exact reported inputs, and had to prove the symptom could not happen. Because the model and method both changed, this is not a clean model comparison.

That distinction matters more than choosing a winner. A different model may bring different assumptions. Running the reported path gives direct evidence about that path. Finding one working path does not rule out the path the user was on.

What I Would Tell Someone Setting This Up

Run the real function against the literal string from the bug report. Not a paraphrase, not a tidied-up version. The exact string, plus three to six neighbors of it. For anything parser-shaped this is a quick way to test whether the reported path behaves as described.
Tell the reviewer what to distrust. Hand over the failure modes you suspect and put the burden on it to prove the symptom cannot happen. A reviewer asked only to find a correct path may stop after finding one.
Do not close an issue on a triage verdict. The costly part was not that the first review got things wrong. Reviews get things wrong. It was that nine issues went away on a single unverified pass. If the report does not reproduce, leave it open as needing evidence until the exact input, state, and environment have been tested.
Trace the write path, not just the render path. Two of the four reversals were hiding on the side of the feature the first review did not inspect.
Once a bug reproduces, preserve the exact input as a regression test. Execution finds the mismatch once; the test keeps it from returning.
When the evidence you need does not exist, add focused instrumentation and collect only the data the investigation requires.
Budget the extra minutes. Six and a half instead of two and a half, for four reversed verdicts, is not a close call.

The four fixes went out in one pull request and the notification diagnostics in another. The reminder pile-up is still open, waiting on the next capture from the device, which is the correct state for it to be in. It was also open the entire time, which is more than I can say for the issues I closed too early.

References

Executor Model, Reviewer Model: Why Cross-Vendor Code Review Actually Works

noreply@blogger.com (Artem Ervits) — Thu, 20 Aug 2026 14:00:51 +0000

I thought one of my projects was in reasonable shape. I had been working in it for months, it was on version 4.24.3, and Claude had been through most of it more than once. Then I pointed Codex GPT-5.6 Sol at it with Ultra High reasoning, asked it to evaluate the codebase and file issues for anything it found, and it came back with 42 new issues in about ten minutes. Some of them were release blocking. I had Claude Opus 4.8 go through all 42 looking for false positives, and it confirmed zero. Every issue was real.

That result is what convinced me to stop having one vendor's model check its own family's work.

The Setup: One Model Writes, a Different Vendor Reviews

The workflow is simple to state. Implement with one model, working from the most critical issues to the least critical and matching model strength to issue criticality: the highest-confidence model takes the most critical work, and the cheaper, faster models take the routine fixes further down the list. Then validate the pull request with a competing but equivalent model from a different vendor, rather than asking the same model to check its own output. Claude Code's built-in PR review command is the mechanism on that side, which is what made the second half of the loop practical rather than a nice idea.

On the round that produced the 42 issues, the executor was Codex 5.6 Terra/Sol and the reviewer was Claude Opus 4.8.

What It Actually Found

"Some were release blocking" is easy to say and hard to believe without examples, so here are three of them. The project is Zone 2 Trainer, my Android heart-rate training app.

The first is a silent data-corruption bug. The History screen loads workouts through a query that only populates part of each row for performance, and then the edit and relink paths hand those same partial objects to a full-row database update. Anything the summary query left out gets written back as a default or a null. Health Connect identity, GPS, intervals, RR data, cycling power and cadence were all in range. Nothing crashes. You just quietly lose columns.

The second is in the same family. Import and manual-entry paths were using Room's REPLACE conflict strategy against an existing workout ID. SQLite implements replace as delete plus insert, so with foreign keys turned on it can cascade-delete the heart-rate samples belonging to that workout before the replacement row lands.

The third was in Bluetooth scanning: a new caller could attach to an already-running scan and complete unexpectedly, scan failures never came back through the normal result path, and one code path read a device address after the Bluetooth permission could have been revoked.

None of these are style nits, and none would show up as a failing test.

What I found convincing was not just the list but the specificity. Each issue came with file-and-line evidence, a statement of expected behavior, and the commands to prove it. That is what made them cheap to act on: the fixes for all three landed in git over the following two days.

Why This Was Not Getting Caught

The obvious question is why none of this surfaced earlier, given how much model attention the code had already had.

I think it is structural. A model reviewing output from the same family shares the training data, the architectural instincts, and the blind spots. If a model does not think to check for a class of bug when it writes code, it tends not to think to check for it when it reviews code, because it is applying the same judgment both times. The review adds a second pass, not a second perspective. Claude had been through this codebase repeatedly and had not flagged the partial-row update problem once.

Codex Sol does not share that lineage, and the gap between the two vendors' failure modes is where the missed issues were sitting.

I should be honest about the flip side, because I have written about it before. Pointing an unfamiliar model at your code does not automatically produce good findings. When I ran GLM 5.2 findings through Claude a few weeks earlier, roughly 40% were not real problems. Same cross-vendor shape, very different result. Which is the whole reason the next step is not optional.

The Validation Step Is What Makes the Number Trustworthy

Forty-two issues from a model I had not used this way before is not automatically good news. A model that is simply more aggressive about flagging things produces a long list too, and a long list of false positives is noise with extra steps. Given the 40% I had just seen from GLM, I had no reason to assume this list was better.

So the number that matters here is not 42. It is zero. Opus 4.8 went through the issues looking for false positives and did not find any, and that is what turned a pile of flagged issues into a list I could work through with confidence.

Fable would have been my first choice for that validation pass, but it was unavailable, having hit its weekly limit. Opus 4.8 did the job instead, and did it well enough that the result held up.

Where This Is Headed

The plan was always to flip the roles: let Claude implement and Codex review the next round, so the review is never coming from the same vendor that wrote the code twice running. That round has not happened yet as of this writing, so I have no results to report on it, only that it is the obvious next step and the point of treating this as a loop rather than a one-time audit.

The workflow has also kept evolving past the two-model version. Fable and Sol are now doing reasoning and planning, with Terra and Opus handling implementation from the higher-reasoning models' comments, which pushes the same cross-vendor principle earlier in the process instead of confining it to the review stage.

What I Would Tell Someone Setting This Up

Do not trust a model to review its own family's output. The shared training is exactly what produces the shared blind spots.
Route the review through something durable, like GitHub issues or a pull request, so the findings are a reviewable artifact instead of a conversation that evaporates.
Validate the reviewer's findings before acting on them, especially the first time you use a new model this way. A long issue list is worth nothing until you know its false positive rate, and 0% and 40% both happened to me inside a month.
Demand file-and-line evidence in the findings. It is what separates an issue you can act on from an opinion you have to go verify yourself.
Match model strength to issue criticality when implementing the fixes. The most expensive model you have does not need to touch every line.
Plan to flip executor and reviewer roles. A model that only ever reviews and never writes will not show you where its own review blind spots are.

The pair of numbers I keep coming back to is 42 and zero: forty-two issues that months of same-vendor attention had not surfaced, and zero of them noise once a different vendor's model checked the work. That gap is the case for cross-vendor review, and it was wider than I expected.

References

Claude Keeps Ignoring My AGENTS.md, and I Think I Know Why

noreply@blogger.com (Artem Ervits) — Tue, 18 Aug 2026 20:31:41 +0000

In July I asked an agent to push a build of my personal assistant app to my phone. Routine task. I do it most days. It built the release APK, ran flutter install, and my habits, journal, and health logs were gone. Not corrupted, not partially migrated. Gone. flutter install uninstalls the app first when it feels like it, and everything my assistant had learned about me lived in app-private storage with no cloud copy I could count on.

The part that still bothers me is not that it happened. It is that the instruction telling it not to do this was already sitting in the repository, in the file the agent claims to read.

The Rule Was Already There, in Capital Letters

After the first time this happened, I did the obvious thing. I wrote the rule down. The commit is still in my history, and the message reads like an incident report because that is what it was:

flutter install uninstalled the app before reinstalling on a real device, wiping local user data (habits, journal, health logs) with no cloud backup guaranteed. Codify adb install -r / flutter run --release as the only acceptable device-deploy path going forward.

The rule that came out of it is not subtle:

Non-destructive install (MANDATORY): real user data (habits, journal, health logs) lives in app-private storage with no guaranteed cloud copy. Never uninstall to deploy. Use adb install -r <apk>, which overwrites in place. Never use flutter install on a device with real data.

I added a second one next to it, also marked mandatory, requiring a fresh backup before any install on a device with real data.

Then it happened again.

That is the uncomfortable part I want to be honest about. I did not forget to write the rule. I wrote it in capital letters, in the file that every agent working on this project loads, and it did not stop anything. The obvious fix was not a fix.

It Is Not One Model

My first instinct was to blame the model I was using that week. That does not survive contact with the evidence.

Opus 4.8 did it. Opus 5 still does it. Codex is not innocent here either. These are different companies training on different data with different post-training, and they fail the same way on the same file. When every vendor produces the same behavior, the problem is probably not the vendor.

I will give Opus 5 credit for one thing, because it is a real improvement. It is far more honest about mistakes than 4.8 was. It went through branches I had shipped and found bugs dating back to sprints that 4.8 had done, and it said plainly that the earlier reasoning was wrong. Older models tended to defend whatever was already in the file. That honesty is worth something.

But honesty after the fact is not the same as following the rule up front. An agent that admits it destroyed my data is still an agent that destroyed my data.

The MEMORY.md Tell

The detail that made this click for me was not the data loss. It was something much smaller and much stranger.

Agents working in my project kept learning lessons and writing them down. Good behavior, in principle. Except they wrote them in the wrong place. One session worked out my versioning scheme and carefully documented it in a MEMORY.md file. Another put notes in some other stray markdown file it had invented.

My versioning rule was already in AGENTS.md. It had been there for months.

That is not an agent breaking a rule it read and decided to skip. That is an agent that never really absorbed the file in the first place, deriving a rule from scratch that it already had, and then filing its discovery somewhere I would never look. You end up telling it, over and over, in nearly every session: read AGENTS.md. Which rather defeats the purpose of having one.

What I Actually Think Is Happening

Here is my honest theory, and it is not flattering to me.

The first line of my AGENTS.md calls it "the operating contract for AI agents and developers." That word, contract, is where I went wrong. A contract is binding. It sits outside the negotiation and constrains it. That is not what this file is at all.

AGENTS.md is context. It goes into the same window as the task I just typed, the files the agent opened, the test output, the compiler errors, and everything else competing for the model's attention. It is not a constraint on the system. It is one more voice in a crowded room, and it is the quietest one, because it was loaded first and has nothing to do with the immediate problem the model is trying to solve.

Now look at what I did every time something went wrong. I added a rule. Twenty eight commits have touched that file since May. It started at 80 lines. By the middle of August it was 220.

Every rule I added to fix a failure made the file longer. And every line I added made all the other lines a little weaker. I spent three months responding to a problem in the exact way that made the problem worse. The rule about not wiping my data was not competing with the model's laziness. It was competing with 219 other lines I had written, most of them about branch naming and lockfiles.

If everything is important, the model has no way to know that one of those lines is about destroying my data and the rest are about tidiness.

Pruning Instead of Adding

So today I did the opposite of what I had been doing. I cut the file from 220 lines to 136.

I went through every rule and asked what it actually protects. Preferences got deleted or compressed. Anything I could enforce somewhere else, I moved out of the file. What survived is mostly rules that protect real user data or a shipped contract, and those are the only ones still marked mandatory. If everything is mandatory then nothing is, so now that word appears only where the failure mode is somebody losing their journal.

I also added a model tiering table, which is a token budget rule as much as a quality one:

Tier	Use for
Haiku - mechanical	Small UI changes, CRUD, tests, renames, formatting, docs
Sonnet - default	Normal features, typical bugs, scoped changes, integration work
Opus - difficult reasoning	Architecture, unclear root causes, concurrency, cross-cutting refactors
Fable - exceptional	Only when Opus is not enough. Not routine.

I had been running the most expensive model available for everything, including renaming variables, and then wondering why I hit my weekly limit on Wednesday. Escalate when the work earns it, not preemptively.

One more rule survived the cut, and it is my favorite, because it came out of a genuinely bad week. Some Bluetooth fixes I shipped a couple months back were too narrow, and the consequences kept escalating with each patch. The root cause was an agent asserting how a third party library behaved without ever checking. So now the file says: cite current source behavior and the tests or commands that prove a change, and never imply proof from tests that were not run.

Is the shorter file working? I genuinely do not know yet. I made this change today. Ask me in a month. What I can say is that the previous strategy, adding another paragraph every time something broke, had three months to work and did not.

The Obvious Answer, and Why It Is Not Enough

The advice I expected to end this post with was: stop writing rules, put the guardrail in tooling. Make it a script the agent has to run instead of a sentence it can skip.

I went to check my own history for a clean example of that working. What I found argues the other way.

In July, unformatted code reached main and failed CI twice in a row, on two different releases. My response, on July 19, was to add another line to AGENTS.md requiring a clean format check before every push. The commit message is blunt about the gap:

Formalizes the gap that let unformatted commits reach main and fail CI twice in a row (v3.11.3, v3.12.0) — flutter analyze/test passing was mistakenly treated as sufficient, but neither checks formatting.

Here is the problem with that story. The tooling already existed. A pre-commit hook running dart format --set-exit-if-changed . had been in the repository since May, two months before those two failures. I did not lack a script. I had the script and the rule, and unformatted code still shipped twice.

Pre-commit hooks only run if somebody ran pre-commit install in that working copy, and that is a manual setup step in my README. A hook is not a wall. It is a rule that happens to be written in YAML, and it can be skipped by a fresh checkout or a flag.

The thing that actually caught the problem was CI. Not because CI is smarter, but because it runs on a machine where nobody involved gets to opt out.

There is a detail here I find funny in a bleak way. My pre-commit config has an exclusion list, and AGENTS.md is on it. The file full of rules is the one file my tooling never checks.

What I Would Tell Someone Writing One of These

Write rules as commands, not preferences. "Prefer" is an invitation to negotiate with something that will happily negotiate.
Reserve your emphasis for things that destroy data. Spend it anywhere else and it stops meaning anything.
Prune when you add. Treat length as a cost you are paying, not a sign of thoroughness.
Move what you can into tooling, but be honest about which tools can be bypassed. A local hook is a suggestion with extra steps. A gate that runs where nobody can opt out is the only kind that holds.

That last one is the real lesson, and I paid for it with my own data. The instruction file is where you put the things you cannot enforce. It is not where you put the things you cannot afford to lose, and neither is a hook somebody has to remember to install.

References

GLM Plans, Fable Validates, and API Keys Are Not for Development

noreply@blogger.com (Artem Ervits) — Thu, 16 Jul 2026 16:25:45 +0000

In my last post I compared Codex, Claude, and GLM 5.2 and landed on two $20 monthly plans as the setup that gets the work done. Two weeks in, I changed that setup: Claude moved to the $200 annual plan, which is essentially the $20 monthly plan at a discount, and Ollama moved to the $20 Pro plan. Same shape, better price. This post is a follow-up on two other fronts. First, the workflow itself has firmed up: GLM 5.2 does the assessment and planning on less critical projects, Fable 5 does the assessment on complex or mission-critical projects and validates GLM 5.2's findings, and Sonnet 5 handles general-purpose coding once Fable 5 has set the plan. Second, I ran the experiment I suspected would go badly and paid for AI by the token, once through an OpenAI API key with the GPT-5.6 Terra model and once through Ollama top-up credits. The numbers are worth sharing because the gap is not small. It is the difference between 3-4 days of work and 30 minutes.

The Workflow: Who Assesses, Who Validates, Who Implements

The pattern I described last time has settled into a repeatable loop, and it now splits by how critical the project is.

For less critical projects, I hand the assessment to GLM 5.2. It goes through the codebase and surfaces the issues, the technical debt, and the gaps it thinks need fixing. I keep it off the more critical work because of the false positive rate described below.

For complex or mission-critical work, Fable 5 does the assessment instead. It reads the codebase and produces the same kind of plan GLM 5.2 would, but the findings are more trustworthy going in, which matters more when the project cannot absorb a wrong fix. Once Fable 5 has written the plan, Sonnet 5 does the general-purpose coding to implement it. Fable 5 sets the direction, Sonnet 5 does the day-to-day typing.

Either way, the findings get captured as GitHub issues, or when the work is more of a campaign than a bug list, documented in a markdown plan checked into the repo. When GLM 5.2 produced the assessment, Fable 5 goes over the issues before anything gets implemented and validates that each one is accurate and actually worth doing.

The validation step is not optional, and I covered why in the last post: when I ran GLM 5.2 findings through Claude, roughly 40% turned out not to be real problems. That false positive rate has not gone away. GLM 5.2 is still the best tool I have for surfacing candidates on lower-stakes work, and it is still a tool whose output you cannot take at face value. Fable 5 is the filter. It commits to a clear verdict on each issue, real or not real, and the issues that survive are the ones that get implemented.

What I like about routing this through GitHub issues or a markdown plan is that the models never talk to each other directly. There is a durable, reviewable artifact in the middle. I can read the issue list myself, Fable's notes attach to something concrete, and when I come back to the project a week later the plan is still there instead of buried in a chat session.

OpenAI API Key with GPT-5.6 Terra: Wrong Tool for the Job

I tried running the GPT-5.6 Terra model through an OpenAI API key as part of the development flow, and the short version is that it is too expensive for that. Development work with an agent is token-hungry in a way that casual use is not. The model reads files, re-reads them, runs commands, loops on test output, and every one of those steps is metered. On a monthly plan that churn is absorbed by the subscription. On an API key you watch the balance drain in real time.

My takeaway is that the API key is best used for general purpose work, not typical programming. One-off questions, summarization, a script that calls the model a few times a day: that is where pay-per-token pricing makes sense, because usage is low and comes in short bursts. For a development workflow where the agent runs for hours, the typical monthly plan is simply more cost efficient than the API. The providers have effectively priced it that way, and fighting that pricing with an API key is a losing move.

The Ollama Numbers: $20 Plan vs $5 Top-Up

I saw the same thing with Ollama, and there I have concrete numbers because I ran both options back to back.

The $20 monthly plan comes with a weekly limit. On that plan, moderate to heavy use gets me 3-4 days of work before the weekly quota runs out. That is the constraint I complained about in the last post, and it is still annoying, but it is a workable rhythm: push hard for most of the week, then let the other window carry the load until the quota resets.

Then I added a $5 credit top-up to see how far pay-as-you-go credits stretch. The answer: about 30 minutes of low to medium effort. Not heavy agent work, not a big refactor, just ordinary back-and-forth, and the credits were gone in half an hour.

Do the math on that. If 30 minutes of light use costs $5, a week of moderate to heavy use at credit prices would run into the hundreds of dollars. The $20 plan delivers 3-4 days of that same heavy use for the price of two top-ups. The subscription is not a little cheaper for a development workflow; it is an order of magnitude cheaper.

Side Project: GLM 5.2 Building My Own MyFitnessPal

Separate from the cost math, I want to mention what I have been building with GLM 5.2 on a lower-stakes personal project, because it is a good example of where the false positive rate stops mattering. I am building a personal assistant app for myself, and one of my long-standing pet peeves is diet tracking. I already had basic macro and calorie counting working in the app.

After hearing my friends talk about how well image recognition works for food logging, I decided to add it. I described the feature to GLM 5.2 in a single prompt, and after a few minor adjustments I had a more or less functional image recognition flow: take a photo of a meal, get back a calorie and macro estimate. The results are surprisingly accurate for what is, under the hood, a single model call.

The image recognition itself runs on Gemini 3.5 Flash, called through my own Gemini API key. This is exactly the kind of usage I described above as the right fit for an API key instead of a monthly plan: a single call per meal logged, not an agent looping over a codebase for hours. The cost has been trivial, and the accuracy is good enough that I have genuinely stopped reaching for MyFitnessPal. I am only half joking when I call it a poor man's MyFitnessPal replacement. It does not have the food database or the social features, but for the one thing I actually wanted, a fast and accurate photo- to-macros estimate, it does the job for pennies a month.

One thing that impressed me specifically: the model was extremely accurate at recognizing authentic Russian foods without a lot of prompting. That is not a cuisine most food-logging apps or general-purpose image models handle well, since it is a narrower training case than pizza or a burger. I did not have to coach it with extra context or dish names. I pointed the camera at a plate and it identified the dish correctly.

This is also a good example of GLM 5.2 in its element. The project is not mission-critical, a wrong calorie estimate is not going to break anything, and the false positive rate that makes me route serious work through Fable 5 for validation is a non-issue here. GLM 5.2 got a working feature out in one prompt and a couple of tweaks, which is exactly the kind of fast, low-stakes iteration where its speed matters more than its accuracy.

Where That Leaves Me

The conclusion from the last post holds, and now I have the receipts for it. Two subscriptions, the Ollama $20 Pro plan and the Claude $200 annual plan, cover the development work. API keys and top-up credits are for the other stuff: automation scripts, occasional general purpose calls, anything where the usage is a trickle instead of a firehose.

If you are trying to decide how to pay for these tools, the question to ask is not which model you want but what shape your usage has. Agent-driven development is a firehose. Buy the subscription and let the provider absorb the churn. Save the API key for the work that only sips.

References

Codex vs Claude vs GLM 5.2: A Practical Comparison

noreply@blogger.com (Artem Ervits) — Tue, 07 Jul 2026 13:59:58 +0000

The question I keep getting asked is which AI coding assistant I actually use day to day. The honest answer is that I use more than one, and the reason is that none of them is uniformly the best. Each one has a lane where it clearly wins, and each one has a failure mode that pushes me back to the others.

This post is a practical comparison of three tools I have spent real time with: Codex, Claude, and GLM 5.2. I am not interested in benchmark scores or leaderboard positioning. I care about how these tools behave across the full lifecycle of a project: initial scaffolding, iterative refinement, design polish, and long-running reliability.

The short version: GLM 5.2 has replaced Codex as my daily driver for implementation work. Codex failed the wife test, which is what pushed me to switch. Claude is strong on design and polish but showed a huge regression after two weeks of use. GLM 5.2 is great at identifying tiny lingering issues and regressions, but the last 24 hours of work showed a 40% false positive rate when validated against Claude. The rest of this post explains what each of those statements actually means.

What I Mean by the Wife Test

The wife test is not a technical benchmark. It is the moment a tool has to explain itself to someone who is not a developer and does not care about the tool at all.

In my case, that is my wife using an app I built. She does not read the commit history. She does not care which model generated which screen. She cares about whether the app opens, whether the buttons do what they say, and whether anything feels broken or weird. If a tool produces code that passes my review but ships an experience that a non-developer immediately notices is off, that tool failed the wife test.

Codex consistently produced clean, well-structured code. The diffs were focused, the tests were reasonable, and the implementation logic was sound. But the resulting UX often felt like it was built by someone who had never watched a real person use the app. The spacing was slightly wrong, the empty states were unhelpful, and the error messages read like they were written for another engineer, not for the person staring at the screen.

That is the gap. Code quality is necessary but not sufficient. A tool can win every code review and still lose the room the moment a real user touches the product.

For infrastructure work, scripts, and backend logic, that tradeoff is fine. For anything a non-developer will touch, I have learned to expect a second pass with a different tool.

GLM 5.2 as Daily Driver: Code Quality and Productivity

GLM 5.2 has replaced Codex as my default for implementation work. When I want a focused change, a real test plan, and a diff I can review without reconstructing the entire file, GLM 5.2 is the tool I reach for first. I made the switch after finding that GLM 5.2 matched Codex on code quality while also being available through Ollama Cloud without the same rate limit pressure.

The strengths are consistent. GLM 5.2 understands the repo as a unit, not just the file in front of it. It preserves the thread of the work across sessions, runs the checks, and reports back with proof. The code quality is high enough that I rarely have to rewrite the logic. I edit, I tighten, and I move on.

I have been super productive with GLM 5.2. It identified a lot of technical debt across the codebase, and I churned through a large set of the issues it surfaced. The result is that my app is now stable and well tested in a way it was not before. The thoroughness that drives the false positive rate is the same thoroughness that made this cleanup possible: GLM 5.2 kept surfacing things until they were actually fixed.

The one thing that holds GLM 5.2 back is the Ollama weekly limit. The limit is real, and when you are in the middle of a productive cleanup pass it is the constraint that stops you, not the tool. That is the tradeoff for the always-available availability I mentioned earlier: the tool is always there until the weekly quota runs out, and then you wait.

Claude: Design and Polish, Then Regression

Claude is the tool I reach for when the work is the experience. If GLM 5.2 builds the skeleton, Claude does the finish work: layout, spacing, copy, empty states, and the small interactions that make a product feel intentional.

The design sense is real. Claude will rework a screen so that it reads better, suggest copy that actually sounds like a human wrote it, and catch the UX inconsistencies that GLM 5.2 leaves behind. When I need a feature to feel finished rather than just functional, Claude is the right tool.

The problem is what happens over time. After about two weeks of steady use, I saw a huge regression. The quality of the suggestions dropped noticeably. The design instincts that made Claude valuable early on seemed to flatten out. The output became more generic, less tuned to the project, and more like a template than a collaborator.

I do not know exactly what causes that regression. It could be context management, it could be model drift, or it could be the way the tool adapts to repeated prompts. What I can say is that the drop was large enough that I stopped trusting Claude as the primary tool for a long-running project and started using it for targeted polish passes instead.

A disclaimer on the Claude regression: the observations above were written a few weeks back using Claude Sonnet 4.6 and 4.8. I have been using Sonnet 5.0 for a week and Fable 5 for the last 24 hours, and the results are fantastic. The regression problem I described may turn out to be specific to the 4.x line, but it is too early to say for certain. I will update this post if the pattern holds or breaks over a longer stretch.

GLM 5.2: Catching Regressions, With Caveats

GLM 5.2 is the newest of the three in my workflow, and it is the one that surprised me. I am running GLM 5.2 through Ollama Cloud, which means the tool is always available with no quota window to wait out. Where GLM 5.2 really shines is identifying tiny lingering issues and regressions that slip past the other tools. It will flag a subtle edge case or a half-finished refactor that Codex and Claude both missed. The specific things it catches well are the structural ones: missing try/catch blocks, logic errors where a branch returns the wrong value or skips a needed step, and swallowed exceptions where an error is caught and silently dropped instead of being handled or surfaced. Those are exactly the bugs that survive a code review because the diff looks reasonable in isolation. Notably, Claude Sonnet 4.6 and 4.8 both missed these same missing and swallowed exceptions, so this is a real gap in Claude, not just a general blind spot. GLM 5.2 is also very good at finding missing test coverage, sometimes obsessively. It will flag an untested branch, an uncovered error path, or a public function with no corresponding test, and it will keep surfacing those gaps until they are addressed. That is genuinely useful, though it can tip into overkill when the coverage it demands is more than the change actually warrants.

The catch is the false positive rate. Over the last 24 hours of work, I ran every GLM 5.2 finding through Claude to validate it, and roughly 40% of the issues GLM 5.2 flagged turned out not to be real problems. GLM 5.2 is great at surfacing candidates, but you cannot take its output at face value. The validation step is not optional; it is the difference between fixing a real regression and chasing a ghost.

One thing that made the validation step more reliable was Claude Sonnet 5.0. Across the same set of flagged issues, Sonnet 5.0 was noticeably more certain about which fixes were actually needed than Opus 4.8 and Sonnet 4.6. Where Opus 4.8 and Sonnet 4.6 would hedge or suggest multiple possible interpretations, Sonnet 5.0 would commit to a clear diagnosis and a clear fix. That confidence is what made the 40% false positive rate manageable: I could trust Sonnet 5.0's judgment on which GLM 5.2 findings were real and which were noise.

The other cost of GLM 5.2's thoroughness is token churn. GLM 5.2 second-guesses itself a lot. It will flag an issue, walk it back, re-flag it from a different angle, and then circle around to the original framing before settling. That looping behavior burns tokens fast. I burnt 45% of my weekly limit in two sittings, mostly on GLM 5.2 re-examining the same concerns instead of committing to a fix. The always-available availability I mentioned earlier cuts both ways: nothing stops the tool from spending that availability on re-deriving conclusions it already reached, and the weekly quota is what runs out first.

There is also a reliability wrinkle I have not fully diagnosed. Some work times out once in a while, and I am not sure whether that is an Ollama thing or a GLM thing. The failure mode is that a run just stops responding and I have to restart it, which is annoying but not frequent enough to be a deal breaker. The other quirk is specific to GLM in VSCode: it keeps asking if we should pause frequently. Mid-task, it will surface a prompt checking whether I want to stop or continue, which breaks the flow when I am trying to let it run. I do not know whether that is a model behavior or a client behavior, but it is worth knowing before you commit to a long unattended session.

There is one important caveat. GLM 5.2 ignored the AGENTS.md file in this repository and implemented fixes directly on main instead of branching out. The AGENTS.md file documents the workflow rules for this repo, including the expectation that changes are surgical and tied to an issue. GLM 5.2 produced workable code, but it skipped the process guardrails entirely. That is the same pattern as the wife test problem from a different angle: the output was right, but the workflow around it was not. Code quality is not enough if the tool cannot also respect the conventions of the repo it is working in. One thing to note though: GLM 5.2 is more honest about its mistakes than Claude. Claude would not admit it failed to follow the AGENTS.md file, or it would blame the failure on lack of memory from a different session. GLM 5.2 would own up to it.

That is the pattern I have settled on. GLM 5.2 for implementation and for surfacing issues and regressions, Claude for the polish pass and validation. None of them is the whole answer. Together they cover the range of work a real project requires.

How I Actually Use Them Together

The practical workflow looks like this:

Start with GLM 5.2 for the implementation. GLM 5.2 produces reviewable, testable code that holds up under scrutiny.
Run GLM 5.2 again to catch lingering issues and regressions it missed on the first pass, but validate every finding through Claude before acting on it.
Run a polish pass with Claude on anything a user will see. Let Claude fix the copy, the spacing, and the empty states.
Validate the polished result against the original implementation. If Claude changed the logic, that is a signal to review, not accept.
For the riskiest changes, keep GLM 5.2 on a tight leash: review the diff carefully and lean on Claude Sonnet 5.0 or Fable 5 to validate the logic before accepting.

The point is not to pick a winner. The point is to use each tool for what it is actually good at and to stop expecting any single model to be uniformly best across code, design, and long-running reliability.

Going Forward: GLM and Claude Side by Side, Split by Criticality

The rate limits are the real constraint, and the workaround I have settled on is not a rotation but a split. The perfect model for me in the last few days is to run GLM 5.2 with an Ollama monthly plan in one window, working on a medium-criticality project, and Claude with Sonnet 5.0 in another window, working on a critical project. Both tools stay busy at the same time, and because the work is spread across two providers, I can get through a significant amount of work without triggering either one's limits.

GLM 5.2 handles the medium-criticality work where its thoroughness at catching regressions and missing test coverage is an asset and its false positive rate is tolerable. Claude with Sonnet 5.0 handles the critical work where I need the most certain diagnoses and the cleanest fixes. The two windows do not compete for the same quota, so neither one becomes the bottleneck for the other.

Claude is also on a monthly plan right now, but I am strongly considering moving it to an annual plan. The split setup has made Claude the tool I lean on for the work that matters most, and locking in the annual plan would remove the month-to-month friction around renewal and pricing. GLM 5.2 stays on the Ollama monthly plan for now, since it is the newer tool in my workflow and I want to keep the option to adjust as it matures.

Final Thought: Two $20 Plans Is Enough

As of today, here is where I have landed. I started on Codex, switched completely to Claude, and iterated from Sonnet 4.6 to 4.8 to finally 5. I was disappointed with 4.8; it did not feel like a clear step forward over 4.6, and in some ways it felt like a step sideways. That disappointment is what pushed me to look harder at the other tools. I finally settled on Sonnet 5 for daily routine work and Fable 5 for codebase analysis and validation. GLM 5.2 complements Fable 5 nicely on a less user-facing project I am simultaneously working on: GLM 5.2 surfaces the issues and does the implementation, and Fable 5 validates the codebase-level concerns that are hard to see from a single diff.

My current thinking is that I will settle on two $20-a-month plans, one for Claude and one for Ollama. That is enough tokens to cover the day-to-day for a part-time hobbyist. I am not running any single tool all day, and the split across two providers means I am not paying for capacity I do not use. Two $20 plans, two tools, two windows, and the work gets done.

References

My Thoughts on AI: Week of June 12

noreply@blogger.com (Artem Ervits) — Fri, 12 Jun 2026 16:51:01 +0000

I am not trying to crown one AI tool as the winner forever. These tools change too fast for that. What matters to me is more practical: when I sit down to do real work, which one helps me keep going, make fewer messy mistakes, and understand what happened when the session is over?

Codex and Claude Code both do serious coding work. They can read files, make changes, run commands, and help move a project forward. But they feel different. Codex feels more like the tool I use when I need to finish implementation. Claude feels stronger when the work needs taste, presentation, and design sense.

This is also just where I am right now. My opinion is based on GPT-5.5 in Codex and Opus 4.8 in Claude Code. Fable 5 also came out this week, on June 9, 2026, so even while I am writing this, the ground is already moving.

Codex Feels Better When the Job Has a Clear Finish Line

Codex works best for me when the task is concrete. Read the repo. Follow the project rules. Make the change. Run the check. Tell me what passed and what failed.

For that kind of work, Codex feels steady. But that same directness can feel too plain when the task needs design judgment. It can solve the ticket, but not always understand the vibe.

Claude Code Feels Better With Presentation and Design

I am seeing this right now while working on my wife's portfolio website. I am not really a designer type of person. She is a fashion designer, not a techie, so she looks at the result differently than I do.

When she looked at Codex's design work, she felt it was lazy and hodgepodge. It did not follow her comments closely enough, and it made up some design choices that did not match what she asked for. Claude's results felt more polished. It followed her design cues much closer. It did not feel stubborn or lazy in the same way.

Plans, Limits, and Model Releases May Change My Stack

The pricing and limits also matter. I am thinking about downgrading Codex Pro and adding Claude Pro, because I may not need one tool to do everything. I might switch back and forth depending on what OpenAI and Anthropic release next.

Fable 5 is part of that thought process. It came out the same week I was writing this, and I burned through my allotted amount in about 30 minutes. With Opus on the $20 Claude Pro plan, I have been able to finish some milestones in one sitting without hitting rate limits.

Codex limits have felt less aggressive to me, even though it is not the same tier as Claude Pro. I only ran out of Codex tokens once, and that was on a day when I had three implementations running at the same time throughout the day.

Where I Land Right Now

I do not think the answer is "Codex forever" or "Claude forever." For implementation-heavy work, Codex is still my default. For design-sensitive work, Claude is making a strong case.

My subscription mix may change as the models change. I may flip-flop. That is fine. The real lesson for me is to use the right tool for the right kind of work, instead of forcing one assistant to be good at everything.

References

My Experience Writing Zone 2 Trainer for Android

noreply@blogger.com (Artem Ervits) — Sat, 30 May 2026 19:55:06 +0000

Zone 2 Trainer started as a simple idea: I wanted a lightweight way to stay honest about easy aerobic work without committing to another subscription or buying another dedicated device. The app became both a fitness tool and a test case for how far modern AI coding assistants can carry a non-mobile developer.

For the last couple of months, I have been developing an Android application to enhance my endurance and recovery. Most applications on the market require an active subscription and a special device. With the current developments in AI-assisted software development, I was able to step out of my comfort zone and build an application from scratch using Android Studio, Antigravity, and Codex.

That matters to me because I am not a developer by trade. I am closer to a tinkerer who is comfortable with scripts, databases, and infrastructure than someone who spends every day building polished mobile apps. Zone 2 Trainer became a useful test of how far AI coding tools can take someone like me when the goal is a real app, not a weekend demo.

Starting With Android Studio and Gemini

Android development has come a long way since I first dabbled with it. I began with Android Studio because it was the environment I already knew. The built-in chat interface gave me enough help to write a first version with a functional, if rough, interface in a couple of days.

That first phase was all about getting the app to exist. Gemini was helpful for moving quickly through unfamiliar Android patterns, but the output still needed a lot of shaping. The app worked, but it had the feel of a prototype.

Adding Polish With Antigravity

The second phase was a mix of Android Studio and Antigravity. This is where the app started to feel more professional. The built-in skills from Antigravity were especially useful for visual polish and user experience work.

At this point, the difference was not just that the app looked better. The workflow changed. I could describe the direction I wanted, review the result, and keep tightening the interface without getting stuck on every small Android implementation detail.

Stabilizing With Codex

The third phase was stability and acceleration. This is where I added Codex to the mix and stabilized the app end to end. I improved the test coverage, chased down rough edges, and built a stronger foundation for future enhancements.

Gemini was useful, but the limits were painful. The Gemini Pro plan had strict limits, and the most time-consuming part of the work became waiting between rate-limit windows before I could finish a feature. That was extremely limiting.

I first used Codex through my ChatGPT Plus subscription. It helped with memory leaks and race conditions, but it still required a lot of waiting between sessions. After upgrading to the Pro tier, I was able to complete an entire feature set for a given session in one go. That changed the way I worked.

The biggest difference was trust. Codex understood the nuance of my prompts better than Gemini did during this project, and the code quality improved. My Crashlytics reports stopped firing alerts about race conditions and crashes after I adopted Codex as the main coding assistant.

What I Learned

The lifecycle of the app ended up falling into three phases:

Android Studio with Gemini for the first functional version.
Android Studio with Antigravity for polish and product feel.
Codex for stability, test coverage, and end-to-end implementation.

This work predates my testing of Gemini 3.5, and most of Zone 2 Trainer was built using Gemini 3.1. I still plan to run 3.5 through its paces. But for this project, Codex became the tool that helped me finish the work instead of only starting it.

The larger lesson is that AI coding tools are not magic, but they are changing who can build useful software. I still had to make decisions, test the app, and care about the details. The difference is that I was able to stay in the work long enough to turn an idea into something real.

References

My Thoughts on AI: Week of May 25

noreply@blogger.com (Artem Ervits) — Sat, 30 May 2026 15:04:57 +0000

The AI product climate this week was weirdly clarifying. I am still not ready to move my development workflow back to Gemini after the rate-limit frustration, but I also have to admit that Google shipped a few things that made me pay attention again.

At the same time, OpenAI reminded me that even the best developer tooling can feel unnecessarily confusing when the billing model sprawls across too many surfaces. Then OpenRouter raised a serious round of funding and made the case for a future where developers do not have to bet everything on one model vendor.

That is the pattern I keep coming back to: the tools are getting better, but the surrounding product experience still matters just as much as the model.

Gemini Is Back in My General-Purpose Rotation

I have complained plenty about Gemini rate limits. The stricter enforcement hit the wrong nerve for me because it made the product feel unpredictable at exactly the moment I wanted to rely on it more. Once a tool makes me ration my thinking, I start looking for a different tool.

That is a big reason I moved my development work to Codex. For coding, I want a tool that can stay with the repo, preserve the thread of the work, and keep going through implementation and validation. Gemini no longer owns that lane for me.

But credit where it is due: the new Flash model feels much better than I expected. Google says Gemini 3.5 Flash is now the default model in the Gemini app and AI Mode in Search, and it is clearly aiming for the everyday assistant lane rather than only the coding assistant lane.

Daily Brief is the more interesting shift for me. The feature points Gemini toward the role I actually want it to play now: less pair programmer, more morning context engine. I do not need every AI tool to be my IDE co-pilot. Some of them are more useful when they help me understand the day, triage the noise, and surface what is worth paying attention to.

So Gemini is not out. It is just moving to a different job in my stack.

OpenAI Billing Still Feels Too Fragmented

OpenAI has become the center of gravity for my development workflow, but the billing experience still feels harder than it needs to be.

Part of the confusion is structural. OpenAI documents ChatGPT and the API platform as separate billing systems, and the API is billed separately from ChatGPT subscriptions. That may make perfect sense from an internal product architecture point of view. From a customer point of view, it can feel like three different doors into the same building, each with its own card on file.

That is where my frustration comes from. I have a monthly subscription for the tools I use directly. Then there is API billing, which is a separate developer platform. Then there are pay-as-you-go and authorization flows that can look like new charges if you are not reading the fine print closely enough.

OpenAI's help docs do explain pieces of this: API billing is separate from ChatGPT plans, API usage can be managed through the platform billing page, and a temporary card authorization can appear when adding an API payment method. The problem is that customers should not have to assemble the mental model from several help articles after a charge shows up.

My recommendation is boring, which is usually a good sign: fold the product billing experience into one clear account view. Show ChatGPT subscriptions, API usage, pay-as-you-go setup, authorizations, limits, and receipts in one place. Let customers drill into details, but do not make them guess which product surface generated which charge.

This is not just a billing complaint. It is a trust issue. If I cannot quickly understand what I am paying for, the product starts to feel more expensive than the line item says.

Codex Remote Connections Have the Promise and the Friction

Codex is still where I want to do development work. It fits the way I think about software: read the repo, make a focused change, run the checks, explain the proof.

The remote connection story is promising, but it still feels like a developing feature. When a session loses connection or hides too much of the build context, the user has to reconstruct where the work actually is. That is the exact kind of friction an agentic coding tool should remove.

The core product direction is right. The missing piece is continuity. I want the remote environment to make the development cycle more legible, not less: current branch, current diff, running commands, failed checks, last successful proof, and what remains. When that context is visible, the user can supervise the work instead of babysitting the connection.

That is the difference between an impressive demo and a tool I trust in a real project.

OpenRouter Looks More Important Than Ever

OpenRouter's latest funding round caught my attention because it validates a pattern I already like: one API surface, many model choices, and the ability to route work based on cost, quality, latency, or availability.

The company announced a $113 million Series B led by CapitalG, with a long list of infrastructure and enterprise investors. OpenRouter also said its weekly volume grew from 5 trillion to 25 trillion tokens over the last six months. Those numbers matter less to me as bragging rights and more as a signal that multi-model infrastructure is becoming a serious layer in the stack.

I used OpenRouter while testing Zone 2 Trainer, and the appeal was immediate. Instead of wiring every provider separately, you get a standard API shape and a lot more flexibility. That does not remove the need to understand model differences, but it lowers the switching cost.

That flexibility is going to matter more as model providers keep changing prices, limits, product names, and access tiers. If the frontier keeps moving this fast, the winning developer experience may be less about picking the one perfect model and more about keeping optionality.

Where I Landed This Week

This week did not make me less committed to Codex for development. If anything, it clarified why I moved there.

But it did make me less dismissive of Gemini. Flash and Daily Brief make Gemini feel useful again as a daily assistant, even if I do not want it driving my repos. It also made me more convinced that OpenAI needs to simplify its billing story before the confusion becomes part of the brand. And OpenRouter continues to look like one of the more practical bets in AI infrastructure.

So my stack is settling into something more nuanced:

Codex for development.
Gemini for daily context and general-purpose assistance.
OpenAI API when I need direct product integration.
OpenRouter when flexibility and model choice matter.

That feels healthier than trying to make one AI product do every job. The more these tools mature, the more I want each one to be clear about what it is best at.

References

Accelerating PostgreSQL with ReadySet Caching

noreply@blogger.com (Artem Ervits) — Tue, 27 Jan 2026 21:20:00 +0000

Disclaimer: The opinions expressed in this article are my own and do not necessarily reflect the views of my employer.

This tutorial demonstrates how to use ReadySet as a caching layer for PostgreSQL databases. ReadySet is a wire-compatible caching solution that sits between your application and database, providing sub-millisecond query performance without requiring code changes.

Why Cache PostgreSQL?

PostgreSQL is the world's most advanced open-source relational database, widely used for transactional workloads. Whether you're running PostgreSQL on-premises, in the cloud, or using a managed service, every query incurs:

Network round-trip latency – Depending on region and network conditions (e.g., 14ms from a client in New Jersey to a cluster in AWS us-east-1)
Query processing overhead – Parsing, planning, and execution on the database server
Result serialization – Packaging and transmitting results back to your application

For read-heavy workloads with repetitive queries (dashboards, APIs, reports), this latency adds up quickly—especially when the same query is executed hundreds or thousands of times per minute.

What is ReadySet?

ReadySet is a SQL caching engine that sits between your application and database. It's wire-compatible with PostgreSQL and MySQL, meaning your application connects to ReadySet exactly like it would connect to your database—no code changes required.

ReadySet Cloud vs. Self-Hosted

ReadySet is available in several deployment options:

Option	Description
ReadySet Cloud	Fully managed service with automatic scaling, monitoring, and maintenance. Ideal for production workloads.
Self-Hosted	Deploy ReadySet yourself using Docker or binaries. Full control over infrastructure.

Note for this tutorial: We use a self-hosted ReadySet instance to demonstrate the setup process. For production workloads, consider using ReadySet Cloud for automatic scaling and maintenance.

How ReadySet Works

┌─────────────┐         ┌─────────────┐         ┌──────────────┐
│ Application │────────▶│  ReadySet   │────────▶│  PostgreSQL  │
│             │  :5433  │   (Cache)   │  :5432  │  (Upstream)  │
└─────────────┘         └─────────────┘         └──────────────┘
                              │
                              ▼
                     ┌─────────────────┐
                     │ In-Memory Cache │
                     │ (Dataflow Graph)│
                     └─────────────────┘

Transparent Proxy – ReadySet intercepts SQL queries from your application
Selective Caching – You explicitly choose which queries to cache using CREATE CACHE commands
Incremental Maintenance – For deep cache mode, ReadySet subscribes to PostgreSQL's replication stream and automatically updates cached results when underlying data changes
Cache Hits – Cached queries are served directly from ReadySet's in-memory dataflow graph, bypassing the upstream database entirely

Key Differentiators

Feature	Traditional Cache (Redis/Memcached)	ReadySet
Query language	Key-value API	Standard SQL
Cache invalidation	Manual (application logic)	Automatic (via replication)
Code changes required	Yes (caching logic)	No (same connection string)
Result freshness	Depends on TTL/manual invalidation	Real-time (deep cache) or TTL-based (shallow cache)

Why Pair PostgreSQL with ReadySet?

The combination addresses a specific architectural challenge: you want the convenience of a managed PostgreSQL service, but you can't tolerate the inherent latency of a cloud-hosted database for high-frequency reads.

Latency Impact

Scenario	Typical Latency	Use Case
Direct to PostgreSQL	14-20ms	Acceptable for occasional queries, batch jobs
Via ReadySet (uncached)	15-21ms	Passthrough with minimal overhead
Via ReadySet (cached)	1-5ms	Dashboards, APIs, real-time apps

When This Pairing Makes Sense

Good Fit

Read-heavy workloads (80%+ reads)
Repetitive queries (same query patterns executed frequently)
Latency-sensitive applications (dashboards, APIs, search)
Teams wanting to reduce database load and improve response times

Not Ideal For

Write-heavy workloads (ReadySet caches reads, not writes)
Highly dynamic queries with unique parameters each time

Prerequisites

Before starting, ensure you have:

Docker installed and running
A PostgreSQL database with customers, orders, and products tables
Logical replication enabled for deep cache
Database credentials with appropriate permissions

Enabling Logical Replication on PostgreSQL

For deep cache mode, ReadySet requires logical replication to be enabled on your PostgreSQL database. This allows ReadySet to subscribe to the Write-Ahead Log (WAL) and automatically update cached query results when data changes.

Step 1: Check Current Replication Setting

Connect to your PostgreSQL database and run:

SHOW wal_level;

If the result is replica or minimal, you need to change it to logical.

Step 2: Enable Logical Replication

Modify the wal_level parameter:

ALTER SYSTEM SET wal_level = 'logical';

Note: This change requires a database restart to take effect. In managed environments, this may be handled automatically or require a support request depending on your configuration.

Step 3: Verify the Change

After the restart, verify logical replication is enabled:

SHOW wal_level;

Expected output:

 wal_level
-----------
 logical
(1 row)

Required Database Permissions

The database user connecting to ReadySet needs the following permissions:

Permission	Purpose
`SELECT` on cached tables	Read data for snapshotting and query execution
`REPLICATION`	Subscribe to the logical replication stream (deep cache)
`CREATE` on the database	Create the replication slot (or use `--disable-create-publication` if manually created)

If you cannot grant REPLICATION privileges (common in managed environments), you can still use shallow cache mode, which doesn't require logical replication.

Shallow Cache Alternative: If enabling logical replication isn't possible, skip to the Shallow Cache Setup section, which uses TTL-based caching instead of replication.

Sample Schema

This tutorial uses the following sample tables. Create these in your PostgreSQL database before starting:

-- Customers table
CREATE TABLE public.customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Products table
CREATE TABLE public.products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

-- Orders table
CREATE TABLE public.orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES public.customers(customer_id),
    product_id INTEGER REFERENCES public.products(product_id),
    quantity INTEGER NOT NULL,
    order_date DATE DEFAULT CURRENT_DATE
);

-- Sample data: Customers
INSERT INTO public.customers (first_name, last_name, email) VALUES
    ('Alice', 'Smith', 'alice@example.com'),
    ('Bob', 'Johnson', 'bob@example.com'),
    ('Charlie', 'Brown', 'charlie@example.com'),
    ('Diana', 'Prince', 'diana@example.com'),
    ('Evan', 'Wright', 'evan@example.com'),
    ('Fiona', 'Gallagher', 'fiona@example.com'),
    ('George', 'Martin', 'george@example.com'),
    ('Hannah', 'Montana', 'hannah@example.com');

-- Sample data: Products
INSERT INTO public.products (product_name, category, price) VALUES
    ('Wireless Mouse', 'Electronics', 25.99),
    ('Gaming Monitor', 'Electronics', 300.00),
    ('Novel: The Great Gatsby', 'Books', 12.50),
    ('Mechanical Keyboard', 'Electronics', 120.50),
    ('Running Shoes', 'Footwear', 89.99),
    ('Coffee Maker', 'Kitchen', 45.00),
    ('Yoga Mat', 'Fitness', 20.00),
    ('Leather Jacket', 'Apparel', 150.00);

-- Sample data: Orders
INSERT INTO public.orders (customer_id, product_id, quantity, order_date) VALUES
    (1, 1, 1, '2023-10-01'),
    (2, 2, 1, '2023-10-02'),
    (1, 3, 2, '2023-10-03'),
    (3, 4, 1, '2023-10-04'),
    (4, 5, 1, '2023-10-05'),
    (5, 6, 1, '2023-10-06'),
    (2, 4, 1, '2023-10-07'),
    (6, 7, 3, '2023-10-08'),
    (7, 3, 1, '2023-10-09'),
    (8, 8, 1, '2023-10-10');

Baseline Performance (Direct to PostgreSQL)

Before implementing ReadySet, let's establish baseline query performance by connecting directly to PostgreSQL. This helps us measure the impact of caching.

Enable timing in psql: To see query execution times, enable timing in your psql session:

\timing on

This displays the elapsed time after each query, which we'll use throughout this tutorial to measure performance.

Query 1: Revenue by Product Category

SELECT
    p.category,
    SUM(o.quantity) as items_sold,
    SUM(p.price * o.quantity) as revenue
FROM orders o
JOIN products p ON o.product_id = p.product_id
GROUP BY p.category
ORDER BY revenue DESC;

Result:

  category   | items_sold | revenue
-------------+------------+---------
 Electronics |          4 |  566.99
 Apparel     |          1 |  150.00
 Footwear    |          1 |   89.99
 Fitness     |          3 |   60.00
 Kitchen     |          1 |   45.00
 Books       |          3 |   37.50
(6 rows)

Time: 19.402 ms

Query 2: Top Customers by Spending

SELECT
    c.first_name,
    c.last_name,
    COUNT(o.order_id) as number_of_orders,
    SUM(p.price * o.quantity) as total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN products p ON o.product_id = p.product_id
GROUP BY c.first_name, c.last_name
ORDER BY total_spent DESC
LIMIT 3;

Result:

 first_name | last_name | number_of_orders | total_spent
------------+-----------+------------------+-------------
 Bob        | Johnson   |                2 |      420.50
 Hannah     | Montana   |                1 |      150.00
 Charlie    | Brown     |                1 |      120.50
(3 rows)

Time: 14.595 ms

Query 3: Order Details with Customer Names

SELECT
    o.order_id,
    o.order_date,
    c.first_name || ' ' || c.last_name AS customer_name,
    p.product_name,
    p.price,
    o.quantity,
    (p.price * o.quantity) AS total_cost
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;

Result:

 order_id | order_date |  customer_name  |      product_name       | price  | quantity | total_cost
----------+------------+-----------------+-------------------------+--------+----------+------------
       11 | 2023-10-01 | Alice Smith     | Wireless Mouse          |  25.99 |        1 |      25.99
       12 | 2023-10-02 | Bob Johnson     | Gaming Monitor          | 300.00 |        1 |     300.00
       ...
(10 rows)

Time: 16.441 ms

Baseline Summary: Direct queries may take longer depending on network latency and the distance between your client application and the PostgreSQL server. Co-locating the client and database in the same region or availability zone isn't always feasible, making caching an attractive alternative.

Understanding ReadySet Cache Modes

ReadySet supports two caching strategies:

Mode	Description	Use Case
Deep Cache	Uses PostgreSQL logical replication to keep caches in sync in real-time	When you need always-fresh data with automatic invalidation
Shallow Cache	Periodically refreshes cached data based on TTL and refresh intervals	When eventual consistency is acceptable and replication isn't available

Deep Cache Setup

Deep cache leverages PostgreSQL's logical replication stream to automatically update cached query results whenever the underlying data changes.

Step 1: Create a Docker Volume for Cache Persistence

Create a named Docker volume to persist ReadySet's cache data across container restarts:

docker volume create readyset_cache

Step 2: Create a Publication (Manual Step)

If your PostgreSQL environment doesn't provide superuser access, you must manually create a publication for the tables you want to replicate:

-- Create publication for specific tables
CREATE PUBLICATION readyset FOR TABLE public.customers, public.products, public.orders;

Note: This step is required because the --disable-create-publication flag is used, which prevents ReadySet from attempting to create the publication itself (which would fail without superuser privileges).

Step 3: Run ReadySet with Deep Cache Configuration

docker run -d -p 5433:5433 -p 6034:6034         \
--name readyset --rm                            \
-e UPSTREAM_DB_URL=postgres://<username>:<password>@<host>:5432/postgres \
-e REPLICATION_TABLES=public.customers,public.orders,public.products \
-e LISTEN_ADDRESS=0.0.0.0:5433                  \
-e STORAGE_DIR=/data/cache \
-v readyset_cache:/data/cache \
readysettech/readyset:latest \
  --verify-skip \
  --disable-upstream-ssl-verification \
  --disable-create-publication \
  --disable-telemetry

Docker Command Parameters Explained

Port Mappings (`-p`)

Parameter	Description
`-p 5433:5433`	Maps the ReadySet PostgreSQL-compatible port. Your application connects here instead of directly to your database
`-p 6034:6034`	Maps the ReadySet metrics and health check HTTP endpoint

Container Options

Parameter	Description
`-d`	Runs the container in detached (background) mode
`--name readyset`	Assigns a friendly name to the container for easier management
`--rm`	Automatically removes the container when it stops (useful for development)

Environment Variables (`-e`)

Variable	Description
`UPSTREAM_DB_URL`	The full PostgreSQL connection string to your database. Format: `postgres://user:password@host:port/database`
`REPLICATION_TABLES`	Comma-separated list of tables to replicate (schema.table format). ReadySet will subscribe to changes on these tables
`LISTEN_ADDRESS`	The address and port ReadySet listens on for incoming connections. `0.0.0.0:5433` allows connections from any network interface
`STORAGE_DIR`	Directory path inside the container where ReadySet stores persistent cache data

Volume Mount (`-v`)

Parameter	Description
`-v readyset_cache:/data/cache`	Creates a Docker named volume `readyset_cache` and mounts it to `/data/cache` inside the container. This persists cache data across container restarts

ReadySet Flags (Command Arguments)

Flag	Description
`--verify-skip`	Skips verification of cached query results against the upstream database. Improves performance but reduces safety checks
`--disable-upstream-ssl-verification`	Disables SSL certificate verification when connecting to the upstream database. Required when the upstream uses self-signed certificates
`--disable-create-publication`	Prevents ReadySet from attempting to create a PostgreSQL publication. Use this when you don't have superuser privileges and have manually created the publication
`--disable-telemetry`	Disables sending anonymous usage statistics to ReadySet

Step 4: Monitor Snapshot Progress

After starting ReadySet, monitor the Docker logs to verify that the snapshot completes successfully:

docker logs readyset --follow

Look for these INFO messages indicating successful table snapshotting:

2026-01-27T16:15:09.210564Z  INFO replicators::postgres_connector::snapshot: Snapshotting tables tables=3 max_parallel_snapshot_tables=7
2026-01-27T16:15:09.210589Z  INFO Snapshotting table{table="public"."customers"}: replicators::postgres_connector::snapshot: Snapshotting table
2026-01-27T16:15:09.222170Z  INFO Snapshotting table{table="public"."orders"}: replicators::postgres_connector::snapshot: Snapshotting table
2026-01-27T16:15:09.229690Z  INFO Snapshotting table{table="public"."products"}: replicators::postgres_connector::snapshot: Snapshotting table
...
2026-01-27T16:15:09.536299Z  INFO replicators::noria_adapter: Snapshot finished
2026-01-27T16:15:09.574068Z  INFO replicators::noria_adapter: Streaming replication started position=(0/D014690, 0/D014690)

Important: Wait for the "Snapshot finished" and "Streaming replication started" messages before creating caches. This ensures ReadySet has a complete copy of your data.

Shallow Cache Setup

Shallow cache is ideal when logical replication isn't available or when eventual consistency is acceptable. It uses TTL-based caching with periodic refreshes.

Switching from Deep Cache? If you previously ran ReadySet in deep cache mode, stop the existing container first:

docker stop readyset

Step 1: Create a Docker Volume for Cache Persistence

If you haven't already created the volume (from the deep cache setup), create it now:

docker volume create readyset_cache

Step 2: Run ReadySet with Shallow Cache Configuration

docker run -d -p 5433:5433 -p 6034:6034         \
--name readyset --rm                            \
-e UPSTREAM_DB_URL=postgres://<username>:<password>@<host>:5432/postgres \
-e LISTEN_ADDRESS=0.0.0.0:5433                  \
-e STORAGE_DIR=/data/cache \
-v readyset_cache:/data/cache \
readysettech/readyset:latest \
  --verify-skip \
  --disable-upstream-ssl-verification \
  --replication-tables-ignore '*.*' \
  --disable-telemetry

Additional Shallow Cache Parameters

Flag	Description
`--replication-tables-ignore '.'`	Ignores all tables for replication. This effectively disables deep cache mode and enables shallow caching only

Note: The REPLICATION_TABLES environment variable is not set in shallow cache mode since we're ignoring all tables for replication.

Creating Cached Queries

Step 1: Connect to ReadySet

Once ReadySet is running, connect to it using any PostgreSQL client:

psql 'postgresql://<username>:<password>@127.0.0.1:5433/postgres'

Important: Use the same username and password as your upstream PostgreSQL database. ReadySet authenticates against the upstream database using these credentials.

Note: The connection string uses 127.0.0.1:5433 (ReadySet) instead of your PostgreSQL host. ReadySet proxies your queries to the upstream database while caching results.

Step 2: Verify ReadySet Status

Before creating caches, verify that ReadySet is connected and ready:

SHOW READYSET STATUS;

Expected output:

            name            |          value
----------------------------+--------------------------
 Database Connection        | Connected
 Connection Count           | 1
 Status                     | Online
 Maximum Replication Offset | (0/B3021CF8, 0/B3021D28)
 Minimum Replication Offset | (0/B3021CF8, 0/B3021D28)
 Last started Controller    | 2026-01-27 14:37:48 UTC
 Last completed snapshot    | 2026-01-27 14:37:51 UTC
 Last started replication   | 2026-01-27 14:37:51 UTC
 Enabled Features           | None
(9 rows)

Verify there are no caches yet:

SHOW CACHES;

 query id | cache name | query text | properties | count
----------+------------+------------+------------+-------
(0 rows)

Step 3: Create Your Cached Queries

Deep Cache Queries (CREATE DEEP CACHE ALWAYS)

Deep cache queries use CREATE DEEP CACHE ALWAYS and are automatically kept in sync via replication:

Query 1: Revenue by Product Category

CREATE DEEP CACHE ALWAYS q1 FROM SELECT
    p.category,
    SUM(o.quantity) as items_sold,
    SUM(p.price * o.quantity) as revenue
FROM orders o
JOIN products p ON o.product_id = p.product_id
GROUP BY p.category
ORDER BY revenue DESC;

Query 2: Top Customers by Spending

CREATE DEEP CACHE ALWAYS q2 FROM SELECT
    c.first_name,
    c.last_name,
    COUNT(o.order_id) as number_of_orders,
    SUM(p.price * o.quantity) as total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN products p ON o.product_id = p.product_id
GROUP BY c.first_name, c.last_name
ORDER BY total_spent DESC
LIMIT 3;

Query 3: Order Details with Customer Names

CREATE DEEP CACHE ALWAYS q3 FROM SELECT
    o.order_id,
    o.order_date,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    p.product_name,
    p.price,
    o.quantity,
    (p.price * o.quantity) AS total_cost
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;

Shallow Cache Queries (CREATE SHALLOW CACHE)

Shallow cache queries include TTL and refresh policies:

CREATE SHALLOW CACHE POLICY TTL 3600 SECONDS REFRESH EVERY 600 SECONDS
shallow_cache_q1 FROM SELECT
    p.category,
    SUM(o.quantity) as items_sold,
    SUM(p.price * o.quantity) as revenue
FROM orders o
JOIN products p ON o.product_id = p.product_id
GROUP BY p.category
ORDER BY revenue DESC;

Shallow Cache Policy Parameters

Parameter	Description
`TTL 3600 SECONDS`	Time-to-live: cached results expire after 3600 seconds (1 hour)
`REFRESH EVERY 600 SECONDS`	ReadySet proactively refreshes the cache every 600 seconds (10 minutes)

Additional Shallow Cache Examples

-- Top customers with shallow caching
CREATE SHALLOW CACHE POLICY TTL 3600 SECONDS REFRESH EVERY 600 SECONDS
shallow_cache_q2 FROM SELECT
    c.first_name,
    c.last_name,
    COUNT(o.order_id) as number_of_orders,
    SUM(p.price * o.quantity) as total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN products p ON o.product_id = p.product_id
GROUP BY c.first_name, c.last_name
ORDER BY total_spent DESC
LIMIT 3;

-- Order details with shallow caching
CREATE SHALLOW CACHE POLICY TTL 3600 SECONDS REFRESH EVERY 600 SECONDS
shallow_cache_q3 FROM SELECT
    o.order_id,
    o.order_date,
    c.first_name || ' ' || c.last_name AS customer_name,
    p.product_name,
    p.price,
    o.quantity,
    (p.price * o.quantity) AS total_cost
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;

Note: The || string concatenation operator is supported in shallow cache mode but not in deep cache mode (see Troubleshooting).

Step 4: Verify Queries Are Being Cached

After creating caches, confirm that your queries are hitting the cache instead of the upstream database.

Check Cache Status

SHOW CACHES;

The count column shows how many times each cache has been hit:

      query id      | cache name |  query text (truncated)  | properties | count
--------------------+------------+--------------------------+------------+-------
 q_663bfa3e73146832 | q1         | SELECT "p"."category"... | always     |     0
 q_272f720b813f03ee | q2         | SELECT "c"."first_name"..| always     |     0
 q_a1b2c3d4e5f67890 | q3         | SELECT "o"."order_id"... | always     |     0

Note: Immediately after creating caches, the count will be 0. Run your cached queries a few times, then check SHOW CACHES again to see the hit count increase.

View All Proxied Queries

To see which queries are being cached vs. proxied to upstream:

SHOW PROXIED QUERIES;

This displays all queries ReadySet has seen, their cache status, and whether they're supported for caching.

Confirm Cache Hit with Timing

The easiest way to verify caching is working: run the same query twice and compare times.

-- First run (may be uncached): ~15-20ms
SELECT p.category, SUM(o.quantity) as items_sold ...

-- Second run (cached): ~1-5ms
SELECT p.category, SUM(o.quantity) as items_sold ...

If the second query is significantly faster, it's being served from the cache.

Verifying Automatic Cache Invalidation (Deep Cache)

Deep cache automatically updates when underlying data changes. Let's demonstrate this with an INSERT.

Step 1: Query the Current Cache

First, run the revenue by category query and note the results:

SELECT
    p.category,
    SUM(o.quantity) as items_sold,
    SUM(p.price * o.quantity) as revenue
FROM orders o
JOIN products p ON o.product_id = p.product_id
GROUP BY p.category
ORDER BY revenue DESC;

Result:

  category   | items_sold | revenue
-------------+------------+---------
 Electronics |          4 |  566.99
 Apparel     |          1 |     150
 Footwear    |          1 |   89.99
 Fitness     |          3 |   60.00
 Kitchen     |          1 |      45
 Books       |          3 |   37.50
(6 rows)

Time: 4.344 ms

Step 2: Insert New Data

Add a new order for an Electronics product:

INSERT INTO orders (customer_id, product_id, quantity, order_date)
VALUES (1, 2, 2, '2024-01-15');

Step 3: Verify Cache Updated Automatically

Re-run the same query—the cache should reflect the new data without any manual invalidation:

SELECT
    p.category,
    SUM(o.quantity) as items_sold,
    SUM(p.price * o.quantity) as revenue
FROM orders o
JOIN products p ON o.product_id = p.product_id
GROUP BY p.category
ORDER BY revenue DESC;

Result:

  category   | items_sold | revenue
-------------+------------+---------
 Electronics |          6 |  807.99
 Apparel     |          1 |     150
 Footwear    |          1 |   89.99
 Fitness     |          3 |   60.00
 Kitchen     |          1 |      45
 Books       |          3 |   37.50
(6 rows)

Time: 4.648 ms

How it works: ReadySet subscribes to PostgreSQL's replication stream. When you INSERT/UPDATE/DELETE data, the change flows through ReadySet's dataflow graph, automatically updating all affected cached query results in real-time.

Performance Impact & Latency Analysis

As discussed in Why Cache PostgreSQL?, databases incur network latency per query due to network round-trips and processing overhead. ReadySet eliminates most of these costs by serving pre-computed results from local memory.

A Note on Data Size

The performance results in this tutorial use a small sample dataset (10 orders, 8 customers, 8 products). With small datasets, the improvements may seem modest—but this actually understates ReadySet's value in production:

Factor	Small Dataset	Large Dataset (millions of rows)
Upstream query time	Fast (data fits in memory)	Slower (disk I/O, larger joins, more aggregation work)
Cached query time	~1-5ms	~1-5ms (constant)
Relative improvement	3-12x	10-100x or more

Key insight: As your data grows, upstream query latency increases due to larger table scans, more complex joins, and heavier aggregation workloads. However, cached query latency remains constant—ReadySet serves pre-computed results regardless of the underlying data size. This means the performance gap between cached and uncached queries widens significantly as your dataset scales.

Deep Cache Performance Results

Query	Direct to PostgreSQL	Via ReadySet (uncached)	Via ReadySet (cached)	Improvement
Q1: Revenue by Category	19.4 ms	18.5 ms	3.7 ms	5.3x faster
Q2: Top Customers	14.6 ms	~15 ms	1.2 ms	12.4x faster
Q3: Order Details	16.4 ms	~17 ms	5.1 ms	3.2x faster

Key Observations

Uncached queries pass through ReadySet to the upstream database with minimal overhead (~1ms)
Cached queries are served directly from ReadySet's in-memory dataflow graph
Complex aggregations (Q2 with COUNT, SUM, GROUP BY, ORDER BY, LIMIT) see the largest improvement
Simple joins (Q3) still benefit significantly but have lower relative improvement

Shallow Cache Performance Results

Query	Direct to PostgreSQL	Via ReadySet (shallow cached)	Improvement
Q1: Revenue by Category	19.4 ms	3.9 ms	5.0x faster
Q2: Top Customers	14.6 ms	4.0 ms	3.6x faster
Q3: Order Details	16.4 ms	4.2 ms	3.9x faster

When to Use Each Cache Mode

Tip: To verify your caches are working, see Step 4: Verify Queries Are Being Cached in the Creating Cached Queries section.

Consideration	Deep Cache	Shallow Cache
Data freshness	Real-time (milliseconds)	Eventual (TTL-based)
Setup complexity	Requires publication + replication	Simpler setup
Best for	OLTP, user-facing apps	Analytics, dashboards, reports
Latency reduction	3-12x improvement	3-5x improvement
Upstream load	Minimal (only replication stream)	Periodic refresh queries

Latency Implications for Production

User Experience

Reducing query latency from 15-20ms to 1-5ms dramatically improves perceived application responsiveness, especially for dashboard loading times, autocomplete/search-as-you-type features, and real-time analytics displays.

Throughput

Lower latency means each connection can handle more queries per second, improving overall system capacity.

Cost Optimization

Fewer queries hitting the upstream database can reduce compute costs.

Consistency Trade-offs

Deep cache stays consistent with upstream through replication. Shallow cache may serve stale data within the TTL window, which is acceptable for many read-heavy workloads.

Comparing Query Execution Plans

To understand query execution behavior, let's compare EXPLAIN ANALYZE output when executed directly on PostgreSQL vs. through ReadySet.

Understanding the Timing Discrepancy: The EXPLAIN ANALYZE output below shows database-side execution time (0.1-0.15ms), while the performance tables earlier show total query time (14-20ms). The difference is network round-trip latency—the time for your query to travel to the database server and the results to return. This is why caching locally with ReadySet provides such dramatic improvements: it eliminates the network hop entirely for cached queries.

Query 1: Revenue by Category (2-table join)

PostgreSQL Direct:

                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=77.67..78.17 rows=200 width=158) (actual time=0.059..0.060 rows=6.00 loops=1)
   Sort Key: (sum((p.price * (o.quantity)::numeric))) DESC
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=2
   ->  HashAggregate  (cost=67.53..70.03 rows=200 width=158) (actual time=0.049..0.053 rows=6.00 loops=1)
         Group Key: p.category
         ->  Hash Join  (cost=14.72..46.28 rows=1700 width=138) (actual time=0.029..0.034 rows=10.00 loops=1)
               Hash Cond: (o.product_id = p.product_id)
               ->  Seq Scan on orders o
               ->  Hash -> Seq Scan on products p
 Planning Time: 0.137 ms
 Execution Time: 0.103 ms
(20 rows)

Via ReadySet (Proxied to Upstream):

                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=77.67..78.17 rows=200 width=158) (actual time=0.057..0.059 rows=6.00 loops=1)
   Sort Key: (sum((p.price * (o.quantity)::numeric))) DESC
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=2
   ->  HashAggregate  (cost=67.53..70.03 rows=200 width=158) (actual time=0.048..0.052 rows=6.00 loops=1)
         Group Key: p.category
         ->  Hash Join  (cost=14.72..46.28 rows=1700 width=138) (actual time=0.028..0.032 rows=10.00 loops=1)
               Hash Cond: (o.product_id = p.product_id)
               ->  Seq Scan on orders o
               ->  Hash -> Seq Scan on products p
 Planning Time: 0.134 ms
 Execution Time: 0.102 ms
(20 rows)

Query 2: Top Customers (3-table join with aggregation)

PostgreSQL Direct:

                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=92.56..92.57 rows=3 width=276) (actual time=0.090..0.092 rows=3.00 loops=1)
   Buffers: shared hit=3
   ->  Sort  (cost=92.56..92.88 rows=130 width=276) (actual time=0.089..0.091 rows=3.00 loops=1)
         Sort Key: (sum((p.price * (o.quantity)::numeric))) DESC
         Sort Method: top-N heapsort  Memory: 25kB
         ->  HashAggregate  (cost=89.25..90.88 rows=130 width=276) (actual time=0.077..0.082 rows=8.00 loops=1)
               Group Key: c.first_name, c.last_name
               ->  Hash Join  (cost=27.65..63.75 rows=1700 width=260) (actual time=0.054..0.061 rows=10.00 loops=1)
                     Hash Cond: (o.product_id = p.product_id)
                     ->  Hash Join  (cost=12.93..44.48 rows=1700 width=248) (actual time=0.028..0.032 rows=10.00 loops=1)
                           Hash Cond: (o.customer_id = c.customer_id)
                           ->  Seq Scan on orders o
                           ->  Hash -> Seq Scan on customers c
                     ->  Hash -> Seq Scan on products p
 Planning Time: 0.189 ms
 Execution Time: 0.149 ms
(30 rows)

Via ReadySet (Proxied to Upstream):

                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=92.56..92.57 rows=3 width=276) (actual time=0.079..0.082 rows=3.00 loops=1)
   Buffers: shared hit=3
   ->  Sort  (cost=92.56..92.88 rows=130 width=276) (actual time=0.078..0.080 rows=3.00 loops=1)
         Sort Key: (sum((p.price * (o.quantity)::numeric))) DESC
         Sort Method: top-N heapsort  Memory: 25kB
         ->  HashAggregate  (cost=89.25..90.88 rows=130 width=276) (actual time=0.067..0.072 rows=8.00 loops=1)
               Group Key: c.first_name, c.last_name
               ->  Hash Join  (cost=27.65..63.75 rows=1700 width=260) (actual time=0.043..0.050 rows=10.00 loops=1)
                     Hash Cond: (o.product_id = p.product_id)
                     ->  Hash Join  (cost=12.93..44.48 rows=1700 width=248) (actual time=0.021..0.026 rows=10.00 loops=1)
                           Hash Cond: (o.customer_id = c.customer_id)
                           ->  Seq Scan on orders o
                           ->  Hash -> Seq Scan on customers c
                     ->  Hash -> Seq Scan on products p
 Planning Time: 0.187 ms
 Execution Time: 0.140 ms
(30 rows)

Execution Time Comparison Summary

Query	Metric	PostgreSQL Direct	Via ReadySet	Improvement
Q1	Planning Time	0.14 ms	0.13 ms	-2%
Q1	Execution Time	0.10 ms	0.10 ms	-1%
Q2	Planning Time	0.19 ms	0.19 ms	-1%
Q2	Execution Time	0.15 ms	0.14 ms	-6%

Key Insights

Same Query Plan Structure: Both executions use identical query plans (Limit → Sort → HashAggregate → Hash Joins → Seq Scans). ReadySet doesn't modify how the upstream database executes queries.
Marginal Improvement on Proxied Queries: Even for uncached queries that pass through to the upstream database, there's a slight performance improvement (~6% faster execution).
The Real Win is Caching: The EXPLAIN output above shows uncached query behavior. For cached queries, ReadySet bypasses this entire execution pipeline and serves pre-computed results directly from memory—reducing latency from 14-20ms to 1-5ms (a 3-12x improvement).

Note: When a query is cached, ReadySet performs a simple key-value lookup in its in-memory dataflow graph rather than executing a traditional query plan against the database.

Troubleshooting

String Concatenation Error in Deep Cache

Problem: Using || for string concatenation in deep cache mode fails:

-- This FAILS in deep cache mode
CREATE DEEP CACHE ALWAYS q3 FROM SELECT
    c.first_name || ' ' || c.last_name AS customer_name
    ...

Error:

ERROR: unknown: Error during RPC (extend_recipe): SQL SELECT query 'q3' couldn't be added:
MIR node '20' couldn't be lowered to dataflow: The provided query is invalid:
cannot invoke '||' on left-side operand type VarChar(50); expected JSONB

Solution: Use the CONCAT() function instead:

-- This WORKS in deep cache mode
CREATE DEEP CACHE ALWAYS q3 FROM SELECT
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name
    ...

Common Issues

Issue	Solution
Cannot connect to ReadySet	Ensure port 5433 is not blocked and the container is running (`docker ps`)
Publication doesn't exist	Manually create the publication with `CREATE PUBLICATION readyset FOR TABLE ...`
SSL verification errors	Use `--disable-upstream-ssl-verification` flag
Cache not updating	For deep cache, verify the publication includes all required tables

Useful Commands

View Cached Queries

SHOW CACHES;

View Query Status

SHOW PROXIED QUERIES;

Drop a Cache

DROP CACHE q1;

Check ReadySet Status

SHOW READYSET STATUS;

Monitor Docker Logs

docker logs readyset --follow

Stop ReadySet

docker stop readyset

Remove Cache Volume (reset all caches)

docker volume rm readyset_cache

Cleanup

To completely remove ReadySet and start fresh:

Step 1: Stop the ReadySet Container

docker stop readyset

Note: If you used the --rm flag when starting the container, it will be automatically removed when stopped.

Step 2: Remove the Docker Volume

docker volume rm readyset_cache

Step 3: Drop the Publication (Deep Cache Only)

If you created a publication for deep cache mode, remove it from your PostgreSQL database:

DROP PUBLICATION IF EXISTS readyset;

Step 4: Verify Cleanup

Confirm everything is removed:

# Check no readyset container exists
docker ps -a | grep readyset

# Check volume is removed
docker volume ls | grep readyset_cache

Acknowledgments

Thanks to the ReadySet team for their support and guidance in getting this tutorial together—their responsiveness and deep product knowledge made the process smooth.

References

ReadySet GitHub Repository
ReadySet Cloud – Fully managed caching service
ReadySet Documentation - Getting Started
PostgreSQL Documentation

Exploring Real-Time Data Ingestion into Snowflake Using CockroachDB, Redpanda, and Kafka Connect

noreply@blogger.com (Artem Ervits) — Mon, 09 Sep 2024 20:27:00 +0000

This article serves as a follow-up to previous discussions on data ingestion into Snowflake, specifically focusing on the complexities and efforts involved in integrating Change Data Capture (CDC) data with Snowflake in real time.

Previous Articles on Snowflake

Previous Articles on CockroachDB CDC

Motivation

This article builds upon the previous discussion in Tour of Snowflake ingestion using CockroachDB and Redpanda Connect, where we investigated the process of streaming changefeeds from CockroachDB to Snowflake using Redpanda Connect and Snowpipe in batch mode. Here, we will shift our focus to Kafka Connect and demonstrate how both batch and streaming modes can be utilized for data ingestion into Snowflake.

Overview

Deploy a CockroachDB cluster with enterprise changefeeds
Deploy Snowflake
Deploy Kafka Connect
Verify
Conclusion

Detailed Instructions

Deploy a CockroachDB Cluster with Enterprise Changefeeds

Start by either launching a CockroachDB instance or utilizing a managed service.

To enable CDC, execute the following commands:

SET CLUSTER SETTING cluster.organization = '<organization name>';
SET CLUSTER SETTING enterprise.license = '<secret>';
SET CLUSTER SETTING kv.rangefeed.enabled = true;

Verify that changefeeds are enabled:

SHOW CLUSTER SETTING kv.rangefeed.enabled;

If the value is false, update it to true.

Create a source table:

CREATE TABLE cockroachdb (
     id INT PRIMARY KEY,
     value STRING DEFAULT md5(random()::text),
     created_at TIMESTAMPTZ DEFAULT now(),
     updated_at TIMESTAMPTZ DEFAULT NULL);

Insert random data:

INSERT INTO cockroachdb SELECT
   (generate_series(1, 10000));

Update a row:

UPDATE cockroachdb SET value = 'UPDATED', updated_at = now() WHERE id = 1;

Create a changefeed job pointing to a local instance of Redpanda:

CREATE CHANGEFEED FOR TABLE cockroachdb INTO 'kafka://redpanda:29092';

Inspect the data:

SELECT * FROM cockroachdb LIMIT 5;

  id |              value               |          created_at           |          updated_at
-----+----------------------------------+-------------------------------+--------------------------------
   1 | UPDATED                          | 2024-09-09 13:17:57.837984+00 | 2024-09-09 13:17:57.917108+00
   2 | 27a41183599c44251506e2971ba78426 | 2024-09-09 13:17:57.837984+00 | NULL
   3 | 3bf8bc26a750a15691ec4d7ddbb7f5e5 | 2024-09-09 13:17:57.837984+00 | NULL
   4 | b8c5786e8651ddfb3a68eabeadb52f2e | 2024-09-09 13:17:57.837984+00 | NULL
   5 | 3a24df165773639ce89d0d877e7103b7 | 2024-09-09 13:17:57.837984+00 | NULL
(5 rows)

The next step is to set up the Snowflake Kafka connector.

Deploy Snowflake

Create a database and schema for outputting changefeed data:

USE ROLE SYSADMIN;
CREATE OR REPLACE DATABASE KAFKADB;
CREATE OR REPLACE SCHEMA kafka_schema;

Follow the Snowflake documentation to configure the Kafka connector.

Create the necessary tables:

CREATE OR REPLACE TABLE kafkatb_batch(
    RECORD_METADATA VARIANT,
    RECORD_CONTENT VARIANT
);

CREATE OR REPLACE TABLE kafkatb_streaming(
    RECORD_METADATA VARIANT,
    RECORD_CONTENT VARIANT
);

Set up roles and permissions:

-- Use a role that can create and manage roles and privileges.
USE ROLE securityadmin;

-- Create a Snowflake role with the privileges to work with the connector.
CREATE OR REPLACE ROLE kafka_connector_role_1;

-- Grant privileges on the database.
GRANT USAGE ON DATABASE kafkadb TO ROLE kafka_connector_role_1;

-- Grant privileges on the schema.
GRANT USAGE ON SCHEMA KAFKADB.kafka_schema TO ROLE kafka_connector_role_1;
GRANT CREATE TABLE ON SCHEMA KAFKADB.kafka_schema TO ROLE kafka_connector_role_1;
GRANT CREATE STAGE ON SCHEMA KAFKADB.kafka_schema TO ROLE kafka_connector_role_1;
GRANT CREATE PIPE ON SCHEMA KAFKADB.kafka_schema TO ROLE kafka_connector_role_1;

-- Only required if the Kafka connector will load data into an existing table.
GRANT OWNERSHIP ON TABLE KAFKADB.KAFKA_SCHEMA.kafkatb_batch TO ROLE kafka_connector_role_1;
GRANT OWNERSHIP ON TABLE KAFKADB.KAFKA_SCHEMA.kafkatb_streaming TO ROLE kafka_connector_role_1;

-- Grant the custom role to an existing user.
GRANT ROLE kafka_connector_role_1 TO USER username;

-- Set the custom role as the default role for the user.
-- If you encounter an 'Insufficient privileges' error, verify the role that has the OWNERSHIP privilege on the user.
ALTER USER username SET DEFAULT_ROLE = kafka_connector_role_1;

Ensure you follow the documentation for setting up key pair authentication for the Snowflake Kafka connector.

Deploy Kafka Connect

Run Redpanda using Docker Compose.

docker compose -f compose-redpandadata.yaml up -d

Once up, navigate to the Redpanda Console.

Click into the cockroachdb topic and install the Snowflake Kafka connector:

confluent-hub install --no-prompt snowflakeinc/snowflake-kafka-connector:latest

Use the following configuration for Kafka Connect in distributed mode, saved as connect-distributed.properties:

bootstrap.servers=172.18.0.3:29092
group.id=connect-cluster
key.converter=org.apache.kafka.connect.json.JsonConverter
value.converter=org.apache.kafka.connect.json.JsonConverter
key.converter.schemas.enable=true
value.converter.schemas.enable=true
offset.storage.topic=connect-offsets
offset.storage.replication.factor=1
config.storage.topic=connect-configs
config.storage.replication.factor=1
status.storage.topic=connect-status
status.storage.replication.factor=1
offset.flush.interval.ms=10000
plugin.path=/usr/share/confluent-hub-components,plugin.path=/usr/local/share/kafka/plugins,/usr/share/filestream-connectors

Deploy Kafka Connect in distributed mode:

./kafka-connect/bin/connect-distributed.sh connect-distributed.properties

{
  "name": "snowflake-sink-batch",
  "config": {
    "connector.class": "com.snowflake.kafka.connector.SnowflakeSinkConnector",
    "tasks.max": "8",
    "topics": "cockroachdb",
    "snowflake.topic2table.map": "cockroachdb:kafkatb_batch",
    "buffer.count.records": "10000",
    "buffer.flush.time": "60",
    "buffer.size.bytes": "5000000",
    "snowflake.url.name": "account-name:443",
    "snowflake.user.name": "username",
    "snowflake.private.key": "private-key",
    "snowflake.private.key.passphrase": "",
    "snowflake.database.name": "kafkadb",
    "snowflake.schema.name": "kafka_schema",
    "snowflake.role.name": "kafka_connector_role_1",
    "key.converter": "org.apache.kafka.connect.storage.StringConverter",
    "value.converter": "com.snowflake.kafka.connector.records.SnowflakeJsonConverter"
  }
}

Publish the connector configuration:

curl -d @"snowflake-sink-batch.json" -H "Content-Type: application/json" -X POST http://kafka-connect:8083/connectors

Verify the connector in the Kafka Connect UI and in the Kafka Connect section of the Redpanda Console.

The comprehensive steps needed to set this up are thoroughly outlined in the tutorial.

Data will now flow into Snowflake in batch mode, with updates occurring every 60 seconds as determined by the buffer.flush.time parameter.

You can now query the data in Snowflake:

SELECT * FROM kafkatb_batch LIMIT 5;

If everything is configured correctly, the data from CockroachDB should be available in Snowflake in real time or in batches, depending on your configuration.

record_metadata:

{
  "CreateTime": 1725887877966,
  "key": "[3]",
  "offset": 30007,
  "partition": 0,
  "topic": "cockroachdb"
}

record_content:

{
  "after": {
    "created_at": "2024-09-09T13:17:57.837984Z",
    "id": 1,
    "updated_at": "2024-09-09T13:17:57.917108Z",
    "value": "UPDATED"
  }
}

The next step is to configure the connector in streaming mode. First, stop the current connector with the following command:

curl -X DELETE http://localhost:8083/connectors/snowflake-sink-batch

The updated connector configuration will appear as follows:

{
  "name": "snowflake-sink-streaming",
  "config": {
    "connector.class": "com.snowflake.kafka.connector.SnowflakeSinkConnector",
    "tasks.max": "8",
    "topics": "cockroachdb",
    "snowflake.topic2table.map": "cockroachdb:kafkatb_streaming",
    "buffer.count.records": "10000",
    "buffer.flush.time": "10",
    "buffer.size.bytes": "5000000",
    "snowflake.url.name": "<snowflake-account>:443",
    "snowflake.user.name": "username",
    "snowflake.private.key": "private-key",
    "snowflake.private.key.passphrase": "",
    "snowflake.database.name": "kafkadb",
    "snowflake.schema.name": "kafka_schema",
    "snowflake.role.name": "kafka_connector_role_1",
    "key.converter": "org.apache.kafka.connect.storage.StringConverter",
    "value.converter": "org.apache.kafka.connect.json.JsonConverter",
    "value.converter.schemas.enable": "false",
    "snowflake.ingestion.method": "SNOWPIPE_STREAMING",
    "errors.log.enable": "true",
    "schemas.enable": "false"
  }
}

Take note of the snowflake.ingestion.method parameter. This feature removes the need to wait 60 seconds to push data to Snowflake, allowing us to reduce the buffer.flush.time to 10 seconds.

To deploy the connector, use the following command:

curl -d @"snowflake-sink-streaming.json" -H "Content-Type: application/json" -X POST http://kafka-connect:8083/connectors

Shortly after deployment, the data will be available in the Snowflake table.

The previous examples demonstrated how data was ingested into predefined Snowflake tables. The following method will automatically infer the schema from the Kafka messages:

{
  "name": "snowflake-sink-streaming-schematized",
  "config": {
    "connector.class": "com.snowflake.kafka.connector.SnowflakeSinkConnector",
    "tasks.max": "8",
    "topics": "cockroachdb",
    "snowflake.topic2table.map": "cockroachdb:kafkatb_streaming_schematized",
    "buffer.count.records": "10000",
    "buffer.flush.time": "10",
    "buffer.size.bytes": "5000000",
    "snowflake.url.name": "<snowflake-account>:443",
    "snowflake.user.name": "username",
    "snowflake.private.key": "private-key",
    "snowflake.private.key.passphrase": "",
    "snowflake.database.name": "kafkadb",
    "snowflake.schema.name": "kafka_schema",
    "snowflake.role.name": "kafka_connector_role_1",
    "key.converter": "org.apache.kafka.connect.storage.StringConverter",
    "value.converter": "org.apache.kafka.connect.json.JsonConverter",
    "value.converter.schemas.enable": "false",
    "snowflake.ingestion.method": "SNOWPIPE_STREAMING",
    "errors.log.enable": "true",
    "schemas.enable": "false",
    "snowflake.enable.schematization": "TRUE"
  }
}

Save this as snowflake-sink-streaming-schematized.json and deploy it using:

curl -d @"snowflake-sink-streaming-schematized.json" -H "Content-Type: application/json" -X POST http://kafka-connect:8083/connectors

Upon deployment, a new table will be created in Snowflake with the following schema:

CREATE OR REPLACE TABLE KAFKADB.KAFKA_SCHEMA.KAFKATB_STREAMING_SCHEMATIZED (
    RECORD_METADATA VARIANT COMMENT 'created by automatic table creation from Snowflake Kafka Connector',
    AFTER VARIANT COMMENT 'column created by schema evolution from Snowflake Kafka Connector'
);

To inspect the table, use the following query:

SELECT after AS record FROM kafkatb_streaming_schematized LIMIT 5;

Sample result:

{
  "created_at": "2024-09-09T16:39:34.993226Z",
  "id": 18712,
  "updated_at": null,
  "value": "0d6bd8a4a790aab95c97a084d17bd820"
}

Verify

We can flatten the data for easier manipulation using the following query:

USE ROLE securityadmin;
GRANT CREATE VIEW ON SCHEMA KAFKADB.kafka_schema TO ROLE kafka_connector_role_1;

USE ROLE kafka_connector_role_1;
USE DATABASE KAFKADB;
USE SCHEMA KAFKA_SCHEMA;
CREATE VIEW v_kafkatb_batch_flattened AS
    SELECT PARSE_JSON(record_content:after):id AS ID,
        PARSE_JSON(record_content:after):value AS VALUE,
        PARSE_JSON(record_content:after):created_at AS CREATED_AT,
        PARSE_JSON(record_content:after):updated_at AS UPDATED_AT
    FROM kafkatb_batch;

SELECT * FROM v_kafkatb_batch_flattened LIMIT 1;

ID      VALUE       CREATED_AT                    UPDATED_AT
1       "UPDATED"   "2024-09-09T13:17:57.837984Z" "2024-09-09T13:17:57.917108Z"

Alternatively, for the schematized table, the view creation statement would be:

CREATE VIEW v_kafkatb_streaming_schematized_flattened AS
    SELECT PARSE_JSON(after):id AS ID,
        PARSE_JSON(after):value AS VALUE,
        PARSE_JSON(after):created_at AS CREATED_AT,
        PARSE_JSON(after):updated_at AS UPDATED_AT
    FROM kafkatb_streaming_schematized;

To verify the data flow, make an update in CockroachDB and check for the changes in Snowflake:

UPDATE cockroachdb
  SET value = 'UPDATED', updated_at = now()
WHERE
  id = 20000;

In Snowflake, execute the following query to confirm the update:

SELECT * FROM v_kafkatb_streaming_schematized_flattened WHERE VALUE = 'UPDATED';

Sample result:

ID      VALUE       CREATED_AT                    UPDATED_AT
20000   "UPDATED"   "2024-09-09T18:15:13.460078Z" "2024-09-09T18:16:56.550778Z"
19999   "UPDATED"   "2024-09-09T18:15:13.460078Z" "2024-09-09T18:15:27.365272Z"

Conclusion

In this process, we explored Kafka Connect as a solution to stream changefeeds into Snowflake. This approach provides greater control over how messages are delivered to Snowflake, leveraging the Snowflake Kafka Connector with Snowpipe Streaming for real-time, reliable data ingestion.

References

Tour of Snowflake ingestion using CockroachDB and Redpanda Connect

noreply@blogger.com (Artem Ervits) — Thu, 15 Aug 2024 14:18:00 +0000

This is an exploratory article delving into various ways to ingest real-time data into Snowflake. The premise of the article is to examine the level of difficulty and effort required to ingest change data capture (CDC) data into Snowflake.

Previous Articles on Snowflake

Integrating Snowflake with Trino

Previous Articles on CockroachDB CDC

Motivation

I work with financial services clients, and it's common to encounter a need for streaming changes in the operational datastore into a data warehouse or a data lake. A former colleague recently reached out for advice on the fastest and most efficient way to load trade data into Snowflake. I've come up with at least three methods, which I will explore in a follow-up series of articles. However, I've decided to first explore Redpanda Connect, a solution that has recently caught my attention. This is by no means a conclusive guide on how changefeed data must be loaded into Snowflake; we're merely exploring the possibilities and discussing the pros and cons in later articles.

CockroachDB changefeeds are an enterprise feature and require a license. In this tutorial, I'm using a free-to-start version of CockroachDB Serverless, which has enterprise changefeeds enabled.

High Level Steps

Deploy a CockroachDB cluster with enterprise changefeeds
Deploy Redpanda Connect
Deploy Snowflake
Verify
Conclusion

Step by Step Instructions

Deploy a CockroachDB Cluster with Enterprise Changefeeds

Start an instance of CockroachDB or use the managed service.

To enable CDC we need to execute the following commands:

SET CLUSTER SETTING cluster.organization = '<organization name>';

SET CLUSTER SETTING enterprise.license = '<secret>';

SET CLUSTER SETTING kv.rangefeed.enabled = true;

I am using CockroachDB Serverless and the above steps are not necessary. You may confirm whether the changefeeds are indeed enabled using the following command:

SHOW CLUSTER SETTING kv.rangefeed.enabled;

If the value is false, change it to true.

Generate sample data:

CREATE TABLE office_dogs (
     id INT PRIMARY KEY,
     name STRING);

INSERT INTO office_dogs VALUES
   (1, 'Petee'),
   (2, 'Carl');

UPDATE office_dogs SET name = 'Petee H' WHERE id = 1;

We've populated the table and then updated a record, let's add more data to make it interesting:

INSERT INTO office_dogs SELECT generate_series(3, 10000), md5(random()::string);

SELECT * FROM office_dogs LIMIT 5;

id,name
1,Petee H
2,Carl
3,6e19280ae649efffa7a58584c7f46032
4,5e4e897f008bb752c8edfa64a3aed356
5,abc0d898318d27f23a43060f89d62e34

SELECT COUNT(*) FROM office_dogs;

Deploy Redpanda Connect

I'm running Redpanda Connect in a Docker Compose file:

docker compose -f compose-redpanda.yaml up -d

The contents of the file are:

services:
  redpanda:
    container_name: redpanda-connect
    hostname: redpanda-connect
    image: docker.redpanda.com/redpandadata/connect
    volumes:
      - ./redpanda/connect.yaml:/connect.yaml
      - /Users/aervits/.ssh/rsa_key.pem:/rsa_key.pem

I will be using the connect.yaml file as the foundation to connect all the components in this article. For more detailed information, you can refer to the documentation provided by Redpanda.

The most basic configuration looks like so:

input:
  stdin: {}

pipeline:
  processors: []

output:
  stdout: {}

Since I'm using CockroachDB input, mine looks like this:

input:
  # CockroachDB Input
  label: ""
  cockroachdb_changefeed:
    dsn: postgresql://<user>:<password>@<cockroachdb-cluster>:<port>/<database>?sslmode=verify-full
    tls:
      skip_cert_verify: true
      #enable_renegotiation: false
      #root_cas: ""
      #root_cas_file: ""
      client_certs: []
    tables: [table_for_cdc] # No default (required)
    cursor_cache: "" # No default (optional)
    auto_replay_nacks: true

pipeline:
  processors: []

output:
  stdout: {}

Leave the pipeline and output as default.

For reference, I'm including the repo with my source code where you can reference the values.

If you have been following along, you may have noticed that I haven't started a changefeed job in CockroachDB. The cockroachdb_changefeed input directly subscribes to the table, which can be observed by examining the logs using the command docker logs redpanda-connect --follow. If you look at the connect.yaml file, the output is sent to stdout:

{"primary_key":"[9998]","row":"{\"after\": {\"id\": 9998, \"name\": \"0794a9d1c99e8e47ee4515be6e0d736f\"}}","table":"office_dogs"}
{"primary_key":"[9999]","row":"{\"after\": {\"id\": 9999, \"name\": \"c85a6b38154f7e3085d467d567141d45\"}}","table":"office_dogs"}
{"primary_key":"[10000]","row":"{\"after\": {\"id\": 10000, \"name\": \"aae9e0849fff8f47e0371a4c06fb255b\"}}","table":"office_dogs"}

The next step is to configure Snowflake. We are not going to look at the available processors today.

Deploy Snowflake

I'm using a Snowflake trial account. You get a generous credit which should be sufficient to complete this tutorial.

We need to create a database and a table where we will output the changefeed data.

CREATE OR REPLACE DATABASE FROM_COCKROACH;
CREATE OR REPLACE TABLE OFFICE_DOGS (RECORD variant);

We also need to create a user with key-pair authentication as we're going to be using the Snowpipe service.

openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8

We must use an encrypted key as Redpanda doesn't support unencrypted version.

Generate a public key:

openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Lastly, generate a PEM file from the private key:

openssl pkcs8 -in rsa_key.p8 -out rsa_key.pem

In Snowflake, alter the user to use the key pair generated in the previous step.

ALTER USER username SET rsa_public_key='MIIB...';

We can now populate the connect.yaml file with the required information for the snowflake_put output. This output type is for commercial use and requires a license, but since we're using it for demo purposes, we are able to proceed.

output:
  # Snowflake Output
  label: ""
  snowflake_put:
    account: <snowflake-account>
    user: <user>
    private_key_file: rsa_key.pem
    role: ACCOUNTADMIN
    database: <database>
    warehouse: <warehouse>
    schema: <schema>
    stage: "@%implicit_table_stage_name"
    path: "path"
    upload_parallel_threads: 4
    compression: NONE
    batching:
      count: 10
      period: 3s
      processors:
        - archive:
            format: json_array
    max_in_flight: 1

If we restart the Compose environment and tail the logs, we can see the following:

level=info msg="Running main config from specified file" @service=benthos benthos_version=v4.32.1 path=/connect.yaml
level=info msg="Listening for HTTP requests at: http://0.0.0.0:4195" @service=benthos
level=info msg="Launching a Redpanda Connect instance, use CTRL+C to close" @service=benthos
level=info msg="Output type snowflake_put is now active" @service=benthos label="" path=root.output
level=info msg="Input type cockroachdb_changefeed is now active" @service=benthos label="" path=root.input

Let's look at the implicit table stage and observe if anything changed.

LIST @%office_dogs;

| dogs/f2f3cf47-d6bc-46f4-88f2-c82519b67481.json | 1312 | 30f709e4962bae9d10b48565d22e9f32 | Wed, 14 Aug 2024 18:58:43 GMT |
| dogs/f6adbf39-3955-4848-93c3-06f873a88078.json | 1312 | 28be7a619ef1e139599077e977ea130b | Wed, 14 Aug 2024 18:58:13 GMT |
| dogs/f8705606-eb07-400a-9ffe-da6834fa1a30.json | 1296 | 5afbdce0e8929fc38a2eb5e0f12b96d6 | Wed, 14 Aug 2024 18:57:29 GMT |
| dogs/f9e5c01a-7dda-4e76-840d-13b8a1e4946a.json | 1296 | 5480c01f1578f67afe2761c7619e9123 | Wed, 14 Aug 2024 18:57:32 GMT |
| dogs/fad4efe7-3f3f-48bc-bdb4-9f0310abcf4d.json | 1312 | 5942c6e2dbaef5ee257d4a9b8e68827d | Wed, 14 Aug 2024 18:58:04 GMT |

The files are ready to be copied into a table. Let's create a pipe:

CREATE OR REPLACE PIPE FROM_COCKROACH.PUBLIC.cockroach_pipe AUTO_INGEST = FALSE AS COPY INTO FROM_COCKROACH.PUBLIC.OFFICE_DOGS FROM (SELECT * FROM @%office_dogs) FILE_FORMAT = (TYPE = JSON COMPRESSION = AUTO STRIP_OUTER_ARRAY = TRUE);

The last remaining step is to refresh the pipe.

ALTER PIPE cockroach_pipe REFRESH;

| dogs/ff0871b1-6f49-43a4-a929-958d07f74046.json | SENT   |
| dogs/ff131d8d-3781-4cf6-8700-edd50dbb87de.json | SENT   |
| dogs/ff216da1-4f9d-4b37-9776-bcd559dd4a6f.json | SENT   |
| dogs/ff221430-4c3a-46be-bbc2-d335cc6cc9e3.json | SENT   |
| dogs/ffbd7d45-5084-4e36-8907-61874ac652b4.json | SENT   |
| dogs/fffb5fa6-23cc-4450-934a-29ccf01c67b9.json | SENT   |

Let's query the table in Snowflake:

SELECT * FROM OFFICE_DOGS LIMIT 5;

{
  "primary_key": "[5241]",
  "row": "{\"after\": {\"id\": 5241, \"name\": \"5e0360a0d10d849afbbfa319a50bccf2\"}}",
  "table": "office_dogs"
}
{
  "primary_key": "[5242]",
  "row": "{\"after\": {\"id\": 5242, \"name\": \"62be250249afe74bfbc5dd356e7b0ad9\"}}",
  "table": "office_dogs"
}
{
  "primary_key": "[5243]",
  "row": "{\"after\": {\"id\": 5243, \"name\": \"7f286800a8a03e74938d09fdba52f869\"}}",
  "table": "office_dogs"
}
{
  "primary_key": "[5244]",
  "row": "{\"after\": {\"id\": 5244, \"name\": \"16a330b8f09bcd314f9760ffe26d0ae2\"}}",
  "table": "office_dogs"
}

We expect 10,000 rows:

SELECT COUNT(*) FROM OFFICE_DOGS;

+----------+
| COUNT(*) |
|----------|
|    10000 |
+----------+

The data is in JSON format. Let's create a view and flatten the data out.

CREATE VIEW v_office_dogs AS
    SELECT PARSE_JSON(record:row):after:id::INTEGER AS id,
           PARSE_JSON(record:row):after:name::STRING AS name FROM OFFICE_DOGS;

Query the view:

SELECT * FROM v_office_dogs WHERE id < 6;

+----+----------------------------------+
| ID | NAME                             |
|----+----------------------------------|
|  1 | Petee H                          |
|  2 | Carl                             |
|  3 | 6e19280ae649efffa7a58584c7f46032 |
|  4 | 5e4e897f008bb752c8edfa64a3aed356 |
|  5 | abc0d898318d27f23a43060f89d62e34 |
+----+----------------------------------+

Verify

Let's make things a bit more interesting and delete data in CockroachDB.

DELETE FROM office_dogs WHERE name = 'Carl';
DELETE FROM office_dogs WHERE id = 1;

In Snowflake, let's refresh the pipe as of a few minutes ago:

ALTER PIPE cockroach_pipe REFRESH MODIFIED_AFTER='2024-08-14T12:10:00-07:00';

Notice there are a couple of files:

+------------------------------------------------+--------+
| File                                           | Status |
|------------------------------------------------+--------|
| dogs/2a4ee400-6b37-4513-97cb-097764a340bc.json | SENT   |
| dogs/8f5b5b69-8a00-4dbf-979a-60c3814d96b4.json | SENT   |
+------------------------------------------------+--------+

I must caution that if you run the REFRESH manually, you may cause duplicates in your Snowflake data. We will look at better approaches in a future article.

Let's look at the row count:

+----------+
| COUNT(*) |
|----------|
|    10002 |
+----------+

The removal process didn't properly update in Snowflake as anticipated; it recognized the deleted records but failed to mirror the state in CockroachDB. We need to incorporate additional logic to achieve this. This will be a task for another time.

Lastly, I would like to note that using Redpanda Connect as a Compose file is optional. You have the option to run the Docker container by executing the following command:

docker run --rm -it -v ./redpanda/connect.yaml:/connect.yaml -v ./snowflake/rsa_key.pem:/rsa_key.pem docker.redpanda.com/redpandadata/connect run

Conclusion

Today, we explored Redpanda Connect as a means to deliver streaming changefeeds into Snowflake. We've only just begun to delve into this topic, and future articles will build upon the foundations laid today.

Integrating Snowflake with Trino

noreply@blogger.com (Artem Ervits) — Fri, 15 Mar 2024 19:16:00 +0000

In today's discourse, we delve into the intricacies of accessing Snowflake via the Trino project. This article illuminates the seamless integration of Trino with Snowflake, offering a comprehensive analysis of its benefits and implications.

Previous articles on Snowflake and Trino:

Motivation

A common query among potential adopters of Snowflake pertains to its compatibility with on-premise data and cloud platforms like Azure. In this article, we address this question head-on, exploring the feasibility of accessing Snowflake alongside on-premise data through the Trino project. Let's unravel the possibilities together.

High Level Steps

Deploy Trino in Docker
Get a trial Snowflake account
Connect the dots
Conclusion

Step by Step Instructions

Navigating the landscape of data integration can be daunting, especially when considering the compatibility of Snowflake with on-premise environments. In this tutorial, we aim to simplify the process by utilizing a Docker environment to simulate on-premise conditions. Our approach prioritizes simplicity, leveraging standard Snowflake configurations and a basic Trino Docker setup. It's essential to consult your documentation for specific scenarios, but let's begin with the fundamentals.

Deploy Trino in Docker

I have a compose file called compose-trino.yaml with the following contents:

services:
  trino:
    container_name: trino
    hostname: trino
    build: trino/.
    ports:
      - "8080:8080"
    environment:
      - _JAVA_OPTIONS=-Dfile.encoding=UTF-8
    volumes:
      - ./trino/catalog:/etc/trino/catalog
      - ./trino/etc:/etc/trino

In the current directory, I have a folder called trino. Within the folder, I have the following files:

FROM trinodb/trino:442
LABEL version="1.0"
LABEL description="trino container"
ENV REFRESHED_AT 2024_03_15

I also have two more folders called etc and catalog.

Within the catalog directory, I've setup a snowflake.properties file with the following contents:

connector.name=snowflake
connection-url=jdbc:snowflake://<account>.snowflakecomputing.com
connection-user=root
connection-password=secret
snowflake.account=account
snowflake.database=database
snowflake.role=role
snowflake.warehouse=warehouse

If you encounter any hurdles along the way, don't hesitate to refer to the comprehensive Trino documentation. Let's dive in!

Once you set up the Snowflake environment, you can adjust these properties with your values.

Within the etc directory, I have a jvm.config with the following contents:

--add-opens=java.base/java.nio=ALL-UNNAMED
-Djdk.module.illegalAccess=permit

These particular JDK flags are Snowflake specific.

I also have config.properties with the following contents:

coordinator=true
node-scheduler.include-coordinator=true
http-server.http.port=8080
discovery.uri=http://example.net:8080

And finally, node.properties with the following contents:

node.environment=production
node.id=ffffffff-ffff-ffff-ffff-ffffffffffff
node.data-dir=/tmp/trino/data

With everything in place, you're now ready to initiate the Compose environment. Execute the following command to start the environment:

docker compose -f compose-trino.yaml up -d

Upon successful configuration, you should observe a running container named trino. You can confirm this by executing the command:

docker ps

f426506aa443   snowflake-docker-trino   "/usr/lib/trino/bin/..."   53 minutes ago   Up 47 minutes (healthy)   0.0.0.0:8080->8080/tcp   trino

If you encounter any issues, you can further troubleshoot by examining the Trino logs using the following command:

docker logs trino

You can access the Trino container with the following command:

docker exec -it trino trino

Once logged in, you can verify the correct configuration of the Snowflake catalog by executing the following command:

show catalogs;

  Catalog
-----------
 snowflake
 system

Get a Trial Snowflake Account

For the next phase of this tutorial, kindly proceed to sign up for a Snowflake Trial Account through the provided link. Opt for the standard edition since we won't be utilizing enterprise features. During the signup process, I've selected the Azure eastus2 region for my Snowflake deployment.

Upon completing the signup, you'll receive a verification email. Once verified, you'll gain access to your Snowflake environment. Retrieve the necessary details from the email sent by Snowflake, particularly the credentials, and populate the snowflake.properties file located in the trino/catalog directory.

Connect the Dots

Snowflake provides a variety of demo tutorials, including the Tasty Bytes series. For this tutorial, we'll focus on the "Load sample data with SQL from S3 bucket" worksheet. Alternatively, feel free to select a dataset of your preference.

---> set the Role
USE ROLE accountadmin;

---> set the Warehouse
USE WAREHOUSE compute_wh;

---> create the Tasty Bytes Database
CREATE OR REPLACE DATABASE tasty_bytes_sample_data;

---> create the Raw POS (Point-of-Sale) Schema
CREATE OR REPLACE SCHEMA tasty_bytes_sample_data.raw_pos;

---> create the Raw Menu Table
CREATE OR REPLACE TABLE tasty_bytes_sample_data.raw_pos.menu
(
    menu_id NUMBER(19,0),
    menu_type_id NUMBER(38,0),
    menu_type VARCHAR(16777216),
    truck_brand_name VARCHAR(16777216),
    menu_item_id NUMBER(38,0),
    menu_item_name VARCHAR(16777216),
    item_category VARCHAR(16777216),
    item_subcategory VARCHAR(16777216),
    cost_of_goods_usd NUMBER(38,4),
    sale_price_usd NUMBER(38,4),
    menu_item_health_metrics_obj VARIANT
);

---> confirm the empty Menu table exists
SELECT * FROM tasty_bytes_sample_data.raw_pos.menu;

---> create the Stage referencing the Blob location and CSV File Format
CREATE OR REPLACE STAGE tasty_bytes_sample_data.public.blob_stage
url = 's3://sfquickstarts/tastybytes/'
file_format = (type = csv);

---> query the Stage to find the Menu CSV file
LIST @tasty_bytes_sample_data.public.blob_stage/raw_pos/menu/;

---> copy the Menu file into the Menu table
COPY INTO tasty_bytes_sample_data.raw_pos.menu
FROM @tasty_bytes_sample_data.public.blob_stage/raw_pos/menu/;

---> how many rows are in the table?
SELECT COUNT(*) AS row_count FROM tasty_bytes_sample_data.raw_pos.menu;

---> what do the top 10 rows look like?
SELECT TOP 10 * FROM tasty_bytes_sample_data.raw_pos.menu;

---> what menu items does the Freezing Point brand sell?
SELECT
   menu_item_name
FROM tasty_bytes_sample_data.raw_pos.menu
WHERE truck_brand_name = 'Freezing Point';

---> what is the profit on Mango Sticky Rice?
SELECT
   menu_item_name,
   (sale_price_usd - cost_of_goods_usd) AS profit_usd
FROM tasty_bytes_sample_data.raw_pos.menu
WHERE 1=1
AND truck_brand_name = 'Freezing Point'
AND menu_item_name = 'Mango Sticky Rice';

---> to finish, let's extract the Mango Sticky Rice ingredients from the semi-structured column
SELECT
    m.menu_item_name,
    obj.value:"ingredients"::ARRAY AS ingredients
FROM tasty_bytes_sample_data.raw_pos.menu m,
    LATERAL FLATTEN (input => m.menu_item_health_metrics_obj:menu_item_health_metrics) obj
WHERE 1=1
AND truck_brand_name = 'Freezing Point'
AND menu_item_name = 'Mango Sticky Rice';

We have a dataset in Snowflake, let's now pivot back to Trino and access the Snowflake data from there.

If your Compose environment is currently active but lacks essential configurations such as snowflake.database, snowflake.warehouse, or other pertinent Snowflake properties, it's crucial to halt the environment. Prior to proceeding, ensure these properties are appropriately configured. Once adjusted, you can restart the Compose environment and continue with the integration process seamlessly.

docker compose -f compose-trino.yaml down

Back in the snowflake.properties file, change the properties to:

connection-user=snowflakeuser
connection-password=snowflakepassword
snowflake.database=tasty_bytes_sample_data
snowflake.role=accountadmin
snowflake.warehouse=compute_wh

Restart the environment and access the Trino shell.

Within the Trino shell, type:

use snowflake.raw_pos;

Since the Snowflake catalog is already configured to connect to our Trino environment, we can omit the database name from the fully qualified table name. Pick any of the above queries from the Snowflake worksheet and try running them in the Trino container.

SELECT COUNT(*) AS row_count
FROM raw_pos.menu;

 row_count
-----------
       100
(1 row)

Query 20240315_185131_00013_45g27, FINISHED, 1 node
Splits: 1 total, 1 done (100.00%)
0.84 [1 rows, 0B] [1 rows/s, 0B/s]

SELECT
   menu_item_name
FROM raw_pos.menu
WHERE truck_brand_name = 'Freezing Point';

   menu_item_name
--------------------
 Lemonade
 Sugar Cone
 Waffle Cone
 Two Scoop Bowl
 Bottled Water
 Bottled Soda
 Ice Tea
 Ice Cream Sandwich
 Mango Sticky Rice
 Popsicle
(10 rows)

Query 20240315_185212_00015_45g27, FINISHED, 1 node
Splits: 1 total, 1 done (100.00%)
1.23 [10 rows, 0B] [8 rows/s, 0B/s]

Indeed, accessing Snowflake datasets using Trino from our local environment demonstrates the flexibility and interoperability of these tools. This integration allows us to seamlessly work with data across different platforms, enhancing our analytical capabilities and workflow efficiency.

Additionally, you can access the Trino UI via http://localhost:8080. With the default configuration, no password is required, and the username is set to admin. By navigating to the "finished queries" section, you can review the queries you've executed, providing valuable insights into your workflow and facilitating debugging if needed. This feature enhances visibility and transparency into your data operations within the Trino environment.

Conclusion

Trino and its commercial version, Starburst, are potent tools for federating data across different sources. This article showcases how easily Snowflake can be accessed using local tools with Trino. The synergy between Snowflake and Trino offers a robust solution for data management and analytics, empowering organizations to leverage cloud data warehousing and distributed query processing for enhanced insights.

Optimizing pgbench for CockroachDB Part 3

noreply@blogger.com (Artem Ervits) — Thu, 30 Nov 2023 20:53:00 +0000

This is a long anticipated third article on optimizing pgbench for CockroachDB. I've been wanting to write a follow up ever since I learned about new improvements in the next release of CockroachDB.

Previous articles on PgBench and CockoachDB:

Motivation

For the longest time CockroachDB only supported serializable isolation. In our next release after 23.1, CockroachDB will additionally support read committed isolation. The idea here is that users coming from other RDBMS tend to face many challenges with application migrations due to the excessive contention stemming from serializable isolation. To ease the migration woes, read committed isolation will provide an easy on-ramp for those applications. Since read committed isolation is less strict, it has additional benefits of being more performant at the cost of data anomalies. Today, we're going to take everything we've learned so far in my series and apply to read committed isolation.

High Level Steps

Deploy a CockroachDB cluster
Capture the baseline using serializable isolation
Compare to read committed isolation
Conclusion

Step by step instructions

Deploy a CockroachDB cluster

I've designed this tutorial to be reproduceable, in fact I have a Docker environment you may use to try or use our serverless tier, the same way as I did in the original article.

Capture the baseline using serializable isolation

Let's create a user we are going to use to execute PgBench as this will allow us to assign session properties controlling the isolation level.

CREATE USER foo WITH CREATEDB;
GRANT ALL ON DATABASE example TO foo;

Initialize the benchmark

docker exec -it postgresql pgbench \
    --initialize \
    --host=${PGHOST} \
    --username=foo \
    --port=${PGPORT} \
    --no-vacuum \
    --scale=10 \
    ${PGDATABASE}

dropping old tables...
creating tables...
NOTICE:  storage parameter "fillfactor" is ignored
NOTICE:  storage parameter "fillfactor" is ignored
NOTICE:  storage parameter "fillfactor" is ignored
generating data (client-side)...
1000000 of 1000000 tuples (100%) done (elapsed 1.87 s, remaining 0.00 s)
creating primary keys...
done in 32.49 s (drop tables 0.01 s, create tables 0.09 s, client-side generate 5.11 s, primary keys 27.28 s).

Some readers will notice that I am not initializing the benchmark with foreign keys, i.e. --foreign-keys. Though nothing prevents me from using the foreign keys constraints, since read committed is still work in progress, it does not currently demonstrate the performance benefits compared to serializable. In fact, my previous attempts testing read committed had shown serializable was more performant than the old implementation of read committed in CockroachDB. As the engineering works out the kinks, I imagine read committed will only get better with time.

The next step is to run PgBench using the built-in tpc-b workload without optimizations.

docker exec -it postgresql \
 pgbench \
    --host=${PGHOST} \
    --no-vacuum \
    --client=8 \
    --jobs=8 \
    --username=foo \
    --port=${PGPORT} \
    --scale=10 \
    --failures-detailed \
    --verbose-errors \
    --max-tries=3 \
    ${PGDATABASE} \
    -T 60 \
    -P 5

pgbench (16.1 (Debian 16.1-1.pgdg120+1), server 13.0.0)
pgbench: warning: scale option ignored, using count from pgbench_branches table (10)
pgbench: client 5 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/5/0 at timestamp 1701374364.725565161,1 too old; must write at or above 1701374364.783286327,1: "sql txn" meta={id=fe1456c4 key=/Table/109/5/569793/0 iso=Serializable pri=0.00522733 epo=0 ts=1701374364.783286327,1 min=1701374364.518938744,0 seq=2} lock=true stat=PENDING rts=1701374364.725565161,1 wto=false gul=1701374365.018938744,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 5 repeats the transaction after the error (try 1/3)
pgbench: client 4 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/3/0 at timestamp 1701374364.881843744,1 too old; must write at or above 1701374364.902487702,1: "sql txn" meta={id=1eb035c5 key=/Table/109/5/110840/0 iso=Serializable pri=0.00276957 epo=0 ts=1701374364.902487702,1 min=1701374364.803162244,0 seq=2} lock=true stat=PENDING rts=1701374364.881843744,1 wto=false gul=1701374365.303162244,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 4 repeats the transaction after the error (try 1/3)
pgbench: client 6 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/1/0 at timestamp 1701374365.916704036,1 too old; must write at or above 1701374365.933405161,1: "sql txn" meta={id=cf8b382e key=/Table/109/5/279511/0 iso=Serializable pri=0.00573224 epo=0 ts=1701374365.933405161,1 min=1701374365.839919870,0 seq=2} lock=true stat=PENDING rts=1701374365.916704036,1 wto=false gul=1701374366.339919870,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 6 repeats the transaction after the error (try 1/3)
pgbench: client 3 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/9/0 at timestamp 1701374367.028168703,1 too old; must write at or above 1701374367.082025953,1: "sql txn" meta={id=b37541b7 key=/Table/109/5/43919/0 iso=Serializable pri=0.01583132 epo=0 ts=1701374367.082025953,1 min=1701374366.918740453,0 seq=2} lock=true stat=PENDING rts=1701374367.028168703,1 wto=false gul=1701374367.418740453,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 3 repeats the transaction after the error (try 1/3)
progress: 5.0 s, 238.8 tps, lat 33.228 ms stddev 23.762, 0 failed, 4 retried, 4 retries
pgbench: client 1 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/7/0 at timestamp 1701374371.162224122,1 too old; must write at or above 1701374371.200365997,1: "sql txn" meta={id=9b00388b key=/Table/109/5/173940/0 iso=Serializable pri=0.01393375 epo=0 ts=1701374371.200365997,1 min=1701374370.906479830,0 seq=2} lock=true stat=PENDING rts=1701374371.162224122,1 wto=false gul=1701374371.406479830,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 1 repeats the transaction after the error (try 1/3)
pgbench: client 2 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/7/0 at timestamp 1701374371.200365997,1 too old; must write at or above 1701374371.221628122,1: "sql txn" meta={id=cecdb1ab key=/Table/109/5/548267/0 iso=Serializable pri=0.00346401 epo=0 ts=1701374371.221628122,1 min=1701374370.973058997,0 seq=2} lock=true stat=PENDING rts=1701374371.200365997,1 wto=false gul=1701374371.473058997,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 2 repeats the transaction after the error (try 1/3)
pgbench: client 1 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/7/0 at timestamp 1701374371.308297414,2 too old; must write at or above 1701374371.385186164,1: "sql txn" meta={id=98bf761d key=/Table/109/5/173940/0 iso=Serializable pri=0.00580461 epo=0 ts=1701374371.385186164,1 min=1701374371.234436747,0 seq=2} lock=true stat=PENDING rts=1701374371.308297414,2 wto=false gul=1701374371.734436747,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 1 repeats the transaction after the error (try 2/3)
pgbench: client 4 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/3/0 at timestamp 1701374373.132044165,1 too old; must write at or above 1701374373.157914498,1: "sql txn" meta={id=8def4300 key=/Table/109/5/477477/0 iso=Serializable pri=0.00196323 epo=0 ts=1701374373.157914498,1 min=1701374373.001536956,0 seq=2} lock=true stat=PENDING rts=1701374373.132044165,1 wto=false gul=1701374373.501536956,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 4 repeats the transaction after the error (try 1/3)
progress: 10.0 s, 225.6 tps, lat 35.522 ms stddev 30.196, 0 failed, 3 retried, 4 retries
progress: 15.0 s, 238.0 tps, lat 33.552 ms stddev 20.915, 0 failed, 0 retried, 0 retries
pgbench: client 3 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/6/0 at timestamp 1701374380.138378168,2 too old; must write at or above 1701374380.184955710,1: "sql txn" meta={id=c6ab1fec key=/Table/109/5/796957/0 iso=Serializable pri=0.00219624 epo=0 ts=1701374380.184955710,1 min=1701374379.949286210,0 seq=2} lock=true stat=PENDING rts=1701374380.138378168,2 wto=false gul=1701374380.449286210,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 3 repeats the transaction after the error (try 1/3)
pgbench: client 4 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/9/0 at timestamp 1701374381.532448085,1 too old; must write at or above 1701374381.532448085,2: "sql txn" meta={id=ab78ff5d key=/Table/109/5/129631/0 iso=Serializable pri=0.02671145 epo=0 ts=1701374381.532448085,2 min=1701374381.403506460,0 seq=2} lock=true stat=PENDING rts=1701374381.532448085,1 wto=false gul=1701374381.903506460,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 4 repeats the transaction after the error (try 1/3)
pgbench: client 7 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/10/0 at timestamp 1701374381.970990086,1 too old; must write at or above 1701374382.002286294,1: "sql txn" meta={id=a3720795 key=/Table/109/5/796844/0 iso=Serializable pri=0.01176172 epo=0 ts=1701374382.002286294,1 min=1701374381.842546002,0 seq=2} lock=true stat=PENDING rts=1701374381.970990086,1 wto=false gul=1701374382.342546002,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 7 repeats the transaction after the error (try 1/3)
progress: 20.0 s, 215.8 tps, lat 36.918 ms stddev 26.072, 0 failed, 3 retried, 3 retries
progress: 25.0 s, 216.2 tps, lat 37.140 ms stddev 25.942, 0 failed, 0 retried, 0 retries
pgbench: client 1 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/9/0 at timestamp 1701374390.624237798,1 too old; must write at or above 1701374390.675212798,1: "sql txn" meta={id=1e77e781 key=/Table/109/5/487347/0 iso=Serializable pri=0.01765513 epo=0 ts=1701374390.675212798,1 min=1701374390.436830756,0 seq=2} lock=true stat=PENDING rts=1701374390.624237798,1 wto=false gul=1701374390.936830756,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 1 repeats the transaction after the error (try 1/3)
pgbench: client 3 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/10/0 at timestamp 1701374391.884443715,1 too old; must write at or above 1701374391.909561465,1: "sql txn" meta={id=1c27478f key=/Table/109/5/424569/0 iso=Serializable pri=0.03662449 epo=0 ts=1701374391.909561465,1 min=1701374391.702494549,0 seq=2} lock=true stat=PENDING rts=1701374391.884443715,1 wto=false gul=1701374392.202494549,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 3 repeats the transaction after the error (try 1/3)
pgbench: client 7 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/3/0 at timestamp 1701374393.572487549,1 too old; must write at or above 1701374393.572487549,2: "sql txn" meta={id=fdacfb83 key=/Table/109/5/730699/0 iso=Serializable pri=0.03673262 epo=0 ts=1701374393.572487549,2 min=1701374393.376278716,0 seq=2} lock=true stat=PENDING rts=1701374393.572487549,1 wto=false gul=1701374393.876278716,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 7 repeats the transaction after the error (try 1/3)
progress: 30.0 s, 209.0 tps, lat 38.151 ms stddev 31.989, 0 failed, 3 retried, 3 retries
progress: 35.0 s, 195.0 tps, lat 40.949 ms stddev 28.077, 0 failed, 0 retried, 0 retries
pgbench: client 1 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/4/0 at timestamp 1701374399.887792969,1 too old; must write at or above 1701374399.911607302,1: "sql txn" meta={id=855e0eca key=/Table/109/5/165844/0 iso=Serializable pri=0.01764135 epo=0 ts=1701374399.911607302,1 min=1701374399.542153677,0 seq=2} lock=true stat=PENDING rts=1701374399.887792969,1 wto=false gul=1701374400.042153677,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 1 repeats the transaction after the error (try 1/3)
progress: 40.0 s, 145.6 tps, lat 54.621 ms stddev 38.881, 0 failed, 1 retried, 1 retries
pgbench: client 2 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/2/0 at timestamp 1701374408.252897556,1 too old; must write at or above 1701374408.252897556,2: "sql txn" meta={id=3c1d5707 key=/Table/109/5/271506/0 iso=Serializable pri=0.00554444 epo=0 ts=1701374408.252897556,2 min=1701374408.060554931,0 seq=2} lock=true stat=PENDING rts=1701374408.252897556,1 wto=false gul=1701374408.560554931,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 2 repeats the transaction after the error (try 1/3)
progress: 45.0 s, 204.8 tps, lat 39.241 ms stddev 36.493, 0 failed, 1 retried, 1 retries
pgbench: client 3 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/2/0 at timestamp 1701374413.178464892,1 too old; must write at or above 1701374413.221088309,1: "sql txn" meta={id=f1f5cf8b key=/Table/109/5/382061/0 iso=Serializable pri=0.00426220 epo=0 ts=1701374413.221088309,1 min=1701374413.072555142,0 seq=2} lock=true stat=PENDING rts=1701374413.178464892,1 wto=false gul=1701374413.572555142,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 3 repeats the transaction after the error (try 1/3)
progress: 50.0 s, 227.0 tps, lat 35.512 ms stddev 26.517, 0 failed, 1 retried, 1 retries
pgbench: client 4 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/9/0 at timestamp 1701374417.439743852,1 too old; must write at or above 1701374417.512724644,1: "sql txn" meta={id=ceae42c3 key=/Table/109/5/647391/0 iso=Serializable pri=0.00061239 epo=0 ts=1701374417.512724644,1 min=1701374417.170008102,0 seq=2} lock=true stat=PENDING rts=1701374417.439743852,1 wto=false gul=1701374417.670008102,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 4 repeats the transaction after the error (try 1/3)
pgbench: client 5 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/9/0 at timestamp 1701374417.557735311,1 too old; must write at or above 1701374417.583758061,1: "sql txn" meta={id=921ad19c key=/Table/109/5/292793/0 iso=Serializable pri=0.01234524 epo=0 ts=1701374417.583758061,1 min=1701374417.337672519,0 seq=2} lock=true stat=PENDING rts=1701374417.557735311,1 wto=false gul=1701374417.837672519,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 5 repeats the transaction after the error (try 1/3)
progress: 55.0 s, 227.2 tps, lat 35.162 ms stddev 26.932, 0 failed, 2 retried, 2 retries
pgbench: client 3 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/9/0 at timestamp 1701374420.093249854,1 too old; must write at or above 1701374420.125789020,1: "sql txn" meta={id=254d2603 key=/Table/109/5/924387/0 iso=Serializable pri=0.01254729 epo=0 ts=1701374420.125789020,1 min=1701374419.940787687,0 seq=2} lock=true stat=PENDING rts=1701374420.093249854,1 wto=false gul=1701374420.440787687,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 3 repeats the transaction after the error (try 1/3)
pgbench: client 2 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/2/0 at timestamp 1701374421.256457729,1 too old; must write at or above 1701374421.285648938,1: "sql txn" meta={id=8a6584e6 key=/Table/109/5/931266/0 iso=Serializable pri=0.00333353 epo=0 ts=1701374421.285648938,1 min=1701374421.081633729,0 seq=2} lock=true stat=PENDING rts=1701374421.256457729,1 wto=false gul=1701374421.581633729,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 2 repeats the transaction after the error (try 1/3)
progress: 60.0 s, 232.0 tps, lat 34.473 ms stddev 23.692, 0 failed, 2 retried, 2 retries
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 8
number of threads: 8
maximum number of tries: 3
duration: 60 s
number of transactions actually processed: 12883
number of failed transactions: 0 (0.000%)
number of serialization failures: 0 (0.000%)
number of deadlock failures: 0 (0.000%)
number of transactions retried: 20 (0.155%)
total number of retries: 21
latency average = 37.261 ms
latency stddev = 28.630 ms
initial connection time = 22.223 ms
tps = 214.593890 (without initial connection time)

Nothing surpising here as we've seen these retries in my previous articles. PgBench introduced retry logic in the version 15 and we can see the workload retry until transactions succeed.

Let's run our optimization from the previous article. You can find the workload in my docker repo.

docker exec -it postgresql pgbench \ 
    --host=${PGHOST} \
    --no-vacuum \
    --file=tpcb-cockroach.sql@1 \
    --client=8 \
    --jobs=8 \
    --username=foo \
    --port=${PGPORT} \
    --scale=10 \
    --failures-detailed \
    --verbose-errors \
    --max-tries=3 \
    ${PGDATABASE} \
    -T 60 \
    -P 5

pgbench (16.1 (Debian 16.1-1.pgdg120+1), server 13.0.0)
progress: 5.0 s, 305.4 tps, lat 25.998 ms stddev 22.118, 0 failed, 0 retried, 0 retries
progress: 10.0 s, 297.2 tps, lat 26.940 ms stddev 28.511, 0 failed, 0 retried, 0 retries
progress: 15.0 s, 279.8 tps, lat 28.628 ms stddev 29.997, 0 failed, 0 retried, 0 retries
progress: 20.0 s, 279.0 tps, lat 28.608 ms stddev 26.844, 0 failed, 0 retried, 0 retries
progress: 25.0 s, 265.8 tps, lat 29.923 ms stddev 36.855, 0 failed, 0 retried, 0 retries
progress: 30.0 s, 160.0 tps, lat 50.258 ms stddev 79.162, 0 failed, 0 retried, 0 retries
progress: 35.0 s, 187.2 tps, lat 42.830 ms stddev 42.750, 0 failed, 0 retried, 0 retries
progress: 40.0 s, 293.4 tps, lat 27.239 ms stddev 26.405, 0 failed, 0 retried, 0 retries
progress: 45.0 s, 275.8 tps, lat 29.087 ms stddev 33.249, 0 failed, 0 retried, 0 retries
progress: 50.0 s, 284.8 tps, lat 28.038 ms stddev 30.830, 0 failed, 0 retried, 0 retries
progress: 55.0 s, 279.8 tps, lat 28.593 ms stddev 29.569, 0 failed, 0 retried, 0 retries
progress: 60.0 s, 279.2 tps, lat 28.617 ms stddev 30.770, 0 failed, 0 retried, 0 retries
transaction type: tpcb-cockroach.sql
scaling factor: 10
query mode: simple
number of clients: 8
number of threads: 8
maximum number of tries: 3
duration: 60 s
number of transactions actually processed: 15945
number of failed transactions: 0 (0.000%)
number of serialization failures: 0 (0.000%)
number of deadlock failures: 0 (0.000%)
number of transactions retried: 0 (0.000%)
total number of retries: 0
latency average = 30.114 ms
latency stddev = 35.211 ms
initial connection time = 7.962 ms
tps = 265.499744 (without initial connection time)

Notice a much cleaner run but we knew this already from the second article.

Compare to read committed isolation

We're now going to switch to read committed for the user foo.

SET CLUSTER SETTING sql.txn.read_committed_isolation.enabled = true; 
ALTER USER foo SET default_transaction_isolation = 'read committed';

We can connect to the cluster using the user account for foo and confirm which isolation is being used.

foo@lb:26000/defaultdb> SHOW default_transaction_isolation;                                                                            
  default_transaction_isolation
---------------------------------
  read committed

We can rerun the benchmark using the built-in workload and read committed

docker exec -it postgresql \            
 pgbench \
    --host=${PGHOST} \
    --no-vacuum \
    --client=8 \
    --jobs=8 \
    --username=foo \
    --port=${PGPORT} \
    --scale=10 \
    --failures-detailed \
    --verbose-errors \
    --max-tries=3 \
    ${PGDATABASE} \
    -T 60 \
    -P 5

pgbench (16.1 (Debian 16.1-1.pgdg120+1), server 13.0.0)
pgbench: warning: scale option ignored, using count from pgbench_branches table (10)
progress: 5.0 s, 227.0 tps, lat 35.042 ms stddev 18.674, 0 failed, 0 retried, 0 retries
progress: 10.0 s, 213.8 tps, lat 37.408 ms stddev 20.119, 0 failed, 0 retried, 0 retries
progress: 15.0 s, 217.8 tps, lat 36.751 ms stddev 19.447, 0 failed, 0 retried, 0 retries
progress: 20.0 s, 215.8 tps, lat 36.922 ms stddev 19.299, 0 failed, 0 retried, 0 retries
progress: 25.0 s, 217.4 tps, lat 36.918 ms stddev 19.705, 0 failed, 0 retried, 0 retries
progress: 30.0 s, 216.2 tps, lat 36.902 ms stddev 20.906, 0 failed, 0 retried, 0 retries
progress: 35.0 s, 205.8 tps, lat 38.992 ms stddev 24.295, 0 failed, 0 retried, 0 retries
progress: 40.0 s, 211.6 tps, lat 37.757 ms stddev 21.103, 0 failed, 0 retried, 0 retries
progress: 45.0 s, 220.2 tps, lat 36.309 ms stddev 21.630, 0 failed, 0 retried, 0 retries
progress: 50.0 s, 217.6 tps, lat 36.876 ms stddev 20.284, 0 failed, 0 retried, 0 retries
progress: 55.0 s, 212.8 tps, lat 37.545 ms stddev 20.558, 0 failed, 0 retried, 0 retries
progress: 60.0 s, 218.8 tps, lat 36.568 ms stddev 19.725, 0 failed, 0 retried, 0 retries
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 8
number of threads: 8
maximum number of tries: 3
duration: 60 s
number of transactions actually processed: 12982
number of failed transactions: 0 (0.000%)
number of serialization failures: 0 (0.000%)
number of deadlock failures: 0 (0.000%)
number of transactions retried: 0 (0.000%)
total number of retries: 0
latency average = 36.985 ms
latency stddev = 20.521 ms
initial connection time = 8.775 ms
tps = 216.191364 (without initial connection time)

Notice how well read committed isolation handles contention in this run. In our original execution, there were 21 retries and even though PgBench retried those transactions, some transactions had to be reprocessed, thereby wasting compute. Here however, those retries didn't even surface. We also processed 100 more transactions 12982 vs 12883 in the original run. This is not significant in a small scale test like mine but every bit counts when these tests are on larger scale.

The next step we're going to be running the optimized workload with read committed

docker exec -it postgresql pgbench \    
    --host=${PGHOST} \                     
    --no-vacuum \
    --file=tpcb-cockroach.sql@1 \
    --client=8 \
    --jobs=8 \
    --username=foo \
    --port=${PGPORT} \
    --scale=10 \
    --failures-detailed \
    --verbose-errors \
    --max-tries=3 \
    ${PGDATABASE} \
    -T 60 \
    -P 5

pgbench (16.1 (Debian 16.1-1.pgdg120+1), server 13.0.0)
progress: 5.0 s, 283.0 tps, lat 28.122 ms stddev 25.956, 0 failed, 0 retried, 0 retries
progress: 10.0 s, 271.0 tps, lat 29.569 ms stddev 28.935, 0 failed, 0 retried, 0 retries
progress: 15.0 s, 265.8 tps, lat 30.030 ms stddev 29.334, 0 failed, 0 retried, 0 retries
progress: 20.0 s, 261.4 tps, lat 30.605 ms stddev 27.032, 0 failed, 0 retried, 0 retries
progress: 25.0 s, 280.0 tps, lat 28.631 ms stddev 27.362, 0 failed, 0 retried, 0 retries
progress: 30.0 s, 286.4 tps, lat 27.781 ms stddev 27.500, 0 failed, 0 retried, 0 retries
progress: 35.0 s, 281.0 tps, lat 28.577 ms stddev 25.379, 0 failed, 0 retried, 0 retries
progress: 40.0 s, 281.4 tps, lat 28.299 ms stddev 23.310, 0 failed, 0 retried, 0 retries
progress: 45.0 s, 270.6 tps, lat 29.701 ms stddev 30.521, 0 failed, 0 retried, 0 retries
progress: 50.0 s, 271.6 tps, lat 29.482 ms stddev 27.495, 0 failed, 0 retried, 0 retries
progress: 55.0 s, 268.6 tps, lat 29.640 ms stddev 26.566, 0 failed, 0 retried, 0 retries
progress: 60.0 s, 274.4 tps, lat 29.274 ms stddev 29.799, 0 failed, 0 retried, 0 retries
transaction type: tpcb-cockroach.sql
scaling factor: 10
query mode: simple
number of clients: 8
number of threads: 8
maximum number of tries: 3
duration: 60 s
number of transactions actually processed: 16484
number of failed transactions: 0 (0.000%)
number of serialization failures: 0 (0.000%)
number of deadlock failures: 0 (0.000%)
number of transactions retried: 0 (0.000%)
total number of retries: 0
latency average = 29.125 ms
latency stddev = 27.490 ms
initial connection time = 5.965 ms
tps = 274.515057 (without initial connection time)

Voila, we are processing 16484 transactions with read committed vs. 15945 with serializable. Again, there are trade-offs with data anomalies but if performance is a concern and the legacy application is built to work with read committed isolation, you will have better experience with read committed.

For posterity, let me show you that foreign keys are supported with read committed and PgBench.

We do have to switch back to serializable isolation as we have to initialize the workload again and currently truncate statement is not supported with read committed.

ALTER USER foo SET default_transaction_isolation = 'serializable';

docker exec -it postgresql pgbench \    
    --initialize \
    --foreign-keys \          
    --host=${PGHOST} \
    --username=foo \
    --port=${PGPORT} \
    --no-vacuum \
    --scale=10 \
    ${PGDATABASE}

dropping old tables...
creating tables...
NOTICE:  storage parameter "fillfactor" is ignored
NOTICE:  storage parameter "fillfactor" is ignored
NOTICE:  storage parameter "fillfactor" is ignored
generating data (client-side)...
1000000 of 1000000 tuples (100%) done (elapsed 2.82 s, remaining 0.00 s)
creating primary keys...
creating foreign keys...
done in 38.08 s (drop tables 0.21 s, create tables 0.06 s, client-side generate 5.81 s, primary keys 30.57 s, foreign keys 1.44 s).

let's switch to read committed first

Built-in workload using explicit transactions

docker exec -it postgresql \         
  pgbench \                                 
    --host=${PGHOST} \
    --no-vacuum \
    --client=8 \
    --jobs=8 \
    --username=foo \
    --port=${PGPORT} \
    --scale=10 \
    --failures-detailed \
    --verbose-errors \
    --max-tries=3 \
    ${PGDATABASE} \
    -T 60 \
    -P 5

pgbench (16.1 (Debian 16.1-1.pgdg120+1), server 13.0.0)
pgbench: warning: scale option ignored, using count from pgbench_branches table (10)
progress: 5.0 s, 200.0 tps, lat 39.704 ms stddev 21.335, 0 failed, 0 retried, 0 retries
progress: 10.0 s, 193.6 tps, lat 41.307 ms stddev 21.592, 0 failed, 0 retried, 0 retries
progress: 15.0 s, 199.4 tps, lat 40.088 ms stddev 19.551, 0 failed, 0 retried, 0 retries
progress: 20.0 s, 197.0 tps, lat 40.730 ms stddev 21.442, 0 failed, 0 retried, 0 retries
progress: 25.0 s, 204.8 tps, lat 38.967 ms stddev 19.367, 0 failed, 0 retried, 0 retries
progress: 30.0 s, 198.4 tps, lat 40.368 ms stddev 22.252, 0 failed, 0 retried, 0 retries
progress: 35.0 s, 196.8 tps, lat 40.791 ms stddev 22.013, 0 failed, 0 retried, 0 retries
progress: 40.0 s, 192.4 tps, lat 41.471 ms stddev 21.291, 0 failed, 0 retried, 0 retries
progress: 45.0 s, 198.8 tps, lat 40.249 ms stddev 21.359, 0 failed, 0 retried, 0 retries
progress: 50.0 s, 193.0 tps, lat 41.309 ms stddev 22.361, 0 failed, 0 retried, 0 retries
progress: 55.0 s, 193.8 tps, lat 41.319 ms stddev 21.147, 0 failed, 0 retried, 0 retries
progress: 60.0 s, 190.6 tps, lat 42.024 ms stddev 22.474, 0 failed, 0 retried, 0 retries
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 8
number of threads: 8
maximum number of tries: 3
duration: 60 s
number of transactions actually processed: 11800
number of failed transactions: 0 (0.000%)
number of serialization failures: 0 (0.000%)
number of deadlock failures: 0 (0.000%)
number of transactions retried: 0 (0.000%)
total number of retries: 0
latency average = 40.687 ms
latency stddev = 21.375 ms
initial connection time = 11.911 ms
tps = 196.451948 (without initial connection time)

Eventhough read committed handles the contention, it still underperforms compared to the execution without foreign keys. Hopefully with further enhancement work in read committed implementation this improves.

Optimized workload

docker exec -it postgresql pgbench \
    --host=${PGHOST} \
    --no-vacuum \
    --file=tpcb-cockroach.sql@1 \
    --client=8 \
    --jobs=8 \
    --username=foo \
    --port=${PGPORT} \
    --scale=10 \
    --failures-detailed \
    --verbose-errors \
    --max-tries=3 \
    ${PGDATABASE} \
    -T 60 \
    -P 5

pgbench (16.1 (Debian 16.1-1.pgdg120+1), server 13.0.0)
progress: 5.0 s, 233.2 tps, lat 34.079 ms stddev 26.993, 0 failed, 0 retried, 0 retries
progress: 10.0 s, 200.7 tps, lat 39.841 ms stddev 36.275, 0 failed, 0 retried, 0 retries
progress: 15.0 s, 220.3 tps, lat 36.399 ms stddev 29.333, 0 failed, 0 retried, 0 retries
progress: 20.0 s, 226.2 tps, lat 35.320 ms stddev 29.787, 0 failed, 0 retried, 0 retries
progress: 25.0 s, 220.0 tps, lat 36.370 ms stddev 28.896, 0 failed, 0 retried, 0 retries
progress: 30.0 s, 222.4 tps, lat 35.921 ms stddev 30.289, 0 failed, 0 retried, 0 retries
progress: 35.0 s, 214.0 tps, lat 37.306 ms stddev 34.033, 0 failed, 0 retried, 0 retries
progress: 40.0 s, 216.2 tps, lat 37.187 ms stddev 31.789, 0 failed, 0 retried, 0 retries
progress: 45.0 s, 227.6 tps, lat 35.093 ms stddev 31.301, 0 failed, 0 retried, 0 retries
progress: 50.0 s, 230.2 tps, lat 34.797 ms stddev 29.103, 0 failed, 0 retried, 0 retries
progress: 55.0 s, 226.2 tps, lat 35.291 ms stddev 30.328, 0 failed, 0 retried, 0 retries
progress: 60.0 s, 217.6 tps, lat 36.763 ms stddev 29.471, 0 failed, 0 retried, 0 retries
transaction type: tpcb-cockroach.sql
scaling factor: 10
query mode: simple
number of clients: 8
number of threads: 8
maximum number of tries: 3
duration: 60 s
number of transactions actually processed: 13279
number of failed transactions: 0 (0.000%)
number of serialization failures: 0 (0.000%)
number of deadlock failures: 0 (0.000%)
number of transactions retried: 0 (0.000%)
total number of retries: 0
latency average = 36.154 ms
latency stddev = 30.686 ms
initial connection time = 7.224 ms
tps = 221.166896 (without initial connection time)

Conclusion

As you see, read committed has performance advantages to serializable isolation, and as bugs get worked out, it will only get better. Considering the trade-offs with read committed isolation, if your application can tolerate the anomalies, it will provide a much better on-ramp experience for applications.

Secure schema migrations with Flyway and CockroachDB Dedicated

noreply@blogger.com (Artem Ervits) — Tue, 24 Oct 2023 19:36:00 +0000

CockroachDB Dedicated comes with TLS enabled by default. At the minimum, a certificate authority is needed to authenticate. A customer was having issues using Flyway and TLS.

My previous articles on schema migrations and CockroachDB

Migrating feature toggles with Unleash and CockroachDB

Motivation

A prospect was having issues with TLS and Flyway schema migrations and since both topics are near and dear to my heart I felt obligated to respond.

High Level Steps

Deploy a CockroachDB Dedicated cluster
Configure Flyway
Verify
Demonstrate the problem

Step by step instructions

Deploy a CockroachDB Dedicated cluster

Spinning up a CockroachDB Dedicated cluster is fairly straight forward. Follow this guide.

Configure Flyway

Setting up a Flyway migration depends on your deployment model. I don't use any sophisticated CI/CD pipeline and rely on Docker for my needs. I'm also using the community edition and limited to certain capabilities.

You need a flyway directory with two child directories, conf and sql. The conf directory containes the authentication details in a file called flyway.conf. You can find the connection details in the cloud console or by typing the \c metacommand in the SQL client to capture the connection string.

Connection string: postgresql://artem:~~~~~~@artem-serverless-5954.g8z.cockroachlabs.cloud:26257/defaultdb?application_name=ccloud&connect_timeout=15&sslmode=verify-full

You may have noticed that I am using a serverless cluster instead but it shouldn't be relevant. The steps in this article are applicable for both scenarios.

flyway.url=jdbc:postgresql://artem-serverless-5954.g8z.cockroachlabs.cloud:26257/defaultdb
flyway.user=artem
flyway.password=password
flyway.connectRetries=3

Populate the sql directory with one or many migration files.

-rw-r--r--  1 artem  staff   39 Oct 20 14:41 V1_0__Create_database.sql
-rw-r--r--  1 artem  staff   96 Oct 20 14:56 V1_3__Seed_table.sql
-rw-r--r--  1 artem  staff   72 Oct 20 15:00 V1_1__Create_type.sql
-rw-r--r--  1 artem  staff  128 Oct 20 15:04 V1_2__Create_table.sql

Verify

You can test that everything works using the info command.

docker run --rm \
    -v $PWD/flyway/sql:/flyway/sql \
    -v $PWD/flyway/conf:/flyway/conf \
    flyway/flyway \
    info

Flyway Community Edition 9.22.3 by Redgate
See release notes here: https://rd.gt/416ObMi

Database: jdbc:postgresql://artem-serverless-5954.g8z.cockroachlabs.cloud:26257/defaultdb (PostgreSQL 13.0)
WARNING: Flyway upgrade recommended: CockroachDB 23.1 is newer than this version of Flyway and support has not been tested. The latest supported version of CockroachDB is 22.1.
Schema history table "public"."flyway_schema_history" does not exist yet
Schema version: << Empty Schema >>

+-----------+---------+-----------------+------+--------------+---------+----------+
| Category  | Version | Description     | Type | Installed On | State   | Undoable |
+-----------+---------+-----------------+------+--------------+---------+----------+
| Versioned | 1.0     | Create database | SQL  |              | Pending | No       |
| Versioned | 1.1     | Create type     | SQL  |              | Pending | No       |
| Versioned | 1.2     | Create table    | SQL  |              | Pending | No       |
| Versioned | 1.3     | Seed table      | SQL  |              | Pending | No       |
+-----------+---------+-----------------+------+--------------+---------+----------+

This seems to work but if for any reason you may have TLS issues, you may need to include &sslmode= and this is primarily the reason for this article.

Here's a message you'll see if you try to circumvent the sslmode with sslmode=disable.

WARNING: Connection error: FATAL: server requires encryption
Retrying in 4 sec...
ERROR: Unable to obtain connection from database (jdbc:postgresql://artem-serverless-5954.g8z.cockroachlabs.cloud:26257/defaultdb?sslmode=disable) for user 'artem': FATAL: server requires encryption

The easiest way to address the problem is to use sslmode=require, i.e.

flyway.url=jdbc:postgresql://artem-serverless-5954.g8z.cockroachlabs.cloud:26257/defaultdb?sslmode=require

and it's in line with what we originally saw, but the problem with require is that it makes no effort to validate the authenticity of the cluster CA certififacte, leading to man in the middle attack risks. Hence, we always recommend using the sslmode=verify-full, which I will demonstrate shortly.

Before we proceed, I want to highlight how docker compose makes this even easier as it eliminiates the need to type out the entire docker command, it will become obvious as we discuss the verify-full method.

version: '3.9'

services:

  flyway:
    container_name: flyway
    hostname: flyway
    image: flyway/flyway
    entrypoint: ["flyway", "info"]
    volumes:
      - $PWD/flyway/sql:/flyway/sql
      - $PWD/flyway/conf:/flyway/conf

Save the following in a docker-compose.yml file and then you can invoke it using the following command

docker compose -f docker-compose.yml up

Demonstrate the problem

In some cases, a more stringent requirement for security is needed where we have to use sslmode=verify-full. Unappologetically, I agree and advocate to use the more security option as much as possible.

flyway.url=jdbc:postgresql://artem-serverless-5954.g8z.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full

flyway  | Retrying in 4 sec...
flyway  | ERROR: Unable to obtain connection from database (jdbc:postgresql://artem-serverless-5954.g8z.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full) for user 'artem': Could not open SSL root certificate file /root/.postgresql/root.crt.
flyway  | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
flyway  | SQL State  : 08006
flyway  | Error Code : 0
flyway  | Message    : Could not open SSL root certificate file /root/.postgresql/root.crt.
flyway  | 
flyway  | Caused by: org.postgresql.util.PSQLException: Could not open SSL root certificate file /root/.postgresql/root.crt.
flyway  | Caused by: java.io.FileNotFoundException: /root/.postgresql/root.crt (No such file or directory)
flyway exited with code 1

Clearly, we've not included the CA certificate but the system is smart enough to look for it in the default location. The problem is it's looking in the default location of the container, not the host system.

What needs to be done is attaching the cert volume to the container. All we need to do is include a single additional line in our compose file and tell flyway.conf to look for the cert in the url.

version: '3.9'

services:

  flyway:
    container_name: flyway
    hostname: flyway
    image: flyway/flyway
    entrypoint: ["flyway", "info"]
    volumes:
      - ./flyway/sql:/flyway/sql
      - ./flyway/conf:/flyway/conf
      - $PWD/root.crt:/certs/root.crt

flyway.url=jdbc:postgresql://artem-serverless-5954.g8z.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full&sslrootcert=/certs/root.crt

At this point, the info command succeeds, let's make the final change to the compose file to run a migration upon a successful connection and use the info command as container healthcheck.

version: '3.9'

services:

  flyway:
    container_name: flyway
    hostname: flyway
    image: flyway/flyway
    entrypoint: ["flyway", "migrate"]
    volumes:
      - ./flyway/sql:/flyway/sql
      - ./flyway/conf:/flyway/conf
      - $PWD/root.crt:/certs/root.crt
    healthcheck:
      test: ["CMD-SHELL", "flyway", "info"]
      interval: 10s
      timeout: 10s
      retries: 3
      start_period: 10s

We can now run the migration

flyway  | Flyway Community Edition 9.22.3 by Redgate
flyway  | See release notes here: https://rd.gt/416ObMi
flyway  | 
flyway  | Database: jdbc:postgresql://artem-serverless-5954.g8z.cockroachlabs.cloud:26257/defaultdb (PostgreSQL 13.0)
flyway  | WARNING: Flyway upgrade recommended: CockroachDB 23.1 is newer than this version of Flyway and support has not been tested. The latest supported version of CockroachDB is 22.1.
flyway  | Successfully validated 4 migrations (execution time 00:00.103s)
flyway  | Current version of schema "public": << Empty Schema >>
flyway  | Migrating schema "public" to version "1.0 - Create database" [non-transactional]
flyway  | Migrating schema "public" to version "1.1 - Create type" [non-transactional]
flyway  | Migrating schema "public" to version "1.2 - Create table" [non-transactional]
flyway  | Migrating schema "public" to version "1.3 - Seed table" [non-transactional]
flyway  | Successfully applied 4 migrations to schema "public", now at version v1.3 (execution time 00:00.750s)

Conclusion

This wraps up our look at TLS certificate issues and containerized schema migrations. If you feel this article incorrectly assumes certain things, please leave feedback in the comments.

Emitting Protocol Buffers with CockroachDB CDC Queries

noreply@blogger.com (Artem Ervits) — Wed, 30 Aug 2023 15:22:00 +0000

As of this writing, CockroachDB does not have official support for protobuf payload as part of change data capture. Today, I am going to demonstrate a feature that may or may not land in CockroachDB proper. The product team is looking into viability of the feature before we can officially support it.

Previous articles on CockroachDB CDC

Motivation

Protocol Buffers are language-neutral, platform-neutral extensible mechanisms for serializing structured data. It's a common choice for platforms needing to pass messages between systems. CockroachDB is a distributed SQL database built on a transactional and strongly-consistent key-value store. It scales horizontally; survives disk, machine, rack, and even datacenter failures with minimal latency disruption and no manual intervention; supports strongly-consistent ACID transactions; and provides a familiar SQL API for structuring, manipulating, and querying data. There is no official support for Protocol Buffers in CockroachDB Changefeeds, even though we use Protocol Buffers extensively in code. A recent customer conversation led to this experiment where I'm going to use several recent features to demonstrate ability to serialize CockroachDB rows to proto and emit via CDC Queries. This is the first time we're looking at CDC Queries. This is a new flexible way to express CockroachDB streams.

This tutorial assumes you have an enterprise license. Given the features in this tutorial are unavailable as a product, you have to follow the steps exactly as described to pull the right source code to make it work. These features are not available in any of the available offerings from Cockroach Labs.

High Level Steps

Build CockroachDB with the Protocol Buffers function
Deploy a CockroachDB cluster with enterprise changefeeds
Deploy a Kafka Consumer
Verify
Conclusion

Step by step instructions

Build CockroachDB with the Protocol Buffers function

Before I show you how to get this working, I'd like to express my gratitude to Yevgeniy Miretskiy, who works on the CDC team for the capability and his mentorship to get this working. The source code for the feature is available in the following commit. For brevity, I will skip the steps to setup a build environment.

Check out the pull request

gh pr checkout 89955

Run the preliminary steps to build Cockroach from source.

./dev doctor

Finally build the code

bazel build pkg/cmd/cockroach-short

Navigate to the directory with the built package

cd _bazel/bin/pkg/cmd/cockroach

Deploy a CockroachDB cluster with enterprise changefeeds

Start an instance of CockroachDB using the built package

./cockroach start-single-node --insecure --background

To enable CDC we need to execute the following commands:

SET CLUSTER SETTING cluster.organization = '<organization name>';

SET CLUSTER SETTING enterprise.license = '<secret>';

SET CLUSTER SETTING kv.rangefeed.enabled = true;

Generate sample data

CREATE TABLE office_dogs (
     id INT PRIMARY KEY,
     name STRING);

INSERT INTO office_dogs VALUES
   (1, 'Petee'),
   (2, 'Carl');

UPDATE office_dogs SET name = 'Petee H' WHERE id = 1;

SELECT * FROM office_dogs;

  id |  name
-----+----------
   1 | Petee H
   2 | Carl

The function we are going to use to convert rows to Protocol Buffers is crdb_internal.row_to_proto(). With the given pull request, this function is readily available for querying.

SELECT crdb_internal.row_to_proto(office_dogs) FROM office_dogs;

                          crdb_internal.row_to_proto
------------------------------------------------------------------------------
  \x0a110a046e616d6512091a07506574656520480a0f0a026964120911000000000000f03f
  \x0a0e0a046e616d6512061a044361726c0a0f0a0269641209110000000000000040

It takes the row and serializes as proto. We can decode the row back to human readable form using the following query, skip the \x and copy the rest of the output into the following function:

SELECT crdb_internal.pb_to_json('google.protobuf.Struct',
decode('0a110a046e616d6512091a07506574656520480a0f0a026964120911000000000000f03f', 'hex')) AS proto;

             proto
--------------------------------
  {"id": 1, "name": "Petee H"}

SELECT crdb_internal.pb_to_json('google.protobuf.Struct',
decode('0a0e0a046e616d6512061a044361726c0a0f0a0269641209110000000000000040', 'hex')) AS proto;

            proto
-----------------------------
  {"id": 2, "name": "Carl"}

We can use this function in the CDC query, but first, let's set up a webhook sink for a quick demonstration of changefeed queries.

git clone https://github.com/cockroachlabs/cdc-webhook-sink-test-server.git
cd cdc-webhook-sink-test-server
cd go-https-server
chmod +x server.sh
./server.sh

./server.sh
......+.........+.....+....+...+.....+...+.+.....+++++++++++++++++++++++++++++++++++++++++++++*.+...........+.+......+.....+...+....+...+......+...........+...+......+++++++++++++++++++++++++++++++++++++++++++++*......+.....+.............+...+..+.+.....................+......+..+.+++++
-----
2023/08/30 09:39:05 starting server on port 3000

With all of the basics in place, we can create a changefeed

CREATE CHANGEFEED INTO 'webhook-https://localhost:3000?insecure_tls_skip_verify=true' WITH updated AS SELECT crdb_internal.row_to_proto(office_dogs) AS proto FROM office_dogs;

        job_id
----------------------
  895654238351589377

Verify

Looking at the terminal where the webhook sink is running

2023/08/30 11:11:25 {"payload":[{"__crdb__": {"updated": "1693408285757033000.0000000000"}, "proto": "\\x0a0e0a046e616d6512061a044361726c0a0f0a0269641209110000000000000040"}],"length":1}
2023/08/30 11:11:25 {"payload":[{"__crdb__": {"updated": "1693408285757033000.0000000000"}, "proto": "\\x0a110a046e616d6512091a07506574656520480a0f0a026964120911000000000000f03f"}],"length":1}

Let's update a record in the office_dogs TABLE

UPDATE office_dogs SET name = 'Tarzan' WHERE id = 1;

2023/08/30 11:12:58 {"payload":[{"__crdb__": {"updated": "1693408377084928000.0000000000"}, "proto": "\\x0a100a046e616d6512081a065461727a616e0a0f0a026964120911000000000000f03f"}],"length":1}

If we use the decode function to inspect the payload

SELECT crdb_internal.pb_to_json('google.protobuf.Struct', decode('0a100a046e616d6512081a065461727a616e0a0f0a026964120911000000000000f03f', 'hex')) AS proto;

             proto
-------------------------------
  {"id": 1, "name": "Tarzan"}

I have to mention that the emitted messages are of dynamically typed format and not strongly typed. If your use case requires strongly typed, it's a conversation we have to have another time.

Conclusion

And this is how you can leverage CockroachDB CDC Queries with built-in functions. This function is not available but it can be, given higher demand. Hopefully you've found this article useful. Please reach out to our teach if you need this capability and we will consider it in the future.

CockroachDB TIL: Volume 13

noreply@blogger.com (Artem Ervits) — Thu, 15 Jun 2023 17:23:00 +0000

This is my series of articles covering short "Today I learned" topics as I work with CockroachDB. Today, we're diagnosing cert issues, looking at client verification of server certificates differences in the cockroach and the psql clients, and checking the node and the CA certificate expiration programmatically.

Topics

Topic 1: Diagnose certificate based authentication
Topic 2: Differences between cockroach and psql clients for client verification of server certificates
Topic 3: Capture the node and the CA certificate expiration programmatically
Topic 4: Check the client certificate expiration

Topic 1: Diagnose certificate based authentication

I was using CockroachDB Serverless with a product called PolyScale.ai for an article I was writing and I've run into a strange issue with certificate-based authN. When you provision the instance of their service, it provides a client connection string.

I used the connection string to connect:

psql "postgres://username:password@psedge.global:5432/defaultdb?sslmode=require"

When I attempted to connect, I'd received an error:

psql: error: connection to server at "psedge.global" (104.248.109.242), port 5432 failed: SSL error: certificate verify failed

The error message confused me as it was pointing to a port. What made everything more confusing was that the service reported it had connected just fine.

I thought the issue was with the CA cert Cockroach relies on and I was stuck until I reached out to the engineering team. My team suggested to run the following command to verify the certificate coming from PolyScale:

/opt/homebrew/Cellar/openssl@3/3.1.0/bin/openssl s_client -showcerts -connect psedge.global:5432 -starttls postgres

We need openssl version 3.1 or later, the one I had by default on my OSX laptop did not work. The response of the command will show the certificate details.

depth=2 C = US, O = DigiCert Inc, OU = www.digicert.com, CN = DigiCert Global Root CA
verify return:1
depth=1 C = US, O = DigiCert Inc, CN = DigiCert TLS RSA SHA256 2020 CA1
verify return:1
depth=0 C = US, ST = California, L = Redwood City, O = "Polyscale, Inc", CN = *.polyscale.global
verify return:1
---
Certificate chain
 0 s:C = US, ST = California, L = Redwood City, O = "Polyscale, Inc", CN = *.polyscale.global
   i:C = US, O = DigiCert Inc, CN = DigiCert TLS RSA SHA256 2020 CA1
   a:PKEY: rsaEncryption, 2048 (bit); sigalg: RSA-SHA256
   v:NotBefore: Oct  4 00:00:00 2022 GMT; NotAfter: Jul 28 23:59:59 2023 GMT

CockroachDB Serverless uses Let's Encrypt for the Certificate Authority. Although not necessary, you can download the CA cert and store it in your filesystem.

That's the certificate I had in my filesystem. Similarly, you can use the above command to view the details of the certificate:

/opt/homebrew/Cellar/openssl@3/3.1.0/bin/openssl s_client -showcerts -connect artem-serverless-3312.g8z.cockroachlabs.cloud:26257 -starttls postgres

Server certificate
subject=CN = *.cockroachlabs.cloud
issuer=C = US, O = Let's Encrypt, CN = R3
---
No client certificate CA names sent
Peer signing digest: SHA256
Peer signature type: ECDSA
Server Temp Key: X25519, 253 bits

The workaround here is to remove the root.crt as it was causing conflicts with the PolyScale cert. As soon as you remove the cert, you can authenticate successfully.

psql "postgres://username:password@psedge.global:5432/defaultdb?sslmode=require"
psql (15.3 (Homebrew), server 13.0.0)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

defaultdb=>

At this point, you can also use the cockroach client

cockroach sql --url "postgres://username:password@psedge.global:5432/defaultdb?application_name=19592894-26b0-42f7-aea8-2b4e1cb8ec24&sslmode=require" 
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
# Client version: CockroachDB CCL v22.2.9 (aarch64-apple-darwin21.2, built 2023/05/08 13:26:12, go1.19.6)
# Server version: CockroachDB CCL v22.2.7 (x86_64-pc-linux-gnu, built 2023/03/28 19:47:29, go1.19.6)

warning: server version older than client! proceed with caution; some features may not be available.

# Cluster ID: 20735006-5a39-49c3-29b8-3d4c8befcbf8
#
# Enter \? for a brief introduction.
#
username@psedge.global:5432/defaultdb>

Now what if we'd like to specify the cert we wanted to use to connect, we can capture the cert coming from PolyScale and store it in our filesystem.

/opt/homebrew/Cellar/openssl@3/3.1.0/bin/openssl s_client -showcerts -connect psedge.global:5432 -starttls postgres | openssl x509 -text

Copy the output into a new file with the BEGIN and END included.

-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----

Save it a new file and then use it in the connection string with sslrootcert=file argument.

psql "postgres://username:password@psedge.global:5432/defaultdb?sslmode=require&sslrootcert=polyscale.crt"

psql (15.3 (Homebrew), server 13.0.0)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

defaultdb=>

We can even combine the certs into a single super cert by copying the contents of both certs including the BEGIN and END, i.e.

-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----
-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----

Once we have both certs stored in this cert, we can connect without any issues.

cockroach sql --url "postgres://username:password@psedge.global:5432/defaultdb?sslmode=verify-full&sslrootcert=/Users/artem/.postgresql/super.crt"

and likewise, use the same cert to connect to CockroachDB:

cockroach sql --url "postgres://username:password@artem-serverless-3312.g8z.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full&sslrootcert=/Users/artem/.postgresql/super.crt"

The lesson here is either have all certs in your default directory and explicitly specify them with sslrootcert or remove sslrootcert from the connection string and make sure your filesystem does not have any residual certs.

Topic 2: Differences between `cockroach` and `psql` clients for client verification of server certificates

While I was writing the first topic, I noticed a slight difference in behavior between the cockroach and psql clients when it comes to client verification of server certificates. Unlike the cockroach client, psql client expects a CA certificate in the default location. From the Postgresql docs

To allow server certificate verification, one or more root certificates must be placed in the file ~/.postgresql/root.crt in the user's home directory. (On Microsoft Windows the file is named %APPDATA%\postgresql\root.crt.) Intermediate certificates should also be added to the file if they are needed to link the certificate chain sent by the server to the root certificates stored on the client.

Certificate Revocation List (CRL) entries are also checked if the file ~/.postgresql/root.crl exists (%APPDATA%\postgresql\root.crl on Microsoft Windows).

The location of the root certificate file and the CRL can be changed by setting the connection parameters sslrootcert and sslcrl or the environment variables PGSSLROOTCERT and PGSSLCRL. sslcrldir or the environment variable PGSSLCRLDIR can also be used to specify a directory containing CRL files.

cockroach client will not rely on the default ~/.postgresql/root.crt location to make the authN. Case in point, use the following connection string while not having any certs in the default directory:

cockroach sql --url "postgres://username:password@artem-serverless-3312.g8z.cockroachlabs.cloud:26257/defaultdb?sslmode=require"

It will work, now let's switch the sslmode to verify-full.

cockroach sql --url "postgres://username:password@artem-serverless-3312.g8z.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full"

It will also work. Now let's switch to the psql client

psql "postgres://username:password@artem-serverless-3312.g8z.cockroachlabs.cloud:26257/defaultdb?sslmode=require"

It works, and sslmode-verify?

psql "postgres://username:password@artem-serverless-3312.g8z.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full"

psql: error: connection to server at "artem-serverless-3312.g8z.cockroachlabs.cloud" (34.138.180.55), port 26257 failed: root certificate file "/Users/artem/.postgresql/root.crt" does not exist
Either provide the file or change sslmode to disable server certificate verification.

We need access to the the root certificate as it cannot be implicitly read from the internet.

Download the cert and sslmode-verify will now work.

THe lesson here if you want to verify the server certificate using the psql client, make sure the certificate is stored in the filesystem. The cockroach client is more forgiving in that regard.

Topic 3: Check the node and the CA certificate expiration programmatically

A customer asks: how do you check the client certificate expiration programmatically? You can of course scrape the node metrics endpoint and capture the info, you can also navigate to the Advanced Debug page and look at the certificate page, but is there a way to check using SQL? The answer is yes.

We expose the certificate metrics using the following names, security.certificate.expiration.ca and security.certificate.expiration.node for the Certificate Authority and node certificates respectively. CockroachDB persists this data in a table crdb_internal.kv_node_status. We can issue the following query to retrieve this information.

-- CA cert
SELECT to_timestamp((metrics->>'security.certificate.expiration.ca')::FLOAT)::TIMESTAMPTZ FROM crdb_internal.kv_node_status;  

-- node cert
SELECT to_timestamp((metrics->>'security.certificate.expiration.node')::FLOAT)::TIMESTAMPTZ FROM crdb_internal.kv_node_status;

  2028-05-20 15:26:26+00
  ...
  2028-05-20 15:26:25+00

It will output for every node in the cluster. Now you can build your own monitoring and alerting solution to alarm you when your certificates are about to expire, that's if you don't offload the certificate management to an external system.

https://www.cockroachlabs.com/docs/stable/metrics.html

Topic 4: Check the client certificate expiration

In the previous topic we discussed how to check node and certifacte authority expiration. We left out the client certifactes because keeping this information in the database is not feasible. There may be thousands of client certs. We are going to cover how to check the client certifacte expiration below, keep in mind this is an area of active development and the proper user experience is not yet realized.

I used the following tutorial to create a cluster, CA, node and client certificates.

The ca.cnf file has a field that controls the cert expiry. Any node and client certs using this ca.cnf configuration file will honor the expiration date specified in this file.

[ CA_default ]
default_days = 1

Skipping the rest of the tutorial to the step where we create a second client user, we can use the same commands from the tutorial to generate a client certificate.

openssl genrsa -out certs/client.expired.key 2048
chmod 400 certs/client.expired.key 
openssl req -new -config client.cnf -key certs/client.expired.key -out client.expired.csr -batch
openssl ca -config ca.cnf -keyfile my-safe-directory/ca.key -cert certs/ca.crt -policy signing_policy -extensions signing_client_req -out certs/client.expired.crt -outdir certs/ -in client.expired.csr -batch

The output will contain the expiration date.

Using configuration from ca.cnf
Check that the request matches the signature
Signature ok
The Subject's Distinguished Name is as follows
organizationName      :ASN.1 12:'Cockroach'
commonName            :ASN.1 12:'expired'
Certificate is to be certified until May 27 17:23:04 2023 GMT (1 days)

Now that we have a client cert, we can use the cockroach cert command to list out all of the client certificates in the certificate directory and their expiration.

cockroach cert --certs-dir certs list

Certificate directory: expired_certs
  Usage  |  Certificate File  |      Key File      |  Expires   |     Notes     | Error
---------+--------------------+--------------------+------------+---------------+--------
  Client | client.expired.crt | client.expired.key | 2023/05/27 | user: expired |
(1 row)

It does not show the time of day when client certificate is going to expire. We can use the openssl x509 -text command to inspect the certificate for a more detailed expiry.

openssl x509 -text -in expired_certs/client.expired.crt | grep "Not After"

  Not After : May 27 17:23:04 2023 GMT

We have an open issue to surface a metric to expose clients with expiring certs. We can discuss how to automate the check when the metric is available.

Finally, one more method to check for cert expiration is with the openssl verify command.

openssl verify -CAfile certs/ca.crt expired/client.expired.crt

O = Cockroach, CN = expired
error 10 at 0 depth lookup:certificate has expired
expired/client.expired.crt: verification failed: 10 (certificate has expired)

Using DuckDB with CockroachDB

noreply@blogger.com (Artem Ervits) — Wed, 17 May 2023 18:51:00 +0000

Reducing network latency and improving read performance with CockroachDB and PolyScale.ai

noreply@blogger.com (Artem Ervits) — Fri, 12 May 2023 16:36:00 +0000

Load balancer high availability with CockroachDB and HAProxy

noreply@blogger.com (Artem Ervits) — Thu, 27 Apr 2023 19:27:00 +0000

Connection pool high availability with CockroachDB and PgCat

noreply@blogger.com (Artem Ervits) — Fri, 21 Apr 2023 19:59:00 +0000