Hacker News Reader

Comment by _xnmw | Hacker News Reader

I wonder if 1.7 petabytes of data (1T indexed records) could fit on a single (very) beefy baremetal server for under a couple thousand dollars a month, served by SQLite.

Like this: https://use.expensify.com/blog/scaling-sqlite-to-4m-qps-on-a...

View on HN

1.7 petabytes on Sqlite?

Sqlite's own advice:

> If your data will grow to a size that you are uncomfortable or unable to fit into a single disk file, then you should select a solution other than SQLite. SQLite supports databases up to 281 terabytes in size, assuming you can find a disk drive and filesystem that will support 281-terabyte files.

> Even so, when the size of the content looks like it might creep into the terabyte range, it would be good to consider a centralized client/server database [over SQLite].

This is the worry IMO. It's fine to dump it on a server with SQLite, but once you start hitting scaling limits, you're in for a potentially rough migration.

Given 30.7TB SSD’s are about $5500 each and you’d need 56 to to get to 1.7PB (with no redundancy). Not to mention that SQLite’s maximum DB size is 140TB.

I don’t think you’d be able to fit this much storage into a single machine, especially not for a few thousand a month and SQLite wouldn’t be appropriate for this use-case.

If you install a RAID controller and a couple of disk boxes, it's possible with 1:1 replication, or with backups. 60 disk 3.5" units already exist, so 2.5" SSD racks. It won't be cheap, but will be resilient and fast. Bloody fast if you have the budget.

> or with backups take a while to restore a PB and a way to take a hot backup without impacting the service that by itself is a task or snapshots which is more disk

> 1:1 replication Depending on the amount of writes could be a ton of extra disk and a bucket for network cost

These systems support zero-downtime snapshots. You tell it to snapshot, it instantly snapshots, you can run a differential/incremental backup at great speeds. Your RAID controller is already caching the hot data, so the impact is minimal.

Except network cost there's no extra disk required. It's just broadcasted writes consumed on the other hand.

These boxes are not dumb JBODS. They support their own replication/backup subsystems, so everything is transparent.

Resilient and fast from a disk perspective, but in practice massively bottlenecked by the fact that Sqlite can only have 1 writer at a time.

At the moment they're just paying someone else to buy $5000 SSD's and run a database on them at many X markup.

There is no upper bounds to economy of scales. Maybe there is for the cents per GB of raw storage, but power usage, security, rent, and everything else scales too, and few of them have upper bounds on economy of scales.

Economies of scale generally have upper limits. Often when you approach the largest scale the existing market will supply you essentially need to become your own supplier which then runs into span of control issues. The organization needs to become competitive in that new market or their costs increase.

Keep scaling and eventually vertical integration ends up looking like a Soviet style planned economy. Your remote mining town needs some way for people to get soap etc so you open a store with it’s own supply chain etc etc.

Yes, there is an upper limit, and that is the entirety of global economy. But that assertion is as useless as it is true.

Hardly the global economy, even approaching a global monopoly for something tends to hit such limits. Few companies deal with this today because most markets are fragmented.

But even at the 30% marketshare the iPhone has been dealing with these issues for a while. They just can’t buy 200 million volume buttons or whatever off the shelf. Now imagine what happens if one of their suppliers would fail days before the phone launches. They have such tight integration not just with the manufacturer’s process but also their finances because Apple simply can’t get replacements at scale on short notice. And remember that’s at ~30% market share, it just gets worse after that.

Outsourcing doesn't mean off the shelf. Apple still works with third parties to manufacture parts as they can leverage the scale of their overall operations to do it cheaper than it would cost apple to do.

They aren’t just working with from a design or quality control standpoint that’s normal enough, they are also purchasing equipment for 3rd party companies to use which isn’t. At this point they need to be world class experts in everything from batteries, sensors, software, and processors to glass.

Tesla does some stuff in house because they can, but they just didn’t have any options when it came to scaling battery production. They hit the limits of what the market could supply without getting directly involved.

Why are these companies going so far as purchasing equipment for 3rd parties instead of doing it in house?

Several reasons, both from Apples perspective and the supplier’s.

Suppliers don’t want to purchase a bunch of equipment and scale their manufacturing capacity for a contract that could go away at any time. So when Apple shows up they are going to price that risk in to their bid or sometimes just outright say no. However, by Apple buying that equipment those risks suddenly get reduced.

Apple on the other hand has huge cash reserves and isn’t that price sensitive. Plus they want to avoid all the issues bring yet another component in house.

That is a long way of saying "economy of scale".

