Tortoise ORM and PostgreSQL: A Performance Guide for the Async-First ORM

Good evening. You have chosen the async-first path.

Tortoise ORM occupies a peculiar position in the Python ecosystem. It is the only Python ORM that was designed async-first — not bolted on after the fact, not adapted from a synchronous core, but built from the first line of code to work with asyncio. It draws heavy inspiration from Django's ORM in API design, which makes it immediately familiar to a large portion of the Python community. And it has, in the past two years, become the second most popular async ORM choice for FastAPI projects.

What it does not have is a performance optimization guide. The official documentation is thorough on features — models, queries, relations, migrations — but largely silent on the performance patterns that separate a 200ms endpoint from a 5ms one. The community guides tend to stop at "it works with FastAPI" without examining what happens when your tables grow past a few thousand rows.

This seems like an omission worth correcting. I have spent considerable time with Tortoise ORM and PostgreSQL, and I have opinions about where the performance lives and where it hides. If you will permit me, I shall share them.

Before we proceed, a word about the models we shall be working with throughout this guide. They are deliberately ordinary — an e-commerce schema with customers, orders, and order items. The kind of schema you could sketch on a napkin. Performance patterns are best demonstrated against boring data models, because in production, most data models are boring. The complexity lives in how you query them.

from tortoise import fields, models

class Customer(models.Model):
    id = fields.IntField(pk=True)
    name = fields.CharField(max_length=200)
    email = fields.CharField(max_length=200, unique=True)
    tier = fields.CharField(max_length=20, default="free")

    orders: fields.ReverseRelation["Order"]

class Order(models.Model):
    id = fields.IntField(pk=True)
    customer = fields.ForeignKeyField("models.Customer", related_name="orders")
    total = fields.DecimalField(max_digits=10, decimal_places=2)
    status = fields.CharField(max_length=20, default="pending")
    created_at = fields.DatetimeField(auto_now_add=True)

    items: fields.ReverseRelation["OrderItem"]

class OrderItem(models.Model):
    id = fields.IntField(pk=True)
    order = fields.ForeignKeyField("models.Order", related_name="items")
    product_name = fields.CharField(max_length=200)
    quantity = fields.IntField()
    unit_price = fields.DecimalField(max_digits=10, decimal_places=2)

The async N+1 problem: asyncio will not save you

I must begin here because it is the single most consequential performance issue in any ORM, and there is a dangerous misconception that async frameworks somehow mitigate it. They do not.

The N+1 problem occurs when your code fetches a list of N items, then fetches related data for each item individually — resulting in N+1 queries instead of 1 or 2. In a synchronous ORM, each query blocks the thread. In an async ORM, each query awaits — and that await still incurs a network round trip to PostgreSQL and back.

# The async N+1 — same problem, different event loop
from tortoise.models import Model

async def get_order_summaries():
    orders = await Order.filter(status="pending")

    summaries = []
    for order in orders:
        # Each .customer triggers a separate await + DB round trip
        customer = await order.customer  # N queries, one per order
        summaries.append({
            "order_id": order.id,
            "total": order.total,
            "customer_name": customer.name,
        })

    return summaries
# 200 pending orders = 201 queries. All awaited sequentially.
# The async event loop does NOT save you here — each await
# still waits for a network round trip to PostgreSQL.

The event loop is free to handle other requests while each await is in flight, which is good for concurrency. But the latency of this specific request is the sum of 201 sequential round trips. At 1ms per query on localhost, that is 201ms minimum. At 3ms per query on a remote database — which is entirely typical for a cloud-hosted PostgreSQL instance in a different availability zone — that is 603ms. For a single endpoint. Serving a single user.

I should be precise about why async does not help here. The for loop awaits each customer lookup sequentially. The event loop cannot advance to the next iteration until the current await resolves. It is not concurrent within this function — it is sequential I/O dressed in async syntax. The concurrency benefit applies only to other requests arriving at the application simultaneously, not to the 201 queries generated by this one.

The asyncio.gather trap

Some developers, upon realizing the sequential nature of the problem, attempt what seems like the obvious async solution: fire all the lookups concurrently with asyncio.gather.

# The asyncio.gather "fix" — trading latency for pool exhaustion
async def get_order_summaries_bad_fix():
    orders = await Order.filter(status="pending")

    # Looks clever: fire all customer lookups concurrently
    customers = await asyncio.gather(*[
        order.customer for order in orders
    ])
    # With 200 orders and maxsize=20, this tries to acquire
    # 200 connections simultaneously. 180 of them queue.
    # Meanwhile, every OTHER endpoint in your application
    # is also waiting for a connection.

    # Worse: if any single lookup fails, gather() can cancel
    # the others or raise — depending on return_exceptions.
    # You have traded a slow endpoint for an unreliable one.

# The right fix is still prefetch_related. Always.
async def get_order_summaries_correct():
    orders = await Order.filter(status="pending").prefetch_related("customer")
    # 2 queries. 2 connections (sequential, not concurrent).
    # Predictable. Fast. Correct.

This is the infrastructural equivalent of discovering that queuing at one checkout counter is slow, so you open 200 checkout counters simultaneously. The grocery store does not have 200 checkout counters. Your connection pool does not have 200 connections. The result is pool exhaustion, connection queuing for every other endpoint, and occasional asyncpg.exceptions.TooManyConnectionsError when the pool runs dry entirely.

The actual fix is the same as in every other ORM: eager loading.

# Fixed with prefetch_related — 2 queries instead of 201
async def get_order_summaries():
    orders = await Order.filter(status="pending").prefetch_related("customer")

    summaries = []
    for order in orders:
        # customer is already loaded — no additional query
        summaries.append({
            "order_id": order.id,
            "total": order.total,
            "customer_name": order.customer.name,  # no await needed
        })

    return summaries

# Deep prefetching works too:
orders = await Order.filter(status="pending").prefetch_related(
    "customer", "items", "items__product"
)
# 4 queries total, regardless of how many orders.

prefetch_related issues two queries: one for the orders, one for all related customers with an IN clause. Two round trips. Two queries. Whether you have 50 orders or 5,000, the query count stays at 2.

Detecting N+1 queries before your users do

The most insidious property of N+1 queries is that they work perfectly in development. With 5 orders in your local database, 6 queries complete in 8ms. You ship it. Three months later, with 5,000 orders, the same endpoint takes 15 seconds. The code has not changed. The data has.

# Detecting N+1 queries in Tortoise ORM
# Tortoise does not have built-in query logging, but asyncpg does.

import logging

# Enable asyncpg query logging
logging.getLogger("tortoise.db_client").setLevel(logging.DEBUG)

# Or instrument at the connection level for production monitoring:
from tortoise import connections

async def count_queries_for_request():
    """Middleware-style query counter for FastAPI."""
    conn = connections.get("default")

    # Use PostgreSQL's pg_stat_statements for production
    # This gives you exact query counts per normalized query pattern
    results = await conn.execute_query_dict("""
        SELECT query, calls, mean_exec_time, total_exec_time
        FROM pg_stat_statements
        WHERE query LIKE '%orderitem%'
        ORDER BY calls DESC
        LIMIT 20
    """)
    # If you see the same SELECT with different parameter values
    # called hundreds of times — that is your N+1.
    return results

