Thinkpad Carbon X1 1st-3d Gen and Ubuntu 18.04

My dear friend Dirk just bought a second-hand Carbon X1 first generation, just like mine (3448 serie). I thought he might need a quick help to get going with Ubuntu 18.04 and this machine. Note that the content of this tutorial works for the 2nd and 3rd generation (20BS serie) as well.

I had 3 complains after installing a brand new Ubuntu 18.04 LTS on an encrypted disk:

  • no hibernate feature
  • no fingerprint scanner feature
  • a too sensitive trackpad

Hopefully all these problems can be solved to provide the OS this laptop deserves.


This chapter is the trickiest part of this tutorial, therefore it comes first. Keep in mind that we are going to change partitions sizes, it’s best to do it right after linux have been installed: when all you can loose is your time, not your data. If your laptop has a running system with sensitive data: please do a backup first.

In order to enable the hibernation, we’ll need to fix the swap partition size, so the RAM content can fit in it when the machine goes to deep sleep. Then we’ll need to setup systemd so it suspends when the lid is closed, and hibernate after a (defined) while if not resumed in the meantime. And to close this chapter, we’ll setup some policies to enable the “hibernate” button in the system menu.


If you didn’t partition the SSD yourself during the installation, high are the chance that your swap partition is smaller than the amount of RAM in your machine. And if you succeeded to install Ubuntu 18.04 with a custom partitioning and and encrypted file-system, than please leave a note in the comment to explain how. I tried many different approach without success: whether the install failed or the machine didn’t boot properly. So let’s start with the assumption that you installed Ubuntu with an encrypted file-system, by letting the installer partition your disk.

You’ll need to boot on the installation disk/usb-drive again, because we shouldn’t change the partition of a running system. Open a Terminal window and decrypt the encrypted partition of your installed system:

ubuntu@ubuntu:~$ sudo cryptsetup luksOpen /dev/sda3 crypt1
Enter passphrase for /dev/sda3

In that example, ensure that /dev/sda3 is your encrypted volume. If you are not sure, check with fdisk -l which is the big partition on your hard-drive, this is most likely the last one. On a NVMe hard-drive the name might be very different.

Once the volume decrypted, scan the LVM volume-groups and active them with the following commands:

root@ubuntu:~# vgscan --mknode
Reading volume groups from cache.
Found volume group "ubuntu-vg" using metadata type lvm2
root@ubuntu:~# vgchange -ay
2 logical volume(s) in volume group "ubuntu-vg" now active

You’ll be then able to check that the volume-group has been activated correctly with:

root@ubuntu:~# pvs
PV VG Fmt Attr PSize PFree
/dev/mapper/crypt1 ubuntu-vg lvm2 a-- 237.25g 48.00m

and list logical-volumes with:

root@ubuntu:~# lvs
LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert
root ubuntu-vg -wi-a----- 236.25g
swap_1 ubuntu-vg -wi-a----- 976.00m

It is recommended to scan the file-system prior to modifications:

root@ubuntu:~# e2fsck -f /dev/mapper/ubuntu--vg-root

Now you can resize your root partition to give some room for the swap partition. There is a whole science to calculate how big your swap should be if you want to hibernate, or you can follow the rule of thumb: RAM amount + 4GB. If the swap is exactly the size of your RAM this might work as well, a bit extra is recommended if your machine is actually swapping – rarely on modern machine – but this depend on you usage. If you start docker or virtual box sometime, follow the rule of thumb…

So in my case the volume-group is about 237GB big, I subtracted 12GB for the swap it leaves 225GB for the root (system) partition:

root@ubuntu:~# resize2fs -p /dev/mapper/ubuntu--vg-root 225g
resize2fs 1.44.1 (24-Mar-2018)
Resizing the filesystem on /dev/mapper/ubuntu--vg-root to 58982400 (4k) blocks.
Begin pass 2 (max = 2947)
Begin pass 3 (max = 1880)
Begin pass 4 (max = 24242)
The filesystem on /dev/mapper/ubuntu--vg-root is now 58982400 (4k) blocks long.

To be sure, just check the file-system again with:

