Especially analytic databases that try to optimize for queries that scan large portions of tables instead of randomly accessing specific rows. For example Redshift has a concept of a sort key that, given you VACUUM after major inserts/updates/deletes, ensures the data is sorted on disk according to the sort key. Ideally this minimizes disk seeks and optimizes large scans.
However, my understanding is databases write their data blocks to a set of database files. And these files are managed by the file system which has it's own block scheme that is subject to fragmentation. So how does the database manage this (or maybe more precisely how does something like VACUUM manage this) so that database blocks are sorted correctly on disk?
I'm guessing it's to do with the fact that most databases run on servers where not much else is running? Or the database is usually given its own disk partitions? Or the database files reserve a bunch of space ahead of time? In either of these cases there aren't too many processes writing to the same disk space as the database so there isn't much fragmentation outside of the database's control?