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.html
  This extension in PostgreSQL adds support for the storage and manipulation of international serial numbers.
• ltree - https://www.postgresql.org/docs/current/ltree.html
  This extension in PostgreSQL provides hierarchical tree-like data storage and querying capabilities.
• seg - https://www.postgresql.org/docs/current/seg.html
  This 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.html
  This 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.html
  This extension in PostgreSQL implements Bloom filter data structures for efficient membership tests.
• btree_gin - https://www.postgresql.org/docs/current/btree-gin.html
  This extension in PostgreSQL allows GIN (Generalized Inverted Index) indexing on B-tree data types.
• btree_gitst - https://www.postgresql.org/docs/current/btree-gist.html
  This extension in PostgreSQL enables GiST (Generalized Search Tree) indexing on B-tree data types.
• dict_int - https://www.postgresql.org/docs/current/dict-int.html
  This extension is an example of adding a dictionary template for full-text search.
• dict_xsyn - https://www.postgresql.org/docs/current/dict-xsyn.html
  This extension is an extended synonym dictionary template for full-text search.
• fuzzystrmatch - https://www.postgresql.org/docs/current/fuzzystrmatch.html
  This extension in PostgreSQL provides functions for approximate string matching and similarity measurement
• pg_trgm - https://www.postgresql.org/docs/current/pgtrgm.html
  This extension in PostgreSQL enables trigram-based text search and similarity analysis.
• unaccent - https://www.postgresql.org/docs/current/unaccent.html
  This 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.git
  This extension enabled detailed session and object level auditing
• pg_stat_statements - https://www.postgresql.org/docs/current/pgstatstatements.html
  This 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.html
  This 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.html
  This 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.html
  This 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.html
  This 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.html
  The 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.html
  This 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.html
  This extension in PostgreSQL permits the utilization of the TCL scripting language for crafting database functions.
• plpgsql - https://www.postgresql.org/docs/current/plpgsql.html
  This 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_tle
  This 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.html
  This extension in PostgreSQL allows you to establish foreign data wrappers, enabling access to stored data.
• oracle_fdw - https://github.com/laurenz/oracle_fdw
  This 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.html
  This 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_partman
  This 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.html
  This 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.html
  This 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_cron
  This 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.html
  This provides insights into row-level locking information within the database.
• pageinspect - https://www.postgresql.org/docs/current/pageinspect.html
  This provides tools for inspecting and analyzing the internal data structures and contents of database pages.
• anon - https://github.com/rap2hpoutre/pg-anonymizer
  This extension masks or replaces PII (Personal Identifiable Information) or commercially sensitive data from the PostgreSQL database.
• pglogical - https://github.com/2ndQuadrant/pglogical
  This 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/pgvector
  This 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.html
  This extension supports the integrity verification of relational indexes.
• contrib-spi - https://www.postgresql.org/docs/14/contrib-spi.html
  These are general-purpose functions that we can easily integrate into any of the relationships.
• plperl-funcs - https://www.postgresql.org/docs/current/plperl-funcs.html
  This extension provides plperl utility functionalities integrated
• earthdistance - https://www.postgresql.org/docs/current/earthdistance.html
  This extension in PostgreSQL enables geographic calculations, allowing you to compute distances and perform location-based queries.
• intagg - https://www.postgresql.org/docs/current/intagg.html
  This extension in PostgreSQL provides functions for efficiently aggregating integer values into arrays.
• intarray - https://www.postgresql.org/docs/current/intarray.html
  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.html
  This extension provides information about the SSL certificate that the current client provided when connecting to PostgreSQL.
• tablefunc - https://www.postgresql.org/docs/current/tablefunc.html
  This 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.html
  This 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.html
  This 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.html
  This extension in PostgreSQL is related to table sampling methods, which can be used in the TABLESAMPLE clause of the SELECT command.