TIL

My Thoughts, Opinions, Learnings and Discoveries

  • My New Reality of Software Engineering

    My New Reality of Software Engineering

    Software engineering as a profession is going through an unprecedented phase shift, not just another tool cycle. In the past month I have written almost no code by hand and still shipped more than ever, because agents now handle large parts of the implementation loop under my direction. That sentence would have sounded like breathless hype even a year ago. It does not now. The models and the tools have crossed a threshold where they are good enough, fast enough, and cheap enough to materially change how teams work and what they can deliver.

    You do not have to like every part of this, and you can still have serious concerns about cost, quality, jobs, or broader social and economic impact. I do too. But this shift is happening whether we are emotionally ready for it or not, and the economics will keep pushing it forward. The practical question is no longer “is this real?” but “how quickly can I learn to use it well?” The people and teams who invest now will have a major advantage in output, scope, and speed over the next few years. There is real value in these technologies for you now, in a way that wasn’t really true before.

    A Career That Suddenly Changed

    I have been a software engineer for more than twenty years. Looking back, it’s amazing how many things have changed in that time, and how many things have not. But what has happened in the last year, especially the last six months, has created a fundamental shift in how I work, how I think, how much I produce, and really what it even means to be a software engineer.

    I was lucky (I think) to have always known that I wanted to write software as a career. I knew I wanted to work with computers from the moment I heard about them, before I ever actually touched one. When I was around six, my parents got me a VTech PreComputer 1000, a simple device that had math and geography quizzes, a very non-standard terrible keyboard, and a single 20-character dot matrix LCD display. But it had a mode where you could write BASIC, and a very small instruction manual. Writing line numbers when you could only see a single line at a time was quite the challenge, but I spent hours writing programs in that thing. Worse yet, it did not have any non-volatile storage, so if you turned the power off, your program was gone. That did not stop me from writing programs over and over, debugging, improving, and just playing with it non-stop. I made monitors, cases, and keyboards out of cardboard boxes and markers, just to pretend. When I saw my first “real” PC at my dad’s work I was hooked. This was the future, and I wanted to be a part of it.

    The idea that I could not just use but control these things was so empowering and enchanting for me. Like actual magic spells that brought my whims to reality. And there was always this intertwined tension between wanting to learn the languages and patterns and efficient ways to write the code, and the actual production of the thing I was trying to build so I could show other people how cool it was. Both brought me so much joy.

    And it has been that way for my entire career. I have always loved the actual writing of the code, the sound of my hands on the keyboard, the diffs in version control, but most importantly the problem-solving challenge and the problems I was solving for my users and customers. I was making lives easier, bringing joy to other people, or making them and myself money. But it always required me to do all of the work: understanding the problem, challenge, or pain point; coming up with a solution; making that solution a reality; debugging and maintaining existing code; building; testing; iterating; showing it to other people; and getting it into production.

    I was at my current job when ChatGPT was first released. It was such a novelty at first, then we started getting scared that our jobs were over. Then we settled into the reality that while these things were cool, they had major limitations. They hallucinated. They could not keep much information in context. There was no way they were going to be able to handle our hundred-thousand-line codebases. We were thinking about their knowledge cutoffs, figuring out how and if they might be useful, and some of us were imagining a future where they could do so much more, but that was not the current reality and was not near term.

    And slowly but surely things improved. We got new models, better tools. When I started at my company we had lots of challenges where we needed to do things at a scale that would mean hiring dozens if not hundreds of people if we wanted to do it fully and repeatedly. There was no way we could hire that many people, much less manage them. But we started building toward that future anyway, thinking that one day it might be possible.

    Then Claude Code came out. The climate started to change. People started saying it could do so much more. I decided to start using it a lot more, letting it write the code maybe 75% of the time. I was still using things like Cursor’s tab completion and writing large blocks of code myself, but I slowly gave it more and more. I was working on large-scale systems, some from scratch, some existing. Large, architectural, ambitious projects, the kinds of things I love and am really known for. And it was handling it all.

    And then, in the last month we got Opus 4.6 and Codex 5.3. These two models specifically have changed the game. They are more thorough, make fewer mistakes, go for longer - they are just all-around capable. My CTO put out a memo to everyone that we should not be writing most of the code anymore, we should be using the agents. They are smart enough, fast enough, thorough enough. Good enough. I was relieved; I had already decided to try and go all in.

    And now I have not written more than a dozen lines of code by hand in the last month. Codex writes my code, helps me plan, manages git, creates my commits, pushes my PRs, gets second opinions, responds to review comments and addresses them, manages my stories, just about everything, just with my control and guidance. I am shipping more than ever, getting more done than ever, while writing virtually no code to do it. I am talking to an agent like I would talk to a colleague in Slack. I am writing in languages that I am not nearly as fluent in, but it does not matter. I am still shipping quality code that does the job as well as if written by an expert in those languages. I’ve shipped over 30 PRs in the 20 working days of February, across four major systems. Features, tests, documentation, bugfixes. It’s a whole new world.

    The New Reality

    In some ways, this world has changed so fast, and in other ways it felt inevitable. I told someone a year ago that before long it will be like we are all engineering managers with full teams of employees. That is really the way I feel now. Across my career we learned new frameworks, new languages, and new patterns, but the fundamental shape of the job stayed mostly the same. This time is different.

    The reality is not evenly distributed yet. At my job, and in my social feed, we are in a bubble where people are pushing these tools hard and adjusting their workflows around them. At the same time, there are many engineers still working mostly the way they worked last year, and the twenty years before that. I am not saying everyone has to move at once, but I do think everyone will have to confront this shift. Within five years the landscape will look completely different. Expectations for output, speed, and scope are going to move. Teams that adopt these tools well will compound output, and that will gradually reset what “normal” looks like across the industry.

    Part of this reminds me of when source control was becoming mainstream. I remember having to proselytize why it was good, a necessary way of working that was worth learning and adopting. I remember people resisting at first because it felt awkward and slow. We take it for granted now, but back then many teams still copied files by hand before making changes. But with version control, nothing fundamental about the job changed. This new way of working is much more revolutionary.

    “Revolutionary” is an interesting and apt word here. In tech, we usually use that word in completely positive terms. Who does not want a revolutionary new product? But actual revolutions are messy and disruptive. There are winners and losers, and there are people very happy with the status quo who will resist as long as they can. This should not be a holy war, and I do not think in moral binaries about it. But I do believe this shift is inevitable. I might be wrong about the exact timeline, but I do not think I am wrong about the direction. We need to start mentally preparing ourselves for what comes next because the economics alone will force the shift.

    There are also new problems we have to solve. When the execution cost drops, teams take on more, and the bottlenecks move. PRs get larger. Review burden increases. Product and design can become the new constraint. The mental split between writing code and supervising/reviewing code gets sharper, and that cognitive load is real. The context switching is easier than it was before, but it still takes its toll.

    I do not want to give the wrong impression: this is not utopia. Agents make mistakes. Quality still depends on tests, review discipline, and engineering judgment. There may be less demand for software engineers in the future. I am concerned about what this means for junior pathways, for hiring, and for the broader economics of software. Businesses themselves will be disrupted by these changes because the economics are changing, and expectations about software are changing with them. I don’t personally believe this is the death of SaaS, but I expect the nature of it to change significantly.

    You may be revolting against this movement because you don’t think AI in general or LLMs in software engineering are actually positive for society. You may be concerned about the energy and water use, the way the data centers and graphics cards and model training are consuming the economy. You may be worried that by participating in it you’ll be somehow culpable if this isn’t the future you want. I understand those viewpoints, and may even agree with you, but for what this means for our reality as software engineers today I just don’t think those things matter. This is the future, and it is the present. We are here.

    A concrete signal: Block laid off 40% of their workforce while explicitly tying that decision to AI-driven changes in how work gets done. I do not think this stays inside tooling conversations much longer. It is going to become a hiring standard. In my next interviews, one of my first questions will be inference budget and agent tooling flexibility, and I expect my fluency with these systems to be one of their first questions for me.

    I want to tell you that it’s okay if you haven’t tried these things yet, or are still using agents in an IDE or tab completion, that’s fine. If you’ve thought that this stuff is changing too fast and have wanted some dust to settle before investing in it, that’s fair. If you already have a full-time job and learning all of this feels like another full-time job, I get it. If you’ve tried these agents in the past and were disappointed, I understand. But it’s time to try again. Things have changed. They’re going to continue to change, but they’re truly good enough to really put to work and a worthwhile investment of your time at this point. If you haven’t gotten started with these technologies yet you aren’t late - you are right on time. But no matter where you are today, there can be a real emotional cost.

    Mourning the Loss

    You may be somewhere in the five stages of grief. Realize that one day you’re going to get to acceptance, but that may take time. It is completely normal if you’re still on an earlier stage.

    If you are a software engineer because you like typing on the keyboard, writing the actual code, and thinking about the control flow of the program through each successive line in a file, this sucks. It does. I certainly think I am one of those people. I have softened my attachment to the actual code over the last decade of working in startups where the usefulness of the thing I write might not last long as we try to find the elusive product market fit, but I have always cared deeply about not just the outcome but also the formatting, organization, and elegance of the code I produced. I tried to leave code better than I found it with every PR. I cared deeply about names, had debates about comments, fought in tabs-vs-spaces wars. I had opinions on the aesthetics of files. I am not happy about leaving that behind - it certainly felt like it mattered at the time. I worry that my ability to read and write code will languish over time, and that somehow that will affect my ability to reason about software itself.

    But if you care about solving problems and shipping, this might be the best thing that has ever happened. In so many jobs in my career I advocated for spending time on our tech-debt column in the project management system. There were so many things we wanted to do but could not find the time for. There was never enough time, never enough people to get everything done. We needed to ship the new feature, fix the priority bug, and do the next thing. Going back to maintain old code, or make that improvement that wasn’t in the critical path, was something you only got to do if you were lucky or you snuck it in. We can do those things now. Most of the time those things were not difficult. They did not need a genius to implement; they just required time we did not have.

    Now you can give your intent and constraints and opinions to an agent that can do those things for you, including a lot of the bookkeeping around the change that used to make it not worth it. How many times have you looked at what would be a change of only a few lines and chosen not to do it because those few lines were actually hours of work when you considered making the story, creating the branch, writing the tests, creating the PR, asking for reviews, iterating on review comments, and deploying it? Those things are materially easier now.

    At the end of the day, we are paid for the things we ship. Shipping has to be the goal. It has been great for the past twenty years that I have been paid to do this thing I love because I was shipping value to customers, but it was the value I was paid for, not the symbols I wrote. But I will mourn the loss of artisanal hand-written code.

    When I am really honest with myself, though, I am happiest when I am productive. That is such a hard word to define, almost an emotion. But when I go to bed at night, I feel best when I feel successful at getting something new out into the world, a desire made into reality. The mechanics of doing that gave me pleasure, but that was never the thing that mattered most. What matters is shipping.

    My Suggestions for How to Get Started

    You may not know how to get started and the options may be overwhelming. If you are curious but still uncomfortable, the answer is staged adoption. Ask the agent to do one contained change, something that you already know exactly how you would do it. Review the diff. Run your tests. See what it got right, what it missed, and where your instructions were vague. Then try again. When you get more comfortable, push it further, give it a little more. Just start talking to it. I think you’ll be surprised.

    The biggest unlock for me was moving from build mode to explicit planning. Before any change, have the agent produce a plan first. Then force it to ask clarifying questions until there is no ambiguity. I often tell it to be relentless about asking questions, questioning assumptions, and raising edge cases. Give it your plan and ask it to elaborate on it and challenge it.

    Trust builds from repetition. At first, keep your hand on the wheel for anything that touches external systems: deploys, migrations, production settings, billing surfaces. Over time as results become predictable you can delegate more of the full loop: branch setup, implementation, tests, commits, PR creation, review response, and cleanup. The goal is not blind trust. The goal is reliable delegation with verification.

    If you only take one practical step this week, make it this: install opencode, launch it in your current project, leave everything as the default, and just talk to it. Put it in plan mode and tell it what you are about to do and see what it thinks. Have it describe to you how a subsystem works. Ask it to find a bug. Just start talking to it and I think you will be surprised. Then, when you get a little more comfortable, get an OpenAI subscription and switch the model to gpt-5.3-codex and ask it to do the thing you were about to do.

    But do not have brand loyalty in any way. Try and choose tools not produced by a single LLM provider (the main reason I like opencode right now). Don’t fall in love and get attached to any one model. Don’t dive too deep into integrations with any one software because a better one will come out tomorrow. This is hard in a lot of ways because it feels like you’re never going to “catch up” much less get ahead, but that’s okay. Part of this new reality is being prepared to learn new things all the time. We are so early. Everything is going to keep getting better. We are still figuring out what these things can do and how to work with them every day. If you can be part of that research that’s awesome, but you don’t have to blaze new trails, use these tools for the value you can get out of them and then replace them as soon as something better comes along.

    You have to change your mindset and judge this workflow by shipping outcomes, not by how much code you personally typed. I still care about code quality and design choices, but I spend less energy on the bookkeeping around changes. That lowers the activation energy for all the little improvements we used to postpone forever. Tech debt gets burned down. Side quests that never seemed worth the overhead actually get done. Use it to be curious - those ideas you’ve had but it would take too long to write out and try. Create a branch, tell it what you’re thinking, and see what happens. Who cares if you have to throw it away - you will have learned something.

    A normal day for me now is usually one core project I am driving end to end, plus smaller improvements running in parallel that would have sat in the backlog before. While I am iterating quickly on the main thread, an agent can be tightening tests in one area, fixing a flaky edge case in another, or preparing a cleanup PR for a naming inconsistency we have ignored for months. None of those tasks are individually hard. They were just never worth the setup cost before. I have the agents read my logs and find problems or opportunities for improvement. I have it check my understanding of subsystems or database tables and where they are used. I treat it like a coworker who already knows all the answers or knows where to find out faster than me.

    I’ll give you an example. Recently my main goal at work has been creating a large system using agents to process and make sense of our data. I am spending the majority of my effort on this, more than enough for a full-time job. Along the way I am seeing bugs or opportunities to improve the UI in other systems, so I fire up another session and tell it what I want and let it cook in the background. Later when I get a break I go and check on its progress, look at the changes and try it out, and tell it to iterate or to cut a detailed PR. I’m shipping several of these side quests a week, things I never would have had time for.

    The agents will make mistakes. You’ll have to be diligent to catch them. Tests and code reviews and actual QA on your part continue to be as important as ever. The agent might write a few thousand lines of code and you’ll realize that it’s gone off the rails and you’ll have to throw it all away and try again. Frequent commits will help. You’ll have situations where you weren’t specific enough or the agent misunderstands you and it goes and does the wrong thing. You’ll tell it to fix a failing unit test and it will delete the unit test (“fixed the glitch” office space style). These are all things you’ll have to navigate and get comfortable with and learn how to avoid. But - it’s worth it, and it’s time to learn these skills.

    Do not expect that the agents will feel amazing when you first start. It takes time to learn how to prompt them the same way it took time to learn to code. You can write subpar code and get results and you can write better code and get better results. Prompting is the same way. It takes practice and experience, and the only way to truly learn is by doing.

    You do not have to stop being an engineer to use this model. You still own the problem framing, architecture decisions, constraints, and quality bar. You are still accountable for what ships. You still need to read every line of code it produces. The difference is that execution becomes collaborative and parallel. In practice, that means you can do more at once, if your team can absorb the review load and process throughput that comes with it.

    There Is So Much More to Say

    Maybe you are worried about the costs, maybe you want more opinions on which LLMs to use, which tools to use, and just more examples of what you can do with it and how to fit it into your teams and your processes and your day-to-day. I hope to write more about these things soon (reach out on Twitter if you would be interested). Don’t believe everything you see on social media about the topic, but do believe some of it. The signal-to-noise ratio is terrible right now, there are lots of voices trying to make money and drive public opinion. Ignore most of them, but when you start to see themes emerge, pay attention. Remember that not everyone is working on the same kinds of things. Devs building frontends and webapps don’t have the same needs as those building operating systems, and so their experiences with the agents won’t be the same. Don’t assume that someone else’s experience applies to you.

    If there is one takeaway from all of this, it is that the leverage is real right now, and the people who learn to direct it well will shape what software engineering looks like next.

    I’m going to leave you with some tweets and articles from the past couple weeks that I think help make my points. I’d love to hear what you think - if you agree or disagree.

    comments
    March 2, 2026

  • Bulk insert into postgres using a single parameter

    motivation

    At my job recently we had a need to bulk insert lots of records quickly and efficiently, but we had some limitations we needed to work around. We have been using Prisma for a while, which has a createMany function to insert multiple rows, but something we have changed or upgraded recently (we aren’t sure what exactly) was causing it to actually insert rows one at a time instead. This isn’t too big of a deal if you are only inserting a few rows at a time, but we needed to insert hundreds to tens of thousands of rows at a time and doing them one by one wasn’t going to cut it.

    We have also very recently started using Prisma’s typedSQL functionality to allow us to write real SQL and still get type safety as well, which is great, except it doesn’t handle a variable number of parameters.

    So I had the idea to instead send a single parameter into the statement as JSON, an array of objects, then parse that into a recordset, and use that to insert into the database.

    example

    Here is what that looks like:

    WITH raw_input(json_blob) AS (
    -- In production you would pass $1 here;
    VALUES (
        $$[
            {
              "id":           "9f2ddc93-0db3-46d2-a2cb-e6df4418faad",
              "key":          "status",
              "value":        "active",
              "created_at":   "2025-06-24T12:34:56Z",
              "updated_at":   "2025-06-24T13:45:00Z"
            },
            {
              "id":           "b8f9152e-a203-4d0a-b530-0a4e45c9b0a9",
              "key":          "priority",
              "value":        "high",
              "created_at":   "2025-06-24T12:34:56Z",
              "updated_at":   "2025-06-24T13:45:00Z"
            }
        ]$$::json
    )
)
-- Expand the JSON array to a proper recordset
, parsed AS (
    SELECT *
    FROM raw_input
    CROSS JOIN LATERAL json_to_recordset(json_blob) AS r(
        id          uuid,
        key         text,
        value       text,
        created_at  timestamptz,
        updated_at  timestamptz
    )
)
-- Bulk-insert the rows in a single SQL statement
INSERT INTO target_table (
      id, key, value, created_at, updated_at
)
SELECT
      id, key, value, created_at, updated_at
