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

61–70 of 164 posts

Re: Inserting a billion rows in SQLite under a minute

#61

Post by avinassh »

siscia wrote: post #10

Creator of RediSQL / zeeSQL ( https://zeesql.com/ ) Insertion performance on a single table are very very hard to optimize. A single process looping it is your best bet. I would just increase the batch size, which is the most influent factor. Then another point... When you do batches, you do BEGIN TRANSACTION; for i in range(1, 50): execute_stmt COMMIT; You do not create a long list of parameters. https://github.com/…

That's a great point, let me try it!

> You do not create a long list of parameters.

I have done much worse by trying to insert a really long string of 100K params

Re: Inserting a billion rows in SQLite under a minute

#62

Post by oldtablet »

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.

I really like sqlite but I'm curious how fast it would be to write the data to parquet files and read it with duckdb. I've done something similar before and in my case duckdb was faster than sqlite.

Re: Inserting a billion rows in SQLite under a minute

#63

Post by ndepoel »

tyingq wrote: post #22
nezirus wrote: post #11

Earlier quoted context omitted.

You just do raw byte copies from sample DB, no SQL or "inserts" or anything similar. Imagine test database consists of 3 parts (all raw bytes) ### PROLOGUE ### Sample row ### EPILOGUE You copy & write prologue, write 1B sample raws (can optimize this at will, large writes, etc) Copy & write epilogue and fsync the data. You probably need to modify some metadata, but that should be a few writes at most. That should be…

Do you mean crafting all the various database page btree structures and entries yourself? I'd be concerned about subtle bugs.

An SQLite database is just a file. You can build the empty database with schema and base values ahead of time, save it to a file (or an in-memory byte buffer) and then every time you want to create a new database, you just copy that file. No need to do any expensive initialization queries that way. If raw high-speed throughput is needed, skipping that step can make a significant difference.

Re: Inserting a billion rows in SQLite under a minute

#64

Post by scns »

avinassh wrote: post #60
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.

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…

I hate hyperbole but i think the title is fine.

Re: Inserting a billion rows in SQLite under a minute

#65

Post by pachico »

I've been dealing with lots of data and SQLite and my outtakes are:

- language has very little to do since the bottleneck will most likely be the way you insert data

- indeed prepared statements are useful but the performance didn't change much when I did long transactions and commit every certain amount of thousand of rows

- having lots of rows in your table is good but certain queries, like aggregation over many rows, are not what SQLite is great about.

- ClickHouse can easily ingest that and more in a laptop without even any scripting language.

Re: Inserting a billion rows in SQLite under a minute

#66

Post by scns »

mongol wrote: post #57
xeromal wrote: post #40

Earlier quoted context omitted.

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.

Yes, in Germany too.

Re: Inserting a billion rows in SQLite under a minute

#67

Post by fridif »

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.

"USA LANDS MAN ON MARS BEFORE SOVIET UNION"

"WELL ACTUALLY IT WAS THE MOON BUT YOU GET THE IDEA"

Re: Inserting a billion rows in SQLite under a minute

#68

Post by sbarre »

dclowd9901 wrote: post #53
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.

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.

Perhaps "Working towards 1B rows in under a minute" would have been better.

Re: Inserting a billion rows in SQLite under a minute

#70

Post by chrisseaton »

fridif wrote: post #67
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.

"USA LANDS MAN ON MARS BEFORE SOVIET UNION" "WELL ACTUALLY IT WAS THE MOON BUT YOU GET THE IDEA"

There’s no need to be snarky. I didn’t write the title, I’m just explaining what the author means.
Post reply on HN