There are 61.44 TB NVMe drives (best price I've seen right now is ~6200. They were ~4800 earlier this year). You can have a 1U server with 32 E1.L slots so you should be able to fit ~1.9PB raw storage into 1U for a little over $200k. Don't know how business financing works, but at 8% interest with a 5 year amortization, that's a bit over $4k/month.

Do you have any good recommendation for such 1U server with 32 slots? Thanks

Supermicro https://www.supermicro.com/en/products/nvme?pro=formfactor%3...

Shows 2U in order to fit 32 drives, but hopefully you can find a 2U slot for this absolute monster of a storage box if you have that need.

This one is 1U and supports dual socket CPU and 6 TB RAM:

https://www.supermicro.com/en/products/system/1U/1029/SSG-10...

I've never done procurement so can't really speak to recommendations. I just see that the products apparently exist.

There's also 32 drive 1U JBOF enclosures for more expansion:

https://www.supermicro.com/en/products/system/1u/136/ssg-136...

Our ops team actually wanted to do this, but we on the project have nightmares from putting 1PB of database on a single host ><

Nimbus ExaDrive is 100TB [1].

[1] https://nimbusdata.com/products/exadrive/

StorageReview plays with 2PB flash machines all the time https://www.youtube.com/watch?v=UQMKtlIjeuk

1PB in a rack with spinning rust + flash buffer has been easy for years now.

The value proposition of commercial loud isn't cost savings unless you manage to quantify all of the ancillary and extrinsic factors such as security risk, HVAC, datacenter personnel, and hardware lifecycle. Any well capitalized and organized company could build their own cloud much more cheaply, but really a significant portion of the calculation is outsourcing the risk components.

The problem with outsourcing the risk components is that you don't know for sure whether they are properly taken care of. Major cloud providers have been caught "oopsing" your data, and bam it is gone. Furthermore, they have no incentive to be more efficient about it, they could easily be using 10x the amount of resources necessary, and you wouldn't even have a clue, you're just paying for evermore expensive crap that becomes less reliable over time.

But the cloud providers compete with other other! Look at the efficient market in display in their bandwidth pricing!

For very large customers, the cloud providers do compete with each other on cost. They often pay different prices than are advertised.

> Any well capitalized and organized company could build their own cloud much more cheaply

Lots of orgs fail to turn money into talent and then talent into products.

It just takes one bad hire at senior level and suddenly your cloud is a vmware install where all machines are boot off network disk, and contention makes the entire thing fall over.

Sometimes things just aren't nails, even when you have a really good hammer.

No it won't. sqlite "only" works with up to 281TB [0] [1]

[0] https://www.sqlite.org/releaselog/3_33_0.html

[1] https://www.sqlite.org/limits.html (#12)

You can split up into 10 SQLite DB's on this individual server.

You've now implemented sharding on top of SQLite.

Eventually all programs will be able to read email.

Any non-trivial complexity codebase eventually implements a mediocre SQL/Lisp/etc.

Exactly! Why take a system not designed for this sort of scale and force it to scale, rather than use systems which are designed and tested for this scale and volume? All you will do is hackily re-invent all the other things that the other databases had to do to scale to this extent.

Plus size is only one limit, you would be limited to 1 write every few milliseconds. My napkin maths estimate is that there are at least 1-2m writes per hour going into this thing, so probably 300-600 writes / second (Average) and maybe over 1k writes/second peak. We are going to fall over here!

Not sure why some people seem to have a viwe of "There is no scaling problem that can't be solved with a sufficient enough number of SQLite databases".

> You can split up into 10 SQLite DB's on this individual server.

1 is a scalable, managed, highly available service, with economies of scale the other is a fixed size, capital expenditure with fixed performance, limited DR, requiring a couple of SRE/DevOps and colo

There is also the will it always work question

Just storing petabytes of data is not the issue. Managing and querying it reliably is.

Beware of WAL mode, as you sacrifice ACID in this configuration.

https://sqlite.org/lang_attach.html

'Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not.'

Once you are splitting up 10 sqlite dbs you have a bespoke distributed system anyways, and you will find yourself doing all the headache of LedgerStore anyways.

Most of the novel work in LedgerStore is probably around managing the headaches of distributed storage, not the persistence layer.

One of the main reasons you put up with the annoyances of tuple-based storage like DynamoDB is because you want extremely high availability that simply cannot be provided by one computer in one physical location.

You wouldn't want to do 1T records on one server even if you could. At that scale, you would prefer to be somewhat distributed for availability and scalability. Also, SQLite has issues at large scale.

A reasonable number for one server is about 32-128 TB, and 1.7 petabytes with some redundancy fits nicely in ~30 servers with a decent distributed database.

Also a bit scary to have a system without a scaling mechnism built-in in the path of customer traffic. At some point you may be racing to upgrade it.

Sure but then you get a whole new set of costs and folks you have to hire to maintain that hardware.

Maybe it could and now you got 99 new Problems. That's why more experienced decision makers won't allow this to happen.

How would you replicate that SQLite DB onto other hosts to achieve redundancy?

One could use Litestream [1]

[1]: https://litestream.io

What if a continuous replication system has a bug one day, and you realize you are just a bit corrupted and have to rerun? Or is it the same with cloud tools?

That's why you always test your backup. I backup the full sqlite.db every day and test the litestream replication every week. So far litestream have been solid.

By the time the TB is restored, time to start the next test

How do you detect restored but bit flipped data ?

I do this in backup testing:

    sqlite3 /path/to/db
    sqlite> PRAGMA integrity_check;

See SQLite3 documentation: https://www.sqlite.org/pragma.html#pragma_integrity_check

Sounds like it will take awhile for TB and it checks db integrity not data integrity

Would you care to tell us what your backup and restore policy would be for 1.7 PB of data?

I'm replying to the question of how one would replicate SQLite 3 in production for redundancy. I myself consider 10GB would be the limit for using SQLite 3 in read/write in production and switch to PostgreSQL.

That's a huge discrepancy. One half of HN wants to put petabytes on SQLite, while your limit is only 10GB.

Why not use SQLite's own guidance on where SQLite probably isn't appropriate:

- Client/Server applications (Check)

- High-volumes (Check)

- Large datasets (Check)

- High concurrency, particularly for writes (Check)

https://www.sqlite.org/whentouse.html

There's only one person in this thread trying to put petabytes in SQLite. Everyone else is telling them the myriad reasons why it's a terrible idea.

Then the same happens as when there is a bug in Aurora's replication. You lose data. I know this from personal experience.

Any open source doing something similar ?

Litestream is open source.

https://github.com/benbjohnson/litestream

Ai. I remember another product and thought it was this. Sorry. Move on and keep up the good work.

it will take forever to create that index. Link describes 10B rows dataset.

Reply on news.ycombinator.com