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

41–50 of 164 posts

Re: Inserting a billion rows in SQLite under a minute

#41

Post by [deleted] »

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.

[deleted]

Re: Inserting a billion rows in SQLite under a minute

#42

Post by atleta »

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?

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.

Re: Inserting a billion rows in SQLite under a minute

#43

Post by avinassh »

tyingq wrote: post #34
eatonphil wrote: post #19

Earlier quoted context omitted.

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

TIL, this is great! I think this could make things even easier instead of writing the raw pages

Re: Inserting a billion rows in SQLite under a minute

#44

Post by gunapologist99 »

bob1029 wrote: post #20
jonnycomputer wrote: post #7

Earlier quoted context omitted.

Are you suggesting have a template and then updating rows with random values where necessary?

Yes. Start from known-good database and then update or insert with needed deltas. You could even have more than you need in the template file and truncate/drop what is not relevant for the current context. Depending on the situation, it might be faster to start from a database containing all of the possible things.

This makes sense. Just do a copy or copy-on-write to an existing database file; you could even have the actual bytes of an empty (or pre-DDL'ed) sqlite file in memory in your app, rather than needing to do a disk copy.

Re: Inserting a billion rows in SQLite under a minute

#45

Post by gunapologist99 »

svdr wrote: post #28

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

Exactly.

  thousand = 1000
  million  = 1000 * thousand (or 1000^2)
  billion  = 1000 * million (or 1000^3)
  trillion = 1000 * billion (or 1000^4)
(not to discount regional differences)

https://www.brainyquote.com/quotes/everett_dirksen_201172

Re: Inserting a billion rows in SQLite under a minute

#46

Post by einpoklum »

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.

It's very likely it wouldn't. (Decent) analytical DBMSes have a whole lot up their sleeves which just choosing one plain-vanilla data structure for your table doesn't even approach in terms of performance. The benefit may be well upwards of 10x in many real-world scenario.

Of course, if you know you get one single query which you know in advance, carefully build a data structure to cater just to that, and you know your data structures beyond the just the basics - then, yes, a DBMS would be overkill. But it still won't be a walk in the park.

Re: Inserting a billion rows in SQLite under a minute

#47

Post by xeromal »

atleta wrote: post #42
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?

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.

Re: Inserting a billion rows in SQLite under a minute

#49

Post by faizshah »

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.

Sqlite provides a full text search module (FTS5) which provides a lot of nice search features. I have used this numerous times to build serverless apis for large static datasets for frontend analytical experiences like data tables without setting up elasticsearch. Thats one use case.

Another is they support this closures.c extension which is very nice for rapid queries on tree structured data locally in memory. The JSON1 extension is also nice for rapidly querying/reshaping deeply nested json data. Theres also spellfix1 that can provide fuzzing capabilities. If you need any of these with low latency and in memory its a great choice.

Sqlite is great for rapidly building low latency static data serving services for frontend experiences. Something I’m exploring now is combining sqlite with streamlit to rapidly build data exploration UIs.

Like how many times have you wanted to quickly add fuzzy matching or full text search to some program? You use fuzzywuzzy but its pretty slow, sqlite provides performant implementations of this stuff thats super simple to set up.

Re: Inserting a billion rows in SQLite under a minute

#50

Post by MobiusHorizons »

I would recommend looking into dtrace or other observability tools to figure out what to optimize next. So far you have basically had to guess where the slowness is and optimize that.

Lastly, how long does it take to make a copy of an already prepared 1b row SQLite file?that seems easier than generating a new one.

Post reply on HN