“

Series: S8 Technology Deep Dives · Article 12 | Level: Advanced | Reading Time: 20 min

PostgreSQL Metadata Storage: The Data Foundation of the Ontology Platform

#TL;DR

PostgreSQL serves as the core metadata store in the Ontology-driven intelligent decision platform. This article deeply explores table design, indexing strategies, flexible JSONB usage, multi-tenant isolation, version management, and coordination with the Redis caching layer across scenarios including Schema Registry, Object Type definitions, Link Type relationships, Property Type attributes, and audit logging. Starting from modeling theory and incorporating production performance tuning experience, we build a comprehensive PostgreSQL best-practices framework tailored for the Ontology platform.

#1. Introduction: Why PostgreSQL

#1.1 Core Advantages of PostgreSQL

PostgreSQL's selection as the metadata store for the Ontology platform is deliberate. Its core advantages manifest in several key areas:

Native JSONB Support: Ontology metadata is inherently semi-structured. The schema for Object Type attribute definitions, constraints, and UI configurations evolves continuously with business requirements. PostgreSQL's JSONB type provides a perfect balance between relational database ACID guarantees and NoSQL flexibility.

Powerful Index System: Multiple index types including B-tree, Hash, GIN, GiST, and BRIN enable precise optimization for different query patterns. GIN index support for JSONB fields in particular allows us to achieve efficient query performance without sacrificing flexibility.

Transactions and Concurrency Control: The MVCC (Multi-Version Concurrency Control) mechanism ensures that read and write operations do not block each other. In the Ontology platform, metadata reads are far more frequent than writes, and MVCC's characteristics ensure read operations are virtually unaffected by writes.

Extension Ecosystem: Extensions like pg_trgm (fuzzy search), pg_partman (automatic partitioning), and pgvector (vector search) enable PostgreSQL to handle various specialized needs without introducing additional middleware.

#1.2 Ontology Metadata Characteristics

The Ontology platform's metadata exhibits the following characteristics:

• Hierarchical structure: Namespace, Object Type, Property Type, and Constraint form multiple levels
• Read-heavy, write-light: Schema changes are infrequent operations, but Schema queries are high-frequency operations
• Version management required: Every Schema change must preserve historical versions for rollback support
• Cross-tenant isolation: Different tenants' metadata must be strictly isolated
• Semi-structured attributes: Core fields are fixed, but extension attributes are flexible and variable

These characteristics determine our table design strategy: core fields use strongly-typed columns, extension attributes use JSONB columns, complemented by carefully designed index and partition strategies.

#2. Schema Registry Design

#2.1 Core Table Structure

The Schema Registry is the metadata hub of the Ontology platform. We adopt a star schema design with object_types at the center, associated with dimension tables like property_types, link_types, and constraints.

CREATE TABLE namespaces (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    rid TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    tenant_id UUID NOT NULL,
    description TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    created_by TEXT NOT NULL,
    metadata JSONB DEFAULT '{}'::jsonb
);

CREATE TABLE object_types (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    rid TEXT NOT NULL,
    namespace_id UUID NOT NULL REFERENCES namespaces(id),
    api_name TEXT NOT NULL,
    display_name TEXT NOT NULL,
    description TEXT,
    version INTEGER NOT NULL DEFAULT 1,
    status TEXT NOT NULL DEFAULT 'DRAFT',
    primary_key_property_rid TEXT,
    title_property_rid TEXT,
    schema_definition JSONB NOT NULL DEFAULT '{}'::jsonb,
    ui_config JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    created_by TEXT NOT NULL,
    UNIQUE(rid, version)
);

