Meta functions

These functions provide instance-level information and table, column and partition information including metadata. They are particularly useful for learning useful information about your instance, including:

build

Arguments:

  • build() does not require arguments.

Return value:

Returns a string with the current QuestDB version and hash.

Examples:

SELECT build();
build
Build Information: QuestDB 7.3.5, JDK 17.0.7, Commit Hash 460b817b0a3705c5633619a8ef9efb5163f1569c

functions

Arguments:

  • functions() does not require arguments.

Return value:

Returns all available database functions.

Examples:

functions();
namesignaturesignature_translatedruntime_constanttype
oror(TT)or(boolean, boolean)FALSESTANDARD
andand(TT)and(boolean, boolean)FALSESTANDARD
notnot(T)not(boolean)FALSESTANDARD

query_activity

Arguments:

  • query_activity() does not require arguments.

Return value:

Returns metadata on running SQL queries, including columns such as:

  • query_id - identifier of the query that can be used with cancel query command or cancelQuery() function
  • worker_id - identifier of worker thread that initiated query processing. Note that many multithreaded queries also run on other workers
  • worker_pool - name of worker pool used to execute the query
  • username - name of user executing the query
  • query_start - timestamp of when query started
  • state_change - timestamp of latest query state change, such as a cancellation
  • state - state of running query, can be active or cancelled
  • query - text of sql query

Examples:

SELECT * FROM query_activity();
query_idworker_idworker_poolusernamequery_startstate_changestatequery
621795sharedbob2024-01-09T10:03:05.557397Z2024-01-09T10:03:05.557397activeselect * from query_activity()
577776sharedbob2024-01-09T08:58:55.988017Z2024-01-09T08:58:55.988017ZactiveSELECT symbol,approx_percentile(price, 50, 2) from trades

memory_metrics

Arguments:

  • memory_metrics() does not require arguments.

Return value:

Returns granular memory metrics.

Examples:

memory_metrics();
memory_tagbytes
TOTAL_USED142624730
RSS328609792
MMAP_DEFAULT196728
NATIVE_DEFAULT256
MMAP_O30
NATIVE_O396

reader_pool

Arguments:

  • reader_pool() does not require arguments.

Return value:

Returns information about the current state of the reader pool in QuestDB. The reader pool is a cache of table readers that are kept open to speed up subsequent reads from the same table. The returned information includes the table name, the ID of the thread that currently owns the reader, the timestamp of the last time the reader was accessed, and the current transaction ID with which the reader is associated.

Examples:

SELECT * FROM reader_pool();
table_nameowner_thread_idlast_access_timestampcurrent_txn
sensorsnull2023-12-01T19:28:14.311703Z1

writer_pool

Arguments:

  • writer_pool() does not require arguments.

Return value:

Returns information about the current state of the writer pool in QuestDB. The writer pool is a cache of table writers that are kept open to speed up subsequent writes to the same table. The returned information includes the table name, the ID of the thread that currently owns the writer, the timestamp of the last time the writer was accessed, and the reason for the ownership.

Examples:

SELECT * FROM writer_pool();
table_nameowner_thread_idlast_access_timestampownership_reason
sys.column_versions_purge_log12023-12-01T18:50:03.412468ZQuestDB system
telemetry_config12023-12-01T18:50:03.470604ZtelemetryConfig
telemetry12023-12-01T18:50:03.464501Ztelemetry
sys.telemetry_wal12023-12-01T18:50:03.467924Ztelemetry
example_tablenull2023-12-01T20:33:33.270984Znull

current database, schema, or user

current_database(), current_schema(), and current_user() are standard SQL functions that return information about the current database, schema, schemas, and user, respectively.

-- Get the current database
SELECT current_database();

-- Get the current schema
SELECT current_schema();

-- Get the current user
SELECT current_user();

Each of these functions returns a single value, so you can use them in a SELECT statement without any arguments.

tables

tables() or all_tables() returns all tables in the database including table metadata.

Arguments:

  • tables() does not require arguments.

Return value:

Returns a table.

Examples:

List all tables
tables();
idnamedesignatedTimestamppartitionBymaxUncommittedRowso3MaxLagwalEnableddirectoryNamededup
1my_tabletsDAY50000030000000 0falsemy_tablefalse
2device_datanullNONE1000030000000falsedevice_datafalse
3short_livednullHOUR1000030000000falseshort_lived (->)false
All tables in reverse alphabetical order
tables() ORDER BY name DESC;
idnamedesignatedTimestamppartitionBymaxUncommittedRowso3MaxLagwalEnableddirectoryNamededup
2device_datanullNONE1000030000000falsedevice_datafalse
1my_tabletsDAY500000300000000falsemy_tablefalse
3short_livedtsHOUR1000030000000falseshort_lived (->)false
note

(->) means the table was created using the IN VOLUME clause.

All tables with a daily partitioning strategy
tables() WHERE partitionBy = 'DAY'
idnamedesignatedTimestamppartitionBymaxUncommittedRowswalEnableddirectoryNamededup
1my_tabletsDAY500000truemy_tablefalse

wal_tables

wal_tables() returns the WAL status for all WAL tables in the database.

Arguments:

  • wal_tables() does not require arguments.

Return value:

Returns a table including the following information:

  • name - table name
  • suspended - suspended status flag
  • writerTxn - the last committed transaction in TableWriter
  • writerLagTxnCount - the number of transactions that are kept invisible when writing to the table; these transactions will be eventually moved to the table data and become visible for readers
  • sequencerTxn - the last committed transaction in the sequencer

Examples:

List all tables
wal_tables();
namesuspendedwriterTxnwriterLagTxnCountsequencerTxn
sensor_walfalse214
weather_walfalse303
test_waltrue719

table_columns

table_columns('tableName') returns the schema of a table.

Arguments:

  • tableName is the name of an existing table as a string.

Return value:

Returns a table with the following columns:

  • column - name of the available columns in the table
  • type - type of the column
  • indexed - if indexing is applied to this column
  • indexBlockCapacity - how many row IDs to store in a single storage block on disk
  • symbolCached - whether this symbol column is cached
  • symbolCapacity - how many distinct values this column of symbol type is expected to have
  • designated - if this is set as the designated timestamp column for this table
  • upsertKey - if this column is a part of UPSERT KEYS list for table deduplication

For more details on the meaning and use of these values, see the CREATE TABLE documentation.

Examples:

Get all columns in a table
table_columns('my_table');
columntypeindexedindexBlockCapacitysymbolCachedsymbolCapacitydesignatedupsertKey
symbSYMBOLtrue1048576false256falsefalse
priceDOUBLEfalse0false0falsefalse
tsTIMESTAMPfalse0false0truefalse
sVARCHARfalse0false0falsefalse
Get designated timestamp column
SELECT column, type, designated FROM table_columns('my_table') WHERE designated = true;
columntypedesignated
tsTIMESTAMPtrue
Get the count of column types
SELECT type, count() FROM table_columns('my_table');
typecount
SYMBOL1
DOUBLE1
TIMESTAMP1
VARCHAR1

table_partitions

table_partitions('tableName') returns information for the partitions of a table with the option to filter the partitions.

Arguments:

  • tableName is the name of an existing table as a string.

Return value:

Returns a table with the following columns:

  • index - INTEGER, index of the partition (NaN when the partition is not attached)
  • partitionBy - STRING, one of NONE, HOUR, DAY, WEEK, MONTH and YEAR
  • name - STRING, name of the partition, e.g. 2023-03-14, 2023-03-14.detached, 2023-03-14.attachable
  • minTimestamp - LONG, min timestamp of the partition (NaN when the table is not partitioned)
  • maxTimestamp - LONG, max timestamp of the partition (NaN when the table is not partitioned)
  • numRows - LONG, number of rows in the partition
  • diskSize - LONG, size of the partition in bytes
  • diskSizeHuman - STRING, size of the partition meant for humans to read (same output as function size_pretty)
  • readOnly - BOOLEAN, true if the partition is attached via soft link
  • active - BOOLEAN, true if the partition is the last partition, and whether we are writing to it (at least one record)
  • attached - BOOLEAN, true if the partition is attached
  • detached - BOOLEAN, true if the partition is detached (name of the partition will contain the .detached extension)
  • attachable - BOOLEAN, true if the partition is detached and can be attached (name of the partition will contain the .attachable extension)

Examples:

Create table my_table
CREATE TABLE my_table AS (
SELECT
rnd_symbol('EURO', 'USD', 'OTHER') symbol,
rnd_double() * 50.0 price,
rnd_double() * 20.0 amount,
to_timestamp('2023-01-01', 'yyyy-MM-dd') + x * 6 * 3600 * 100000L timestamp
FROM long_sequence(700)
), INDEX(symbol capacity 32) TIMESTAMP(timestamp) PARTITION BY WEEK;
Get all partitions from my_table
table_partitions('my_table');
indexpartitionBynameminTimestampmaxTimestampnumRowsdiskSizediskSizeHumanreadOnlyactiveattacheddetachedattachable
0WEEK2022-W522023-01-01 00:36:00.02023-01-01 23:24:00.0399830496.0 KiBfalsefalsetruefalsefalse
1WEEK2023-W012023-01-02 00:00:00.02023-01-08 23:24:00.02809830496.0 KiBfalsefalsetruefalsefalse
2WEEK2023-W022023-01-09 00:00:00.02023-01-15 23:24:00.02809830496.0 KiBfalsefalsetruefalsefalse
3WEEK2023-W032023-01-16 00:00:00.02023-01-18 12:00:00.01018390246480.0 MiBfalsetruetruefalsefalse
Get size of a table in disk
SELECT size_pretty(sum(diskSize)) FROM table_partitions('my_table');
size_pretty
80.3 MB
Get active partition of a table
SELECT * FROM table_partitions('my_table') WHERE active = true
indexpartitionBynameminTimestampmaxTimestampnumRowsdiskSizediskSizeHumanreadOnlyactiveattacheddetachedattachable
3WEEK2023-W032023-01-16 00:00:00.02023-01-18 12:00:00.01018390246480.0 MiBfalsetruetruefalsefalse

version/pg_catalog.version

version() or pg_catalog.version() returns the supported version of the PostgreSQL Wire Protocol.

Arguments:

  • version() or pg_catalog.version() does not require arguments.

Return value:

Returns string.

Examples:


SELECT version();

--The above equals to:

SELECT pg_catalog.version();
version
PostgreSQL 12.3, compiled by Visual C++ build 1914, 64-bit, QuestDB