Extension

The extensions available in KakaoCloud PostgreSQL are provided for server management and monitoring within the database.

info

All extensions available in the template1 database are registered in KakaoCloud PostgreSQL by default.
When creating a new database, all extensions are immediately available without requiring a database restart for specific extensions.

info

For more detailed information about extensions, please refer to the PostgreSQL Additional Supplied Modules and Extensions.

Supported extension list

Name	Description
adminpack	Provides extended administrative features for managing and monitoring PostgreSQL servers. Includes functions for file settings, log directory listing, file read/write operations, and more. Useful for debugging and system analysis.
amcheck	Verifies the consistency of database indexes, particularly B-Tree indexes. Helps identify potential corruption and ensures data integrity and performance.
autoinc	Supports auto-increment columns for PostgreSQL tables, generating unique values automatically when new rows are inserted.
bloom	Implements Bloom filter indexes for efficient searching in large datasets. Useful for space-efficient probabilistic data checks, providing fast lookups with minimal storage.
intarray	Provides advanced functions and operators for manipulating integer arrays. Ideal for applications handling large sets of integer data stored as arrays.
btree_gin	Combines B-Tree and GIN indexing capabilities for efficient indexing of complex data types like arrays, JSONB, and Hstore.
btree_gist	Enhances GiST indexing by supporting B-Tree operators, offering combined benefits of space efficiency and sorting functionality.
citext	Case-insensitive text type for PostgreSQL, simplifying text comparisons and searches, especially for use cases like user names or email addresses.
cube	Handles multidimensional data for geometric and spatial applications. Useful in data analysis and geographic data storage.
dblink	Facilitates connections to and queries from external PostgreSQL databases, enabling distributed database operations.
dict_xsyn	Enhances text search by supporting synonyms, improving search accuracy and consistency for text-based applications.
dict_int	Provides integer dictionaries for PostgreSQL text search, enhancing token normalization and text processing.
earthdistance	Calculates distances between geographical coordinates. Useful for GIS, mapping applications, and location-based services.
file_fdw	Treats external files (e.g., CSV) as foreign tables, simplifying data integration and processing within PostgreSQL.
fuzzystrmatch	Provides string matching algorithms for detecting typos and similar strings, enhancing text cleaning and search functionalities.
hstore	Stores key-value pairs efficiently, supporting flexible data structures for applications like logs and attribute storage.
pg_buffercache	Monitors the shared buffer cache, offering insights for database performance optimization.
pgcrypto	Offers cryptographic functions for secure data storage and processing, including hashing and encryption algorithms.
pgvector	Efficiently stores and queries high-dimensional vector data, suitable for applications like machine learning and similarity searches.
pg_cron	Schedules and automates maintenance tasks directly within the database using a cron-based scheduler.
pgrowlocks	Displays row-level lock information, helping diagnose and resolve database locking issues.
pgstattuple	Analyzes table and index space usage, including live rows, dead tuples, and free space, aiding database optimization.
plperl	Enables writing PostgreSQL functions in Perl, useful for text processing and regular expression-based transformations.
plpgsql	PostgreSQL's built-in procedural language combines SQL with procedural constructs, allowing complex database logic.
plprofiler	Profiles PL/pgSQL functions, identifying performance bottlenecks and aiding in function optimization.
plpython3u	Supports writing PostgreSQL functions in Python 3, ideal for leveraging Python libraries within the database.
postgres_fdw	Facilitates querying and manipulating remote PostgreSQL tables as if they were local, supporting distributed databases.
uuid-ossp	Generates universally unique identifiers (UUIDs), suitable for primary keys or unique identifiers in distributed systems.
pg_visibility	Provides visibility information for table pages and rows, aiding database tuning and problem diagnosis.
intagg	Aggregates multiple integers into an array, streamlining complex aggregation and data analysis tasks.
hypopg	Supports hypothetical indexes for evaluating query performance impact without actual index creation.
pg_freespacemap	Monitors free space map data, optimizing space utilization in tables and indexes.
pgaudit	Enhances audit logging, recording SQL statements for security compliance and troubleshooting.
pg_stat_kcache	Collects kernel-level performance metrics for queries, aiding in advanced performance monitoring.
pg_logical	Supports logical replication for PostgreSQL, enabling fine-grained replication of database changes.
pageinspect	Inspects internal page structure of database objects, useful for in-depth troubleshooting and performance tuning.
lo	Manages large objects (LOBs) like images and videos, facilitating efficient storage and retrieval of large data files.
ltree	Manages hierarchical data, enabling efficient queries for tree structures like file systems and organizational charts.
jsonb_plperl	Simplifies JSONB data manipulation in Perl functions, combining PostgreSQL's JSON capabilities with Perl's text processing.
unaccent	Removes accents from text, improving search and comparison accuracy for multi-language datasets.
hstore_plperl	Integrates hstore with Perl functions, enabling advanced key-value pair manipulation using Perl.
hstore_plperlu	Combines the hstore data type with untrusted Perl language (plperlu) for flexible data manipulation. plperlu provides enhanced functionality but requires careful handling due to potential security risks.
hypopg	Allows hypothetical index creation to evaluate query performance impacts without actually creating indexes, aiding in performance tuning and index design.
insert_username	Automatically records the current user's name when rows are inserted or updated. Useful for data auditing and tracking changes.
intagg	Aggregates integer values into arrays, simplifying data aggregation and analysis.
intarray	Provides functions and operators for advanced manipulation of integer arrays, suitable for applications with large integer datasets.
isn	Handles international standard numbers like ISBN, ISMN, and ISSN for storage, validation, and formatting.
jsonb_plperl	Simplifies manipulation of JSONB data using Perl functions, leveraging PostgreSQL's JSON capabilities with Perl's text processing strengths.
jsonb_plperlu	Extends JSONB data manipulation in PostgreSQL using plperlu for more advanced operations while cautioning against potential security risks.
jsonb_plpython3u	Enables JSONB data handling in Python 3 functions within PostgreSQL, offering flexibility and power for advanced JSON processing.
lo	Manages large objects (LOBs), such as images and videos, enabling efficient storage and retrieval of binary data.
ltree	Supports hierarchical data management and querying, useful for tree-structured data like file systems or organizational charts.
ltree_plpython3u	Integrates the ltree data type with Python 3, enabling hierarchical data manipulation within Python functions in PostgreSQL.
moddatetime	Automatically updates timestamps for rows when they are inserted or modified, aiding in change tracking and auditing.
old_snapshot	Monitors and manages long-held snapshots to address performance and locking issues, particularly for long-running queries.
orafce	Adds Oracle compatibility features to PostgreSQL, facilitating database migration and compatibility with Oracle-specific functionality.
pageinspect	Provides detailed insights into PostgreSQL's internal page structures, aiding in performance tuning and troubleshooting.
pg_buffercache	Monitors shared buffer cache state, offering insights for optimizing database performance.
pg_freespacemap	Monitors free space map usage, aiding in space optimization for tables and indexes.
pgaudit	Enhances audit logging for PostgreSQL, providing detailed SQL activity records for compliance and security.
pgcrypto	Offers cryptographic functions for secure data storage, including hashing and encryption utilities.
pgrowlocks	Displays row-level lock information, assisting in diagnosing and resolving locking issues.
pgstattuple	Analyzes table and index space usage, providing detailed statistics for optimization.
pg_stat_kcache	Collects kernel-level performance metrics for query execution, aiding in advanced performance monitoring.
pg_visibility	Examines table and row visibility to optimize usage and identify dead tuples.
plperl	Allows writing PostgreSQL functions in Perl, particularly useful for text processing and transformations.
plpgsql	Combines SQL and procedural logic for complex database operations within PostgreSQL.
plprofiler	Profiles PL/pgSQL functions to identify bottlenecks and optimize performance.
plpython3u	Enables Python 3-based procedural programming within PostgreSQL, supporting advanced logic in user-defined functions.
plpythonu	Uses Python 2 for procedural programming within PostgreSQL; recommended to use plpython3u due to Python 2's end of life.
postgres_fdw	Enables querying and manipulation of remote PostgreSQL tables as if they were local, supporting distributed database setups.
refint	Provides triggers to manage referential integrity between related tables, ensuring consistent relationships.
seg	Handles segment data types for geometric and spatial applications.
sslinfo	Offers SSL connection details, aiding in monitoring and securing database connections.
tablefunc	Provides advanced table manipulation functions, including crosstabs and hierarchical sorting.
tcn	Sends notifications for table changes, supporting real-time monitoring and responsive applications.
tsm_system_rows	Samples a fixed number of rows from tables for analysis and testing.
tsm_system_time	Samples rows from tables based on a specified time frame for analysis and testing.
unaccent	Removes accents from text for easier comparison and searching across languages.
uuid-ossp	Generates UUIDs for use as unique identifiers in distributed systems.
pgvector	Efficiently stores and queries high-dimensional vector data for applications like machine learning and similarity searches.
pg_cron	Implements a cron-based scheduler for database maintenance and automation tasks within PostgreSQL.

