I started using ChatGPT when it came out a few months ago. It was mind blowing to chat with a computer and have it feel almost like a real person.

Some people are talking about how it's going to replace all sorts of jobs, including software developers. I'm not sure about that. But I have found some ways that it can definitely make our jobs easier, if we understand how it works and use it cautiously.

Understanding the limitations

Like all Large Language Models (LLMs), ChatGPT has been trained on a massive quantity of text from the Internet. Its basically a function which takes a context as input, including your prompt and the rest of your chat log, up to a limit of roughly 2,000 words. Based on that context, it is trying to make an educated guess of what should come next. Specifically, it's trying to predict what the people who trained it would have voted as the best response.

So when you're using it for coding, or anything else, always keep in mind that it is a guessing machine. Yes, it has been trained with a large amount of information, but not all that information is correct or up to date, and, most importantly, it's very good at completely making things up while exuding confidence.

It's amazing that ChatGPT can run some statistical analysis on words written by humans, and suddenly people are wondering if this thing is going to take over the world (no), take our jobs (maybe), or become self aware (definitely not). Statistically analyzing text becomes an excellent way to imitate humans, and come up with text that looks extremely plausible. GPT probably stands for "Guessing Plausible Text" (j/k).

Unfortunately, in programming, plausible doesn't cut it. We need things to be precisely accurate. It can quickly become frustrating to use an LLM as a tool to help you with programming, because it's wrong so often.

There's still hope. I've found it can still be a very powerful and helpful tool, even for developers.

Researching with ChatGPT

I think a very natural but dangerous way to use ChatGPT is as a search engine, to ask it factual questions. The problem here is that it's like playing "two truths and a lie". A lot of what it says is certainly true, but there's absolutely no way to know which parts are completely made up.

Even knowing this, I find myself using it this way anyway, but with a caveat. You need to treat ChatGPT as if it's your know-it-all friend who will go on and on confidently about any topic, even ones he is actually clueless about. I've learned about lots of new tools and features with ChatGPT, and some of them really did exist!

One trick is to ask for references. This is as simple as adding "Give references." to your prompts, or asking for them after. For coding topics, ChatGPT will usually be able to give you URLs you can click on to specific official documentation, and that is very useful.

Clicking those links to follow-up is absolutely critical here, because very often ChatGPT has told me how to do something using some specific API or function, and it has turned out to have been making it up. These situations did not save me any time, it actually wasted my time.

All that said, I love how ChatGPT can introduce me to all sorts of things I've never heard of before. Searching on Google would have required me clicking on dozens of semi-related pages and skimming through. ChatGPT is excellent at summarizing content, so you can take advantage of that.

Here's where ChatGPT can really shine: Let's say you have some specific software architectural challenge in front of you and you're not sure how to approach it. Open up ChatGPT and write it out in as much detail as you can.

"I need to build on online web-based chat interface. There will be a small number of users, and I'm not sure which database to use to manage this. I'm using AWS for web hosting and I'm hoping to find a serverless solution to save money. I'm familiar with JavaScript and Python. What are some tech stacks I could use for this? Provide references."

Seconds later, you'll have a list of options, some of which you may not have heard of, and links to read more about each one. If there's one you like, or if you have any follow up questions, you can just say "Tell me more about #2". Or you can provide more detail with your specific requirements to refine it's suggestions.

You always need to be careful, because I find that the more specific you get, the more likely you're going to encourage it to make up something that doesn't exist. Always ask for references, and don't make a decision until you've followed up on other websites to verify what ChatGPT says.

Transforming code and text

There are some low-risk and highly effective uses of ChatGPT, and transforming content is one of them. You can paste in some code or text, and ask it to rewrite it in some specific way. In these cases, it seems much less likely to make an error, and if it does make a mistake, you should be able to recognize it and refine your request quickly.

I've pasted in a JavaScript file with two dozen constant strings defined, and asked to convert all the variable names to uppercase. At first it converted both the variable names and the string contents to uppercase, so I had to be more specific and tell it to leave the strings alone. Then it completed it quickly and accurately, saving me a few mindless minutes.

I've pasted in an email from a client with a list of described menu options, plus a snippet of Svelte code with a few placeholders in the menu, and asked ChatGPT to add all the menu options into the code. It handled this very well.

