Common mistakes: UNION vs. UNION ALL › Cybertec – The PostgreSQL Database Company

In my role as a PostgreSQL consultant and trainer there are a couple of issues, which pop up on a regular basis.

Source: Common mistakes: UNION vs. UNION ALL › Cybertec – The PostgreSQL Database Company

Little things you’d better remember.

Faces – a flickr gallery

 

A flickr gallery I like a lot. I’m not a photographer but if I were I’d love to short portraits like these.

 

Elon Musk Debuts the Tesla Powerwall – YouTube

 

Wireless keyboard on a phone

I’m writing this post by typing on wireless keyboard into my phone. It’s the first time I connect a wireless keyboard to my iPhone 5 and I have to say it’s a pretty interesting experience. 

I’m typing directly into the WordPress app’s input field. There’s some lag, which makes it feel odd. I don’t know if it’s the bluetooth connection, if it’s the input field itself that does not expect such a fast input sequence. 

I suppose this input field is somehow translating what I type into HTML, doing some extra work at each keystroke. In a previous test I did with the Notes app the input was pretty smooth with no lag at all.

On a quick search on Amazon I saw there are some interesting models, including one which is capable to swtich quickly among up to three devices. Very nice.

Why would one use such a keyboard to type into the phone? I don’t know but I suppose that when you are on a gig and you want to be free to draft a blog post without carrying your laptop with you, such setup may be a pretty good alternative. On a tablet it makes definitely much more sense. 

Anyway the experience is positive and I’m considering buying one for myself (this is the Apple keyboard I borrowed from a friend’s home computer).

Do you ever use a wireless keyboard on a phone/tablet device? I’m curious to know about your work/entertainment setup.

My favourite light-background colorschemes for VIM

I’m addicted to Vim colorschemes. I change them very frequenty, I’m unable to settle to a favourite one. Sometimes light backgrounds, sometimes dark ones. During the last few months I’ve come up with a list of the ones I switch more frequently.

Here is a list of the light background ones, unranked. Most of them you can find among the deafult Gvim installation.

Buttercream

Selection_012

This is pretty neat and probably one of my favourites.

Autumnleaf

Selection_013

The one thing I don’t like about this one is the cursive for strings.

martin_krischik

Selection_014

Sometimes you are in the mood for background highlight.

Papayawhip

Selection_015

Sometimes you need the world to be more on the pink shades.

Fine_blue

Selection_016

Ice ice baby.

Biogoo

Selection_017

This is the one I turn on more frequently when I need  a light background which is not too bright.

Conclusion

I intentionally left out two important choices, which are Solarized and Dawn. I know they are popular, I gave them a try but they didn’t stick.

What about you? I’d like to read about your favourite ones.

Scaling Elasticsearch for Production at Verizon: 500 Billion Documents & Counting | Elastic

Elastic{ON} Video of the Week: Scaling Elasticsearch for Production at Verizon: 500 Billion Documents & Counting | Elastic.

 

Postgres essentials: window functions

Window functions in Postgres allow you to perform computations related to the set of rows being returned by your query. Imagine you can group your query by a certain column, and have computations be limited by the boundaries of that group (in other words, a window).

To better explain the concept, let’s look at a very simple example (data can be downloaded here).

Imagine we have a table with 100 records, with four columns:

  • id (primary key)
  • name
  • a performance score from 1 to 100
  • marital status field

Something like this:

Selection_542

Now let’s suppose you want to view the best performers in each marital status. First thing to do is to order the list by marital status and performance. From the following image you can see what a window is:

Selection_547

Now that we have clear what a window is, we can introduce the concept of window functions. Very easily, window functions are functions which operate on those windows of data, in other words on those sub-recordsets. We can add columns with the result of a function that takes into account only the values of other rows inside the boundaries of the window. 

We can use the rank() function to show the rank of each record in its own window. A rank is not like a row number. A ranks output an equal value for an equal input inside a given set. 
 
Let’s write this query: 
 
Selection_549
 
Which leads to the following result: 
 
Selection_550
As you can see ranking restarts when a new window starts
 
Now go back and take a look at the query. Right after the call to the rank() function we define a partition criteria and a sorting. Those two parameters are required to specify the scope, the field of action of the window function. 
 
