46 ms·

I think SQLite + WAL could be a compelling solution for game patching scenario. There are already SQLite clustering technologies that utilize WAL frame shippin

I think SQLite + WAL could be a compelling solution for game patching scenario. There are already SQLite clustering technologies that utilize WAL frame shipping in order to replicate data. I don't see why a steam patch couldn't just be a range of WAL frames to be applied to a client's database.

ilyt 4y ago
I think that train already sailed. With HDD or SDD that might've been feasible but with NVMe you really want as little between raw storage and game engine.
- mastax 4y ago
  Yeah, it would probably be easier to move back to individual files from packfiles. Assuming that the main motivation for packfiles was layout for HDDs and not filesystem overhead.
  - ilyt 4y ago
    Initially packfiles were absolutely about filesystem overhead, "a lot of small files" were terrible performance-wise. OSes got better tho, don't know in windows side, but on linux side "few tens of thousands of small files" case really isn't that bad But I'm really talking about ability to just directly load an asse via reading a blob of data vs having to munge it thru SQLite code. Or even just mmap'ing data directly
    - account42 4y ago
      Windows is still extremely painful with many small files (at last the Win32 API, maybe going directly via Nt* functions is slightly better) and it only gets worse once you add "Antivirus" Malware that wants to hook into all FS access.
mikepurvis 4y ago
Yes, but I think this workflow is dependent on an assumption that there’s no cost overhead for random accesses, which only became true in the current console generation, with mandatory fast SSDs. As long as you were potentially seeking around a spinning platter, you needed to have way more control over data locality than was possible with something like SQLite.
justinclift 4y ago
> There are already SQLite clustering technologies that utilize WAL frame shipping in order to replicate data. Would you be ok to point out ones that do this? Searching online isn't showing up anything mentioning WAL frame shipping for SQLite. :( Was thinking about exactly this scenario a few days ago, as I have a potential use for it. :)
- justinclift 4y ago
  Ahhh, at least Litestream seems to do this: https://litestream.io/how-it-works/ Awesome. :)
gcr 4y ago
interesting, is this a supported SQLite feature? I understand that when the WAL file gets too big (e.g. 4mb), SQLite will then checkpoint its contents back to the main sqlite database. How can one generate a WAL file big enough to contain a game patch?

ilyt 4y ago

I think that train already sailed. With HDD or SDD that might've been feasible but with NVMe you really want as little between raw storage and game engine.

mastax 4y ago
Yeah, it would probably be easier to move back to individual files from packfiles. Assuming that the main motivation for packfiles was layout for HDDs and not filesystem overhead.
- ilyt 4y ago
  Initially packfiles were absolutely about filesystem overhead, "a lot of small files" were terrible performance-wise. OSes got better tho, don't know in windows side, but on linux side "few tens of thousands of small files" case really isn't that bad But I'm really talking about ability to just directly load an asse via reading a blob of data vs having to munge it thru SQLite code. Or even just mmap'ing data directly
  - account42 4y ago
    Windows is still extremely painful with many small files (at last the Win32 API, maybe going directly via Nt* functions is slightly better) and it only gets worse once you add "Antivirus" Malware that wants to hook into all FS access.

mikepurvis 4y ago

Yes, but I think this workflow is dependent on an assumption that there’s no cost overhead for random accesses, which only became true in the current console generation, with mandatory fast SSDs. As long as you were potentially seeking around a spinning platter, you needed to have way more control over data locality than was possible with something like SQLite.

justinclift 4y ago

> There are already SQLite clustering technologies that utilize WAL frame shipping in order to replicate data. Would you be ok to point out ones that do this? Searching online isn't showing up anything mentioning WAL frame shipping for SQLite. :( Was thinking about exactly this scenario a few days ago, as I have a potential use for it. :)

justinclift 4y ago
Ahhh, at least Litestream seems to do this: https://litestream.io/how-it-works/ Awesome. :)

gcr 4y ago

interesting, is this a supported SQLite feature? I understand that when the WAL file gets too big (e.g. 4mb), SQLite will then checkpoint its contents back to the main sqlite database. How can one generate a WAL file big enough to contain a game patch?