FROM   parsed
RETURNING *;

    You can try it for yourself here: https://dbfiddle.uk/Am4xRgbz

    There are only two parts to really understand here:

    json_to_recordset takes a json blob and a schema for the resulting recordset and parses your data into a recordset. You don’t have to reference or use all keys in the json, and if you provide a column that doesn’t have a matching key you will get null for those values.

    The only other thing to know is that you need to use CROSS JOIN LATERAL, this will parse each record individually giving you one row per object.

    And that’s all there is to it. Now we have a statement that takes a single input, but that input can contain thousands of rows. The limit for a single parameter into a statement is an entire gigabyte of data, so you can use this to insert a lot of data at once.

    The difference for us was significant. When inserting 1k records individually it was timing out after 60s. Even inserting in batches of 250 at a time using the previous createMany functionality it was taking 10’s of seconds. This was able to do it in less than a second every time, usually less than a 1/4 of a second.

    Of course you could use this with ON CONFLICT DO UPDATE for upserts, or just as a way to get data into a statement for joining with other data. And you can enrich the json_to_recordset definition with default columns, CHECK constraints, or even a WHERE clause inside the CTE to filter out data early before you insert. Even join with other data in the database.

    full SQL example

    Adding the full SQL example here in case the dbfiddle link ever stops working:

    