Extension details

postgres_fdw

postgres_fdw is a foreign data wrapper (FDW) provided by PostgreSQL that enables access to remote PostgreSQL database tables as if they were local tables. This functionality supports creating external tables referencing remote databases within the local database.
To use postgres_fdw provided by KakaoCloud, you must use the create_fdw_server procedure. This procedure is pre-created in the template1 database, ensuring availability in newly created databases.

To create a remote server using the procedure, follow these steps. Upon execution, the user account is automatically mapped, allowing external tables to be created and used.

1. Create a database using an administrator or user account with database creation privileges.
2. Ensure that the security group for the target database has the outbound rule (port open) configured.
3. In the created database, use the create_fdw_server procedure to create a server for performing FDW operations.
4. Query the external database.

postgres_fdw usage example

# [Create FDW server and connect user information]
SELECT * FROM create_fdw_server(
    'fdw_server',                                 -- Server name
    'pg_endpoint',                                -- External database endpoint
    'foreign_database',                           -- External database name
    5432,                                         -- External database connection port (numeric format)
    'test_database',                              -- User database to query the external database
    'admin',                                      -- User account ID
    'foreign_admin',                              -- External database account ID
    'foreign_pass'                                -- External database account password
);

# Example:
SELECT * FROM create_fdw_server(
    'db_nameserver',
    'db_name-pg-001.csnzphcqgi72.ap-northeast-2.rds.amazonaws.com',
    'postgres',
    5432,
    'grey',
    'admin',
    'remote_user',
    'remote_password'
);

