PostgreSQL Extensions Review & Recommendations

​

Executive Summary

1. Current extension usage patterns
2. Codebase analysis (full-text search, ILIKE queries, JSONB operations)
3. Multi-tenant healthcare ERP requirements
4. Performance optimization needs

• ✅ 10 extensions currently installed (pgcrypto, pg_stat_statements, pg_net, pg_cron, uuid-ossp, pg_graphql, pgmq, pg_trgm, unaccent, btree_gin)
• ✅ 3 high-priority extensions enabled (pg_trgm, unaccent, btree_gin) - Phase 1 Complete
• 📊 2 medium-priority extensions for future optimization (pg_partman, pg_jsonschema)
• 🔍 Full-text search patterns optimized - trigram indexes added

​

Currently Installed Extensions

​

High-Priority Recommendations

​

1. pg_trgm - Trigram Text Search ⭐⭐⭐

• Current Pattern: Extensive use of ILIKE '%pattern%' queries (found 25+ instances)
• Problem: ILIKE with leading wildcards cannot use indexes efficiently
• Solution: Trigram indexes enable fast fuzzy text matching

-- Current pattern (slow on large tables)
WHERE name ILIKE '%search%'
WHERE description ILIKE '%pattern%'

-- With pg_trgm (fast with index)
WHERE name % 'search'  -- Similarity operator
WHERE similarity(name, 'search') > 0.3

• hr_employees (name, job_title searches)
• hr_positions (title searches)
• pf_documents (title, content searches)
• fw_workflow_templates (name, description searches)
• it_knowledge_base_articles (title, content searches)
• All tables with full-text search indexes

-- Enable extension
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Add trigram indexes for common search columns
CREATE INDEX idx_hr_employees_name_trgm ON hr_employees USING gin (name gin_trgm_ops);
CREATE INDEX idx_hr_employees_job_title_trgm ON hr_employees USING gin (job_title gin_trgm_ops);
CREATE INDEX idx_pf_documents_title_trgm ON pf_documents USING gin (title gin_trgm_ops);

• Before: Sequential scan on ILIKE queries (slow on large tables)
• After: Index scan with trigram matching (10-100x faster)
• Storage: ~30% of indexed column size (acceptable trade-off)

1. Enable extension in migration
2. Add trigram indexes for high-traffic search columns
3. Update search queries to use % operator or similarity() function
4. Monitor query performance improvement

​

2. unaccent - Accent-Insensitive Search ⭐⭐⭐

• Healthcare Context: Names often contain accents (José, François, etc.)
• Current Problem: ILIKE searches won’t match “Jose” when searching for “José”
• Solution: Unaccent extension removes accents for search

-- Enable extension
CREATE EXTENSION IF NOT EXISTS unaccent;

-- Create unaccented search index
CREATE INDEX idx_hr_employees_name_unaccent 
ON hr_employees USING gin (unaccent(name) gin_trgm_ops);

-- Search query
WHERE unaccent(name) ILIKE unaccent('%jose%')

• hr_employees (first_name, last_name)
• pf_profiles (first_name, last_name)
• rh_residents (first_name, last_name)
• Any table with person names

• Minimal overhead (unaccent is fast)
• Significantly better search results for international names

​

3. btree_gin - GIN Indexes for Standard Types ⭐⭐

• Current Pattern: 363 tables with custom_fields JSONB columns
• Problem: Standard GIN indexes on JSONB can be large
• Solution: btree_gin allows composite indexes mixing JSONB with other types

-- Enable extension
CREATE EXTENSION IF NOT EXISTS btree_gin;

-- Composite index for organization_id + custom_fields queries
CREATE INDEX idx_hr_employees_org_custom_fields 
ON hr_employees USING gin (organization_id, custom_fields);

-- Query example
WHERE organization_id = '...' 
AND custom_fields @> '{"badge_number": "12345"}'

• Faster queries filtering by organization_id + custom_fields
• Smaller indexes than separate GIN indexes
• Better query planning for multi-tenant queries

​

4. pg_partman - Table Partitioning ⭐⭐

• Data Archival Plan: Identified need to archive old audit logs, activity logs
• Problem: Large tables (audit logs, workflow executions) will slow down over time
• Solution: Partition tables by date for easier archival and better query performance

• pf_audit_logs (partition by created_at monthly)
• pf_activity_logs (partition by created_at monthly)
• fw_workflow_executions (partition by created_at monthly)
• pf_notifications (partition by created_at monthly)

-- Enable extension
CREATE EXTENSION IF NOT EXISTS pg_partman;

-- Convert existing table to partitioned
SELECT partman.create_parent(
  p_parent_table => 'public.pf_audit_logs',
  p_control => 'created_at',
  p_type => 'range',
  p_interval => 'monthly',
  p_premake => 3
);