Now let’s suppose we want to query for just the first ranked people in each marital status. It’s very easy now that we have rank column in place. Just wrap everything inside a subquery and add a where condition. 
 
Selection_001
Selection_002
 

Conclusion

Before window functions it was not that easy to get the same result. I don’t know precisely because I’m not that old. Anyway I suppose the same result was a matter of nested subqueries and similar sorcery.

There’s much more you can do with window functions. Have a look a the documentation page for the feature and for the available functions.

I hope you found this useful. Here you can download the data I used for this post, in case you want to try it yourself.

 

 

Backup files and paths to S3 with write-only keys

Lately I’ve been doing some maintenance to several servers, most of which had to be just turned off since legacy services had no longer any reason to exist.
I don’t know what about you, but for me, when it’s time to turn off a VPS, I always feel a bit anxious. Even though app repository and database are already backed up for archive, you sometimes stumble upon snowflake server configurations or application logs which are not backed up and that may be of interest in the future.

In such cases I used to backup those files locally on my laptop and then move them some where depending on the specific situation. Sometimes it was a CD or DVD, sometimes some other kind of medium. Sometimes I though it would have been tremendously useful to move those files from the server to S3 directly, in some kind of backup bucket.

Other times it was just the need to have a quick way to send a bunch of files to S3 directly, say for periodic backup of databases or filesystem snapshots.

Then I though about security issues related to keeping S3 keys on those servers. If for any reason a host was compromised, to lose control of a key that allows anyone to read everything from that bucket would be a mess. Bacukups very often hold all sorts of sensible information and the idea to have to deal with such security concern was just too much.

S3 and write only keys

I never really developed a standard procedure for that, until few days ago. In fact I though about the possibility to have write-only keys on several servers, and a kind of script to allow you to just send files to S3, with no possibility to read anything.

That sounded great to me. As part of a standard setup for every host I could configure the following:

  • a configuration file with S3 write only keys and bucket name
  • a script suitable to be used with S3 write only keys

In the beginning I considered to use a binary like s3cmd for this purpose, but I found it was not playing well with write-only keys. Then I decided to build my own script. It was actually very easy with few lines of Ruby to come up with a script which was doing just that: read a path from the command line and recursively push the tree to S3.

Sink3

Sink3 is available here on github. It’s in such an early stage that I felt a little bit uncomfortable even to write this post. But then I thought “hey! it’s working after all.”

Here is what it does:

  • it uses the hostname to create a root folder on S3
  • it creates a folder from the current date inside the hostname folder
  • it copies files or paths it receives as arguments inside the date folder

Working this way it can even be used to perform periodical backups. Example usage:

assuming a host named tiana

What you get in the bucket is:

nice hum? You don’t have to worry about anything else other than to avoid conflicts in filenames. That would overwrite what you backed up previously.

6979672865_1a7060d3cc_z

What I’ve learned from the Rubik’s Cube

Around the last year I started playing with the Rubik’s Cube. I’ve always been fascinated by that toy (I don’t know if toy is the proper word for it), but I never had the chance to play extensively with it. Maybe because I never owned one. One day I was wandering through the shelves of a toy store, I saw one and brought it home.

I started to play with it during the summer time, as a way to relax myself while doing something manual other than video games. I tried do understand how it worked and tried to come up with some reasoning on possible ways to solve. I was able to solve the first layer – yes, I thought it was a good idea to try to solve it by “layers”.

In few days I was able to solve the first layer very quickly, it’s not that hard. Then I started to think to the second layer, slightly harder, but I was able to craft my homemade recipe to make the second layer too. Sometimes it was working, sometimes not. I started to get frustrated. At a certain point I gave up. I told myself it was basically too hard for my brain to go ahead with it and I asked YouTube to show me something.

You won’t belive that, but I was surprised to find out that YouTube was actually full of videos of people showing you how to solve the Rubik’s Cube. After few search results I saw that a lot of people was talking about solving it by layers and I felt proud of myself for the little achievement of starting the approach from the right perspective.