create table target_table (id uuid, key text, value text, created_at timestamptz, updated_at timestamptz);

WITH raw_input(json_blob) AS (
    -- In production you would pass $1 here;
    VALUES (
        $$[
            {
              "id":           "9f2ddc93-0db3-46d2-a2cb-e6df4418faad",
              "key":          "status",
              "value":        "active",
              "created_at":   "2025-06-24T12:34:56Z",
              "updated_at":   "2025-06-24T13:45:00Z"
            },
            {
              "id":           "b8f9152e-a203-4d0a-b530-0a4e45c9b0a9",
              "key":          "priority",
              "value":        "high",
              "created_at":   "2025-06-24T12:34:56Z",
              "updated_at":   "2025-06-24T13:45:00Z"
            }
        ]$$::json
    )
)
-- Expand the JSON array to a proper recordset
, parsed AS (
    SELECT *
    FROM raw_input
    CROSS JOIN LATERAL json_to_recordset(json_blob) AS r(
        id          uuid,
        key         text,
        value       text,
        created_at  timestamptz,
        updated_at  timestamptz
    )
)
-- Bulk-insert the rows in a single SQL statement
INSERT INTO target_table (
      id, key, value, created_at, updated_at
)
SELECT
      id, key, value, created_at, updated_at
FROM   parsed
RETURNING *;


