Overview of KubeBlocks PostgreSQL Addon

PostgreSQL is a powerful, open-source relational database management system known for its:

• Scalability for handling large datasets
• Robust security features
• Extensive customization options
• Support for diverse applications

The KubeBlocks PostgreSQL Addon provides a complete solution for deploying and managing PostgreSQL clusters in Kubernetes environments. Key features include:

• Flexible deployment topologies
• Comprehensive lifecycle management
• Reliable backup and restore operations
• Support for multiple PostgreSQL versions

Key Features

Lifecycle Management

KubeBlocks simplifies PostgreSQL operations with comprehensive lifecycle management:

Backup and Restore

KubeBlocks supports multiple backup strategies for PostgreSQL:

Pre-installed Extensions

The KubeBlocks PostgreSQL image ships with the following extensions pre-installed. Most can be activated with CREATE EXTENSION <name> without any additional installation steps.

AI / Vector

pgvector adds a vector data type and similarity search operators to PostgreSQL. Store embedding vectors generated by AI models and run approximate nearest-neighbor (ANN) searches with IVFFlat or HNSW indexes — the foundation for semantic search and RAG pipelines directly inside your database.

CREATE EXTENSION vector;
CREATE TABLE items (id serial, embedding vector(1536));
SELECT * FROM items ORDER BY embedding <-> '[0.1, 0.2, ...]' LIMIT 10;

Geospatial

PostGIS is the industry-standard geospatial extension for PostgreSQL. It adds geometry and geography data types, spatial indexes (GiST), and hundreds of functions for distance calculations, intersection tests, buffer zones, and coordinate transforms — essential for any application working with location data.

CREATE EXTENSION postgis;
SELECT ST_Distance(
  ST_MakePoint(116.4, 39.9)::geography,  -- Beijing
  ST_MakePoint(121.5, 31.2)::geography   -- Shanghai
);

Time-series

TimescaleDB extends PostgreSQL with hypertables — automatically partitioned tables optimized for time-ordered inserts and range queries. It also provides continuous aggregates (auto-refreshing materialized views), data retention policies, and native compression for historical data. Best suited for monitoring metrics, IoT sensor data, and financial tick data.

CREATE EXTENSION timescaledb;
CREATE TABLE metrics (time TIMESTAMPTZ NOT NULL, value DOUBLE PRECISION);
SELECT create_hypertable('metrics', 'time');

Analytics

pg_duckdb embeds the DuckDB OLAP engine inside PostgreSQL. Complex aggregations and analytical queries run through DuckDB's vectorized execution engine, which can be orders of magnitude faster than PostgreSQL's row-based executor for wide-table scans. Available on PG14 and later (not available on PG12). Note that the bundled version varies significantly by PostgreSQL major version: 0.3.0 on PG14–PG17, 1.1.0 on PG18.

CREATE EXTENSION pg_duckdb;
SELECT region, sum(revenue) FROM orders GROUP BY region;  -- runs via DuckDB

roaringbitmap provides a compressed bitmap data type optimized for large integer set operations. Intersections, unions, and cardinality counts on sets of millions of user IDs run in milliseconds. Ideal for audience segmentation, permission systems, and analytics pre-aggregation. Available on PG18 only — not present in PG12–PG17.

CREATE EXTENSION roaringbitmap;
SELECT rb_and(bitmap_active, rb_and(bitmap_female, bitmap_beijing))
FROM user_segments;

Search

pg_trgm enables trigram-based fuzzy text search. Its key benefit is making LIKE '%keyword%' queries index-friendly via GiST or GIN indexes, eliminating full-table scans. It also provides a similarity() function for ranking results by how closely strings match.

CREATE EXTENSION pg_trgm;
CREATE INDEX ON products USING gin(name gin_trgm_ops);
SELECT name, similarity(name, 'iphone') AS score
FROM products WHERE name % 'iphone' ORDER BY score DESC;

Observability

pg_stat_statements records cumulative execution statistics — call count, total and mean execution time, rows returned — for every distinct SQL statement. It is the primary tool for identifying slow queries and is a dependency of most PostgreSQL monitoring systems. Enabled automatically; query the pg_stat_statements view directly.

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Security

pgaudit produces detailed audit logs of SQL activity beyond what PostgreSQL's standard logging captures. You can configure it to log specific statement classes (DDL, DML, SELECT, or all), specific roles, or specific objects — satisfying compliance requirements such as SOC 2, HIPAA, and PCI DSS.

CREATE EXTENSION pgaudit;
SET pgaudit.log = 'ddl, write';

Scheduling

pg_cron runs scheduled SQL tasks inside the database using standard cron syntax. No external scheduler or application code is needed — useful for recurring maintenance tasks like VACUUM, data archival, or report generation.

CREATE EXTENSION pg_cron;
SELECT cron.schedule('nightly-vacuum', '0 3 * * *', 'VACUUM ANALYZE orders');

Data Management

pg_partman automates partition lifecycle management for PostgreSQL native partitioned tables. It creates future partitions on a schedule, drops or detaches expired partitions based on a retention policy, and handles the bookkeeping that is otherwise error-prone when done manually. Works with both time-based and ID-based partition strategies.

CREATE EXTENSION pg_partman;
SELECT partman.create_parent('public.logs', 'created_at', 'native', 'daily');

Replication

pglogical implements logical replication at the table level, independent of PostgreSQL's built-in streaming replication. Key capabilities include selective table replication, cross-major-version replication (e.g., PG14 → PG17 for zero-downtime upgrades), and bidirectional or multi-master replication topologies.

CREATE EXTENSION pglogical;
-- On the provider node:
SELECT pglogical.create_node(node_name := 'provider', dsn := 'host=... dbname=mydb');
SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

Supported Versions

KubeBlocks PostgreSQL Addon supports these PostgreSQL versions:

The list of supported versions can be found by following command:

kubectl get cmpv postgresql