4. Partitioned Databases

A partitioned database forms documents into logical partitions by using a partition key. All documents are assigned to a partition, and many documents are typically given the same partition key. The benefit of partitioned databases is that secondary indices can be significantly more efficient when locating matching documents since their entries are contained within their partition. This means a given secondary index read will only scan a single partition range instead of having to read from a copy of every shard.

As a means to introducing partitioned databases, we’ll consider a motivating use case to describe the benefits of this feature. For this example, we’ll consider a database that stores readings from a large network of soil moisture sensors.

Traditionally, a document in this database may have something like the following structure:

  1. {
  2.     "_id": "sensor-reading-ca33c748-2d2c-4ed1-8abf-1bca4d9d03cf",
  3.     "_rev":"1-14e8f3262b42498dbd5c672c9d461ff0",
  4.     "sensor_id": "sensor-260",
  5.     "location": [41.6171031, -93.7705674],
  6.     "field_name": "Bob's Corn Field #5",
  7.     "readings": [
  8.         ["2019-01-21T00:00:00", 0.15],
  9.         ["2019-01-21T06:00:00", 0.14],
  10.         ["2019-01-21T12:00:00", 0.16],
  11.         ["2019-01-21T18:00:00", 0.11]
  12.     ]
  13. }

So we’ve got a bunch of sensors, all grouped by the field they monitor along with their readouts for a given day (or other appropriate time period).

Along with our documents, we might expect to have two secondary indexes for querying our database that might look something like:

  1. function(doc) {
  2.     if(doc._id.indexOf("sensor-reading-") != 0) {
  3.         return;
  4.     }
  5.     for(var r in doc.readings) {
  6.         emit([doc.sensor_id, r[0]], r[1])
  7.     }
  8. }

and:

  1. function(doc) {
  2.     if(doc._id.indexOf("sensor-reading-") != 0) {
  3.         return;
  4.     }
  5.     emit(doc.field_name, doc.sensor_id)
  6. }

With these two indexes defined, we can easily find all readings for a given sensor, or list all sensors in a given field.

Unfortunately, in CouchDB, when we read from either of these indexes, it requires finding a copy of every shard and asking for any documents related to the particular sensor or field. This means that as our database scales up the number of shards, every index request must perform more work, which is unnecessary since we are only interested in a small number of documents. Fortunately for you, dear reader, partitioned databases were created to solve this precise problem.

4.1. What is a partition?

In the previous section, we introduced a hypothetical database that contains sensor readings from an IoT field monitoring service. In this particular use case, it’s quite logical to group all documents by their sensor_id field. In this case, we would call the sensor_id the partition key.

A good partition has two basic properties. First, it should have a high cardinality. That is, a large partitioned database should have many more partitions than documents in any single partition. A database that has a single partition would be an anti-pattern for this feature. Secondly, the amount of data per partition should be “small”. The general recommendation is to limit individual partitions to less than ten gigabytes (10 GB) of data. Which, for the example of sensor documents, equates to roughly 60,000 years of data.

4.2. Why use partitions?

The primary benefit of using partitioned databases is for the performance of partitioned queries. Large databases with lots of documents often have a similar pattern where there are groups of related documents that are queried together.

By using partitions, we can execute queries against these individual groups of documents more efficiently by placing the entire group within a specific shard on disk. Thus, the view engine only has to consult one copy of the given shard range when executing a query instead of executing the query across all q shards in the database. This mean that you do not have to wait for all q shards to respond, which is both efficient and faster.

4.3. Partitions By Example

To create a partitioned database, we simply need to pass a query string parameter:

  1. shell> curl -X PUT http://127.0.0.1:5984/my_new_db?partitioned=true
  2. {"ok":true}

To see that our database is partitioned, we can look at the database information:

  1. shell> curl http://127.0.0.1:5984/my_new_db
  2. {
  3.   "cluster": {
  4.     "n": 3,
  5.     "q": 8,
  6.     "r": 2,
  7.     "w": 2
  8.   },
  9.   "compact_running": false,
  10.   "db_name": "my_new_db",
  11.   "disk_format_version": 7,
  12.   "doc_count": 0,
  13.   "doc_del_count": 0,
  14.   "instance_start_time": "0",
  15.   "props": {
  16.     "partitioned": true
  17.   },
  18.   "purge_seq": "0-g1AAAAFDeJzLYWBg4M...",
  19.   "sizes": {
  20.     "active": 0,
  21.     "external": 0,
  22.     "file": 66784
  23.   },
  24.   "update_seq": "0-g1AAAAFDeJzLYWBg4M..."
  25. }

You’ll now see that the "props" member contains "partitioned": true.

Now that we’ve created a partitioned database, it’s time to add some documents. Using our earlier example, we could do this as such:

  1. shell> cat doc.json
  2. {
  3.     "_id": "sensor-260:sensor-reading-ca33c748-2d2c-4ed1-8abf-1bca4d9d03cf",
  4.     "sensor_id": "sensor-260",
  5.     "location": [41.6171031, -93.7705674],
  6.     "field_name": "Bob's Corn Field #5",
  7.     "readings": [
  8.         ["2019-01-21T00:00:00", 0.15],
  9.         ["2019-01-21T06:00:00", 0.14],
  10.         ["2019-01-21T12:00:00", 0.16],
  11.         ["2019-01-21T18:00:00", 0.11]
  12.     ]
  13. }
  14. shell> $ curl -X POST -H "Content-Type: application/json" \
  15.             http://127.0.0.1:5984/my_new_db -d @doc.json
  16. {
  17.     "ok": true,
  18.     "id": "sensor-260:sensor-reading-ca33c748-2d2c-4ed1-8abf-1bca4d9d03cf",
  19.     "rev": "1-05ed6f7abf84250e213fcb847387f6f5"
  20. }