You can ask it to rewrite a short function from JavaScript to Python, and it will do a good job of this as well, though it can make some mistakes depending on the complexity or the length of the code.

If you ever have these sorts of straightforward boring text transformation jobs in front of you, and your IDE isn't up to the job, try asking ChatGPT to do it for you, and save the headache.

Understanding & improving code

ChatGPT is excellent at summarizing any type of content, and that includes code.

Just paste in a chunk of code and it'll be able to tell you what the code does. You can ask it to add inline comments to the code for you too, though Copilot is quite good at this too.

If you get a weird error message, ChatGPT might be able to give you an explanation of why the error might have happened, and some possible ways to fix the error. Unlike a webpage, you can ask follow up questions in realtime and get feedback to help you find a solution.

I've also had success pasting in a freshly written function or module, and asking ChatGPT to suggest improvements. It's told me ways to improve error handling, or some cases I hadn't thought of where things might break. It's even found a few bugs in my code, and showed me how to fix them. If you work alone, it's nice to use ChatGPT for feedback and review, and maybe you'll learn something new too.

Coding with ChatGPT

ChatGPT is very capable of writing code. However, like everything else it does, it often makes mistakes.

In my experience, the code written by ChatGPT is rarely perfect on the first try. Very often, the code will try to do something that isn't possible, or misunderstand what was being asked of it. I guess that's true of code written by humans too.

When you're asking ChatGPT to write code for you, it's up to you to run the code and paste back any error messages or problems into the chat, asking for fixes. In a way, it's like the roles are reversed. You're no longer the programmer, but stuck between the AI and the compiler. I have to say, this is not a very fun place to be. I would much rather just make changes to the code myself, than to try different prompts until ChatGPT is able to generate the right code. Often it's faster to type the code you specifically want and need than to type some prompts and wait to see if ChatGPT has made it correct.

It's almost like working with a junior developer, except that a junior developer is capable of learning and improving and eventually becoming a senior developer. ChatGPT, on the other hand, isn't learning anything from you over the long term. It might learn from you in the short term, but remember, the context of an LLM is limited, and that means that it will soon forget the suggestions you made for improvement.

If, on the other hand, you're new to programming, then ChatGPT is going to be extremely helpful and time saving. I've seen lots of new developers have great success using ChatGPT in this way, to do things they don't know how to do. I believe ChatGPT and similar tools will enable a lot more people to get into coding, and that's really exciting.

Even as an experienced developer, we're always learning new things. Having ChatGPT lead the way and provide feedback in a new programming language or library can be extremely helpful. Just be wary that it's very likely to make mistakes, so you still need to understand what the code is doing. Never trust code written by an AI, just as you wouldn't trust any code you find on the Internet. Ultimately, code generated by an LLM is coming from code from the Internet, security issues and all.

Fortunately, ChatGPT makes some of this easier for you. As mentioned above, you can ask ChatGPT to explain the code it's written, or look for bugs. Sometimes it's worth doing this with the code it just generated. It's kind of funny how that's possible. Since it generates a word at a time, it can't often go back and fix its own mistakes during generation. So if you ask it if it made any mistakes, sometimes it'll be able to spot the mistake right away and write a better version.

Ask for small, simple code snippets

To be honest, I haven't enjoyed having ChatGPT generate large amounts of code for me. It hasn't seemed to saved me much time, it just changed how I spent my time. I've had more success asking it to do smaller, more limited things.

It's really good at writing SQL queries for you. Paste in the table schema and tell it what you're looking to query. You can also be specific about which programming language and library you're using to connect to the database. I think this will be very helpful to a lot of people.

It can also generate things like regular expressions, or other complex code, based on your description. More detail is always better here, including specific examples of edge cases.

Ask it to generate some boilerplate code for you, to give you a head start. Or, paste in the specifications from your manager and have it attempt a first draft for you to use as a starting point. Depending on your skill level, you might prefer to move into your editor and do the rest of the coding from here.

It's important that you're able to quickly test what it generated and verify that it works as expected. You can even paste in some code and ask ChatGPT to generate some unit tests for you. You can use it with Test Driven Development, pasting in some unit tests and ask it to write the code. You can even ask ChatGPT to generate some test code alongside any other code it generates, by including in your prompt something like "Write tests for the code too."

