phiresky

10 ms·

61.
by phiresky 4y ago
The zstd estimator is not that great, so you should definitely keep the auto-detection of the file system on. If you try to compress a completely incompressible file, it will still be extremely slow at higher compression levels: https:&#x2
62.
by phiresky 4y ago
> You can potentially get better results this way than a generic solution like the article because you might have some application knowledge about what rows are likely to be similar to others The linked project actually does exactly this
63.
by phiresky 4y ago
Transparent to the application - basically the SQL queries (select, insert, update) in your code base don't have to change at all. This would be in contrast to e.g. compressing the data in your code before inserting it into the databas
64.
by phiresky 4y ago
I did actually previously write a VFS, though it did something else entirely: https://phiresky.github.io/blog/2021/hosting-sqlite-database... You're right that a comparison to compression VFS is completely mi
65.
by phiresky 4y ago
It would be interesting to try something like this on the file system level, yes. Basically you could heuristically find interesting files, then train a compression dictionary for e.g. each file or each directory. Then you'd compress 3
66.
Show HN: Reduce SQLite database size by up to 80% with transparent compression(github.com)
420 points by phiresky 4y ago|90 comments
67.
by phiresky 4y ago
This article is a bit more rambling than I'd like but I've been sitting on this project for almost a year now, never really fully able polish it to my satisfaction. Let me know if something is unclear ;)
68.
sqlite-zstd: Transparent dictionary-based row-level compression for SQLite(phiresky.github.io)
5 points by phiresky 4y ago|1 comments
69.
by phiresky 4y ago
https://casual-effects.com/markdeep/ has a similar idea but different execution. I use something similar for my blog (I don't have a WYSIWYG editor for the widgets though, that's pretty neat): The posts are w
70.
by phiresky 4y ago
Alternatives (without judgement): https://github.com/cantino/mcfly https://github.com/jcsalterego/historian https://github.com/larkery/zsh-histdb + https://github.
71.
by phiresky 5y ago
The popular pg-promise library for PostgreSQL in NodeJS has a similar issue - for some types of queries ("Formatting Filters") it interpolates parameters itself instead of using real parameterized queries. This is especially bad b
72.
by phiresky 5y ago
The shutter-open-for-half-a-frame or "180° shutter angle" is commonly recommended as a default for shooting video. It is the result of having to compromise between inaccurate recording and a blurry video when your frame rate is lo
73.
by phiresky 5y ago
This only got better (at least here) because booking.com had to change their practices after legal action from the EU consumer protection commission. > As a market leader, it is vital that companies like Booking.com meet their responsib
74.
by phiresky 5y ago
According to the docs, looks like the ANY type in a strict table might do that: > In a STRICT table, a column of type ANY always preserves the data exactly as it is received. For an ordinary non-strict table, a column of type ANY will at
75.
by phiresky 5y ago
You can also try sqlite-zstd [1], which is an sqlite extension allows transparent compression of individual rows by automatically training dictionaries based on groups of data rows. Disclaimer: I made it and it's not production ready [
76.
by phiresky 5y ago
There's a free and opensource program called `diffpdf` that can compare both visually and by text. It has a GUI and works great, though it doesn't specially handle layout changes. It's included in the normal package sources i
77.
by phiresky 5y ago
Looks like Netlify changed something since I wrote this article regarding what headers they send. Detecting support for Range-requests is kinda tricky and relies on heuristics [1]. Not sure why it still works in Chrome though. You can go to
78.
by phiresky 5y ago
If you're talking about a normal local SQlite DB and your dataset is less than maybe 100GB of plaintext then SQLite FTS will work fine regarding performance.
79.
by phiresky 5y ago
You can chunk the file into e.g. 2MB chunks. The CDN can then cache all or the most commonly used ones. That's what I did in the original blog post to be able to host it on GitHub Pages.
80.
by 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
81.
by phiresky 5y ago
Wikidata is a great resource, but the SPARQL query language seems more annoyingly complicated and confusing than it could be. I'm using Wikidata to automatically categorize visited websites and used programs for time tracking purposes.
82.
by phiresky 5y ago
From what I understand, to really get spatial audio from stereo headphones, you need to use a HRTF (head-related transfer function) specific to the person. There's an open dataset of 50 different HRTFs [1] and a long video to compare t
83.
by phiresky 5y ago
I never understood why the `sizes` attribute has to be so complicated. Why do I have to decide on which image to choose based on the display size instead of it automatically choosing the best image based on the size the image is displayed a
84.
by phiresky 5y ago
Redis EXPIRE doesn't actually delete any data after it expires though. Active deletion happens at random, so you can easily still have expired values in memory months later: > Redis keys are expired in two ways: a passive way, and a
85.
by phiresky 5y ago
Thank you, I really appreciate it. It's pretty fun to do this kind of thing for yourself, but it's really rewarding to be able to share it with other people.
86.
by phiresky 5y ago
The actual transferred data for the sqlite code should only be 550kB (gzip compression). Stripping out the write parts is a good idea. SQLite actually has a set of compile time flags to omit features [1]. I just tried enabling as many of th
87.
by phiresky 5y ago
I think it wouldn't change much - SQLite is already pretty optimized towards reads, for example a write always replaces a whole page and locks the whole DB. The free pages can easily be removed by doing VACUUM beforehand which should b
88.
by phiresky 5y ago
That's true, but it also means that random access will always use at least that amount of data even if it only has to fetch a tiny amount. I did a few (non-scientific) benchmarks on a few queries and 1kB seemed like an OK compromise. A
89.
by phiresky 5y ago
Yeaah I felt like at that point the article was already long enough so I didn't bother describing the DOM part too much - even though I spent more time implementing that than I did implementing the rest ;) Basically SQLite has a virtua
90.
by phiresky 5y ago
The B-Tree is a tree that in this case is perfectly balanced. So if you do a query with an index in a database it will fetch an logarithmic amount of data from the index and then a constant amount of data from the table. For the example the

More ›