-- example so you can see what the parsed data looks like
WITH raw_input(json_blob) AS (
    VALUES (
        $$[
            {
              "id":           "9f2ddc93-0db3-46d2-a2cb-e6df4418faad",
              "key":          "status",
              "value":        "active",
              "created_at":   "2025-06-24T12:34:56Z",
              "updated_at":   "2025-06-24T13:45:00Z"
            },
            {
              "id":           "b8f9152e-a203-4d0a-b530-0a4e45c9b0a9",
              "key":          "priority",
              "value":        "high",
              "created_at":   "2025-06-24T12:34:56Z",
              "updated_at":   "2025-06-24T13:45:00Z"
            }
        ]$$::json
    )
)
, parsed AS (
    SELECT *
    FROM raw_input
    CROSS JOIN LATERAL json_to_recordset(json_blob) AS r(
        id          uuid,
        key         text,
        value       text,
        created_at  timestamptz,
        updated_at  timestamptz
    )
)  
select * from parsed;

select * from target_table;

    comments
    June 24, 2025

  • Precedence Level ordering with Resets using window functions in SQL

    Motivation

    One of my larger projects at Vendr in the last couple years has been a document extraction system. It uses LLM and other technologies to extract structured data from contracts and other documents, allowing us to build a sizable dataset of all of the actual pricing and negotiation for SaaS software for thousands of companies.

    But as we develop our extractions to get new details, or to improve the accuracy of the information, we may extract a document multiple times. We also have human review, correction, and people adding more information from context that may not be in the document. We may be extracting dozens of fields across the extractions, and so we needed a way to determine the precedence of different extractions on a per field per document level.

    The problem at hand: Precedence Level

    There is more that goes into how to choose which extraction is the best for an individual document than what I am going to go into here, the whole process is actually quite involved, but I wanted to talk about a particular challenge to allow us to set a precedence level on each extraction (or change it after the fact), to help guide that process.

    To start with, we needed two precedence levels - DEFAULT and IMPORTANT. We would typically set an LLM extraction as DEFAULT, and human involved extractions as IMPORTANT. An IMPORTANT extraction beats a DEFAULT one, even if the DEFAULT comes after an IMPORTANT, but within the same precedence level the last one wins.

    But then sometimes we may have an advancement in the LLM extractions that would lead us to want to let it take precedence over any previous extractions, even human guided IMPORTANT ones - but from then we would want any future extraction to take precedence again, either a new more recent DEFAULT or a new human corrected IMPORTANT extraction.

    So I wanted to support another precedence level called RESET. An extraction with RESET should beat anything that came before it, but be treated like DEFAULT after that point, with a new DEFAULT or IMPORTANT taking precedence.

    We store all extractions in the database and never get rid of them - we want to keep track of every value that was ever extracted for a field in a document and retain that history, and also we want to be able to adjust the precedence (and other attributes that affect which extraction is the best) at a later date.

    To calculate the best extraction I use a SQL function in postgres - so I needed a way to implement this precedence logic using nothing but SQL.

    The SQL challenge

    Ordering information by precedence level with just a priority (IMPORTANT > DEFAULT) is easy in SQL - you just have to assign a sort value to those levels using a join against those values or a case statement plus a timestamp. But implementing something like RESET means that a normal sort wont work - the ordering is dependent on a timestamp and if a RESET comes before it.

    So to do this, you need window functions - in this case LEAD or LAG - to look at the records before the current row and figure out if there was a RESET preceding it. But not just for the record immediately before it, all of them.

    Here is an example of the query (much simplified and reworked to try and focus on just the precedence level for clarity):

    with data (id, document_id, value, precedence_level, updated_at) as (
	values
		( 1, 1, 'Z', 'DEFAULT', '2025-01-01'::date)
		, ( 2, 1, 'Y', 'DEFAULT', '2025-01-02') -- later DEFAULT beats previous DEFAULT
		, ( 3, 1, 'X', 'DEFAULT', '2025-01-03') -- later DEFAULT beats previous DEFAULT
		, ( 4, 1, 'E', 'IMPORTANT', '2025-01-04') -- IMPORTANT beats DEFAULT
		, ( 5, 1, 'F', 'DEFAULT', '2025-01-05') -- previous IMPORTANT beats this record
		, ( 6, 1, 'D', 'IMPORTANT', '2025-01-06') -- later IMPORTANT beats previous IMPORTANT
		, ( 7, 1, 'C', 'RESET', '2025-01-07') -- RESET beats everything else
		, ( 8, 1, 'B', 'DEFAULT', '2025-01-08') -- bc last record was RESET this DEFAULT now takes precedence
		, ( 9, 1, 'A', 'IMPORTANT', '2025-01-09') -- IMPORTANT overrides DEFAULT
)
, data_with_resets as (
	select *
		, count(*)
			filter (where data.precedence_level = 'RESET')
			over (
				partition by document_id
				order by updated_at asc
			) prev_reset_count
	from data
)
, calculate_ranking as (
	select
		dense_rank() over (
			partition by document_id
			order by
				(prev_reset_count * 10) +
				(data.precedence_level = 'IMPORTANT')::int * 5
					desc
				, updated_at desc
		) as rnk
		, *
	from data_with_resets data
	order by 1
)
, data_with_prev_value as (
	select *
		, lead(value) over (partition by document_id order by rnk) prev_value
	from calculate_ranking
)
, final as (
	select
		dense_rank() over (ORDER BY rnk, updated_at desc ) rnk_final
		, *
	from data_with_prev_value
	order by 1 asc
)
select *
from final
;

    You can play with this query and the parts of it here: https://dbfiddle.uk/HBKDOaR3?highlight=16

    The key parts that make this work start with the data_with_resets CTE. Here we count how many preceding rows are a RESET precedence level using a window for the same document ordered by the timestamp. It isn’t necessarily obvious from this code unless you are familiar with window functions, but when you use count with a window (the over clause) it is a running count up to and including the current row.

    Then the actual logic happens in the calculate_ranking CTE. we use a dense_rank which gives us a consecutive ranking without gaps. We partition by the document and then we order by the precedence level. We add 10 for each RESET, DEFAULT is treated as 0 and IMPORTANT is treated as 5, then ordering by the timestamp.

    The next CTE isn’t required for our purpose here, but allows us to figure out what the previous value was, based on the same precedence ordering, so we can tell if something changed.

    Technically the last final CTE isn’t strictly necessary, but it allows us to get a single ranking field to order the final recordset by.

    Conclusion

    The main takeaway I hope to convey with this post is that SQL traditionally has you think in terms of sets of data which is very powerful, but sometimes you need to be able to work iteratively and consider the previous or next row, or a window of rows not including the entire set. Window functions extend your capabilities of what you can achieve in a single SQL query and using only your database, keeping you from having to do this work in the application layer, increasing performance and lowering your memory and network utilization.

    comments
    January 26, 2025

  • Optimizing Search with Parallel Queries in TypeScript

    Recently, I encountered a situation where I built a search interface for a backend process. Users could search using various criteria—different identifiers or other metadata tagged to the records. However, the query I initially wrote to handle these searches didn’t perform well. It relied heavily on OR conditions or IN clauses (which are logically equivalent to OR), making indexing difficult and query performance sluggish[1]

    In this case, all the different search options were mutually exclusive — if results were found using one criterion, no other criterion would yield results. All our identifiers are UUIDs or globally unique formats, ensuring that only one search method could return valid results. Additionally, some queries were fast and efficient, while others were slower and more resource-intensive. Combining all these into a single query meant the user would always have to wait for the slowest part of the search, even if they were searching for something could return results immediately.

    A Different Approach: Parallel Query Execution

    To improve performance, I decided to run multiple searches in parallel and return results as soon as one of them succeeded. If none of the queries returned results, the system would fall back to showing the latest records. This solution is somewhat similar to Promise.race(), but with a twist. Promise.race() resolves or rejects with the first settled promise, regardless of whether it yields useful data. In my case, I wanted to inspect the resolved values and only return a result if it contained results.

    After working with ChatGPT, Claude, and some friends, I developed the following solution. Below are two versions of the function—one that uses key-value pairs for better traceability and another that works with an array of queries. I have more to say about it below.

    Implementation

    /**
 * Runs multiple queries in parallel and resolves with the first query that returns records.
 * If no query returns records, it rejects with an error.
 *
 * @param queries An array of promises representing database queries.
 * @returns A promise that resolves with the first valid result or rejects if no results are found.
 */
