Today I Learned

A Hashrocket project

15 posts by jackchristensen

Compute group proportions of a whole in SQL

It’s easy with SQL to use aggregates to count members of a group. But what if you also want the proportion that group is of the whole. The answer is to use a window function combined with the aggregate. The window function is evaluated after the normal aggregation. i.e. it can reference the values of other post-aggregation rows.

select type, count(*), count(*)::numeric / sum(count(*)) over ()
from pets
group by type;
  type  | count |        ?column?        
--------+-------+------------------------
 dog    |    13 | 0.52000000000000000000
 rabbit |     3 | 0.12000000000000000000
 cat    |     9 | 0.36000000000000000000

New PostgreSQL 9.6 slice syntax

PostgreSQL arrays support slice operations.

jack=# select ('{a,b,c,d,e,f}'::text[])[2:4];
  text
---------
 {b,c,d}

New in PostgreSQL 9.6 is slice syntax with implicit lower and upper bounds.

jack=# select ('{a,b,c,d,e,f}'::text[])[:4];
   text
-----------
 {a,b,c,d}
jack=# select ('{a,b,c,d,e,f}'::text[])[3:];
   text
-----------
 {c,d,e,f}

Previously, array_lower and array_upper was needed to slice from a particular index to the bounds of an array.

Quick Garbage Collector Stats from Go Programs

Set the GODEBUG=gctrace=1 in the environment to get log output from a Go program whenever the garbage collector runs.

Example:

gc 1 @10.282s 0%: 0.12+0.17+0.10 ms clock, 0.38+0/0.053/0.19+0.30 ms cpu, 4->4->0 MB, 5 MB goal, 4 P

The clock and cpu sections give us details on how long the GC cycle took. In this case we can see that it took well under 1ms.

See https://golang.org/pkg/runtime/ for details on what the output means.

Intel Speedstep and Ubuntu 14.04 Performance

Intel Speedstep works with the OS to adjust the clock speed of the CPU in real-time to save power. Older Linux kernels had a poor interaction with Speedstep that could cause the CPU to be downclocked even when running something demanding like a test suite. This can be fixed by disabling Speedstep in BIOS or upgrading the kernel. I was on kernel 3.13 and upgraded to 4.2. I saw 15-40% speed increase when running these tests.

Ubuntu 14.04.4 ships with a new kernel, but older installs of 14.04 will not be automatically upgraded. Run uname -a to see what kernel you are running. If it is not at least 4.2, then you may want to upgrade your kernel 1. Using aptitude this is simple as:

sudo aptitude install linux-generic-lts-wily

1 Upgrading the kernel in this way should be safe, but changing the kernel always has the potential to break something, so proceed at your own risk.

Render 64-bit integers as strings in JSON

When rendering a server-side object containing 64-bit integers, precision can be lost if it is rendered as a number. For example, if a JSON document contains the number 9219976635399687875, JavaScript will parse it as 9219976635399688000. This is caused by JavaScript only having floating point numbers. To ensure very large numbers are not rounded, render the value out as a string, e.g. “9219976635399687875”.