As recommended by this Firefox bug, exactly what does the action of cleaning a database accomplish? Is operation based on all modern database software, or only certain ones?

Databases, which use MVCC to isolate transactions from one another, have to periodically scan the tables to remove outdated copies of rows. In MVCC, whenever a row is up-to-date or erased, it can't be immediately recycled, because there can be active transactions that may still begin to see the old version from the row. Rather than checking if that's the situation, that could be rather pricey, old rows are assumed to remain relevant. The entire process of claiming back the area is deferred before the table is cleaned, which with respect to the database, could be started instantly, or clearly.

It's particularly mentioning to SQL lite vacuum command.

It's getting rid of space remaining from Remove claims.

It is extremely similiar to defragging a filesystem. More information around the PGSQL docs.