15 ms·

Show HN: Static.wiki – read-only Wikipedia using a 43GB SQLite file

796 points by segfall 5y ago

lanevorockz 5y ago
Maybe that would be a nice way to start Wikipedia alternatives, sijce it became a form of political activism the trustworthiness of Wikipedia fell down significantly.
punnerud 5y ago
Related post 89 days ago: https://news.ycombinator.com/item?id=27016630 https://news.ycombinator.com/item?id=27016630 (Hosting SQLite databases on GitHub Pages or any static file hoster)
voldemort1968 5y ago
There's also https://www.kiwix.org/en/ https://www.kiwix.org/en/ which is great to take on long offline flights, browsing through wikipedia.
- rycomb 5y ago
  And someone keeps a relatively up-to-date .slob file with the entire wikipedia (that can be read with Aard2), which I find more useful than kiwix! (A link to it can be found in the GitHub page with Aard dictionaries)
  - ce4 5y ago
    Aard2 Android: https://github.com/itkach/aard2-android https://github.com/itkach/aard2-android Aard2 web: https://github.com/itkach/aard2-web https://github.com/itkach/aard2-web Dictionaries: https://github.com/itkach/slob/wiki/Dictionaries https://github.com/itkach/slob/wiki/Dictionaries
    - exikyut 5y ago
      files: enwiki-20210616* size: 18.9 GB How'd they manage that...
  - zelienople 5y ago
    Seriously? They couldn't make the content something standard like a .zip file? Or a zip file with a SQL database in it? Bloody monkeys always have to go and create a new standard for everything. They will argue it's for performance or something, but it's really an exercise in ego. "Oh, mine's better!" No, it's not. What is better is what works with everything else in the world. And that index! We couldn't capitalize the proper name of Wikipedia? And do you not understand there are visually impaired people in the world who can't read light red text on a white background? What is wrong with people? And the terminology! What in the fuckety is Aard2? What is a .slob file? Why is it that everyone uses obscure acronyms or whatever for everything and never explains anything? Ego again, probably. Let's create a language that no one else understands to make ourselves feel superior! No, you are not superior, you are just turning language into thick vegetable soup. Didn't you people go to university, or did you not pay attention when they taught you that the first reference is always completely spelled out and explained and you can use the acronyms (or whatever they are) after that. It's only common sense that you don't spout gibberish and nonsense words without explaining then in the first instance, unless your goal is to confuse everyone for the purpose of making yourself feel superior. And the Google sheet and the flat index, what a hodgepodge of nonsense. What the heck does it mean? What is the difference between the different versions? Who knows? I have to go now. My eyes are bleeding from trying to read light red text on a bright white background. I'll check back again once I get a Braille laptop screen.
- lmm 5y ago
  Does it actually work? I tried and it would always give up partway through the downloads (and most of the times when I ask in these threads it turns out the person recommending it doesn't actually use it, they just think it's a cool idea...)
  - 41b696ef1113 5y ago
    I too have had rather disappointing results with Kiwix. Love the idea, but the implementation needs work. At this point, given the complexities of parsing Wikitext, I wish Wikipedia themselves would offer SQLite dumps of the latest text only content.
mongol 5y ago
Perfect if stuck on a remote deserted island! Must be the closest existing thing to the Hitchhiker's guide?
- r00fus 5y ago
  Hitchhikers Guide to Wikipedia? HHGTWP?
- wyldfire 5y ago
  Towel not included.
- yreg 5y ago
  I installed a reader with a Wikipedia dump (without multimedia) on the original iPhone. At the time (at least in my region) WiFi was quite rare and it really did feel like the Hitchhiker's guide and incredibly powerful. I only kept it for a few weeks though since it consumed almost all of the space. (And I imagine it was probably my language mutation, the english Wikipedia must have been too big already.)
- pronoiac 5y ago
  You might look at Kiwix, and the data sources they've indexed: https://www.kiwix.org/en/ https://www.kiwix.org/en/ (Oddly, it's crashing at launch on my iPad right now)
  - deleted 5y ago
    [deleted]
- phil294 5y ago
  I'm not trying to kill the fun here, but since this thing relies on a working internet connection, it would probably be pretty useless on a remote island
  - dTal 5y ago
    You could host it locally easily enough!
