Temporal Databases Annotated Bibliography


I’ve been reading about temporal databases for a few years now, so I think it’s time I share my bibliography and notes. This is presented in “narrative order”, so that you can get a sense of how the research has developed. This article somewhat overlaps a mini literature review I wrote on the Postgres hackers mailing list, but this article is more complete and in a place where I can keep it updated.

Temporal databases let you track the history of things over time: both the history of changes to the database (e.g. for auditing) and the history of the thing itself. They are not the same thing as time-series databases: whereas a time-series database has time-stamped events, a temporal database stores the history of things, typically by adding a start/end time to each row (so two timestamps, not one). With time-series the challenge is typically scale; with temporal the challenge is with complexity and correctness.


Snodgrass, Richard T. Developing Time-Oriented Database Applications in SQL. 1999. The seminal work on temporal databases and still the most useful introduction I know. Covers the “combinatorial explosion” of non-temporal/state-temporal/system-temporal/bi-temporal tables, current/sequenced/non-sequenced queries, SELECT/INSERT/UPDATE/DELETE, different RDBMS vendors, etc. Very similar to the proposed TSQL2 standard that was ultimately not accepted but still influenced Teradata’s temporal support. Available as a free PDF from his website.

Hugh Darwen and C. J. Date. “An Overview and Analysis of Proposals Based on the TSQL2 Approach.” Latest draft 2005, but originally written earlier. Criticizes the TSQL2 proposal’s use of “statement modifiers”, especially their problems with composability when a view/subquery/CTE/function returns a temporal result. Available as a PDF.

Ralph Kimball and Margy Ross. The Data Warehouse Toolkit. 3rd edition, 2013. (2nd edition 2002, 1st 1996.) (My notes are based on reading the 2nd edition, but I don’t think there are major relevant changes.) This book is not about temporal databases per se, but in Chapter 4 (and scattered around elsewhere) he talks about dealing with data that changes over time (“Slowly Changing Dimensions”). His first suggestion (Type 1) is to ignore the problem and overwrite old data with new. His Type 2 approach (make a new row) is better but loses the continuity between the old row and the new. Type 3 fixes that but supports only one change, not several. This writing is evidence for the need to handle temporal data, and the contortions that result from not having a systematic approach. (pdf)

C. J. Date, Hugh Darwen, Nikos Lorentzos. Time and Relational Theory, Second Edition: Temporal Databases in the Relational Model and SQL. 2nd edition, 2014. (First edition published in 2002.) I haven’t read this one yet but I would love to see what Date’s ideal system looks like. If you’ve read his other works you know that he is quite rigorous, often critical of SQL’s compromises vs the pure relational model (e.g. NULL and non-distinct results), and not always very practical. I think his idea might look something like sixth-normal form, which would be great for temporal DDL but sounds burdensome to use.

SQL:2011 Draft standard. (pdf) Personally I find the standard pretty disappointing. It uses separate start/end columns instead of built-in range types, although range types offer benefits like exclusion constraints and convenient operators for things like “overlaps” that are verbose to code correctly by hand. It only mentions inner joins, not the various outer joins, semi-joins (EXISTS), anti-joins (NOT EXISTS), or aggregates. Many of its features apply only to system-time, not application-time, even though applicaion-time is the more interesting and less-available feature. (There are lots of auditing add-ons, but almost nothing for tracking the history of things.) The syntax seems too specific, lacking appropriate generality. A lot of these drawbacks seem motivated by a goal that goes back to TSQL2: to let people add temporal support to old tables without breaking any existing queries. That has always seemed to me like an unlikely possibility, and an unfortunate source of distortions. I don’t expect something for free, and I don’t mind doing work to migrate a table to a temporal format, as long as the result is good. Instead we get an (ostensible) one-time benefit for a prolonged compromise in functionality and ease-of-use.

Krishna Kulkarni and Jan-Eike Michels. “Temporal Features in SQL:2011”. SIGMOD Record, September 2012. Nice overview of the temporal features included in the SQL:2011 standard. Here is a PDF of the paper. See also these slides by Kulkani.

Peter Vanroose. “Temporal Data & Time Travel in PostgreSQL,” FOSDEM 2015. (Slides as a pdf) Lots of good detail here about SQL:2011. I’d love to see a recording of this talk if it’s available, but I haven’t found it yet.

