format_bytes

Applies to VAST Cluster 5.5.

Functions › Numeric and math functions › Number formatting. Writes a number of bytes for people to read, in binary units of 1,024: KiB, MiB, GiB, TiB and PiB. formatReadableSize is the same function.

Syntax

format_bytes(n)
formatReadableSize(n)

Returns: VARCHAR.

Aliases: formatReadableSize.

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_numeric_format_bytes. 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 six 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_numeric_format_bytes"'

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, 'photo.jpg', 5000000), "
    "(3, 'backup.tar', 3221225472), "
    "(4, 'empty.log', 0), "
    "(5, 'archive.parquet', 1125899906842624), "
    "(6, NULL, NULL)")

[]
[]
[(6,)]

What it does

format_bytes(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:

Two details:

• Below 1,024 it writes whole bytes: '0 bytes', '1 byte', '1023 bytes'.
• The decimal is cut, not rounded: format_bytes(1500) is '1.4 KiB' (1,500 bytes is 1.46 KiB), and format_bytes(5000000) is '4.7 MiB' (4.77 MiB). Don't compare the text with a rounded value.

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 format_bytes(NULL), format_bytes(0), format_bytes(1), format_bytes(2), format_bytes(1023), format_bytes(1024)")
run("SELECT format_bytes(1500), format_bytes(1000000), format_bytes(1152921504606846976), format_bytes(9223372036854775807)")
run("SELECT format_bytes(1536::INTEGER), format_bytes(255::UTINYINT)")
run("SELECT format_bytes(1536.0::DOUBLE)")
run("SELECT format_bytes(18446744073709551615::UBIGINT)")
run(f"SELECT format_bytes('abc') FROM {TABLE} LIMIT 1")

[(None, '0 bytes', '1 byte', '2 bytes', '1023 bytes', '1.0 KiB')]
[('1.4 KiB', '976.5 KiB', '1024.0 PiB', '8191.9 PiB')]
[('1.5 KiB', '255 bytes')]
InternalError: Binder Error: No function matches the given name and argument types 'format_bytes(DOUBLE)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'format_bytes(UBIGINT)'. You might need to add explicit type casts.
OSError: Could not convert string 'abc' to INT64

Examples

The size of each file, with both names of the function:

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

[(1, 'notes.txt', '1.5 KiB', '1.5 KiB'), (2, 'photo.jpg', '4.7 MiB', '4.7 MiB'), (3, 'backup.tar', '3.0 GiB', '3.0 GiB'), (4, 'empty.log', '0 bytes', '0 bytes'), (5, 'archive.parquet', '1.0 PiB', '1.0 PiB'), (6, None, None, None)]

The files of 1 GiB or more. The condition is on the raw byte count, and format_bytes() only formats the rows returned:

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

[(3, 'backup.tar', '3.0 GiB'), (5, 'archive.parquet', '1.0 PiB')]

The total size. sum() of a BIGINT column returns a DECIMAL, which format_bytes() doesn't take, so cast the sum to BIGINT:

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

[('1.0 PiB',)]

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 format_bytes() is always computed by the Query Engine:

• In the SELECT list, the scan reads the column and the Query Engine computes format_bytes() for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes format_bytes() 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 format_bytes(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 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. Call format_bytes() in the SELECT list of the rows you return, and sort and compare on the number.

Limitations and errors

• Integers up to BIGINT. A DECIMAL, DOUBLE or FLOAT fails with No function matches the given name and argument types, and so do UBIGINT and HUGEINT. Cast the value to BIGINT first: format_bytes(CAST(x AS BIGINT)). This includes the result of sum() on an integer column.
• Binary units only. For units of 1,000 (kB, MB, GB), use formatReadableDecimalSize.
• One decimal place, cut. See What it does.

run("SELECT format_bytes(1.5)")
run("SELECT format_bytes(CAST(1536.0 AS BIGINT))")

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.
[('1.5 KiB',)]

Similar functions

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

[('1.5 KiB', '1.5 KiB', '1.5 kB', '1.5 KiB')]
[('1.4 MiB', '1.4 MiB', '1.5 MB')]

Clean up:

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

[]
[]

See also

• Numeric and math functions: all the numeric functions.
• formatReadableDecimalSize and pg_size_pretty.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.