Artificial Intelligence Database Checklists (for Bash-first Linux Ops)

You finally shipped your AI feature. The first user query hits your vector search, and… the database lags, caches churn, backups weren’t configured, and no one can explain the slowdown. Sound familiar? The data layer is the quiet workhorse of AI systems—and it deserves a repeatable, Bash-friendly checklist.

This article gives you practical, command-line-first checklists to stand up, tune, and operate databases for AI workloads (embeddings, feature stores, metadata, and monitoring). You’ll get the “why,” then scripts and steps you can copy-paste. We’ll use PostgreSQL + pgvector for examples (works great for many production teams), with portable Linux installation commands for apt, dnf, and zypper.

Why an “AI Database Checklist” is essential

• AI access patterns are different: large batch writes for embeddings, read-heavy approximate nearest-neighbor queries, and latency-sensitive retrieval-augmented generation (RAG).

• Capacity is spiky: training/embedding jobs burst I/O and memory; inference can be steady but high-QPS.

• Reproducibility and drift: models, features, and embeddings evolve—schema/versioning must be traceable.

• Reliability and compliance: backups, restore drills, and access controls must be baked in from day one.

The following checklists help you deploy quickly and safely, with Bash commands you can automate.

---

1) Install and baseline your stack (PostgreSQL + pgvector + essentials)

We’ll install:

• PostgreSQL (database)

• pgvector (vector search extension)

• Utilities: curl, jq, fio (for disk baselines), sysstat (for CPU/I/O), git, build tools

Installation – Ubuntu/Debian (apt):

sudo apt update
sudo apt install -y postgresql postgresql-contrib \
  postgresql-server-dev-all \
  curl jq fio sysstat git build-essential
# Start/enable PostgreSQL (usually auto-starts)
sudo systemctl enable --now postgresql

Installation – Fedora/RHEL/CentOS (dnf):

sudo dnf install -y postgresql-server postgresql-contrib \
  postgresql-devel \
  curl jq fio sysstat git gcc make
# Initialize and start PostgreSQL
sudo postgresql-setup --initdb
sudo systemctl enable --now postgresql

Installation – openSUSE/SLE (zypper):

sudo zypper install -y postgresql-server postgresql-contrib \
  postgresql-devel \
  curl jq fio sysstat git gcc make
# Initialize and start PostgreSQL
sudo -u postgres initdb -D /var/lib/pgsql/data
sudo systemctl enable --now postgresql

Install pgvector from source (portable across distros):

# Ensure pg_config is in PATH (often /usr/bin/pg_config)
pg_config --version

# Build and install pgvector
git clone https://github.com/pgvector/pgvector.git
cd pgvector
make
sudo make install

Create database, enable extension, and a test user:

sudo -u postgres psql -v ON_ERROR_STOP=1 <<'SQL'
CREATE ROLE aiuser WITH LOGIN PASSWORD 'change-me' NOSUPERUSER NOCREATEDB NOCREATEROLE;
CREATE DATABASE aidb OWNER aiuser;
\c aidb
CREATE EXTENSION IF NOT EXISTS vector;
SELECT extversion FROM pg_extension WHERE extname='vector';
SQL

Baseline your disk and system I/O:

# Quick disk read test (non-destructive)
fio --name=readtest --filename=/tmp/fio.dat --size=1G --bs=256k --rw=read --direct=1 --iodepth=32

# CPU & I/O activity (enable sysstat service on some distros)
mpstat 1 5
iostat -xz 1 5

GPU presence (if you’ll embed on the DB host—often not recommended, but good for checks):

lspci | egrep -i 'nvidia|amd|tesla|radeon'
command -v nvidia-smi && nvidia-smi || echo "No NVIDIA driver"
command -v rocm-smi && rocm-smi || echo "No ROCm driver"

Why this matters:

• pgvector brings vector indexing and similarity search into a mature RDBMS environment.

• fio and sysstat establish a baseline—so you can detect regressions after schema/index changes.

---

2) Schema and versioning checklist for embeddings and features

Store embeddings with explicit dimensions and simple metadata. Keep schema and index creation scripted and repeatable.

Create an embeddings table and index:

# Set dimension (match your model, e.g., OpenAI text-embedding-3-small => 1536)
DIM=1536