Tom Johnston and Randall Weis. Managing Time in Relational Databases: How to Design, Update and Query Temporal Data. 2010. I haven’t read this one yet, although see just below for Johnston’s other book. This one sounds more practical and less original, although I don’t know for sure.

Tom Johnston. Bitemporal Data: Theory and Practice. 2014. I felt like I found a kindred soul when I read how he connects database design and ontology, as I’ve always thought of programming as “applied philosophy.” Databases as Aristotelian propositional logic is inseparable from the mathematical set-based theory. Johnston gives helpful distinctions between the physical rows in the table, the assertions they represent, and the things themselves. Eventually this leads to a grand vision of connecting every assertion’s bitemporal (or tritemporal) history to its speaker, somewhat like some ideas in the Semantic Web, although this doesn’t sound very practical. Like Date he seems to be landing on something like sixth-normal form, with a view-like presentation layer to bring all the attributes back together again. He points out how unsatisfactory Kimball’s suggestions are. He also criticizes the limitations of SQL:2011 and offers some amendments to make it more useful. Describes a (patented) idea of “episodes” to optimize certain temporal queries.

Anton Dignös, Michael H. Böhlen, and Johann Gamper. “Temporal Alignment”, SIGMOD ’12. Amazing! Shows how to define temporal versions of every relational operator by a combination of the traditional operators and just two simple transforms, which they call “align” and “split”. Gives a very readable exposition of the new theory and then describes how they patched Postgres 9.0 and benchmarked the performance. I think this solves the composability problems Date objected to in TSQL2, and unlike SQL:2011 it is general and comprehensive. The focus is on state-time, and I’m not sure how it will map onto bi-temporal, but even just having good state-time functionality would be tremendous. And the paper is only 12 easy-to-read pages! (pdf)

Anton Dignös, Michael Hanspeter Böhlen, Johann Gamper, and Christian S. Jensen. “Extending the kernal of a relational dmbs with comprehensive support for sequenced temporal queries,” ACM Transactions on Database Systems, 41(4):1-46. Continues the previous paper but adds support for scaling the inputs to aggregate groups according to how much of their time period goes into each group. Gives more benchmarks against a patched Postgres 9.5. (pdf) These researchers are now trying to contribute their work to the Postgres core project, of which I am very much in favor. :-)


Finally here are some tools for temporal support in Postgres. The sad theme is that pretty much everything gives audit support but not history support:


The pgaudit extension looks pretty useful but I haven’t tried it yet. According to the AWS docs you can even use this on RDS.

Vlad Arkhipov’s temporal tables extension only supports system-time (auditing). Also on Github and a nice writeup by Clark Dave.

Magnus Hagander presented an approach to capturing system-time history in a separate schema at PGConf US 2015 and PGDay’15 Russia. Here are slides and video. Quite elegant if you want to ask questions like “what did we think as of time t?” If I recall correctly this is similar to one of the ideas proposed at the end Snodgrass, although I haven’t compared them carefully. Hagander points out that DDL changes against temporal databases are challenging and hopefully infrequent. This is a topic that is almost completely absent from the literature, except for a brief mention in Johnston 2014.


Chronomodel extends the ActiveRecord ORM to record system-time history. The pg_audit_log gem works fine but like many audit solutions is rather write-only. I wouldn’t want to build any functionality that has to query its tables to reconstruct history. You could also try paper_trail or audited (formerly acts_as_audited). Of these projects only Chronomodel seems to be aware of temporal database research.

Further Research

Temporal databases are exciting because there is still so much to do. For example:

  • What should the UI look like? Even one dimension adds a lot of complexity, let alone bi-temporal. How do you present this to users? As usual an audit history is easier, and it’s possible to find existing examples, whereas a state-time history is more challenging but probably more valuable. How should we let people view and edit the history of something? How does it work if there is a “Save” button vs save-as-you-type?

  • What does “full stack” temporal support look like? Do we extend REST? What would be a nice ORM interface? Should we use triggers to hide the temporal behavior behind regular-looking SQL? Or maybe extend SQL so you can more explicitly say what you want to do?

  • SELECT support for “as of” semantics or “over time” semantics.

  • Temporal foreign keys. I’m working on this one.

  • DDL changes. For example if you want to add a NOT NULL column, what do you do with the old data? Could there be built-in support to apply constraints only to a given time span?

Postgres isn't running the archive_command on my standby


