Recent Posts

How to Move a Remote Server Between Tailnets (Without Losing SSH Access)

Moving a remote machine between two Tailscale networks (tailnets) is a high-stakes operation. Because Tailscale is a single-session service, logging out usually means cutting the very branch you’re sitting on.

If you’re restructuring your organization or—as in our case—migrating because an old admin left the building, here is how to perform the “Hot Swap” without getting locked out.

The Problem: The “Suicide” Command

Normally, if you SSH into a machine via Tailscale and run tailscale logout, your connection dies instantly. You can’t run the subsequent tailscale up command because you no longer have a route to the box. You’ve just effectively bricked your remote access.

read more
image from Postgres 11 Hash Partitioning Part1

Postgres 11 Hash Partitioning Part1

Goal: Explore Postgres 11’s Declarative Partitioning features

This entire post is detailed explanation of a thread in postgresql mailing list and wonderful answers by David Rowley

Why do I need partitions? I already have an index!

  • A partition is a logical unit that can be
    • drop-ed and removed from queried dataset as well as the database
    • detach-ed from the frequently queried dataset, but retained in the database for adhoc queries
    • detach-ed and attach-ed somewhere else (say, another table in same postgres instance)
  • Whenever optmizer decides to fall back to a sequential scan, you would be scanning a smaller table.

Types of partitioning - list, range, and hash

  • If your dataset needs to be partitioned by date where you can expire data easily, choose list or range. I will not go into these, because Postgres documentation itself explains this very well. It may be worthwhile to read that document first.
  • hash is special, it may be what you need if your key isn’t something you can easily enumerate, say a userid. hash will distribute the data evenly (assuming the users themselves are evenly distributed). Instead of configuring data for user1 to go into the child table partition-for-user1, we would be asking for it to be directed to partition-N where N = hash(key) % M, M being the number of partitions.
    • Do not use hash partitions if you need to expire data. For example, if you decided to use HASH(date), it wouldn’t let you archive a month’s worth of data easily. HASH(‘2018-10-01’) has no relation to HASH(‘2018-10-01’)

Annoyances with builtin HASH partitioning

  • Postgres uses a builtin hash function that isn’t one of the popular hashes typically available in programming languages.
  • If you need the ability to move partitions around (which was unique to my use case), you need to identity (from your application), which key resolves to which partition.
  • See this thread. It seems, it is worthwhile to investigate custom partitioning.

But first, let us get familiar with the builtin hash partitioning

Create main table

hyper=> create table foobar (k text, something_else int) partition by hash (k);
CREATE TABLE

Let us try to insert some data

hyper=> insert into foobar values ('Hello', 25);
ERROR:  no partition of relation "foobar" found for row
DETAIL:  Partition key of the failing row contains (k) = (Hello).

Well, we told postgres to use HASH to partition, but didn’t tell it how to resolve a hash value to a partition. Let us see what is the hash for key = ‘hello’…

read more