# [Querying external database]
# Example query for a server created through create_fdw_server
SELECT srvname, srvtype, srvversion, srvoptions FROM pg_foreign_server;

# Example of mapping a remote table:
CREATE FOREIGN TABLE remote_table (
    id SERIAL,
    name TEXT,
    value NUMERIC
)
SERVER fdw_server_example -- FDW server name
OPTIONS (schema_name 'public', table_name 'remote_table_name');

pg_cron

pg_cron is an extension that enables cron-based job scheduling to enhance database maintenance and operational efficiency. In KakaoCloud, the default database postgres includes pg_cron, and the time zone for pg_cron is set to UTC±00:00.
When using pg_cron, the timezone is set to UTC±00:00.

Below are examples and procedures for using pg_cron:

1. Access the postgres database where pg_cron is configured.
2. Register a schedule in cron format.
3. Verify if the cron job executed at the scheduled time.
4. Delete the schedule.

pg_cron usage example

postgres=# \c postgres {Administrator/UserID}
You are now connected to database "postgres" as user "{Administrator/UserID}".

# Register a schedule: Create a schedule named 'weekly-vacuum' to run the VACUUM function on the 'test_database' every day at 4 AM (UTC)
postgres=> SELECT cron.schedule_in_database('weekly-vacuum', '* 4 * * *', 'VACUUM', 'test_database');

# View schedules
postgres=> SELECT * FROM cron.job;
 jobid |  schedule   | command | nodename  | nodeport | database | username | active |     jobname
-------+-------------+---------+-----------+----------+----------+----------+--------+------------------
     1 | 5 20 * * *  | VACUUM  | localhost |     5432 | db_name  | admin    | t      | weekly-vacuum

# Check schedule execution details
postgres=> SELECT * FROM cron.job_run_details;
 jobid | runid | job_pid | database | username | command |  status   | return_message |          start_time           |           end_time
-------+-------+---------+----------+----------+---------+-----------+----------------+-------------------------------+---------------------
     8 |     1 |  379928 | db_name  | postgres | VACUUM  | succeeded | VACUUM         | 2024-11-11 11:01:00.066025+00 | 2024-11-11 11:01:05.821539+00
(1 row)

# Remove a schedule
postgres=> SELECT cron.unschedule('weekly-vacuum');
 unschedule
------------
 t
(1 row)