This just came up on the Postgres mailing list, and I spent a long time figuring it out a few months ago, so maybe this blog post will make it a bit more Googleable.

The problem is you have a master archiving to a standby, but you want the standby to run an archive command too, either to replicate downstream to another standby, or to take pressure off the master when running base backups, e.g. with the WAL-E backup tool. For some reason the master’s achive_command runs fine, but the standby’s isn’t even getting used!

The issue is that in 9.5 and 9.6, Postgres will ignore an achive_mode=on setting if it is running in standby mode. Arguably this is kind of useful, so that you can set up the standby as close to the master as possible, and if you fail over it will immediately start running that command.

But if you really do want to do archiving from the standby, the solution is to say archive_mode=always. Once you make that change, Postgres will start running your archive_command.

Btw, if you are using Ansible, as of today the postgresql role does not respect always. If you give it something truthy it will always generate on. I’ve written a pull request to support always, but it is not yet merged.

Javascript Daylight Savings Time: One Weird Trick Your Application Hates


I’ve talked in the past about how to handle timezones in Rails, so here is a tip for handling timezones in Javascript, in particular around Daylight Savings Time.

Suppose you have a time: April 3, 2017, at midnight Pacific Time. You want to express it as UTC in ISO 8601 format, for instance to send it over the wire as JSON. The result is "2017-04-03T07:00:00.000Z". Note the 07:00. Pacific Time is -8 hours from UTC during Standard Time, and -7 hours during Daylight Savings Time. April 3 falls in Daylight Savings Time.

Now suppose we change the year: April 3, 1969, still at midnight Pacific Time. DST started later that year, so now the answer is "1969-04-03T08:00:00.000Z". But if we run new Date(1969, 3, 3).toISOString() your browser gives us: . That might look correct, or you might see a 07:00 again.

Believe it or not, the original Javascript specification said that browsers should use the current year’s Daylight Savings Time transition dates when building dates from any year. If you just re-read that sentence in disbelief and still think it is too crazy to be real, here is a conversation with links to the old and new spec. I think it’s crazy too!

Right now, some browsers do the right thing (ignore the old spec), some do the wrong thing (follow the old spec), and it also depends on what version you’re running. It even seems to depend on what year you’re asking about. For instance modern Chrome seems to give me the right answers back to 1970, but then is wrong before that. Also, even if your browser does the wrong thing, you might still get lucky based on the current year and the date you’re trying to build. I wrote a jsbin page you can load in multiple browsers to see if they agree.

I think the only safe answer is to use moment-timezone to build your dates. For instance if you know the timezone:

moment.tz([y, m, d], tz)

or if you don’t:

moment.tz([y, m, d], moment.tz.guess())

(And don’t forget the m is off by one.)

If you need to force that back into a regular Date object, you could do:

new Date(moment.tz([y, m, d], tz).toJSON())

Just make sure that you’re using moment-timezone-with-data.js, not plain moment-timezone.js, or you’ll still be relying on the browser’s own idiosyncratic behavior.

I hope this is helpful to someone. If your users enter birthdays with some kind of date picker, you probably suffer from this bug!

What to Learn


I once heard a tech speaker say that in programming her job description was “learn new things,” and I’m happy to steal that way of putting it. In seventeen years of professional work I’ve never done a project that didn’t require me to learn something new on-the-job. It’s what I love about programming. But how do you decide what to learn?

Some things you learn because your project demands it, and those lessons are small and focused (hopefully most of the time): a new library here, some protocol detail there, today something about Linux, tomorrow something about Docker. This is stuff you do on the job, practically every day.

That’s not what I’m talking about here, but I’ll offer some advice in passing: when you learn a new tidbit, try to write it down. You don’t have to spend time polishing it, and if it helps then write it somewhere private. But write it down. I use personal man pages for this—and I’m not very good at it myself. If you’re bad at it too, then at the very least spend an extra 20 minutes making sure you actually understand, and come up with a few experiments to test that your take is correct. Try to put your understanding into words, at least in your own head.

But instead of that on-the-spot learning, I want to talk about things we learn that take more time and have a more long-term payoff. We typically do this off the job, without pay. I think most programmers love learning (or they would soon find a different job), so we can’t help ourselves. But also it pays to keep your skills current and sharp. Every professional has to do this. My favorite book about professional services work (which despite the title is about way more than managing) talks about developing your “asset”—you—by continuously learning. Lawyers, architects, accountants, doctors—all have to keep learning. Car mechanics too. With programmers the pace is different, but I expect the world doesn’t exactly stand still for anyone else either.