If you are using PostgreSQL's pg_stat_statements extension — and you should be — look for normalized query patterns with very high calls counts relative to your traffic. A query that appears 200 times per second on an endpoint serving 1 request per second is your N+1. For a detailed examination of N+1 patterns across all major ORMs, I have written about this at some length.

select_related vs prefetch_related: when to use which

Tortoise borrows Django's terminology here, which is helpful if you know Django and confusing if you do not. The distinction is straightforward, but the consequences of choosing incorrectly are not.

# select_related — uses a SQL JOIN (Tortoise calls it this too, but
# be aware: it only works for ForeignKey fields, not reverse relations)
orders = await Order.filter(status="pending").select_related("customer")

# Generated SQL:
# SELECT "order"."id", "order"."total", "order"."status", ...
#        "customer"."id", "customer"."name", ...
# FROM "order"
# LEFT JOIN "customer" ON "order"."customer_id" = "customer"."id"
# WHERE "order"."status" = 'pending'

# prefetch_related — uses a second IN query (works for everything)
orders = await Order.filter(status="pending").prefetch_related("items")

# Generated SQL (two queries):
# SELECT ... FROM "order" WHERE "order"."status" = 'pending'
# SELECT ... FROM "orderitem" WHERE "orderitem"."order_id" IN (1, 2, 3, ...)

select_related uses a SQL JOIN. It fetches the parent and related objects in a single query. It only works for forward ForeignKey relationships — you can follow the arrow from Order to Customer, but not from Customer to their Orders.

prefetch_related uses a second query with an IN clause. It works for everything — forward ForeignKeys, reverse relations, many-to-many. It is the more versatile tool.

When to prefer which:

• Single ForeignKey, always needed: select_related. One query, one round trip. The JOIN is cheap on indexed foreign keys.
• Reverse relations or many-to-many: prefetch_related. There is no alternative — JOINs on one-to-many relationships multiply your result rows, which is worse than a second query.
• Multiple levels deep: prefetch_related with dotted paths: "items__product". Each level adds one query — predictable and bounded.
• Both at once: Perfectly valid. select_related("customer").prefetch_related("items") gives you one JOIN for the customer and one additional query for items.

The cartesian product pitfall

There is a reason Tortoise restricts select_related to ForeignKey fields, and it is worth understanding rather than merely accepting.

# The select_related cartesian product pitfall
# Joining multiple one-to-many relations multiplies result rows

# If an order has 10 items and 3 status_history entries:
orders = await Order.filter(status="pending").select_related(
    "customer"  # This is fine — it is a ForeignKey (many-to-one)
)

# But if you try to JOIN reverse relations (hypothetically):
# SELECT ... FROM "order"
# JOIN orderitem ON orderitem.order_id = order.id
# JOIN status_history ON status_history.order_id = order.id
# An order with 10 items and 3 history entries produces 30 rows.
# 100 such orders = 3,000 rows instead of 100.
# This is why Tortoise restricts select_related to ForeignKey fields.

# The correct approach for multiple reverse relations:
orders = await Order.filter(status="pending").select_related(
    "customer"          # JOIN — one customer per order
).prefetch_related(
    "items",            # Separate IN query — no row multiplication
    "status_history"    # Another separate IN query
)
# 3 queries, clean results, no cartesian product.

When you JOIN a one-to-many relationship, PostgreSQL returns one row per child. An order with 10 items produces 10 rows in the result set. JOIN two one-to-many relationships simultaneously, and the result is the cartesian product — 10 items times 3 status history entries produces 30 rows for a single order. This row multiplication is not hypothetical; it is a mathematical certainty. Tortoise prevents it by design. prefetch_related avoids the problem entirely by using separate queries — each returning only the rows that belong to each relationship, with no cross-multiplication.

values() and values_list(): skip the model tax

Every ORM imposes a cost for instantiating model objects. Each row returned from the database becomes a Python object with attribute descriptors, metadata, state tracking, and a dictionary of field values. For 50 rows, this overhead is negligible. For 10,000 rows, it is measurable. For 100,000 rows, it dominates the response time.

# Full model instantiation — every field, every object
orders = await Order.filter(status="pending")
# Each row becomes an Order instance with all fields populated.
# For 10,000 rows, that is 10,000 Python objects with attribute
# dictionaries, field descriptors, and model metadata.

# values() — returns dictionaries, skips model instantiation
order_data = await Order.filter(status="pending").values(
    "id", "total", "status", "created_at"
)
# Returns: [{"id": 1, "total": 49.99, "status": "pending", ...}, ...]
# 30-50% faster for large result sets. Same SQL, less Python overhead.

# values_list() — returns tuples, even leaner
order_ids = await Order.filter(status="pending").values_list("id", flat=True)
# Returns: [1, 2, 3, 4, ...]
# Useful for subqueries, batch operations, existence checks.

The SQL is identical in both cases. The difference is what happens after the rows arrive from PostgreSQL. With values(), Tortoise skips model instantiation entirely and returns plain dictionaries. With values_list(), it returns tuples — even leaner.

I should quantify "30-50% faster" because vague performance claims are precisely the sort of thing that makes a waiter uneasy. The overhead per model instance is roughly 50-100 microseconds — attributable to Python's object creation, descriptor protocol, and Tortoise's internal field converters. At 50 rows, that is 2.5-5ms of overhead. Barely noticeable. At 10,000 rows, it is 500ms-1s. At that point, you are spending more time creating Python objects than PostgreSQL spent executing the query.

values() with related fields

The real power of values() emerges when combined with related field traversal and annotations.

# values() with related field traversal
order_data = await Order.filter(status="pending").values(
    "id", "total", "created_at",
    "customer__name", "customer__email", "customer__tier"
)
# Returns flat dictionaries with related fields:
# [{"id": 1, "total": 49.99, "customer__name": "Acme Corp", ...}, ...]
# Single JOIN query, dictionary output. No model objects at all.

# Combining values() with annotate() — the real power move
from tortoise.functions import Count, Sum

customer_stats = await Customer.annotate(
    order_count=Count("orders"),
    total_revenue=Sum("orders__total"),
).filter(
    order_count__gt=0
).values(
    "name", "tier", "order_count", "total_revenue"
)
# PostgreSQL computes the aggregation. Tortoise returns dictionaries.
# No model instantiation. No Python-side computation.
# For a dashboard endpoint serving 50 customers, this returns
# in under 5ms — including the GROUP BY.

This pattern — annotate, filter, values — is the backbone of any dashboard or reporting endpoint. PostgreSQL does the computation. Tortoise returns dictionaries. FastAPI serializes them to JSON. No model objects are created at any point in the pipeline. For high-throughput read endpoints, this is the pattern to reach for first.

When to use them:

• API responses: If the endpoint returns JSON, you are going to serialize to a dictionary anyway. Starting with a dictionary skips one full object-creation cycle.
• Reports and exports: Large result sets that will be formatted into CSV, Excel, or aggregated further in Python. Model instances add nothing here.
• Subquery preparation: values_list("id", flat=True) gives you a list of IDs for use in subsequent filters or batch operations.
• Dashboard endpoints: Any endpoint that aggregates data for display — charts, summary cards, leaderboards. These should never instantiate full model objects.

When not to use them: if you need model methods, validation, or relationship traversal. Dictionaries do not have .save() or .customer. This is a read-path optimization, not a general-purpose replacement. And I should note — if your model has custom properties that compute derived values, those will not appear in values() output. You would need to either replicate the logic in an annotation or accept the model instantiation cost for that particular endpoint.

Efficient filtering and query patterns

Before we discuss bulk operations and aggregations, allow me to address the queries themselves. Tortoise's filter API is more capable than most guides suggest, and there are patterns here that eliminate unnecessary database round trips.

# Efficient filtering patterns in Tortoise ORM

# Range queries — PostgreSQL uses B-tree indexes for these
recent_orders = await Order.filter(
    created_at__gte=cutoff_date,
    total__gte=100,
    status="shipped",
).order_by("-created_at").limit(50)
# Ensure composite index: (status, created_at DESC)
# for this specific access pattern.

# Existence checks — don't fetch what you don't need
has_orders = await Order.filter(
    customer_id=customer_id, status="pending"
).exists()
# Generates: SELECT 1 FROM "order" WHERE ... LIMIT 1
# Returns True/False. No data transfer. No model instantiation.

# Count without fetching
pending_count = await Order.filter(status="pending").count()
# Generates: SELECT COUNT(*) FROM "order" WHERE status = 'pending'
# Returns an integer. Not a queryset. Not a list.

# Subquery-style filtering
from tortoise.queryset import Q

# OR conditions
vip_or_recent = await Customer.filter(
    Q(tier="enterprise") | Q(orders__created_at__gte=last_month)
).distinct()

# NOT conditions
non_cancelled = await Order.filter(
    ~Q(status="cancelled"),
    created_at__gte=last_week,
)

Two methods deserve particular attention: .exists() and .count(). I see developers fetch entire querysets to check if any rows exist, or iterate results to count them. PostgreSQL can answer "does at least one row match?" and "how many rows match?" without transferring a single row of data. The difference for a table with 500,000 rows is the difference between transferring 500,000 rows over the network and transferring a single boolean or integer.

On index alignment: the filter patterns above only perform well if your PostgreSQL indexes match your access patterns. A filter on status and created_at ordered by created_at DESC wants a composite index on (status, created_at DESC). Tortoise's Meta.indexes can define simple B-tree indexes, but for composite indexes with specific sort orders, you will want Aerich custom SQL or a direct CREATE INDEX. The ORM generates the queries; it is your responsibility to ensure the indexes exist to serve them.

Pagination: offset will betray you eventually

Every application that lists data eventually needs pagination. Most reach for offset-based pagination because it is simple and maps directly to the UI concept of "page 1, page 2, page 3." It works well for the first hundred pages. Then it does not.

# Pagination: offset vs keyset

# Offset pagination — simple but degrades with depth
page = await Order.filter(
    status="shipped"
).order_by("-created_at").offset(10000).limit(50)
# PostgreSQL must skip 10,000 rows before returning 50.
# At page 200 (offset 10,000), this is noticeably slow.
# At page 2,000 (offset 100,000), it is painful.

# Keyset pagination — consistent performance at any depth
page = await Order.filter(
    status="shipped",
    created_at__lt=last_seen_date,  # cursor from previous page
    id__lt=last_seen_id,            # tiebreaker for same timestamp
).order_by("-created_at", "-id").limit(50)
# PostgreSQL seeks directly to the cursor position via index.
# Page 1 and page 2,000 take the same time: ~2ms.

# For the first page (no cursor yet):
first_page = await Order.filter(
    status="shipped"
).order_by("-created_at", "-id").limit(50)

# The cursor for the next page is the last item's values:
# next_cursor = {"created_at": last_item.created_at, "id": last_item.id}

Offset-based pagination has a fundamental flaw: PostgreSQL must read and discard all rows before the offset. OFFSET 10000 LIMIT 50 reads 10,050 rows and discards 10,000 of them. The work scales linearly with the offset value. Page 1 and page 200 have dramatically different performance characteristics, which is precisely the kind of inconsistency that erodes user trust.

Keyset pagination — also called cursor-based pagination — uses the values from the last row of the previous page as a starting point for the next query. PostgreSQL seeks directly to that position via the index. Page 1 and page 2,000 take the same time. The tradeoff is that you cannot jump to an arbitrary page number — you must navigate sequentially. For most modern applications (infinite scroll, "load more" buttons, API pagination with cursors), this is not a limitation. For applications that genuinely need "go to page 147," offset pagination remains the only option, and you should consider caching strategies or limiting the maximum page depth.

I should be honest: Tortoise does not have built-in cursor-based pagination. You assemble it yourself from filter(), order_by(), and limit(). This is straightforward — as the example above demonstrates — but it is additional code that you must write and maintain. Django REST Framework and SQLAlchemy both have third-party libraries that provide cursor pagination out of the box. Tortoise does not, at present.

Bulk operations: the difference between 40ms and 5 seconds

Individual create() calls in a loop are the insert equivalent of the N+1 problem. Each call issues a separate INSERT statement and waits for PostgreSQL to acknowledge it. The network round trips accumulate mercilessly.

# Individual creates — one INSERT per row, one round trip per row
for item in item_list:
    await OrderItem.create(
        order=order,
        product_name=item["name"],
        quantity=item["qty"],
        unit_price=item["price"],
    )
# 1,000 items = 1,000 INSERTs = 1,000 round trips = ~3-5 seconds

# bulk_create — one INSERT with multiple value tuples
items = [
    OrderItem(order=order, product_name=i["name"],
              quantity=i["qty"], unit_price=i["price"])
    for i in item_list
]
await OrderItem.bulk_create(items, batch_size=500)
# 1,000 items = 2 INSERTs (batched at 500) = 2 round trips = ~40ms

# bulk_update — UPDATE with CASE/WHEN, batched
for item in items:
    item.unit_price = calculate_new_price(item)
await OrderItem.bulk_update(items, fields=["unit_price"], batch_size=500)
# Same principle: 2 queries instead of 1,000

The numbers are not subtle. For 1,000 rows:

The batch_size parameter controls how many rows are included in each INSERT statement. PostgreSQL handles multi-row inserts efficiently, but excessively large batches (10,000+ rows in a single statement) can cause memory pressure on both the application and the database. Each row in the batch adds parameters to the prepared statement, and asyncpg encodes them all in memory before sending. 500-1,000 is a sensible range for most cases. For very wide tables (many columns), reduce the batch size. For narrow tables (few columns), you can safely increase it.

