System Table Compatibility

Besides compatibility with the PostgreSQL SQL dialect and protocol, CedarDB also supports a large part of the PostgreSQL system table catalog in the pg_catalog schema. These system tables contain information about the system state in the form of metadata. This metadata is often used by external tools and clients to interact with the database system for introspection and reflection, e.g., to show which tables exist.

This page provides an overview of the currently supported system tables and views.

🟢 Supported
🟡 Stubbed for compatibility: These do not contain actual content and exist solely for compatibility purposes, as CedarDB does not share PostgreSQL’s codebase or internal architecture
🔴 Not yet supported

Additionally, CedarDB exposes information that are not available in PostgreSQL in its CedarDB system tables.

System Tables

System tables provide a raw view into the state of the database system. In contrast to PostgreSQL, system tables in CedarDB are read-only, and can only be indirectly influenced through DDL statements.

System tables often contain many low-level details. For more accessible and friendly access to the same information, consider using the built-in system views, or the SQL-standard information schema.

Feature	Support State	Details
pg_aggregate	🟡	Stores information about aggregate functions.
pg_am	🟢	Contains information about access methods; amhandler is unset.
pg_amop	🟡	Stores information about operators associated with access methods.
pg_amproc	🟡	Contains information about support procedures associated with access methods.
pg_attrdef	🟢	Stores column default values.
pg_attribute	🟢	Contains information about table columns.
pg_authid	🟢	Stores information about database roles.
pg_auth_members	🟡	Tracks role memberships.
pg_cast	🟡	Contains information about type casts.
pg_class	🟢	Stores information about tables, indexes, sequences, and other relations.
pg_collation	🟢	Contains information about collations.
pg_constraint	🟢	Stores information about table constraints.
pg_conversion	🟡	Contains information about encoding conversions.
pg_database	🟢	Stores information about databases.
pg_db_role_setting	🟡	Contains per-role and per-database configuration settings.
pg_default_acl	🟡	Stores default access privileges.
pg_depend	🟡	Tracks dependencies between database objects.
pg_description	🟢	Stores optional descriptions (comments) for database objects.
pg_enum	🟡	Contains information about enum types.
pg_event_trigger	🟡	Stores information about event triggers.
pg_extension	🟡	Contains information about installed extensions.
pg_foreign_data_wrapper	🟡	Stores information about foreign-data wrappers.
pg_foreign_server	🟡	Contains information about foreign servers.
pg_foreign_table	🟡	Stores information about foreign tables.
pg_index	🟢	Contains information about indexes.
pg_inherits	🟡	Tracks table inheritance hierarchies.
pg_init_privs	🟡	Stores initial privileges of database objects.
pg_language	🟡	Contains information about procedural languages.
pg_largeobject	🟡	Stores large object data.
pg_largeobject_metadata	🟡	Contains metadata for large objects.
pg_namespace	🟢	Stores information about schemas.
pg_opclass	🟢	Contains information about operator classes.
pg_operator	🟡	Stores information about operators.
pg_opfamily	🟡	Contains information about operator families.
pg_parameter_acl	🟡	Stores access privileges for server parameters.
pg_partitioned_table	🟡	Contains information about partitioned tables.
pg_policy	🟡	Stores information about row-level security policies.
pg_proc	🟢	Contains information about functions and procedures.
pg_publication	🟡	Contains all publications created in the database.
pg_publication_namespace	🟡	Maps schemas to publications, supporting a many-to-many relationship.
pg_publication_rel	🟡	Maps relations (tables) to publications, supporting a many-to-many relationship.
pg_range	🟡	Stores information about range types, supplementing entries in pg_type.
pg_replication_origin	🟡	Contains all replication origins created, shared across all databases in a cluster.
pg_rewrite	🟡	Stores rewrite rules for tables and views.
pg_seclabel	🟡	Stores security labels on database objects, manipulable with the SECURITY LABEL command.
pg_sequence	🟢	Contains information about sequences, with additional details in pg_class.
pg_shdepend	🟡	Records dependency relationships between database objects and shared objects, like roles.
pg_shdescription	🟡	Stores optional descriptions (comments) for shared database objects.
pg_shseclabel	🟡	Stores security labels for shared database objects.
pg_statistic	🟡	Stores statistical data about the contents of the database, used by the query planner.
pg_statistic_ext	🟡	Stores extended statistics for columns, aiding in more accurate query planning.
pg_statistic_ext_data	🟡	Contains data for extended statistics objects.
pg_subscription	🟡	Stores information about logical replication subscriptions.
pg_subscription_rel	🟡	Tracks the state of individual relations in a subscription.
pg_tablespace	🟡	Stores information about the available tablespaces.
pg_transform	🟡	Stores information about transforms between data types and procedural languages.
pg_trigger	🟡	Contains information about triggers on tables.
pg_ts_config	🟡	Stores text search configurations.
pg_ts_config_map	🟡	Maps text search configurations to dictionaries.
pg_ts_dict	🟡	Stores text search dictionaries.
pg_ts_parser	🟡	Contains text search parsers.
pg_ts_template	🟡	Stores text search templates.
pg_type	🟢	Stores information about data types.
pg_user_mapping	🟡	Contains information about user mappings for foreign data access.

CedarDB System Tables

CedarDB provides additional information in its system-specific system tables. As the PostgreSQL system tables, those system tables are read-only.

cedardb_compression_info

This system table contains information about how tables and columns are compressed in CedarDB. Note that CedarDB can use different compression schemes within the same column and that this table currently only includes statistics on cold data. For more information on cold and hot data, see this blog post.

Column	Type	Description
oid	Integer	The Object Identifier of the table.
tablename	Text	The name of the table.
attributename	Text	The name of the attribute.
datatype	Text	The type of the attribute.
encoding	Text	The lightweight encoding scheme used for this value, e.g. dictionary or frame-of-reference encoding.
generalcompression	Text	The general-purpose compression method applied on top of the lightweight encoding. Currently either `none` or `zstd` (see compression option).
encodedvaluewidth	Text	The maximum number of bytes required to encode a value using the lightweight encoding. For instance, if a dictionary has at most 256 keys, each value can be encoded using just one byte.
uncompressedsize	Bigint	The size of the uncompressed data in bytes. For strings, this includes additional meta data to be able to query the data.
encodedsize	Bigint	The size of the data in bytes after the lightweight encoding, before any general-purpose compression is applied.
compressedsize	Bigint	The size of the data on disk in bytes, after both the lightweight encoding and the general-purpose compression have been applied.
tuplecount	Bigint	The number of encoded tuples.

This is an excerpt of the output for TPCH with scale factor 1:

oid         tablename   attributename   datatype    encoding                    generalcompression  encodedvaluewidth   uncompressedsize    encodedsize     compressedsize  tuplecount
-----
268435460   partsupp    ps_comment      text        simple dictionary          zstd                fourbyte            106775896           103736736       27070464        765159
268435460   partsupp    ps_comment      text        simple dictionary          zstd                twobyte             4855777             4647024         1196032         34841
268435460   partsupp    ps_supplycost   numeric     truncate                    zstd                fourbyte            6400000             3200016         2142208         800000
268435460   partsupp    ps_availqty     int32       truncate                    zstd                twobyte             3200000             1600032         1474560         800000
268435460   partsupp    ps_suppkey      int32       truncate                    zstd                twobyte             3200000             1600032         618496          800000
268435460   partsupp    ps_partkey      int32       frameofreferencetinyblocks  zstd                eightbyte           3200000             891312          20480           800000
268435460   partsupp    tid             bigint      increment                   none                zerobytes           6400000             0               0               800000
268435462   orders      o_clerk         char        sortedstring dictionary     zstd                twobyte             46484672            3114096         2265088         1500000
268435462   orders      o_comment       text        simple dictionary          zstd                fourbyte            96751727            90643888        29347840        1500000
268435462   orders      tid             bigint      increment                   none                zerobytes           12000000            0               0               1500000
...

Note the two rows for ps_comment: CedarDB split this column across two differently-encoded blocks during loading, illustrating that a single column can use different encoding and compression schemes at once for different parts of its data. Also note that generalcompression is only zstd when applying it reduces the on-disk size by a large enough margin. Otherwise CedarDB skips the extra compression pass and stores the lightweight-encoded data as is (generalcompression = none, compressedsize = encodedsize), as is the case for tid here.

System Views

System views provide convenient access to system information. System tables often contain numeric identifiers, e.g., for the owner of tables. The views instead use more human-readable symbolic names.

Feature	Support State	Details
pg_available_extensions	🟡	Lists available extensions.
pg_available_extension_versions	🟡	Shows available versions of extensions.
pg_backend_memory_contexts	🟡	Displays memory contexts of the backend.
pg_config	🔴	Provides access to compile-time configuration parameters.
pg_cursors	🔴	Lists open cursors.
pg_file_settings	🔴	Summarizes contents of configuration files.
pg_group	🟢	Displays groups of database users.
pg_hba_file_rules	🔴	Summarizes client authentication configuration.
pg_ident_file_mappings	🟡	Summarizes client user name mapping configuration.
pg_indexes	🟢	Shows information about indexes.
pg_locks	🟡	Displays locks currently held or awaited.
pg_matviews	🟡	Lists materialized views.
pg_policies	🟡	Displays information about policies.
pg_prepared_statements	🟡	Lists prepared statements.
pg_prepared_xacts	🔴	Shows prepared transactions.
pg_publication_tables	🔴	Displays publications and their associated tables.
pg_replication_origin_status	🔴	Provides information about replication origins, including replication progress.
pg_replication_slots	🔴	Displays replication slot information.
pg_roles	🟢	Lists database roles.
pg_rules	🟡	Shows information about rules.
pg_seclabels	🟡	Displays security labels.
pg_sequences	🟡	Lists sequences.
pg_settings	🟢	Provides access to parameter settings.
pg_shadow	🟢	Displays database users.
pg_shmem_allocations	🟡	Shows shared memory allocations.
pg_stats	🔴	Provides planner statistics.
pg_stats_ext	🟡	Displays extended planner statistics.
pg_stats_ext_exprs	🟡	Shows extended planner statistics for expressions.
pg_tables	🟢	Lists tables.
pg_timezone_abbrevs	🟢	Displays time zone abbreviations.
pg_timezone_names	🟢	Lists time zone names.
pg_user	🟢	Shows database users.
pg_user_mappings	🟡	Displays user mappings.
pg_views	🟢	Lists views.