root@ubuntu:~# e2fsck -f /dev/mapper/ubuntu--vg-root
e2fsck 1.44.1 (24-Mar-2018)
Pass 1: Checking inodes, blocks, and sizes
Pass 2: Checking directory structure
Pass 3: Checking directory connectivity
Pass 4: Checking reference counts
Pass 5: Checking group summary information
/dev/mapper/ubuntu--vg-root: 177123/14745600 files (0.1% non-contiguous), 2426029/58982400 blocks

you can now safely reduce the size of the logical-volume with:

root@ubuntu:~# lvreduce -L 225G -r /dev/ubuntu-vg/root 
fsck from util-linux 2.31.1
/dev/mapper/ubuntu--vg-root: clean, 177123/14745600 files, 2426029/58982400 blocks
resize2fs 1.44.1 (24-Mar-2018)
The filesystem is already 58982400 (4k) blocks long. Nothing to do!
Size of logical volume ubuntu-vg/root changed from 236.25 GiB (60481 extents) to 225.00 GiB (57600 extents).
Logical volume ubuntu-vg/root successfully resized.

You can remove the old swap volume:

root@ubuntu:~# lvremove /dev/ubuntu-vg/swap_1 
Do you really want to remove and DISCARD active logical volume ubuntu-vg/swap_1? [y/n]: y
Logical volume "swap_1" successfully removed

…and create a new bigger one. This following command says “use the remaining space”, and note that volume names are kept the same.

root@ubuntu:~# lvcreate -l 100%FREE -n swap_1 ubuntu-vg 
Logical volume "swap_1" created.

Check what’s just been done, the list of logical-volumes:

root@ubuntu:~# lvs
LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert
root ubuntu-vg -wi-a----- 225.00g
swap_1 ubuntu-vg -wi-a----- 12.25g

Create the swap file-system:

root@ubuntu:~# mkswap -L swap_1 /dev/ubuntu-vg/swap_1 
Setting up swapspace version 1, size = 12.3 GiB (13157527552 bytes)
LABEL=swap_1, UUID=4a10e0f5-44d7-4f57-ae23-de172958e7f1

And adapt the fstab if required. If you gave it the same logical-volume name than it already was, you can skip this step. Ubuntu 18.04 uses LV names instead of UUID.

root@ubuntu:~# mount /dev/ubuntu-vg/root /mnt/
root@ubuntu:~# vi /mnt/etc/fstab
root@ubuntu:~# umount /mnt

Write last changes to the disk:

root@ubuntu:~# pvchange -x n /dev/mapper/crypt1
Physical volume "/dev/mapper/crypt1" changed
1 physical volume changed / 0 physical volumes not changed

…and deactivate open volume-groups:

root@ubuntu:~# vgchange -an
0 logical volume(s) in volume group "ubuntu-vg" now active
root@ubuntu:~# cryptsetup luksClose crypt1

You can now restart your machine and boot your installed operating system. You will be prompted for the password at boot to decrypt the LUKS volume. Everything should work as before, but this time with a bigger swap partition.

Lastly, you’ll have to change a line in /etc/default/grub to tell your kernel that there possibly is the content of your RAM in your swap when booting. If so, it should “resume” from hibernation.


Where the value of UUID is the string returned when running mkswap to make the swap. If you don’t find it back in your terminal, just run:

lsblk -o NAME,UUID

Once GRUB defaults updated you will need to update the actual GRUB config files with the command

sudo update-grub

Reboot your machine, test the hibernation. If this works as it should, tap your own shoulder: Congratulation, the hardest work is done.

Configure hardware events and enable “hibernate” in the system actions (top right menu)

I wanted my laptop to suspend when I close the lid and hibernate after a defined time if the suspend isn’t resumed (namely: the lid kept closed)

To enable that, edit as system-logind configuration file:

sudo vim /etc/systemd/logind.conf

You’ll be prompted for the root password. If you don’t know how to use vim just grow up and learn.
Vi won’t be be removed from major distribution anytime soon.

Well, uncomment and edit the HandleHibernateKey and HandleLidSwitch variables:


The timeout to switch from suspend to hibernate can be set in /etc/systemd/sleep.conf. The file might not yet exists.

sudo vim /etc/systemd/sleep.conf

I found 15 minutes a good delay for an old 1th generation with a battery holding about 2h. For a 3rd generation with a much better battery (and economic architecture), I switched it to one hour.

When you are done, restart systemd-logind service with the following command:

