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

61–70 of 101 posts

Re: Learning a few things about running SQLite

#61

Post by holgerschurig »

> 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 forum pops up, I don't even open that anymore. Sure, Ubuntu is similar to Debian, but the amount of wrong guessworks in these forums is hefty. I however usually open the Arch Wiki pages, despite Arch very != Debian. But the articles there are written by knowledgeable people.

Re: Learning a few things about running SQLite

#63

Post by bambax »

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 harder to completely understand than they are to read; it often happens for example that SQLite won't use existing indexes, for no obvious reason.

Re: Learning a few things about running SQLite

#64

Post by rtpg »

Diving a bit more into databases than your current comfort level/current job demands remains a great way to level up.

I've worked with many web developers who get mental blockage around DB tooling (granted, I have similar mental blockage when it comes to some operations stuff like K8s), and you can go far in life without really having to ask _that many questions_.

But going in and finding out how your SQL turns into data gotten from disk/written to disk is very helpful in just "knowing" what might be a decent idea. That and understanding your DB's locking system (or lack thereof...).

Figuring that stuff out can help reduce the surprise level when you can't seem to get a "simple COUNT" working quickly in Postgres or the like...

Re: Learning a few things about running SQLite

#65

Post by rmunn »

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…

The Arch wiki is one of the best Linux-related resources out there. I used to look things up there all the time back when I was running Mint (which is basically Ubuntu under the hood). Ironically, now that I'm actually running Arch I think I'm looking things up on the Arch wiki less often than when I was running Mint.

Re: Learning a few things about running SQLite

#66

Post by dukeyukey »

inigyou wrote: post #37
dukeyukey wrote: post #31

Earlier quoted context omitted.

To be fair they also say > Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite.

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.

They also say it seems to work well up to 500k a day, which is quite a bit.

Re: Learning a few things about running SQLite

#67

Post by xetera »

inigyou wrote: post #33
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 (…

Looks similar to EXPLAIN QUERY PLAN: https://sqlite.org/eqp.html Raw EXPLAIN dumps bytecode, which is usually much more verbose than you want. EXPLAIN QUERY PLAN dumps a summary.

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.

Re: Learning a few things about running SQLite

#68

Post by stevoski »

As a database person, this was hard to read. I wanted to find out what the problems are and solve them.

A db table with only 10k rows? Even a full table scan should be extremely fast.

And with SQLite - which I assumed runs in-process, but even if not, surely is running on the same physical server? Faster still.

Of course, the magic phrase in my head is “create index”.

I hope Julia posts an update!

Edit: I highly suspect the “slow deletes” problem is a classic “n+1” problem suffered by many ORM users, until they come to understand more about the underlying db interactions.

Re: Learning a few things about running SQLite

#70

Post by [deleted] »

9rx wrote: post #46
abrookewood wrote: post #39

Earlier quoted context omitted.

Honestly, I love PostgreSQL, but now I have another server or service to run. SQLite is just a file and often, that is enough.

PGlite offers the "real database" compiled to WASM, which can then be embedded similarly to SQLite. You don't have to choose between PostgreSQL and "just a file".

[deleted]
Post reply on HN