Do people create pools with both SSD and spinning storage together? How does that work, if it does?
Occasionally. More frequently, it’s mixing types of SSD (eg m.2 SATA with m.2 NVMe, or m.2 NVMe with regular SATA/SAS/SCSI/whatever).
Essentially, your vdevs and pools with mixed media types will perform for the most part as though they were composed entirely of the slowest member of the vdev or pool, for any given kind of “being slow.” They’ll be a little faster than that, but not a whole lot, because you’ll need to read or write from/to that slowest member on the majority of operations performed on any multiple-disk pool.
Can the pool be configured to use the SSD as a massive cache?
Yes and no. L2ARC is a thing, and so is LOG–the former accelerates reads, and the former accelerates sync writes. But if you don’t have much in the way of sync writes–and most workloads don’t–a LOG won’t do you any good, and L2ARC is a lot less useful to most workloads than most people tend to assume it will be.
You’re usually better off in a small and extremely constrained system using your single SSD for something entirely separate: either a separate ZFS pool that’s SSD-only for your highest-IOPS needs, or a separate filesystem entirely for the same reasons (eg a boot drive that’s simple ext4 on an SSD, with the pool being on the rust).
Would it be difficult to add more spinning storage?
Not if you’re running mirrors. Say you set up a pool on a single HDD, which we’ll call disk0. Then, you want to add three more HDDs, disk1 through disk3. Well, your single-drive pool has no redundancy, so first off you fix that:
zpool attach mypool disk0 disk1
Now you’ve got a pool with two disks, in a single mirror vdev. This gives you redundancy so you can survive single disk failures, recover automatically from any on-disk corruption events, and roughly doubles your read IOPS (with sufficient parallelism in your workload).
Next, add a second mirror vdev with
zpool add mypool mirror disk1 disk2
Now you have a pool with two mirror vdevs. Each is redundant, each offers roughly double the read IOPS of a single drive. This gives you a pool with roughly quadruple the read IOPS (and throughput) and roughly double the write IOPS (and throughput) of a single drive of the same class.