Post 1540277 by bobstechsite@bobadon.rocks
(DIR) More posts by bobstechsite@bobadon.rocks
(DIR) Post #1540276 by codesections@fosstodon.org
2018-11-27T21:01:55Z
0 likes, 0 repeats
I might replace a SQL database with ~100 million rows with a collection of flat files. Tell me why I'm crazy.Here's the scenario: The DB contains info that is only ever queried by one key. I'm considering saving each group of data to a single JSON file (~10 million files) with the key as its filename. After some local testing, I believe nginx could serve any of the static files at least an order of magnitude faster than I could query the DB (even with the right indexes).Why is this crazy?
(DIR) Post #1540277 by bobstechsite@bobadon.rocks
2018-11-27T21:11:40Z
0 likes, 0 repeats
@codesections reading a file from a hard drive and searching for an individual entry is a lot slower than searching an index held in memory.If you're set on switching to JSON you'd probably want to look into NoSQL databases like MongoDB or Couchbase. Sqlite is also flatfile but won't scale particularly well.
(DIR) Post #1540278 by codesections@fosstodon.org
2018-11-27T21:18:06Z
0 likes, 0 repeats
@bobstechsite I think I didn't explain myself that well. I'm not intent on switching to JSON—I'm agnostic about the storage format at rest but, regardless of the format, I'll be serializing the data as JSON to send to the client.I also won't be searching the file for anything, at least for a read operation. Each file would represent a single HTTP response body, so I'd just be serving the file directly as a static file.
(DIR) Post #1540279 by rick_777@cybre.space
2018-11-27T21:40:16Z
0 likes, 0 repeats
@codesectionsSo basically you're serving text files?The question is why were you storing them in a database in the first place! :blob_gnikniht: @bobstechsite
(DIR) Post #1540280 by feld@bikeshed.party
2018-11-27T21:43:09.856117Z
0 likes, 0 repeats
@rick_777 @bobstechsite @codesections i worked for a company that just loved paying Oracle gobs of money and they had a crazy web app that generated the entire webpage from PL/SQL code dynamically and even the static assets were stored in the database as BLOBs. It was insane.Some people just like doing things their way :)
(DIR) Post #1540508 by bobstechsite@bobadon.rocks
2018-11-27T21:53:26Z
0 likes, 0 repeats
@feld @rick_777 @codesections that sounds a bit like they might've been using Apex.(Fair disclosure: I work for Oracle, not an official spokesperson, etc.)
(DIR) Post #1540509 by feld@bikeshed.party
2018-11-27T21:54:36.477678Z
0 likes, 0 repeats
@bobstechsite @codesections @rick_777 I don't know what Apex is, but I can tell you that in 2008 we had the world's largest PL/SQL codebase and Oracle was doing a case study on usI recall something being 20 million lines of PL/SQL, but not sure if it was just that one app or everything the company had written
(DIR) Post #1540887 by bobstechsite@bobadon.rocks
2018-11-27T22:04:45Z
0 likes, 0 repeats
@feld @rick_777 @codesections 10 years on it behaves a bit differently, but Oracle Apex is basically a system for creating web front-ends for Oracle databases without having to do much coding.A lot of the design and logic is configured through a browser-based WYSIWYG. The only code you're really asked for are PL/SQL queries.(I've put this behind a CW so I'm not unwittingly advertising work stuff on a personal profile)
(DIR) Post #1540888 by feld@bikeshed.party
2018-11-27T22:06:48.388189Z
0 likes, 0 repeats
@bobstechsite @codesections @rick_777 Nope, this was a full blown CMS that we also used to run ecommerce and polling (as in public polling) sites as well.The codebase now lives somewhere in Gallup.
(DIR) Post #1806402 by audiodude@tiny.tilde.website
2018-11-27T22:38:22Z
0 likes, 0 repeats
@codesections Isn't this worse than just putting them in some non-relational database, say MongoDB or something, that will do the indexing and lookups and provide some modicum of durability?
(DIR) Post #1806403 by codesections@fosstodon.org
2018-11-27T22:42:06Z
0 likes, 0 repeats
@audiodude Worse in what respect? It's worse in terms of ability to manipulate the data or to scale horizontally. But it's *much* better in terms of speed of responding to simple GET requests for the resource. Turns out nginx is crazy fast at serving static files—far faster than even a well-optimized database can generate response. (Of course, databases do quite a bit more, but it's not free in terms of execution time)
(DIR) Post #1806404 by clacke@libranet.de
2018-12-07T12:14:52Z
0 likes, 1 repeats
If you can make the data immutable, static files have fantastic horizontal scalability.I have been considering a social network server that is mostly a static website, with only the latest data being stored in some form of database. Periodically you'd redeploy it with more static data replacing the dynamic data.