I have a materialized VIEW in PostgreSQL/PostGIS that contains observations for thousands of species. I am currently serving this view as a single WMS/WFS layer using Geoserver, and this works fine for our web applications. We just use a CQL filter in the application to limit to the species selected by the user. But, I am not sure of the best way to get the data to end users in a GIS client (ArcMap mostly, but also QGIS), who will almost always want to view observations for a single species at a time. Although SQL filters can be applied to a WFS layer in QGIS, I am not sure about ArcMap. Also, it might be too much to ask to suggest that end users form and apply these queries in their GIS client, since most end users will be biologists, rather than GIS or database specialists. The options I can think of that might be viable are:
1) Serve the data as-is, and ask end users to filter by species on their end.
2) Programmatically create a separate layer for each species, possibly as layer groups, in Geoserver, grouped by major taxonomic groupings (e.g., Birds, Mammals, etc.). This is problematic because the data change on a daily basis, so views/layers would have to be regenerated frequently.
Are there other options I might be missing?