31–40 of 166 posts
What if you create something that is optimized for performance without offering the full SQL language? Just a container to store small files with padding to avoid the expensive open/close calls.
For small- to mid-sized projects, I’ve always realized huge gains in simplicity by haves “Files” tables to store various assets. It means instances in a web-farm can pull the files down when they initialize easily, it means files are automatically versioned, it provides an obvious place to put the files when they are being uploaded on the Admin panel. It means all the files are getting backed up as part of the databa…
My (naive?) view is that Nginx caches static files by default after the first hit so the inconvenience isn't worth it in my web apps. Would I really see an improvement switching to a SQLite-esque file system?
Once I tried to hookup a way to serve the files directly from the DB through IIS, and it was a total failure. By the time I had figured out getting all the various headers right, I was still running into some weird caching issue that would corrupt the request at random intervals.
So now, if the files are static hosting assets, I will initialize a ‘cache/‘ folder on the local filesystem (for that machine on the web-farm) when the first request comes in and just read all those files back into the local file system for the web server to handle.
The only remaining concern is refreshing the cache when files change in the DB.
Next week on Show HN: `sqlitefs`, the SQLite-backed FUSE filesystem?
In the past I've had huge speedups by moving simple single-table databases that had grown a bit (e.g., time series data) from sqlite to postgres. Insert performance is also quite bad forcing you to write applications with extra caching layers to be able to do a bunch of inserts at once. Sqlite is great for many applications but it's speed is somewhat oversold. I really wish the postgres engine was embeddable into app…
Obviously if it was embeddable it wouldn’t be as fast and be subject to the same design limitations as SQLite.
We've been moving so much into web apps that optimizing for desktop apps like these is not a big concern these days. Sqlite ends up being used in that niche but it's extremely easy to run into it's limitations even in very simple personal usage. It's fine for what it is, an alternative to open(), but there are plenty of use cases where optimizing for fast open is not relevant.
Here's an example of where I'd really like postgres to replace sqlite:
https://github.com/pedrocr/syncer/
I've had to add an extra caching layer because of sqlite INSERT performance. Given that I've already had to code around limitations and the schema is simple I will probably end up using a simpler non-SQL database in the future instead.
Since they don't bother explaining which Linux filesystem they used, I suspect they mean EXT4, and the particular version of EXT4 in the kernel in that particular version of Ubuntu.
The real news is: ext4 is about as fast as SQLite for small objects.
As someone who has spent time consulting for different kinds of "we do stuff on the internet" companies, I can confidently say that this is a premature optimization for 99.999% of the companies/projects. The companies simply don't have enough IO traffic to need it. Is it cool? Sure. Is it sexy? Maybe. Is it needed ? Nope. Do boring stuff. Use files. If you send your small files over the internet via web and you need…
Earlier quoted context omitted.
Obviously if it was embeddable it wouldn’t be as fast and be subject to the same design limitations as SQLite.
There's nothing about being embeddable that's a limitation. All I really want is a lib I can link to that gives me an entry point to launch the database against a given folder, with some of the tweaks the postgres config file has, and that gives me a connection back. That entry point can launch however many threads it wants and behave exactly like postgres itself. After that my app can connect to a standard postgres…
An always-running in-memory server process is going to be a lot better at concurrency than multiple embeddable libraries trying to take locks out on the same file on disk.
Really depends on which filesystem too, you'll notice here that Microsoft's NTFS implementation is horrendously slow, HFS+ is several times faster, but still slower. Since they don't bother explaining which Linux filesystem they used, I suspect they mean EXT4, and the particular version of EXT4 in the kernel in that particular version of Ubuntu. The real news is: ext4 is about as fast as SQLite for small objects.
What if you create something that is optimized for performance without offering the full SQL language? Just a container to store small files with padding to avoid the expensive open/close calls.