export async function firstQueryWithResultsWithKey<T>({
  queriesAsRecord,
  resultTest = ({ result }) => Array.isArray(result) && result.length > 0,
  noResultsReturn = null,
}: {
  queriesAsRecord: Record<string, Promise<T>>;
  resultTest?: ({ result, key }: { result: T; key: string | null }) => boolean;
  noResultsReturn?: T | null;
}): Promise<{ result: T; key: string | null }> {
  return new Promise((resolve, reject) => {
    let completed = false; // Track if a query has returned results
    let pendingCount = Object.keys(queriesAsRecord).length; // Track the number of pending queries

    if (pendingCount === 0) {
      if (noResultsReturn) {
        resolve({ result: noResultsReturn, key: null });
      } else {
        reject(new Error("No queries returned any records"));
      }
    }

    // Define a function to handle each query independently
    const handleQuery = async (query: Promise<T>, key: string) => {
      try {
        if (!completed) {
          const result = await query;

          // If the result has records and no other query has resolved
          if (resultTest({ result, key }) && !completed) {
            completed = true; 
            resolve({ result, key }); // Resolve with the first valid result
          }
        }
      } catch (error) {
        console.error("Query error:", error); // Log query errors
      } finally {
        // Decrement pending count and check if all queries are exhausted
        pendingCount--;
        if (pendingCount === 0 && !completed) {
          if (noResultsReturn) {
            resolve({ result: noResultsReturn, key: null });
          } else {
            reject(new Error("No queries returned any records"));
          }
        }
      }
    };

    // Start all queries in parallel
    Object.entries(queriesAsRecord).forEach(([key, query]) =>
      handleQuery(query, key),
    );
  });
}

