snapshot_id

BIGINT

Primary key

The snapshot ID.

It is referenced by most metadata tables. The value increases by 1 for each created snapshot

snapshot_time

TIMESTAMP

NOT NULL

The snapshot creation date and time

schema_version

BIGINT

NOT NULL

The metadata catalog version.

The value increases by 1 each time the catalog metadata changes, for example, when a new analytical table is created.

This allows caching the catalog metadata

next_catalog_id

BIGINT

NOT NULL

The ID assigned to the next metadata catalog object.

The value increases by 1 each time a catalog object is created, for example, when a new analytical table is created

next_file_id

BIGINT

NOT NULL

The ID assigned to the next Parquet file.

The value increases by 1 each time a Parquet file is created or deleted

is_visible

BOOLEAN

NOT NULL

Specifies whether metadata catalog objects associated with the snapshot are visible.

When a stored procedure, such as metastore.add_table, creates multiple catalog objects associated with the same snapshot, the is_visible field is set to false. Catalog objects become visible only after the operation completes successfully, and the is_visible field is set to true.

You can use the is_visible field to temporarily hide a catalog object without deleting it and make it visible again later

snapshot_id

BIGINT

Primary key, foreign key

The ID of the snapshot from the pga_snapshot metadata table

changes_made

VARCHAR

-

A comma-separated list of snapshot changes in the following formats:

schema_id

BIGINT

Primary key

The analytical schema ID assigned by increasing the next_catalog_id field of the pga_snapshot metadata table

schema_uuid

UUID

NOT NULL, UNIQUE

The persistent analytical schema ID stored for compatibility with Postgres Pro schemas

begin_snapshot

BIGINT

Foreign key, NOT NULL

The snapshot associated with creating the analytical schema, from the pga_snapshot metadata table

end_snapshot

BIGINT

Foreign key

The snapshot associated with deleting the analytical schema, from the pga_snapshot metadata table.

If the value is NULL, the analytical schema is not deleted

schema_name

VARCHAR

NOT NULL, UNIQUE

The unique analytical schema name (e.g., my_schema)

proxy_table_id

BIGINT

NOT NULL, UNIQUE

The ID of the proxy table associated with the analytical schema, from the pga_proxy_table metadata table

table_id

BIGINT

Primary key

The analytical table ID assigned by increasing the next_catalog_id field of the pga_snapshot metadata table

table_uuid

UUID

NOT NULL, UNIQUE

The persistent analytical table ID stored for compatibility with heap tables

begin_snapshot

BIGINT

Foreign key, NOT NULL

The snapshot associated with creating the analytical table, from the pga_snapshot metadata table

end_snapshot

BIGINT

Foreign key

The snapshot associated with deleting the analytical table, from the pga_snapshot metadata table.

If the value is NULL, the analytical table is not deleted

schema_id

BIGINT

NOT NULL, UNIQUE

The ID of the analytical schema that contains the analytical table, from the pga_schema metadata table

table_name

VARCHAR

NOT NULL

The analytical table name (e.g., my_table).

The table_name fields are not UNIQUE because pgpro_metastore does not delete the metadata of analytical tables. Deleted analytical tables are associated with the end_snapshot fields to indicate that these tables can no longer be used.

This allows you to create a new analytical table with the same name as a previously deleted table

path

VARCHAR

NOT NULL, UNIQUE

The path to the storage directory with Parquet files of the analytical table relative to the uri field of the pga_storage metadata table.

The absolute path is generated by concatenating the uri field of the pga_storage metadata table and the path field of the pga_table metadata table

storage_id

BIGINT

Foreign key, NOT NULL

The ID of the storage that contains the analytical table, from the pga_storage metadata table

view_sql

VARCHAR

-

The CREATE VIEW command used to create a Postgres Pro view for the analytical table

was_evolution

BOOLEAN

NOT NULL

Specifies whether the metadata of the analytical table was updated using the following stored procedures:

This information is used by the metastore.create_view stored procedure

proxy_table_id

BIGINT

NOT NULL, UNIQUE

The ID of the proxy table associated with the analytical table, from the pga_proxy_table metadata table

storage_id

BIGINT

Primary key

The storage ID assigned by increasing the next_catalog_id field of the pga_snapshot metadata table

begin_snapshot

BIGINT

Foreign key, NOT NULL

The snapshot associated with creating the storage, from the pga_snapshot metadata table

end_snapshot

BIGINT

Foreign key

The snapshot associated with deleting the storage, from the pga_snapshot metadata table.

If the value is NULL, the storage is not deleted

storage_name

VARCHAR

NOT NULL

The storage name (e.g., my_storage).

The storage_name fields are not UNIQUE because pgpro_metastore does not delete the metadata of storages. Deleted storages are associated with the end_snapshot fields to indicate that these storages can no longer be used.

This allows you to create a new storage with the same name as a previously deleted storage

uri

VARCHAR

NOT NULL, UNIQUE

The URI of the root storage directory

