Tiered Storage

Learn how to implement and manage tiered storage in Tacnode Table with this comprehensive guide, optimizing data storage efficiency and performance.

Tacnode supports configurable tiered storage across different media to meet business requirements for cost optimization and performance. This guide covers implementing and managing tiered storage strategies for your database workloads.

Overview

Tiered storage allows you to optimize costs and performance by placing data on appropriate storage media based on access patterns and business requirements.

Storage Tiers Available

Tablespace Concept

A tablespace is a logical storage zone that defines the physical storage location of data. In Tacnode:

• Tables, indexes, or entire databases can be bound to designated tablespaces
• Tablespaces can be assigned during creation or modification
• Each tablespace maps to a specific storage location

Tablespace Management

Creating Tablespaces

Create tablespaces to define storage locations for your data:

CREATE TABLESPACE tablespace_name
    [ OWNER { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }]
    LOCATION 'location';

Parameters:

• tablespace_name: Unique name for the tablespace
• OWNER: Optional owner specification
• location: Storage tier location (/hot or /cold)

Examples:

-- Create tiered tablespaces
CREATE TABLESPACE space_hot LOCATION '/hot';
CREATE TABLESPACE space_cold LOCATION '/cold';

-- Create tablespace with specific owner
CREATE TABLESPACE analytics_hot 
    OWNER analytics_user 
    LOCATION '/hot';

Dropping Tablespaces

Remove unused tablespaces when no longer needed:

DROP TABLESPACE [ IF EXISTS ] tablespace_name;

Examples:

-- Drop specific tablespace
DROP TABLESPACE space_cold;

-- Safely drop tablespace if exists
DROP TABLESPACE IF EXISTS old_archive_space;

Assigning Data to Tablespaces

Table Creation with Tablespace

Specify tablespace during table creation:

-- Store high-frequency transactional data in Hot storage
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT NOW(),
    amount NUMERIC(10,2),
    status VARCHAR(20) DEFAULT 'pending'
) TABLESPACE space_hot;

-- Archive historical data in Cold storage
CREATE TABLE order_history (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    order_date TIMESTAMP,
    amount NUMERIC(10,2),
    status VARCHAR(20),
    archived_date TIMESTAMP DEFAULT NOW()
) TABLESPACE space_cold;

Index Creation with Tablespace

Place indexes strategically based on usage patterns:

-- Hot storage for frequently queried indexes
CREATE INDEX idx_orders_user_id 
    ON orders (user_id) 
    TABLESPACE space_hot;

CREATE INDEX idx_orders_date 
    ON orders (order_date DESC) 
    TABLESPACE space_hot;

-- Cold storage for archive indexes
CREATE INDEX idx_history_archived_date 
    ON order_history (archived_date) 
    TABLESPACE space_cold;

Migrating Existing Data

Move existing tables and indexes between tablespaces:

-- Migrate table to different tablespace
ALTER TABLE old_orders SET TABLESPACE space_cold;

-- Migrate index to different tablespace
ALTER INDEX idx_old_orders_date SET TABLESPACE space_cold;

Database-Level Configuration

Setting Default Tablespace

Configure default tablespaces for new objects:

-- Set default tablespace for new tables
SET default_tablespace = space_hot;

-- Set tablespace for temporary tables
SET temp_tablespaces = 'space_cold';

Per-Session Configuration

Configure tablespace settings for specific sessions:

-- Configure for current session
SET default_tablespace = space_cold;

