We're in the middle of a project in which we use file_fdw to provide an interface to CSV files which are replaced nightly, but we then also have indexed materialized views to provide faster access to the data in those files. The CSVs are only read once per day when REFRESHing the materialized views after a new set of files is dropped in.
One of the nice things file_fdw is that it allows you to create foreign tables even when the source files don't exist on the filesystem. PG only cares about the files at the moment it's trying to read them. There is one downside, though, which is that the files have to be specified via absolute path. This is something that has to be considered given the heterogeneous machine environments on which the application runs.