Table Design
Storage Modes
Tacnode supports three storage modes, each optimized for different workload patterns:
Storage Mode | Best For | Performance Characteristics | Use Cases | Trade-offs |
---|---|---|---|---|
Row Storage (Default) | OLTP workloads | Fast row-level operations, Low latency queries | Transactional systems, frequent updates | Higher storage for analytics |
Columnar Storage | OLAP workloads | Efficient column scans, excellent compression | Analytics, reporting, data warehousing | Slower for row-level operations |
Hybrid Storage | HTAP workloads | Balanced read/write performance | Mixed analytical and transactional | Increased complexity and storage |
Row Storage
Row storage is the default mode, organizing data by rows. Ideal for transactional workloads:
Best for: OLTP applications, frequent INSERT/UPDATE/DELETE operations, queries retrieving complete rows.
Columnar Storage
Columnar storage stores data by columns, enabling efficient analytical operations:
Best for: Analytics, reporting, data warehousing, aggregate queries on specific columns.
Hybrid Storage
Hybrid storage combines both approaches for mixed workloads:
Best for: HTAP scenarios requiring both transactional and analytical capabilities.
Storage Mode Management
You can change storage modes online without downtime:
Primary Key Design
Primary keys ensure data integrity and optimize query performance. Follow these design principles:
Business Keys vs Surrogate Keys
Composite Primary Keys
Design composite keys following the leftmost prefix principle:
Guidelines:
- Queries should include the leftmost column(s) of the composite key
- Place most selective columns first
- Design keys to support your most common query patterns
Index Design
Tacnode offers various index types optimized for different data characteristics:
Secondary Indexes
Specialized Index Types
Index Selection Guidelines:
- Secondary indexes for non-primary key queries and sorting
- Bitmap indexes for low-cardinality filtering (gender, status, category)
- GIN indexes for complex data types (JSONB, arrays, full-text search)
- Vector indexes for similarity search and machine learning applications
Table Creation and Constraints
CREATE TABLE Methods
CREATE TABLE LIKE - Copy table structure:
CREATE TABLE AS SELECT (CTAS) - Create tables from query results:
CTAS Limitations
CREATE TABLE AS SELECT does not copy:
- Primary key constraints
- Foreign key constraints
- Indexes
- Triggers
- Check constraints
Add these manually after table creation if needed.
Auto-increment Columns
Constraints
Performance Optimization
Storage-Specific Optimization
Partition Tables
Partition tables improve query performance and maintenance operations for large datasets by dividing data across multiple physical partitions. This feature works with all storage modes to provide better scalability and management.
For detailed partitioning strategies, configuration, and best practices, see the Table Partitioning Guide.
Tiered Storage
Tiered storage optimizes costs by automatically moving data between different storage tiers (hot, cold) based on access patterns and data age, ensuring frequently accessed data stays on high-performance storage.
For comprehensive tiered storage configuration and lifecycle management, see the Tiered Storage Guide.
Schema Evolution
Schema evolution allows you to modify table structures without downtime, supporting online DDL operations like adding columns, changing data types, and switching storage modes while maintaining application availability.
For detailed schema change procedures and migration strategies, see the Schema Evolution Guide.
Best Practices
Key Takeaways
Storage Mode Selection:
- Use Row Storage for OLTP workloads with frequent updates
- Use Columnar Storage for OLAP workloads with analytical queries
- Use Hybrid Storage for HTAP workloads requiring both capabilities
Primary Key Design:
- Choose stable, unique columns that support common query patterns
- Use composite keys strategically, following the leftmost prefix principle
- Consider UUIDs for better distribution in high-throughput systems
Index Strategy:
- Create indexes that align with your query patterns
- Use appropriate index types for different data characteristics
- Monitor index usage and remove unused indexes
Performance Optimization:
- Align table design with intended workload patterns
- Consider constraint overhead versus data integrity benefits
- Design tables to support your most common query patterns
This streamlined approach to table design ensures optimal performance while maintaining data integrity and supporting your application's scalability requirements.