-- Create table (will use space_cold by default)
CREATE TABLE session_data (
    id SERIAL PRIMARY KEY,
    data JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

Partitioned Table Tiering Strategies

Time-Based Tiering

Implement automatic tiering based on data age:

-- Create partitioned table
CREATE TABLE sales_data (
    id SERIAL,
    sale_date DATE NOT NULL,
    customer_id INT NOT NULL,
    product_id INT NOT NULL,
    amount NUMERIC(10,2),
    PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (sale_date);

-- Current year partition in Hot storage
CREATE TABLE sales_2024
    PARTITION OF sales_data
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01')
    TABLESPACE space_hot;

-- Previous year partition in Cold storage
CREATE TABLE sales_2023
    PARTITION OF sales_data
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01')
    TABLESPACE space_cold;

-- Archive partition for older data
CREATE TABLE sales_archive
    PARTITION OF sales_data
    FOR VALUES FROM ('2020-01-01') TO ('2023-01-01')
    TABLESPACE space_cold;

Usage-Based Tiering

Tier data based on access patterns:

-- Create customer segmentation table
CREATE TABLE customer_data (
    customer_id INT PRIMARY KEY,
    segment VARCHAR(20) NOT NULL,
    registration_date DATE,
    last_activity_date DATE,
    profile_data JSONB
) PARTITION BY LIST (segment);

-- Active customers in Hot storage
CREATE TABLE customer_premium
    PARTITION OF customer_data
    FOR VALUES IN ('premium', 'gold', 'active')
    TABLESPACE space_hot;

-- Inactive customers in Cold storage  
CREATE TABLE customer_inactive
    PARTITION OF customer_data
    FOR VALUES IN ('inactive', 'archived')
    TABLESPACE space_cold;

Monitoring and Optimization

Checking Tablespace Usage

Monitor space utilization across tablespaces:

-- Check tablespace sizes
SELECT 
    spcname AS tablespace_name,
    pg_size_pretty(pg_tablespace_size(spcname)) AS size,
    spclocation AS location
FROM pg_tablespace
WHERE spcname NOT IN ('pg_default', 'pg_global');

-- List tables and their tablespaces
SELECT 
    schemaname,
    tablename,
    COALESCE(tablespace, 'pg_default') AS tablespace,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Performance Monitoring

Track performance metrics by storage tier:

-- Monitor query performance by tablespace
SELECT 
    schemaname,
    tablename,
    COALESCE(tablespace, 'pg_default') AS tablespace,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch,
    n_tup_ins + n_tup_upd + n_tup_del AS total_modifications
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

Best Practices

Data Classification Strategy

1. Hot Storage Candidates:

   • Current transactional data
   • Frequently queried reference tables
   • Real-time analytics data
   • Recent log data

2. Cold Storage Candidates:

   • Historical archive data
   • Backup and recovery data
   • Compliance and audit logs
   • Infrequently accessed reference data

Optimization Guidelines

1. Cache Strategy:

   -- Configure appropriate cache for cold storage workloads
   ALTER SYSTEM SET shared_buffers = '256MB';  -- Adjust based on workload
   ALTER SYSTEM SET effective_cache_size = '4GB';  -- Set based on system memory

2. Index Strategy:

   • Place frequently used indexes in hot storage
   • Use partial indexes to reduce cold storage footprint
   • Consider index-only scans for cold data

3. Monitoring Strategy:

   • Set up alerts for tablespace utilization
   • Monitor query performance across tiers
   • Track data access patterns for tiering decisions

Migration Planning

1. Assessment Phase:

   • Analyze current data access patterns
   • Identify candidates for each storage tier
   • Estimate storage and performance impact

2. Implementation Phase:

   • Start with non-critical data
   • Plan migration during low-traffic periods
   • Monitor performance impact

3. Validation Phase:

   • Verify query performance meets requirements
   • Confirm storage cost objectives achieved
   • Document lessons learned for future migrations

Troubleshooting

Common Issues

Issue: High latency on cold storage queries

• Solution: Implement caching strategy or move frequently accessed data to hot storage

Issue: Tablespace migration taking too long

• Solution: Break large migrations into smaller chunks or schedule during maintenance windows

Issue: Running out of space in hot storage

• Solution: Review data access patterns and migrate appropriate data to cold storage

Performance Optimization

-- Analyze query patterns to optimize tiering
SELECT 
    query,
    calls,
    total_time,
    mean_time,
    rows
FROM pg_stat_statements
WHERE query LIKE '%your_table%'
ORDER BY total_time DESC
LIMIT 10;

This tiered storage strategy enables you to optimize both cost and performance by placing data on appropriate storage media based on business requirements and access patterns.