pgit: I Imported the Linux Kernel into PostgreSQL

The server

Hetzner Dedicated "Server Auction" from their Finland datacenter (HEL1):

OS installation

Hetzner installimage with Ubuntu 24.04 LTS. Two changes from the default config: RAID 0 (SWRAIDLEVEL 0) for maximum throughput (no redundancy needed for ephemeral analysis work), and a simple partition layout:

PART /boot ext3 1024M
PART swap swap 4G
PART / ext4 all

This gives ~3.5 TB usable storage across the two 1.92 TB SSDs.

OS tuning

After booting into the installed image:

# --- Packages ---
apt update && apt upgrade -y
apt install -y \
  tmux btop htop iotop \
  cpufrequtils numactl \
  git curl wget unzip \
  build-essential \
  ufw \
  linux-tools-common linux-tools-$(uname -r)

# --- CPU governor → performance (all 48 threads) ---
for cpu in /sys/devices/system/cpu/cpu*/cpufreq/scaling_governor; do
    echo performance > "$cpu"
done
cat > /etc/default/cpufrequtils << 'EOF'
GOVERNOR="performance"
EOF
systemctl enable cpufrequtils
systemctl restart cpufrequtils

# --- Kernel mitigations off ---
sed -i 's/GRUB_CMDLINE_LINUX_DEFAULT="consoleblank=0"/GRUB_CMDLINE_LINUX_DEFAULT="consoleblank=0 mitigations=off"/' /etc/default/grub.d/hetzner.cfg
update-grub

# --- sysctl ---
cat >> /etc/sysctl.conf << 'EOF'

vm.swappiness = 1
vm.dirty_ratio = 5
vm.dirty_background_ratio = 2
kernel.numa_balancing = 1
EOF
sysctl -p

# --- Disable Transparent Huge Pages ---
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag
cat > /etc/systemd/system/disable-thp.service << 'EOF'
[Unit]
Description=Disable Transparent Huge Pages
DefaultDependencies=no
After=sysinit.target local-fs.target
Before=basic.target

[Service]
Type=oneshot
ExecStart=/bin/sh -c 'echo never > /sys/kernel/mm/transparent_hugepage/enabled && echo never > /sys/kernel/mm/transparent_hugepage/defrag'

[Install]
WantedBy=basic.target
EOF
systemctl daemon-reload
systemctl enable disable-thp

# --- noatime ---
sed -i 's|relatime|noatime|g' /etc/fstab
mount -o remount,noatime /

# --- Firewall ---
ufw default deny incoming
ufw default allow outgoing
ufw allow ssh
ufw --force enable

# --- Go 1.26.0 ---
wget https://go.dev/dl/go1.26.0.linux-amd64.tar.gz
rm -rf /usr/local/go && tar -C /usr/local -xzf go1.26.0.linux-amd64.tar.gz
rm go1.26.0.linux-amd64.tar.gz
echo 'export PATH=$PATH:/usr/local/go/bin:$HOME/go/bin' >> ~/.bashrc
source ~/.bashrc

# --- Docker ---
apt install -y docker.io
systemctl enable docker
systemctl start docker

# --- Reboot for mitigations ---
reboot

pg-xpatch container

Pulled the standard latest pg-xpatch Docker image:

docker pull ghcr.io/imgajeed76/pg-xpatch:latest

pgit version

pgit v4 with a few local changes that weren't released at the time of the import. By the time you're reading this, they should be included in the latest version, so everything here is reproducible with a normal go install. The main change is a seq ordering fix that replaces a monotonic timestamp hack with an explicit seq INTEGER NOT NULL column for commit ordering. This makes delta chain decompression significantly faster for sequential scans. Full changelist:

• db/schema.go — Added seq INTEGER NOT NULL column, order_by => 'seq' in xpatch.configure()
• db/commits.go — Added Seq field to struct, updated all INSERT/COPY statements
• cli/import.go — Populates Seq (1-indexed), removed monotonic timestamp hack
• repo/commit.go — pgit commit computes MAX(seq)+1, includes seq in INSERT
• cli/analyze.go — --timeout flag (replaces hardcoded 5min), ORDER BY seq ASC in queries
• cli/local.go — Shows actual container image instead of hardcoded DefaultImage
• container/runtime.go — New GetContainerImage() function
• Docs/text cleanup across README, sql.go, commits.go, ulid.go, config/reflect.go

pgit configuration

