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

Re: Inserting a billion rows in SQLite under a minute

#11

Post by nezirus » Sun, Jul 18, 2021, 2:13 PM UTC

jonnycomputer wrote: post #7

bob1029 wrote: post #4

You can go even faster if you can organize your problem such that you simply start from a copy of a template SQLite database each time (it is just a file/byte sequence). We do SQL evaluation for a lot of business logic throughout our product, and we have found that starting from a template database (i.e. one with the schema predefined and canonical values populated) can save a lot of time when working in tight loops.

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

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 as good as it gets, providing your IO is optimal.

Re: Inserting a billion rows in SQLite under a minute

#12

Post by avinassh » Sun, Jul 18, 2021, 2:13 PM UTC

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.

Re: Inserting a billion rows in SQLite under a minute

#13

Post by asicsp » Sun, Jul 18, 2021, 2:14 PM UTC

>The larger DB required more than 30 minutes to generate. So I spent 30+ hours to reduce the 30 mins of running time :p

Worth it.

>I could just run s the script again.

Found a typo.

Re: Inserting a billion rows in SQLite under a minute

#14

Post by jonnycomputer » Sun, Jul 18, 2021, 2:20 PM UTC

>The generated data would be random with following constraints: The area column would hold six digits area code (any six digits would do, no validation). The age would be any of 5, 10, or 15. The active column is either 0 or 1.

That is about 6 million combinations. This is not such a big space that it would be impractical to precompute all possible combinations. I wonder if hashing in to such a precomputed table might help. Might not if the overhead of maintaining the lookup table is too high.

Re: Inserting a billion rows in SQLite under a minute

#15

Post by elteto » Sun, Jul 18, 2021, 2:21 PM UTC

rmetzler wrote: post #8

Is PRAGMA temp_store = MEMORY the same as putting the file on a TempFS?

Maybe, maybe not. I imagine writing directly to memory will always be faster than writing to a filesystem, even if it’s ultimately backed by RAM. Might be a negligible difference though.

Re: Inserting a billion rows in SQLite under a minute

#16

Post by eismcc » Sun, Jul 18, 2021, 2:22 PM UTC

nezirus wrote: post #11

jonnycomputer wrote: post #7

Earlier quoted context omitted.

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

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…

This idea is mentioned as one of the future work at the bottom.

Re: Inserting a billion rows in SQLite under a minute

#17

Post by einpoklum » Sun, Jul 18, 2021, 2:22 PM UTC

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.

Re: Inserting a billion rows in SQLite under a minute

#18

Post by mimir » Sun, Jul 18, 2021, 2:29 PM UTC

Database optimization posts are always interesting, but it's really hard to do any apples to apples comparison. Your performance is going to depend mostly on your hardware, internal database settings and tunings, and OS level tunings. I'm glad this one included some insight into the SQLite settings disabled, but there's always going to be too many factors to easily compare this to your own setup.

For most SQL systems, the fastest way to do inserts is always just going to batched inserts. There's maybe some extra tricks to reduce network costs/optimize batches [0], but at it's core you are still essentially inserting into the table through the normal insert path. You can basically then only try and reduce the amount of work done on the DB side per insert, or optimize your OS for your workload.

Some other DB systems (more common in NoSQL) let you actually do real bulk loads [1] where you are writing direct(ish) database files and actually bypassing much of the normal write path.

[0] https://dev.mysql.com/doc/refman/5.7/en/insert-optimization.... [1] https://blog.cloudera.com/how-to-use-hbase-bulk-loading-and-...

Re: Inserting a billion rows in SQLite under a minute

#19

Post by eatonphil » Sun, Jul 18, 2021, 2:32 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.

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.

Re: Inserting a billion rows in SQLite under a minute

#20

Post by bob1029 » Sun, Jul 18, 2021, 2:33 PM UTC

jonnycomputer wrote: post #7

bob1029 wrote: post #4

You can go even faster if you can organize your problem such that you simply start from a copy of a template SQLite database each time (it is just a file/byte sequence). We do SQL evaluation for a lot of business logic throughout our product, and we have found that starting from a template database (i.e. one with the schema predefined and canonical values populated) can save a lot of time when working in tight loops.

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.