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

111–120 of 164 posts

Re: Inserting a billion rows in SQLite under a minute

#111

Post by roman-holovin »

gfodor wrote: post #99
roman-holovin wrote: post #97

INSERT INTO user (area, age, active) SELECT abs(random()) % 1000000, (abs(random()) % 3 + 1) * 5, abs(random()) % 2 FROM generate_series(1, 100000000, 1) Faster by 10% than fastest author implementation on my machine - 19 seconds against 21 for 'threaded_batched'.

I have to assume this was rejected as a valid approach, but if not, this whole thread gets 10x more interesting than it already was. I hope the author responds.

Well, even if you just insert zeros instead of random values, it takes 9 seconds on my computer to insert 100M rows, so even that is not a 1B rows per minute.

And I think INSERT INTO ... SELECT is the fastest way to bulk insert data into sqlite.

Also, I have tried to use carray sqlite feature that allow to share memory with sqlite and use recursive CTE to query it, but it is slower. Though, you can pass values you've generated from Rust instead of using random().

Re: Inserting a billion rows in SQLite under a minute

#112

Post by anigbrowl »

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.

It's a great article! Always nice to see clear well-written guides that save people time from reinventing the wheel.

Re: Inserting a billion rows in SQLite under a minute

#114

Post by [deleted] »

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.

[deleted]

Re: Inserting a billion rows in SQLite under a minute

#115

Post by polyrand »

Really good article, I’ve experimented with this kind of workloads in SQLite a few times and found it insightful. One note though, using:

  pragma temp_store = memory;
only affects temporary tables and indices, not the main database itself[0]

[0] https://sqlite.org/pragma.html#pragma_temp_store

Re: Inserting a billion rows in SQLite under a minute

#116

Post by Fiahil »

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 did similar Rust/Python record generation experiments, and I can relate to these numbers.

However, I think your Rust threaded trial might be a little bit off in subtle ways. I would truly expect it to perform about several times better than single threaded Rust and async Rust (async is generally slower on these workloads, but still faster than Python)

Edit : After reading your rust code, you might have room for some improvements :

- don’t rely on random, simply cycle through your values

- pre-allocate your vecs with ˋwith_capacityˋ

- dont use channels, prefer deques..

- ..or even better, don’t use synchronization primitives and open one connection per thread (not sure if it will work with sqlite?)

Re: Inserting a billion rows in SQLite under a minute

#117

Post by Groxx »

Yep - I discovered most of this a while back when I needed to do a bunch of exploring of ~200m rows of data (like 50GB). I chucked it into SQLite and was done with everything in a day, it was absolutely wonderful. This post is a pretty good summary of what to change and why.

Prepared statements should be considered the norm for SQLite - they have pretty major performance benefits, and any decent ORM or query engine can probably do it for you implicitly or with a single flag, so it's practically free for many applications. I'm always surprised how often I see applications or benchmarks or "how to be fast with SQLite" not using them, so I'm definitely glad to see them covered here :)

Re: Inserting a billion rows in SQLite under a minute

#119

Post by xxs »

adrianN wrote: post #27
xxs wrote: post #24

Earlier quoted context omitted.

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.

If the set of queries you want to run is fixed you can probably beat SQLite with a lot of work, if the queries are dynamic I doubt that you can do much better than SQLite without herculean efforts. A in-memory database is thus a good option if you either don't care too much about the runtime of your queries and you want to save a bunch of development time, or if you don't know enough about your workload to be able to…

>you can =probably= beat SQLite with =a lot of= work,

Actually, I am beyond certain. When it's all about the memory no database comes even remotely close to a properly picked datastructures + structure/objects layout.

If I need transaction log + persistence, databases have a decent application.

In more than 20y, I have never had a case: Yay, I can use relation structures in memory b/c I don't know what I am going to do with the data.

Re: Inserting a billion rows in SQLite under a minute

#120

Post by xxs »

tgv wrote: post #32
xxs wrote: post #24

Earlier quoted context omitted.

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.

>For something like redis,

Redis is in pretty much the same category. Testing is sort of a valid case, if you are committed to write pure SQL with minimal use of any dialect specifics (but even 'create table' syntax might be different). Running on the real thing is close to no replacement when it comes to databases.

Many databases have docker images nowadays, so it aint hard to run them locally. Likely at a point you'd want to optimize the SQL, itself, rendering the tests incompatible.

Post reply on HN