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.
- map for “View1”:
emit(doc.temp, doc); - map for “View2”:
emit(doc.temp, { startTime: doc.StartTime, stopTime: doc.StopTime, value: doc.Value, unit: doc.Unit });
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
| offset | skip + limit (ms) | skip + limit + startkey (ms) |
| 500,000 | 12727.30 | 3.78 |
| 250,000 | 6387.77 | 3.59 |
| 50,000 | 1282.25 | 3.49 |
| 5,000 | 130.15 | 3.67 |
| 500 | 15.85 | 3.45 |
| 50 | 4.52 | 3.93 |
View2 – average (ms) of five queries
| offset | skip + limit (ms) | skip + limit + startkey (ms) |
| 500,000 | 2401.10 | 2.33 |
| 250,000 | 1208.68 | 2.26 |
| 50,000 | 239.12 | 2.06 |
| 5,000 | 28.12 | 2.62 |
| 500 | 4.66 | 2.06 |
| 50 | 2.33 | 2.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
}