The RETURNING problem

There is a caveat with bulk_create that the documentation understates, and I feel obligated to address it directly.

# Caveat: bulk_create does not return IDs by default
items = [
    OrderItem(order=order, product_name=i["name"],
              quantity=i["qty"], unit_price=i["price"])
    for i in item_list
]

# This creates the rows but does NOT populate the .id field:
await OrderItem.bulk_create(items, batch_size=500)
# items[0].id is still None

# If you need the IDs, you have two options:

# Option 1: Use ignore_conflicts=False (default) and fetch after
await OrderItem.bulk_create(items, batch_size=500)
# Then query for the IDs you need — which partly defeats the purpose.

# Option 2: Use raw SQL with RETURNING
from tortoise import connections
conn = connections.get("default")
rows = await conn.execute_query_dict("""
    INSERT INTO orderitem (order_id, product_name, quantity, unit_price)
    SELECT unnest($1::int[]),
           unnest($2::text[]),
           unnest($3::int[]),
           unnest($4::numeric[])
    RETURNING id, product_name
""", [order_ids, names, quantities, prices])
# Full PostgreSQL power. IDs returned in a single round trip.

If your workflow requires the auto-generated IDs of the inserted rows — for example, to create child records that reference them — bulk_create leaves you in an awkward position. You must either query for the IDs afterward (adding another round trip and requiring some way to identify which rows you just inserted) or drop to raw SQL with RETURNING. This is a genuine limitation, and it affects a common pattern: creating a parent record and its children in a single operation.

Bulk deletion: the forgotten optimization

# Bulk deletion — often overlooked
# Bad: fetching objects just to delete them
items = await OrderItem.filter(order__status="cancelled")
for item in items:
    await item.delete()  # N queries, N round trips

# Better: delete without fetching
deleted_count = await OrderItem.filter(
    order__status="cancelled"
).delete()
# One DELETE query. Zero model instantiation. Returns row count.

# For conditional deletes with complex logic:
from tortoise import connections
conn = connections.get("default")
await conn.execute_query("""
    DELETE FROM orderitem
    WHERE order_id IN (
        SELECT id FROM "order"
        WHERE status = 'cancelled'
        AND created_at < NOW() - INTERVAL '90 days'
    )
""")
# PostgreSQL handles the subquery. No data leaves the database.

A note on bulk_update: it works by generating UPDATE ... SET field = CASE WHEN id = 1 THEN value1 WHEN id = 2 THEN value2 END. This is effective for modest batch sizes but becomes unwieldy for very large updates. For bulk updates exceeding 5,000 rows, a raw UPDATE ... FROM or a temporary table approach will outperform it. The CASE/WHEN statement grows linearly with the number of rows, and PostgreSQL's planner treats each case as a separate expression to evaluate. At scale, this is neither fast nor elegant.

Aggregations and annotations: let PostgreSQL do the math

A pattern I encounter with distressing frequency: fetching thousands of rows into Python, then computing sums, counts, and averages in application code. PostgreSQL has been computing aggregations since 1996. It is remarkably good at it. Let it work.

from tortoise.functions import Count, Sum, Avg

# Aggregation — computed on the database, not in Python
stats = await Order.filter(status="shipped").annotate(
    item_count=Count("items"),
    order_total=Sum("items__unit_price"),
).values("id", "item_count", "order_total")

# Group-level aggregation
revenue_by_tier = await Customer.annotate(
    total_spent=Sum("orders__total"),
    order_count=Count("orders"),
    avg_order=Avg("orders__total"),
).filter(order_count__gt=0).values(
    "tier", "total_spent", "order_count", "avg_order"
)

# This generates a proper GROUP BY — no Python loops, no N+1.

Tortoise's annotate() generates proper GROUP BY queries with aggregate functions computed on the database. The result set contains the computed values — not the raw rows. For a table with 500,000 orders across 5 tiers, the difference between fetching 500,000 rows and computing in Python versus fetching 5 grouped rows from PostgreSQL is the difference between 2 seconds and 3 milliseconds.

I want to make the magnitude of this concrete. Fetching 500,000 rows means: PostgreSQL reads 500,000 rows from disk or buffer cache, encodes them into the wire protocol, sends them over the network, asyncpg decodes each row, Tortoise creates 500,000 Python dictionaries (or worse, 500,000 model objects), and your Python code iterates all of them to compute a sum. PostgreSQL can answer the same question with a single sequential scan and no data leaving the database. The network cost alone — at roughly 100 bytes per row — is 50MB of data transfer that a GROUP BY eliminates entirely.

Available functions include Count, Sum, Avg, Min, Max, and Lower/Upper/Trim for strings. For anything beyond these — PERCENTILE_CONT, window functions, ARRAY_AGG, conditional aggregation with FILTER (WHERE ...) — you will need the raw query escape hatch. This is not a minor limitation for analytics-heavy applications. The built-in aggregate functions cover perhaps 60% of real-world aggregation needs. The remaining 40% — percentiles, conditional counts, array aggregation, string aggregation — require raw SQL.

The raw SQL escape hatch: knowing when to use it

Every ORM has limits. Tortoise's query builder is competent for standard CRUD and moderate-complexity queries, but PostgreSQL offers capabilities that no ORM fully wraps — window functions, CTEs, lateral joins, full-text search with ranking, PERCENTILE_CONT, recursive queries. When you need these, reach for raw SQL without guilt.

# When Tortoise's query builder cannot express what you need:
from tortoise import connections

conn = connections.get("default")

# Raw query — full PostgreSQL power
results = await conn.execute_query_dict("""
    SELECT c.tier,
           COUNT(*) AS customer_count,
           SUM(o.total) AS revenue,
           PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY o.total) AS median_order
    FROM customer c
    JOIN "order" o ON o.customer_id = c.id
    WHERE o.created_at >= $1
    GROUP BY c.tier
    ORDER BY revenue DESC
""", [cutoff_date])

# execute_query_dict returns List[dict] — no model overhead.
# execute_query returns (count, List[dict]).
# For truly complex analytics, this is faster and clearer than
# chaining fifteen annotate() calls.

Some developers treat raw SQL as a failure of the ORM. It is not. The ORM handles 90% of your queries well. The remaining 10% — analytics, reporting, search, complex aggregations — often deserve hand-written SQL because the optimal query shape is not expressible through a method-chaining API. This is not a criticism of Tortoise; it applies to every ORM.

execute_query_dict() returns a list of dictionaries with no model overhead. For large analytic queries, this is exactly what you want.

CTEs: the query pattern Tortoise cannot express

Common Table Expressions are perhaps the most significant PostgreSQL feature absent from Tortoise's query builder. CTEs allow you to decompose complex queries into named, readable subqueries — and for recursive queries (hierarchical data, graph traversal), they are the only option.

# CTEs (Common Table Expressions) — impossible in Tortoise's query builder
from tortoise import connections

conn = connections.get("default")

# Example: find customers whose spending exceeds their tier average
results = await conn.execute_query_dict("""
    WITH tier_averages AS (
        SELECT c.tier,
               AVG(o.total) AS avg_total
        FROM customer c
        JOIN "order" o ON o.customer_id = c.id
        WHERE o.status = 'shipped'
        GROUP BY c.tier
    ),
    customer_totals AS (
        SELECT c.id, c.name, c.tier,
               SUM(o.total) AS lifetime_total,
               COUNT(o.id) AS order_count
        FROM customer c
        JOIN "order" o ON o.customer_id = c.id
        WHERE o.status = 'shipped'
        GROUP BY c.id, c.name, c.tier
    )
    SELECT ct.name, ct.tier, ct.lifetime_total,
           ct.order_count, ta.avg_total AS tier_average,
           ct.lifetime_total / NULLIF(ta.avg_total, 0) AS ratio
    FROM customer_totals ct
    JOIN tier_averages ta ON ct.tier = ta.tier
    WHERE ct.lifetime_total > ta.avg_total * 2
    ORDER BY ratio DESC
""")
# Three readable, composable subqueries. No ORM can express this
# as cleanly as SQL itself. And there is no shame in that.

I do not regard this as a failing. CTEs are inherently compositional — their value lies in how you combine named subqueries, and this is precisely the kind of flexibility that a method-chaining API cannot capture without becoming more complex than SQL itself. Write your CTEs in raw SQL. Keep them in well-named functions. Document what they compute and why. This is good engineering, not a workaround.

Window functions: analytics that stay in PostgreSQL

If CTEs are the most significant absent feature, window functions are the most useful absent feature. Running totals, rankings, moving averages, lead/lag comparisons — these are table-stakes analytics operations that PostgreSQL handles with extraordinary efficiency.

# Window functions — another raw SQL necessity
from tortoise import connections

conn = connections.get("default")

# Running total and rank per customer
results = await conn.execute_query_dict("""
    SELECT o.id, o.total, o.created_at,
           c.name AS customer_name,
           SUM(o.total) OVER (
               PARTITION BY o.customer_id
               ORDER BY o.created_at
           ) AS running_total,
           ROW_NUMBER() OVER (
               PARTITION BY o.customer_id
               ORDER BY o.total DESC
           ) AS size_rank
    FROM "order" o
    JOIN customer c ON o.customer_id = c.id
    WHERE o.status = 'shipped'
    ORDER BY c.name, o.created_at
""")
# Window functions are among PostgreSQL's most powerful features.
# No Python ORM wraps them adequately. This is fine.
# Use the ORM for CRUD. Use SQL for analytics.

The alternative — fetching all orders and computing running totals in Python — is slower by orders of magnitude and requires holding the entire dataset in memory. Window functions operate on the data where it lives, during the same scan that reads it. There is no data transfer, no serialization overhead, no Python loop. For any application that displays dashboards, leaderboards, or trend analyses, window functions in raw SQL are not optional. They are the correct tool.

Transaction management: correctness before performance

I have focused thus far on making things fast. Allow me to briefly make things correct, because a fast application that produces wrong results is worse than a slow one that produces right ones.

# Transaction management in Tortoise ORM
from tortoise.transactions import atomic, in_transaction

# Decorator style — wraps entire function in a transaction
@atomic()
async def create_order_with_items(customer_id, items_data):
    order = await Order.create(
        customer_id=customer_id,
        total=sum(i["qty"] * i["price"] for i in items_data),
        status="pending",
    )
    items = [
        OrderItem(
            order=order,
            product_name=i["name"],
            quantity=i["qty"],
            unit_price=i["price"],
        )
        for i in items_data
    ]
    await OrderItem.bulk_create(items, batch_size=500)
    return order
# If bulk_create fails, the Order creation is rolled back too.
# Atomic. Consistent. No partial state.

# Context manager style — more granular control
async def transfer_order(order_id, new_customer_id):
    async with in_transaction() as tx:
        order = await Order.select_for_update().get(id=order_id)
        old_customer_id = order.customer_id
        order.customer_id = new_customer_id
        await order.save()

        # Update related audit trail
        await AuditLog.create(
            event_type="order_transfer",
            payload={
                "order_id": order_id,
                "from": old_customer_id,
                "to": new_customer_id,
            },
        )
    # Both operations commit together, or neither does.

Tortoise provides two transaction interfaces: the @atomic() decorator for wrapping entire functions, and the in_transaction() context manager for finer control. Both generate BEGIN/COMMIT/ROLLBACK statements. Both ensure that either all operations within the transaction succeed, or none of them do.

A common mistake: omitting transactions when multiple related writes must succeed or fail together. Creating an order without a transaction means that if bulk_create for the items fails, you have an order with no items. The order exists. The items do not. Your data is inconsistent. This is the kind of bug that does not appear in testing — it appears at 3 AM when a network hiccup causes one query to fail mid-operation.

SELECT FOR UPDATE: preventing race conditions

For operations where concurrent access would produce incorrect results, Tortoise supports PostgreSQL's row-level locking.

# SELECT FOR UPDATE — preventing race conditions
from tortoise.transactions import in_transaction

async def process_order(order_id):
    async with in_transaction():
        # Lock the row until transaction completes
        order = await Order.select_for_update().get(id=order_id)

        if order.status != "pending":
            return {"error": "Order already processed"}

        # Safe to modify — no other transaction can read this row
        # with FOR UPDATE until we commit or rollback
        order.status = "processing"
        await order.save()

        # ... do payment processing, inventory checks, etc.

        order.status = "shipped"
        await order.save()

    return {"status": "shipped"}

# Without select_for_update(), two concurrent requests could both
# read status="pending", both proceed to process, and you ship
# the same order twice. The database lock prevents this.
# It is not optional for any operation where concurrent modification
# would produce incorrect results.

A word of caution: select_for_update() holds a lock on the selected rows until the transaction completes. If your transaction does slow operations while holding the lock — external API calls, heavy computation, sending emails — every other request attempting to access those rows will wait. Keep locked transactions short. Do only the minimum necessary work between the lock acquisition and the commit. If you need to call an external service as part of the operation, do so after the transaction commits, not during it.

PostgreSQL-specific features: arrays, JSONB, and GIN indexes

Tortoise ships with PostgreSQL-specific field types that most guides neglect to mention. These are not cosmetic — they unlock PostgreSQL features that can eliminate entire tables from your schema.

from tortoise.contrib.postgres.fields import ArrayField
from tortoise.indexes import Index

class Product(models.Model):
    id = fields.IntField(pk=True)
    name = fields.CharField(max_length=200)
    tags = ArrayField(element_type="text")  # PostgreSQL array
    metadata = fields.JSONField(default=dict)  # JSONB
    search_vector = fields.CharField(max_length=500)  # for full-text search

    class Meta:
        table = "product"
        indexes = [
            Index(fields=["name"]),  # B-tree (default)
        ]

# For GIN indexes on arrays or JSONB, use raw SQL in migrations
# or aerich custom SQL:
# CREATE INDEX idx_product_tags ON product USING GIN (tags);
# CREATE INDEX idx_product_meta ON product USING GIN (metadata jsonb_path_ops);

# Then query with PostgreSQL array operators:
products = await Product.filter(tags__contains=["organic", "local"])
# Or use raw SQL for full-text search:
results = await conn.execute_query_dict("""
    SELECT * FROM product
    WHERE to_tsvector('english', name) @@ plainto_tsquery('english', $1)
""", [search_term])

A few things worth noting:

ArrayField stores native PostgreSQL arrays. Combined with a GIN index, it enables fast containment queries (@>) without a join table. Tags, permissions, feature flags — if the list is short and rarely queried individually, an array with a GIN index is simpler and faster than a many-to-many relation.

I should offer an honest boundary here: arrays are not a replacement for proper relational tables when the array elements are themselves entities with attributes. If your "tags" are just strings and you only need containment checks (does this product have the tag "organic"?), an array is ideal. If your tags have creation dates, descriptions, usage counts, and relationships to other entities, they deserve their own table. The array optimization is for simple, denormalized data — and that is not a criticism. Denormalization has a place, and arrays are how PostgreSQL does it well.

JSONB: powerful but easy to misuse

# JSONB deep dive — what Tortoise supports and where it stops

# Storing flexible metadata
class AuditLog(models.Model):
    id = fields.IntField(pk=True)
    event_type = fields.CharField(max_length=50)
    payload = fields.JSONField(default=dict)
    created_at = fields.DatetimeField(auto_now_add=True)

# Basic containment query (Tortoise supports this)
logs = await AuditLog.filter(
    payload__contains={"action": "login", "source": "api"}
)
# Generates: WHERE payload @> '{"action": "login", "source": "api"}'
# GIN index on payload makes this fast.

# But for path-based queries, you need raw SQL:
conn = connections.get("default")
results = await conn.execute_query_dict("""
    SELECT id, event_type, payload, created_at
    FROM auditlog
    WHERE payload->>'action' = 'login'
      AND (payload->'metadata'->>'ip_address')::inet
          << '10.0.0.0/8'::inet
    ORDER BY created_at DESC
    LIMIT 100
""")
# jsonb_path_query, nested key extraction, casting —
# none of this is expressible through Tortoise's filter API.
# PostgreSQL's JSON support is deep. The ORM's wrapper is shallow.
# Know where the boundary is.

JSONField maps to PostgreSQL's JSONB type. Tortoise supports basic __contains lookups, but for complex JSON path queries (jsonb_path_query, @? operators), you will need raw SQL. A GIN index with jsonb_path_ops keeps these queries fast.

The honest counterpoint on JSONB: it is extraordinarily flexible, and that flexibility is precisely what makes it dangerous. I have seen teams store entire relational models inside JSONB columns because "it was easier than creating a migration." Six months later, they are writing raw SQL to query nested JSON paths, maintaining application-level consistency checks that PostgreSQL would have enforced for free with foreign keys, and wondering why their queries are slow despite having GIN indexes. JSONB is for genuinely semi-structured data — API payloads, audit metadata, configuration blobs. If you find yourself querying the same JSON paths repeatedly, those paths want to be columns.

Full-text search is not directly supported by Tortoise's field types or query builder. You will need raw SQL to use to_tsvector, plainto_tsquery, and ts_rank. This is one area where SQLAlchemy's PostgreSQL dialect has a meaningful advantage — it provides native TSVectorType and match() support.

For GIN indexes specifically, Tortoise's migration tool Aerich supports custom SQL in migrations. Define your GIN indexes there rather than attempting to express them through the model's Meta.indexes.

How does Tortoise compare to SQLAlchemy async?

This is the question I am most frequently asked, so I shall answer it directly rather than diplomatically.

Tortoise's advantage is simplicity. The Django-inspired API means less ceremony for standard operations, and the async-first design means no session management gymnastics. You do not need to think about AsyncSession, expire_on_commit, or begin() context managers. You call await Order.filter(...) and you get results. There is a directness to Tortoise that SQLAlchemy's layered architecture does not offer.

SQLAlchemy's advantage is breadth. It supports more loading strategies, more PostgreSQL-specific types, more query patterns, and has a vastly larger ecosystem of extensions and documentation. Its async support, while grafted onto a synchronous core, is production-stable and well-tested. It also supports multiple database drivers — you can use asyncpg, psycopg3, or aiosqlite depending on your needs.

For FastAPI applications with straightforward CRUD and moderate query complexity, Tortoise is the simpler choice. For applications that need complex query patterns, full-text search integration, or extensive PostgreSQL-specific features, SQLAlchemy provides more tools. Performance at the query level is comparable — both generate similar SQL and both use asyncpg under the hood.

An honest assessment of Tortoise's limitations

I would be a poor waiter indeed if I praised Tortoise without acknowledging where it falls short. The candor is not optional; it is what makes the recommendation credible.

Ecosystem maturity. Tortoise is younger than SQLAlchemy by over a decade. When you encounter an obscure edge case, Stack Overflow has 47 answers for SQLAlchemy and 2 for Tortoise. The documentation, while adequate, lacks the depth and breadth of SQLAlchemy's. You will read source code more often.

Aerich vs Alembic. Tortoise's migration tool, Aerich, is functional but occasionally struggles with complex schema changes — particularly renaming columns, altering field types, or managing multi-database setups. Alembic, by contrast, has been battle-tested for over a decade and handles nearly every migration scenario. If your schema evolves frequently or in complex ways, this difference matters.

No Unit of Work pattern. SQLAlchemy tracks which objects have been modified and flushes changes to the database in an optimized batch. Tortoise does not — each .save() call issues a separate UPDATE. For operations that modify many objects, you must manually batch with bulk_update. This is not a fundamental problem, but it requires more deliberate coding.

Smaller contributor base. Tortoise's development pace has slowed at times. Major features like SQLAlchemy's with_loader_criteria or hybrid_property do not have Tortoise equivalents and may never. If you are building a large, long-lived application, the long-term maintenance trajectory of your ORM matters.

None of this means Tortoise is the wrong choice. For many applications — particularly FastAPI services with clean, moderate-complexity schemas — it is the right choice precisely because it is simpler. But you should make that choice with open eyes.

Connection pooling and FastAPI integration

Tortoise uses asyncpg as its PostgreSQL driver, which means you inherit asyncpg's excellent connection pooling. The defaults, however, are conservative.

from tortoise import Tortoise

# Connection configuration — the defaults are rarely optimal
await Tortoise.init(
    db_url="postgres://user:pass@localhost:5432/mydb",
    modules={"models": ["app.models"]},

    # Connection pool settings (uses asyncpg under the hood)
    # Default min_size=1, max_size=5 — too small for most apps
    # Rule of thumb: max_size = 2-3x your CPU cores
)

# Or with explicit connection parameters:
await Tortoise.init(
    config={
        "connections": {
            "default": {
                "engine": "tortoise.backends.asyncpg",
                "credentials": {
                    "host": "localhost",
                    "port": 5432,
                    "user": "app_user",
                    "password": "secret",
                    "database": "mydb",
                    "minsize": 5,   # minimum pool connections
                    "maxsize": 20,  # maximum pool connections
                },
            }
        },
        "apps": {
            "models": {
                "models": ["app.models"],
            }
        },
    }
)

The pool size defaults (minsize=1, maxsize=5) are appropriate for development but inadequate for production workloads. A FastAPI application handling 100 concurrent requests with a pool of 5 connections will queue 95 of them — adding latency that has nothing to do with query performance.

Allow me to be specific about sizing. The commonly cited formula is maxsize = 2-3x CPU cores, but this deserves nuance. If your queries are fast (under 5ms), fewer connections are needed because they are returned to the pool quickly. If your queries include slow operations — joins on large tables, aggregations, or raw analytic queries — you need more connections because they are held longer. The right number depends on your query latency distribution, not just your CPU count.

A practical approach: start with maxsize = cores * 2, monitor with pg_stat_activity, and increase if you see connection queuing. PostgreSQL itself has a limit — max_connections, typically 100 by default. If you have multiple application instances, the total pool size across all instances must not exceed this. For many-instance deployments, a connection pooler like PgBouncer sits between your application pools and PostgreSQL, multiplexing many application connections over fewer database connections.

Monitoring pool health

# Monitoring your connection pool health
from tortoise import connections

async def pool_health_check():
    """Check pool utilization — expose via health endpoint."""
    conn = connections.get("default")

    # asyncpg pool stats (accessed via internal pool)
    pool = conn._pool  # internal, but useful for monitoring
    return {
        "pool_size": pool.get_size(),
        "pool_free": pool.get_idle_size(),
        "pool_used": pool.get_size() - pool.get_idle_size(),
        "pool_min": pool.get_min_size(),
        "pool_max": pool.get_max_size(),
    }

# PostgreSQL side — check active connections
async def pg_connection_stats():
    conn = connections.get("default")
    return await conn.execute_query_dict("""
        SELECT state, count(*) as count,
               avg(EXTRACT(EPOCH FROM (now() - state_change)))::numeric(10,2)
                   AS avg_seconds_in_state
        FROM pg_stat_activity
        WHERE datname = current_database()
          AND pid != pg_backend_pid()
        GROUP BY state
        ORDER BY count DESC
    """)
    # Healthy output: mostly "idle" connections.
    # Unhealthy: many "active" or "idle in transaction" connections.

Expose pool metrics through a health endpoint. If pool_used consistently approaches pool_max, you are undersized. If pool_free is consistently equal to pool_max, you are oversized — those idle connections consume memory on both the application and PostgreSQL sides. Idle connections are not free.

FastAPI integration

The FastAPI integration is, I must say, one of Tortoise's genuine selling points.

from fastapi import FastAPI
from tortoise.contrib.fastapi import register_tortoise

app = FastAPI()

# One function call. Tortoise handles startup/shutdown lifecycle.
register_tortoise(
    app,
    db_url="postgres://user:pass@localhost:5432/mydb",
    modules={"models": ["app.models"]},
    generate_schemas=True,  # dev only — creates tables on startup
    add_exception_handlers=True,
)

# Your endpoint — just use await
@app.get("/orders/pending")
async def pending_orders():
    return await Order.filter(
        status="pending"
    ).prefetch_related(
        "customer"
    ).values(
        "id", "total", "customer__name", "created_at"
    )
# No session management, no dependency injection for DB access.
# The connection pool handles everything.

Compare this to SQLAlchemy's async setup, which requires creating an engine, a session factory, an async session dependency, and careful lifecycle management. Tortoise's register_tortoise handles all of it. For teams that value simplicity and have straightforward database needs, this matters.

Pydantic integration: automatic schema generation

Tortoise includes a Pydantic model generator that creates serialization schemas directly from your ORM models. For FastAPI projects, this eliminates an entire category of boilerplate.

# Tortoise + Pydantic integration — automatic schema generation
from tortoise.contrib.pydantic import pydantic_model_creator

# Generate Pydantic models from Tortoise models
OrderOut = pydantic_model_creator(Order, name="OrderOut")
OrderIn = pydantic_model_creator(
    Order, name="OrderIn", exclude_readonly=True
)
CustomerOut = pydantic_model_creator(
    Customer, name="CustomerOut",
    include=("id", "name", "email", "tier")  # explicit field selection
)

@app.get("/orders/{order_id}", response_model=OrderOut)
async def get_order(order_id: int):
    return await OrderOut.from_queryset_single(
        Order.get(id=order_id).prefetch_related("customer", "items")
    )

@app.get("/orders/", response_model=list[OrderOut])
async def list_orders(status: str = "pending", limit: int = 50):
    return await OrderOut.from_queryset(
        Order.filter(status=status).prefetch_related(
            "customer"
        ).limit(limit)
    )
# Pydantic serialization + Tortoise querying in one call.
# The generated schemas appear in FastAPI's /docs automatically.

The generated Pydantic models include field types, validation constraints, and optional field selection — and they appear automatically in FastAPI's OpenAPI documentation. This is a meaningful productivity advantage over SQLAlchemy, where you typically maintain separate Pydantic models by hand.

The caveat: the generated models are not always exactly what you want. Computed fields, custom serialization logic, and complex nested structures may still require hand-written Pydantic models. But for 80% of CRUD endpoints, the generator is sufficient and saves real time.

Migrations with Aerich

No performance guide would be complete without addressing schema management, because the fastest query in the world means nothing if your schema cannot evolve safely.

# Aerich — Tortoise's migration tool
# Install: pip install aerich

# Initialize (once)
# aerich init -t app.config.TORTOISE_ORM
# aerich init-db

# Create migration after model changes
# aerich migrate --name add_customer_tier
# aerich upgrade

# The config Aerich expects:
TORTOISE_ORM = {
    "connections": {
        "default": "postgres://user:pass@localhost:5432/mydb"
    },
    "apps": {
        "models": {
            "models": ["app.models", "aerich.models"],
            "default_connection": "default",
        }
    },
}

# Custom SQL in migrations (for GIN indexes, etc.)
# aerich generates Python migration files. You can add raw SQL:
# In the generated migration file:
async def upgrade(db):
    await db.execute_query(
        "CREATE INDEX idx_product_tags ON product USING GIN (tags)"
    )

async def downgrade(db):
    await db.execute_query("DROP INDEX IF EXISTS idx_product_tags")

Aerich is Tortoise's official migration tool. It auto-generates migration files from model changes, supports upgrade and downgrade paths, and integrates with Tortoise's connection configuration.

What Aerich does well: detecting new fields, new models, basic field type changes, and generating the corresponding SQL. For simple schema evolution — adding columns, adding tables, adding indexes — it works reliably.

Where Aerich requires caution: column renames (Aerich may interpret these as a drop + add, which destroys data), complex type changes (e.g., changing a CharField to a JSONField), and any operation that requires data migration alongside schema migration. For these, write the migration SQL by hand in the custom migration function. It is five minutes of work that prevents data loss. I regard this as a reasonable tradeoff.