sudo systemctl restart systemd-logind.service

If you need to debug the service, and possibly find a mistake in your config file, just check the logs:

sudo journalctl -u systemd-logind.service

Next, to enable the “hibernate” button in the system menu edit the /etc/polkit-1/localauthority/50-local.d/com.ubuntu.enable-hibernate.pkla file.

sudo vim /etc/polkit-1/localauthority/50-local.d/com.ubuntu.enable-hibernate.pkla

with the following content

[Re-enable hibernate by default in upower]
Identity=unix-user:* Action=org.freedesktop.upower.hibernate ResultActive=yes

[Re-enable hibernate by default in logind]
Identity=unix-user:* Action=org.freedesktop.login1.hibernate;org.freedesktop.login1.handle-hibernate-key;org.freedesktop.login1;org.freedesktop.login1.hibernate-multiple-sessions;org.freedesktop.login1.hibernate-ignore-inhibit ResultActive=yes

When your computer is in hibernation and you push the power button, you’ll be asked for a password, just like a normal boot. That’s right, the content of your RAM has been saved to the hard-drive and the machine completely turned off. To resume it, the volume need to be decrypted again, and for that your password is asked.

That’s it for the hibernation.


Install the synaptic driver


and copy the following content in /etc/X11/Xsession.d/56_synaptic_fix :

 export `xinput list | grep -i touchpad | awk '{ print $6 }'`
xinput --set-prop "$id" "Synaptics Noise Cancellation" 20 20
xinput --set-prop "$id" "Synaptics Finger" 35 45 250
xinput --set-prop "$id" "Synaptics Scrolling Distance" 180 180

Ensure that the owner of the file is root and permissions 644. Restart your session and you’ll have a usable trackpad.

Fingerprint scanner

Just install the pam package to enable fingerprint authentication on Ubuntu:

sudo apt install libpam-fprint

Then run the following command to teach your system to use the fingerprint as an authentication system:

sudo pam-auth-update

…and select the fingerprint option

You’ll find a new “Fingerprint login” option in users settings to register one finger.

Keep in mind that the finger-print authentication is not the safest thing ever. Your finger isn’t a password, it’s an image and the computer will try to “guess” if this is you, with potential mistakes. You will be able to log in with this technique, but not to unlock your password-keychain. For the latter you’ll always need a real password.


Rails/Devise authenticating using AWS/Cognito Identity


For a while now, I’m developing a sort of IoT controller with Rails 4. Until now, Devise was used to authenticate users locally using the Devise’s provided :database_authenticable module.

Things changed recently, and I had to move some features of this IoT controller toward AWS. Authentication against AWS/Cognito Identity is one part of the project.
But Why?” you might wonder. Because we need to extend authentication to other products, using a common user database. Cognito fits the description and helps boost the development on the AWS ecosystem. In other words, we want to be able to use our IoT controller alongside with other new products without depending exclusively on the authentication part of the IoT controller.
This means that local user database will still be used for the application-related data like permissions, session, extended profile attributes or database relationship, but pure authentication happen exclusively on Cognito. I moved to AWS just the first A of AAA. This is a disputable choice, let’s call it team work.

So, the purpose of this post is to synthesize the procedure required in order to:

  • authenticate against Cognito
  • handle passwords manipulation and accounts validation
  • migrate user data with a Lambda triggered by Cognito
  • create new local users if they are not already existing

The following example should work well with recent Rails versions.


Obviously Rails and Devise are required, in addition to the following gem:

gem 'aws-sdk', '~> 3'

Rails 4/Devise adaptation

Environment variables will carry all the AWS specific access information to Rails. Depending on the setup,  /.env file probably already exists in which we can add the two following Cognito Pool info (TLDR; Create a new App client without App client secret) :


where the variables values matches the AWS setup.

We’ll need a new a new Devise strategy, a safe location for this file is in /config/initializers/cognito_authenticable.rb :

require 'aws-sdk'
require 'devise/strategies/authenticatable'

module Devise
  module Strategies
    class CognitoAuthenticatable < Authenticatable
      def authenticate!
        if params[:user]

          client =


            resp = client.initiate_auth({
              client_id: ENV["AWS_COGNITO_CLIENT_ID"],
              auth_flow: "USER_PASSWORD_AUTH",
              auth_parameters: {
                "USERNAME" => email,
                "PASSWORD" => password

            if resp
              user = User.where(email: email).try(:first)
              if user
                user = User.create(email: email, password: password, password_confirmation: password)
                if user.valid?
                  return fail(:failed_to_create_user)
              return fail(:unknow_cognito_response)

          rescue Aws::CognitoIdentityProvider::Errors::NotAuthorizedException => e

            return fail(:invalid_login)


            return fail(:unknow_cognito_error)



      def email

      def password


This strategy will create a new local user if Cognito authenticated it, but the user doesn’t exist locally.

The we can configure Devise to use the strategy by giving it the class name in the file /config/initializers/devise.rb

Devise.setup do |config|

  # (...)

  config.warden do |manager|
    manager.strategies.add(:cognito, Devise::Strategies::CognitoAuthenticatable)
    manager.default_strategies(:scope => :user).unshift :cognito

  # (...)


Now Devise should authenticate against the Cognito user database.

The next step would be to allow password reset using the password recovery system of AWS Cognito.

class Users::PasswordsController < Devise::PasswordsController

  skip_before_action :assert_reset_token_passed

  def create

    raise ArgumentError, "Unexpected block given for requested action: #{params.inspect}" if block_given?


      client =
      resp = client.forgot_password({
        client_id: ENV["AWS_COGNITO_CLIENT_ID"],
        username: params[:user][:email]

      session[:reset_password_email] = params[:user][:email]

      redirect_to edit_user_password_path


      flash[:alert] = I18n.t("devise.errors.unknown_error")
      redirect_to new_user_password_path



  def edit

    gon.flash_notice = I18n.t("devise.notices.change_password_email")


  def update

    if params[:user][:password].blank?

      flash[:alert] = I18n.t("activerecord.errors.models.user.attributes.password.blank")
      redirect_to edit_user_password_path(reset_password_token: params[:user][:reset_password_token])

    elsif params[:user][:password] != params[:user][:password_confirmation]

      flash[:alert] = I18n.t("activerecord.errors.models.user.attributes.password.mismatch")
      redirect_to edit_user_password_path(reset_password_token: params[:user][:reset_password_token])

    elsif params[:user][:reset_password_token].blank?

      flash[:alert] = I18n.t("devise.errors.verification_code_missing")
      redirect_to edit_user_password_path(reset_password_token: params[:user][:reset_password_token])

    elsif session[:reset_password_email].nil?

      flash[:alert] = I18n.t("devise.errors.verification_code_expired")
      redirect_to new_user_password_path



        client =
        resp = client.confirm_forgot_password({
          client_id: ENV["AWS_COGNITO_CLIENT_ID"],
          confirmation_code: params[:user][:reset_password_token],
          username: session[:reset_password_email],
          password: params[:user][:password]

        session.delete :reset_password_email

        redirect_to unauthenticated_root_path, notice: I18n.t("devise.notices.password_changed")

      rescue Aws::CognitoIdentityProvider::Errors::InvalidPasswordException => e

        flash[:alert] = e.to_s
        redirect_to edit_user_password_path(reset_password_token: params[:user][:reset_password_token])


        flash[:alert] = I18n.t("devise.errors.unknown_error")
        redirect_to edit_user_password_path(reset_password_token: params[:user][:reset_password_token])





In this case, the sign up procedure is still handled by Rails on the local database (see below), therefore account confirmation (with Devise’s :confirmable  module) is handled by Devise without any changes.

Migrating users from Rails to Cognito

We wanted to migrate a user from the Rails database to Cognito if the user isn’t already existing in the Cognito database. For that at least a new endpoint in config/routes.rb  is required:

​post '/aws/auth',
 to: 'users#aws_auth',
 defaults: {format: 'json'},
 as: 'aws_auth'

For this route it is supposed that controllers/users_controller.rb  have a aws_auth  method:

class UsersController < ApplicationController

  # skip_filter other access restrictions...
  before_filter :restrict_access, only: [:aws_auth]

  # (...)

  def aws_auth

    defaults = {
      id: nil,
      first_name: nil,
      last_name: nil,
      email: nil,
      authentication_hash: nil
    user = User.where(email: aws_auth_params[:email]).first

    if user
      answer = user.as_json(only: defaults.keys)
      answer[:user_exists] = true
      answer[:success] = user.valid_password?(aws_auth_params[:password])
      answer = defaults
      answer[:success] = false
      answer[:user_exists] = false

    respond_to do |format|
      format.json { render json: answer }


  # (...)


    def restrict_access
      head :unauthorized unless params[:access_token] == TOKEN_AUTH_OF_YOUR_CHOICE


That’s all for the Rails side, now in Cognito Pool of the AWS console there is a Trigger, in which lambdas can be attached. The User Migration trigger can be set to the following JavaScript lambda:

'use strict';

console.log('Loading function');

const https = require('https');

const attributes = (response) => {
  return {
    "email_verified": "true",
    "name": response.first_name + " " + response.last_name,

const checkUser = (server, data, callback) => {
  let postData = JSON.stringify( data );

  let options = {
    hostname: server,
    port: 443,
    path: "/aws/auth",
    method: 'POST',
    headers: {
         'Content-Type': 'application/json',
         'Content-Length': postData.length

  let req = https.request(options, (res) => {

    let data = "";
    res.on('data', (chunk) => {
      data += chunk;
    res.on('end', () => {
      if ( data ){
        let response = JSON.parse( data );
        console.log( 'response:', JSON.stringify(response, null, 2) );
        callback( null, response);
      } else {
        callback( "Authentication error");

  req.on('error', (e) => {
    callback( e );

  req.write( postData );
exports.handler = (event, context, callback) => {

  console.log('Migrating user:', event.userName);

  let rails_server_url = process.env.rails_server_url || "";

  checkUser( rails_server_url, {
    email: event.userName,
    password: event.request && event.request.password,
    access_token: process.env.rails_server_access_token
  }, (err, response ) => {
    if ( err ){
      return"Connection error");
    if ( event.triggerSource == "UserMigration_Authentication" ) {
      // authenticate the user with your existing user directory service
      if ( response.success ) {
          event.response.userAttributes = attributes( response ) ;
          event.response.finalUserStatus = "CONFIRMED";
          event.response.messageAction = "SUPPRESS";
          console.log('Migrating user:', event.userName);
      } else if ( response.user_exists ) {
"Bad password");
      } else {"Bad user");
    } else if ( event.triggerSource == "UserMigration_ForgotPassword" ) {
      if ( response.user_exists ) {
        event.response.userAttributes = attributes( response ) ;
        event.response.messageAction = "SUPPRESS";
        console.log('Migrating user with password reset:', event.userName);
      } else {"Bad user");
    } else {"Bad triggerSource " + event.triggerSource);

This scripts use the two following environment variables, which should be set with the proper information:



It’s possible.

Raspberry Pi 3 and bluetooth speaker setup [command line]

Streaming sound from a Raspberry Pi 3 to a bluetooth speaker was not as easy as it should. Internet is overloaded with outdated information about this issue. So I compiled a small tutorial for the latest Raspbian (at the date of writing). My setup is composed with the following elements:

Trying to connect the device to the Raspberry Pi with the following code will raise an error:  Failed to connect: org.bluez.Error.Failed

pi@raspi:~# sudo bluetoothctl -a
Agent registered
[bluetooth]# default-agent
Default agent request successful
[bluetooth]# power on
Changing power on succeeded
[bluetooth]# scan on
Discovery started
[CHG] Controller B8:27:EB:XX:XX:XX Discovering: yes
[NEW] Device 40:EF:4C:AA:BB:CC JBL GO
[CHG] Device 40:EF:4C:AA:BB:CC UUIDs:
[bluetooth]# pair 40:EF:4C:AA:BB:CC
Attempting to pair with 40:EF:4C:AA:BB:CC
[CHG] Device 40:EF:4C:AA:BB:CC Connected: yes
[bluetooth]# trust 40:EF:4C:AA:BB:CC
[CHG] Device 40:EF:4C:AA:BB:CC Trusted: yes
Changing 40:EF:4C:AA:BB:CC trust succeeded
[CHG] Device 40:EF:4C:AA:BB:CC UUIDs:
[CHG] Device 40:EF:4C:AA:BB:CC Paired: yes
Pairing successful
[CHG] Device 40:EF:4C:AA:BB:CC Connected: no
[bluetooth]# connect 40:EF:4C:AA:BB:CC
Attempting to connect to 40:EF:4C:AA:BB:CC
Failed to connect: org.bluez.Error.Failed

It is not obvious, but what is missing to the connecting device is the answer from the host saying that it can stream sound. In order to send the correct answer to the device, pulseaudio should be started first and should correctly load the bluetooth module.

First, ensure that all requirements are installed:

pi@raspi:~# sudo apt install pi-bluetooth pulseaudio-module-bluetooth mpg123

Then start pulseaudio an check that the module is indeed loaded:

pi@raspi:~# sudo pulseaudio --start
W: [pulseaudio] main.c: This program is not intended to be run as root (unless --system is specified).
pi@raspi:~# pactl list | grep bluetooth
Name: module-bluetooth-policy
module.description = "When a bluetooth sink or source is added, load module-loopback"
Name: module-bluetooth-discover

At this point, if you try to connect your device, this should work. Most of the devices produce a sound when the connection succeed. Within bluetoothctl , it is not required to redo the pairing procedure, but before continuing you may need to ensure that the bluetooth controller is on with [bluetooth]# power on .

[bluetooth]# connect 40:EF:4C:AA:BB:CC
Attempting to connect to 40:EF:4C:AA:BB:CC
[CHG] Device 40:EF:4C:AA:BB:CC Connected: yes
Connection successful

Load a MP3 on your Raspberry Pi and play it using mpg123 your.mp3 .

If you restart your Raspberry Pi now, your device will fail to connect again.
pulseaudio is not really ment to start system-wide at boot, but in the case of embedded device it makes sense.

You could use /etc/rc.local  but it’s a bit punk, so instead let’s create a systemd service.
Add the following content to this file /lib/systemd/system/pulseaudio.service :

Description=PulseAudio Daemon


ExecStart=/usr/bin/pulseaudio --system --realtime --disallow-exit --no-cpu-limit

And start the service by hand using systemctl start pulseaudio . When running systemctl status pulseaudio , you may find a warning about starting pulseaudio system-wide, but don’t worry, with this setup it makes sense.
Add pulseaudio to the list of services to start at boot: systemctl enable pulseaudio.service

Don’t forget to add both root and pi users to the group pulse-access in /etc/groups :


Note that the group ID 115 may be different on your system.

Reboot your Raspberry Pi and you are good to go, if the speaker in ON, it should be automatically connected to your system.

Let’s encrypt (il buono), rails (il brutto) and heroku (il cattivo)

Il buono

This article is just paraphrasing this one with a bit more accuracy, corrections and cynicism.

First step: Update your rails code on heroku

The route /.well-known/acme-challenge/KEY  should be added to your config/routes.rb  file like so

get ‘/.well-known/acme-challenge/:id’ => ‘CONTROLLER#letsencrypt’

where CONTROLLER  is the controller of your choice, in which the method should look like this

 def letsencrypt
   if params[:id] == ENV['LETSENCRYPT_KEY']
     render text: ENV['LETSENCRYPT_CHALLENGE']
     render text: "nope"

and don’t forget to make it “public”, so if you are using cancancan the following line is required on top of your controller file

skip_authorization_check only: [:letsencrypt]

Push it on heroku

> git push heroku # this may differ depending on your setup

and wait for it be deployed.

Second step: Install require software and generate the key

On ubuntu you can install letsencrypt  command like this

> sudo apt install letsencrypt

The run the command with root privileges

> sudo letsencrypt certonly --manual

follow the instructions and when it asks you to verify that the given URL is reachable, don’t presse ENTER but follow third step instead.

Third step: Update Heroku variables

Go on Heroku console, in settings>Reveal config vars and add LETSENCRYPT_KEY  and LETSENCRYPT_CHALLENGE  keys with their corresponding values from letsencrypt  command, a step before.

Restart Heroku within UI or with the following command where YOUR_APP_NAME  is… your app name.

> heroku restart -a YOUR_APP_NAME

It would be a good idea to try the URL from your browser before coninuing.

Fourth step: Verify the challenge and push certificate to Heroku

If your SSL endpoint is not yet setup on Heroku, take the time and money to do it

> heroku addons:create ssl:endpoint -a YOUR_APP_NAME

Then you will be able to push the certificate to your Heroku instance.

> sudo heroku certs:add /etc/letsencrypt/live/YOUR_DOMAIN/fullchain.pem /etc/letsencrypt/live/YOUR_DOMAIN/privkey.pem -a YOUR_APP_NAME

If it’s a certificate update, replace the certs:add  by certs:update  and your are good.

Fifth and last step: Behold!

Give yourself some time for a walk and think about the beauty of living, yet still away from the coming technological singularity.

Slow Query with “LIMIT 1” in Postgresql

Recently I had issues on a production database running slow on tables with more than 1M rows.
This is a complex Ruby on Rails app connected to a Postgresql database, I went through the whole stack to figure out that it’s a simple index problem. Very few or no related doc was found about this issue, therefor I quickly post a summary of my adventure.

In Rails I often write something simple as ActivityLog.first or Player.find(123).activity_logs.first , where the model ActivityLog have a default scope describe like so:

class ActivityLog < ActiveRecord::Base
  default_scope { order("created_at desc") }

This leads to a SQL query looking like this:

SELECT "activity_logs".* FROM "activity_logs" WHERE "activity_logs"."player_id" = 123 ORDER BY created_at ASC LIMIT 1;

This is probably one of the most common query in my Rails applications, and certainly not only in my applications.

Unfortunately, this may become a slow query on big tables. Indeed, if this query is often called your app may become from sticky to unusable (the latter was my case).

The following example illustrate the issue.
I recall it: this may be a very common query:

# EXPLAIN ANALYSE SELECT "activity_logs".* FROM "activity_logs" WHERE "activity_logs"."player_id" = 123 ORDER BY created_at ASC LIMIT 1;
Limit (cost=0.42..464.30 rows=1 width=708) (actual time=1124.357..1124.357 rows=0 loops=1)
-> Index Scan using index_activity_logs_on_created_at on activity_logs (cost=0.42..421660.15 rows=909 width=708) (actual time=1124.356..1124.356 rows=0 loops=1)
Filter: (player_id = 123)
Rows Removed by Filter: 1033340
Planning time: 0.084 ms
Execution time: 1124.378 ms
(6 rows)

This query on my dev machine, which is way more powerful than our production VPS takes 1’124ms to run. If called only once per user’s click, this would immediately degrade your Apdex score.

As described in Postresql doc and by reading the EXPLAIN (ANALYZE, BUFFERS) output, a lot of disk access caused by the filter on player_id makes the query slow.

The numbers provided by BUFFERS help to identify which parts of the query are the most I/O-intensive.

# EXPLAIN (ANALYSE, BUFFERS) SELECT "activity_logs".* FROM "activity_logs" WHERE "activity_logs"."player_id" = 123 ORDER BY created_at ASC LIMIT 1;
Limit (cost=0.42..464.30 rows=1 width=708) (actual time=1127.860..1127.860 rows=0 loops=1)
Buffers: shared hit=405695 read=445170
-> Index Scan using index_activity_logs_on_created_at on activity_logs (cost=0.42..421660.15 rows=909 width=708) (actual time=1127.858..1127.858 rows=0 loops=1)
Filter: (player_id = 123)
Rows Removed by Filter: 1033340
Buffers: shared hit=405695 read=445170
Planning time: 0.083 ms
Execution time: 1127.880 ms
(8 rows)

The planner took the decision to use an index scan on created_at (the ORDER BY  attribute) and then filter player_id which in this situation isn’t the best choice. Disabling it with SET enable_indexscan = OFF;  improve drastically the performances, but it’s not a production solution.

# SET enable_indexscan = OFF;
# EXPLAIN ANALYSE SELECT "activity_logs".* FROM "activity_logs" WHERE "activity_logs"."player_id" = 123 ORDER BY created_at ASC LIMIT 1;
Limit (cost=3395.09..3395.09 rows=1 width=708) (actual time=0.026..0.026 rows=0 loops=1)
-> Sort (cost=3395.09..3397.36 rows=909 width=708) (actual time=0.025..0.025 rows=0 loops=1)
Sort Key: created_at
Sort Method: quicksort Memory: 25kB
-> Bitmap Heap Scan on activity_logs (cost=19.47..3390.54 rows=909 width=708) (actual time=0.021..0.021 rows=0 loops=1)
Recheck Cond: (player_id = 123)
-> Bitmap Index Scan on index_activity_logs_on_player_id (cost=0.00..19.24 rows=909 width=0) (actual time=0.019..0.019 rows=0 loops=1)
Index Cond: (player_id = 268)
Planning time: 0.087 ms
Execution time: 0.052 ms
(10 rows)

The query execution time drop down to 0.05ms which is factor of 22’480 from the previous planner decision !

As described here another way to trick the planner is to add another field in the ORDER BY  clause.

# EXPLAIN (ANALYSE, BUFFERS) SELECT "activity_logs".* FROM "activity_logs" WHERE "activity_logs"."player_id" = 268 ORDER BY created_at ASC, id ASC LIMIT 1;
Limit (cost=3395.09..3395.09 rows=1 width=708) (actual time=0.038..0.038 rows=0 loops=1)
Buffers: shared read=3
-> Sort (cost=3395.09..3397.36 rows=909 width=708) (actual time=0.037..0.037 rows=0 loops=1)
Sort Key: created_at, id
Sort Method: quicksort Memory: 25kB
Buffers: shared read=3
-> Bitmap Heap Scan on activity_logs (cost=19.47..3390.54 rows=909 width=708) (actual time=0.034..0.034 rows=0 loops=1)
Recheck Cond: (player_id = 268)
Buffers: shared read=3
-> Bitmap Index Scan on index_activity_logs_on_player_id (cost=0.00..19.24 rows=909 width=0) (actual time=0.032..0.032 rows=0 loops=1)
Index Cond: (player_id = 268)
Buffers: shared read=3
Planning time: 0.093 ms
Execution time: 0.060 ms
(14 rows)

Which improve the execution time in a comparable way. In a hurry this the solution a adopted, I have quickly patched my scope that way to improve globally the performances. It was safe and easy to commit with the advantage not to expand database indices size.

class ActivityLog < ActiveRecord::Base
  default_scope { order("created_at desc, id desc") }

But for the sake of a better understanding I dug a bit more this issue. I have more than one big table, and I have more than one app online… I don’t like to keep such gray zone and smoking work-around in my code.

First, I tried to help the planner change its decision by tweaking statistics on every column, but it didn’t changed planner decision and didn’t improve the execution time.

# ALTER TABLE activity_logs ALTER COLUMN created_at SET STATISTICS 1000;
# EXPLAIN (ANALYSE, BUFFERS) SELECT "activity_logs".* FROM "activity_logs" WHERE "activity_logs"."player_id" = 268 ORDER BY created_at ASC LIMIT 1;
Limit (cost=0.42..474.81 rows=1 width=707) (actual time=1145.167..1145.167 rows=0 loops=1)
Buffers: shared hit=405660 read=445205
-> Index Scan using index_activity_logs_on_created_at on activity_logs (cost=0.42..421251.54 rows=888 width=707) (actual time=1145.165..1145.165 rows=0 loops=1)
Filter: (player_id = 268)
Rows Removed by Filter: 1033340
Buffers: shared hit=405660 read=445205
Planning time: 0.092 ms
Execution time: 1145.187 ms
(8 rows)

Then I realized that simply building an index on player_id, my condition, and created_at, my ordering attribute, should help Postgersql extracting my query while using the actual planner decision.

CREATE INDEX activity_logs_player_id_created_at ON activity_logs (player_id, created_at);

…and the end result is pleasant enough. 0.03ms – my work is almost done.

# SET enable_indexscan = ON;
# EXPLAIN (ANALYSE, BUFFERS) SELECT "activity_logs".* FROM "activity_logs" WHERE "activity_logs"."player_id" = 268 ORDER BY created_at ASC LIMIT 1;
Limit (cost=0.42..4.45 rows=1 width=707) (actual time=0.013..0.013 rows=0 loops=1)
Buffers: shared hit=3
-> Index Scan using activity_logs_player_id_created_at on activity_logs (cost=0.42..3570.31 rows=888 width=707) (actual time=0.012..0.012 rows=0 loops=1)
Index Cond: (player_id = 268)
Buffers: shared hit=3
Planning time: 0.099 ms
Execution time: 0.031 ms
(7 rows)

On the Rails side a simple migration like the following change my user’s life, and restored my peace of mind.

class AddIndicesToBigTables < ActiveRecord::Migration
  def change
    add_index :activity_logs, [:player_id, :created_at], unique: false