pg_size_pretty

Applies to VAST Cluster 5.5.

Functions › Session and utility functions › Formatting. Writes a number of bytes for people to read, in binary units of 1,024: pg_size_pretty(1536) is '1.5 KiB'. It's a PostgreSQL-compatible name for format_bytes and gives the same results. It can also be called as pg_catalog.pg_size_pretty.

Syntax

pg_size_pretty(n)
pg_catalog.pg_size_pretty(n)

Returns: VARCHAR.

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (ADBC), and work in a schema called docs_sql_fn_utility_pg_size_pretty. Set QE_ENDPOINT, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY and VASTDB_BUCKET (SQL reference), and run the blocks in order in one Python session. Below each block is the output you should see.

This block connects, defines the run() and plan() helpers, and creates a table of five files with their sizes in bytes. The last row has no values:

import os
import re

import adbc_driver_manager
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = f'"{BUCKET}/docs_sql_fn_utility_pg_size_pretty"'

conn = dbapi.connect(
    driver=adbc_driver_vastdb.get_driver_path(),
    db_kwargs={
        "vast.db.endpoint": os.environ["QE_ENDPOINT"],
        "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
        "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
    },
    autocommit=True,
)

def server_message(e):
    """The server's message in a driver error, without the request ID or the echoed statement."""
    text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e))
    found = (re.search(r'message: "(.*?)", request_id', text, re.S)
             or re.search(r"description: (.*)\)", text, re.S))
    message = found.group(1) if found else text.strip().splitlines()[-1]
    message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S)
    message = re.split(r"\\n|\n", message)[0].replace('\\"', '"')
    return message.replace(BUCKET, "<bucket>")

def run(sql):
    """Run one statement. Print the rows it returns, or the error."""
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchall() if cur.description else []
    except (adbc_driver_manager.Error, OSError) as e:
        print(f"{type(e).__name__}: {server_message(e)}")
    else:
        print(rows)

def plan(sql):
    """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert."""
    with conn.cursor() as cur:
        cur.execute("EXPLAIN " + sql)
        steps = cur.fetchall()[0][1]
    print("\n".join(line for line in steps.splitlines() if "Rows" not in line))

TABLE = f"{SCHEMA}.files"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, name VARCHAR, size_bytes BIGINT)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'notes.txt', 1536), "
    "(2, 'video.mp4', 1000000000), "
    "(3, 'empty.log', 0), "
    "(4, 'archive.tar', 1099511627776), "
    "(5, NULL, NULL)")

[]
[]
[(5,)]

What it does

pg_size_pretty(n) divides n by 1,024 until it's below 1,024, or until it reaches PiB, and writes the result with one decimal place and the unit. Below 1,024 it writes whole bytes:

The decimal is cut, not rounded: pg_size_pretty(10239) is '9.9 KiB' (10,239 bytes is 9.999 KiB).

NULLs and edge cases

The same cases, as the cluster returns them. They use the table and helpers from Set up the examples:

run("SELECT pg_size_pretty(NULL), pg_size_pretty(-1), pg_size_pretty(-1024), pg_size_pretty(10239), pg_size_pretty(10240)")
run("SELECT pg_size_pretty(9223372036854775807), pg_size_pretty('2048'), pg_catalog.pg_size_pretty(1024)")
run(f"SELECT pg_size_pretty('abc') FROM {TABLE} LIMIT 1")
run("SELECT pg_size_pretty(1.5)")
run("SELECT pg_size_pretty(1024::HUGEINT)")

[(None, '-1 byte', '-1.0 KiB', '9.9 KiB', '10.0 KiB')]
[('8191.9 PiB', '2.0 KiB', '1.0 KiB')]
OSError: Could not convert string 'abc' to INT64
InternalError: Binder Error: No function matches the given name and argument types 'format_bytes(DECIMAL(2,1))'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'format_bytes(HUGEINT)'. You might need to add explicit type casts.

Examples

The size of each file:

run(f"SELECT id, name, pg_size_pretty(size_bytes) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'notes.txt', '1.5 KiB'), (2, 'video.mp4', '953.6 MiB'), (3, 'empty.log', '0 bytes'), (4, 'archive.tar', '1.0 TiB'), (5, None, None)]

The files of 1 GiB or more, largest first. The condition and the sort are on the raw byte count, and pg_size_pretty() only formats the rows returned:

run(f"SELECT name, pg_size_pretty(size_bytes) FROM {TABLE} WHERE size_bytes >= 1073741824 ORDER BY size_bytes DESC LIMIT 10")

[('archive.tar', '1.0 TiB')]

How it runs in VAST

The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (VAST_SCAN). VAST DataBase can evaluate a comparison of a column with a constant, but not a function of a column, so pg_size_pretty() is always computed by the Query Engine:

• In the SELECT list, the scan reads the column and the Query Engine computes pg_size_pretty() for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes pg_size_pretty() and keeps the rows that match. No rows are skipped in storage.

The plan of a WHERE condition on the formatted text shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE pg_size_pretty(size_bytes) = '1.5 KiB'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (format_bytes(size_bytes) │
│        = '1.5 KiB')       │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│         size_bytes        │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Filter and sort on the byte count, not on the text. size_bytes >= 1073741824 is a comparison of the raw column with a constant, which is pushed down to VAST DataBase, so rows that don't match never leave storage. Its plan has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE size_bytes >= 1073741824")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

• Format last. The text doesn't sort by size ('953.6 MiB' sorts after '1.0 TiB'), so call pg_size_pretty() only in the SELECT list.

Limitations and errors

• Integers up to BIGINT. A DECIMAL, DOUBLE or HUGEINT fails, and the error names format_bytes, the function pg_size_pretty calls. Cast the value to BIGINT first: pg_size_pretty(CAST(x AS BIGINT)).
• Binary units only. For units of 1,000 (kB, MB, GB), use formatReadableDecimalSize.
• One decimal place, cut. See What it does.

run("SELECT pg_size_pretty(CAST(1536.0 AS BIGINT))")
run(f"SELECT pg_size_pretty(CAST(sum(size_bytes) AS BIGINT)) FROM {TABLE}")

[('1.5 KiB',)]
[('1.0 TiB',)]

Similar functions

run("SELECT pg_size_pretty(1536), format_bytes(1536), formatReadableSize(1536), formatReadableDecimalSize(1536)")
run("SELECT pg_size_pretty(1000000000), format_bytes(1000000000), formatReadableSize(1000000000), formatReadableDecimalSize(1000000000)")

[('1.5 KiB', '1.5 KiB', '1.5 KiB', '1.5 kB')]
[('953.6 MiB', '953.6 MiB', '953.6 MiB', '1.0 GB')]

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

See also

• format_bytes and formatReadableDecimalSize.
• Numeric and math functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.