Getting a single random row, or a few rows, from a table in order to get representative data for example is a frequent need. The most common way to do this in PostgreSQL is using ORDER BY random() like:

SELECT id FROM data ORDER BY random() LIMIT 1

But when run on a large table this can be very slow because it will have to scan the entire table to find the rows. Jonathan Katz mentioned a different way to do it on Twitter, which reminded me that people keep coming up with different (and sometimes very complicated) ways of trying to solve this problem.

And while Jonathan's method (he has the super simple sample code and results up on a gist) is still about twice as fast as ORDER BY random() on my test (with his data), it comes with some problems. For example, it requires a contiguous set of id values, that have to be integers. And it still takes about a second to run on my machine with his sample of 5 million rows -- and will keep getting slower as the table grows.

And it turns out, if you don't need your row to be perfectly random, just mostly random, and can deal with some caveats, PostgreSQL has built-in functionality that does the job about 20,000 times faster than Jonathan's version and 40,000 times faster than ORDER BY random(). Enter TABLESAMPLE.

Getting a single random row, or a few rows, from a table in order to get representative data for example is a frequent need. The most common way to do this in PostgreSQL is using ORDER BY random() like:

SELECT id FROM data ORDER BY random() LIMIT 1 

But when run on a large table this can be very slow. Jonathan Katz mentioned a ...

As mentioned in my previous post, we have some interesting projects involving Hyper-V. One of them being a way to automatically verify that our Windows server backups are functional.

This post is about how we’ve set that up.

TLDR: check the script on github

Windows backup on OpenStack

Our main virtualization platform is OpenStack, which is awesome. And while we try to mostly utilize open-source solutions, there are times when that’s just not the best way to ...

TLDR: Jump to the end for the XML tags we found necessary to get this working.

We have a few projects coming up where we for different and exciting reasons need to use Hyper-V. But running Hyper-V on bare-metal would in this case take up more resources than reasonable for us, so we’re nesting it inside one of our kvm clusters.

But getting Hyper-V 2019 to actually run steadily using nested virtualization inside of qemu-kvm provided some challenges that turned ...

I want to wrap up the VPC template from the previous blog entry “Moving forward with Cloudformation templates”

What we ended up with there was a VPC with a Private and a Public Subnet in 3 Availability Zones.

Now I want to start to use the Outputs section of the template.

And when that has been introduced, I want to use Nested Stacks

Why Outputs?

When we create a VPC with the template from the last ...

Clojure is a really nice, dynamic programming language on the Java virtual machine. It gives you the expressivity of a lisp, full interop with the whole Java/Kotlin/Scala ecosystem of libraries, a battle-hardened VM, and it’s elegant.

The downside is that it runs on the JVM and has to pay a heavy cold start penalty. We can fix that, using GraalVM, and have our cake, eat it, and space-time fold it too.

We will be using a few containers, podman and GraalVM’s native-image ...

Contributing to Free Software using Open Source methodics may look like intimidating deep expert work. But it doesn’t have to be that. Most Free Software communities are friendly to newcomers, and welcome all kind of contributions

Contributing to Free Software using Open Source methodics may look like intimidating deep expert work. But it doesn’t have to be that. Most Free Software communities are friendly to newcomers, and welcome all kind of contributions.

Reporting bugs

Hitting a bug is an opportunity, not a nasty problem. When you hit a bug, it should be reported, and with a bit of luck, it may even be fixed. Reporting the bug in an open forum also makes other users find the bug, give attention to it, and they may in turn be able to help out working around or fixing it. Reporting bugs is the most basic, but still of the most valuable contributions you may do. Finding bugs are finding real problems. Reporting bugs are helping fixing them, for you, and for other users. You may not complain to your coworker on a bug unless it is reported upstream.

While reporting bugs, remember to collect as much information as possible on the issue, including logs, runtime envionment, hardware, operating system version, etc. While collecting this information, make sure you don’t send any traceable private information that may be used by rouge parties, like ip adresses, hostnames, passwords, customer details, database names, etc.

Bugs in operating system packages

Bugs in components delivered by a Linux distribution (Ubuntu, Debian, Fedora, Red Hat, SuSE, etc), should be reported through their bug reporting interface. Remember to search for the bug before posting yet another duplicate bug. Perhaps a workaround already exists.

So the next time something strange happens to your haproxy, nginx, varnish, or your firefox browser crashes or has unexpected behaviour, collect data from your logs, and open a bug report.

There are many steps to take on the path to automation. Some are easier, some not so much.

One less popular step is documentation. Yes - the documentation you haven’t written. You know what I mean.

No matter how well written your code is, there usually is a gap between today’s YOU and the future YOU, who has to work with and pick up what you leave behind.

This is usually also the reason for writing the documentation last. Better ...

All our customers have an online presence. A subset of these have higher demands when it comes to latency and reliability than others. Sometimes this is purely because of high amount of real end-user traffic - and sometimes it’s more malicious; A DDOS-attack.

In most OpenStack-configurations, you have the concept of «port security». This is a firewall enforced on the network interface of the virtual instance. It is also there to prevent a malicious self-service user from spoofing their IP ...

Scenario: I had the need for a small tool that would parse logfiles of approximately 6 million lines. To each line apply two regexps to extract a few values, and using two separate dictionaries/hashmaps (choose your poison wrt terminology) calculate how many times each capture group int he regexp shows up.

All in all, this is not going to be a very performance critical run, as it will only parse about 4 times these 6 million lines per day, so even at worst case we're talking less than 100M regexp matches per day. Piece of cake for any language. And since it's a weekend and I don't have any time when I have to deliver this finished script, I set out to prove to myself that python is going to be fast enough for this.