I started to focus on a video explaining how to solve the second layer. Thinking back to those days, I remember it took me a huge amount of time to learn how to make the second layer. I found it really hard to grasp those 3D movements. I started to practice on and on with the first two layers. Solve it till the second layer, mess it up, start again. This was for a long time my favourite relax practice.

Once you solve the second layer, there are at least other four techniques to follow to solve the remaining layer:

  • the yellow cross
  • the oriented yellow cross
  • the corners in place
  • the corners oriented

Those are the ones I learned and the ones that I keep using today. I suppose there are many others, maybe faster and more complex ones, but I’m not interested in learning them for now.

The other day I was playing with it while being absorbed in other thoughts, when I realized that no matter how much I practiced, there was always a certain degree of effort I had to put in it. It was like if, while I was getting better in speed and precision of my movements and the “automation” of those movements, the amount of effort I had to put in the recognition of the “rule to apply” was somewhat constant.

I could be completely absorbed in other thoughts while doing the easy parts, for instance moving blocks around when the pattern to apply was recognized, but I had to focus a lot when I had to understand what was the next step required.

I divided the mental activity required to solve the Rubik’s Cube with those pre-learned techniques in mainly three areas:

  • Pattern recognition
  • Rule representation
  • Rule application

Pattern recognition

This requires your brain to read the colors and positions and to scan your memory in order to find a matching pattern. This is quite expensive as it seems, since I can’t completely focus on something else while doing that.

This step is completed when your brain understands at what point in the solving process you are. For instance, when you say: “the next step is to make the oriented yellow cross”. Let’s call this “high intensity”.

Rule representation

By “rule application” I mean to figure out what are the movements to be done in order to complete the rule. This is what you should have clear when you start to move the cubes to place them in the desired positions.

For instance, this step is when you say: “ok, in order to make the oriented yellow cross I have to do this series of movements”.
I’m not sure this is actually a step by itself because I admit sometimes I don’t even have to think about it. If this step exists, it’s for sure very short lived and melts into the third step. Call it “medium intensity”.

Rule application

This part is basically all mechanical. The part of your brain working on this I guess is the “cerebellum”, the one you use for all movements you already master, like walking and typing on a keyboard. During this step I mostly have to focus on the precision of movements.

It’s the Zen part, when you want to move your fingers as precise as a robot would. Like the people you see solving the cube in less than a minute. I’m not good at that, but I don’t blame myself to much.

That’s the part I enjoy the most actually, it’s the part I can do completely disconnected, thinking to whatever else. Sometimes I can even close my eyes and still make it right. Let’s say this is “zero effort, high amusement”.

After coming up with this kind of theoretical separation of scopes, I started to ask myself if similar scopes can be matched in some field of everyday life.

According to the above schema, it would make sense to think that for the human brain to recognize patterns is somewhat expensive. To think about “what to do next” is not so hard when you have a set of applicable solutions to the problem.

To apply the rule may be more or less hard depending on the kind of physical activity involved, but it’s absolutely something that can be automated and “delegated” to peripheral areas of the brain.

The conclusion is that I was able to find several examples of such scopes applied to real life activities. To talk about those would require a separate post. For now I leave you with these few thoughts. I would love to hear what you think about it.

Selection_396

HelpScout Free Plan Review

Today I’m writing about HelpScout. If you haven’t tried already, you can go there and setup a free account. You’ll be surprised of how much it resembles your inbox.

In my previous post I wrote about Desk.com. What I liked the most was the configurability of languages, assuming you are in the need for a multilingual support, and the wide range of options you have to set up your workflow. HelpScout goes in another direction.

At Exelab we were looking for a way to streamline communication with our clients. Too often it happens to receive support emails and to reply to them without carbon-copying anyone in the team. Ours is a small team, with very intense communication between us (even if we are a distributed), so using an help desk software always seemed to be an over sized approach. We know tools can stand in your way very easily, furthermore we always thought helpdesk software is perfect when you have a product, while less suitable when you are doing just consulting. Add to this the fact that most helpdesk software bill you per agent and you’ll have the full picture of why we didn’t use any of those before.

