Database Performance Guide

Optimization strategies, indexing patterns, and query best practices for Axiom Genesis databases.

Overview

Axiom Genesis databases are designed for multi-tenant, high-concurrency workloads. This guide covers performance optimization across both MSSQL and PostgreSQL.

Index Strategy

Multi-Tenant Index Pattern

Every table should have indexes that include tenant_id as the leading column:

-- Primary lookup pattern: tenant + primary key
CREATE INDEX idx_users_tenant_id
ON app_users(usr_tenant_id, usr_id)
WHERE usr_is_deleted = 0;

-- Secondary lookup pattern: tenant + frequently queried column
CREATE INDEX idx_users_tenant_email
ON app_users(usr_tenant_id, usr_email_id)
WHERE usr_is_deleted = 0;

Filtered Indexes

Use filtered indexes to exclude soft-deleted records:

-- MSSQL - Filtered index
CREATE INDEX idx_users_active
ON app_users(usr_tenant_id, usr_status)
WHERE usr_is_deleted = 0;

-- PostgreSQL - Partial index
CREATE INDEX idx_users_active
ON app_users(usr_tenant_id, usr_status)
WHERE usr_is_deleted = 0;

JSON Indexes

For frequently queried JSON paths:

-- MSSQL 2022+ - Computed column + index
ALTER TABLE app_page_configs
ADD pgc_page_type AS JSON_VALUE(pgc_config, '$.pageType');

CREATE INDEX idx_page_type
ON app_page_configs(pgc_tenant_id, pgc_page_type)
WHERE pgc_is_deleted = 0;

-- PostgreSQL - GIN index on JSONB
CREATE INDEX idx_page_config_gin
ON app_page_configs USING GIN (pgc_config);

-- PostgreSQL - Expression index for specific path
CREATE INDEX idx_page_type
ON app_page_configs((pgc_config->>'pageType'))
WHERE pgc_is_deleted = 0;

Query Optimization

Always Include Tenant Filter

-- ✅ Good - Tenant filter enables index usage
SELECT * FROM app_users
WHERE usr_tenant_id = @tenantId
  AND usr_status = 1
  AND usr_is_deleted = 0;

-- ❌ Bad - Full table scan
SELECT * FROM app_users
WHERE usr_status = 1;

Use Appropriate Data Types

-- ✅ Good - Use BIGINT for ID parameters
.input('userId', sql.BigInt, userId)

-- ❌ Bad - Implicit conversion causes index scan
.input('userId', sql.VarChar, userId.toString())

Limit Result Sets

-- ✅ Good - Paginated query
SELECT TOP 50 * FROM app_users
WHERE usr_tenant_id = @tenantId
  AND usr_is_deleted = 0
ORDER BY usr_id
OFFSET @offset ROWS;

-- ❌ Bad - Unbounded query
SELECT * FROM app_users
WHERE usr_tenant_id = @tenantId;

Avoid SELECT *

-- ✅ Good - Explicit columns
SELECT usr_id, usr_email_id, usr_first_name, usr_last_name
FROM app_users
WHERE usr_tenant_id = @tenantId;

-- ❌ Bad - Returns unnecessary data
SELECT * FROM app_users;

Connection Pool Optimization

MSSQL Configuration

// config/db/db_connections.js
const poolConfig = {
  min: 10,                    // Minimum connections
  max: 100,                   // Maximum connections
  idleTimeoutMillis: 30000,   // Close idle connections after 30s
  acquireTimeoutMillis: 60000, // Wait 60s for available connection
  createTimeoutMillis: 30000,  // Connection creation timeout
  destroyTimeoutMillis: 5000,  // Connection destruction timeout
  reapIntervalMillis: 1000,    // Check for idle connections every 1s
};

PostgreSQL Configuration

const poolConfig = {
  min: 10,
  max: 100,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 60000,
  statement_timeout: 30000,    // Query timeout
};

Caching Strategy

Redis Cache Patterns

// Entity registry caching (24-hour TTL)
const CACHE_TTL = 86400;

async function getEntityInfo(entityCode) {
  const cacheKey = `obj_registry:${entityCode}`;
  
  // Try cache first
  let cached = await redis.get(cacheKey);
  if (cached) return JSON.parse(cached);
  
  // Fall back to database
  const result = await db.query(`
    SELECT * FROM app_object_registries
    WHERE obj_code = @entityCode
  `);
  
  // Cache result
  await redis.setex(cacheKey, CACHE_TTL, JSON.stringify(result));
  return result;
}

