danielwertheim

danielwertheim


notes from a passionate developer

Developer that lives by the mantra "code is meant to be shared".

Share


Tags


Disclaimer

This is a personal blog. The opinions expressed here represent my own and not those of my employer, nor current or previous. All content is published "as is", without warranty of any kind and I don't take any responsibility and can't be liable for any claims, damages or other liabilities that might be caused by the content.

CouchDB, Pagination - Is skip and limit enough?

Daniel WertheimDaniel Wertheim

It's a well known fact that you shall not use only skip and limit to achieve pagination in CouchDb. How-ever, since v1.2 of CouchDb, things has apparently become a bit better, but is skip and limit without the need of startkey good enough for some cases of pagination? Lets have a quick look.

The reason why I had a quick look at this was these resources: https://issues.apache.org/jira/browse/COUCHDB-1076 where comments states:

As far as I'm aware, skip is equivalently fast to a startkey search because whole sub trees are skipped when their document count does not exceed the remaining skip

And the documentation (currently for v1.6) talks about this pre and post v1.2 behavior and that not all cases needs the old construct any more. Hence gone is the "Warning! Do not use this construct for pagination" when it comes to only using skip and limit.

Conclusion

Paging is not for GUI only. Paging is also for machines. The case could be simple batch processing, where you need to locate a certain range to process incrementally. But when you are doing a GUI, what are the chances of a user sitting and clicking next to a big range of documents? And will your scenario even have this amount of posts? And what is big? As always, it boils down to: "evaluate for your scenario". For me the conclusion is:

simple will be good enough for POCs and when small amount of data in GUI pagination.

The test

I imported 1.700.543 documents, into a v1.5 CouchDb instance running on a Windows 8.1, i7 quad-core powered machine with SSD and 8GB RAM.

Views

There are two secondary-indices (views) one returning the full document and one a slim projection. Both has an ordered integer as key and the document as value. Look at the end of this post for a sample document.

Results

The results where measured by a simple release compiled .Net4.5 C# console application using MyCouch v.0.23.0. Each view was touched once as warmup behavior.

View1 – average (ms) of five queries

offsetskip + limit (ms)skip + limit + startkey (ms)
500,00012727.303.78
250,0006387.773.59
50,0001282.253.49
5,000130.153.67
50015.853.45
504.523.93

View2 – average (ms) of five queries

offsetskip + limit (ms)skip + limit + startkey (ms)
500,0002401.102.33
250,0001208.682.26
50,000239.122.06
5,00028.122.62
5004.662.06
502.332.10

Sample document

{
    "_id": "500001",
    "_rev": "1-3913a16a3d816da7e2ead69319ebaa7c",
    "StartTime": "2014-03-28T10:00:00+01:00",
    "StopTime": "2014-03-28T11:00:00+01:00",
    "Weather": {
        "Temperature": {
            "Value": 8.58,
            "Unit": "°C"
        },
        "Humidity": {
            "Value": 51.12,
            "Unit": "%"
        },
        "SolarInsolation": {
            "Value": 463.9,
            "Unit": "W/m2"
        },
        "AirPressure": {
            "Value": 1026.23,
            "Unit": "hPa"
        },
        "WindSpeed": {
            "Value": 3.79,
            "Unit": "m/s"
        },
        "WindDirection": {
            "Value": 43.13,
            "Unit": "°"
        },
        "RainFall": {
            "Unit": "mm"
        }
    },
    "AirQuality": {
        "TotalIndex": 0.48,
        "NO2": {
            "Value": 19.13,
            "Unit": "µg/m3",
            "Index": 0.48
        },
        "SO2": {
            "Value": 1.07,
            "Unit": "µg/m3",
            "Index": 0.02
        },
        "PM10": {
            "Value": 16.05,
            "Unit": "µg/m3",
            "Index": 0.32
        },
        "CO": {
            "Value": 109.36,
            "Unit": "µg/m3",
            "Index": 0.05
        },
        "NOx": {
            "Value": 33.16,
            "Unit": "µg/m3",
            "Index": 0.11
        },
        "PM2_5": {
            "Value": 8.09,
            "Unit": "ug/m3",
            "Index": 0.32
        }
    },
    "temp": 500001
}

Developer that lives by the mantra "code is meant to be shared".

Comments