Comparison to Copilot

As I've written about before, I really enjoy using GitHub Copilot, and it helps me to be more productive. Copilot also uses GPT, but it's doing so in a more focused way that automatically takes your code into its context. It's very good at suggesting code while you're writing it, suggesting comments for your code, or generating code based on your comments. ChatGPT hasn't at all replaced my use of Copilot. If anything, it has made me appreciate Copilot more, and encouraged me to use Copilot in more creative ways. I've found myself bringing up the Copilot suggestions panel more often, to see the variety of suggestions available, and very often there are some better and more useful snippets available in here.

For some reason, using Copilot is less misleading. When Copilot makes a wrong suggestion, it doesn't bother me. Perhaps it's because there's no confidence here, everything is just a "suggestion".

ChatGPT is of course better at discussing and explaining things in plain language. Microsoft is already planning to integrate a chat interface into Copilot, so-called "GitHub Copilot X". You can sign up for the beta if you want to get early access to Copilot chat. I'm really looking forward to this, as it'll likely be a lot more useful for coding than ChatGPT currently is.

It's not a human

It's very important to keep in mind that ChatGPT is not a person. It's a statistically-driven guessing machine.

Like a human, it makes mistakes, but it won't tell you how sure or unsure it is about being right.

Like a human, it's trying to generate responses it thinks you'll like, but it has no feelings and will never be your friend.

Like a human, it has biases that it's not aware of and can't articulate, but it's incapable of growing and learning from you over time.

It can be hard to talk to a machine like this without all the baggage we've picked up from talking to real humans. I find myself saying "please" and "thank you" when I really don't need to.

I think we need to create a new place in our brains for interacting with things like this.

It's ok to be a bit blunt and succinct. Often it's necessary to be extra explicit, and state things that might otherwise seem obvious. You don't need to spare the feelings of these guessing machines. You need to tell it whenever it's wrong and ask it to fix its mistakes. You can tell it to "be succinct", to "skip unnecessary phrases" and "just output the code" and other commands which speed it up and tailor the output to your preferences. You may need to repeat these phrases regularly, and you may likely find some new patterns that work well for you.

Try it for yourself, have fun

I've outlined some of the approaches that have worked for me, but I suggest you try it out yourself and see what works for you. I think it's worth experimenting and finding a way for ChatGPT and other AI tools to help you out in your work.

These tools should make your life better, and make work more fun. The goal isn't just to save time, but to enjoy the process.

When you're feeling stuck, you can use ChatGPT as a mentor to help you get unstuck. When you want to bounce some ideas off someone, ChatGPT can give you helpful suggestions.

Save the fun coding stuff for yourself, and leave the boring parts for ChatGPT.

A couple of weeks ago, I bought one of those Continuous Glucose Monitors (CGM). My nutritionist suggested I do this, even though I'm not diabetic or pre-diabetic, just to learn something about myself and how food affects me. I'm always excited about new technology and gadgets, so of course I went out and bought one right away.

I bought the FreeStyle Libre 2 monitor, the only brand available in Canada (as far as I know). It cost $119 CAD, and I was able to buy it from the pharmacy without a prescription.

The monitor itself is a small thin disc with a tiny needle in the middle. You install it into the back of your arm with an applicator. I was nervous, but it never hurt at all. It has a strong adhesive, like a bandage, so there's very little chance of it falling out accidentally. It stayed put for the full 14 days, though I felt I had to be a bit careful with it.

Once installed, you can sync and activate it with your smartphone, by installing the app. The monitor syncs to your phone with NFC, so basically you can tap it as often as you want to get your current blood sugar. You'll also see a line chart of what happened through the day on a minute-by-minute basis.

After having kept a close eye on it for the last two weeks, I now have a way better understanding of what my body feels like when the glucose changes, and how to keep it level. Here are a few things I learned from it.

Note: I'm fairly healthy and don't have diabetes or pre-diabetes, and these are just hunches, so may not be true or apply to you. Factors like sleep, exercise, hormones, and your overall health can have a big influence on your blood sugar. The only way to learn about your own body is to try this yourself. Still, I'm hopeful some of this will be at least interesting to some of you.

