SQLite the only database you will ever need in most cases (2021) • Hacker News Board

Re: SQLite the only database you will ever need in most cases (2021)

#351

Post by sireat » Thu, Feb 16, 2023, 7:14 PM UTC

somsak2 wrote: post #345

sireat wrote: post #342

I am ashamed to admit I've run into a case where SQlite was the wrong choice. I have a cluster of 20 Windows 7,8,10,11 machines spread across multiple sites that I wanted to run some log analytics. This was for some custom software running on all 20 machines which had a SQlite API. So I did the simplest and cheapest thing that would work. I setup sync.com folder on all machines (think dropbox/ onedrive) to write logs…

not necessarily a problem with the db. rather the syncing strategy. i figure using a Cron with something like rsync would have worked

Indeed if those were Linux / BSD based machines

Setting up cron and rsync on different Windows machines (WSL, cygwin?) was not something I would have looked forward to.

Re: SQLite the only database you will ever need in most cases (2021)

#352

Post by srcreigh » Thu, Feb 16, 2023, 7:16 PM UTC

zzzeek wrote: post #348

srcreigh wrote: post #273

Earlier quoted context omitted.

SQLite has commands to rename columns (this is somewhat new). Which other migration is not supported without new table/copy/drop old table process? Also, MySQL can't run a migration on a FK-constrant table without downtime. To do this you need an online schema migration tool which generally requires the absence of foreign keys.

> SQLite has commands to rename columns (this is somewhat new). Which other migration is not supported without new table/copy/drop old table process? adding or dropping any constraints, including nullability, foreign keys, check constraints, changing the structure of the primary key, etc. changing a type also, even given SQLite's squishy typing model. pretty much anything is not allowed except adding and renaming col…

Fair enough re migrations. That’s annoying

Faire used ghost for MySQL migrations in 2018. We’d run some migrations in a blocking fashion if they were on a smaller table, but the smaller ones still took a few seconds. I suspect the delay would be very noticeable (15s+) for data sizes far less than at FB.

Re: SQLite the only database you will ever need in most cases (2021)

#353

Post by acuozzo » Thu, Feb 16, 2023, 7:20 PM UTC

di456 wrote: post #341

acuozzo wrote: post #336

Earlier quoted context omitted.

I meant hardware caches like L1, L2, and L3 on the CPU. SQLite is used in some HPC work. ISO-8601 datetime strings can easily wreck your L1 cache. Instead of filling an eighth of the cache with a 64-bit value, you wind up filling almost half with a 27-character-long string.

I learned something new, thanks for sharing! What format works well for hardware caching?

yw :-)

As small as you can tolerate tbh. The game is played by keeping data as small as possible and ensuring that related data has good locality in memory.

The gap between how fast your CPU works and how fast data can be retrieved from RAM is enormous.

Re: SQLite the only database you will ever need in most cases (2021)

#354

Post by sph » Thu, Feb 16, 2023, 7:23 PM UTC

JohnBooty wrote: post #331

sph wrote: post #258

Earlier quoted context omitted.

No offense and not here to toot my own horn, but just because you are better on one side of the stack doesn't mean that true full stack developers are unicorns. Like others upthread, I am one of them, and probably because I've been paid to do this job for 16+ years. Honestly my challenge is making potential employers understand that yes, I know my way around Elixir or React, as I do around C or Rust, as I do around s…

I know my way around Elixir or React, as I do around C or Rust, as I do around sysadmin (not only DevOps) or DBA work, as I do around low-level system code To what level do you know these tools? This is a challenging discussion because the idea of "knowing" or "being good at" a tool is so nebulous. As I mentioned elsewhere I'm using a definition that is essentially, "the level of skill a solid engineer would acquire…

I've used Elixir full time for the past 6 years.

I've written Rust full time for the past 3 years.

I've written C since I was 14 in 2001. I wrote a small operating system (up to reading and running a binary from ext2) around 2004 in C, so I know how a computer works at low level. I might still be able to write x86 assembly.

I've been a MySQL DBA full time for 3 years.

I've administered Linux systems since I was 14 in 2001, and for all my professional career.