Local storages have simple URIs (e.g., file:///home/j.doe/workspace/axe/).

S3 storages have URIs with more complicated structures and parsing (e.g., s3://premdb/team01.csv?versionId=w_B5qT8s5MkiT09.IRHay0lW.PycsHTS). These URIs are generated using third-party libraries.

tmp_uri

VARCHAR

NOT NULL, UNIQUE

The URI of the storage directory for temporary files

Local storages have simple URIs (e.g., file:///home/j.doe/workspace/axe/).

S3 storages have URIs with more complicated structures and parsing (e.g., s3://premdb/team01.csv?versionId=w_B5qT8s5MkiT09.IRHay0lW.PycsHTS). These URIs are generated using third-party libraries.

storage_type

BIGINT

NOT NULL

The storage type.

Local, NFS, and S3 storages are currently supported

min_space

BIGINT

NOT NULL

The minimum free space for the storage, in bytes.

It is verified when executing the metastore.add_storage stored procedure or any stored procedure that requires removing temporary files from the directory specified by the tmp_uri field.

Default value: 10 Gb

uri_id

BIGINT

Foreign key, NOT NULL, UNIQUE

The ID of the URI associated with the root storage directory, from the pga_uri metadata table

tmp_uri_id

BIGINT

Foreign key, NOT NULL, UNIQUE

The ID of the URI associated with the storage directory for temporary files, from the pga_uri metadata table

proxy_table_id

BIGINT

NOT NULL, UNIQUE

The ID of the proxy table associated with the storage, from the pga_proxy_table metadata table

folder_id

BIGINT

Primary key

The shared directory ID assigned by increasing the next_catalog_id field of the pga_snapshot metadata table

begin_snapshot

BIGINT

Foreign key, NOT NULL

The snapshot associated with creating the shared directory, from the pga_snapshot metadata table

end_snapshot

BIGINT

Foreign key

The snapshot associated with deleting the shared directory, from the pga_snapshot metadata table.

If the value is NULL, the shared directory is not deleted

path

VARCHAR

NOT NULL

The path to the shared directory relative to the root storage directory.

The absolute path is generated by concatenating the uri field of the pga_storage metadata table and the path field of the pga_folder metadata table

folder_name

VARCHAR

NOT NULL

The shared directory name (e.g., my_directory).

The folder_name fields are not UNIQUE because pgpro_metastore does not delete the metadata of shared directories. Deleted directories are associated with the end_snapshot fields to indicate that these directories can no longer be used.

This allows you to create a new shared directory with the same name as a previously deleted directory

storage_id

BIGINT

Foreign key, NOT NULL

The ID of the storage that contains the shared directory, from the pga_storage metadata table

proxy_table_id

BIGINT

NOT NULL, UNIQUE

The ID of the proxy table associated with the shared directory, from the pga_proxy_table metadata table

column_id

BIGINT

Primary key

The column ID.

If the initial Parquet file contains the field_id field, it is used as the column_id field. The ID is not updated until the column is deleted.

The column_id fields must be unique

begin_snapshot

BIGINT

Foreign key, NOT NULL

The snapshot associated with creating the column, from the pga_snapshot metadata table

end_snapshot

BIGINT

Foreign key

The snapshot associated with deleting the column, from the pga_snapshot metadata table.

If the value is NULL, the column is not deleted

table_id

BIGINT

Foreign key, NOT NULL

The ID of the analytical table that contains the column, from the pga_table metadata table

column_order

BIGINT

NOT NULL

The sequence number of the column.

The column_order fields must be unique within the analytical table, but the ordered sequence can have gaps

column_name

VARCHAR

NOT NULL

The column name (e.g., my_column).

The column_name fields are not UNIQUE because pgpro_metastore does not delete the metadata of columns. Deleted columns are associated with the end_snapshot fields to indicate that these columns can no longer be used.

This allows you to create a new column with the same name as a previously deleted column

column_type

VARCHAR

NOT NULL

The column type corresponding to one of the supported pgpro_metastore data types

initial_default

VARCHAR

-

The initial column value after its creation, for example, after executing the ALTER TABLE command.

It can be NULL

default_value

VARCHAR

-

The default column value used in upsert operations.

It can be NULL

nulls_allowed

BOOLEAN

-

Specifies whether the column can contain NULL values

is_uniq

BOOLEAN

-

Specifies whether the column can contain repeating values for different rows

data_file_id

BIGINT

Primary key

The Parquet file ID assigned by increasing the next_file_id field of the pga_snapshot metadata table

begin_snapshot

BIGINT

Foreign key, NOT NULL

The snapshot associated with creating the Parquet file using the metastore.add_files or metastore.copy_table operation, from the pga_snapshot metadata table

end_snapshot

BIGINT

Foreign key

The snapshot associated with marking snapshots as expired, from the pga_snapshot metadata table.

If the value is NULL, the Parquet file is not deleted

table_id

BIGINT

Foreign key, NOT NULL

The ID of the analytical table that contains the Parquet file, from the pga_table metadata table

file_order

BIGINT

NOT NULL

The order of the Parquet file in the analytical table.

The file_order fields must be unique within the analytical table, but the ordered sequence can have gaps

path

VARCHAR

NOT NULL, UNIQUE

The path to the Parquet file (e.g., my_file.parquet)

path_is_relative

BOOLEAN

NOT NULL

Specifies whether the path to the Parquet file is relative to the path to the analytical table or absolute.

Default value: false

file_format

VARCHAR

NOT NULL

The file format.

Currently, only the Parquet format is supported

record_count

BIGINT

NOT NULL

The number of rows in the Parquet file

file_size_bytes

BIGINT

NOT NULL

The Parquet file size, in bytes

row_id_start

BIGINT

NOT NULL

The row_id value for the first row in the Parquet file.

It indicates the starting position of the file within the analytical table

checksum_crc32

BIGINT

NOT NULL

The current checksum value of the Parquet file.

It is used for data integrity checks

data_file_id

BIGINT

Primary key, foreign key

The ID of the Parquet file from the pga_data_file metadata table

path

VARCHAR

NOT NULL, UNIQUE

The path to the Parquet file (e.g., my_file.parquet)

path_is_relative

BOOLEAN

NOT NULL

Specifies whether the path to the Parquet file is relative to the path to the analytical table or absolute.

Default value: false

schedule_start

TIMESTAMPTZ

NOT NULL

The date and time when the Parquet file is scheduled for deletion

table_id

BIGINT

Primary key, foreign key

The ID of the analytical table for which statistics are collected, from the pga_table metadata table

record_count

BIGINT

NOT NULL

The number of rows in the analytical table

next_row_id

BIGINT

NOT NULL

The row_id value for the next row to be added to the analytical table

file_size_bytes

BIGINT

NOT NULL

The total size of all Parquet files of the analytical table, in bytes

column_id

BIGINT

Primary key, foreign key

The ID of the column for which statistics are collected, from the pga_column metadata table

contains_null

BOOLEAN

NOT NULL

Specifies whether the column can contain NULL values

contains_nan

BOOLEAN

-

Specifies whether the column can contain NaN values.

This is relevant only for FLOAT, DOUBLE, and NUMERIC data types

min_value

VARCHAR

-

The minimum column value.

It is displayed as a string for consistency and must be converted to the current column data type when used.

max_value

VARCHAR

-

The maximum column value.

It is displayed as a string for consistency and must be converted to the current column data type when used.

data_file_id

BIGINT

Primary key, foreign key

The ID of the Parquet file that contains the column, from the pga_data_file metadata table

column_id

BIGINT

Primary key, foreign key

The ID of the column for which statistics are collected, from the pga_column metadata table

column_size

BIGINT

NOT NULL

The column size, in bytes

value_count

BIGINT

NOT NULL

The number of values for the column in the Parquet file.

This is not the same as the number of rows since the column can contain nested types, such as structures and lists

null_count

BIGINT

NOT NULL

The number of NULL values in the column

min_value

VARCHAR

-

The minimum column value in the Parquet file.

It can serve as an upper bound estimate for the minimum column value

max_value

VARCHAR

-

The maximum column value in the Parquet file.

It can serve as a lower bound estimate for the maximum column value

partition_id

BIGINT

Primary key

The partition ID assigned by increasing the next_catalog_id field of the pga_snapshot metadata table

begin_snapshot

BIGINT

Foreign key, NOT NULL

The snapshot associated with creating the partitioned analytical table, from the pga_snapshot metadata table

end_snapshot

BIGINT

Foreign key

The snapshot associated with deleting the partitioned analytical table, from the pga_snapshot metadata table.

If the value is NULL, the table is not deleted

table_id

BIGINT

Foreign key, NOT NULL

The ID of the partitioned analytical table from the pga_table metadata table

partition_id

BIGINT

Primary key, foreign key

The ID of the partition that contains the column, from the pga_partition_info metadata table

column_id

BIGINT

Foreign key

The ID of the column from the pga_column metadata table

table_id

BIGINT

Foreign key, NOT NULL

The ID of the analytical table that contains the column, from the pga_table metadata table

partition_key_index

BIGINT

NOT NULL

The sequence number of the column in the partition starting at 0.

For example, if a partition contains columns A, B, and C, the partition_key_index value for the column B is 1

data_file_id

BIGINT

Primary key, foreign key

The ID of the Parquet file from the pga_data_file metadata table

table_id

BIGINT

Foreign key, NOT NULL

The ID of the analytical table that contains the Parquet file, from the pga_table metadata table

partition_key_index

BIGINT

NOT NULL

The partition_key_index value from the pga_partition_column metadata table

partition_value

VARCHAR

NOT NULL

The value of the partition column identified by the partition_key_index value.

All rows in the Parquet file have the same value for the column

lsn

BIGSERIAL

Primary key

The transaction log sequence number (LSN)

txid

BIGINT

NOT NULL

The task ID

op_id

INT

-

The stored procedure ID.

One task can consist of multiple stored procedures

kind

VARCHAR

NOT NULL

The stored procedure type.

Possible values:

attr

JSONB

-

The initial and runtime data associated with the task

proxy_table_id

BIGINT

Primary key

The proxy table ID

proxy_table_name

VARCHAR

NOT NULL

The proxy table name identifying the associated pgpro_metastore object.

The name has the following format:

    prefix_object_name_object_number
  

Where:

Examples of proxy table names: