I am not able to understand the concept of window functions. How exactly they work and what are the pros and cons of such technique?
I have read that using limit and offset is slow but its still used. So decision between limit/offset and window functions depend upon size of data to get from the db or window functions should always be used?
What is ideal window size to get maximum performance from this? Can it be the case where if number of rows to get is less then simply use limit/offset?
My use case basically requires sorting data on date and then bring the latest data first. How window functions fit in this scenario.