Inserting a billion rows in SQLite under a minute • Hacker News Board

Re: Inserting a billion rows in SQLite under a minute

#31

Post by einpoklum » Sun, Jul 18, 2021, 3:06 PM UTC

> Recently, I ran into a situation where I needed a test database with lots of rows and needed it fast. So I did what any programmer would do: wrote a Python script to generate the DB.

The author must have a rather low opinion of programmers... generating data with a script is good for 1,000 rows. Maybe 10,000 rows. 100,000 is probably stretching it. Beyond that the slowness will be meaningful.

Anyway, if the "test database" data is given in advance, you want an in-memory DBMS which can load data from files. If it isn't - use an in-memory DBMS which supports generated columns, or apply your random-number-generating function on a trivial column.

(MonetDB standalone/embedded would be a decent option; maybe the columnar storage mechanism in MariaDB? And of course there are commercial offerings.)

> Unfortunately, it was slow. Really slow. So I did what any programmer would do: went down the rabbit hole of learning more about SQLite...

That's the wrong approach. SQLite isn't called "lite" for nothing. It's not what you would use for bulk operations on large tables.

Read the SQLite FAQ: https://www.sqlite.org/whentouse.html

it oversells itself a bit, but still says its (only) advantages for data analysis are: "easier to install and use and the resulting database is a single file that can be written to a USB memory stick or emailed."

Re: Inserting a billion rows in SQLite under a minute

#32

Post by tgv » Sun, Jul 18, 2021, 3:07 PM UTC

xxs wrote: post #24

einpoklum wrote: post #17

Earlier quoted context omitted.

Yes, if you want to run non-trivial queries on that data. Although, frankly, SQLite would not be my choice.

I can't think of a single case where in-memory database is a good option, aside playing w/ SQL. Whatever queries that might be a hashmap/tree/skiplist, etc. would be a lot better.

For something like redis, but with more structure.

But a pretty good use case (IMO) is testing. If you want to do an integration test with an SQL database, and you want to test large numbers, this might be a good fully functional stub to run locally.

Re: Inserting a billion rows in SQLite under a minute

#33

Post by kylec » Sun, Jul 18, 2021, 3:16 PM UTC

svdr wrote: post #28

I thought one billion was 1000M (and not 100M)?

This is an important comment. It's not a typo in the article, even the source code only does 100M rows, not 1B. The author definitely does not hit the target of 1B rows in a minute.

Re: Inserting a billion rows in SQLite under a minute

#34

Post by tyingq » Sun, Jul 18, 2021, 3:17 PM UTC

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.

Sqlite has a btree module/api, though there's a lot of "TODO:" notes in the document: https://sqlite.org/btreemodule.html

Re: Inserting a billion rows in SQLite under a minute

#35

Post by arusahni » Sun, Jul 18, 2021, 3:22 PM UTC

[ @dang, please delete if/when you get the chance, thank you ]

Re: Inserting a billion rows in SQLite under a minute

#36

Post by kangalioo » Sun, Jul 18, 2021, 3:28 PM UTC

arusahni wrote: post #35

[ @dang, please delete if/when you get the chance, thank you ]

Did you mean to post this to https://news.ycombinator.com/item?id=27872596 ?

Re: Inserting a billion rows in SQLite under a minute

#37

Post by [deleted] » Sun, Jul 18, 2021, 3:33 PM UTC

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.

[deleted]

Re: Inserting a billion rows in SQLite under a minute

#38

Post by chrisseaton » Sun, Jul 18, 2021, 3:37 PM UTC

svdr wrote: post #28

I thought one billion was 1000M (and not 100M)?

The author doesn’t say a billion is 100 million. They say they’d like to be able to insert a billion, and say they’re able to insert 100 million. It’s not a contradiction.

Re: Inserting a billion rows in SQLite under a minute

#39

Post by minitoar » Sun, Jul 18, 2021, 3:40 PM UTC

chrisseaton wrote: post #38

svdr wrote: post #28

I thought one billion was 1000M (and not 100M)?

The author doesn’t say a billion is 100 million. They say they’d like to be able to insert a billion, and say they’re able to insert 100 million. It’s not a contradiction.

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

Re: Inserting a billion rows in SQLite under a minute

#40

Post by xeromal » Sun, Jul 18, 2021, 3:42 PM UTC

svdr wrote: post #28

I thought one billion was 1000M (and not 100M)?

Yeah, that's my interpretation of a billion too. I vaguely recall that India or Britain interprets a billion differently though. Maybe that's what they're thinking?