nynx 5y ago
This is fantastic. I knew that we’d see things like this once that hosting sqlite databases project was posted.
arianon 5y ago
Shocked to find out that this is very much a static website, it's "merely" downloading a small portion of the 43GB SQLite file in question with HTTP Range requests, and then it uses a WASM-compiled copy of SQLite to query the requested data. Very impressive.
- simonw 5y ago
  It's the same trick that was described here - it's absolutely brilliant: https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-github-pages/ https://phiresky.github.io/blog/2021/hosting-sqlite-database...
  - JoeyBananas 5y ago
    wouldn't this be vulnerable to DOS attacks? I can make the database run arbitrarily long and complicated queries
    - CodesInChaos 5y ago
      The database runs on the client. It can't do anything you couldn't do through any other http client.
    - simonw 5y ago
      Since the work all happens in your browser, the only victim of a long complicated query would be you own browse and the S3 bandwidth bill of the person hosting the database (since you'd end up sucking down a lot of data). But if you want to jack up someone's S3 bandwidth bill there are already easier ways to do it!
      - floatingatoll 5y ago
        It's a static file, CDN it.
        simlevesque 5y ago
max1cc 5y ago
The search seems to be broken right now, but it looks like you can access whatever page you want via URL (e.g. http://static.wiki/en/Computer_Science http://static.wiki/en/Computer_Science)
sgt 5y ago
thgttg.sqlite3
- schwartzworld 5y ago
  hhgttg
simonw 5y ago
I'm nervous on your behalf for your S3 bill, have you done napkin maths for how much this will cost to host, especially given the relative inefficiency of the autocomplete queries? A trick that might help: create a separate SQLite table containing just the article titles to autocomplete against. That might give you queries that use smaller ranges of the file.
- sitkack 5y ago
  I built a wikipedia reader on the ipad years ago, I used two sqlite databases, one for the article titles using sqlite FTS then I compressed the articles themselves and merged the compressed chunks into a set of files < 2G, the title database had the offset into the compressed blobs. Only including the most popular pages, it was just under 10G.
  - simonw 5y ago
    That's really clever. Were you storing the article text as compressed data in BLOB columns in a SQLite table?
    - sitkack 5y ago
      I realize I wasn't clear. I used 2 sqlite databases just to store the article titles and offsets into binary files (<2GB) that had chunks of compressed data. Each chunk contained 10s-100s of stories, so that the decompressed around was around 20megs I believe. I had planned to store everything in sqlite, but there were too many issues. The FTS extension to sqlite is excellent, it made whole classes of problems literally melt away. Recalling now, the article text itself, I stored in groups of articles concatenated together so that that data compression could take advantage of similar texts. This data was just Lua data structures, with the article title and article body in an array. So I knew which chunk it was in, but I had to decompress a whole chunk at a time and then iterate through the list of decompressed articles to find the specific one. I was trying to tune search time, decompression time and compression ratio. I had a handful of parameters in the job that would take the xml dumps and create the sqlite and compressed blobs. Memory consumption would spike as a chunk was decompressed, but after a call to the Lua garbage collector after pulling the article out of the decompression buffer, everything was fine again. This was late 2010 or so. The job that processed the XML into database+bin data took over an hour to run on my laptop. If I did it again, I'd use a compression library that was tuned for decompression and used an external dictionary. For the index, I'd use fst [1] The project stalled out in parsing the wikitext, there are now libraries that can handle the parsing. I have considered resurrecting it, with jpeg2000 or other image codecs, one could include the images as well. The system did not have FTS over the article text itself. I had planned to use some sort tree of bloom filters, something similar to [2]. I was really trying to keep the total footprint of all the data under 10G or so it could fit on the majority of devices. Now with larger flash sizes, better codecs and better compression libraries, the functionality and performance could be greatly enhanced. I learned a ton from this project, I really recommend getting deep into something, but also working within your limits and having fallback plans when something looks insurmountable. There were plenty of times where I thought I was a dead end, only to say, "what if ..." the next day, run a quick experiment and be on to the next insurmountable roadblock. [1] https://github.com/BurntSushi/fst https://github.com/BurntSushi/fst [2] https://arxiv.org/abs/2002.03057 https://arxiv.org/abs/2002.03057
suprbeast 5y ago
We put this on an e-reader once. Onboard wikipedia, it was cool. It was 2011.
- spiritplumber 5y ago
  cool! was it able to update itself or no?
dleslie 5y ago
Two immediate issues: 1. Doesn't work if Privacy Badger is active. 2. Nim (Programming Language) is not indexed.
- chadcatlett 5y ago
  I noticed if you go directly to the page for Nim, it works. http://static.wiki/en/Nim_(programming_language) http://static.wiki/en/Nim_(programming_language)
- JadeNB 5y ago
  > 2. Nim (Programming Language) is not indexed. You and dom96 (https://news.ycombinator.com/item?id=28013376 https://news.ycombinator.com/item?id=28013376) both noticed this issue, just two minutes apart. I guess there's some sort of inclination among HN readers testing out a static copy of Wikipedia to search for Nim? :-)
  - johnisgood 5y ago
    dom96 is a core developer of the language, and author of Nim in Action, so it makes sense for him to look it up. I don't know the OP.
- Retr0id 5y ago
  More generally, it appears that any link containing brackets is broken/missing.
  - qwertox 5y ago
    This appears to be the issue.
- qwertox 5y ago
  http://static.wiki/en/Gardena http://static.wiki/en/Gardena does not contain a link to http://static.wiki/en/Gardena_(company) http://static.wiki/en/Gardena_(company) yet https://en.wikipedia.org/wiki/Gardena https://en.wikipedia.org/wiki/Gardena does.
twotwotwo 5y ago
That's super cool. The autocomplete trickiness is fun. Looks (from links in GitHub) like it's using full-text search and currently needs to scan a lot to rank results. Doing just prefix search might be a cheap half-solution to autocomplete, and maybe layering on hacks around stopwords etc. can get you a bit further. For another angle, enwiki's full compressed list of titles (enwiki-20210720-all-titles-in-ns0.gz) is 86MB. Grabbing the whole dang thing (in the background?) and doing something with it client-side is a different sort of approach from what this page is going for but not unthinkable.
dom96 5y ago
Really cool! I think I see a bug: searching “Nim” shows “Nim (programming language)” but it’s not clickable.
- segfall 5y ago
  Yep, you're right. The wikitext -> markdown conversion strips out some links, noticeably on the disambiguation pages, for an unclear reason.
segfall 5y ago
OP here. Happy to take questions, with the caveat that my work here is mostly glue. Some background: https://github.com/segfall/static-wiki https://github.com/segfall/static-wiki The datasets I generated: https://www.kaggle.com/segfall/markdownlike-wikipedia-dumps-in-sqlite https://www.kaggle.com/segfall/markdownlike-wikipedia-dumps-... The original author of this approach: https://news.ycombinator.com/item?id=28013514 https://news.ycombinator.com/item?id=28013514
- segfall 5y ago
  For added fun, you can try switching between different databases: http://static.wiki/en/United_States http://static.wiki/en/United_States http://static.wiki/zh/美国 http://static.wiki/zh/美国 http://static.wiki/fr/United_States http://static.wiki/fr/United_States http://static.wiki/enwikiquote/United_States http://static.wiki/enwikiquote/United_States http://static.wiki/simple/United_States http://static.wiki/simple/United_States
- johnwheeler 5y ago
  Is the db in memory?
  - deleted 5y ago
    [deleted]
  - segfall 5y ago
    sql.js is in memory, but the database file is only partially loaded from the static file host.
  - exikyut 5y ago
    No, as other comments have noted it's stored in S3.
- ddtaylor 5y ago
  Is there a way to get this simple theme / stylesheet to work for the regular Wikipedia? I really like it.
andymoe 5y ago
Reminds me that at one point each page of the Apple Wiki Server that shipped with OS X Server back in the day used one SQLite file per page. Worked ok-ish.
phiresky 5y ago
Author of the referenced blog (and library) here. This is great! The full text search engine in SQLite is sadly not really good for this - one reason is that it uses standard B-Trees, another is that it forces storing all token positions if you want BM25 sorting, which is a huge overhead for articles as long as Wikipedia's. But that doesn't mean full text search isn't possible in a very efficient manner with statically hosted data! I wrote a proof of concept of making the Rust-based tantivy library work in the same way, which has a lot of internal things that can make the index much smaller and more efficient than SQLite's. It's also >10x faster in creating the search index. Here's the demo also for Wikipedia: https://demo.phiresky.xyz/tmp-ytccrzsovkcjoylr/dist/index.html https://demo.phiresky.xyz/tmp-ytccrzsovkcjoylr/dist/index.ht... I'm not sure if it's more efficient than the SQlite version in this form, but it definitely has more upward potential and is more fun to work with. And the corresponding draft PR: https://github.com/tantivy-search/tantivy/pull/1067 https://github.com/tantivy-search/tantivy/pull/1067 I sadly haven't gotten around to working on it more and writing an article about it. Other people are also working on using this stuff to make Sci-hub and LibGen more available by using this in combination with IPFS for distributed and "uncensorable" hosting which is pretty awesome. Edit: Just realized that the OP demo only searches in article titles, while mine searches in full article contents by default. You can search in only the titles in my demo by querying `title:harry title:potter`
- segfall 5y ago
  Thank you, Phiresky. My little side project only exists because of your work.
  - ignoramous 5y ago
    Congrats on your side project! This wonderful discussion exists because of your work.
- tored 5y ago
  I have been thinking of using FTS5 with SQLite to search emails. Not close of 43 GB ofc, so I would probably not have any major performance problems, but still is FTS5 any good? Or should I look into other solutions?
Camillo 5y ago
Read-only Wikipedia is just "Wikipedia" for 99.99% of users. I can't remember the last time I've even considered clicking the edit button.
- quenix 5y ago
  Give it a try! It's strangely fulfilling.
  - foxpurple 5y ago
    Editing an existing page is usually pretty easy and conflict free. Creating a new page is close to impossible and strictly guarded. It’s almost not worth trying for a beginner because you will almost certainly be rejected. The moderators require multiple approved sources of information for a page to be created. These pages must be reputable sources (IE not blog posts, forum posts, or other wikis), and must not have any kind of commercial link to the subject. So if you create a page about a product and you state that it has feature X, you can not use the companies own page that talks about having X as a reference. So basically you can’t make a page about something until a major news website writes about it or you can reference it in some formal study. I tried to create a few pages. One of them being about a file format and found that I could not use the companies website with the PDF describing the spec as a reference for the format because there is a commercial link between the file format and the source linked.
    - quenix 5y ago
      This is so painfully true. I've tried to submit two articles so far to AfC (articles for creation) and both were rejected. It's puzzling to me because I often see extremely low quality articles (with just one sentence, for example), published onto mainspace.
      - thealig 5y ago
        Oh interesting. I've started editing a bit on Wikipedia recently, and seems like there are a lot of potential articles to improve upon. The deletionism issue reminds me of this article https://www.gwern.net/In-Defense-Of-Inclusionism https://www.gwern.net/In-Defense-Of-Inclusionism
andyxor 5y ago
this is awesome, seems to be a good use case for using IPFS decentralized storage, either for copies of the big sqllite db dump file, or (as an alternative) for the individual wiki articles "snapshots" i think it would be easier to keep it in sync with original wiki using the second option, where each article in static.wiki is synced with original wikipedia pages and updated individually, vs. updating the huge sqllite dump on every change in the originals then the sqllite will be only holding metadata and links to static (and may be version controlled) versions of individual static pages/article snapshots, so the size will be much smaller and easier to distribute on ipfs Edit: looks like there is similar effort in the works, to put wiki on ipfs https://blog.ipfs.io/24-uncensorable-wikipedia/ https://blog.ipfs.io/24-uncensorable-wikipedia/
geenat 5y ago
Cool example of minimalism. But hate to be that guy- if you're querying a database, it is not static.
- dmd 5y ago
  Nope, it is - unless you think "loading html pages from a remote web server" is also not static. The point is there's no computation going on on the server, behind the scenes; your browser is doing all the work of knowing what to fetch, NOT the remote end, with the magic of range requests.
- peterkelly 5y ago
  That's what's so brilliant about this. The database query happens on the client, not on the server. The server side is entirely static hosting.
chovybizzass 5y ago
Searched for "porn" -- got this: http://static.wiki/en/Progressive_outer_retinal_necrosis http://static.wiki/en/Progressive_outer_retinal_necrosis
yukinon 5y ago
There's a bug when the target article is titled the same as the disambiguation page. Search for "Earthbound", and try clicking on the article for the video game. It'll bring you right back to the disambiguation page.
deleted 5y ago
[deleted]
NelsonMinar 5y ago
I've been using static copies of Wikipedia for years; they're great to have when traveling. I mostly use the Kiwix app. Their ZIM format database for all English is 83 GB; 43 GB for one without pictures. Compares nicely to the 43 GB here. I once spent an hour in the Louvre learning about Rubens astonishing paintings of Marie de' Medici thanks to Kiwix. Without it I was just like "huh, nice paintings, I wonder what they are?" They're incredible and I owe that moment of kismet to Wikipedia, Kiwix, and my mobile phone. https://en.wikipedia.org/wiki/Marie_de%27_Medici_cycle https://en.wikipedia.org/wiki/Marie_de%27_Medici_cycle
- elcritch 5y ago
  That’s brilliant! Art museums are fun but there’s so much context missing for most paintings.
- mohiit 5y ago
  I remember having it on my ipod touch way back in 2012 when it was only around 7gb.
8note 5y ago
I find that most wikipedia pages I'd want to look at are utterly unintelligible with communication conventions youd need to look up separately to understand the article. I can't use an offline Wikipedia without that coursework
harlanji 5y ago
Bonito. I want and yet have problems downloading even a 350mb file on my carrier, tethered via 2 different devices. Being able to DL something like this should be a requirement to be called an Internet carrier. Non-mobile is not an option for some, eg. low income unemployed and homeless. Maybe they’ve been a software engineer for 20 years and can reach unreachable markets. (No doubt it’s more profitable to spy and steal from the vulnerable, if God allows). What if a customer needs their records and can not afford to get them and they lose their life or suffer significantly? Who pays the balance? They didn’t need use the Internet? Okay, well they did use it and they do during pandemic times.
mwidell 5y ago
I remember having the iPhone 2G back in 2007, and that someone created a way to store a 5GB wikipedia dump (the complete wikipedia at that time, but without photos) on it, so you could have the whole Wikipedia in your pocket. At the time it felt pretty amazing.
- spiritplumber 5y ago
  Don't panic!
deleted 5y ago
[deleted]
graderjs 5y ago
That's awesome man. I didn't know you could host Wikipedia on your own client / own back-end. Seems like this means if someone could built a better interface and performance for Wikipedia they could begin to steal some of their traffic. Is it not?
Scaevolus 5y ago
You should compress the individual article texts with zlib. Right now, looking up an article text for a decent size article ("Virus") takes 10 round-trips-- 5 appear to be btree traversals, but another 5 (!) are to read in the page content, as chunks of 8K 8K 16K 32K 64K. Compressing the article might cut one round trip off that Increasing the sqlite page size to a much larger size like 64KB or 128KB may improve average latency too, since fewer btree pages would need to be fetched to find the offset of the desired row, and most page contents could be read with one fetch. This matters more when fetches over the network take ~100ms.
andrepd 5y ago
How does this compare to https://www.kiwix.org/en/ https://www.kiwix.org/en/
hutrdvnj 5y ago
Since it doesn't require any backend, a IPFS mirror would be great.
ctenb 5y ago
If wikipedia used this approach they could stop asking for money for server upkeep.
- brokensegue 5y ago
  wikipedia keeps getting updated. this approach is static. it's really easy to serve a static wikipedia lots of different ways.
dnamlin 5y ago
I plowed the 40 GiB database file into sqlite_zstd_vfs [1] which reduced it 75% to 10 GiB. This plug-in also supports HTTP access [2] in the spirit of phiresky's, however, I don't have a WebAssembly build of mine yet, so it's a library for desktop/command-line apps for now. You can try it out on Linux or macOS x86-64: pip3 install genomicsqlite genomicsqlite https://f000.backblazeb2.com/file/mlin-public/static.wiki/en.zstd.db "select text from wiki_articles where title = 'SQLite'" ("genomicsqlite" is the CLI for my Genomics Extension [3], which is built around these Zstandard compression & web layers.) [1] https://github.com/mlin/sqlite_zstd_vfs https://github.com/mlin/sqlite_zstd_vfs [2] https://github.com/mlin/sqlite_web_vfs https://github.com/mlin/sqlite_web_vfs [3] https://mlin.github.io/GenomicSQLite https://mlin.github.io/GenomicSQLite EDITS: I expanded on this comment in this gist https://gist.github.com/mlin/ee20d7c5156baf9b12518961f36590c3 https://gist.github.com/mlin/ee20d7c5156baf9b12518961f36590c... If you want to download the whole en.zstd.db, then please kindly get it from zenodo (which doesn't support HTTP range requests, but is free): https://zenodo.org/record/5149677 https://zenodo.org/record/5149677
- jart 5y ago
  Great work but why not compress with deflate if you are serving http requests since then you could directly copy the database content to the wire as gzip encoded responses.
  - dnamlin 5y ago
    With sqlite_zstd_vfs the data are compressed beforehand and stored that way "at rest", so web responses are directly copied to the wire controlled by HTTP range headers, similarly to the OP. They need to be decompressed by a client library sitting between SQLite and the wire.
jart 5y ago
[Hit #1 on HN hosting 43gb Wikipedia SQLite DB on AWS] https://justine.lol/hugemistake.gif https://justine.lol/hugemistake.gif Each time I post a link to Hacker News that becomes popular it ends up being at least a $100 in bandwidth bills from Google Cloud, and I've got a simple tiny unbloated website. This poor guy is going to go bankrupt. The Internet dark age is like Ma Bell all over again when we'd get those $500 long distance bills for dialing into BBSs.
cafxx 5y ago
[nit] you should really run analyze before vacuum, not the other way around (https://github.com/segfall/static-wiki/blob/6d455de149c24e2a11b526f949388a0c896e5e30/scripts/optimize_db.js#L18-L24 https://github.com/segfall/static-wiki/blob/6d455de149c24e2a...)
xchip 5y ago
Could article pages be stored as a torrent?
dankwizard 5y ago
Hi, I am just commenting to mention a bug. When I look at page for The Smith's self titled album ( http://static.wiki/en/The_Smiths_(album) http://static.wiki/en/The_Smiths_(album) ) , the track listing section doesn't populate. Other than that, I love the simplicity and cleanness of the design
Soullessmate 5y ago
This is great stuff, looks like I can serve a db straight off my hard drive to https://keybase.pub https://keybase.pub! https://book.keybase.io/docs/files#storage https://book.keybase.io/docs/files#storage

796 points by segfall 5y ago

Maybe that would be a nice way to start Wikipedia alternatives, sijce it became a form of political activism the trustworthiness of Wikipedia fell down significantly.

punnerud 5y ago

Related post 89 days ago: https://news.ycombinator.com/item?id=27016630 https://news.ycombinator.com/item?id=27016630 (Hosting SQLite databases on GitHub Pages or any static file hoster)

voldemort1968 5y ago

There's also https://www.kiwix.org/en/ https://www.kiwix.org/en/ which is great to take on long offline flights, browsing through wikipedia.

rycomb 5y ago
And someone keeps a relatively up-to-date .slob file with the entire wikipedia (that can be read with Aard2), which I find more useful than kiwix! (A link to it can be found in the GitHub page with Aard dictionaries)
- ce4 5y ago
  Aard2 Android: https://github.com/itkach/aard2-android https://github.com/itkach/aard2-android Aard2 web: https://github.com/itkach/aard2-web https://github.com/itkach/aard2-web Dictionaries: https://github.com/itkach/slob/wiki/Dictionaries https://github.com/itkach/slob/wiki/Dictionaries
  - exikyut 5y ago
    files: enwiki-20210616* size: 18.9 GB How'd they manage that...
- zelienople 5y ago
  Seriously? They couldn't make the content something standard like a .zip file? Or a zip file with a SQL database in it? Bloody monkeys always have to go and create a new standard for everything. They will argue it's for performance or something, but it's really an exercise in ego. "Oh, mine's better!" No, it's not. What is better is what works with everything else in the world. And that index! We couldn't capitalize the proper name of Wikipedia? And do you not understand there are visually impaired people in the world who can't read light red text on a white background? What is wrong with people? And the terminology! What in the fuckety is Aard2? What is a .slob file? Why is it that everyone uses obscure acronyms or whatever for everything and never explains anything? Ego again, probably. Let's create a language that no one else understands to make ourselves feel superior! No, you are not superior, you are just turning language into thick vegetable soup. Didn't you people go to university, or did you not pay attention when they taught you that the first reference is always completely spelled out and explained and you can use the acronyms (or whatever they are) after that. It's only common sense that you don't spout gibberish and nonsense words without explaining then in the first instance, unless your goal is to confuse everyone for the purpose of making yourself feel superior. And the Google sheet and the flat index, what a hodgepodge of nonsense. What the heck does it mean? What is the difference between the different versions? Who knows? I have to go now. My eyes are bleeding from trying to read light red text on a bright white background. I'll check back again once I get a Braille laptop screen.
lmm 5y ago
Does it actually work? I tried and it would always give up partway through the downloads (and most of the times when I ask in these threads it turns out the person recommending it doesn't actually use it, they just think it's a cool idea...)
- 41b696ef1113 5y ago
  I too have had rather disappointing results with Kiwix. Love the idea, but the implementation needs work. At this point, given the complexities of parsing Wikitext, I wish Wikipedia themselves would offer SQLite dumps of the latest text only content.

mongol 5y ago

Perfect if stuck on a remote deserted island! Must be the closest existing thing to the Hitchhiker's guide?

r00fus 5y ago
Hitchhikers Guide to Wikipedia? HHGTWP?
wyldfire 5y ago
Towel not included.
yreg 5y ago
I installed a reader with a Wikipedia dump (without multimedia) on the original iPhone. At the time (at least in my region) WiFi was quite rare and it really did feel like the Hitchhiker's guide and incredibly powerful. I only kept it for a few weeks though since it consumed almost all of the space. (And I imagine it was probably my language mutation, the english Wikipedia must have been too big already.)
pronoiac 5y ago
You might look at Kiwix, and the data sources they've indexed: https://www.kiwix.org/en/ https://www.kiwix.org/en/ (Oddly, it's crashing at launch on my iPad right now)
- deleted 5y ago
  [deleted]
phil294 5y ago
I'm not trying to kill the fun here, but since this thing relies on a working internet connection, it would probably be pretty useless on a remote island
- dTal 5y ago
  You could host it locally easily enough!

nynx 5y ago

This is fantastic. I knew that we’d see things like this once that hosting sqlite databases project was posted.

arianon 5y ago

Shocked to find out that this is very much a static website, it's "merely" downloading a small portion of the 43GB SQLite file in question with HTTP Range requests, and then it uses a WASM-compiled copy of SQLite to query the requested data. Very impressive.

simonw 5y ago
It's the same trick that was described here - it's absolutely brilliant: https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-github-pages/ https://phiresky.github.io/blog/2021/hosting-sqlite-database...
- JoeyBananas 5y ago
  wouldn't this be vulnerable to DOS attacks? I can make the database run arbitrarily long and complicated queries
  - CodesInChaos 5y ago
    The database runs on the client. It can't do anything you couldn't do through any other http client.
  - simonw 5y ago
    Since the work all happens in your browser, the only victim of a long complicated query would be you own browse and the S3 bandwidth bill of the person hosting the database (since you'd end up sucking down a lot of data). But if you want to jack up someone's S3 bandwidth bill there are already easier ways to do it!
    - floatingatoll 5y ago
      It's a static file, CDN it.
      - simlevesque 5y ago

max1cc 5y ago

The search seems to be broken right now, but it looks like you can access whatever page you want via URL (e.g. http://static.wiki/en/Computer_Science http://static.wiki/en/Computer_Science)

sgt 5y ago

thgttg.sqlite3

schwartzworld 5y ago
hhgttg

simonw 5y ago

I'm nervous on your behalf for your S3 bill, have you done napkin maths for how much this will cost to host, especially given the relative inefficiency of the autocomplete queries? A trick that might help: create a separate SQLite table containing just the article titles to autocomplete against. That might give you queries that use smaller ranges of the file.

sitkack 5y ago
I built a wikipedia reader on the ipad years ago, I used two sqlite databases, one for the article titles using sqlite FTS then I compressed the articles themselves and merged the compressed chunks into a set of files < 2G, the title database had the offset into the compressed blobs. Only including the most popular pages, it was just under 10G.
- simonw 5y ago
  That's really clever. Were you storing the article text as compressed data in BLOB columns in a SQLite table?
  - sitkack 5y ago
    I realize I wasn't clear. I used 2 sqlite databases just to store the article titles and offsets into binary files (<2GB) that had chunks of compressed data. Each chunk contained 10s-100s of stories, so that the decompressed around was around 20megs I believe. I had planned to store everything in sqlite, but there were too many issues. The FTS extension to sqlite is excellent, it made whole classes of problems literally melt away. Recalling now, the article text itself, I stored in groups of articles concatenated together so that that data compression could take advantage of similar texts. This data was just Lua data structures, with the article title and article body in an array. So I knew which chunk it was in, but I had to decompress a whole chunk at a time and then iterate through the list of decompressed articles to find the specific one. I was trying to tune search time, decompression time and compression ratio. I had a handful of parameters in the job that would take the xml dumps and create the sqlite and compressed blobs. Memory consumption would spike as a chunk was decompressed, but after a call to the Lua garbage collector after pulling the article out of the decompression buffer, everything was fine again. This was late 2010 or so. The job that processed the XML into database+bin data took over an hour to run on my laptop. If I did it again, I'd use a compression library that was tuned for decompression and used an external dictionary. For the index, I'd use fst [1] The project stalled out in parsing the wikitext, there are now libraries that can handle the parsing. I have considered resurrecting it, with jpeg2000 or other image codecs, one could include the images as well. The system did not have FTS over the article text itself. I had planned to use some sort tree of bloom filters, something similar to [2]. I was really trying to keep the total footprint of all the data under 10G or so it could fit on the majority of devices. Now with larger flash sizes, better codecs and better compression libraries, the functionality and performance could be greatly enhanced. I learned a ton from this project, I really recommend getting deep into something, but also working within your limits and having fallback plans when something looks insurmountable. There were plenty of times where I thought I was a dead end, only to say, "what if ..." the next day, run a quick experiment and be on to the next insurmountable roadblock. [1] https://github.com/BurntSushi/fst https://github.com/BurntSushi/fst [2] https://arxiv.org/abs/2002.03057 https://arxiv.org/abs/2002.03057

suprbeast 5y ago

We put this on an e-reader once. Onboard wikipedia, it was cool. It was 2011.

spiritplumber 5y ago
cool! was it able to update itself or no?

dleslie 5y ago

Two immediate issues: 1. Doesn't work if Privacy Badger is active. 2. Nim (Programming Language) is not indexed.

chadcatlett 5y ago
I noticed if you go directly to the page for Nim, it works. http://static.wiki/en/Nim_(programming_language) http://static.wiki/en/Nim_(programming_language)
JadeNB 5y ago
> 2. Nim (Programming Language) is not indexed. You and dom96 (https://news.ycombinator.com/item?id=28013376 https://news.ycombinator.com/item?id=28013376) both noticed this issue, just two minutes apart. I guess there's some sort of inclination among HN readers testing out a static copy of Wikipedia to search for Nim? :-)
- johnisgood 5y ago
  dom96 is a core developer of the language, and author of Nim in Action, so it makes sense for him to look it up. I don't know the OP.
Retr0id 5y ago
More generally, it appears that any link containing brackets is broken/missing.
- qwertox 5y ago
  This appears to be the issue.
qwertox 5y ago
http://static.wiki/en/Gardena http://static.wiki/en/Gardena does not contain a link to http://static.wiki/en/Gardena_(company) http://static.wiki/en/Gardena_(company) yet https://en.wikipedia.org/wiki/Gardena https://en.wikipedia.org/wiki/Gardena does.

twotwotwo 5y ago

That's super cool. The autocomplete trickiness is fun. Looks (from links in GitHub) like it's using full-text search and currently needs to scan a lot to rank results. Doing just prefix search might be a cheap half-solution to autocomplete, and maybe layering on hacks around stopwords etc. can get you a bit further. For another angle, enwiki's full compressed list of titles (enwiki-20210720-all-titles-in-ns0.gz) is 86MB. Grabbing the whole dang thing (in the background?) and doing something with it client-side is a different sort of approach from what this page is going for but not unthinkable.

dom96 5y ago

Really cool! I think I see a bug: searching “Nim” shows “Nim (programming language)” but it’s not clickable.

segfall 5y ago
Yep, you're right. The wikitext -> markdown conversion strips out some links, noticeably on the disambiguation pages, for an unclear reason.

segfall 5y ago

OP here. Happy to take questions, with the caveat that my work here is mostly glue. Some background: https://github.com/segfall/static-wiki https://github.com/segfall/static-wiki The datasets I generated: https://www.kaggle.com/segfall/markdownlike-wikipedia-dumps-in-sqlite https://www.kaggle.com/segfall/markdownlike-wikipedia-dumps-... The original author of this approach: https://news.ycombinator.com/item?id=28013514 https://news.ycombinator.com/item?id=28013514

segfall 5y ago
For added fun, you can try switching between different databases: http://static.wiki/en/United_States http://static.wiki/en/United_States http://static.wiki/zh/美国 http://static.wiki/zh/美国 http://static.wiki/fr/United_States http://static.wiki/fr/United_States http://static.wiki/enwikiquote/United_States http://static.wiki/enwikiquote/United_States http://static.wiki/simple/United_States http://static.wiki/simple/United_States
johnwheeler 5y ago
Is the db in memory?
- deleted 5y ago
  [deleted]
- segfall 5y ago
  sql.js is in memory, but the database file is only partially loaded from the static file host.
- exikyut 5y ago
  No, as other comments have noted it's stored in S3.
ddtaylor 5y ago
Is there a way to get this simple theme / stylesheet to work for the regular Wikipedia? I really like it.

andymoe 5y ago

Reminds me that at one point each page of the Apple Wiki Server that shipped with OS X Server back in the day used one SQLite file per page. Worked ok-ish.

phiresky 5y ago

Author of the referenced blog (and library) here. This is great! The full text search engine in SQLite is sadly not really good for this - one reason is that it uses standard B-Trees, another is that it forces storing all token positions if you want BM25 sorting, which is a huge overhead for articles as long as Wikipedia's. But that doesn't mean full text search isn't possible in a very efficient manner with statically hosted data! I wrote a proof of concept of making the Rust-based tantivy library work in the same way, which has a lot of internal things that can make the index much smaller and more efficient than SQLite's. It's also >10x faster in creating the search index. Here's the demo also for Wikipedia: https://demo.phiresky.xyz/tmp-ytccrzsovkcjoylr/dist/index.html https://demo.phiresky.xyz/tmp-ytccrzsovkcjoylr/dist/index.ht... I'm not sure if it's more efficient than the SQlite version in this form, but it definitely has more upward potential and is more fun to work with. And the corresponding draft PR: https://github.com/tantivy-search/tantivy/pull/1067 https://github.com/tantivy-search/tantivy/pull/1067 I sadly haven't gotten around to working on it more and writing an article about it. Other people are also working on using this stuff to make Sci-hub and LibGen more available by using this in combination with IPFS for distributed and "uncensorable" hosting which is pretty awesome. Edit: Just realized that the OP demo only searches in article titles, while mine searches in full article contents by default. You can search in only the titles in my demo by querying `title:harry title:potter`

segfall 5y ago
Thank you, Phiresky. My little side project only exists because of your work.
- ignoramous 5y ago
  Congrats on your side project! This wonderful discussion exists because of your work.
tored 5y ago
I have been thinking of using FTS5 with SQLite to search emails. Not close of 43 GB ofc, so I would probably not have any major performance problems, but still is FTS5 any good? Or should I look into other solutions?

Camillo 5y ago

Read-only Wikipedia is just "Wikipedia" for 99.99% of users. I can't remember the last time I've even considered clicking the edit button.

quenix 5y ago
Give it a try! It's strangely fulfilling.
- foxpurple 5y ago
  Editing an existing page is usually pretty easy and conflict free. Creating a new page is close to impossible and strictly guarded. It’s almost not worth trying for a beginner because you will almost certainly be rejected. The moderators require multiple approved sources of information for a page to be created. These pages must be reputable sources (IE not blog posts, forum posts, or other wikis), and must not have any kind of commercial link to the subject. So if you create a page about a product and you state that it has feature X, you can not use the companies own page that talks about having X as a reference. So basically you can’t make a page about something until a major news website writes about it or you can reference it in some formal study. I tried to create a few pages. One of them being about a file format and found that I could not use the companies website with the PDF describing the spec as a reference for the format because there is a commercial link between the file format and the source linked.
  - quenix 5y ago
    This is so painfully true. I've tried to submit two articles so far to AfC (articles for creation) and both were rejected. It's puzzling to me because I often see extremely low quality articles (with just one sentence, for example), published onto mainspace.
    - thealig 5y ago
      Oh interesting. I've started editing a bit on Wikipedia recently, and seems like there are a lot of potential articles to improve upon. The deletionism issue reminds me of this article https://www.gwern.net/In-Defense-Of-Inclusionism https://www.gwern.net/In-Defense-Of-Inclusionism

andyxor 5y ago

this is awesome, seems to be a good use case for using IPFS decentralized storage, either for copies of the big sqllite db dump file, or (as an alternative) for the individual wiki articles "snapshots" i think it would be easier to keep it in sync with original wiki using the second option, where each article in static.wiki is synced with original wikipedia pages and updated individually, vs. updating the huge sqllite dump on every change in the originals then the sqllite will be only holding metadata and links to static (and may be version controlled) versions of individual static pages/article snapshots, so the size will be much smaller and easier to distribute on ipfs Edit: looks like there is similar effort in the works, to put wiki on ipfs https://blog.ipfs.io/24-uncensorable-wikipedia/ https://blog.ipfs.io/24-uncensorable-wikipedia/

geenat 5y ago

Cool example of minimalism. But hate to be that guy- if you're querying a database, it is not static.

dmd 5y ago
Nope, it is - unless you think "loading html pages from a remote web server" is also not static. The point is there's no computation going on on the server, behind the scenes; your browser is doing all the work of knowing what to fetch, NOT the remote end, with the magic of range requests.
peterkelly 5y ago
That's what's so brilliant about this. The database query happens on the client, not on the server. The server side is entirely static hosting.

chovybizzass 5y ago

Searched for "porn" -- got this: http://static.wiki/en/Progressive_outer_retinal_necrosis http://static.wiki/en/Progressive_outer_retinal_necrosis

yukinon 5y ago

There's a bug when the target article is titled the same as the disambiguation page. Search for "Earthbound", and try clicking on the article for the video game. It'll bring you right back to the disambiguation page.

deleted 5y ago

[deleted]

NelsonMinar 5y ago

I've been using static copies of Wikipedia for years; they're great to have when traveling. I mostly use the Kiwix app. Their ZIM format database for all English is 83 GB; 43 GB for one without pictures. Compares nicely to the 43 GB here. I once spent an hour in the Louvre learning about Rubens astonishing paintings of Marie de' Medici thanks to Kiwix. Without it I was just like "huh, nice paintings, I wonder what they are?" They're incredible and I owe that moment of kismet to Wikipedia, Kiwix, and my mobile phone. https://en.wikipedia.org/wiki/Marie_de%27_Medici_cycle https://en.wikipedia.org/wiki/Marie_de%27_Medici_cycle

elcritch 5y ago
That’s brilliant! Art museums are fun but there’s so much context missing for most paintings.
mohiit 5y ago
I remember having it on my ipod touch way back in 2012 when it was only around 7gb.

8note 5y ago

I find that most wikipedia pages I'd want to look at are utterly unintelligible with communication conventions youd need to look up separately to understand the article. I can't use an offline Wikipedia without that coursework

harlanji 5y ago

Bonito. I want and yet have problems downloading even a 350mb file on my carrier, tethered via 2 different devices. Being able to DL something like this should be a requirement to be called an Internet carrier. Non-mobile is not an option for some, eg. low income unemployed and homeless. Maybe they’ve been a software engineer for 20 years and can reach unreachable markets. (No doubt it’s more profitable to spy and steal from the vulnerable, if God allows). What if a customer needs their records and can not afford to get them and they lose their life or suffer significantly? Who pays the balance? They didn’t need use the Internet? Okay, well they did use it and they do during pandemic times.

mwidell 5y ago

I remember having the iPhone 2G back in 2007, and that someone created a way to store a 5GB wikipedia dump (the complete wikipedia at that time, but without photos) on it, so you could have the whole Wikipedia in your pocket. At the time it felt pretty amazing.

spiritplumber 5y ago
Don't panic!

deleted 5y ago

[deleted]

graderjs 5y ago

That's awesome man. I didn't know you could host Wikipedia on your own client / own back-end. Seems like this means if someone could built a better interface and performance for Wikipedia they could begin to steal some of their traffic. Is it not?

Scaevolus 5y ago

You should compress the individual article texts with zlib. Right now, looking up an article text for a decent size article ("Virus") takes 10 round-trips-- 5 appear to be btree traversals, but another 5 (!) are to read in the page content, as chunks of 8K 8K 16K 32K 64K. Compressing the article might cut one round trip off that Increasing the sqlite page size to a much larger size like 64KB or 128KB may improve average latency too, since fewer btree pages would need to be fetched to find the offset of the desired row, and most page contents could be read with one fetch. This matters more when fetches over the network take ~100ms.

andrepd 5y ago

How does this compare to https://www.kiwix.org/en/ https://www.kiwix.org/en/

hutrdvnj 5y ago

Since it doesn't require any backend, a IPFS mirror would be great.

ctenb 5y ago

If wikipedia used this approach they could stop asking for money for server upkeep.

brokensegue 5y ago
wikipedia keeps getting updated. this approach is static. it's really easy to serve a static wikipedia lots of different ways.

dnamlin 5y ago

I plowed the 40 GiB database file into sqlite_zstd_vfs [1] which reduced it 75% to 10 GiB. This plug-in also supports HTTP access [2] in the spirit of phiresky's, however, I don't have a WebAssembly build of mine yet, so it's a library for desktop/command-line apps for now. You can try it out on Linux or macOS x86-64: pip3 install genomicsqlite genomicsqlite https://f000.backblazeb2.com/file/mlin-public/static.wiki/en.zstd.db "select text from wiki_articles where title = 'SQLite'" ("genomicsqlite" is the CLI for my Genomics Extension [3], which is built around these Zstandard compression & web layers.) [1] https://github.com/mlin/sqlite_zstd_vfs https://github.com/mlin/sqlite_zstd_vfs [2] https://github.com/mlin/sqlite_web_vfs https://github.com/mlin/sqlite_web_vfs [3] https://mlin.github.io/GenomicSQLite https://mlin.github.io/GenomicSQLite EDITS: I expanded on this comment in this gist https://gist.github.com/mlin/ee20d7c5156baf9b12518961f36590c3 https://gist.github.com/mlin/ee20d7c5156baf9b12518961f36590c... If you want to download the whole en.zstd.db, then please kindly get it from zenodo (which doesn't support HTTP range requests, but is free): https://zenodo.org/record/5149677 https://zenodo.org/record/5149677

jart 5y ago
Great work but why not compress with deflate if you are serving http requests since then you could directly copy the database content to the wire as gzip encoded responses.
- dnamlin 5y ago
  With sqlite_zstd_vfs the data are compressed beforehand and stored that way "at rest", so web responses are directly copied to the wire controlled by HTTP range headers, similarly to the OP. They need to be decompressed by a client library sitting between SQLite and the wire.

jart 5y ago

[Hit #1 on HN hosting 43gb Wikipedia SQLite DB on AWS] https://justine.lol/hugemistake.gif https://justine.lol/hugemistake.gif Each time I post a link to Hacker News that becomes popular it ends up being at least a $100 in bandwidth bills from Google Cloud, and I've got a simple tiny unbloated website. This poor guy is going to go bankrupt. The Internet dark age is like Ma Bell all over again when we'd get those $500 long distance bills for dialing into BBSs.

cafxx 5y ago

[nit] you should really run analyze before vacuum, not the other way around (https://github.com/segfall/static-wiki/blob/6d455de149c24e2a11b526f949388a0c896e5e30/scripts/optimize_db.js#L18-L24 https://github.com/segfall/static-wiki/blob/6d455de149c24e2a...)

xchip 5y ago

Could article pages be stored as a torrent?

dankwizard 5y ago

Hi, I am just commenting to mention a bug. When I look at page for The Smith's self titled album ( http://static.wiki/en/The_Smiths_(album) http://static.wiki/en/The_Smiths_(album) ) , the track listing section doesn't populate. Other than that, I love the simplicity and cleanness of the design

Soullessmate 5y ago

This is great stuff, looks like I can serve a db straight off my hard drive to https://keybase.pub https://keybase.pub! https://book.keybase.io/docs/files#storage https://book.keybase.io/docs/files#storage