There is so much to learn! And the hype is everywhere, stealing your attention and diffusing your time. Prototype, jQuery, Backbone, Knockout, Angular, Ember, React, Vue, … Less, Sass, Uglify, Asset Pipeline, Npm, Bower, Babel, Gulp, Grunt, Ember-cli, Webpack, … Oracle, MySQL, Postgres, Memcached, Cassandra, Mongo, CouchDB, Redis, Riak, DynamoDB, … Aaah! You can’t learn it all, so you have to be deliberate.

I often hear advice to learn one new language a year, two new languages a year, whatever. The best versions of this advice say to learn a new “kind” of language, like Lisp or Haskell or Prolog. For a new programmer, that’s pretty good advice, and I still follow it myself. (For me the last few years it’s been Haskell, Rust, and Elixir.) But for several years I’ve tried to adopt a more strategic approach. One of the problems with learning another language is that either it’s something you won’t actually use, or you mostly leave behind the old one, so it’s like starting over from zero. (Not really, but a little bit.) After a couple dozen you start to wonder how to make the investment more worthwhile. Is there a way to make our learning build on itself, so we aren’t throwing away so much time? Here is my own approach to having some “continuity” in what I learn:

First, realize that there are so many more categories besides language! There are operating systems, cloud environments, back-end frameworks, front-end frameworks, databases, build tools, deployment tools, networking protocols, specialties like GIS or machine learning, industries like e-commerce or finance or health care, “soft” skills like writing, requirements gathering, design, management, financial planning, sales. Don’t get stuck in a rut of thinking in only one dimension.

Second, don’t be too focused. Go ahead and mix in some “useless” learning. I’ve had fun lately reading about the history of transistors, the integrated circuit, and the Internet. Or to take it to an extreme, you could learn some Greek or Latin or Chinese. :-) Whatever you like. One of those books (The Chip) actually talks about how Jack Kilby, the co-inventor of the IC, would read several newpapers and a bunch of magazines every day, plus every new patent granted by the government. Maybe that’s an extreme, but it’s good to have some breadth because you never know what will come in handy or inspire you. But more than that, recreation is important. Read some trashy science fiction or something.

But when you are being deliberate, I think there are three good alternatives to “learn another language”. The first is to learn something that complements your current skills. Suppose you are (or want to be) a “full-stack web developer.” Okay, learn some Rails, pick one Javascript framework and learn it, but then also learn some advanced Postgres, learn some details about HTTP or SSL or CORS, learn Wireshark and IP, learn HTTP Canvas, learn a configuration management tool like Chef or Ansible. I think Chef is a great complement to Rails (or Ansible to Django). My own current “expansion of territory” is down the stack, learning some Rust and reading The Linux Programming Interface. Learn the things that border on what you do, so you are gradually expanding. This is “breadth”, but in a calculated, not desultory way. You’ll probably put those skills to use right away, so they’ll sink in and make you better at your job.

Second is to dive deep somewhere. I’ve really enjoyed getting to know Postgres. I’ve been hired to scale it, to replicate it, to write C extensions for it. Maybe for you it is React or Datomic or AWS or reverse engineering or Ruby performance tuning. But get on the mailing list, follow what problems the community is trying to solve right now, write some blog posts, get to know someone in the community. Whatever it is, use it enough to find some friction points and maybe even fix one or two. You don’t have to make it your whole identity (though you could), but instead of learning a new thing, go in the opposite direction: go deep.

So far this is a lot like the classic “T-shaped person” advice, but I’m saying that for the breadth, pick things connected to your specialty, and for your specialty, have a “specialty within the specialty”. Keep trying to push a little further out, a little further down.

Third and finally is to learn something truly new, at the cutting edge of research. For the last couple years I’ve been reading about temporal databases, which have 20-30 years of academic study but few well-developed practical tools (especially open source ones). This isn’t something I’ve been able to use on a real project (yet), but it’s been great fun, and it feels like a way to find opportunities to build something before anyone else does. How you find your topic is by listening to your pain and seeing if there are other people trying to solve the same problems. Some other things I wish I could become an expert in: Bayesian statistics, operational transforms, HTTP/2, column-store databases, RDF, type theory, vector CPU instructions. There is so much happening! Pick something that people are writing papers about and learn a little.