CREATE TABLE property_types (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    rid TEXT NOT NULL,
    object_type_id UUID NOT NULL REFERENCES object_types(id) ON DELETE CASCADE,
    api_name TEXT NOT NULL,
    display_name TEXT NOT NULL,
    data_type TEXT NOT NULL,
    description TEXT,
    is_required BOOLEAN NOT NULL DEFAULT false,
    is_indexed BOOLEAN NOT NULL DEFAULT false,
    is_unique BOOLEAN NOT NULL DEFAULT false,
    default_value JSONB,
    constraints JSONB DEFAULT '[]'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE link_types (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    rid TEXT NOT NULL UNIQUE,
    api_name TEXT NOT NULL,
    display_name TEXT NOT NULL,
    source_object_type_id UUID NOT NULL REFERENCES object_types(id),
    target_object_type_id UUID NOT NULL REFERENCES object_types(id),
    cardinality TEXT NOT NULL DEFAULT 'MANY_TO_MANY',
    description TEXT,
    properties JSONB DEFAULT '[]'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

#2.2 RID (Resource Identifier) Design

The Ontology platform uses hierarchical RIDs as globally unique identifiers. The RID format is:

ri.onto.{namespace}.{resource_type}.{name}

For example: ri.onto.main.object-type.Employee

RIDs are stored in TEXT-type columns rather than UUIDs because RIDs need human readability and hierarchical structure information. We create B-tree indexes on RID columns and leverage PostgreSQL's prefix matching optimization to accelerate namespace-based range queries.

#2.3 JSONB Flexible Attributes

The schema_definition and ui_config fields use JSONB type, storing extension attributes whose schemas cannot be predefined:

{
  "schema_definition": {
    "visibility": "PROMINENT",
    "searchable": true,
    "interfaces": ["Actionable", "Timeseries"],
    "custom_validations": [
      {"type": "regex", "field": "email", "pattern": "^[\\w.-]+@[\\w.-]+\\.\\w+$"}
    ]
  },
  "ui_config": {
    "icon": "person",
    "color": "#4A90D9",
    "default_view": "table",
    "hidden_properties": ["internal_id"]
  }
}

For efficient JSONB field queries, we create GIN indexes:

CREATE INDEX idx_object_types_schema ON object_types USING GIN (schema_definition jsonb_path_ops);
CREATE INDEX idx_object_types_ui ON object_types USING GIN (ui_config jsonb_path_ops);

The jsonb_path_ops operator class is more compact than the default GIN operator class and supports fast @> containment operator queries.

#3. Deep Index Strategy Optimization

#3.1 B-tree Index Best Practices

B-tree is PostgreSQL's default index type, suitable for equality and range queries. For metadata tables, we create B-tree indexes on high-frequency query fields:

CREATE INDEX idx_object_types_namespace ON object_types(namespace_id);
CREATE INDEX idx_object_types_status ON object_types(status) WHERE status = 'ACTIVE';
CREATE INDEX idx_property_types_object ON property_types(object_type_id);
CREATE INDEX idx_link_types_source ON link_types(source_object_type_id);
CREATE INDEX idx_link_types_target ON link_types(target_object_type_id);

Partial indexes are a powerful optimization tool. The idx_object_types_status above only indexes rows where status = 'ACTIVE', because most queries only concern active Object Types. This dramatically reduces index size and maintenance overhead.

#3.2 Composite Index Design

Column order in composite indexes is critical. Follow the principle of "equality conditions first, range conditions second":

CREATE INDEX idx_object_types_tenant_status ON object_types(namespace_id, status, updated_at DESC);

This index optimizes the high-frequency query pattern: "find all active Object Types in a namespace, sorted by update time."

#3.3 Covering Indexes

PostgreSQL 11's INCLUDE clause allows non-key columns in indexes, enabling Index-Only Scans:

CREATE INDEX idx_object_types_listing ON object_types(namespace_id, status)
    INCLUDE (rid, api_name, display_name, updated_at);

This covering index allows listing queries to retrieve all data entirely from the index without heap table access, significantly reducing I/O operations.

#3.4 GIN Index Optimization

For JSONB field queries, GIN indexes are essential. However, GIN index maintenance costs are high. We optimize through several strategies:

• fastupdate parameter: Enable GIN's fastupdate feature, deferring index updates to vacuum or batch execution when memory fills
• gin_pending_list_limit: Control pending list size, balancing write and query performance
• Selective indexing: Create expression indexes only for JSONB paths that need querying

CREATE INDEX idx_object_types_visibility ON object_types
    ((schema_definition->>'visibility'));
CREATE INDEX idx_object_types_searchable ON object_types
    ((schema_definition->>'searchable'))
    WHERE (schema_definition->>'searchable')::boolean = true;

#4. Multi-Tenant Isolation Strategy

#4.1 Schema-Based Isolation

For scenarios requiring strong isolation, we use PostgreSQL's Schema feature for tenant isolation:

CREATE SCHEMA tenant_acme;
CREATE TABLE tenant_acme.object_types (LIKE public.object_types INCLUDING ALL);

Each tenant has an independent Schema containing the complete table structure. By setting search_path, applications transparently access the corresponding tenant's data:

async def set_tenant_context(conn, tenant_id: str):
    schema_name = f"tenant_{tenant_id}"
    await conn.execute(f"SET search_path TO {schema_name}, public")

#4.2 Row-Level Security (RLS)

For scenarios needing shared table structures with data isolation, we use PostgreSQL's Row-Level Security policies:

ALTER TABLE object_types ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON object_types
    USING (namespace_id IN (
        SELECT id FROM namespaces WHERE tenant_id = current_setting('app.tenant_id')::uuid
    ));

RLS enforces tenant isolation at the database level. Even if application-layer bugs exist, cross-tenant data access is impossible. This Defense in Depth is crucial in PaaS platforms.

#4.3 Hybrid Isolation Model

In actual deployment, we adopt a hybrid isolation model: core metadata uses Schema isolation (highest security level), audit logs and statistics use RLS isolation (balancing performance and security), and shared configuration data resides in the public Schema.

#5. Version Management and Auditing

#5.1 Schema Version Control

Schema changes in the Ontology platform must be traceable. We implement a complete version management mechanism:

CREATE TABLE schema_versions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    object_type_rid TEXT NOT NULL,
    version INTEGER NOT NULL,
    schema_snapshot JSONB NOT NULL,
    change_description TEXT,
    changed_by TEXT NOT NULL,
    changed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    change_type TEXT NOT NULL,
    diff_from_previous JSONB,
    UNIQUE(object_type_rid, version)
);

On every Schema change, we update the object_types table and insert a schema_versions record within the same transaction. The diff_from_previous field stores the diff from the previous version for quick understanding of changes.

#5.2 Audit Logging

Audit logs capture all metadata change operations. We use PostgreSQL triggers for automatic change capture:

CREATE TABLE audit_log (
    id BIGSERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    record_id UUID NOT NULL,
    action TEXT NOT NULL,
    old_data JSONB,
    new_data JSONB,
    changed_by TEXT NOT NULL,
    changed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    client_ip INET,
    request_id TEXT
);

CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (table_name, record_id, action, old_data, new_data, changed_by)
    VALUES (
        TG_TABLE_NAME,
        COALESCE(NEW.id, OLD.id),
        TG_OP,
        CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN to_jsonb(OLD) END,
        CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN to_jsonb(NEW) END,
        current_setting('app.current_user', true)
    );
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

#5.3 Time-Travel Queries

Based on version management and audit logs, we can implement time-travel queries to view Schema state at any point in time:

SELECT schema_snapshot
FROM schema_versions
WHERE object_type_rid = $1
  AND changed_at <= $2
ORDER BY version DESC
LIMIT 1;

This capability is extremely useful when troubleshooting production issues: we can precisely determine whether a specific Schema change is related to a problem.

#6. Query Performance Optimization

#6.1 Query Plan Analysis

PostgreSQL's EXPLAIN ANALYZE is the core tool for performance optimization. We establish performance baselines for all critical queries:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ot.rid, ot.api_name, ot.display_name, ot.version,
       array_agg(pt.api_name) as properties
FROM object_types ot
LEFT JOIN property_types pt ON pt.object_type_id = ot.id
WHERE ot.namespace_id = $1 AND ot.status = 'ACTIVE'
GROUP BY ot.id
ORDER BY ot.updated_at DESC
LIMIT 50;

Focus on these indicators:

• Seq Scan vs Index Scan: Sequential scans indicate missing indexes or queries unsuitable for existing indexes
• Buffers: shared hits mean cache hits; shared reads mean disk reads
• Rows estimated vs actual: Large discrepancies indicate stale statistics requiring ANALYZE

#6.2 Join Query Optimization

When loading complete Object Type information, multiple table joins are required. We use CTEs (Common Table Expressions) and lateral joins to optimize complex queries:

WITH active_types AS (
    SELECT id, rid, api_name, display_name, version, schema_definition
    FROM object_types
    WHERE namespace_id = $1 AND status = 'ACTIVE'
)
SELECT
    at.*,
    (SELECT jsonb_agg(jsonb_build_object(
        'rid', pt.rid,
        'api_name', pt.api_name,
        'data_type', pt.data_type,
        'is_required', pt.is_required
    )) FROM property_types pt WHERE pt.object_type_id = at.id) as properties,
    (SELECT jsonb_agg(jsonb_build_object(
        'rid', lt.rid,
        'api_name', lt.api_name,
        'target_type', lt.target_object_type_id
    )) FROM link_types lt WHERE lt.source_object_type_id = at.id) as outgoing_links
FROM active_types at;

#6.3 Connection Pool Configuration

PostgreSQL connection creation is expensive (approximately 100ms). We use PgBouncer as a connection pool proxy:

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 300

Transaction mode releases connections immediately after transaction completion, maximizing connection reuse. For OLTP scenarios with few long transactions, this is the optimal choice.

#7. Partitioning Strategy

#7.1 Time-Based Partitioning for Audit Logs

Audit logs are partitioned by month, ensuring old data can be efficiently archived and cleaned:

CREATE TABLE audit_log (
    id BIGSERIAL,
    table_name TEXT NOT NULL,
    record_id UUID NOT NULL,
    action TEXT NOT NULL,
    old_data JSONB,
    new_data JSONB,
    changed_by TEXT NOT NULL,
    changed_at TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY RANGE (changed_at);

CREATE TABLE audit_log_2026_01 PARTITION OF audit_log
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE audit_log_2026_02 PARTITION OF audit_log
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

Using the pg_partman extension automates partition creation and management:

SELECT partman.create_parent('public.audit_log', 'changed_at', 'native', 'monthly');

#7.2 Data Partitioning for Large Tenants

For tenants with especially large data volumes, we can further partition by Object Type within the tenant Schema:

CREATE TABLE tenant_bigcorp.objects (
    id UUID NOT NULL,
    object_type_rid TEXT NOT NULL,
    data JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY LIST (object_type_rid);

This two-level partition strategy (tenant then Object Type) ensures highly effective partition pruning during queries.

#8. Coordination with Redis Cache

#8.1 Cache Consistency Guarantees

Cache consistency between PostgreSQL and Redis is a classic distributed systems problem. We use the "update database first, then invalidate cache" pattern:

async def update_object_type(type_rid: str, updates: dict) -> ObjectType:
    async with db.transaction():
        updated = await db.update_object_type(type_rid, updates)
        await db.create_schema_version(type_rid, updated)
    await redis.delete(f"ontology:object_type:{type_rid}")
    await redis.publish("schema_changes", json.dumps({
        "type": "object_type_updated",
        "rid": type_rid,
        "version": updated.version
    }))
    return updated

#8.2 Cache Warming Strategy

After system startup or Redis failure recovery, cache warming is needed to avoid database pressure from cold starts:

async def warm_cache():
    hot_types = await db.query("""
        SELECT ot.* FROM object_types ot
        JOIN access_stats ast ON ast.object_type_id = ot.id
        WHERE ot.status = 'ACTIVE'
        ORDER BY ast.access_count DESC
        LIMIT 1000
    """)
    pipeline = redis.pipeline()
    for ot in hot_types:
        key = f"ontology:object_type:{ot.rid}"
        pipeline.setex(key, 3600, ot.model_dump_json())
    await pipeline.execute()

#8.3 Change Propagation Mechanism

When Schema changes occur, all dependents must be notified to invalidate their caches. We use a combination of PostgreSQL LISTEN/NOTIFY and Redis Pub/Sub:

CREATE OR REPLACE FUNCTION notify_schema_change()
RETURNS TRIGGER AS $$
BEGIN
    PERFORM pg_notify('schema_changes', json_build_object(
        'table', TG_TABLE_NAME,
        'action', TG_OP,
        'rid', NEW.rid,
        'version', NEW.version
    )::text);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER schema_change_trigger
    AFTER INSERT OR UPDATE ON object_types
    FOR EACH ROW EXECUTE FUNCTION notify_schema_change();

The application layer listens to PostgreSQL notifications and forwards them to Redis Pub/Sub, ensuring all nodes receive timely change notifications.

#9. Backup and Recovery

#9.1 Logical Backup

For critical data like metadata, we perform daily logical backups:

pg_dump --format=custom --compress=9 \
  --file="metadata_$(date +%Y%m%d).dump" \
  --schema=public \
  --table='namespaces|object_types|property_types|link_types|schema_versions' \
  ontology_db

#9.2 WAL-Based Continuous Archiving

WAL (Write-Ahead Log) archiving provides Point-in-Time Recovery (PITR) capability:

archive_mode = on
archive_command = 'cp %p /archive/wal/%f'

Combined with base backups and WAL archiving, we can recover to any point-in-time data state. This is critical in accidental operation recovery scenarios.

#9.3 Cross-Region Replication

For disaster recovery needs, we use PostgreSQL's streaming replication for cross-region data synchronization:

primary_conninfo = 'host=primary.region-a.internal port=5432 user=repl'
restore_command = 'cp /archive/wal/%f %p'

Asynchronous replication mode typically has millisecond-level delays under normal conditions, but may cause data loss during network failures. For critical data like metadata, we configure synchronous replication to ensure at least one standby confirms before committing transactions.

#10. Production Environment Tuning

#10.1 Key Parameter Configuration

# Memory
shared_buffers = 8GB          # 25% of physical memory
effective_cache_size = 24GB   # 75% of physical memory
work_mem = 64MB               # Sort memory for complex queries
maintenance_work_mem = 2GB    # Memory for VACUUM, CREATE INDEX

# WAL
wal_level = replica
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9

# Concurrency
max_connections = 200
max_worker_processes = 8
max_parallel_workers_per_gather = 4

# Query Planning
random_page_cost = 1.1        # SSD storage
effective_io_concurrency = 200 # SSD storage

#10.2 VACUUM Strategy

PostgreSQL's MVCC mechanism produces dead tuples that need VACUUM cleanup. For metadata tables (low write volume), default autovacuum parameters suffice. For audit log tables (write-intensive), more aggressive configuration is needed:

ALTER TABLE audit_log SET (
    autovacuum_vacuum_threshold = 1000,
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_threshold = 500,
    autovacuum_analyze_scale_factor = 0.005
);

#10.3 Monitoring Metrics

#Key Takeaways

1. JSONB is a metadata storage powerhouse — it achieves a perfect balance between relational ACID guarantees and semi-structured flexibility.
2. Index design determines query performance — the combined use of partial indexes, covering indexes, and GIN indexes can reduce query latency by an order of magnitude.
3. Multi-tenant isolation needs multiple layers — Schema isolation plus RLS provides complete security from application layer to database layer.
4. Version management is the foundation of Schema evolution — every change must create a version snapshot, supporting auditing and rollback.
5. Cache coordination requires careful design — the combination of PostgreSQL LISTEN/NOTIFY and Redis Pub/Sub achieves efficient change propagation.

#Next Article

The next article, S8-13: Spring Boot + gRPC Best Practices, will deep dive into the integration of Spring Boot and gRPC in the Control Layer, including service definition, interceptor chains, error handling, load balancing, and coordinated design with Protobuf.

tags: [postgresql, metadata, jsonb, indexing, multi-tenant, rls, schema-registry, audit, ontology-paas, S8]