Cache Invalidation

// Invalidate on update
async function updateEntityRegistry(entityCode, updates) {
  await db.query('UPDATE app_object_registries SET ...');
  
  // Clear cache
  await redis.del(`obj_registry:${entityCode}`);
}

Query Analysis

MSSQL Query Plan

-- Enable execution plan
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Your query here
SELECT * FROM app_users WHERE usr_tenant_id = 1;

-- Disable
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

PostgreSQL Query Plan

-- Analyze query
EXPLAIN ANALYZE
SELECT * FROM app_users WHERE usr_tenant_id = 1;

-- With buffer information
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM app_users WHERE usr_tenant_id = 1;

Common Issues

Issue	Symptom	Solution
Missing index	Table scan in plan	Add appropriate index
Parameter sniffing	Inconsistent performance	Use OPTIMIZE FOR or RECOMPILE
Implicit conversion	Index not used	Match parameter types
Large result set	High memory usage	Add pagination
Lock contention	Query timeouts	Optimize transactions

Bulk Operations

Batch Inserts

// ✅ Good - Batched insert
const batch = records.map(r => `(@tenantId, '${r.code}', '${r.name}')`);
await db.query(`
  INSERT INTO app_items (itm_tenant_id, itm_code, itm_name)
  VALUES ${batch.join(',')}
`);

// ❌ Bad - Individual inserts
for (const record of records) {
  await db.query(`INSERT INTO app_items VALUES ...`);
}

Table-Valued Parameters (MSSQL)

-- Create type
CREATE TYPE dbo.ItemTableType AS TABLE (
  itm_code NVARCHAR(100),
  itm_name NVARCHAR(200)
);

-- Use in procedure
CREATE PROCEDURE InsertItems
  @TenantId BIGINT,
  @Items dbo.ItemTableType READONLY
AS
BEGIN
  INSERT INTO app_items (itm_tenant_id, itm_code, itm_name)
  SELECT @TenantId, itm_code, itm_name FROM @Items;
END

Monitoring

Key Metrics

Metric	Threshold	Action
Query duration	> 1 second	Optimize query
Connection pool usage	> 80%	Increase pool size
Cache hit ratio	< 80%	Review cache strategy
Lock wait time	> 5 seconds	Investigate blocking
Deadlocks	Any	Review transaction design

MSSQL DMVs

-- Top queries by execution count
SELECT TOP 10
  qs.execution_count,
  qs.total_elapsed_time / qs.execution_count AS avg_time,
  SUBSTRING(qt.text, 1, 100) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.execution_count DESC;

-- Missing indexes
SELECT * FROM sys.dm_db_missing_index_details;

PostgreSQL Statistics

-- Slow queries
SELECT query, calls, mean_time, total_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- Index usage
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

Best Practices Checklist

Query Design

Include tenant_id in WHERE clause
Use parameterized queries
Limit result sets with TOP/LIMIT
Select only needed columns
Use appropriate data types

Index Design

Leading column is tenant_id
Filtered for is_deleted = 0
Cover frequently queried columns
Consider JSON computed columns

Connection Management

Use connection pooling
Close connections properly
Set appropriate timeouts
Monitor pool utilization

Caching

Cache entity registries
Cache configuration data
Implement cache invalidation
Monitor cache hit ratio

Overview​

Index Strategy​

Multi-Tenant Index Pattern​

Filtered Indexes​

JSON Indexes​

Query Optimization​

Always Include Tenant Filter​

Use Appropriate Data Types​

Limit Result Sets​

Avoid SELECT *​

Connection Pool Optimization​

MSSQL Configuration​

PostgreSQL Configuration​

Caching Strategy​

Redis Cache Patterns​

Cache Invalidation​

Query Analysis​

MSSQL Query Plan​

PostgreSQL Query Plan​

Common Issues​

Bulk Operations​

Batch Inserts​

Table-Valued Parameters (MSSQL)​

Monitoring​

Key Metrics​

MSSQL DMVs​

PostgreSQL Statistics​

Best Practices Checklist​

Query Design​

Index Design​

Connection Management​

Caching​

See Also​