export async function firstQueryWithResults<T>({
  queries,
  resultTest = (result) => Array.isArray(result) && result.length > 0,
  noResultsReturn = null,
}: {
  queries: Promise<T>[];
  resultTest?: (result: T) => boolean;
  noResultsReturn?: T | null;
}): Promise<T> {
  let queriesAsRecord = Object.fromEntries(
    queries.map((query, index) => [index.toString(), query]),
  );

  const { result } = await firstQueryWithResultsWithKey({
    queriesAsRecord,
    resultTest: ({ result }) => resultTest(result),
    noResultsReturn,
  });
  return result;
}

    Both versions of the function execute a collection of promises in parallel, returning the first valid result. If no queries pass the resultTest, the function returns the provided fallback value (if any) or throws an error.

    Example Usage

    Here’s a unit test to demonstrate how the function works:

    const wait = (interval: number) =>
    new Promise((resolve) => setTimeout(resolve, interval));

it("should return the noResultsReturn value if no queries return results", async () => {
    const query1 = async ({ id }: { id: string }): Promise<QueryResult> => {
      await wait(100);
      return [];
    };
    const query2 = async ({ id }: { id: string }): Promise<QueryResult> => {
      await wait(200);
      return [];
    };
    const query3 = async ({ id }: { id: string }): Promise<QueryResult> => {
      await wait(300);
      return [{a: id}];
    };

    let result: QueryResult | null = null;

    result = await firstQueryWithResults({
      queries: [query1({ id: "1" }), query2({ id: "2" }), query3({ id: "3" })],
      resultTest: (result) => result.length > 0,
      noResultsReturn: [],
    });
    expect(result).toEqual([]);
  });

    In this test, three promises simulate query operations. The first two return empty arrays after 100ms and 200ms, while the third returns a valid result after 300ms. The resultTest function ensures that only non-empty arrays pass the validation.

    Reflections and Considerations

    This approach improves user experience by returning results as quickly as possible, but it also increases the load on the database since multiple queries run in parallel. For my use case — an internal back-office tool — I find the trade-off acceptable. However, I plan to monitor system performance to ensure this doesn’t impact the overall system.

    An alternative approach would be to let users specify the type of identifier they are searching for. This way, the system would only execute the relevant query, reducing database load. However, the current solution offers a seamless experience since users don’t need to know or care about the type of identifier—they just paste it and search.

    Although I used the term “query” throughout the article because of my use case, the function is generic and can be applied to any collection of promises, not just database operations.

    I welcome any feedback or suggestions on this approach. While I’m not fully convinced this is the optimal solution, I found the challenge of reimagining Promise.race() to be an interesting exercise.

    Here is a gist with the function and more unit tests so you can see how it works: https://gist.github.com/ryanguill/e89931f9d223e74dabcb070879a58298

    [1] There are ongoing improvements in PostgreSQL to optimize queries with OR conditions. For more, see: https://www.crunchydata.com/blog/real-world-performance-gains-with-postgres-17-btree-bulk-scans

    comments
    October 19, 2024

  • safe_cast() function for postgresql

    I love using JSON in relational databases. When support for JSON types and functionality first started coming out in SQL I generally thought it was neat but that it wouldn’t be something I would ever want to use in production. I could not have been more wrong.

    I could talk at length (and have) about all the ways that it is useful, but if you do you will find that the main way you pull information out of JSON will bring it out as TEXT. And frequently when you’re using JSON you can’t be sure that the data is exactly the right format you expect anyway. Lately I store a lot of JSON that comes back from LLMs, and while it gets it right most of the time, you can never really be sure - you need to trust be verify.

    So I have been using this function for a long time to safely convert from text to a given datatype in postgresql. If the cast can be made successfully it will, otherwise it will return the second argument as a default - most of the time I use null but it can be anything.

    /* 
  utility function to convert from text to various data types
    , such as when you are pulling values out of json 
  The function will cast the value of the first argument 
    to the type of the second argument.
  If the first argument cannot be convert to the target type
    the value of the second argument will be returned as the default.
  If you want to return null as the default, cast the null to the target
    type like `null::integer` or `null::date`
*/
DROP FUNCTION IF EXISTS safe_cast(text, anyelement);
CREATE OR REPLACE FUNCTION safe_cast(text, anyelement)
RETURNS anyelement
LANGUAGE plpgsql as $$
BEGIN
    $0 := $1;
    RETURN $0;
    EXCEPTION WHEN OTHERS THEN
        RETURN $2;