1. Eating a sugary or high carb meal (pizza, oatmeal, half a bag of doritos) makes my glucose quickly spike up high and then drop sharply, dropping lower than my average. My understanding is this is because insulin was released, which allows the glucose to move from the blood into the cells, and excess glucose is converted into fat.

2. Right after a high spike, I often drop too low and feel like snacking shortly after eating even though my body just had an excess of energy. So I'm probably storing that glucose as fat, and then end up eating more than I need. The takeaway here is that I'm going to try to limit half my meal to carbs, and add in a salad or some protein to balance things out.

3. Foods with a balance of carbs, fat, protein and fibre will make the glucose go up slower, not as high, stay up longer, and come down slower, ending up closer to my average, so I'm not hungry afterwards. By limiting the level of refined carbs or sugar in a meal, there's a better chance I'll feel fuller for longer, and be able to go longer without snacking afterwards.

4. Whenever I'm feeling hungry, I've probably dropped below 5.0 mmol/L. It's interesting how clearly blood sugar coincides with the desire to eat.

5. Whenever I'm feeling starving or woozy, I've probably dropped below 4.0 mmol/L. This happened a few times when I exercised about an hour after eating a high-carb meal. While I'm crashing from the spike, my insulin and physical activity are both drawing down glucose quickly at the same time, and it ends up going too low. I now avoid eating a high carb or sugary meal (eg. oatmeal) before exercising.

6. I never felt any of my sugar spikes. The highest I got to was 11 mmol/L after eating pizza. Usually my spikes are around 9 mmol/L. Hyperglycemia starts around 10 mmol/L. Hyperglycemia can damage cells and increase insulin resistance, and over time can contribute to developing long-term health problems such as cardiovascular disease and type-2 diabetes.

7. Meals with fewer carbs might barely go up at all. Even a single slice of bread or pizza, or a handful of chips seems to have very little impact. This shows how important portion control is. By avoiding carbs altogether, it was possible for me to stay basically flat all day, though I don't know how necessary or healthy that is.

8. It's really interesting how a lot of advice I hear all the time now makes more sense in a tangible way. Eat smaller meals (shorter spike). Try intermittent fasting (more time between spikes). Avoid sugar, especially sugary drinks (very sharp spike). Eat light before exercising (so you're not crashing after a spike and dip into danger zone). Etc.

9. I now suspect that a lot of times over the years when I've felt really off without explanation were due to crashing below 4 after a spike.

10. As a test one night, I ate a tremendous amount of pasta for dinner. Surprisingly, this didn't make me spike or crash sharply! But what it seemed to do was keep me at a rather high baseline. Somehow, I was at the highest hours later, right before bed (8.3 mmol/L). When I woke up the next morning it was still quite high (6.6 mmol/L, in the pre-diabetic range for a fasting glucose)! This was just a one-off so there could have been other factors at play (exercise, stress, etc.) but I feel like it was probably mostly the pasta.

Overall, I was really glad I tried this out. I think it's absolutely worth the cost, because it has given me tangible first-hand experience with a lot of things I already knew in the abstract. I see it as a one-time educational thing that'll ideally pay benefits over the next decades. I believe it will help me make better choices and hopefully avoid problems with my health over the long run.

I might try it again down the road to see how I've changed. Or maybe I'll get one of those finger prick glucose monitors to spot check when I'm feeling strange.

The biggest change I've made after all this: I don't put sugar in my coffee anymore. This was adding two sharp glucose spikes early in my day every day, so cutting those out was a quick win. Now, I tend to stay relatively flat through the day with a moderate, slower increase after dinner.

---

Here are two line graphs, one from my best day, and the other from my worst day (the first spike was oatmeal, and the dinner spike is from pizza, and apparently it even broke the monitor!):

For comparison, here are two sample days sent to me from RevK who is a diabetic:

I moved codingwithjesse.com to Cloudflare Pages this week!

I was having some intermittent outages on my website in the hours before I wanted to publish my last blog post. Since I wanted to publish immediately, but didn't want to send people to a broken site, I decided I'd finally try out hosting my website on Cloudflare Pages. It went so smoothly, I was able to get it working in under an hour and publish the blog post!

