Live data from Hacker News

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

unixsheikh.com

341–350 of 378 posts

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

#341
post #239

Earlier quoted context omitted.

It's cheap if the cache is also on SQLite. Might not even need a separate db for some use cases. This runs on python and works with several backends including SQLite. https://requests-cache.readthedocs.io/en/stable/

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?

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

#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 to the same log.db file across all machines.

This worked great at first (I could analyze the db remotely).

However you see the potential problem here...

Logging was maybe a few writes an hour PER MACHINE but inevitably you start getting conflicts (since sync takes a 5-30 seconds to actually sync).

Now I am faced with merging 20 conflicting log.db files.

In theory I should have used a server based SQL database. Or perhaps I should have just lived with 20 different log.db files.

In my defense there was only SQlite API so I would have had to write some middleware to transfer to another DB.

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

#343
post #243

Earlier quoted context omitted.

I've dabbled in the whole stack as you've said. I've written a web framework, done some Docker, done some fun Javascript demos, shipped web and desktop code in a bunch of languages. Built and maintained my own physical servers back when that was a thing people did. Windows, Linux. Currently relearning C and 68K game development (at a glacial pace) as a side project. Did assembly language in college. Ran a business. D…

I heard a story that the inventor of python was once asked in an interview at Amazon to rate himself from 1-10 in python. The interviewer didn’t know who he was and it was just a standard question. He said 7. I don’t think you have to be a 10 to be a good. But you might need to be 10 to be exceptional. And I will agree that it would be very challenging to be a 7, let alone an 10, across multiple domains at the same t…

That story is hilarious.

I think it highlights how difficult it is to even discuss these things. We're all using different definitions of "good" and "mastery" and "7/10" and "10/10".

    Being able to adapt and pick up new things at a 7 
    level though - is the real FSD
This is a really interesting line of thought.

By my definition, I actually don't think this is possible, at least not for larger languages/tools/frameworks.

My reasoning is this. To be a 7 (my definition) requires time. You need to not just understand the basic premise and syntax of a language, but you need battle scars. You need to have shipped some code in that language, gotten familiar with the ecosystem of libraries, you've troubleshot production issues, and become familiar with common pitfalls and how to avoid them. You've checked out some large codebases in that language and gleaned best practices and things to avoid. You have probably also spent some time in that community, watched/attended presentations from recognized leaders, and have a sense of which way the wind is blowing.

Everything I just described takes time. I don't think even the smartest person in the world can drop in and achieve that immediately unless we're talking about a relatively simple tool.

I mean, could somebody who already knows CSS pick up TailwindCSS and be a 7 quickly? Absolutely.

Could an engineer who is new to Ruby/Kotlin/Python be a 7 quickly? Not by my definition, not by a longshot. In my experience, seasoned developers drop into existing codebases in these sorts of languages and make a mess of things at first until they get used to the ecosystem.

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

#344
post #29

This sentiment pops up regularly on HN, and I've seen at least one article per month for the past few months, but the trouble is, none of them seem to help you actually deploy it. They assume you're comfortable spinning up public web servers. If you want to use a PaaS to deploy an app, because you don't want to spend your time learning to be a sysadmin, then all the tutorials are going to put you on the Postgres path…

is $15/month really that much of an expense? i share my PostgreSQL instance across projects, really does not seem like that big of a deal to me considering it's such a big hobby of mine.

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

#345
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

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

#346

Earlier quoted context omitted.

If you make it so I can have one SQLite database per user, I’d be a customer too

When you say per user, you mean per user of your app?

yeah exactly. I want each user's data to be silo'd in their own sqlite database each. It's basically just a multi-tenant setup using sqlite.

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

#347

Earlier quoted context omitted.

If you’re exceptionally careful and skilled about writing smart queries, SQLite can for sure scale to the point you can afford to resign anything you want. The idea behind vitess and sharded MySQL (and for that matter kubernetes too) is that you can move fast and not make a terrible mess of things. I can split off one poorly designed table with Vitess - with SQLite that would be an application redesign. But in genera…

Google reportedly receives 99k search queries per second - which company actually handles 6 digit QPS reads?

Well if you're talking about a single query, virtually no one hits that scale. But in terms of over-all QPS in quickly-scaled apps, 6 digit QPS is super common. All it takes is a poorly programmed notification or messaging feature to hit that number. Also, that was for sure a high-water mark, not the typical usage!

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

#348
post #200

and if you need to migrate your database schema in any non-trivial way.....well then you're on your own. for anything beyond adding a column to a table, you'll have to copy the whole table to a new one with the structure you want, drop the old table, then rename your new table, carrying along all the foreign key constraints and other constraints while you do so. Or use a tool which does this (I write one such tool an…

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 columns.

> Also, MySQL can't run a migration on a FK-constrant table without downtime.

That's not an issue for the overwhelming vast majority of MySQL databases in production, which are to be clear not running or aspiring to run at Facebook / OLTP-level scales. A table with a few million rows can be migrated in seconds, the table gets locked for a few seconds, everything keeps running after a brief pause. This is not a problem for the "most cases" use case the article refers towards.

> To do this you need an online schema migration tool which generally requires the absence of foreign keys.

if you are running at Facebook / OLTP scales or aspiring to be, then yes. Otherwise, not usually. The article here is referring to SQLite being used for "most cases", not just "running at Facebook / OLTP -level scales". Running at Facebook / OLTP-level scales is still one of the places where you most certainly would *not* be using SQLite for your primary database.

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

#349

Earlier quoted context omitted.

Third paragraph of the article says: "In contrast to many other database management systems, SQLite is not a client-server database engine, but you actually very rarely need that. If your application software runs on the same physical machine as the database, which is what most small to medium sized web applications does, then you probably only need SQLite." That's how we square it. It's right there in the article.

That is a claim, not an explanation. Satisfactory answers need to be provided for the typical requirements that a small to medium web app might have, or the claim is unjustified. (Nearly) zero down time is a common requirement. Can a live backup be made while transactions are in progress? That appears to be the case. What about schema changes? Can columns or indexes be added without interfering with access to the tab…

> (Nearly) zero down time is a common requirement.

Are we still talking about small to medium web apps ? I'm sorry but if HN goes down things will be OK. In fact, there is a very large majority of services that can go down go down and things will still be OK.

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

#350

Earlier quoted context omitted.

When you say per user, you mean per user of your app?

yeah exactly. I want each user's data to be silo'd in their own sqlite database each. It's basically just a multi-tenant setup using sqlite.

Cool, thanks for the clarification!
Post reply on HN