• Query Performance: Faster queries on recent data (only scan relevant partitions)
• Maintenance: Easy to drop old partitions (vs. DELETE operations)
• Storage: Better compression and index efficiency per partition

1. Review data archival plan (docs/migration/DATA_ARCHIVAL_PLAN.md)
2. Plan partition strategy (monthly vs. quarterly)
3. Test partitioning on staging environment
4. Migrate production tables during maintenance window

​

5. pg_jsonschema - JSONB Validation ⭐

• Current Pattern: 363 tables with custom_fields JSONB DEFAULT '{}'
• Problem: No validation of JSONB structure (could store invalid data)
• Solution: Validate custom_fields against JSON Schema

-- Enable extension
CREATE EXTENSION IF NOT EXISTS pg_jsonschema;

-- Add validation constraint
ALTER TABLE hr_employees
ADD CONSTRAINT hr_employees_custom_fields_schema
CHECK (jsonb_matches_schema(
  '{
    "type": "object",
    "properties": {
      "badge_number": {"type": "string"},
      "external_id": {"type": "string"}
    },
    "additionalProperties": true
  }'::jsonb,
  custom_fields
));

• Minimal (validation only on INSERT/UPDATE)
• Prevents invalid data from being stored
• Better data quality for reporting

• May be too restrictive if custom_fields structure varies significantly
• Consider making validation optional per organization

​

Medium-Priority Recommendations

​

6. citext - Case-Insensitive Text Type

• Many queries use LOWER(column) = LOWER(value) or ILIKE
• citext type handles case-insensitivity at the type level

• Not recommended for existing tables (would require column type changes)
• Consider for new tables where case-insensitivity is required
• Current ILIKE pattern is acceptable and doesn’t require migration

-- For new tables only
CREATE TABLE example (
  id UUID PRIMARY KEY,
  email citext NOT NULL UNIQUE  -- Case-insensitive email
);

​

7. ltree - Hierarchical Tree Structures

• Could be useful for organizational hierarchies, category trees
• More efficient than recursive CTEs for tree queries

• Only if tree structures are needed
• Current patterns don’t show hierarchical data requirements
• Defer until needed

​

8. tcn - Triggered Change Notifications

• Current Pattern: Event-based integration for workflows (FW-16)
• Problem: Manual event publishing via triggers
• Solution: Automatic change notifications via tcn extension

-- Enable extension
CREATE EXTENSION IF NOT EXISTS tcn;

-- Add trigger for change notifications
CREATE TRIGGER hr_employee_changes
AFTER INSERT OR UPDATE OR DELETE ON hr_employees
FOR EACH ROW EXECUTE FUNCTION triggered_change_notification();

• May duplicate existing event publishing logic
• Review FW-16 event integration patterns before implementing
• Defer until event system review

​

Low-Priority / Not Recommended

​

Extensions to Avoid

​

Implementation Plan

​

Phase 1: Immediate (Week 1-2) ✅ COMPLETE

1. ✅ pg_trgm - Enabled with trigram indexes for high-traffic search columns
2. ✅ unaccent - Enabled with unaccented search indexes for name columns
3. ✅ btree_gin - Enabled for composite index optimization

-- Migration: Enable high-priority extensions
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE EXTENSION IF NOT EXISTS btree_gin;

-- Add trigram indexes for common search patterns
-- (See Phase 1 implementation script)

• Verify search performance improvement
• Test accent-insensitive searches
• Monitor index sizes

​

Phase 2: Optimization (Month 2-3)

• Monitor table sizes monthly
• Review data archival plan progress
• Assess custom_fields data quality issues

​

Phase 3: Future (As Needed)

​

Migration Script Template

-- ============================================================================
-- PostgreSQL Extensions - Phase 1 Implementation
-- ============================================================================
-- Enable high-priority extensions for text search and JSONB optimization
-- Date: 2026-01-16
-- ============================================================================

-- ----------------------------------------------------------------------------
-- 1. Enable Extensions
-- ----------------------------------------------------------------------------

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE EXTENSION IF NOT EXISTS btree_gin;

-- ----------------------------------------------------------------------------
-- 2. Add Trigram Indexes for High-Traffic Search Columns
-- ----------------------------------------------------------------------------

-- HR Module
CREATE INDEX IF NOT EXISTS idx_hr_employees_name_trgm 
ON hr_employees USING gin (name gin_trgm_ops)
WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_hr_employees_job_title_trgm 
ON hr_employees USING gin (job_title gin_trgm_ops)
WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_hr_positions_title_trgm 
ON hr_positions USING gin (title gin_trgm_ops)
WHERE deleted_at IS NULL;

-- Platform Module
CREATE INDEX IF NOT EXISTS idx_pf_documents_title_trgm 
ON pf_documents USING gin (title gin_trgm_ops);

