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

51–60 of 164 posts

Re: Inserting a billion rows in SQLite under a minute

#51

Post by res0nat0r »

xeromal wrote: post #47
atleta wrote: post #42

Earlier quoted context omitted.

I think it's the same in UK English as well. But in some (most?) European languages billion actually means 1 000 000 million (so a thousand times more ). And we use "milliard" for 1000 million.

It looks like you're right. Just looked it up. A billion is 1million^2 in its etymology so English speakers are the odd one out.

He hasn't reached the goal yet, and is currently doing 100 million in ~34 seconds.

> Looking forward to discussions and/or collaborations with curious souls in my quest to generate a billion record SQLite DB quickly. If this sounds interesting to you, reach out to me on Twitter or submit a PR.

Re: Inserting a billion rows in SQLite under a minute

#53

Post by dclowd9901 »

minitoar wrote: post #39
chrisseaton wrote: post #38

Earlier quoted context omitted.

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.

I agree. While the title reflects the eventual goal of the effort, the goal has yet to be achieved (and may or may not be achievable at all). I think it’s a bit irresponsible to use a title like that for a post that neglects to have achieved what was described in the title.

Re: Inserting a billion rows in SQLite under a minute

#54

Post by adamnemecek »

einpoklum wrote: post #17
mirekrusin wrote: post #6

If it's single, in memory table, is there really need to use database? Won't language provided data structures suffice?

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

What would be your choice?

Re: Inserting a billion rows in SQLite under a minute

#55

Post by avinassh »

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.

yes, I plan to try this next and measure the performance. Others also suggested similar idea to try.

> I am also interested in writing the SQLite or PostgreSQL file format straight to disk as a faster way to do ETL.

what exactly you are trying to do here?

Re: Inserting a billion rows in SQLite under a minute

#57

Post by mongol »

xeromal wrote: post #40
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?

In Swedish, and I think in German and other languages too, 1 billion is 1e12. 1e9 is called milliard / miljard in Swedish.

Re: Inserting a billion rows in SQLite under a minute

#59

Post by justinclift »

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.

You mention wanting to do a Go version. Not sure if it's useful, but this is a SQLite "bulk data generation" util I threw together ages ago in Go:

https://github.com/sqlitebrowser/sqlitedatagen

There's some initial work to parallelise it with goroutines here:

https://github.com/sqlitebrowser/sqlitedatagen/blob/multi_ro...

Didn't go very far down that track though, as the mostly single threaded nature of writes in SQLite seemed to prevent that from really achieving much. Well, I _think_ that's why it didn't really help. ;)

Re: Inserting a billion rows in SQLite under a minute

#60

Post by avinassh »

minitoar wrote: post #39
chrisseaton wrote: post #38

Earlier quoted context omitted.

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.

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 feedback that title should indicate that it is a test database and emphasise that it is not durable.

I am wondering the right way to convey all of this in the title yet also keep it short.

Post reply on HN