This week has been one of those weeks were nothing has really worked properly, and I have spent more time fixing bugs and production issues than actually coding.
On the positive side, it has also meant that I have learnt a lot in the process and should any of these issues come up again I will know how to solve them. For my benefit, and hopefully for yours too, I thought I would share what I have learned this week.
Postgres Performance Issues #
After some minor query changes one of our queries went from taking less than 1 second to run to taking 16 seconds. After making sure the query was correct and there weren't any unnecessary joins that could be causing the issue we checked the indexes and even added an extra one with no effect.
The next step was to take the query and run it in pgAdmin with the same parameters. Annoyingly the query came back almost instantly in under 1 second but still took 16 seconds when run through the API.
After checking all the inputs I managed to find the issue.
It turns out Postgres is very particular with data types. The issue was caused by a currency input which was cast to a text type @Currency::text. The underlying field in the database however was a character(3) not text so the index wasn't being used correctly.
Casting the input to bpchar or even varchar solved the performance issue.
When going through the code I found a few other odd issues like this such as joining tables to CTEs and the joining field having a subtly different data type.
When you run a query with Dapper and Npgsql it will have its own default data types that it will pass in so it is important to cast the inputs to what you expect them to be.
This is one reason why I always prefer using something like Dapper where I can write the query myself rather than relying on what something like Entity Framework writes for me and having no option to fix it.
Postgres Storage Issues #
It was a heavy database week this week. I noticed our development database was running out of storage space and I had to up the storage from 32 GB to 64 GB. The thing is, this is only used for test data, so the database really shouldn't be that big.
I checked the database size, and it was just 25 MB , so what on earth was using all the space.
We are using the Azure Flexible Postgres database, and we had set up a read-only replica. All has been working well for the past 6 months but after looking through all the monitoring I noticed that the replication lag in the last week had gone from less than 2 seconds to around 6 minutes.
After doing some Googling (DuckDuckGoing in my case) I ran the following query on the database:
SELECT
slot_name,
slot_type,
active,
restart_lsn,
pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) as retained_wal_bytes,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as retained_wal_size
FROM pg_replication_slots;
This showed that the replication slot was inactive and the retained_wal_size was 29 GB. Again looking at monitoring I could see that the Transaction Log Storage Used (which includes the WAL) had slowly been increasing for the past week starting just after the lag started.
I tried spinning up a new replica and got errors saying that the primary database and replica database couldn't communicate.
It turns out the issue was caused by some overly restrictive network security group (NSG) rules that were blocking access between the 2 databases. Azure had switched the replication to file based replication rather than streaming which was causing the delay and the WAL to keep increasing.
Given the replication was still working I would have thought the WAL would stay constant instead of growing linearly forever. After fixing the NSG the replication lag disappeared and the WAL returned to normal.
SwiftData oddities #
I have still been working on my habit tracking app that I showed off last week. While testing, I noticed a big bug with my soft delete function. After deleting a habit it would disappear as expected, but after closing and opening the app it would reappear.
After a bit of debugging (hello print statements) I noticed that the deletedDate was being set correctly but the isDeleted flag was not.
I changed my code to use the deletedDate as the filter and all was working fine. So why did isDeleted not work.
Well it turns out that isDeleted is a system instance property along with hasChanges. Rather than the compiler telling me I was using a system property it just decided to ignore it!
I am going to have to remember that one!
❤️ Picks of the Week #
📝 Article — World's first 'body in a box' biological computer uses human brain cells with silicon-based computing — I am pretty sure I have seen how this movie ends! It will be interesting to see how well this performs, but it could pave the way for a new form of computing.
🛠️ Tool — Sidekick: Local-first native macOS LLM app — I think the future of AI is definitely going to have to be locally run AI models. It is great to see that people are already coming up with tools like this.
📝 Article — Switching from Pyenv to Uv — I have been using Python more as I have been exploring some data tools like dbt. Python package management and virtual environments seem like a mess. Uv seems to be fixing a lot of this and is worth a try.
📝 Article — With AI you need to think bigger — As I showed last week, nearly anyone can make an app in weekend using AI. It does mean we need to be a bit more ambitious with our projects as the bar is a lot lower now.
📝 Article — What makes code hard to read: Visual patterns of complexity (2023) — I prefer more verbose code to a 1 liner that are you going to struggle to understand after a few weeks have passed. It is interesting to see there are some static analysis metrics that could be used for this.
📝 Article — Here’s how I use LLMs to help me write code — If you want to code a whole project with an LLM it helps to be good at writing product specs. If you can write a detailed project spec with enough technical information that a developer could use then you can go far with LLM coding tools like Cursor.
📝 Article — I use Cursor daily - here's how I avoid the garbage parts — There is some good advice in here if you are just getting started with coding with LLM.
🛠️ Tool — Peer-to-peer file transfers in the browser — The internet started as a peer-to-peer network and I always feel like it is underutilised technology. Of course, it is difficult to make money off something when you cut out the middle man.
🛠️ Tool — The DuckDB Local UI — I have heard good things about DuckDB and need to give a try in my next project. This local UI they have added looks really nice.
👾 Game — Time Portal: Get dropped into history, guess where you landed — I enjoy playing GeoGuessr and this is nearly as fun, although I must admit my history knowledge isn't that great.
📝 Article — Cursor told me I should learn coding instead of asking it to generate it — Haha this is funny. I wonder what the code was that prompted this response. I am sure there is some snarky StackOverflow comment that is very similar.
📝 Article — Fast and random sampling in SQLite — SQLite is another DB technology that I need to use more of. I always end up sticking to what I know (mainly Postgres, MS SQL and MySQL) but SQLite is perfect for many projects.
🛠️ Tool — Briar: Peer to Peer Encrypted Messaging — Messaging is perfect for peer-to-peer, at least if you can get around the not always online issue. This looks like an interesting project that could get around mass government surveillance.
💬 Quote of the Week #
One helpful rule of thumb for knowing where you might have a little extra talent is to consider what you were obsessively doing before you were ten years old. There's a strong connection between what interests you and what you're good at. People are naturally drawn to the things they feel comfortable doing, and comfort is a marker for talent.
From How to Fail at Almost Everything and Still Win Big by Scott Adams.
Top comments (0)