Can my DB?

-- List available extensions
SELECT * FROM pg_available_extensions ORDER BY name;

-- Install an extension into the current database
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS postgis SCHEMA public;
CREATE EXTENSION IF NOT EXISTS pgvector VERSION '0.7.0';

-- List installed extensions
SELECT extname, extversion FROM pg_extension;

-- Upgrade an installed extension
ALTER EXTENSION postgis UPDATE TO '3.4.0';

-- Remove an extension
DROP EXTENSION IF EXISTS pgcrypto;

• Extensions bundle SQL objects, C shared libraries, index operator classes, and custom types into a single installable unit.
• Common extensions include PostGIS (spatial data), pgvector (vector/AI embeddings), pg_trgm (trigram similarity), pgcrypto (cryptographic functions), hstore (key-value), uuid-ossp (UUID generation), tablefunc (crosstab), postgres_fdw (foreign data), pg_stat_statements (query statistics), timescaledb (time-series), and citus (distributed).
• Managed PostgreSQL services (Amazon RDS, Cloud SQL, Azure Database) allow a curated subset of extensions.
• PGXN (the PostgreSQL Extension Network) and the broader PostgreSQL community on GitHub host hundreds of extensions.

-- List all plugins
SHOW PLUGINS;

-- Install a plugin from a shared library
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

-- Install a component (MySQL 8.0+)
INSTALL COMPONENT 'file://component_validate_password';

-- List components
SELECT * FROM mysql.component;

-- Uninstall a plugin
UNINSTALL PLUGIN validate_password;

-- Check available storage engines (a type of plugin)
SHOW ENGINES;

• MySQL's plugin system supports storage engines (InnoDB, MyISAM, Memory, NDB), full-text parsers, authentication plugins, and audit plugins.
• MySQL 8.0 introduced components as a newer, more modular extension mechanism alongside the older plugin API.
• There are far fewer community plugins than PostgreSQL has extensions.
• MySQL Shell extensions allow writing shell plugins in JavaScript or Python.
• There is no equivalent to PostgreSQL's extension model for adding data types or index operator classes.

-- Install a plugin by name (loads and registers permanently)
INSTALL PLUGIN METADATA_LOCK_INFO SONAME 'metadata_lock_info';

-- Install a plugin by shared library (all plugins in the library)
INSTALL SONAME 'ha_spider';

-- List all installed plugins
SHOW PLUGINS;

-- Uninstall a plugin
UNINSTALL PLUGIN METADATA_LOCK_INFO;
UNINSTALL SONAME 'ha_spider';

• MariaDB has more first-party plugins than MySQL and a more active community of plugin authors.
• MariaDB-specific plugins include Spider (horizontal sharding across MySQL/MariaDB nodes), CONNECT (external data sources: MySQL, Oracle, ODBC, CSV, JSON, XML), Mroonga (full-text search for Japanese), Aria (crash-safe storage engine), and ColumnStore (columnar analytics).
• INSTALL SONAME installs all plugins in a given shared library at once.
• MariaDB plugin API is largely compatible with MySQL's, so many MySQL plugins work on MariaDB.
• MariaDB does not use the MySQL Component infrastructure.

-- Oracle extensibility is via optional licensed components and PL/SQL packages.
-- Check installed options and components:
SELECT comp_name, version, status FROM dba_registry;

-- Example: Spatial and Graph option (if licensed)
SELECT * FROM mdsys.sdo_geom_metadata_table;

-- User-defined types as a form of extension
CREATE TYPE point_t AS OBJECT (
  x NUMBER,
  y NUMBER,
  MEMBER FUNCTION distance(p point_t) RETURN NUMBER
);
/

• Oracle sells optional database components as separately licensed add-ons: Spatial and Graph, Advanced Analytics (R/machine learning), Database Vault, Label Security, OLAP, Partitioning, and others.
• These are not community extensions; they are commercial Oracle products installed by Oracle DBAs.
• PL/SQL packages (DBMS_*) are the in-engine extensibility mechanism for custom logic.
• Oracle has nothing comparable to PostgreSQL's community-built extensions.
• Java stored procedures and external procedures (C/Java via extproc) allow limited native-code extensibility.

-- Enable CLR integration
sp_configure 'clr enabled', 1;
RECONFIGURE;

-- Load a .NET assembly
CREATE ASSEMBLY MyExtension
  FROM 'C:\assemblies\MyExtension.dll'
  WITH PERMISSION_SET = SAFE;

-- Create a CLR scalar function from the assembly
CREATE FUNCTION dbo.ComputeHash(@input NVARCHAR(MAX))
RETURNS VARBINARY(32)
AS EXTERNAL NAME MyExtension.[MyExtension.Functions].ComputeHash;

-- Enable external scripts (R/Python, requires ML Services)
sp_configure 'external scripts enabled', 1;
RECONFIGURE;

• CLR integration (CREATE ASSEMBLY) allows .NET-based user-defined functions, stored procedures, aggregates, and types, but must be explicitly enabled.
• SQL Server Machine Learning Services enables running R and Python scripts inside the database engine.
• Linked servers extend data access to external sources (Oracle, DB2, ODBC), but are an integration feature rather than an extension system.
• There is no community extension marketplace comparable to PostgreSQL's.
• SQL Server does not support loading arbitrary native shared libraries as plugins.

-- Enable extension loading (must be done from application code or CLI)
-- In the sqlite3 CLI:
.load './path/to/extension'

