We started to notice some speed issues yesterday with certain queries in one of the largest tables (~50 million rows) in our PostgresSQL database, and we wanted to share some of the results of the investigation.

During our tests today, we noticed that the slow queries ordered by id DESC were running in about 5 seconds, and queries that were ordered by created_at DESC were running in 0.1 milliseconds. Multicolumn indexes were present for both queries to use, and EXPLAIN ANALYZE showed that the queries hitting those indexes with an index scan.

This led to the obvious question: wtf?

After some investigation, we found that the order of columns in the index were reversed. One had the columns defined as (feed_id, created_at DESC) and the other had the columns defined as (id DESC, feed_id). After looking at the PostgresSQL docs for multicolumn indexes, the issue was clear.

It turns out that you need to define your indexes with a constraint (feed_id) first, so that you are only scanning a portion of the index. By defining id first, the full index was scanned, which resulted in very slow queries. We switched the order of the columns in the index to (feed_id, id DESC) and the queries are now running in the ~0.1 ms range, so you should notice that things are now much more responsive.

Please check out the relevant docs in the PostgresSQL 9.5 manual if you would like more info about multicolumn indexes.

We are considering a change to the way IO handles the retain flag on MQTT publish packets. The retain flag is used to allow the MQTT broker to retain the sent value, and will tell the server to pass it to any new subscribers as soon as they connect. We found that this feature was a little hard for people to understand at first, so we decided to force the retain flag to true for users by default. This change ensured that users always got the current state of their IO feeds as soon as they connected, without having to send a HTTP request to the REST API for the current value.

The decision to force the retention of values caused issues with some users, because they lost the option to decide which values were cached. We are planning to revert this change, and give people the option to cache the values using the retain flag.

In addition to this change, we are thinking of extending the meaning of the retain flag, and also allowing users to decide which values are logged to the database. So for example, if you wanted to push out temperature values every second to your connected devices, but only wanted to log the temperature value every minute, you could do so by only setting the retain flag to true for the values you wish to log. This should allow for greater flexibility when logging, and will result in increased speed for feeds that are focused on realtime interaction. We welcome your feedback in the IO forum.

If you would like to read more about the MQTT retain flag, please check out this article on HiveMQ’s blog.

Here are the stats for the past week:

* 29.03 million inserts of logged data in the last 7 days
* 10,969 users
* 8,675 online feeds (24,277 feeds total)

On Thursday, April 21st we will be deploying Version 2 of our API. This version is quite different from our existing API V1.

IMPORTANT: Your code or sketches will need to be updated if you are using the REST API.

From now until Thursday, you will want to update your code or sketches to change ‘/api/…’ to ‘/api/v1/’ so that you continue using v1 of the API until you are ready to upgrade to the latest version.

Here are some of the highlights (not including bug fixes):

NEW

• New permission system that will allow for sharing read and write access to your feeds, groups, & dashboards with other users.
• New AWS inspired HTTP request signing to help avoid exposing the user’s AIO key over insecure connections.
• Dashboards, blocks, & triggers will be able to be modified via the REST API.

CHANGES

• Username will be a required component of the URL.
• You will no longer be able to access feeds & groups via numeric ID. Feed key & group keys will be used as the unique identifier in API v2.
• Feeds will be able to be added to many groups, and the feed’s data will be namespaced to the group the data is pushed to. You will also be able to access all of the feed’s data by accessing the feed directly.

We’re temporarily disabling the email triggers until we can make them more robust in a near future update. In the meantime, the alternative would be to use our IFTTT channel, which can do pretty much the same thing as our internal trigger emails.

We want to build a much more robust system. As is, there are some weaknesses in our current design that can trigger emails to be sent far too often, or without a way for them to reset after the trigger threshold has been hit.

If you have any suggestions on what you’d like to have implemented with this feature, please let us know at the our IO forum.

This week we will be deploying two major changes to IO. We will be deploying v2 of the REST API, and we will be moving our MQTT services from mosca to aedes. This should allow us to squeeze even more performance out of our MQTT workers, and will also allow us to support MQTT QoS 2.

Here are the stats for the past week:

* 28.69 million inserts of logged data in the last 7 days
* 10,512 users
* 8,077 online feeds (22,695 feeds total)