# --- PostgreSQL core ---
pgit config --global container.shared_buffers 64GB
pgit config --global container.effective_cache_size 400GB
pgit config --global container.work_mem 256MB
pgit config --global container.wal_buffers 512MB
pgit config --global container.max_wal_size 32GB
pgit config --global container.checkpoint_timeout 60min
pgit config --global container.max_connections 50
pgit config --global container.port 5433
pgit config --global container.shm_size 450g

# --- Parallelism (24c/48t EPYC 7401P) ---
pgit config --global container.max_worker_processes 28
pgit config --global container.max_parallel_workers 24
pgit config --global container.max_parallel_per_gather 12

# --- xpatch content cache (350 GB) ---
pgit config --global container.xpatch_cache_size_mb 358400            # 350 GB
pgit config --global container.xpatch_cache_max_entries 41000000      # 31.5M needed + 30%
pgit config --global container.xpatch_cache_max_entry_kb 16384        # 16 MB max single entry
pgit config --global container.xpatch_cache_slot_size_kb 4            # default, fine for mixed sizes
pgit config --global container.xpatch_cache_partitions 24             # one per core

# --- xpatch sub-caches ---
pgit config --global container.xpatch_group_cache_size_mb 256         # 85K groups need 4 MB (62×)
pgit config --global container.xpatch_tid_cache_size_mb 4096          # 25.8M rows need 1.1 GB (3.7×)
pgit config --global container.xpatch_seq_tid_cache_size_mb 4096      # 25.8M rows need 1.5 GB (2.7×)

# --- xpatch insert / encoding ---
pgit config --global container.xpatch_insert_cache_slots 256          # 24 workers × concurrent groups
pgit config --global container.xpatch_encode_threads 2                # ×24 workers = 48 HW threads
pgit config --global container.xpatch_warm_cache_workers 24           # one per core

# --- Import ---
pgit config --global import.workers 24

Configuration rationale

Git baseline measurements

Before importing, we cloned the kernel and measured the raw git repository:

cd /root
git clone --single-branch --branch master https://github.com/torvalds/linux.git
cd /root/linux

