8 ms·
The Untold Story of SQLite (2021)
- thunderbong 4y agoPreviously - https://news.ycombinator.com/item?id=27718701 https://news.ycombinator.com/item?id=27718701 (2 years ago, 95 comments)
- papito 4y agoThe origins of SQLite are pretty wild. This was one of my favorite episodes.
- deleted 4y ago[deleted]
- counttheforks 4y ago[flagged]
- bobobob420 4y agomarked as duplicate https://news.ycombinator.com/item?id=34504085 https://news.ycombinator.com/item?id=34504085 https://news.ycombinator.com/item?id=34511831 https://news.ycombinator.com/item?id=34511831 These were not taken down. Anyways who cares what the BBC has to say about India. They are not exactly a known unbiased source when it comes to coverage of India and never have been. I slightly agree with some of your thoughts on speaking your mind freely on this board however its probably for the best. Dang knows this place turning into a cesspool is not useful. I will say to other hacker news commentators please do not downvote if you disagree with a comment. Discourse is important but I do agree anonymous discourse is much more nuanced I do not understand how this post is number one. Are you saying Hacker News can promote certain posts to the top and it is not organically curated?
- croes 4y agoIt's not about what the BBC says but what India does to prevent others seeing what the BBC says.
- codetrotter 4y agoElaborate
- counttheforks 4y agoA dupe of 3 and 4 day old posts, which got nearly 200 comments? No. It is not reasonable to just remove that. This is censorship. Especially when you consider that dang tends to instead just post a comment saying: "Previously discussed at ..." > I do not understand how this post is number one. Are you saying Hacker News can promote certain posts to the top and it is not organically curated? Absolutely, yes. The mods can influence the ranking directly and they have openly talked about this. If you search https://hn.algolia.com/ https://hn.algolia.com/ for "penalized", "demoted" or "promoted" you will find discussions regarding this. --- Since dang banned me from posting new replies: > Maybe you haven’t been here long enough to have read the FAQ or know that generally all dupes from 3-4 days ago are marked, and only allowed after at least several months? I've been here for many years. I just make a new account periodically. Maybe you haven't been paying enough attention to see the difference in behavior that dang exhibits when he wants to censor something? Notice how he's usually all over the comments explaining why something was removed as dupe, but now there is nothing except him linking to the older posts (https://news.ycombinator.com/item?id=34559614 https://news.ycombinator.com/item?id=34559614), waiting for an hour or two, and then silently deleting the new one. Not to mention that the subject of the post is completely different. The latest post is about the documentary being removed from archive.org at the request of the BBC. None of the earlier posts cover this.
- dahart 4y agoMaybe you haven’t been here long enough to have read the FAQ or know that generally all dupes from 3-4 days ago are marked, and only allowed after at least several months? https://news.ycombinator.com/newsfaq.html https://news.ycombinator.com/newsfaq.html “If a story has not had significant attention in the last year or so, a small number of reposts is ok. Otherwise we bury reposts as duplicates.”
- dahart 4y agoI’m confused by your comment and edits. You both asked for and complained about being given a reason why the article is a dupe. Which is it? The comment explains why using those two links. Why has this particular article gotten you so riled up? I come here in part for the moderation, it increases the quality of discussions. It’s not surprising that if you keep making a lot of noise and trying to generate off-topic outrage, it might be met with some active moderation, that’s exactly what I would expect, and I’m sorry it’s bothering you so much, but FWIW I don’t mind that it’s happening, and it does seem reasonable and in-bounds from my perspective. BTW to me it seems like you might be making some incorrect assumptions about what’s happening and why; the news topic does seem to be covered adequately with the first one or two articles.
- posharma 4y agoThere’s an industry wide fad (like ChatGPT) and then there’s a hacker news wide fad which is SQLite :-). Not sure how it all started. Last few months (or my be even an year) there has been an unprecedented number of SQLite posts. What gives?
- jestarray 4y agohow is sqlite a fad? its used pretty much everywhere, is simple, and yet surprisingly scalable to some
- posharma 4y agoSQLite is not a fad. It’s just that it never gained so much attention here before.
- cdcarter 4y agoGoogle Trends [0] indicates that sqlite is about as popular as usual. There's been some recent growth in the last year, but that's after a bit of a dip. A regression of the time series would be fairly flat. I suspect popularity here comes in waves through a Katamari Damacy effect. People start reading about a topic, and start posting, thus more people read, research, post, etc... until a saturation point, a cooling off period, and then a rebuild. [0]: obviously not the same as whats happening on HN, I'd love to see someone pull these numbers from the HN API!
- TheRealPomax 4y agoThey were talking about it being a fad here on HN, in terms of submitted (and upvoted to FP) posts per week/month/year. It's always been a topic getting the incidental FP attention, but the number of times something relating to SQLite makes it to the front page here seems to have increased considerably in recent months.
- cdcarter 4y ago
- putna 4y agowow, amazing story. > it’s the old joke of, you get 95% of the functionality with the first 95% of your budget, and the last 5% on the second 95% of your budget. repeats every time
- aidenn0 4y agoI think that's overly optimistic. It implies you can have a complete project with less than double your budget.
- pflanze 4y agoI thought that the implication was that the budget grew by a factor of ~20, not 2. As in, the first 95% finished in 95% of the original budget, the rest finished in 95% of the final budget, so the first 95% took 5% of the final budget. Hmm, maybe I didn't detect the sarcasm in your reply.
- aidenn0 4y agoOh that makes more sense. I thought both were in terms of the original budget
- breck 4y agoOne thing not emphasized enough in this interview: SQLite is public domain. So was the web, which also won against its better funded licensed competitors at the time. The lesson is clear: if you want to win in the long run, you need not just great skills and tech, but you have to go public domain. Or to put it bluntly: #LicensesAreForLosers. https://breckyunits.com/how-the-public-domain-can-win.html https://breckyunits.com/how-the-public-domain-can-win.html
- buttspelunker 4y agoAre you talking about CERN's decision to put their web client and server source code into the public domain in 1993? It seems like a stretch - to me - to attribute the success of the web to that decision.
- breck 4y agoYou couldn't predict that CERN's protocol would win, but you COULD predict that a public domain protocol would win. Proof: For every pair {protocolX,protocolY} where functionality(protocolX) = functionality(protocolY) && isPublicDomain(protocolX) == true && isPublicDomain(protocolY) == false, then speedAndUtility(protocolX) >> speedAndUtility(protocolY). https://breckyunits.com/how-the-public-domain-can-win.html https://breckyunits.com/how-the-public-domain-can-win.html
- andylynch 4y agoIt was probably a big factor. Gopher was a real competitor initially but the University of Minnesota which owned the IP started trying to charge license fees in Feb ‘93- I suspect CERN’s decision was a response to this as well as MOSAIC’s similar efforts around the same time.
- buttspelunker 4y agoWas there a single university that didn't have NCSA Mosaic installed and available to their X terminals after that? This still doesn't add up to me.
- 4y ago
- revskill 4y agoInterview question: Given a 2GB csv file, write a script to do calculation with it. Me: Put all in SQLite and write a SQL query.
- dfinninger 4y agoYeah, there are actually a number of scripts I have at my company that use Python’s baked in SQLite3 module to do something complicated very easily. There’s even a lib for Parquet making analysis on a small number of problematic files quite easy.
- cauthon 4y agohow/where do y’all suggest hosting a shared SQLite db?
- simonw 4y agoThat's exactly what my https://datasette.io https://datasette.io project aims to solve! Recent examples: - https://simonwillison.net/2023/Jan/27/exploring-musiccaps/ https://simonwillison.net/2023/Jan/27/exploring-musiccaps/ - https://simonwillison.net/2022/Aug/21/scotrail/ https://simonwillison.net/2022/Aug/21/scotrail/ - https://simonwillison.net/2022/Sep/5/laion-aesthetics-weeknotes/ https://simonwillison.net/2022/Sep/5/laion-aesthetics-weekno...
- pletnes 4y agoHave a go with duckdb next time - you can query csv files without loading them first.
- simonw 4y agoYou can do that with SQLite too: https://til.simonwillison.net/sqlite/one-line-csv-operations https://til.simonwillison.net/sqlite/one-line-csv-operations (DuckDB is a lot more ergonomic for that kind of thing though - it's really fantastic tech)
- 4y ago
- rompic 4y agoMy favourite random sqlite story: The company I once worked for used an outdated version of sqlite (3.8.6) in one of their products. The databases used got bigger and bigger and in a very big project one of the "already known to be slow"-queries took more than an hour on my laptop making the tool unusable. On a quiet day, I was able to save the temporary table used as part of the process and run the problematic query against it in an isolated fashion. The query returned an extremely high number of results and when I discovered this I questioned my SQL-fu, my sanity and my trust in computers. I found that we were hit by a bug that was fixed 6 years before I discovered it (https://sqlite.org/src/info/6f2222d550f5b0ee7ed https://sqlite.org/src/info/6f2222d550f5b0ee7ed). Sqlite's query planner assumed that a field with a not null constraint can never be null, which isn't the case for the right hand table in a left join. I fixed it by adding a not null check in the query and then later by updating the library. After that the 1 hour query ran in ~700 ms. This faster run time also helped with smaller projects and in the end allowed extending our test suite considerably. Tldr: Keep your dependencies up to date.
- zoomablemind 4y ago> ...Tldr: Keep your dependencies up to date. I'd rather TL;DR it as use query EXPLAIN to see what may be slowing any query down.
- rompic 4y agoI think it was due to EXPLAIN that I found out what's happening. At the same time there have been roughly 60 release notes that mention performance since 3.8.6, so these came for free with the update: https://www.sqlite.org/changes.html https://www.sqlite.org/changes.html
- EGreg 4y agoNow what about the untold true story of SQLite?
- throwaway29303 4y agoShane Harrelson did this for us about 10 years ago. He came up with this huge corpus of SQL statements, and he ran them against every database engine that he could get his hands on. We wanted to make sure everybody got the same answer, and he managed to segfault every single database engine he tried, including SQLite, except for Postgres. Postgres always ran and gave the correct answer. We were never able to find a fault in that. The Postgres people tell me that we just weren’t trying hard enough. It is possible to fault Postgres, but we were very impressed. We crashed Oracle, including commercial versions of Oracle. We crashed DB2. Anything we could get our hands on, we tried it and we managed to crash it, but the point was that we wanted to make sure that SQLite got the same answers for all of these queries, or equivalent answers, because a lot of these queries, they’re indeterminate and the rows might come out in a different order because you [crosstalk 00:25:10] order by clause, so we wanted to make sure that all the database engines got equivalent answers. Mostly, we wanted to make sure that SQLite was getting the same answers everybody else is. That’s another test suite, and then we have lots of smaller ones, as well. Between them all, it’s a lot of testing code, and it takes a long time to run. I hope some people are/were paying attention to this. ;)
- marcosdumay 4y agoYou mean fixing their databases? I would expect Postgres dev to act on it, but not much from the other ones.
- deleted 4y ago[deleted]
- elteto 4y ago“Shane Harrelson […] came up with this huge corpus of SQL statements, and he ran them against every database engine that he could get his hands on. We wanted to make sure everybody got the same answer, and he managed to segfault every single database engine he tried, including SQLite, except for Postgres. Postgres always ran and gave the correct answer. We were never able to find a fault in that. The Postgres people tell me that we just weren’t trying hard enough. It is possible to fault Postgres, but we were very impressed.” Did not expect to find such a cool anecdote about Postgres here!
- deleted 4y ago[deleted]
- brongondwana 4y agoI had some Perl at a previous job which would produce a multi-thousand line SQL statement that was pasted into a phppgadmin screen to get a data set out. Awful workaround but it was smooth and fast. Very nice db to work with
- fijiaarone 4y ago“Freedom means taking care of yourself.”
- bch 4y ago> “Freedom means taking care of yourself.” Others say "freedom" is just another word for "nothing left to lose." By the first definition though, I think one should keep in mind "With great [freedom] comes great responsibility."
- jacob019 4y agoShould I be using file extension .db or .sqlite3 ?
- simonw 4y agoMy experience is that it doesn't matter at all, but .db appears to me a lot more common than .sqlite3
- ocdtrekkie 4y agoI tend to use .sqlite so people know what the file is likely to open in. On Windows systems, I've found endless .dat files that could be from any number of database formats. I've noticed the tendency for .db but I consider it a bad practice, all things considered.
- breck 4y agoI prefer .sqlite3. It's 10x clearer what it is.
- supriyo-biswas 4y agoetilqs[1] [1] https://github.com/mackyle/sqlite/blob/3cf493d/src/os.h#L52-L66 https://github.com/mackyle/sqlite/blob/3cf493d/src/os.h#L52-...
- harryvederci 4y agoThat's a prefix, not a suffix.
- deleted 4y ago[deleted]
- fijiaarone 4y agoI’m interested in looking at fossil SCM - not the built in web server, wiki, and issue tracking, but the auto sync and simplified interface appeal for personal projects, and maybe an embedded versioned file based CMS.
- maxk42 4y agoI've used git since it was first released to the public and recently I feel like I know less about it than ever before. I switched to fossil for personal projects around a year ago and haven't regretted it one bit!
- dognotdog 4y agoI kind of went the other way as github/gitlab took off. I was using fossil for years because it was so self contained with issue tracking and everything, and it is great, but the lack of easy two-way interop with github had me gradually drop it for new projects, when I anticipate some level of other contributors. That being said, going from fossil->git via git-export is very simple and painless.
- ilyt 4y agoThat reminds me of git-bug https://news.ycombinator.com/item?id=33730417 https://news.ycombinator.com/item?id=33730417 Basically issue tracking added to git repo directly That being said my personal project are just push/pull/commit so I don't see the reason to change. Maybe some script to auto-push commits every hour or something but, well I have backups so that's not really required either
- panda_fish 4y agoThanks for that amazing trip down memory lane… > Wow, I’ve got an SQL database running on my Palm Pilot. Weirdly, my thesis in 2000 was to write an SQL parser for Palm (Palm V - still have it). I remember there being such a massive gap in the market for a pervasive standards-compliant data storage solution. I used javacc, which is still around I think - I can’t imagine it covered 1% of the features of SQLite though. Bravo!
- zoomablemind 4y agoAnybody figured out what's those formulations may be on the white board in the article's hero image? Quantum mechanics?
- sega_sai 4y agoI could recognise the Schroedinger's equation and one Maxwell's equation.
- wslh 4y agoIt would be great to add to the archeology of embedded databases several ones written by Konstantin Knizhnik [1]. Here [2] he compares nine of his databases for multiple programming languages. [1] http://www.garret.ru/index.html http://www.garret.ru/index.html [2] http://www.garret.ru/compare.html http://www.garret.ru/compare.html
- kjellsbells 4y agoThis was a great story, nicely told. Looking into it as an outsider, it seems that a key inflection point was their adoption of a really industrial-strength test discipline at just the right time. Not only was it impressive to have written the engine, but it seems to be doubly impressive to knuckle down for a year to get the test coverage - and it paid off in spades.