I burned three weeks of performance optimization on CitizenApp before realizing the problem wasn't our FastAPI endpoints or React rendering—it was a single dashboard query that spawned 47,000 database calls. The culprit? Computing tenant metrics in Python instead of letting SQLAlchemy push the work to PostgreSQL.
Most teams fall into this trap because it feels easier. You load tenants, loop through their feature usage, calculate adoption rates in memory. The code reads naturally. It works in development. Then production hits 500 concurrent users and your database connection pool evaporates.
I'm going to show you why SQLAlchemy's hybrid_property
decorator exists, why it's essential for multi-tenant systems, and how to use it to move computation from your application layer to the database where it scales.
Let's say you're tracking AI feature usage across a multi-tenant hierarchy. Your data model looks roughly like this:
from sqlalchemy import Column, Integer, String, ForeignKey, DateTime, func
from sqlalchemy.orm import relationship
from datetime import datetime
class Tenant(Base):
__tablename__ = "tenants"
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
parent_id = Column(Integer, ForeignKey("tenants.id"), nullable=True)
children = relationship("Tenant", remote_side=[id], backref="parent")
ai_features = relationship("AIFeatureUsage", back_populates="tenant")
class AIFeatureUsage(Base):
__tablename__ = "ai_feature_usage"
id = Column(Integer, primary_key=True)
tenant_id = Column(Integer, ForeignKey("tenants.id"), nullable=False)
feature_name = Column(String, nullable=False)
inference_cost = Column(Float, default=0.0)
created_at = Column(DateTime, default=datetime.utcnow)
tenant = relationship("Tenant", back_populates="ai_features")
Your dashboard endpoint loads all tenants and computes adoption rate (how many of your 9 AI features each tenant has used):
@app.get("/tenants/metrics")
async def get_tenant_metrics(session: Session = Depends(get_session)):
tenants = session.query(Tenant).all() # 1 query
metrics = []
for tenant in tenants:
feature_count = session.query(AIFeatureUsage)\
.filter(AIFeatureUsage.tenant_id == tenant.id)\
.distinct(AIFeatureUsage.feature_name)\
.count()
adoption_rate = feature_count / 9
metrics.append({
"tenant_id": tenant.id,
"adoption_rate": adoption_rate
})
return metrics
With 100 tenants, this is 101 queries. With 1,000 tenants? 1,001 queries. The dashboard becomes unusable.
SQLAlchemy's hybrid_property
lets you define computed attributes that work in two contexts:
Here's the corrected approach:
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy import and_
class Tenant(Base):
__tablename__ = "tenants"
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
parent_id = Column(Integer, ForeignKey("tenants.id"), nullable=True)
children = relationship("Tenant", remote_side=[id], backref="parent")
ai_features = relationship("AIFeatureUsage", back_populates="tenant")
@hybrid_property
def feature_adoption_rate(self) -> float:
"""
Python-side: Count distinct features used by this tenant.
"""
if not self.ai_features:
return 0.0
distinct_features = len(set(f.feature_name for f in self.ai_features))
return distinct_features / 9
@feature_adoption_rate.expression
@classmethod
def feature_adoption_rate(cls):
"""
SQL-side: Compute adoption rate as a subquery.
This runs in the database, not in Python.
"""
from sqlalchemy.sql import select, func as sql_func
feature_count = select(sql_func.count(
sql_func.distinct(AIFeatureUsage.feature_name)
)).where(
AIFeatureUsage.tenant_id == cls.id
).correlate(cls).scalar_subquery()
return feature_count / 9
Now your endpoint becomes:
@app.get("/tenants/metrics")
async def get_tenant_metrics(session: Session = Depends(get_session)):
tenants = session.query(Tenant).add_columns(
Tenant.feature_adoption_rate.label("adoption_rate")
).all()
return [
{
"tenant_id": t.id,
"adoption_rate": t.adoption_rate
}
for t in tenants
]
This produces one query—a single JOIN that computes adoption rates at the database layer.
Real multi-tenant systems are hierarchical. Parent tenants inherit the aggregated usage of their children. Here's where hybrid properties shine:
class Tenant(Base):
__tablename__ = "tenants"
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
parent_id = Column(Integer, ForeignKey("tenants.id"), nullable=True)
children = relationship("Tenant", remote_side=[id], backref="parent")
ai_features = relationship("AIFeatureUsage", back_populates="tenant")
@hybrid_property
def total_inference_cost(self) -> float:
"""
Include costs from this tenant + all descendants.
"""
own_cost = sum(f.inference_cost for f in self.ai_features)
children_cost = sum(c.total_inference_cost for c in self.children)
return own_cost + children_cost
@total_inference_cost.expression
@classmethod
def total_inference_cost(cls):
"""
Recursive CTE in SQL (PostgreSQL 12+).
"""
from sqlalchemy import text
direct = select(func.coalesce(
func.sum(AIFeatureUsage.inference_cost), 0
)).where(
AIFeatureUsage.tenant_id == cls.id
).correlate(cls).scalar_subquery()
children_costs = select(func.coalesce(
func.sum(Tenant.total_inference_cost), 0
)).where(
Tenant.parent_id == cls.id
).correlate(cls).scalar_subquery()
return direct + children_costs
Here's what burned me: hybrid properties don't always translate to SQL. Some expressions are too complex or use Python-only logic:
class Tenant(Base):
@hybrid_property
def permission_inheritance_depth(self) -> int:
"""
Count how many levels deep in the hierarchy.
This LOOKS like it should work...
"""
if self.parent:
return 1 + self.parent.permission_inheritance_depth
return 0
@permission_inheritance_depth.expression
@classmethod
def permission_inheritance_depth(cls):
pass
Fix: For recursive hierarchies, use PostgreSQL CTEs directly:
from sqlalchemy import text, literal_column
@classmethod
def permission_inheritance_depth_expr(cls):
cte = text("""
WITH RECURSIVE tenant_depth AS (
SELECT id, parent_id, 0 as depth FROM tenants
WHERE id = :tenant_id
UNION ALL
SELECT t.id, t.parent_id, td.depth + 1
FROM tenants t
JOIN tenant_depth td ON t.id = td.parent_id
)
SELECT MAX(depth) FROM tenant_depth
""")
return cte
CitizenApp's dashboard queries 9 AI features across a 3-level tenant hierarchy. Without hybrid properties, each metric was N+1: load tenants, load features per tenant, load children, compute costs, compute adoption. That's roughly 30 queries per user.
With hybrid properties pushed to SQL:
The pattern generalizes: any computed metric that depends on related data belongs in a hybrid property. Let your database do what it's designed for.