sudo -u postgres psql aidb <<SQL
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE IF NOT EXISTS doc_embeddings (
  id BIGSERIAL PRIMARY KEY,
  doc_id TEXT NOT NULL,
  chunk_idx INT NOT NULL,
  embedding vector($DIM) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- IVF index for scalable ANN search (tune lists based on data size)
CREATE INDEX IF NOT EXISTS idx_doc_embeddings_ann
  ON doc_embeddings
  USING ivfflat (embedding vector_l2_ops)
  WITH (lists = 200);

-- For exact search sanity testing
CREATE INDEX IF NOT EXISTS idx_doc_embeddings_l2
  ON doc_embeddings USING hnsw (embedding vector_l2_ops)
  WITH (m = 16, ef_construction = 64);
SQL

Version your schema:

# Store a schema “release” tag and Git commit hash for traceability
REL=v1.0.0
GIT=$(git rev-parse --short HEAD 2>/dev/null || echo "no-git")

sudo -u postgres psql aidb <<SQL
CREATE TABLE IF NOT EXISTS schema_releases (
  id BIGSERIAL PRIMARY KEY,
  release TEXT NOT NULL,
  git_sha TEXT,
  applied_at TIMESTAMPTZ DEFAULT now()
);
INSERT INTO schema_releases(release, git_sha)
SELECT '$REL', '$GIT'
WHERE NOT EXISTS (SELECT 1 FROM schema_releases WHERE release='$REL');
SQL

Why this matters:

• Embedding dimension mismatches cause subtle runtime errors; set it once and enforce it.

• ANN index types (ivfflat, hnsw) need deliberate tuning and reindexing as data scales.

• Schema versioning helps you debug drift between environments.

---

3) Performance and capacity checklist

Tune PostgreSQL for mixed OLTP + ANN workloads. Use ALTER SYSTEM for quick, scriptable changes, then reload.

Minimal starter tuning (adjust to your RAM/CPU):

sudo -u postgres psql -v ON_ERROR_STOP=1 <<'SQL'
ALTER SYSTEM SET shared_buffers = '2GB';
ALTER SYSTEM SET effective_cache_size = '6GB';
ALTER SYSTEM SET work_mem = '64MB';
ALTER SYSTEM SET maintenance_work_mem = '512MB';
ALTER SYSTEM SET wal_level = 'replica';
ALTER SYSTEM SET max_wal_size = '4GB';
ALTER SYSTEM SET checkpoint_timeout = '15min';
ALTER SYSTEM SET synchronous_commit = 'off';  -- if you can accept minimal risk for speed
ALTER SYSTEM SET autovacuum_naptime = '10s';
ALTER SYSTEM SET track_io_timing = 'on';

-- Helpful for analytics and query visibility
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
ALTER SYSTEM SET pg_stat_statements.max = 10000;
ALTER SYSTEM SET pg_stat_statements.track = 'all';
SQL

sudo systemctl restart postgresql

Per-session ANN query tuning and testing:

# Probe count for ivfflat (higher=better recall, more CPU)
sudo -u postgres psql aidb <<'SQL'
SET ivfflat.probes = 10;

EXPLAIN ANALYZE
SELECT doc_id, chunk_idx
FROM doc_embeddings
ORDER BY embedding <-> '[0.1, 0.2, ...]'::vector
LIMIT 5;
SQL

Replace the vector literal with a real embedding. Check rows/loops, index usage, and latency.

OS-level checks that impact DB performance:

# NUMA/THP status (THP can hurt latency-sensitive DBs)
cat /sys/kernel/mm/transparent_hugepage/enabled || true
cat /sys/kernel/mm/transparent_hugepage/defrag || true

# Open files and process limits (ensure high enough)
ulimit -n
cat /proc/sys/fs/file-max

Why this matters:

• ANN performance depends on index params (lists, probes, ef), correct operator class, and memory.

• PostgreSQL throughput benefits from sufficient shared buffers and correct WAL/checkpoint sizing.

• OS features like THP can increase latency jitter.

---

4) Reliability, backups, and restore drills

Automate logical backups. Keep at least one recent restore test.

Install client tools (if not present):

• apt/dnf/zypper steps already covered above with PostgreSQL packages.

Create a backup script and retention:

sudo install -d -m 750 -o postgres -g postgres /var/backups/pg
sudo tee /usr/local/bin/pg_backup.sh >/dev/null <<'SH'
#!/usr/bin/env bash
set -euo pipefail
TS=$(date +%Y%m%d-%H%M%S)
OUT=/var/backups/pg/aidb-$TS.sql.gz
sudo -u postgres pg_dump --no-owner --no-privileges aidb | gzip -9 > "$OUT"
# keep last 14 backups
ls -1t /var/backups/pg/aidb-*.sql.gz | tail -n +15 | xargs -r rm -f
echo "Wrote $OUT"
SH
sudo chmod +x /usr/local/bin/pg_backup.sh