-- In SQL (if enabled via sqlite3_enable_load_extension)
SELECT load_extension('./path/to/extension.so');

-- Load with a specific entry point
SELECT load_extension('./path/to/extension.so', 'sqlite3_myext_init');

-- Example: use the FTS5 built-in extension
CREATE VIRTUAL TABLE docs USING fts5(title, body);
INSERT INTO docs VALUES ('Hello', 'world example');
SELECT * FROM docs WHERE docs MATCH 'world';

• SQLite supports loadable extensions that can add new SQL functions, virtual table implementations, and collating sequences.
• Extension loading is disabled by default for security and must be enabled explicitly via sqlite3_enable_load_extension() in the C API.
• Common extensions include FTS5 (full-text search, built-in), SpatiaLite (spatial/GIS), sqlite-zstd (transparent compression), sqlite-vec / sqlite-vss (vector search for AI), and sqlean (a collection of common utilities).
• Many SQLite builds (e.g., Python's sqlite3 module) ship with FTS5, JSON1, and math functions compiled in.
• Extensions are .so/.dll shared libraries loaded at runtime.

-- Redshift does not support user-installed extensions.
-- Some PostgreSQL catalog functions are available:
SELECT * FROM pg_catalog.pg_settings WHERE name LIKE 'enable%';

-- Extensibility is through user-defined functions (Python/SQL)
CREATE OR REPLACE FUNCTION f_sha256(s VARCHAR)
RETURNS VARCHAR
STABLE AS $$
  import hashlib
  return hashlib.sha256(s.encode()).hexdigest()
$$ LANGUAGE plpythonu;

• Redshift is a fully managed service and does not support user-installable extensions or plugins.
• Functionality is determined by AWS and updated automatically.
• Some PostgreSQL extensions are pre-installed internally (e.g., certain pg_catalog functions), but users cannot install new extensions.
• Extensibility is through Python UDFs (plpythonu), Lambda UDFs (external functions), and SQL UDFs.
• Redshift is based on a fork of PostgreSQL 8.0.2 and has diverged significantly.

-- DB2 optional components are installed at the OS level, not via SQL.
-- Check installed features:
db2ls -a

-- Register a Java external function
CREATE FUNCTION schema.my_func(IN v VARCHAR(100))
  RETURNS VARCHAR(100)
  LANGUAGE JAVA
  PARAMETER STYLE JAVA
  NO SQL
  EXTERNAL NAME 'myjar:com.example.MyFunctions.myFunc';

-- Register a C external function
CREATE FUNCTION schema.c_func(IN x INT)
  RETURNS INT
  LANGUAGE C
  PARAMETER STYLE SQL
  EXTERNAL NAME 'mylib!c_func';

• DB2 optional components are IBM products licensed separately: DB2 Text Search (full-text), Spatial Extender (GIS), Query Patroller, Optim Performance Manager.
• These are installed at the operating system level and registered into DB2, not installed via SQL DDL.
• User-defined external functions in Java and C provide function-level extensibility within the engine.
• There are no community-built extensions comparable in scope to PostgreSQL's.
• IBM Data Studio and third-party tools provide additional tooling but are external to the database engine.

-- Snowflake does not support user-installed extensions.
-- Extensibility is via Snowpark UDFs:
CREATE OR REPLACE FUNCTION my_udf(x FLOAT)
RETURNS FLOAT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'compute'
AS $$
def compute(x):
    return x * 2.0
$$;

-- External functions call AWS Lambda / Azure Functions
CREATE OR REPLACE EXTERNAL FUNCTION call_api(n INTEGER)
  RETURNS VARIANT
  API_INTEGRATION = my_api_integration
  AS 'https://xyz.lambda-url.us-east-1.on.aws/my-function';

• Snowflake is a fully managed cloud service with no user-installable extensions or plugins.
• Extensibility is through Snowpark UDFs in Python, Java, and Scala for custom logic inside the engine.
• External functions call AWS Lambda, Azure Functions, or Google Cloud Functions for integration with external systems.
• Snowflake Marketplace provides data sets and data apps for sharing, not engine extensions.
• Native Apps Framework allows building and distributing applications on Snowflake, but not extending the core engine.

-- BigQuery does not support user-installed extensions.
-- SQL UDFs for custom functions:
CREATE OR REPLACE FUNCTION mydataset.celsius_to_f(c FLOAT64)
RETURNS FLOAT64 AS ((c * 9/5) + 32);

-- JavaScript UDF
CREATE OR REPLACE FUNCTION mydataset.parse_json_field(data STRING)
RETURNS STRING
LANGUAGE js AS '''
  var obj = JSON.parse(data);
  return obj.name;
''';

-- Remote function (calls Cloud Run)
CREATE OR REPLACE FUNCTION mydataset.remote_predict(x FLOAT64)
RETURNS FLOAT64
REMOTE WITH CONNECTION `myproject.us.my-connection`
OPTIONS (endpoint = 'https://my-service-xyz.a.run.app/predict');

• BigQuery is a serverless managed service with no user-installable extensions.
• Extensibility is through SQL UDFs, JavaScript UDFs, and remote functions (Cloud Run, Cloud Functions).
• BigQuery ML (BQML) allows training and invoking ML models using SQL directly in BigQuery.
• Connected Sheets integrates BigQuery with Google Sheets for analysis without SQL.
• The BigQuery Omni feature extends BigQuery queries to data in AWS S3 and Azure Blob Storage.