Extensions on Tessell for PostgreSQL

Tessell for PostgreSQL comes with several extensions out of the box that enhance the capabilities of a PostgreSQL instance.

Not all extensions are necessary for every database. Each extension is tailored to a specific use case. For instance, if you need to create unique identifiers in PostgreSQL, you can use the "uuid-ossp" extension in your application queries after creating them. If you want to store key-value pairs, you can create the "hstore" extension, which does the job. Tessell for PostgreSQL only supports trusted extensions, which restricts access to the underlying OS from the database.

Extended Data Types

Tessell for PostgreSQL allows the use of the following data types, which provides more convenience to application developers.

• citext -https://www.postgresql.org/docs/current/citext.html
  This extension in PostgreSQL provides case-insensitive text comparison and storage.\

• cube -https://www.postgresql.org/docs/current/cube.html
  This extension in PostgreSQL enables multidimensional indexing and querying support.

• hstore -https://www.postgresql.org/docs/current/hstore.html

• This extension in PostgreSQL facilitates storing key-value pairs within a single database column.

• isn -https://www.postgresql.org/docs/current/isn.htmlThis extension in PostgreSQL adds support for the storage and manipulation of international serial numbers.

• ltree -https://www.postgresql.org/docs/current/ltree.htmlThis extension in PostgreSQL provides hierarchical tree-like data storage and querying capabilities.

• seg -https://www.postgresql.org/docs/current/seg.htmlThis extension in PostgreSQL provides support for storing and querying ranges of values on a number line.

• uuid-ossp -https://www.postgresql.org/docs/current/uuid-ossp.htmlThis extension in PostgreSQL allows for the generation of universally unique identifiers (UUIDs) using various algorithms.

Query Performance

Tessell for PostgreSQL allows the use of several extensions that provide additional index methods. These extensions allow users to create a different set of indexes on the data, which can significantly improve query performance.

• bloom -https://www.postgresql.org/docs/current/bloom.htmlThis extension in PostgreSQL implements Bloom filter data structures for efficient membership tests.

• btree_gin -https://www.postgresql.org/docs/current/btree-gin.htmlThis extension in PostgreSQL allows GIN (Generalized Inverted Index) indexing on B-tree data types.

• btree_gitst -https://www.postgresql.org/docs/current/btree-gist.htmlThis extension in PostgreSQL enables GiST (Generalized Search Tree) indexing on B-tree data types.

• dict_int -https://www.postgresql.org/docs/current/dict-int.htmlThis extension is an example of adding a dictionary template for full-text search.

• dict_xsyn -https://www.postgresql.org/docs/current/dict-xsyn.htmlThis extension is an extended synonym dictionary template for full-text search.

• fuzzystrmatch -https://www.postgresql.org/docs/current/fuzzystrmatch.htmlThis extension in PostgreSQL provides functions for approximate string matching and similarity measurement

• pg_trgm -https://www.postgresql.org/docs/current/pgtrgm.htmlThis extension in PostgreSQL enables trigram-based text search and similarity analysis.

• unaccent -https://www.postgresql.org/docs/current/unaccent.htmlThis extension in PostgreSQL provides functions for removing accents and diacritic marks from text

Audit & Monitoring

Tessell for PostgreSQL comes with auditing and monitoring extensions built-in, ready to use out of the box. By using these auditing and monitoring extensions, users can easily keep track of database activity and performance. This helps identify and resolve issues before they become major problems.

• pgaudit -https://github.com/pgaudit/pgaudit.gitThis extension enabled detailed session and object level auditing

• pg_stat_statements -https://www.postgresql.org/docs/current/pgstatstatements.htmlThis extension in PostgreSQL provides detailed statistics about SQL statements executed in the database

Observability

Tessell for PostgreSQL comes with a set of actions that allow you to dive deep into the database system and understand how the engine works. This helps to troubleshoot the potential database issues by providing detailed insights into the database engine.

• pageinspect -https://www.postgresql.org/docs/current/pageinspect.htmlThis extension in PostgreSQL allows users to inspect the contents of database pages, providing insights into the internal structure of tables and indexes.

• pg_buffercache -https://www.postgresql.org/docs/current/pgbuffercache.htmlThis extension in PostgreSQL allows users to examine the contents of the shared buffer cache, providing insights into the data that is currently cached in memory.

• pg_freespacemap -https://www.postgresql.org/docs/current/pgfreespacemap.htmlThis extension in PostgreSQL provides tools for managing and monitoring the free space map, which helps track available space within table and index pages.

• pg_visibility -https://www.postgresql.org/docs/current/pgvisibility.htmlThis extension in PostgreSQL enables the examination of the visibility of tuples (rows) on database pages.

• pg_walinspect -https://www.postgresql.org/docs/current/pgwalinspect.html(PG15+)This extension in PostgreSQL enables us to examine the contents of WAL files, which is useful for debugging, analysis, reporting, or education.

• pgrowlocks -https://www.postgresql.org/docs/current/pgrowlocks.htmlThe pgrowlocks module provides a function to show row locking information for a specified table.

• pgstattuple -https://www.postgresql.org/docs/current/pgstattuple.html
  This extension is a widely known and used PostgreSQL extension that provides statistical information about tables and indexes.

Procedural Languages

Tessell for PostgreSQL also supports several procedural languages. These languages allow developers to write stored procedures and functions in their preferred language, making it easier to integrate with existing codebases and applications.

• plperl -https://www.postgresql.org/docs/current/plperl.htmlThis extension in PostgreSQL allows you to use the Perl programming language for creating database functions and triggers.