Systemd timer (portable, avoids cron differences):

sudo tee /etc/systemd/system/pg-backup.service >/dev/null <<'UNIT'
[Unit]
Description=Backup aidb (pg_dump)

[Service]
Type=oneshot
ExecStart=/usr/local/bin/pg_backup.sh
UNIT

sudo tee /etc/systemd/system/pg-backup.timer >/dev/null <<'UNIT'
[Unit]
Description=Run aidb backup daily

[Timer]
OnCalendar=daily
Persistent=true

[Install]
WantedBy=timers.target
UNIT

sudo systemctl daemon-reload
sudo systemctl enable --now pg-backup.timer
systemctl list-timers --all | grep pg-backup

Restore drill (validate your backups):

# Create a scratch DB and restore latest backup
LATEST=$(ls -1t /var/backups/pg/aidb-*.sql.gz | head -n1)
sudo -u postgres psql -c "DROP DATABASE IF EXISTS aidb_restore;"
sudo -u postgres psql -c "CREATE DATABASE aidb_restore OWNER aiuser;"
gunzip -c "$LATEST" | sudo -u postgres psql aidb_restore

Why this matters:

• Backups you haven’t restored are just files you hope work.

• Daily logical dumps are simple; for large deployments, add WAL archiving and replicas.

---

5) Observability and drift checks

Measure query hot spots, storage growth, and embedding quality trends.

Enable and query pg_stat_statements:

sudo -u postgres psql aidb <<'SQL'
SELECT query, calls, total_time, mean_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;
SQL

Track table/index bloat and growth:

sudo -u postgres psql aidb <<'SQL'
-- Top relations by size
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;
SQL

Lightweight OS telemetry:

# 1-second samples for 30s
mpstat 1 30
iostat -xz 1 30
vmstat 1 30

Simple embedding drift signal (row counts by day, average chunk size proxy):

sudo -u postgres psql aidb <<'SQL'
SELECT date_trunc('day', created_at) AS day, count(*) AS rows
FROM doc_embeddings
GROUP BY 1 ORDER BY 1 DESC LIMIT 14;
SQL

If you store additional stats (e.g., embedding norms or feature summaries), compute rolling means/percentiles and alert on deltas.

Why this matters:

• Slow queries and index misuse show up quickly in pg_stat_statements.

• Storage growth alerts you when reindexing, vacuum tuning, or partitioning is due.

• Drift signals help you catch upstream embedding/model changes before latency explodes.

---

Real-world example: loading and querying embeddings

Bulk load embeddings (CSV with id,chunk_idx,embedding_json):

# Example loader: transform JSON array to pgvector literal
INPUT=embeddings.csv
sudo -u postgres psql aidb -v ON_ERROR_STOP=1 <<'SQL'
CREATE TEMP TABLE stage (doc_id text, chunk_idx int, emb_json jsonb);
SQL

awk -F',' '{print $1","$2",\"" $3 "\""}' "$INPUT" | \
  sudo -u postgres psql aidb -c "COPY stage FROM STDIN WITH (FORMAT csv);"

sudo -u postgres psql aidb <<'SQL'
INSERT INTO doc_embeddings(doc_id, chunk_idx, embedding)
SELECT doc_id, chunk_idx, ('[' || array_to_string(ARRAY(
  SELECT jsonb_array_elements_text(emb_json)::text
), ',') || ']')::vector
FROM stage;
SQL

ANN search with tuned probes:

sudo -u postgres psql aidb <<'SQL'
SET ivfflat.probes = 20;

-- Replace with a real query vector
WITH q AS (
  SELECT '[0.01,0.02,...]'::vector AS v
)
SELECT d.doc_id, d.chunk_idx, d.embedding <-> q.v AS distance
FROM doc_embeddings d, q
ORDER BY d.embedding <-> q.v
LIMIT 5;
SQL

---

Conclusion and next steps (CTA)

A solid AI database isn’t magic—it’s checklists and scripts:

• Install and baseline with PostgreSQL + pgvector and sysstat/fio.

• Enforce schema/versioning for embedding dimensions and indexes.

• Tune PostgreSQL and OS for ANN workloads.

• Automate backups and practice restores.

• Watch performance and drift with simple, actionable queries.

Your next step: 1) Run the install/baseline commands on a staging host. 2) Create the embeddings table and indexes; load a small sample. 3) Benchmark with fio and pg_stat_statements; tune probes/lists. 4) Wire up the backup timer and perform a restore drill.

If you want a production-ready, one-shot bootstrap, wrap these steps into a Bash script and commit it in your repo alongside your model and app code. Reproducibility wins outages.