CedarDB System Views

cedardb_compression_infos

This system view gives an easier usable representation of the compression ratio of the entries in the cedardb_compression_info system table. It groups by oid, tablename, attributename, datatype, encoding, and generalcompression, and adds the following ratio columns:

Column	Type	Description
encoding_ratio	Numeric	`uncompressedsize / encodedsize`: the compression ratio achieved by the lightweight encoding alone.
compression_ratio	Numeric	`encodedsize / compressedsize`: the additional compression ratio achieved by the general-purpose codec (e.g. zstd).
total_ratio	Numeric	`uncompressedsize / compressedsize`: the combined compression ratio.

This is an excerpt of the output for the same TPCH scale factor 1 tables shown above:

oid         tablename   attributename   datatype    encoding                    generalcompression  encoding_ratio  compression_ratio  total_ratio  tuplecount
-----
268435460   partsupp    ps_comment      text        simple dictionary          zstd                1.03            3.83                3.95         800000
268435460   partsupp    ps_supplycost   numeric     truncate                    zstd                2.00            1.49                2.99         800000
268435460   partsupp    ps_availqty     int32       truncate                    zstd                2.00            1.09                2.17         800000
268435460   partsupp    ps_suppkey      int32       truncate                    zstd                2.00            2.59                5.17         800000
268435460   partsupp    ps_partkey      int32       frameofreferencetinyblocks  zstd                3.59            43.52               156.25       800000
268435460   partsupp    tid             bigint      increment                   none                NULL            NULL                NULL         800000
268435462   orders      o_clerk         char        sortedstring dictionary     zstd                14.93           1.37                20.52        1500000
268435462   orders      o_comment       text        simple dictionary          zstd                1.07            3.09                3.30         1500000
268435462   orders      tid             bigint      increment                   none                NULL            NULL                NULL         1500000
...

Note that ps_comment’s two rows in cedardb_compression_info are collapsed into a single row here, and that ratios are NULL when a column has an encodedsize or compressedsize of 0 (e.g. increment-encoded columns, which need no storage at all).

Information Schema

The information_schema is a standardized, cross-database schema that allows portable system introspection. For compatibility, CedarDB follows the PostgreSQL Information Schema, which matches the ISO/IEC 9075-11 standard.

The information schema views are not included in the default search path, so queries on it need to use the fully qualified name:

select * from information_schema.tables;

Table name	Support State	Details
information_schema_catalog_name	🟢
administrable_role_authorizations	🟢
applicable_roles	🟢
attributes	🟡
character_sets	🟢
check_constraint_routine_usage	🟡
check_constraints	🟢
collations	🟢
collation_character_set_applicability	🟢
column_column_usage	🟡
column_domain_usage	🟡
column_options	🟡
column_privileges	🟡
column_udt_usage	🟢
columns	🟢
constraint_column_usage	🟢
constraint_table_usage	🟢
data_type_privileges	🟢
domain_constraints	🟡
domain_udt_usage	🟡
domains	🟡
element_types	🟢
enabled_roles	🟢
foreign_data_wrapper_options	🟡
foreign_data_wrappers	🟡
foreign_server_options	🟡
foreign_servers	🟡
foreign_table_options	🟡
foreign_tables	🟡
key_column_usage	🟢
parameters	🟢
referential_constraints	🟢
role_column_grants	🟡
role_routine_grants	🟡
role_table_grants	🟡
role_udt_grants	🟡
role_usage_grants	🟢
routine_column_usage	🟡
routine_privileges	🟡
routine_routine_usage	🟡
routine_sequence_usage	🟡
routine_table_usage	🟡
routines	🟢
schemata	🟢
sequences	🟢
sql_features	🟡
sql_implementation_info	🟡
sql_parts	🟡
sql_sizing	🟡
table_constraints	🟢
table_privileges	🟡
tables	🟢
transforms	🟡
triggered_update_columns	🟡
triggers	🟡
udt_privileges	🟡
usage_privileges	🟡
user_defined_types	🟡
user_mapping_options	🟡
user_mappings	🟡
view_column_usage	🟡
view_routine_usage	🟡
view_table_usage	🟡
views	🟢

Ecosystem and Client Compatibility