tomevault-io Xano Database Best Practices
PostgreSQL best practices adapted for Xano's architecture, XanoScript, and abstraction layer.
Quick Reference: Related Skills
| Topic | Skill | Priority |
|---|---|---|
| Query optimization, N+1, indexing | xano-query-performance |
CRITICAL |
| Schema design, normalization, constraints | xano-schema-design |
HIGH |
| RLS, injection prevention, auth | xano-security |
CRITICAL |
| Addons, batch operations, caching | xano-data-access |
MEDIUM |
| Query Analytics, debugging | xano-monitoring |
MEDIUM |
Xano Architecture Overview
Data Format Options
Xano supports two PostgreSQL data formats:
Standard SQL Format (Default since late 2025):
CREATE TABLE x_<workspaceID>_<tableID> (
id SERIAL PRIMARY KEY,
name VARCHAR(255),
email VARCHAR(255),
created_at TIMESTAMP
);
- Full indexing support (B-tree, partial, composite)
- Native SQL query optimization
- Recommended for production applications
JSONB Format (Legacy):
CREATE TABLE mvpw_<workspaceID>_<tableID> (
id SERIAL PRIMARY KEY,
data JSONB
);
- Flexible schema changes
- Limited indexing (GIN indexes only)
- Better for prototyping or document-like data
XanoScript Query Syntax
| Operation | PostgreSQL | XanoScript |
|---|---|---|
| Select all | SELECT * FROM users |
db.query user { return = {type: "list"} } |
| Filter | WHERE age > 25 |
db.query user { filter = "age > ?", 25 } |
| Single record | SELECT * FROM users WHERE id = 1 LIMIT 1 |
db.get user { field_name = "id", field_value = 1 } |
| Insert | INSERT INTO users (name) VALUES (?) |
db.add user { name = "John" } |
| Join | LEFT JOIN posts ON... |
Use Addons pattern (see xano-data-access) |
| Raw SQL | Direct execution | db.raw "SELECT * FROM users" |
Data Format Decision Tree
Does the optimization rely on field-level indexing?
├─ YES → Use Standard SQL format (B-tree indexes)
└─ NO → Does it involve complex queries (joins, CTEs)?
├─ YES → Use Standard SQL format
└─ NO → Does schema change frequently?
├─ YES → JSONB format acceptable
└─ NO → Use Standard SQL format (better performance)
Key Best Practice Categories
1. Query Performance (CRITICAL)
See: xano-query-performance skill
Critical patterns:
- Use Addons instead of N+1 query loops
- Create indexes on frequently queried columns
- Always paginate large result sets
- Chain filters in single blocks
2. Schema Design (HIGH)
See: xano-schema-design skill
Key principles:
- Normalize data appropriately
- Choose correct data types
- Set up proper foreign keys and constraints
- Consider Standard SQL format for performance-critical tables
3. Security (CRITICAL)
See: xano-security skill
Essential practices:
- Enable Row Level Security (RLS) for sensitive data
- Use parameterized queries to prevent SQL injection
- Implement proper authentication flows
- Validate all user inputs
4. Data Access Patterns (MEDIUM)
See: xano-data-access skill
Optimization techniques:
- Use Addons for efficient joins
- Implement cursor-based pagination for large datasets
- Batch operations for bulk inserts/updates
- Leverage Xano's built-in caching
5. Monitoring & Diagnostics (MEDIUM)
See: xano-monitoring skill
Monitoring approach:
- Use Query Analytics dashboard
- Analyze slow queries via Direct Database Connector
- Set up performance baselines
- Track API response times
JSONB vs Standard SQL Quick Reference
| Feature | JSONB Format | Standard SQL Format |
|---|---|---|
| B-tree indexes | No | Yes |
| Partial indexes | No | Yes |
| Composite indexes | Limited | Yes |
| Field-level SELECT | No (full record) | Yes |
| Schema flexibility | High | Low |
| Query optimization | Limited | Full PostgreSQL |
| Recommended for | Prototyping, documents | Production, complex queries |
Performance Measurement
Use Xano Query Analytics (Dashboard → Analytics) to:
- Identify slow queries
- Track query frequency
- Monitor error rates
- Compare before/after optimizations
For advanced analysis with EXPLAIN:
- Use Direct Database Connector (Premium tier)
- Run
EXPLAIN ANALYZEon problematic queries - Check for sequential scans vs index scans
Resources
- Xano Docs: https://docs.xano.com
- Database Performance: https://docs.xano.com/the-database/database-performance-and-maintenance
- XanoScript Reference: https://docs.xano.com/xanoscript
- Direct Database Query: https://docs.xano.com/the-function-stack/functions/database-requests/direct-database-query
Converted and distributed by TomeVault — claim your Tome and manage your conversions.