Then there's Python, Go, etc.

Probably the one I know the least is React, which means I was PM on a React codebase for a short while, and spent way too much debugging weird issues with Next.js. I stopped paying attention post-hooks, since the frontend world changes too fast, and I'm getting old.

--

Again, this is not to toot my own horn, it's just that if you live and breathe computers, and hate doing the same thing for long (I blame my ADHD), over a long enough time you tend to have quite the repertoire. I think I'm quite average compared to other people that have been around as long.

You'll soon notice there doesn't tend to be anything revolutionary in computing. After your third framework and language, you'll keep finding the same ideas and concept with minor variations.

Re: SQLite the only database you will ever need in most cases (2021)

#355

Post by joshspankit » Thu, Feb 16, 2023, 7:48 PM UTC

crazygringo wrote: post #327

joshspankit wrote: post #227

Earlier quoted context omitted.

Store the index on the filesystem and populate it on write. Not satire, though a bit sensationalistic to argue it’s a solid solution that’s usually overlooked because it’s “too slow”. I’m just pointing out it’s not actually slow any more. Back in the days of scaled applications running on MySQL, DDR2 was 3200MB/s and people were so happy when their DB was small enough they could fit it in RAM.

I don't think the problem is that it's too slow. I think the problem is that all sorts of utilities and commands break when dealing with hundreds of thousands of files in a single directory. Also the block size means you'll waste an incredible amount of disk space.

I feel like wasting disk space is not a real issue any more: if you’re working with more than 1TB of data you (at least you better) have the resources to pay for bigger HDs which are at an almost trivial cost per TB.

Files in a single directory: It was discussed in another comment, but there’s a tried and true solution to that: simply nest your items in folders. For example with UUID as primary key you could have a folder structure of ‘(first 4 bytes)/(second four bytes)/(...so on)/(full uuid)’ where your nesting level is enough that you have no more than 50,000 files in each directory. For smaller pools you can reduce the layers (‘(first two bytes)/(full uuid)’ for example still gives you quite a few entries before any one folder gets to 50,000)

Re: SQLite the only database you will ever need in most cases (2021)

#356

Post by torbica » Thu, Feb 16, 2023, 7:51 PM UTC

bob1029 wrote: post #126

briHass wrote: post #38

Earlier quoted context omitted.

The biggest one missing is date and/or time. The workarounds all suck: - Store the date as a huge, wasteful string in ISO8601 format - Store it as Unix epoch seconds - Store it as a fractional Julian day Besides the first one, you have to remember how the date is stored and ensure all client libraries handle the conversion. If you want to view or manipulate the latter 2 formats in SQL, you need to chain a bunch of co…

> Store it as Unix epoch seconds This is what we do. Have been storing 100% of our timestamps this way in SQLite for ~8 years now. Using .NET to handle the actual conversion to/from long. var myTimeUtc = DateTime.UtcNow; var myTimeUnix = new DateTimeOffset(myTimeUtc).ToUnixTimeSeconds(); var myTimeUtc2 = DateTimeOffset.FromUnixTimeSeconds(myTimeUnix).UtcDateTime; No drama at all. No weird libraries or utility methods…

Are you maybe hiring? I have been reading you posts last couple years on HN.

I am doing something very similar with .NET stack (single file deployments), SQLite and offline scenario...

Please contact me on my username's email (gmail).

Re: SQLite the only database you will ever need in most cases (2021)

#357

Post by grep_name » Thu, Feb 16, 2023, 8:10 PM UTC

sph wrote: post #258

JohnBooty wrote: post #225

Earlier quoted context omitted.

As the sibling comment notes, yes -- my point was that the 2023 version of a "full stack developer" is distressingly shallow in most of the individual skills. I'll use myself as an example. I am a "full stack developer." I have fairly deep backend knowledge. But my React skills basically amount to the ability to make small JSX tweaks, and my sysadmin-y skills are also at roughly the same level. Conversely, the folks…

