Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

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

unixsheikh.com

Post reply on HN
Print view View original

321–330 of 378 posts

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

#321

Post by pdimitar »

erik_seaberg wrote: post #173
canadianfella wrote: post #122

Earlier quoted context omitted.

Why do people use hosted databases instead of just installing them? I don’t understand it.

If you install your own copy, you are on call for it, and the colo is unlikely to offer much help. You also have to set up monitoring or you won’t even know when it fails. Then there’s replication. Backups. All this stuff is work that PaaS vendors are ready to automate away, if my time is expensive for the org. For fun, sure, dink around and learn as long as there are no customers to affect.

You are not wrong but for each personal project this upfront cost is 2 hours maximum, with accumulated 5-6 more over the course of the next 3-6 months.

Not a huge sacrifice. Though I do get the argument of "I want to pay $5 and it to just work" and I've done so part of the times. Just pointing out that the upfront investment in doing it on your own is not so big.

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

#322

Post by drexlspivey »

albert_e wrote: post #259

AWS has a habit of taking a open source project and creating a "managed service" offering of it. Is it possible to offer SQLite as a managed / serverless offering? A light weight and cheap relational data store that we just consumer using an API

Cloudflare D1 does that https://blog.cloudflare.com/introducing-d1/

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

#323

Post by pdimitar »

lenkite wrote: post #312
irskep wrote: 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…

Also "as long as your web application can run on the same machine as the database, which it can in 99% of the time" Where did 99% of the time come from ?

Not the poster you replied to but from my anecdotal experience -- from most of my work ever.

I can only remember 3 companies out of all 40+ I consulted and contracted for that actually needed a separate machine for app and a DB, let alone such that actually truly need several of each.

It's a very non-romantic truth but most projects out there can easily fit in a VPS with 4 CPU cores, 8GB ram and 100-200GB SSD space. App + DB + self-hosted telemetry included.

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

#324

Post by srcreigh »

chasil wrote: post #295
srcreigh wrote: post #248

Earlier quoted context omitted.

I don’t get this. SQLite can perform 500k writes/s (or 5k write txns/s). What app are you building which requires more than this?

To optimize the writes, you must look at inotify. The sqlite commit is a specific pattern, it is either write or close/write. Using inotify events can see these faster than random waits.

Respect. I’m curious what use case for >500k writes/second or 5k write txns/second you’re implementing? I know nothing about inotify and random waits. What else can you tell me?

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

#325

Post by sanderjd »

bravura wrote: post #318
sanderjd wrote: post #167

Earlier quoted context omitted.

It is honestly difficult for me to imagine what kind of applications people are working on that have these - to me - very lax reliability constraints. But we're just disagreeing over what "most" applications are like, based on our experiences, without any empirical data to say either way, so :shrug:.

Off-the-top of my head. * Hobby apps designed for yourself and your friends. * Hobby apps that you hope will become products but probably won't achieve traction. * Apps where you're trying a new stack or framework. * Annotation apps for academia. Basically, every single app written by people who aren't doing internet facing web dev as their core engineering function. Also intranet apps as other commenter mentioned.

When I read an article like this that claims some architectural technique is broadly applicable, I don't think it is talking about hobby or just-for-learning applications. Certainly you can do whatever you want with those, but that's not very illuminating.

I'm not sure exactly what you have in mind for annotation apps for academia - things like zotero that run client side? If so, sure, there is a big world of client side software where a database is useful, and I think SQLite (or DuckDB) seems like a no brainer there.

I don't really agree about intranet apps, which are often even more critical to the people using them than an arbitrary consumer app. But I'll grant that for a company that spans a small number of time zones, you can at least have downtime windows outside work hours.

In any case, as I've said all over this thread, the only disagreement here is over what kinds of software is "most". And my intuition for "most" is based on my experience working on and using applications where a lot of effort is made to keep the thing running all the time while still evolving it. Maybe you're all right that "most" software isn't like that.

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

#326

Post by mharig »

sanderjd wrote: post #242
naniwaduni wrote: post #178

Earlier quoted context omitted.

> I don't think it's true that most applications just run a single db-and-application node. I've never worked on such an application. Rather than applications you've worked on (many of us spend years on end working on a narrow range of applications), consider software you use (most of us flip between multiple applications every day spanning the gamut of uses). Ignore, for a moment, whatever you know about their imple…

Yes I think pretty much all the applications I interact with require fault tolerance and uptime that (to me) seems simpler to implement with separation between application and database nodes.

So you do not interact with

- Browsers

- Messengers

- The telephone directory of your cellphone

- SMS apps

- The sqlite.org Website

- ...

?

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

#327

Post by crazygringo »

joshspankit wrote: post #227
crazygringo wrote: post #215

Earlier quoted context omitted.

I honestly can't tell if this is satire or not. But I think I'm curious either way: how do you index a column?

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.

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

#328

Post by srcreigh »

aembleton wrote: post #301
srcreigh wrote: post #270

Earlier quoted context omitted.

Such as?

Strong typing and exclusion constraints are two off the top of my head.

SQLite does have strict typing and check constraints, I suspect the R*Tree module with check constraints would provide rectangular exclusion, though not circles.

Have any applications you’ve build needed both of these features?

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

#329

Post by canadiantim »

nikita wrote: post #266
canadiantim wrote: post #251

Earlier quoted context omitted.

Congrats on the upcoming launch! I'm definitely very strongly considering using Neon in my upcoming project. Mind if I ask a couple questions? Would you be able to comment on if Neon is a good fit for having one postgres database per user and how well that would scale? E.g. what if millions of users? Also with the managed service, is there help with applying migrations or helping manage migrations for such a multitud…

One database per user is something many of our customers are already using today. I think if there are millions of users it’s a bit of an overkill. Because likely you will have some very light users that you can still collocate on one database but give heavy users a dedicated one. With Neon you can do either. Our minimum configuration is 1/2 core which may still be too much for one light user.

Thank you! That makes a lot of sense. Yes its probably overkill to do one db per user. Better to split off when they become heavier users. Thanks again for the awesome tech eh and good luck on the formal launch!!

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

#330

Post by srcreigh »

nikeee wrote: post #313
srcreigh wrote: post #279

Earlier quoted context omitted.

SQLite added strict mode recently, check it out. Agreed time handling is sub par - could use builtin date formatting from epoch to ISO which would fix all the problems IMO.

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 verbose if I had to explicitly construct a date object for it.

Post reply on HN