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

Inserting a billion rows in SQLite under a minute

avi.im

Post reply on HN
Print view View original

141–150 of 164 posts

Re: Inserting a billion rows in SQLite under a minute

#141

Post by avinassh »

minitoar wrote: post #77
avinassh wrote: post #60

Earlier quoted context omitted.

Hey, sorry for the misleading title. I started with ‘Fast SQLite Inserts’ and it had many iterations. In the title, I wanted to intend that I want to insert 1 billion rows under a minute on my machine. I thought the current title is fine, since I got LGTM for earlier drafts. The detail about on my machine is also important since mine is a two year old laptop and all the measurements are done on it. Also I got another…

Thanks for the explanation. I think it only needs a minor tweak. Maybe prefix with “trying to” or something like that. I am empathetic to the challenge of naming things concisely and accurately.

Hey, I read all the suggestions and prefixed the title with "Towards ...". I hope it makes it clear now

Re: Inserting a billion rows in SQLite under a minute

#142

Post by avinassh »

infogulch wrote: post #72
minitoar wrote: post #39

Earlier quoted context omitted.

Ok but that is a rather clickbaity title. The title makes it sound like they are successfully doing that.

Perhaps " Towards Inserting 1B Rows in SQLite in Under a Minute" would be a better title.

Thanks for this suggestion and I have updated the title!

Re: Inserting a billion rows in SQLite under a minute

#143

Post by sharken »

nojvek wrote: post #140

For the table you have 3 integer rows and a char(6) row. Taking integer as 32 bit that is a total of 18 bytes of data per row. At billion rows, that is 18GB of data. With some overhead for storing page info, let’s call it 20GB flat. A modern SSD can deliver ~500MB/s write speed. That means writing 20GB of data can be done in 40 seconds. Therefore a billion rows in a minute is quite plausible. At-least not bottlenecke…

It's a good insight to know what the hardware is capable of.

The SSD in the authors machine can do 1300MB/s and the latest M1 model can do 2100MB/s.

For current gen SSDs with PCIe 4.0, that number increases to 6600MB/s for the Sabrent Rocket 4 Plus.

That would mean just around ~5 seconds for writing to disk.

https://www.pcgamer.com/best-pcie-4-ssd-for-gaming/

Re: Inserting a billion rows in SQLite under a minute

#144

Post by sharken »

mimir wrote: post #18

Database optimization posts are always interesting, but it's really hard to do any apples to apples comparison. Your performance is going to depend mostly on your hardware, internal database settings and tunings, and OS level tunings. I'm glad this one included some insight into the SQLite settings disabled, but there's always going to be too many factors to easily compare this to your own setup. For most SQL systems…

For relational databases in the enterprise with large amounts of data, batched inserts work great.

For large deletes it is often better to move the rows that won't be deleted to a new table and rename the table when done.

With large updates it is important to look at the query plan and optimize it with good indexes. Batching also works well in this scenario.

Re: Inserting a billion rows in SQLite under a minute

#145

Post by teekert »

Webapps (or webapp frameworks) like Nextcloud and Django, come, by default, with SQLite. That makes it easy to start, but they all warn you: When you get serious, get a real DB like MariaDB or PostgreSQL... And with stuff like this I wonder, is this really necessary?

I really like SQLite, it's well supported in Python, backup-restore is very simple. What is the real difference (probably application depended) between SQLite and a "real" DB engine?

Re: Inserting a billion rows in SQLite under a minute

#146

Post by tcbasche »

teekert wrote: post #145

Webapps (or webapp frameworks) like Nextcloud and Django, come, by default, with SQLite. That makes it easy to start, but they all warn you: When you get serious, get a real DB like MariaDB or PostgreSQL... And with stuff like this I wonder, is this really necessary? I really like SQLite, it's well supported in Python, backup-restore is very simple. What is the real difference (probably application depended) between…

I guess the idea is you can scale the app independently of the DB. How do you scale out a single file? (Genuinely curious)

Re: Inserting a billion rows in SQLite under a minute

#147

Post by DemocracyFTW »

rmetzler wrote: post #8

Is PRAGMA temp_store = MEMORY the same as putting the file on a TempFS?

I tried using tmpfs to create and exercise a file-based SQLite DB, and it performed significantly worse than a RAM-based DB (opened with an empty string or ':memory:').

Re: Inserting a billion rows in SQLite under a minute

#148

Post by exikyut »

avinassh wrote: post #12

Hey all, author here. I didn't expect this to reach front page of HN. I came here to submit and it was here already! I am looking for more ideas to experiment. Here's one idea which someone from another forum gave me: exploring recursive queries and using SQLite random methods to do the insertions. Another crazy idea is to learn about SQLite file format and just write the pages to disk.

Potentially interesting:

https://github.com/siara-cc/sqlite_micro_logger_arduino

https://github.com/siara-cc/sqlite_micro_logger_arduino/blob...

This is a heavily subsetted implementation of SQLite3 that can read/write databases (presumably on SD cards) from very small microcontrollers.

It presumably doesn't have the same ACID compliance properties, but with a single Now I'm thinking it could actually be interesting to see what drh thinks of this implementation (and any gotchas in it) because of its small size and accessibility.

Re: Inserting a billion rows in SQLite under a minute

#149

Post by exikyut »

eatonphil wrote: post #19
avinassh wrote: post #12

Hey all, author here. I didn't expect this to reach front page of HN. I came here to submit and it was here already! I am looking for more ideas to experiment. Here's one idea which someone from another forum gave me: exploring recursive queries and using SQLite random methods to do the insertions. Another crazy idea is to learn about SQLite file format and just write the pages to disk.

I am also interested in writing the SQLite or PostgreSQL file format straight to disk as a faster way to do ETL. I'd be curious to hear if anyone has actually tried this. I'd be as curious if you end up trying this too.

See also https://news.ycombinator.com/item?id=27880920

Re: Inserting a billion rows in SQLite under a minute

#150

Post by einpoklum »

einpoklum wrote: post #122
adamnemecek wrote: post #54

Earlier quoted context omitted.

What would be your choice?

If you don't do any joins, Clikhouse is worth a try. Otherwise, MonetDB (or even MonetDB embedded) if you want FOSS. Commercial offerings - Actian Vector, or maybe HyperDB. The big corporate ones are probably pretty good, but it's always difficult to tell because there's this taboo on any proper benchmarking against them. If you just want to browse what other people are using, and not go by the recommendations of a r…

Ah, I forgot a newer option: DuckDB. It self-styles as "SQLite for analytics"... https://duckdb.org/
Post reply on HN