First of all, you should know that Cloudflare Pages works easiest with a static website. It's very possible to run websites with server-side code using Cloudflare Workers, but my website is static, so I didn't need to worry about that. (I plan to move some other sites to Cloudflare Pages and Workers later, so I'll probably do a write-up of that when I do!)

At the time of writing this, Cloudflare Pages is free, with unlimited sites, unlimited requests, and unlimited bandwidth, with a limit of 500 builds per month, but you should double check the pricing page as it may change in the future.

Setting up Cloudflare Pages in an emergency

I didn't have time to figure out the best way to use Cloudflare Pages, and wasn't totally sure I'd want to stick with it, so I did the easiest possible thing I could find.

All I wanted to do was somehow upload a zip file of my website and have it be hosted on there. I didn't know if that was possible, but I was eager to figure it out. Here's how I pulled it off in record time.

1. I logged on to the Cloudflare dashboard and clicked Pages in the side nav. I clicked the "Create a project" button, and chose the "Direct upload" option. Perfect!

2. Cloudflare asked me to create a name for my project. I chose "codingwithjesse" and clicked "Create Project".

3. I clicked "select from computer" and chose "Upload zip" to browse to my zip file and upload it. Easy!

4. After a while (I have 600+ pages on my site, and it took a few minutes), it was ready, and I could click "Deploy site". Success!

5. I was able to see my new site at codingwithjesse.pages.dev and verify that everything looked good. I did have to wait a few minutes for the DNS to propagate and the subdomain to show up, but when it did, it looked perfect.

6. Returning to the newly created project, I had to click on the "Custom domains" tab and the "Set up a custom domain" button, so that I could map www.codingwithjesse.com to this new subdomain.

7. Since I already had my domain on Cloudflare, all I had to do was confirm the new DNS records and it was ready to go!

If you're new to Cloudflare, there will obviously be other things you have to do to get set up here. But it's also possible to use Cloudflare Pages without using Cloudflare DNS - you'll just have to manually set up the CNAME records in your DNS provider. Don't worry, Cloudflare walks you through that process.

Deploying to Cloudflare Pages the easier way

The zip file approach worked great as a first test, and I actually used the same zip upload method a dozen more times as I made small edits to the site. But that got tiring, so I wanted to figure out how to deploy my changes automatically and programmatically from my command line. Turned out this approach was just as easy as using the dashboard.

Cloudflare's command line tool is called Wrangler. This tool is how you can easily interact with Cloudflare and deploy to Cloudflare Pages.

To get it working, I needed to have two things in environment variables: an API key, and my Account ID.

I went and set up an API key that only has access to Pages on my Cloudflare account. I went to the API Tokens section of the Cloudflare dashboard, and created a new token. I added only one permission to the token: Account > Cloudflare Pages > Edit.

I also copied the account ID from my dashboard to use in the environment variable.

I had to run CLOUDFLARE_ACCOUNT_ID=theaccountid CLOUDFLARE_API_TOKEN=thisisthetoken npx wrangler pages publish ./build, telling it to upload all the files in my build directory. It asked me if I wanted to create a new project or use an existing project. I chose "Use an existing project", and was able to see my "codingwithjesse" project right there to select it.

It uploaded the files, and Success! It gave me a temporary deployment subdomain where I could verify that the changes I wanted are correct. Uploading this way was much faster, as it only had to upload the files that had changed.

This actually didn't update my production site. To push directly to production, and to skip the question about which project to use, I had to run CLOUDFLARE_ACCOUNT_ID=theaccountid CLOUDFLARE_API_TOKEN=thisisthetoken npx wrangler pages publish ./build --project-name codingwithjesse --branch main

Making a private bash script

You shouldn't put your API key in your git repo, so make sure you don't put it into your package.json or commit it anywhere by accident.

To avoid this, I usually create a simple bash script push.sh which is in the root of a lot of my projects. I add push.sh to my .gitignore so it won't be committed by accident. The contents are simply like this:

#!/bin/bash

npm run build

CLOUDFLARE_ACCOUNT_ID=theaccountid CLOUDFLARE_API_TOKEN=thisisthetoken npx wrangler pages publish ./build --project-name codingwithjesse --branch main

You'll have to run chmod +x ./push.sh to allow it to execute. After that, you can build and push the site just by running ./push.sh.

There are other ways to manage your environment variables and secrets, but this is the approach that works well for me for a lot of projects.

Lots of possibilities

Cloudflare Pages can integrate into your GitHub repo and other deployment pipelines, so that whenever you push your changes live, it'll build automatically. This doesn't work for me for this blog, because the content is in a database and doesn't live in my git repo, but might be a good option for your project.

If you're interested in learning more, check out the Cloudflare Pages documentation. There are examples for pretty much every framework out there, so you should have no problem figuring out the best way to deploy your static site.

I got an email today from one of my clients, letting me know that one of his web apps was down. He said he was getting an error and asked me to take a look.

In the end, I was able to fix it and get it running faster than ever. What caused it turned out to be a huge surprise to me. I thought I'd outline the steps I went through here, to try to help others trying to solve similar problems in the future.

See for yourself

The obvious first step is to go see for myself. I loaded up the web site, wondering what kind of error I would find. The site initially loaded fine, but then a spinner would appear, and seemed to get stuck. After a long while, the main content of the site failed to load, and an error appeared about failing to parse JSON.

I opened up dev tools and refreshed, keeping an eye on the network tab. Most things were loading fine, including static assets. I noticed that there were some fetches to the web site API that were taking a long time. They eventually failed with a 504 gateway timeout error.

The web site is behind a load balancer, and I know load balancers generally have a timeout limit around one minute. So all I knew is that these API calls were taking longer than that. I could only assume they would eventually succeed and were simply slow, but I wasn't totally sure.

Try to reproduce

Fortunately, I already had a dev environment for the site set up locally. I didn't write the whole application, but I had recently made some performance improvements to parts of the site. I wasn't very familiar with the API side of things though.

Sure enough, it started up fine, and the data all loaded correctly. So I figured it probably wasn't an issue with the code itself.

I started to wonder what could have happened to break the site all of a sudden, when it had worked fine in the past. Did the server auto-update some dependency that was breaking something? Was the server out of disk space? Was the database out of memory?

Getting close to the metal

My next step was to actually ssh into one of the servers to try and see what's going on there. Everything seemed ok. I ran free -m to check on the memory, but the RAM usage was fine. I ran df -h to check on disk usage, but none of the disks were full. Running top, the CPU usage looked fine as well. I was a bit stumped.

I turned to look at the database. This site is running on AWS, so I logged on to the RDS admin in the console and checked the graphs in the monitoring tab. Everything seemed fine there too. CPU wasn't too high, there were barely any connections, and the database wasn't out of space.

Still, I knew these API requests were hanging on something. I went back to the code and looked at the API endpoints in question, and all they did was make a database query. At this point I was pretty sure it was database-related somehow.

Going into the database

I decided to log in to the production database using the mysql command-line tool. The database is not accessible to the public, so only the production web server has access. I'd never gone into there before, so I looked at the config file for the server-side application to find the credentials (hostname, username, password and database name).

This is a MySQL database (MariaDB actually), so once I got in, the first thing I ran was SHOW PROCESSLIST to see if there was anything there. There were a ton of queries in there, many of them had been running for more than a minute. One had been sitting there for almost an hour!

Optimizing queries

Finally, I found the problem. All the slow queries were working with a single table. There was a mix of SELECT and UPDATE statements, so I figured the table was probably missing indices or something, something to make the queries run slowly.

I called SHOW CREATE TABLE xyz on the table, to see the structure of it. I was wrong, there were lots of keys on the table. Knowing that MySQL will only use one key per query, my first guess was that the problem was that maybe there were actually too many keys on the table, and the table would benefit by having fewer keys with multiple columns in it instead, targeted at these particularly queries.

I tested my theory by hand writing a simple query to see how slow it would be. It was slow, but it only took about one second, not a minute. So it wasn't that.

I wondered if I was missing something. Calling SHOW PROCESSLIST shows a summary of queries, but it cuts them off. A quick DuckDuckGo search later, and I found out you can call SHOW FULL PROCESSLIST to see the entire query.

It was then that I discovered what the problem was. The query was written exclusively using LIKE statements instead of =, eg.:

SELECT *
FROM xyz
WHERE thing_id LIKE '12345678'
AND status LIKE 'ok'

Even the update statements used LIKE:

UPDATE xyz
SET status = 'ok'
WHERE id LIKE '12345678'

I found it unconventional to say the least. But was that really causing the problem?

I changed my hand-written query to use LIKE instead of =, and sure enough, I had to Ctrl+C to abort it after a long time of waiting.

I realised that yes, of course, this would slow things down. MySQL must be scanning the entire table, converting the IDs from numbers to strings, and doing pattern matching on each one. No wonder it was running so slowly!

Closer to a solution

I searched the code base for "LIKE" and found the cause. Buried in a custom query builder a past developer had assembled, it would only use either LIKE or IN for all query parameters, including in UPDATE statements.

I'm not totally sure what the developer was thinking here. Were they making it so you could search on any field easily? I'm not sure, because I wasn't able to find an example where fields were actually searched on anywhere. We may never know.

The problem was in the code base

I was surprised the problem actually was in the code base itself. It made sense to me, this isn't the kind of problem that would have shown up locally, or even when the site first launched. It would have grown slowly as the table size grew, and apparently only became a major issue once the table had over 750k records in it.

The solution seemed straightforward to me. I modified the API endpoints that used this table, and rewrote the queries directly instead of using this query builder code. (Side note: I've never liked query builders, and this is an excellent example of why!)

I would have liked to modify the query builder to replace LIKE with =, but because I'm not sure if that functionality was needed elsewhere, I thought it best to leave it alone, and migrate away from the query builder instead.

Ship it!

Last step was to commit and push the code, and rolled out an updated version of the system. Shortly after the new version went live, I logged in to the database again and ran SHOW PROCESSLIST. Nothing but a bunch of idle connections! Perfect!

I went over to the AWS admin panel, and sure enough, the "Read IOPS/second" chart had dropped from a steady 100 down to 0! That was a nice reassurance that things were massively improved.

The site wasn't just working again, it was faster than ever!

Lessons learned

There are always lessons to be learned from any outage. Here are a few I learned today:

1. You should definitely not use LIKE in your database queries to match numeric, indexed IDs. I've never done this, but now the fact has been ingrained deep in my brain.

2. You probably shouldn't write your own query builders. Again, I've never liked query builders so this just reaffirmed my belief.

3. You should maybe think about testing your web apps with a very large amount of dummy data in the database sometimes. I've never done this, as it's slow and seems a bit excessive, but I think I may start trying this in the future, particularly on systems where I expect the tables to grow enormously over time.

4. SHOW FULL PROCESSLIST is a thing. Okay, that's not so much a lesson as it is something I learned that wasn't already in my tool belt.

All in all, the whole process took about an hour, and I'm glad I was able to get things back up and running quickly. Beyond the lessons learned, I got to know the system a little better, and have a lot of new ideas of ways I can improve and speed things up in the future, so it was definitely a worthwhile experience!

When you're building a new web application, or even a new feature, how can you ensure that you're not creating a nightmare code base that will need to be rewritten completely in a few years?

Some people will say it's hopeless to even try and write code that will last. I've even heard people suggest that you should aim to rewrite all your code every few years. That sounds like a very expensive, wasteful strategy.

In two decades of building web apps, I've seen many codebases start as a shiny new prototype and grow into a huge system. In some cases, they've become old, ugly, painful legacy systems that teams are begging to rewrite and replace. (And often those rewrites will themselves grow into ugly, painful legacy systems!) But sometimes a codebase will remain more or less unchanged a decade later, running smoothly as ever.

I believe there are some decisions you can make when writing code that will help it to last longer, and withstand the test of time.

Change is inevitable

Probably the one thing you can be sure of is that change will come. The goals of a business will change, and the people within a business will change. There will inevitably be features added, and existing features will evolve and be repurposed. The names of the products will almost surely change. So is it even possible to write code that doesn't need to change?

I think the key is in the phrase "If it ain't broke, don't fix it". Code that is fulfilling its task, that is doing what it's supposed to do, and is bug-free, is code that will last a long time.

Nightmare code

To understand how to write code that will last, let's think about the opposite: a nightmare codebase that demands to be rewritten. The worst I've seen is a web server written as a giant single file with thousands of lines of code. A system built like a house of cards, where changing one thing will break everything. Code that is very difficult to read or understand. Code that literally gives developers nightmares.

Unfortunately, this is often the kind of code that comes out of throwing together a quick prototype. A hero developer stays up late one night and churns out a first draft like a stream-of-consciousness. The next morning, the business owner is delighted to see their dreams come to life. Everyone's happy.

Then, they ask to change just one thing. Add this little feature. And this other feature. And now this user needs this other thing. And could you just change that other thing quick?

Months later, and this rough draft has accidentally become the foundation for a web application that continues to grow, held together with digital duct tape.

So how do you prevent this nightmare from unfolding?

Do one thing, and do it well

Modularity is extremely important in writing code that will last. And a good module is a piece of code that does one thing, and does it well. That one thing might be interfacing with a single database table. Or it could be handling HTTP calls on a single URL and passing the data to and from other modules that talk to the database.

I find generally that it works best when each module has zero, one or two major dependencies. With zero dependencies, you have a set of functions that receive input data, process it in some way, and return results. With one dependency, you have a set of functions that act as an abstraction or interface to that dependency. With two dependencies, you're writing code that bridges the gap between the two, acting as an adapter or controller.

More than two major dependencies, and you should ask yourself if there's any way to split things up into smaller pieces that are responsible for fewer things.

A dependency might not always be a program you have to install. Another module in your system is also a dependency. So is your business logic. I think about dependencies as anything that your module "knows about". This could even be the shape of certain data structures that might not have explicit type definitions.

The fewer things a module knows about, the more likely the module will be able to persist unchanged over time, because there will be fewer reasons to change it.

When your web application is built with small, independent modules that only do one thing, the chances are much, much lower that any of those pieces will need to be rewritten. And the chance of the whole application needing to be rewritten all at once drops to nearly zero. Even if you later want to do a major redesign, you'll find it easier to copy over lots of these older, simple modules to reuse in the new system.

Finally, a tangible example

Let's say you need to send out a Forgot Password email. You could do the whole thing in one file, but I would prefer to split it up like this:

1. A module that knows how to actually send an email using AWS SES or something, but doesn't know the recipient, subject or body of the email. function sendEmail(toAddress, subject, body), for example.

2. A module that knows about the subject and body of the Forgot Password email, but doesn't know who it's sending to or what the reset URL will be. function sendForgotPasswordEmail(toAddress, resetUrl)

3. A module for the user table in the database, that has a function to generate a reset code, but doesn't know how the reset code will be used or even whether an email will be sent out. function createResetCode(userEmail)

4. A module that knows about the URL structure of the site, and has a function that can generate a password reset link from a reset code. function getResetUrlFromCode(code)

5. A module that ties everything together. It takes an email address, calls createResetCode, uses that to call getResetUrlFromCode, passes that to getForgotPasswordEmail, and sends the recipient address and email body to sendForgotPasswordEmail. forgotPassword(email)

6. A user interface widget with a form, a text field and a button, so the user can type in their email address and click Send password reset link. When the form is submitted, it tells the user to go wait for the email.

7. A module that is responsible for the server-side password reset part of the system. It receives the form submission, pulls the email address from the form data, calls forgotPassword, and then sends a success status back to the browser.

Here, only a few modules are likely to change. You'll probably see changes to the sendForgotPasswordEmail function, as well as the user interface widget. All the other modules I've outlined are very reusable, and highly unlikely to change, unless you change your email sending provider, or your database software, or something else major. Even in those situations, the code that needs to change is very isolated and easy to replace without affecting anything else.

You can even improve on this further, by having the contents of the email be database-driven, so that non-technical staff members can change the email templates themselves through an admin interface. But an architecture like this is a good starting point that makes those sorts of changes simpler to make.

A good start

If you get in the habit of writing more modular code, and splitting things up as early as possible, then the next time you're throwing together a quick prototype, you'll be able to lean on those principals in the process.

Instead of a giant ball of tangled dependencies and logic, you'll be building smaller, simpler, reusable components that can be used as solid building blocks. Some of these will be so useful and generic that you'll even be able to reuse them in completely different systems without changing them at all.