In the beginning of 2019 Oracle stopped releasing free-of-charge updates to their JDK, except for personal use. At the same time Oracle started requiring a subscription for Oracle JDK use in production environments. In this blog post we will look into OpenJDK as an alternative to the Oracle provided JDK and how one would migrate to it.

Now we continue improving the VPC template from my previous blog entry “Starting with Cloudformation templates”

What we ended up with there was a VPC with one subnet connected to the Internet. Or what is know in AWS lingo as a “Public Subnet”.

The goal now is a VPC with presence in tree Availability Zones with a “Public Subnet” in each, and a “Private Subnet” in each as well.

Humble beginnings

Before we go all out on tree ...

The number of great webservers on our toolbelt is constantly growing. From the venerable Apache httpd over lighttpd to nginx - and for the reverse proxy space pound, varnish and also nginx - the number just keeps growing. Caddy is a newcomer in this field, yet its features are already impressive. Lets take a brief look.

In a samba setup where users and groups are fetched from Active Directory to be used in a unix/linux environment, AD may prohibit the samba winbind tools like wbinfo to recurse into its group structure. You may get groups and users and their corresponding gids and uids, but you may not get the members of a group.

It is usually possible to do the opposite, that is, probing a user object and get the groups that user is member of. Here is a little script that collects all users, probing AD for the groups of each and every user, and sorting and putting it together. In perl of course.

https://github.com/ingvarha/groupmembers

The plan

One of Redpill Linpro’s customers - had parts of their web presence managed by another provider - “P”. The customer wanted to migrate the server operations to Redpill Linpro (“RL”), including moving their Elasticsearch cluster to Redpill Linpro’s Elasticsearch offering, preferrably without downtime. While exporting and importing by using snapshots was an option, the better option would be a live migration.

A really useful Elasticsearch feature is the capability ...

As we saw in the introduction to ActiveMQ Artemis post, in ActiveMQ Artemis the implementation is separated from the configuration and data, requiring one to create a broker instance after installation of the implementation. One of the advantages of doing this, is that it makes upgrades much easier. Lets take a detailed look at that now.

Apache ActiveMQ is a modern, open source messaging platform. For years now, the community has been working on the successor to the venerable ActiveMQ - now sometimes referred to as ActiveMQ classic.

The new project is named ActiveMQ Artemis, at least until the time that it will be re-branded as ActiveMQ 6.0. Apparently that will happen once ActiveMQ Artemis has all features of ActiveMQ, but since no one knows if and when this feature parity is achieved lets take a look at ActiveMQ Artemis - the future of the ActiveMQ project.

This is not the place to tell anyone why Infrastructure as Code is a good idea. For that I can point the potential readers to a blog by my colleague Yngve about that: Why code your infrastructure?

I a short series of blogs, I intend to demonstrate building infrastructure in AWS in steps, where I will be building upon previous entries. Basic knowledge of network and VPC is assumed.

Note that following these instructions can and will incur costs ...

I'm sure everybody is aware you can have PostgreSQL fetch data live across the internet (or locally of course) as part of your queries and use the data directly. In fact there are a large number of drivers available to access different kinds of data for download. But in the simplest case, we can also just use the file_fdw wrapper that's included in the standard PostgreSQL packages, together with everybody's http-swiss-army-knife, curl.

In attempting to adapt this post for the public, what more time-relevant dataset to work off in these pandemic-affected times than the open data provided by the ECDC, being both open data and very current. In particular for this example, they provide public datasets with COVID numbers from across the world (actual public data, requiring no registration to read, and actual data, not just a limited API).

So, let's see how we can access this data from PostgreSQL:

CREATE EXTENSION file_fdw;

CREATE SERVER curly FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE _rawdata (
 daterep text not null,
 day int not null,
 month int not null,
 year int not null,
 cases int not null,
 deaths int not null,
 countries text not null,
 geoid text not null,
 countrycode text null,
 popdata int null,
 continent text not null,
 cumulative14days float null
)
SERVER curly
OPTIONS (
 PROGRAM 'curl -s https://opendata.ecdc.europa.eu/covid19/casedistribution/csv/',
 FORMAT 'csv',
 HEADER 'on'
);

And to use this we can simply query the foreign table, which will then trigger a http GET of the data:

covid=# SELECT count(*) FROM _rawdata;
 count 
-------
 41837
(1 row)

However, there is an unfortunate interaction with LIMIT. So if we for example try to get just the first 10 rows:

covid=# SELECT * FROM _rawdata LIMIT 10;
ERROR:  program "curl -s https://opendata.ecdc.europa.eu/covid19/casedistribution/csv/" failed
DETAIL:  child process exited with exit code 23

I’m sure everybody is aware you can have PostgreSQL fetch data live across the internet (or locally of course) as part of your queries and use the data directly. In fact there are a large number of drivers available to access different kinds of data for download. But in the simplest case, we can also just use the file_fdw wrapper that’s included in the standard PostgreSQL packages, together with everybody’s http-swiss-army-knife, curl.

In attempting to adapt this ...

So, you have a partitioned table. And you want to change your mind. Re-partitioning is "easy" if you can take downtime -- just create a new table with a new name and copy all the data over. But what if we want to try to do it without downtime? Logical replication enhancements in PostgreSQL 13 brings us some new options for this!

But first a disclaimer -- this is definitely not pretty! And does not take into consideration things like foreign keys and similar. But sometimes a quick hack can be the best hack.

So let's go!