END;
$$;

    The function itself looks a little terse and strange, but if you look and play with the examples you’ll get a better understanding of how it works. I have been using it for a long time, I can’t remember if I actually wrote it or got it from somewhere else - I believe I may have adapted it from this answer on stackoverflow: https://stackoverflow.com/a/2095676/7186

    Below are examples, but you can play with the function and these examples here: https://dbfiddle.uk/3kAop9-Z

    
select
	  safe_cast('true', false::boolean) = true
	, safe_cast('false'::text, false::boolean) = false
	, safe_cast('yes'::text, false::boolean) = true
	, safe_cast('no'::text, false::boolean) = false
	, safe_cast('on'::text, false::boolean) = true
	, safe_cast('off'::text, false::boolean) = false
	, safe_cast('1'::text, false::boolean) = true
	, safe_cast('0'::text, false::boolean) = false
	, safe_cast(1::text, false::boolean) = true
	, safe_cast(0::text, false::boolean) = false
	, safe_cast('foo'::text, false::boolean) = false
	, safe_cast('3'::text, false::boolean) = false
	, safe_cast(3::text, false::boolean) = false
	, safe_cast('', false::boolean) = false
	, safe_cast(null::text, false::boolean) is null
	;

select
	  safe_cast('123', null::numeric) = 123::numeric
	, safe_cast('123.45', null::numeric) = 123.45::numeric
	, safe_cast('0', null::numeric) = 0::numeric
	, safe_cast('-1', null::numeric) = -1::numeric
	, safe_cast('-1.2', null::numeric) = -1.2::numeric
	, safe_cast('123x', null::numeric) is null
	, safe_cast('', null::numeric) is null
	, safe_cast('foobar', null::numeric) is null
	, safe_cast('123', null::numeric) > 1
    , safe_cast('123', 0::integer) = 123::integer
	, safe_cast('', 0::integer) = 0::integer
	, safe_cast('foo', 0::integer) = 0::integer
	;

select
	  safe_cast('2024-01-02', null::date) = '2024-01-02'::date
	, safe_cast('01-02-2024', null::date) = '2024-01-02'::date
	, safe_cast('2024-01-023', null::date) = '2024-01-23'::date --possibly surprising
	, safe_cast('2024-01-', null::date) is null
	, safe_cast('2024-01-123', null::date) is null
	, safe_cast('2024', null::date) is null
	, safe_cast('foobar', null::date) is null
    , safe_cast('2024-01-02', null::timestamptz) = '2024-01-02 00:00:00'::timestamptz
	;

    Other databases have similar functionality built in, but most do not have the ability to set a default if the value cannot be safely cast baked into the function, most just return null and then you can use coalesce or similar to set a different default if you need to. (The following list is the ones I know off the top of my head, and is not meant to be exhaustive)

    MSSQL has TRY_CAST: https://learn.microsoft.com/en-us/sql/t-sql/functions/try-cast-transact-sql?view=sql-server-ver16

    Snowflake has TRY_CAST: https://docs.snowflake.com/en/sql-reference/functions/try_cast

    DuckDB has TRY_CAST https://duckdb.org/docs/sql/expressions/cast.html#try_cast

    comments
    October 17, 2024