Database implementors have many choices for implementing data stores and any transactional semantics that they need.
Databases traditionally use very large files because their implementors have chosen to re-implement filesystem functionality at the low level for performance reasons.
Most often they use their own journalling implementations and fsync(). This is of course legitimate. But using filesystem-level rename to provide atomicity would also be perfectly reasonable.
The size of the renamed and replaced file is an implementation detail only. Rename doesn't impose a requirement to copy large hunks of data only to throw it away. The unit of replacement might be a btree node, for example.
Nothing forces an implementor to use large files for any particular purpose.