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

Learning a few things about running SQLite

jvns.ca

Post reply on HN
Print view View original

81–90 of 101 posts

Re: Learning a few things about running SQLite

#81

Post by maccard »

rafabulsing wrote: post #49
inigyou wrote: post #37

Earlier quoted context omitted.

So about one per second (up to ten, less conservatively). I concur. But if you think your site might ever scale beyond that, do yourself a favor and use Postgres from the get-go.

The vast, vast majority of websites never see anything even close to that, so it's a safe bet unless you have some specific reason to expect it to reach that kind of traffic, or you are dealing with workloads that SQLite really does not handle well, e.g. many concurrent writes. And if your workload is mostly reads, then you probably can use a cache layer, which allows SQLite to go further still.

Adding a cache layer to keep using the wrong database is an architecture failure - if you use Postgres from the get go you will not need the cache until way way later on anyway. (Side note, I’m team MySql but the same point applies)

Re: Learning a few things about running SQLite

#82

Post by [deleted] »

simonw wrote: post #8
striking wrote: post #5

> Maybe one day I’ll learn to read a query plan. With SQLite's `.expert` mode you can delay that day a little longer: https://www.sqlite.org/cli.html#index_recommendations_sqlite... sqlite> CREATE TABLE x1(a, b, c); -- Create table in database sqlite> .expert sqlite> SELECT * FROM x1 WHERE a=? AND b>?; -- Analyze this SELECT CREATE INDEX x1_idx_000123a7 ON x1(a, b); 0|0|0|SEARCH TABLE x1 USING INDEX x1_idx_000123a7 (…

I've worked with large MySQL databases that used row-based replication and things like an UPDATE or DELETE that affected millions of rows had to be applied in batches there, because otherwise one SQL query might result in a million updated rows needing to be sent to all of the replicas at once.

[deleted]

Re: Learning a few things about running SQLite

#83

Post by simonw »

holgerschurig wrote: post #61

> I didn’t care to investigate further and > my best guess and > and presumably other things?) and > maybe there’s a bunch of Python code running inside a transaction Basically, this article has no substance. The author didn't bother to learn anything, didn't look things up. And is then wildly guessing, sometimes wrong. This is BTW the reason why (as a Debian user) if I search something Linux related and a Ubuntu for…

Julia Evans is an extremely knowledgeable person.

She's also one of the best out there at demystifying technology and helping people understand what solving problems actually looks like.

This article doesn't pretend to be a world expert's take on using SQLite. The clue is right there in the title - "learning a few things about running SQLite" - which sets expectations right from the start.

The wider message is consistent throughout all of her writing. You can do this stuff. Here are simple practices to show how to figure out problems and build your knowledge. You don't have to know everything, and you certainly don't have to pretend to know everything. Sharing what you've figured out so far, in as clear a way as possible, is a virtue.

Re: Learning a few things about running SQLite

#84

Post by simonw »

inigyou wrote: post #72
xetera wrote: post #67

Earlier quoted context omitted.

In my experience, SQLite explain plans are by far the most useless of any database out there. No concept of costing, no buffer information, no explain analyze. It's almost like they don't want you looking at it.

sqlite competes with fopen. If you don't like it, use a real database.

What's your criteria for a "real database"?

Re: Learning a few things about running SQLite

#86

Post by serhii777_123 »

bananamogul wrote: post #29
striking wrote: post #5

> Maybe one day I’ll learn to read a query plan. With SQLite's `.expert` mode you can delay that day a little longer: https://www.sqlite.org/cli.html#index_recommendations_sqlite... sqlite> CREATE TABLE x1(a, b, c); -- Create table in database sqlite> .expert sqlite> SELECT * FROM x1 WHERE a=? AND b>?; -- Analyze this SELECT CREATE INDEX x1_idx_000123a7 ON x1(a, b); 0|0|0|SEARCH TABLE x1 USING INDEX x1_idx_000123a7 (…

Another side effect of deleting 10 million rows in some databases (e.g., Oracle) is that the database writes out 10 million rows' worth of undo, which can swamp the disk space set aside for archive logs if you can't back it up and clear it off fast enough. Committing more frequently help, but if you have large databases and regularly need to purge, the best way in my experience is to use partitioning. Dropping the ol…

[flagged]

Re: Learning a few things about running SQLite

#87

Post by ufmace »

Natfan wrote: post #59
simonw wrote: post #50

Earlier quoted context omitted.

For logging, in particular for an environment where you don't want a leaked credential to allow the deletion of any previously recorded files.

does write only prevent overwrites?

It does effectively, if you turn on bucket versioning.

I think write-only is worthwhile for database backups to guard against a ransomware compromise.

Re: Learning a few things about running SQLite

#88

Post by antonvs »

simonw wrote: post #84
inigyou wrote: post #72

Earlier quoted context omitted.

sqlite competes with fopen. If you don't like it, use a real database.

What's your criteria for a "real database"?

A couple of obvious ones would be running as a standalone service, and supporting multiple concurrent writes. Both of these help support transparent handling of multiple concurrent clients, which seems like table stakes for a "real database".

Re: Learning a few things about running SQLite

#90

Post by deathanatos »

datadrivenangel wrote: post #3

"Maybe one day I’ll learn to read a query plan." Query plans aren't that hard to read! [0] 0 - https://xkcd.com/2501/

They're not hard to read until you see the queries that coworkers at large companies tend to generate. If the query is a novella that joins half of humankind's know data sources, the query plan tends to not be much better. Then there's always the dreaded question: "why is it slow?" "Because you're making the database yearn for the sweet release of death."
Post reply on HN