Recently we started to rearrange our internal workflow, in order to make it easier to deal with clients (and for clients to deal with us). Se we decided to give a second shot to someone of those tools. We heard good things of HelpScout and this is how it all started. We signed up for a free plan and started to play with it. To go straight to the point I think HelpScout is the best option around for our setup.

I was really impressed of who much it resembles a webmail.

image

I love the way they arranged navigation on the page. It’s simple and focuses on the important things. You can’t get lost. I don’t remember the onboarding to be invasive (I don’t remember it at all actually), yet I was able to find everything I needed very quickly.

HelpScout is a hub for your team’s email communication. It’s like being altogether using the same email account in front of the same computer, with the addition of a few useful tools: you can assign tickets to other team members, mark a conversation with four states (open, pending, closed, spam), you add tags.

You configure an email address to receive inbound messages (you need to have that email account working separately) and you are done. You start talking to your clients through HelpScout right away.

Customer records are automatically created from incoming messages. A little form allows you to create customer records if you want to write to someone who doesn’t exist yet in your database.

One thing I didn’t like about Desk.com was this step of creating customers. It’s distracting, you are presented with an endless list of input fields, of which you just need first name, last name and email. It never happened to me to input anything else in Desk.com. Of course it depends on the kind of helpdesk you are working on. The more complete the tool is, the more options you have to deal with, which makes the tool less pleasant to work with, unless it’s designed extremely well.

With HelpScout you experience the complete opposite. The options you are presented with are just those you need. This makes the whole experience smooth and effortless.

Another winning point in HelpScout is that you can edit inbound messages. When we started to use it, we setup a dedicated email account for it (support). Clients were still talking to us using our own email addresses. In order to have the conversation flowing through the new tool we decided to start forwarding messages to support and reply from there for every support email we received.

It did work so well that I think someone at HelpScout spent time thinking about this exact workflow. When you forward an email from, say, Gmail, you an this block of text at the beginning of the email body with info on the forwarding.
If you put this block of text at the first row of your message, HelpScout will use it to populate the issue. You’ll have the original sender recognized as the customer and I guess also the conversation title will have the “Fwd: ” prefix removed.
That’s it. It’s like if your customer wrote directly to HelpScout.

Edit incoming messages

One thing I appreciate very much is the possibility to edit incoming messages. Sometimes you want to create a ticket from an incoming message that includes non relevant blocks of text, or a typo.

This feature is also very useful when you need to reformat forwarded messages. It’s easy, click “edit” and edit. No warnings, no alerts. HelpScout treats you as an adult. It just assumes you know what you are doing.

Integrations

At Exelab we use HipChat. Needless to say we immediately linked HelpScout to HipChat. It sends immediate notifications and, more importantly it uses mentions! Maybe matching email addresses, I don’t know, the fact is that it knows that if a customer replied to you in HelpScout, it has to mention you in HipChat. It’s impossible to miss a conversation this way, also because very often one has email notifications active for mentions in HipChat too. One thing I’m considering to do is actually to turn down some email notification from HelpScout (which are very configurable).

HighRise

In the recent rearrangement of our workflow we also started to use HighRise (which I’ll talk about in a future post maybe). HelpScout and HighRise integration allows you to do the following:

  • save customers from HelpScout to HighRise (the opposite doesn’t seem to be possible unfortunately)
  • configure auto Bcc for yourself, to make all your messages saved to HighRise too.

Being HighRise a complete CRM solution, it’s very valuable to have the history of messages saved there too. You can for example aggregate customers in “companies” and “cases” so to have a more complete view of a conversation going on a particular project. HelpScout and HighRise seem to complete each other in this sense.

Conclusion

I wish I stared to use it before. It solves a problem with very gentle learning curve. Your inbox is not the right place to deal with support requests, unless you work alone of course. You need all the team to be able to know what’s going on in your organization. HelpScout makes it easy, and integrates smartly with the other tools you already use. Even the free plan gives you big value.

We are very focused on defining processes to scale our business and provide a better service for our clients. So far HelpScout seems to be the best fit for our needs. The free plan seems to give us all we need already. We don’t use a knowledge base yet, which is not included in the free plan anyway.I think we won’t hesitate to switch to the paid plan (they have just one) as soon as needed.

Give it a try, you won’t be disappointed.