git rev-list --count HEAD                                              # 1,428,882
git gc --quiet && du -sb .git/objects/pack/*.pack                      # 6,213,222,259 (5.79 GB)
git cat-file --batch-all-objects --batch-check='%(objecttype) %(objectsize)' \
  | awk '{sum += $2} END {printf "%.2f GB\n", sum/1024/1024/1024}'     # 144.43 GB
time git fast-export --reencode=yes --show-original-ids master \
  > /root/linux.fastexport                                             # 17m18s, 126 GB
time git gc --aggressive --quiet && du -sb .git/objects/pack/*.pack    # 24m46s, 2,093,181,079 (1.95 GB)

The import

Started the pg-xpatch container with pgit local start (the start check times out because allocating 450 GB of shared memory takes a while, but it starts eventually). Waited for the database to be ready with docker logs pgit-local -f.

cd /root/linux-analysis
pgit init
time pgit import /root/linux --branch master --fastexport /root/linux.fastexport

Note: The second table strips PostgreSQL overhead from each component, so the rows won't sum to match the on-disk table above.

171,525 paths collapsed into 137,600 delta groups (rename/copy detection). 7.9x blob deduplication: 24.4M file refs point to only 3.1M unique content versions.

Btrfs has 7 entries in the top 30, all radiating from ctree.h. That single header is the coupling hub of the entire Btrfs filesystem.

These counts are from commit messages only, not source code. Using PostgreSQL word boundary regex (\y) to avoid false positives ("ass" matching "class", "hell" matching "shell"):

And in the source code, some gems:

Weekend warriors:

Jonathan Cameron and Christophe JAILLET do more than half their kernel work on weekends. They're hobby contributors maintaining drivers in their free time.

Single-file devotees:

Connor McAdams's entire kernel career (89 commits) is a single Creative Labs sound card driver (patch_ca0132.c). Lydia Wang (63 commits) lived in the VIA audio codec. Two separate people, Pavel Rojtberg and Cameron Gutman, independently devoted their kernel contributions exclusively to the Xbox controller driver (xpad.c).

Career journeys:

James Bottomley (SCSI maintainer) has committed from 19 different email domains over 20 years. His git log IS his resume: SteelEye → HP → Parallels → Odin → and more. Linus has 12 domains. David S. Miller has 11.

Christmas Day commits:

The kernel has never had a zero-commit Christmas in 21 years. 2008 had 157 commits on December 25th (the x86 unification frenzy). Even on the quietest Christmas (2005, 1 commit), someone was working.

The busiest single day:

November 18, 2022: 662 commits. One person (Uwe Kleine-König) did 583 of them in a mass platform_driver return type conversion. The second busiest was January 30, 2008 (640 commits): three developers sustaining 600+ commits/day for three consecutive days during the x86 unification.

Commit message culture gap:

Jeff Garzik averages 161 bytes per commit message. Filipe Manana (Btrfs) averages 1,950 bytes. A 12x gap. The entire memory management subsystem (mm/) skews verbose: its top 3 contributors are all in the top 5 longest-message writers. When your bugs are subtle and catastrophic, you explain yourself.

GPU drivers are revert magnets:

drm/amd/display (191 reverts) + drm/amdgpu (127) + drm/i915 (147) = 516 GPU reverts, nearly 10% of all reverts in the entire kernel. Display code has complex hardware interactions, power management state machines, and regression-sensitive userspace APIs.

# Storage & compression (2.1s)
pgit stats --xpatch

# Churn (2.3s)
pgit analyze churn --limit 30

# Hotspots (1.8s)
pgit analyze hotspots --depth 1 --limit 25

# Authors (34.3s)
pgit analyze authors --limit 30 --timeout 60m

# Activity (9.4s)
pgit analyze activity --period year --limit 25 --timeout 60m

# Coupling (48.3s)
pgit analyze coupling --limit 30 --timeout 60m

# Merge hierarchy (4.6s)
pgit sql "SELECT committer_name, COUNT(*) as merged,
  SUM(CASE WHEN author_name = committer_name THEN 1 ELSE 0 END) as self_authored
  FROM pgit_commits GROUP BY committer_name ORDER BY merged DESC LIMIT 15"

# Corporate contributions (5.4s)
pgit sql "SELECT
  CASE
    WHEN author_email LIKE '%%@intel.com' THEN 'Intel'
    WHEN author_email LIKE '%%@redhat.com' THEN 'Red Hat'
    WHEN author_email LIKE '%%@kernel.org' THEN 'kernel.org'
    -- ... (full domain mapping)
  END as org,
  COUNT(*) as commits, COUNT(DISTINCT author_email) as authors
  FROM pgit_commits GROUP BY org ORDER BY commits DESC"

# Fixes: tag evolution (4.5s)
pgit sql "SELECT EXTRACT(YEAR FROM authored_at)::int as year,
  SUM(CASE WHEN message ~* 'Fixes:\s+[0-9a-f]{12}' THEN 1 ELSE 0 END) as fixes_commits,
  COUNT(*) as total
  FROM pgit_commits GROUP BY year ORDER BY year"

# Most-fixed commits (4.9s)
pgit sql "SELECT SUBSTRING(message FROM 'Fixes:\s+([0-9a-f]{12})') as broken_commit,
  COUNT(*) as times_fixed
  FROM pgit_commits
  WHERE message ~* 'Fixes:\s+[0-9a-f]{12}'
  GROUP BY broken_commit ORDER BY times_fixed DESC LIMIT 10"

# Profanity with word boundaries (~5s)
pgit sql "WITH words(word) AS (VALUES ('fuck'),('shit'),('damn'),('stupid'),('crap'),('ugly'),('hack'),('workaround'))
  SELECT w.word, COUNT(*) as cnt
  FROM pgit_commits c, words w
  WHERE c.message ~* ('\y' || w.word || '\y')
  GROUP BY w.word ORDER BY cnt DESC"

# Source code search
pgit search "fuck" --path "*.c" --path "*.h"
pgit search "fuck" --path "*.c" --path "*.h" --all

# Triple reverts (5.7s)
pgit sql "SELECT author_name, authored_at::date, LEFT(message, 200)
  FROM pgit_commits
  WHERE message ILIKE 'Revert \"Revert \"Revert%%'"

# Kent Overstreet trajectory (4.1s)
pgit sql "SELECT EXTRACT(YEAR FROM authored_at)::int as year, COUNT(*)
  FROM pgit_commits WHERE author_name = 'Kent Overstreet'
  GROUP BY year ORDER BY year"

# Weekend warriors (1m 41s)
pgit sql "SELECT author_name, COUNT(*) as weekend_commits,
  (SELECT COUNT(*) FROM pgit_commits c2 WHERE c2.author_name = c.author_name) as total
  FROM pgit_commits c WHERE EXTRACT(DOW FROM authored_at) IN (0, 6)
  GROUP BY author_name ORDER BY weekend_commits DESC LIMIT 15"