• pltcl -https://www.postgresql.org/docs/current/pltcl.htmlThis extension in PostgreSQL permits the utilization of the TCL scripting language for crafting database functions.

• plpgsql -https://www.postgresql.org/docs/current/plpgsql.htmlThis extension in PostgreSQL enables the use of the SQL procedural language, allowing you to create custom stored procedures, functions, and triggers.

• pg_tle -https://github.com/aws/pg_tleThis extension in PostgreSQL can enable developers to use trusted language extensions for PostgreSQL to create and install extensions on restricted filesystems.

Foreign Data Wrappers

Tessell for PostgreSQL also supports Foreign Data Wrappers, which allow users to access data from external sources as if it were local PostgreSQL tables. This can be very useful for integrating with other data sources and systems, and can simplify the process of working with heterogeneous data.

• postgres_fdw -https://www.postgresql.org/docs/current/postgres-fdw.htmlThis extension in PostgreSQL allows you to establish foreign data wrappers, enabling access to stored data.

• oracle_fdw -https://github.com/laurenz/oracle_fdwThis extension in PostgreSQL permits access to data stored in remote Oracle databases through foreign data wrappers.

Data Management

Tessell for PostgreSQL provides additional extensions for data management. These extensions enable users to manage large datasets more efficiently, allowing for better organization and analysis of the data. Use these extensions to optimize storage, improve data processing.

• lo -https://www.postgresql.org/docs/current/lo.htmlThis extension in PostgreSQL is used to manage large objects (BLOBs), allowing storage and manipulation of binary data like images, audio files, and documents.

• pg_partman -https://github.com/pgpartman/pg_partmanThis extension in PostgreSQL is designed to manage and automate table partitioning, making it easier to maintain large datasets by dividing them into smaller ones.

• pg_prewarm -https://www.postgresql.org/docs/current/pgprewarm.htmlThis extension in PostgreSQL helps improve database performance by preloading data into the system cache.

• pg_repack-This extension in PostgreSQL aids in optimizing database storage by reorganizing tables and indexes.

• pg_surgery -https://www.postgresql.org/docs/current/pgsurgery.html (PG 14+)This extension should be used cautiously. By using this extension, we can perform surgery on a damaged relationship. This always has to be a last resort when you are unable to recover the table data.

• pg_visibility -https://www.postgresql.org/docs/current/pgvisibility.htmlThis extension is an internal module used for analyzing the visibility of data within the database.

• old_snapshot -https://www.postgresql.org/docs/current/oldsnapshot.html (PG 14+)This extension helps in the inspection of server state that is used in getting the old snapshot threshold.

• pg_cron -https://github.com/citusdata/pg_cronThis extension allows you to schedule and run database tasks using cron-like syntax directly within the database.

• pgrowlocks -https://www.postgresql.org/docs/current/pgrowlocks.htmlThis provides insights into row-level locking information within the database.

• pageinspect -https://www.postgresql.org/docs/current/pageinspect.htmlThis provides tools for inspecting and analyzing the internal data structures and contents of database pages.

• anon -https://github.com/rap2hpoutre/pg-anonymizerThis extension masks or replaces PII (Personal Identifiable Information) or commercially sensitive data from the PostgreSQL database.

• pglogical -https://github.com/2ndQuadrant/pglogicalThis extension allows for the logical replication of specific tables or parts of tables between different PostgreSQL databases.

Embedding

Tessell for PostgreSQL provides the following extensions, which can help generate embeddings for use in generative AI applications.

• pgvector -https://github.com/pgvector/pgvectorThis extension supports vector similarity search for PostgreSQL.

Utilities

Tessell for PostgreSQL provides the below developer and administrator sets of extensions.

• amcheck -https://www.postgresql.org/docs/current/amcheck.htmlThis extension supports the integrity verification of relational indexes.

• contrib-spi -https://www.postgresql.org/docs/14/contrib-spi.htmlThese are general-purpose functions that we can easily integrate into any of the relationships.

• plperl-funcs -https://www.postgresql.org/docs/current/plperl-funcs.htmlThis extension provides plperl utility functionalities integrated

• earthdistance -https://www.postgresql.org/docs/current/earthdistance.htmlThis extension in PostgreSQL enables geographic calculations, allowing you to compute distances and perform location-based queries.

• intagg -https://www.postgresql.org/docs/current/intagg.htmlThis extension in PostgreSQL provides functions for efficiently aggregating integer values into arrays.

• intarray -https://www.postgresql.org/docs/current/intarray This extension in PostgreSQL introduces support for arrays of integers, enabling operations and optimizations related to integer arrays.

• sslinfo -https://www.postgresql.org/docs/current/sslinfo.htmlThis extension provides information about the SSL certificate that the current client provided when connecting to PostgreSQL.

• tablefunc -https://www.postgresql.org/docs/current/tablefunc.htmlThis extension in PostgreSQL provides a set of functions that generate tables with various useful structures for analysis purposes, including crosstab tables and pivot tables.

• tcn -https://www.postgresql.org/docs/current/tcn.htmlThis extension in PostgreSQL enables asynchronous notifications of changes to database rows through triggers.

• tsm_system_rows -https://www.postgresql.org/docs/current/tsm-system-rows.htmlThis extension in PostgreSQL is related to table sampling methods, which can be used in the TABLESAMPLE clause of the SELECT command.

• tsm_system_time -https://www.postgresql.org/docs/current/tsm-system-time.htmlThis extension in PostgreSQL is related to table sampling methods, which can be used in the TABLESAMPLE clause of the SELECT command.