No offense and not here to toot my own horn, but just because you are better on one side of the stack doesn't mean that true full stack developers are unicorns. Like others upthread, I am one of them, and probably because I've been paid to do this job for 16+ years. Honestly my challenge is making potential employers understand that yes, I know my way around Elixir or React, as I do around C or Rust, as I do around s…

I'm also fairly generalist although my title is cloud engineer, and have all the skills listed by the OP + IaC infrastructure deployment and cloud stuff, as does everyone on my team. I think that's the trick; everyone on my team is responsible for everything, so we're constantly taking tickets in whatever we're weakest at and consulting each other, which causes really fast skill growth. I can see how it would seem impossible in a more siloed environment though.

> I know my way around Elixir or React, as I do around C or Rust

Interestingly, I find that I've never been asked to write anything in a compiled language yet. I've been wondering how I could work those skills into my job, but it seems like it doesn't come up much in the things I end up working on

Re: SQLite the only database you will ever need in most cases (2021)

#358

Post by kragen » Thu, Feb 16, 2023, 8:14 PM UTC

Spivak wrote: post #272

kragen wrote: post #34

Earlier quoted context omitted.

i know that i'm correcting their terminology 'durability' already has a well-established, rigorously-defined meaning in this context, which is confusingly similar to pitr but definitely not the same thing the downside of sync replication, as i understand it, is that although your data will survive any one of your machines being instantly nuked from orbit, your entire service will go down; semi-sync avoids this proble…

But they’re using the other well-established meaning of durability a la how AWS and others describe their storage platforms. It’s pretty much the same thing but taken at whole system level. On that level an ACID database is as durable as the underlying storage medium which is sadly not very durable.

well, it's sort of arbitrary that the standard definition of durability requires your data to survive machine checks and kernel panics and power outages but not disk failures, isn't it

especially since nowadays in many data centers disk failures are far more common

(though full raid failures are less common)

but that is the standard definition

Re: SQLite the only database you will ever need in most cases (2021)

#359

Post by nikeee » Thu, Feb 16, 2023, 8:30 PM UTC

srcreigh wrote: post #330

nikeee wrote: post #313

Earlier quoted context omitted.

I know about STRICT tables [0], but they still follow the quirky coercion rules. The reasoning seems to be that other DBMs have a similar behaviour. However, I want _errors_ if I insert '123' into an INT column, so it's easier to find problems in my code. [0]: https://www.sqlite.org/stricttables.html

The quirky coercion rules that PG, MySQL, SQL server and oracle also all follow? Let’s be clear, if this is a problem it’s a problem with all SQL DBs, not just SQLite. I’m curious why ‘123’ in an INT column is so bad? I suspect the conversion rules are in place because they shouldnt ever cause logical errors. I personally appreciate using created_at < ‘2021-05-23’ in Postgres queries. The query would only be more ver…

The reason for that being not that good basically has the same reason as with coercion rules in weakly typed languages like JavaScript.

If I'm passing a string to an int column, there is most likely an issue in my application code. If there currently is none, there might will be.

For example, I might have forgotten to parse the string properly in my application code. If I'm doing '10' * 2 in JS, it returns 20. If I later change it to '10' + 10, it will be '1010'. Say I then save the result of that compilation in the DB. Raising on '1010' would have prevented me from persisting the error and gave me an opportunity to investigate the situation. Without an error, there will be a much harder debugging session.

These coercions were popular back in the 90s/00s, which is why I think most DBMs have them. At least that's why JS has it.

Re: SQLite the only database you will ever need in most cases (2021)

#360

Post by maxthegeek1 » Thu, Feb 16, 2023, 9:32 PM UTC

chillfox wrote: post #289

fomine3 wrote: post #264

Earlier quoted context omitted.

scheduled maintenance downtime at night != unplanned failure downtime at random time

Correct, but in the context of deploys I would hope they are not random failures, but rather planned events that you do when you have updates to... deploy.

Yes, but you also don't want to have to deploy in the middle of the night in order to avoid downtime during peak hours.

Not only is that a pain for whoever is monitoring the deployment, but now if the deployment breaks something you're going to have to go wake up all of the relevant stakeholders, if you even know who they are.

Not to say late night deployments are never justified, but definitely not something devs want to be doing regularly.