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

271–280 of 378 posts

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

#271

Post by srcreigh »

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

Nope. SQLite is already available in the same process and using the same file system as your server. In some cases (ex Python) without adding any new dependencies. It's downright silly to try to think of a way to make it easier.

Here's your easy cheap and lightweight relational datastore API:

    import sqlite3
    conn = sqlite3.connect("db.sqlite3")
    cur = conn.cursor()
    cur.execute("SELECT * FROM products LIMIT 25")
    print(cur.fetchall())

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

#272

Post by Spivak »

kragen wrote: post #34
Spivak wrote: post #27

Earlier quoted context omitted.

That's not what the parent means by durability, they mean having your data survive any one of your machines being instantly nuked from orbit at the most inconvenient possible time. Just having sync replication is enough, doesn't have to be fancy like semi-sync.

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.

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

#273

Post by srcreigh »

zzzeek wrote: 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.

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

#274

Post by srcreigh »

Arathorn wrote: post #92
ripdog wrote: post #71

I have wondered why Synapse, the most feature-complete Matrix homeserver, so vehemently recommends against use of SQLite as it's backing db. They say that the performance is insufficient and it's only appropiate for testing purposes. That would make sense if you assume that Synapse is only going to be used in instances with hundreds/thousands of users, but plenty of people host their own instances for themselves only…

The problem is that even a single user matrix server can be very resource intensive if that user joins big rooms with thousands of users spread over thousands of servers. Synapse is very database heavy, so the parallelism in Postgres helps a lot - plus some of the hot DB paths have special cased queries for Postgres to use some of its more obscure features that Sqlite lacks. Finally, we don’t dogfood or optimise Syna…

Which obscure Postgres optimization features does SQLite lack?

Can you share any extra info about the table layouts or queries which are slower in SQLite vs Postgres? In particular which postgres-specific optimizations have been made?

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

#275

Post by srcreigh »

erulabs wrote: post #45

See I’ve been using Vitess on Kubernetes for even personal projects and I gotta say I love that I can run, for 10 bucks a month on Linode, the same tools that I know by experience I can scale to a multi-billion dollar valuation worth of customers. Heck I even run it in development on my laptop thanks to Skaffold. Sure it’s all insane overkill - but I use Linux for the same reasons - I want one API that I can use ever…

You also run a company which offers kubernetes self hosting as a business, which is quite the bias.

Anyways, the question isn't whether client-server databases scale, it's whether SQLite doesn't scale. What were the database size, resource constraints, architecture for the multi-billion $ company? How did this scale over tiem? Do you have any reason to believe that a SQLite architecture couldn't scale to support the service offerings you saw?

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

#276

Post by azurelake »

irskep wrote: post #63
samtho wrote: post #55

Earlier quoted context omitted.

I think the point to be learned here is that SQLite fundamentally does not fit in a PaaS model. They are even transparent with this limited use case[0]. I work with embedded systems so I use it a lot, and all the web work I do nowadays is one-off project site and small utilities that are usually a single process so I end up use SQLite 90% of the time I’m reaching for a solution. 0: https://www.sqlite.org/whentouse.ht…

I think it's more that PaaS vendors aren't interested in first-class SQLite support when they can sell overpriced managed Postgres instead. Sure, it doesn't scale the same way, so it's hard to move upmarket and sell to Enterprise, but it's a shame that there's no one-click solution like there is for a managed database.

Let's say the following product existed:

1. You can only run one instance of your app.

2. There is a small window of downtime each deploy.

3. Your app has access to 10 GB of storage. The storage is persisted across deploys and can be used for sqlite.

4. Your sqlite data is automatically backed up and can be restored / downloaded as needed.

How much per month would you pay for that product?

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

#277

Post by RedShift1 »

EGreg wrote: post #159
joshspankit wrote: post #156

I’ve argued this before and I’ll argue it here now: Modern computers are fast enough that in many cases “the only database you will ever need” can be files on the filesystem. For example “1 row = 1 file”. It brings additional benefits as well: for low-write applications you can use git to get a history (+transactions if you store them in the log), backups are super easy, replication is trivial. For higher-write appli…

Transactions, really? How do you lock rows, how do you have relations, how do you do joins? In fact, ext3 can only handle about 50,000 files in one directory. So you'll have to split up your "primary key" into letters like abc/def/foo like we do

Postfix turning eyes away

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

#278

Post by srcreigh »

umvi wrote: post #157

Everytime I try to use SQLite I run into db locking issues where I seemingly have to try to run my query in a retry loop. Am I doing something wrong or does SQLite just not play nice in multi threaded contexts?

Is multi threading necessary for your language/platform? These problems can be avoided using single process async services.

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

#279

Post by srcreigh »

nikeee wrote: post #179

I really like SQLite and I use it a lot. There are only two things that are missing to make it near-perfect: - A type for Instants / time handling - being strict with types. No inserts of ints into a string column

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.
Post reply on HN