production

AdaptiveGears CLI

CLI tools for data engineering workflows

#cli#postgres#uuid#tools

Generating Time-Sortable UUIDs

UUID v4 is random. UUID v7 embeds a timestamp - records sort chronologically by ID.

uvx adaptivegears uuid           # v4 (random)
uvx adaptivegears uuid -7        # v7 (time-sortable)
uvx adaptivegears uuid -7 -n 5   # multiple

v7 is useful for primary keys where you want insertion order without a separate timestamp column.

Checking PostgreSQL Connectivity

Quick sanity check before running queries:

export PGDATABASE=mydb PGHOST=localhost PGUSER=postgres

uvx adaptivegears pg ping
# PONG

Uses libpq environment variables - same as psql.

Listing Tables with Size Statistics

What’s in this database? How big are the tables?

uvx adaptivegears pg list
# users          1,234    12.5 MB     10.4 kB
# orders       892,103   156.2 MB       184 B
# events     5,421,892     2.1 GB       412 B

Columns: table name, row count, total size, average row size.

Filter and sort:

uvx adaptivegears pg list 'page_*'           # glob pattern
uvx adaptivegears pg list -s myschema        # different schema
uvx adaptivegears pg list --min-rows 1000    # only tables with 1000+ rows
uvx adaptivegears pg list --sort size        # largest first
uvx adaptivegears pg list --json             # machine-readable

Analyzing Temporal Distribution of Records

How many records per day? Per month? Where are the gaps?

uvx adaptivegears pg histogram events created_at
# 2024-12-01    1,234
# 2024-12-02      892
# 2024-12-03    1,456

The column must be a date or timestamp type - the command validates this before querying.

Change granularity:

uvx adaptivegears pg histogram events created_at --by month
uvx adaptivegears pg histogram events created_at --by hour

Filter by date range:

uvx adaptivegears pg histogram events created_at --since 2024-01-01
uvx adaptivegears pg histogram events created_at --last 30d
uvx adaptivegears pg histogram events created_at --last 3m --bars
# cumulative percentage
uvx adaptivegears pg histogram events created_at --last 30d --cumulative

Nullable columns automatically filter out NULLs.

Generating DDL from PostgreSQL Tables

PostgreSQL has no SHOW CREATE TABLE. This reconstructs it from system catalogs:

uvx adaptivegears pg schema users
CREATE TABLE users (
    id uuid NOT NULL,
    email text NOT NULL,
    created_at timestamp with time zone DEFAULT now(),
    CONSTRAINT users_pkey PRIMARY KEY (id),
    CONSTRAINT users_email_key UNIQUE (email)
);

CREATE INDEX users_created_at_idx ON users (created_at);

Includes columns, constraints (PK, FK, UNIQUE, CHECK), and indexes. Uses format_type() and pg_get_constraintdef() internally for accurate type representation.

uvx adaptivegears pg schema users -s myschema   # different schema
uvx adaptivegears pg schema users --json        # DDL as JSON string

References