I think some pages still use them for running background stuff. My browser is setup to clear all of them upon closing the tab.
This whole direction is being silently discontinued anyway. Running browser apps has become harder, not easier.
So, there is some piece of infrastructure for this future here and there. Service Workers is one of those pieces. But the apps only achieved some success in closed markets (extension stores). It never became a standard (visit a page, pin it, becomes a fully fledged app).
Instead, the web moved to mobile and desktop apps through other means (super-Cordoba/Electron-like apps, little JS/HTML insertions in traditional apps, other inventive web ways that do not involve a collaborative standard).
The leftovers of this imagined distribution mechanism are being pushed aside (hidden in weird menus or options). Tech is still there because it is a standard, but the counterpoint UI and market decisions are pointing in other directions.
For example, both in Chrome and Firefox, the ability to invoke the browser "chromeless" that was a part of this whole thing has been removed or muted in some way. It was never a standard, so it was removed as soon as possible (probably few people working on it).
Does that make sense?