The only change required to the first example document is that we are now including the partition name in the document id by prepending it to the old id separated by a colon.

Working with documents in a partitioned database is no different than a non-partitioned database. All APIs are available, and existing client code will all work seamlessly.

Now that we have created a document, we can get some info about the partition containing the document:

  1. shell> curl http://127.0.0.1:5984/my_new_db/_partition/sensor-260
  2. {
  3.   "db_name": "my_new_db",
  4.   "doc_count": 1,
  5.   "doc_del_count": 0,
  6.   "partition": "sensor-260",
  7.   "sizes": {
  8.     "active": 244,
  9.     "external": 347
  10.   }
  11. }

And we can also list all documents in a partition:

  1. shell> curl http://127.0.0.1:5984/my_new_db/_partition/sensor-260/_all_docs
  2. {"total_rows": 1, "offset": 0, "rows":[
  3.     {
  4.         "id":"sensor-260:sensor-reading-ca33c748-2d2c-4ed1-8abf-1bca4d9d03cf",
  5.         "key":"sensor-260:sensor-reading-ca33c748-2d2c-4ed1-8abf-1bca4d9d03cf",
  6.         "value": {"rev": "1-05ed6f7abf84250e213fcb847387f6f5"}
  7.     }
  8. ]}

Note that we can use all of the normal bells and whistles available to _all_docs requests. Accessing _all_docs through the /dbname/_partition/name/_all_docs endpoint is mostly a convenience so that requests are guaranteed to be scoped to a given partition. Users are free to use the normal /dbname/_all_docs to read documents from multiple partitions. Both query styles have the same performance.

Next, we’ll create a design document containing our index for getting all readings from a given sensor. The map function is similar to our earlier example except we’ve accounted for the change in the document id.

  1. function(doc) {
  2.     if(doc._id.indexOf(":sensor-reading-") < 0) {
  3.         return;
  4.     }
  5.     for(var r in doc.readings) {
  6.         emit([doc.sensor_id, r[0]], r[1])
  7.     }
  8. }

After uploading our design document, we can try out a partitioned query:

  1. shell> cat ddoc.json
  2. {
  3.     "_id": "_design/sensor-readings",
  4.     "views": {
  5.         "by_sensor": {
  6.             "map": "function(doc) { ... }"
  7.         }
  8.     }
  9. }
  10. shell> $ curl -X POST -H "Content-Type: application/json" http://127.0.0.1:5984/my_new_db -d @ddoc2.json
  11. {
  12.     "ok": true,
  13.     "id": "_design/all_sensors",
  14.     "rev": "1-4a8188d80fab277fccf57bdd7154dec1"
  15. }
  16. shell> curl http://127.0.0.1:5984/my_new_db/_partition/sensor-260/_design/sensor-readings/_view/by_sensor
  17. {"total_rows":4,"offset":0,"rows":[
  18. {"id":"sensor-260:sensor-reading-ca33c748-2d2c-4ed1-8abf-1bca4d9d03cf","key":["sensor-260","0"],"value":null},
  19. {"id":"sensor-260:sensor-reading-ca33c748-2d2c-4ed1-8abf-1bca4d9d03cf","key":["sensor-260","1"],"value":null},
  20. {"id":"sensor-260:sensor-reading-ca33c748-2d2c-4ed1-8abf-1bca4d9d03cf","key":["sensor-260","2"],"value":null},
  21. {"id":"sensor-260:sensor-reading-ca33c748-2d2c-4ed1-8abf-1bca4d9d03cf","key":["sensor-260","3"],"value":null}
  22. ]}

Hooray! Our first partitioned query. For experienced users, that may not be the most exciting development, given that the only things that have changed are a slight tweak to the document id, and accessing views with a slightly different path. However, for anyone who likes performance improvements, it’s actually a big deal. By knowing that the view results are all located within the provided partition name, our partitioned queries now perform nearly as fast as document lookups!

The last thing we’ll look at is how to query data across multiple partitions. For that, we’ll implement the example sensors by field query from our initial example. The map function will use the same update to account for the new document id format, but is otherwise identical to the previous version:

  1. function(doc) {
  2.     if(doc._id.indexOf(":sensor-reading-") < 0) {
  3.         return;
  4.     }
  5.     emit(doc.field_name, doc.sensor_id)
  6. }

Next, we’ll create a new design doc with this function. Be sure to notice that the "options" member contains "partitioned": false.

  1. shell> cat ddoc2.json
  2. {
  3.   "_id": "_design/all_sensors",
  4.   "options": {
  5.     "partitioned": false
  6.   },
  7.   "views": {
  8.     "by_field": {
  9.       "map": "function(doc) { ... }"
  10.     }
  11.   }
  12. }
  13. shell> $ curl -X POST -H "Content-Type: application/json" http://127.0.0.1:5984/my_new_db -d @ddoc2.json
  14. {
  15.     "ok": true,
  16.     "id": "_design/all_sensors",
  17.     "rev": "1-4a8188d80fab277fccf57bdd7154dec1"
  18. }

And to see a request showing us all sensors in a field, we would use a request like:

  1. shell> curl -u adm:pass http://127.0.0.1:15984/my_new_db/_design/all_sensors/_view/by_field
  2. {"total_rows":1,"offset":0,"rows":[
  3. {"id":"sensor-260:sensor-reading-ca33c748-2d2c-4ed1-8abf-1bca4d9d03cf","key":"Bob's Corn Field #5","value":"sensor-260"}
  4. ]}

Notice that we’re not using the /dbname/_partition/... path for global queries. This is because global queries, by definition, do not cover individual partitions. Other than having the "partitioned": false parameter in the design document, global design documents and queries are identical in behavior to design documents on non-partitioned databases.