One final note on Aerich: always run aerich upgrade against a copy of your production database — not production itself — before deploying. Migration tools generate SQL based on the current model definitions. If the production schema has drifted (manual changes, failed partial migrations), the generated SQL may not apply cleanly. Test first. Deploy second.

Testing Tortoise ORM applications

Performance optimizations are only valuable if they are verified, and verification requires tests. Tortoise provides a testing framework, though it requires some consideration.

# Testing with Tortoise ORM
from tortoise.contrib.test import TestCase, initializer, finalizer
import unittest

# Option 1: Tortoise's built-in test framework
class TestOrderSummaries(TestCase):
    async def test_prefetch_reduces_queries(self):
        # Create test data
        customer = await Customer.create(name="Test", email="t@t.com")
        for i in range(10):
            await Order.create(
                customer=customer, total=i * 10, status="pending"
            )

        # This should issue exactly 2 queries, not 11
        orders = await Order.filter(
            status="pending"
        ).prefetch_related("customer")

        for order in orders:
            # Accessing .customer should NOT trigger a query
            self.assertEqual(order.customer.name, "Test")

# Option 2: Use an in-memory SQLite for fast tests
# (but beware: SQLite lacks PostgreSQL-specific features)
initializer(
    ["app.models"],
    db_url="sqlite://:memory:",
)

# Option 3: Use a real PostgreSQL test database
# This is slower but catches PostgreSQL-specific issues
initializer(
    ["app.models"],
    db_url="postgres://user:pass@localhost:5432/test_mydb",
)

The honest tension: testing against SQLite is fast (in-memory, no setup, no cleanup) but misses PostgreSQL-specific behavior. Testing against PostgreSQL is accurate but slower and requires a running database instance. For unit tests of business logic, SQLite is fine. For integration tests that verify query performance, index usage, or PostgreSQL-specific features like JSONB queries and array containment, you must test against PostgreSQL.

A practical approach: run your test suite against SQLite in development for speed, and against PostgreSQL in CI for accuracy. If a test passes on SQLite and fails on PostgreSQL, you have found a portability assumption that needs addressing. These are valuable discoveries, not inconveniences.

A performance checklist for Tortoise ORM

To summarize the patterns that matter most, in order of impact:

1. Eliminate N+1 queries. Use prefetch_related for every relationship you access in a loop. Use select_related for single ForeignKey joins. This alone can improve endpoint latency by 10-100x. Enable query logging in development to catch them early.
2. Use values() for read-only responses. If the endpoint returns JSON, skip model instantiation. 30-50% faster for large result sets. Combine with annotate() for dashboard endpoints.
3. Bulk your writes. bulk_create and bulk_update with sensible batch sizes (500-1,000). Never loop over individual create() calls. Use .delete() on querysets instead of fetching then deleting.
4. Push computation to PostgreSQL. Use annotate() for aggregations. Use raw SQL for window functions, CTEs, and complex analytics. Fetch 5 grouped rows, not 500,000 raw rows.
5. Use keyset pagination. Offset pagination degrades linearly with page depth. Cursor-based pagination performs consistently regardless of position.
6. Size your connection pool. maxsize of 2-3x your CPU cores as a starting point. Monitor with pg_stat_activity and pool health checks. Consider PgBouncer for multi-instance deployments.
7. Use PostgreSQL-specific types. Arrays with GIN indexes for simple lists. JSONB for semi-structured data. Raw SQL for full-text search. But do not use JSONB as a substitute for proper relational modeling.
8. Know when to use raw SQL. Window functions, CTEs, complex aggregations, full-text search — reach for execute_query_dict() without apology. The ORM handles CRUD. SQL handles analytics.
9. Wrap related writes in transactions. @atomic() or in_transaction(). Use select_for_update() for operations where concurrent access would produce incorrect results.
10. Ensure your indexes match your queries. The ORM generates the SQL. You ensure the indexes exist. Composite indexes for multi-column filters. GIN indexes for array and JSONB containment. Check EXPLAIN ANALYZE for your critical paths.

When Tortoise is not the right choice

A waiter who recommends his own household for every occasion is no waiter at all — he is a salesman. There are situations where Tortoise ORM is not the best tool for the task, and I would rather you know this now than discover it six months into a project.

Heavy analytics workloads. If your application is primarily analytics — dashboards, reports, data exploration — you will spend most of your time in raw SQL. The ORM adds little value when 70% of your queries are CTEs with window functions. Consider SQLAlchemy Core (not the ORM, just the query builder) or a dedicated analytics library like SQLAlchemy with its expression language.

Complex domain models. If your domain has deep inheritance hierarchies, polymorphic associations, or complex lifecycle hooks, SQLAlchemy's identity map, unit of work, and event system provide tools that Tortoise simply does not have. Tortoise models are flat and direct. This is a strength for simple schemas and a limitation for complex ones.

Multi-database applications. Tortoise supports multiple named connections, but its cross-database query support is limited. If you need to join data across databases, or route queries to read replicas based on the operation type, SQLAlchemy provides more sophisticated connection management.

Applications that may leave async. Tortoise has no synchronous mode. If there is any chance your application might need synchronous database access — for a management command, a migration script, a Celery task — Tortoise cannot provide it without running an event loop. SQLAlchemy works in both modes.

For FastAPI applications with clean CRUD schemas, moderate complexity, and a team that values simplicity, Tortoise remains an excellent choice. Know your application's trajectory, and choose accordingly.

What happens below the ORM

Every optimization in this guide operates at the application layer — choosing the right Tortoise methods, structuring queries to minimize round trips, using bulk operations. These are essential, and they are entirely within your control.

There is, however, a layer below. The SQL that Tortoise generates arrives at PostgreSQL, where the query planner decides how to execute it. Missing indexes cause sequential scans. Stale statistics lead to bad join strategies. Repeated expensive queries compute the same result thousands of times per hour. A well-written Tortoise query can still execute poorly if the database infrastructure beneath it is unattended.

This is, if you will permit the observation, where the division of labor matters most. You — the developer — control the application layer. You write good Tortoise code. You use prefetch_related, values(), bulk_create. You choose keyset pagination over offset pagination. You push aggregations to PostgreSQL. These are engineering decisions, and they are yours to make well.

The infrastructure layer — index creation based on actual access patterns, query plan optimization, materialized view management, connection pool tuning — is a different discipline. It requires continuous observation of how your queries actually behave under production load, not just how you intended them to behave.

Gold Lapel sits between your application and PostgreSQL — a proxy that observes the actual query traffic. It detects missing indexes from access patterns and creates them. It identifies repeated expensive queries and materializes the results. It catches N+1 patterns that escaped code review and batches them before they reach the database.

Tortoise sends the SQL. Gold Lapel ensures PostgreSQL executes it optimally. They operate at different layers, and the combination is more effective than either alone. Write good Tortoise code — and let the infrastructure attend to the rest.