I am sorry if the title might sound silly, but I will try to explain the problem and why that title seemed the right to me.
I have an OpenLayers application which display different WMS Image Layers and make query against the relative WFS services coming from GeoServer.
These layers are published from different PostgreSQL views. All the views are generated against a join with a main attribute table and a main geographic table through common feature ids.
The main attribute table is getting big (right now it is 2.5 millions rows), and the response of the application in serving the WMS and other operations like using cql_filter is getting slower.
I cannot create a tiled WMS as my data change frequently and data is served with a filter on dates, so that I would have to create tiles for each single day I have in the views.
Also, I took care of serving the layers with the same coordinate system as my data not to slow down the process due to reprojection.
I tried to create SQL Views in GeoServer to further decrease the size of each view in order to see if this would have improved performance, but I still have slow responses, and I guess (I might be wrong though), that this is due to the data eventually coming from the same big main table under the hood.
So, my idea was to split the main big attribute table into smaller tables, and create my views against these.
Would this be a valuable path or do you have better suggestions?
I can provide more information if needed.