So that’s what I’ve learned the last few years about learning. Instead of “learn another language”, try to be strategic. Try to build on what you have. Careers are long, so try to find some long-term problems you can grapple with. Maybe like Kilby you will even solve one!

Doing Many Things


I often say that making code do one thing is easy, but making it do lots of things at once is hard. You especially see this in UI code, although it happens on the back end too. The worst of all is front-end web development, where so many things are outside your control.

For example, a customer asks to add a real-time “suggestions” feature to a search box, so that as users type they see possible things to search for. That’s pretty easy, until you think about all the details you have to support:

  • It works in IE/Firefox/Chrome/Safari, across lots of versions.
  • It works on Windows/OS X/iOS/Linux.
  • It works on desktops/tablets/phones.
  • It works when the user resizes the browser window.
  • It works when a user has configured text to appear larger than normal.
  • It has a hover effect that changes the background color and the cursor.
  • It has a hover effect that bolds the suggestion text, but doesn’t resize the row’s height/width.
  • It works when there are two search boxes on the page.
  • It works in the header/body text/left sidebar.
  • It works inside a dropdown menu.
  • It works inside a div that is floated.
  • It works inside a div with absolute positioning.
  • It works inside a div with relative positioning.
  • It works inside flowed text.
  • It works in an iframe.
  • It works without resizing the containing element.
  • It works when the containing element has overflow: hidden.
  • It works on lots of different pages across the website.
  • It works on all our different websites.
  • It works when the doctype is HTML5, HTML 4 Loose, HTML 4 Strict, XHTML, and missing.
  • It works in Bootstrap/Foundation/our custom CSS. (Hopefully you can just pick one here!)
  • It works with vanilla Javascript/jQuery/Angular/React/Ember. (Ditto.)
  • It works with or without Turbolinks.
  • It works with Ajax.
  • It works when the Javascript/CSS is minified and concatenated.
  • It works when the HTML and other static assets are served from a CDN on a different domain name from the back end.
  • The suggestions cover whatever is below the search box.
  • … but new modal dialogs cover the suggestions.
  • It works inside a modal dialog.
  • The suggestions don’t extend outside the modal dialog, but you can scroll to see more of them.
  • It works when a modal dialog opens another modal dialog.
  • Clicking a suggestion takes you to the search results.
  • Clicking elsewhere hides the suggestions.
  • Clicking a link elsewhere on the page still works when suggestions are visible.
  • When in a modal dialog and showing suggestions, clicking in the dialog hides just the suggestions.
  • When in a modal dialog and showing suggestions, clicking outside the dialog hides the suggestions and the dialog too.
  • When the search box is close to the bottom of the page, suggestions appear above it instead of below.
  • Clicking a suggestion doesn’t show that funny dashed line.
  • Clicking a suggestion row in the margin around the text still chooses the suggestion.
  • It works when suggestions include quotes and angle brackets (", ', <, >).
  • It works in English/Spanish/French/Mandarin Chinese/etc.
  • It works with right-to-left text (e.g. Arabic).
  • When a suggestion is too long to fit, it appears with an ellipsis, but hovering shows a tooltip with the whole suggestion.
  • The ellipsis works correctly with Arabic and Chinese text.
  • The ellipsis doesn’t change when the text becomes bold from a hover.
  • The back button works as expected.
  • It works when there are no suggestions.
  • It works when there are 1,000 sugestions, but it doesn’t show them all.
  • You can use a scroll bar to see more suggestions.
  • Scrolling down far enough loads more suggestions from the back end.
  • A suggestion is guaranteed to have at least some search results.
  • Suggestions show how many search results they have before you click.
  • It is fast.
  • It has tests.

I’m not saying it’s reasonable to do all these things every time. You may have customers for whom supporting Arabic and Chinese is a waste of money. I didn’t even list the things users will ask for that are just crazy! I did throw in “search box inside a dropdown menu” to give you a mild taste, and I may have included others that are … questionable. But you have to at least think about them.

In any case, there is a lot to get right, and these details are the difference between polished and unpolished. I learned early in my career that if I deliver something where a few details are still not quite right, I will surely get caught and have to fix it, and the customer will be a lot happier if they never see the broken version in the first place. Polish takes time, but it is still required.

The problem is that completing the list gets harder and harder as you approach the end. No item by itself is hard, but adding the next item without breaking the others is hard. In relativity, accerlating at high velocity takes more energy than accelerating the same amount at low velocity, and the energy to reach c is infinite. Web development has a similar law: requirement n + 1 costs more than requirement n. Adding an ellpisis isn’t hard. Adding it in right-to-left text with an Arabic font on IE 6 in quirks mode when the user has large text enabled is hard.

And now, just to draw one more connection: one thing you learn from experience is ruthless simplification, and skepticism toward adding “concepts” to your app. If you have n features, and they all interact, the complexity grows not by O(n) but O(n^2). I’ve talked mostly about front-end work, but I think it is the same everywhere, and we just see it more on the front end because there it is so unavoidable. Some of it is multiple browsers, some of it is features users expect from any UI, but there our n is already high before we even do anything. In our own feature design we can at least be ruthless. On the front end, we just have to keep polishing. It makes me pity people who sell Wordpress plugins for a living.

As much as you can, it pays off tremendously to manage the combinatorial explosion. You can limit scope, you can stop supporting old browsers. You can design components to be isolated, since if they don’t interact they don’t raise each other’s n. Somehow you have to keep that number under control. The good news is that the marginal benefit of cutting n is large.

You can also write tests, which let you tolerate a higher n by making it less expensive to test in an n^2 world. Sadly browser tests are the most expensive to write, the most brittle (i.e. the shortest-lived), and the longest to run, but still I am always glad to have them.

I guess I have written myself into two morals here. One is: don’t permit any more complexity than you have to. The second is: sometimes you just have to keep polishing, also known as “That’s why they call it work!” Painfully, these really tug you in different directions. Choosing when to apply them is not easy.

Good luck!

Scaling Sidekiq


Sidekiq is a great option for handling background jobs in Ruby projects. Here I’ll show you how to get the best utilization out of a box dedicated to running Sidekiq jobs. Whether you have one machine or ten, the goal is to work off as many jobs as possible from each machine.

CPU Utilization

To do that, we want to keep every core busy. You can monitor your CPU activity with a tool like vmstat(8). If you say vmstat 10, you’ll get a new row every ten seconds, like so:

procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 9  0  92056 553620 174128 6268208    0    0     1    25 1323 1443 23 77  0  0  0
 8  0  92056 552628 174128 6268492    0    0    14    38 1307 1447 22 78  0  0  0
 8  0  92056 551760 174132 6268672    0    0     3    49 1317 1461 23 77  0  0  0
 8  0  92056 550544 174140 6268832    0    0     1    28 1353 1490 23 77  0  0  0
 8  0  92056 550296 174148 6268940    0    0     1    35 1240 1360 22 78  0  0  0

The last few columns show percentage of CPU time doing user work (us), system work (sy)—which more or less means executing kernel system calls—, idle (id), and blocked on I/O (wa, “waiting”). Add the first two together to get how much your CPU is working.

There is also st, which means “stolen time”! If you are running a VM, this means the time your hypervisor gives to someone else. You will probably see all zeros in this column, so I’ll ignore it going forward. We want to get our idle and wait time as close to zero as possible, without drowning the machine in too much work. Above we have a machine that is doing a good job keeping busy. You can see that its user and system time are at 100, and the other columns are zero.

So how do we do this? Usually it means running lots of jobs at the same time, not one-after-another. Even a one-core machine can juggle many jobs. That’s because jobs typically have to block on I/O, for example when they talk to the network or save things to disk. While that job is waiting for an answer, your CPU can work on another one.

(This is not really relevant here, but disk I/O is a little different than other I/O. Technically, reading/writing with a regular file can’t “block” but only “sleep”. That completely messes up non-blocking I/O for regular files, and even the newer aio functions have many limitations and in fact are implemented by threads in userspace—but fortunately we are not talking about single-threaded non-blocking I/O; we are talking about multiple processes/threads. Whether you call it blocking or sleeping, the CPU will still schedule different work if something is stuck on a regular file read. And if this paragraph doesn’t make sense, feel free to dismiss it as a pedantic footnote. :-)


Sidekiq is great here because it supports multi-threading. Multiple threads let you do concurrent work in one Ruby process (at least as long as you are not still stuck on Ruby 1.8). Without threads you’d need a separate process for each concurrent job, and that can use up memory quickly, especially with something like Rails. It is always sad to have more CPU available that you can’t use because you’re out of RAM.

In practice, threads mostly help if you are using a concurrent Ruby implementation like JRuby. MRI Ruby has a Global Interpreter Lock (GIL), which prevents two threads from executing at once. Still, even in MRI you will still see some benefit, because when one thread blocks on I/O, MRI can make progress on another. So despite the GIL, MRI can still make sure at least some thread is running.

With Sidekiq, you can say how many threads to run with the concurrency setting. Normally you’d set this in your sidekiq.yml file. Note that each thread needs its own database connection! That means if your concurrency is 10, then in your database.yml you must have a pool of 10 also (or more). Otherwise the threads will halt each other waiting to check out a database connection, and what is the point of that? They might even get timeout errors.


But wait, there’s more! We can push the concurrency up and up, and still see something like this from vmstat:

procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 1  0  92048 585840 174148 6269120    0    0     0    16  956  990  7 18 75  0  0
 1  0  92048 585212 174160 6269256    0    0     1    23 1012 1037  8 17 75  0  0
 1  0  92048 580456 174164 6269312    0    0     1    33  896  884  8 17 75  0  0

Why is the idle CPU time stuck at 75?

It turns out this is a four-core machine, and even with multiple threads, a single MRI process can only use one core, because of the GIL. That’s not what we want at all!

So the answer is to run more processes—at least one per core. You can see how many cores you’ve got by saying cat /proc/cpuinfo. If you are using something like god, it is easy to put several processes into a group and control them together, like so:

app_root = '/var/www/myapp/current'
4.times do |i|
  God.watch do |w|
    w.name     = "myapp-sidekiq-#{i}"
    w.group    = "myapp-sidekiq"
    w.log      = File.join(app_root, 'log', "#{w.name}.log")
    w.pid_file = File.join(app_root, 'current', 'tmp', 'pids', "#{w.name}.pid")
    w.start    = <<-EOS.gsub("\n", " ")
      cd '#{app_root}' &&
        bundle exec ./bin/sidekiq --environment production
                                  --pidfile '#{w.pid_file}'
                                  --logfile '#{w.log}'
    # ...

If you aren’t using god and don’t know how to do this with your own process manager, then I think Sidekiq Enterprise has a similar feature called Swarms.

Scheduling cores

That gets us almost there, but there is still a problem. You might still see idle time stuck somewhere, like (on a 4-core system) 25. The problem is that while the kernel does its best to use your whole CPU, it can still wind up putting two Sidekiq processes on the same core, and since they are such long-lived processes, they are stuck that way, competing for their shared core while another one sits idle. The kernel doesn’t know ahead of time that they are going to run for days and keep so busy.

Fortunately we can still force each process onto its own core. For that we use the taskset(1) command. When you say taskset -c 2 date, you are telling Linux to run date on core 2. (Core numbers start from zero, as you can see in /proc/cpuinfo.) So our god config would become:

w.start    = <<-EOS.gsub("\n", " ")
  cd '#{app_root}' &&
    bundle exec taskset -c #{i} ./bin/sidekiq --environment production
                                              --pidfile '#{w.pid_file}'
                                              --logfile '#{w.log}'

After that, we’ll have one process on each core. At this point, you should start experimenting with your concurrency setting, to make each core fully utilized. The right setting there will depend on how much blocking a job does, but I have seen useful numbers up to 20. Just try some things out, and watch vmstat.

Note that you don’t have to increase the connection pool size in database.yml as you add cores (just concurrency). That’s because each process is a separate thing, each with its own pool. But you do have to increase the max connections on your database server. For instance with Postgres you’d want to set max_connections in postgresql.conf. Here you need to allow enough connections for concurrency times cores (times servers), plus some more for your actual Rails app serving web requests, plus some more for anything else you’ve got going on. That can be a lot of connections! Don’t be surprised if improving your job throughput exposes a bottleneck elsewhere in your system.


Tuning Sidekiq can be complicated, because of the double layers of threads plus processes. You need a confident understanding of how Ruby and your operating system handle concurrency, and it helps to use tools like vmstat to measure what’s going on and verify your understanding. If you have a box dedicated to just Sidekiq jobs, my recommendation is to run one process per core, using taskset to keep them separate, and then tune concurrency from there. Hopefully this will help with your own projects!

Next: Postgres Permissions