Tiered Storage

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

Storage Tier	Location	Media Type	Use Case
Hot Storage	`/hot` (default)	High-Performance SSD	Frequently accessed data requiring low latency
Cold Storage	`/cold`	HDD	Infrequently accessed data prioritizing cost savings

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

Cold Storage Performance Optimization

When using Cold storage in a Nodegroup, bind a cache to improve access performance:

Create Cache: In Console → "Data" → "Cache", create new cache or reuse existing resources
Bind Cache: Associate the cache with your Nodegroup to accelerate cold data access

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';

Privileges Required

Creating tablespaces requires Tacnode superuser privileges.

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;

Prerequisites

Ensure no tables or indexes are using the tablespace before dropping it.

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;

Migration Impact

Changing tablespace rewrites all data. During this operation:

Storage usage temporarily doubles until completion
The operation may take significant time for large tables
Consider running during maintenance windows

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

Hot Storage Candidates:
- Current transactional data
- Frequently queried reference tables
- Real-time analytics data
- Recent log data
Cold Storage Candidates:
- Historical archive data
- Backup and recovery data
- Compliance and audit logs
- Infrequently accessed reference data

Optimization Guidelines

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

Index Strategy:
- Place frequently used indexes in hot storage
- Use partial indexes to reduce cold storage footprint
- Consider index-only scans for cold data
Monitoring Strategy:
- Set up alerts for tablespace utilization
- Monitor query performance across tiers
- Track data access patterns for tiering decisions

Migration Planning

Assessment Phase:
- Analyze current data access patterns
- Identify candidates for each storage tier
- Estimate storage and performance impact
Implementation Phase:
- Start with non-critical data
- Plan migration during low-traffic periods
- Monitor performance impact
Validation Phase:
- Verify query performance meets requirements
- Confirm storage cost objectives achieved
- Document lessons learned for future migrations

Gradual Migration

Consider implementing tiered storage gradually:

Start with clear archive data candidates
Monitor performance and cost impact
Expand tiering strategy based on results
Automate tier transitions where possible

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.

Tiered Storage

On this page