CREATE INDEX IF NOT EXISTS idx_pf_profiles_name_trgm 
ON pf_profiles USING gin (
  (first_name || ' ' || last_name) gin_trgm_ops
);

-- Forms & Workflow Module
CREATE INDEX IF NOT EXISTS idx_fw_workflow_templates_name_trgm 
ON fw_workflow_templates USING gin (name gin_trgm_ops);

-- IT Module
CREATE INDEX IF NOT EXISTS idx_it_knowledge_base_articles_title_trgm 
ON it_knowledge_base_articles USING gin (title gin_trgm_ops);

-- ----------------------------------------------------------------------------
-- 3. Add Unaccented Search Indexes for Name Columns
-- ----------------------------------------------------------------------------

CREATE INDEX IF NOT EXISTS idx_hr_employees_name_unaccent_trgm 
ON hr_employees USING gin (unaccent(name) gin_trgm_ops)
WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_pf_profiles_name_unaccent_trgm 
ON pf_profiles USING gin (
  unaccent(first_name || ' ' || last_name) gin_trgm_ops
);

-- ----------------------------------------------------------------------------
-- 4. Add Composite GIN Indexes (btree_gin) for Multi-Tenant + JSONB Queries
-- ----------------------------------------------------------------------------

-- Example: If queries filter by organization_id + custom_fields
-- CREATE INDEX IF NOT EXISTS idx_hr_employees_org_custom_fields 
-- ON hr_employees USING gin (organization_id, custom_fields)
-- WHERE deleted_at IS NULL;

-- ----------------------------------------------------------------------------
-- 5. Comments
-- ----------------------------------------------------------------------------

COMMENT ON EXTENSION pg_trgm IS 
  'Trigram text search for fast fuzzy matching (replaces slow ILIKE queries)';

COMMENT ON EXTENSION unaccent IS 
  'Accent-insensitive text search for international names';

COMMENT ON EXTENSION btree_gin IS 
  'GIN indexes for standard types, enables composite indexes with JSONB';

​

Performance Monitoring

​

Before/After Metrics

1. Search Query Performance:
   -- Before: ILIKE query execution time
   EXPLAIN ANALYZE SELECT * FROM hr_employees 
   WHERE name ILIKE '%john%';
   
   -- After: Trigram query execution time
   EXPLAIN ANALYZE SELECT * FROM hr_employees 
   WHERE name % 'john';
   
2. Index Sizes:
   SELECT 
     schemaname,
     tablename,
     indexname,
     pg_size_pretty(pg_relation_size(indexrelid)) as index_size
   FROM pg_stat_user_indexes
   WHERE indexname LIKE '%_trgm'
   ORDER BY pg_relation_size(indexrelid) DESC;
   
3. Index Usage:
   SELECT 
     schemaname,
     tablename,
     indexrelname,
     idx_scan,
     idx_tup_read,
     idx_tup_fetch
   FROM pg_stat_user_indexes
   WHERE indexname LIKE '%_trgm'
   ORDER BY idx_scan DESC;
   

​

Rollback Plan

-- Remove indexes first
DROP INDEX IF EXISTS idx_hr_employees_name_trgm;
DROP INDEX IF EXISTS idx_hr_employees_name_unaccent_trgm;
-- ... (remove all trigram indexes)

-- Drop extensions (only if necessary)
-- DROP EXTENSION IF EXISTS pg_trgm;
-- DROP EXTENSION IF EXISTS unaccent;
-- DROP EXTENSION IF EXISTS btree_gin;

​

References

​

Project Documentation

• Performance Recommendations: docs/migration/SUPABASE_PERFORMANCE_RECOMMENDATIONS.md
• Data Archival Plan: docs/migration/DATA_ARCHIVAL_PLAN.md
• Search Framework: specs/pf/specs/PF-57-search-filter-sort-framework.md
• Database Schema: docs/architecture/DATABASE_SCHEMA.md

​

External Resources

• pg_trgm Documentation: https://www.postgresql.org/docs/current/pgtrgm.html
• unaccent Documentation: https://www.postgresql.org/docs/current/unaccent.html
• btree_gin Documentation: https://www.postgresql.org/docs/current/btree-gin.html
• pg_partman Documentation: https://github.com/pgpartman/pg_partman

​

Summary

1. ✅ pg_trgm - HIGH - Fast fuzzy text search (replaces slow ILIKE)
2. ✅ unaccent - HIGH - Accent-insensitive name searches
3. ✅ btree_gin - HIGH - Composite indexes for JSONB + standard types
4. ⏳ pg_partman - HIGH (Future) - Table partitioning for large audit logs
5. ⏳ pg_jsonschema - MEDIUM - JSONB validation (if needed)

• Search Performance: 10-100x faster text searches
• User Experience: Better search results (accent-insensitive, fuzzy matching)
• Database Performance: Better index utilization, smaller query times