Snapshots, JWT, SSD Death, SQL

1. Snapshots (@ Wikipedia): Wrote bash script that generates new snapshots on all disks with YYYYMMDD-HHMMSS name in snapshots directory on shutdown or daily, because it's fast enough. If shutdown based mode, it deletes the snapshots 5 - 15 minutes after system start, if there are too many and if in daily mode, it just deletes snapshots which are older than specified time in days. Configuration allows setting the retention times / counts per source sub-volume. Makes it very easy and quick to recover oh, I messed up something situations, because you've got snapshots where you can recover stuff from. - This is also the first time I built these as Ubuntu Services, it took some extra effort to get everything working smoothly, but now it's good! - Yeah, naturally using btrfs for this.

2. SSD (solid-state drive) (@ Wikipedia) Death: One of my systems probably had power source / motherboard issues, or something like that. The system started to power off randomly, and after that had been going on for a while, the SSD died as well. I guess the SSD wasn't properly protected for power loss. I've seen so many flash cards to get corrupted, when the internal FTL (Flash Translation Layer) (@ Wikipedia) writes fail / partially succeed with varying voltage / power loss. Most of SD cards can be recovered with formatting, but in this case the disk got bricked. Well, that's it. New build, finally!

3. JWT (JSON Web Token) (@ Wikipedia): Tuning stuff with JWT. Some sources say that you can fully trust JWT information, but I really don't want to do that. I preferred to assign unique cryptographically secure and random session / user identifier, instead of using something like database id directly. If the database id is used directly and there's issue with JWT signature verification then attacker could impersonate whatever user they ever want trivially. - This all seems trivial and logical, but so many documents on internet just simply use user id without any extra security layer. - RS256 / HS256

4. SQL (Structured Query Language) (@ Wikipedia): Colleagues had huge issues with SQL Server deleting lots of stuff and table locking, and writing huge journals. Yeah, for sure nothing new. Just that some huge deletes might take weeks to run in small snips with slight sleep in between. Which can be bit longer than expected by many operators. - I've also helped them doing lots of fine tuning, optimizing old SQL queries, to improve performance when some data sets grow a lot. Resumed below...

SQL FUN [NOT IN, NOT EXISTS, JOIN NULL]

Funny and long discussion, if using NOT IN, NOT EXISTS or JOIN NULL works best. After all those didn't make any meaningful difference. Yet it's interesting to notice that the query plans for all of these tasks were slightly different, even if I would have assumed that SQL Server mangles all of those to the same, because in this case there results were same. Delete all unreferenced Items. Interestingly when asking from experts and different AIs results vary. And there's no consensus if and is some of these options is the ultimate correct way of doing this. Also different SQL applications, and data structures and cases could lead to different results. The good old answer, what's the best way of doing it? Well, it depends. Run all of these options and go and figure out which one works best for you.

As well as some of the situations to consider where cases where 99% of rows are to be deleted. Then it's faster to create a new table, copy remaining stuff to it and drop old and rename the new table in place. Depending on the database engine and it's storage, this also might or might not, do compaction / vacuuming for the data, if table specific database / index files are being used. Again, it depends if this is the solution or not.

Option "NOT IN" query plan:

StmtText

  |--Clustered Index Delete(OBJECT:([Item].[PK__I__] AS [i]), OBJECT:([Item].[ItemKey] AS [i]))

       |--Nested Loops(Left Anti Semi Join, WHERE:([Item].[Id] as [i].[Id] IS NULL OR [Item].[Id] as [i].[Id]=[Header].[Id]))

            |--Index Scan(OBJECT:([Item].[ItemKey] AS [i]), ORDERED FORWARD)

            |--Index Scan(OBJECT:([Header].[Key2]))

Option "NOT EXISTS" query plan:

StmtText

  |--Clustered Index Delete(OBJECT:([Item].[PK__I__] AS [i]), OBJECT:([Item].[ItemKey] AS [i]))

       |--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([i].[Id]))

            |--Index Scan(OBJECT:([Item].[ItemKey] AS [i]), ORDERED FORWARD)

            |--Index Seek(OBJECT:([Header].[Key2] AS [h]), SEEK:([h].[Id]=[Item].[Id] as [i].[Id]) ORDERED FORWARD)

Option "JOIN NULL" query plan:

StmtText

  |--Clustered Index Delete(OBJECT:([Item].[PK__I__] AS [i]), OBJECT:([Item].[ItemKey] AS [i]))

       |--Filter(WHERE:([Header].[Id] as [h].[Id] IS NULL))

            |--Nested Loops(Left Outer Join, OUTER REFERENCES:([i].[Id]))

                 |--Index Scan(OBJECT:([Item].[ItemKey] AS [i]), ORDERED FORWARD)

                 |--Index Seek(OBJECT:([Header].[Key2] AS [h]), SEEK:([h].[Id]=[Item].[Id] as [i].[Id]) ORDERED FORWARD)

2025-06-08