Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQLite small blob storage: 35% Faster Than the Filesystem

sqlite.org

Post reply on HN
Print view View original

151–160 of 208 posts

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#151

Post by microcolonel »

notacoward wrote: post #28
craigds wrote: post #12

For web map tiles (millions of tiny PNGs), everyone who's anyone stores their tiles in sqlite rather than on disk: https://www.mapbox.com/help/an-open-platform/#mbtiles

FYI, in sqlite is still on disk.

For what it's worth, I think Mapbox's vector tiles can be stored in around 48GiB of RAM, which is perfectly within reason for a server. It's also the sort of data which is simple to shard. They can have multiple geolocated databases for a local area, and maybe fewer for international queries. This can be done pretty simply at the load balancer level.

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#152

Post by programd »

wyc wrote: post #35

I know that SQLite performs exceptionally on embedded devices including most phones, IoT devices, and more. However, for better or worse, it's a flat file. Does anyone know of a TCP/IP-speaking SQL database that would work well on an embedded device? PostgreSQL/MariaDB seem kinda heavy, and the net couplers for SQLite look pretty unsupported.

MySQL/MariaDB can be tuned to run very well on low resource devices. Remember that they were originally developed back in the days of 486 CPUs. A couple of years ago I had MySQL backing a commercial product on a 512M ARM7 5400RPM device (we originally developed it with 256M of memory). Performance was not an issue for our use case even with on the order of a million rows in the DB.

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#153

Post by cat199 »

craigds wrote: post #12

For web map tiles (millions of tiny PNGs), everyone who's anyone stores their tiles in sqlite rather than on disk: https://www.mapbox.com/help/an-open-platform/#mbtiles

Definitely an interesting use case -

Part of the concerns outlined in that article and of more importance for this company are ease of distribution/compatibility for customers of mapsets and mobile devices, rather than raw read/write performance, however.

Though the storage size claims might be relevant, and definitely a clear example that sqlite can be very workable and performant in the context of blob storage.

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#154

Post by JBReefer »

Const-me wrote: post #86

SQLite is good when you’re mostly reading. For writing, the major drawback of SQLite is it doesn’t support concurrent writes. All filesystems do (at least when writing different files), all full-fledged RDBMS-es do, even some embedded databases do (like ESENT). Yet, in SQLite only a single thread can write. Even embedded chips are multicore these days…

Isn't that because SQLLite's whole use case is "simple, small, fast database that competes with fopen() and not Postgres"? At least in my experience on NTFS, unless you know the section that 2 threads are writing to, and that section is different, you can't write to a file concurrently (N.B.: in C#)

I'd be happy to be wrong (seriously that would make my day if someone could explain a clean way to do the above) but if I want to get "big boy database features" then I'd use a heavy database like SQL Server. I think it's great that there's an option like SQLLite out there for rapid prototyping and "store all your music metadata" type applications!

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#155

Post by dkersten »

imron wrote: post #99
dkersten wrote: post #98

Earlier quoted context omitted.

which involves dropping a single header in your project and about 10 lines of code. 10 lines of user code + the sqlite library. Using other formats like zip is hardly any more user code (+ some external library).

The sqlite 'library' is a single header file and a single c file that you can just drop in your project and use.

So are many libraries for other formats.

Btw I'm personally a big fan of SQLite, I just think the argument here is a bit of a straw man.

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#156

Post by pjc50 »

troutaway123 wrote: post #149
patio11 wrote: post #114

Earlier quoted context omitted.

My experience (for an application which had a working set of under 1 GB of files in the 50kb to N MB range, and approximately 50 GB persisted at any given time) was that preserving access to the toolchain which operates trivially with files was worth the additional performance overhead of working with the files and, separately, occasionally having to retool things to e.g. not have 10e7 files in a single folder, which…

¿Porque no los dos? Store the files in a database but expose them via FUSE.

That gives you the time cost of a filesystem plus the time cost of a database plus an extra round-trip in and out of kernel space.

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#157

Post by phamilton »

fnord123 wrote: post #141
icebraining wrote: post #121

Earlier quoted context omitted.

Ah, but the filesystem has to do all that as well! It must receive a path, parse it, and then execute the query, with possible optimizations (eg. ext4 even has indexes implemented with hashed b-trees). A filesystem is just an hierarchical database.

>A filesystem is just an hierarchical database. Loosely. Today's file systems aren't transactional (i.e. acid compliance), which is a basic property that most people consider necessary for a database.

That's not really a true statement. I use plenty of databases that aren't transactional beyond a single record.

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#158

Post by troutaway123 »

pjc50 wrote: post #156
troutaway123 wrote: post #149

Earlier quoted context omitted.

¿Porque no los dos? Store the files in a database but expose them via FUSE.

That gives you the time cost of a filesystem plus the time cost of a database plus an extra round-trip in and out of kernel space.

> preserving access to the toolchain which operates trivially with files was worth the additional performance overhead of working with the files

Sounds like they're OK with that.

In any case, couldn't you avoid the kernel trips with some dylib foolery (assuming the toolchain is dynamically linked)?

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#159

Post by _jal »

phamilton wrote: post #157
fnord123 wrote: post #141

Earlier quoted context omitted.

>A filesystem is just an hierarchical database. Loosely. Today's file systems aren't transactional (i.e. acid compliance), which is a basic property that most people consider necessary for a database.

That's not really a true statement. I use plenty of databases that aren't transactional beyond a single record.

It is a true statement. You're not a member of the set of "most people":

> a basic property that most people consider necessary

I use non-ACID stores for some purposes, but don't consider them real databases.

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#160

Post by _jal »

Klathmon wrote: post #129
rusanu wrote: post #120

Earlier quoted context omitted.

> Do a query, get back a handle to a file that you can treat just like you opened it yourself Things are a bit more complex. For one, the trivial problem of client vs. server host. The DB cannot return a handle (a FD) from the server, because it has no meaning on the host running the app. The second problem is that any file manipulation must conform to the DB semantics for transactions, locking, rollback and recovery…

I figured there was something massively annoying with it. I'm hoping that PostgreSQL can try to tackle this use-case somehow because it's so damn common and 99% of the time the solution that is used completely throws out all the guarantees that the database gives you.

I'm not sure there's a non-annoying way to bridge the problem. The semantics are just so different that there's a lot of friction that needs to be handled somewhere. (What does O_DIRECT mean in this context? What happens when someone decides to store files that are never normally closed?)

In some ways, the problem mirrors OR mappers. A lot of common cases can be handled, but there are always situations where you're confronted with the fact that relational logic just doesn't map cleanly to OO (or hierarchic storage).

Post reply on HN