Skip to main content

ORM, Search, & Migrations: OneHub Interview Masterclass

Q1: Explain the 'N+1 Problem' and how to solve it in SQLAlchemy and Eloquent.​

Technical Answer: The N+1 problem occurs when an ORM executes one query to fetch a list of parent records, and then N additional queries to fetch related child records for each parent. To solve it, use "Eager Loading" (SQLAlchemy: joinedload or subqueryload; Eloquent: with()). Code/Syntax Justification:

# SQLAlchemy (N+1)
users = session.query(User).all()
for u in users: print(u.posts) # Hits DB N times

# SQLAlchemy (Fixed)
users = session.query(User).options(joinedload(User.posts)).all()

# Eloquent (Laravel)
$users = User::with('posts')->get();

🔍 Behind the Question: Database performance and understanding how ORMs abstract SQL. ❌ The Red Flags: Suggesting adding indexes to fix N+1 (indexes help the child query but don't stop the number of queries); not knowing the difference between Join and Subquery loading.


Q2: Unit of Work vs. Active Record: What are they and which does SQLAlchemy/Eloquent use?​

Technical Answer: Active Record (Eloquent, Django) maps a row to an object. The object has save() and delete() methods. Unit of Work/Data Mapper (SQLAlchemy) separates the object from the database logic. A Session tracks changes and flushes them all at once. Code/Syntax Justification:

// Active Record (Eloquent)
$user = new User;
$user->name = 'John';
$user->save(); // Direct DB sync

// Unit of Work (SQLAlchemy)
user = User(name='John')
session.add(user) # Just tracking
session.commit() # Flushes all changes in one transaction

🔍 Behind the Question: Architectural patterns and state management. ❌ The Red Flags: Thinking they are the same; not understanding that Unit of Work allows for more complex transactions and batching.


Q3: What are Database Migrations, and why is 'Alembic' or 'Artisan' necessary?​

Technical Answer: Migrations are version control for your database schema. They provide a reproducible way to evolve the DB state across environments. Tools like Alembic (Python) or Artisan (PHP) generate migration files (up/down scripts) and track which versions have been applied to the local/prod DB. Code/Syntax Justification:

# Alembic
alembic revision --autogenerate -m "add_user_table"
alembic upgrade head

# Laravel
php artisan make:migration create_users_table
php artisan migrate

🔍 Behind the Question: DevOps practices and schema management. ❌ The Red Flags: Manually running SQL ALTER TABLE in production; not checking migration files into Git.


Q4: How do you handle "Race Conditions" when updating a record in an ORM?​

Technical Answer: Use Optimistic Locking (version columns) or Pessimistic Locking (SELECT FOR UPDATE). Optimistic assumes no conflict and fails on save if the version changed. Pessimistic locks the row at the DB level until the transaction finishes. Code/Syntax Justification:

# SQLAlchemy Pessimistic Lock
user = session.query(User).with_for_update().filter_by(id=1).one()
user.balance += 100
session.commit()

🔍 Behind the Question: Concurrency control and data integrity. ❌ The Red Flags: "Checking then updating" without a lock or transaction (the classic TOCTOU bug).


Q5: What is a "Composite Index" and how does 'Prefix Rule' apply?​

Technical Answer: A composite index covers multiple columns (e.g., (last_name, first_name)). The Prefix Rule (or Left-most Prefix) states that the index is only useful if the query filters by the first column in the index. Code/Syntax Justification:

# Query: filter(User.last_name == 'Doe') -> Uses index
# Query: filter(User.first_name == 'John') -> DOES NOT use index
Index('idx_name', User.last_name, User.first_name)

🔍 Behind the Question: Query optimization and DB internals. ❌ The Red Flags: Thinking the order of columns in a composite index doesn't matter.


Q6: Explain the 'Identity Map' pattern in ORMs.​

Technical Answer: The Identity Map ensures that within a single session/request, the ORM only ever creates one instance of a specific database row. If you query the same User ID twice, you get the exact same Python/PHP object reference. Code/Syntax Justification:

u1 = session.query(User).get(1)
u2 = session.query(User).get(1)
print(u1 is u2) # True

🔍 Behind the Question: Memory management and object state consistency. ❌ The Red Flags: Expecting different objects for the same row; not realizing that modifying u1 also modifies u2.


Q7: When should you use a Search Engine (Elasticsearch/Meilisearch) vs. SQL 'LIKE'?​

Technical Answer: Use SQL LIKE for simple, exact prefix matches on small datasets. Use Search Engines for: Full-text search (BM25 ranking), fuzzy matching (typo tolerance), faceted search, high-volume indexing, and "Search-as-you-type" performance. Code/Syntax Justification:

# Meilisearch Python
client.index('movies').search('batman', {
'filter': 'genre = action',
'facets': ['genre']
})

🔍 Behind the Question: System design and choosing the right tool for the job. ❌ The Red Flags: Using LIKE '%term%' on a table with millions of rows (kills performance); indexing every single column in Elasticsearch (huge overhead).


Q8: How do you implement "Soft Deletes" and what are the pitfalls?​

Technical Answer: Soft deletes use a deleted_at timestamp instead of removing the row. Pitfall: You must remember to filter out deleted rows in every query (though most ORMs have "Global Scopes" to do this automatically). Code/Syntax Justification:

// Eloquent
use SoftDeletes;
// SQL equivalent: SELECT * FROM users WHERE deleted_at IS NULL;

🔍 Behind the Question: Data retention and query complexity. ❌ The Red Flags: Forgetting to handle Unique Constraints (you can't have two 'active' users with the same email, but what if one is soft-deleted?).


Q9: Explain the 'Data Mapper' vs 'Active Record' trade-offs in testing.​

Technical Answer: Data Mapper (SQLAlchemy) is easier to mock because the domain models are plain objects with no DB dependencies. Active Record (Eloquent) often requires a database connection or complex mocking of the static ::save() methods to test. Code/Syntax Justification:

# SQLAlchemy model is a POPO (Plain Old Python Object)
user = User(name="Test")
assert user.name == "Test" # No DB needed

🔍 Behind the Question: Testability and decoupling. ❌ The Red Flags: Writing "unit tests" that hit a real database every time.


Q10: What is "Database Normalization" and when is "Denormalization" acceptable?​

Technical Answer: Normalization (1NF-3NF) reduces redundancy and ensures data integrity. Denormalization (adding redundant data) is acceptable to optimize read performance in high-scale systems, specifically to avoid complex, slow joins. Code/Syntax Justification: Example: Storing comment_count on a Post table instead of counting Comments every time. 🔍 Behind the Question: Database design fundamentals. ❌ The Red Flags: Over-normalizing to the point where simple queries require 10 joins; denormalizing without a strategy to keep redundant data in sync.


Section 2: SQLAlchemy ORM Deep Dive​

Q11: What is the SQLAlchemy Unit of Work pattern and how does session.flush() differ from session.commit()?​

Technical Answer: The Unit of Work tracks all changes (inserts, updates, deletes) to ORM objects within a Session. flush() writes pending changes to the database as SQL statements within the current transaction — but does NOT commit. commit() calls flush() then commits the transaction, making changes permanent and visible to other connections. Code/Syntax Justification:

from sqlalchemy.orm import Session

with Session(engine) as session:
user = User(name="Alice", email="alice@example.com")
session.add(user)

session.flush() # INSERT executed, user.id is now populated
print(user.id) # Works — DB assigned the ID
# BUT — if we crash here, the INSERT is rolled back

session.commit() # Transaction committed — data is permanent

🔍 Behind the Question: Understanding transaction boundaries and the ORM's persistence lifecycle. ❌ The Red Flags: Calling commit() after every single add() — defeats batching and transactional atomicity.


Q12: How do you implement eager loading vs lazy loading in SQLAlchemy?​

Technical Answer: Lazy loading (default) issues a SELECT per relationship access — the N+1 problem. Eager loading pre-fetches related objects in the original query. SQLAlchemy provides joinedload (LEFT JOIN), subqueryload (separate SELECT with IN), and selectinload (SELECT ... WHERE id IN). Code/Syntax Justification:

from sqlalchemy.orm import joinedload, selectinload, subqueryload

# N+1 problem — lazy loading
users = session.query(User).all()
for user in users:
print(user.orders) # SELECT per user — 100 users = 101 queries

# Fix: eager loading
users = session.query(User).options(
selectinload(User.orders) # SELECT ... WHERE user_id IN (1,2,3...)
).all()
# Only 2 queries total: 1 for users, 1 for all their orders

# Joined load (single query with LEFT JOIN)
users = session.query(User).options(
joinedload(User.orders)
).all()
# 1 query with LEFT JOIN — but may duplicate user rows

# Nested eager loading
users = session.query(User).options(
selectinload(User.orders).selectinload(Order.items)
).all()

🔍 Behind the Question: Query optimization — the #1 ORM performance issue. ❌ The Red Flags: Printing len(user.orders) in a loop and not noticing 1000 extra queries in the log.


Q13: How do you implement soft deletes in SQLAlchemy?​

Technical Answer: Add an is_deleted or deleted_at column. Override the default query to filter out deleted records using query_class or SQLAlchemy events. Use @event.listens_for(Session, "do_orm_execute") to inject a WHERE deleted_at IS NULL filter automatically. Code/Syntax Justification:

from sqlalchemy import Column, DateTime, Boolean
from sqlalchemy.orm import Query
from datetime import datetime

class SoftDeleteMixin:
deleted_at = Column(DateTime, nullable=True, default=None)

def soft_delete(self):
self.deleted_at = datetime.utcnow()

class User(Base, SoftDeleteMixin):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)

# Automatic filter using event
from sqlalchemy import event

@event.listens_for(Session, "do_orm_execute")
def _filter_soft_deleted(execute_state):
if execute_state.is_select:
execute_state.statement = execute_state.statement.where(
User.deleted_at.is_(None)
)

# Usage
user.soft_delete()
session.commit()
# user is still in DB but filtered from queries

🔍 Behind the Question: Data retention, GDPR compliance, and undo capabilities. ❌ The Red Flags: session.delete(user) with CASCADE — permanently losing related orders, payments, and audit history.


Q14: What is SQLAlchemy's hybrid property and when do you use it?​

Technical Answer: @hybrid_property creates a property that works both at the Python level (on instances) and at the SQL level (in queries). At the Python level, it computes a value from the instance. At the SQL level, it generates a SQL expression that can be used in WHERE, ORDER BY, etc. Code/Syntax Justification:

from sqlalchemy.ext.hybrid import hybrid_property

class User(Base):
__tablename__ = 'users'
first_name = Column(String)
last_name = Column(String)

@hybrid_property
def full_name(self):
return f"{self.first_name} {self.last_name}"

@full_name.expression
def full_name(cls):
return cls.first_name + " " + cls.last_name

# Works on instances
user = User(first_name="John", last_name="Doe")
print(user.full_name) # "John Doe"

# Works in queries — generates SQL
users = session.query(User).filter(User.full_name == "John Doe").all()
# SELECT * FROM users WHERE first_name || ' ' || last_name = 'John Doe'

🔍 Behind the Question: ORM expressiveness and query-level computed properties. ❌ The Red Flags: Filtering in Python after loading all rows — [u for u in users if u.full_name == "John Doe"] — pulls entire table into memory.


Q15: How do you implement multi-tenancy in SQLAlchemy?​

Technical Answer: Three approaches: (1) Schema-per-tenant: separate PostgreSQL schemas, switch via search_path. (2) Row-level: shared tables with a tenant_id column, filtered via SQLAlchemy events. (3) Database-per-tenant: separate connections per tenant. Row-level is most common in SaaS. Code/Syntax Justification:

# Row-level multi-tenancy with automatic filtering
from sqlalchemy import event
from contextvars import ContextVar

current_tenant_id = ContextVar('current_tenant_id')

class TenantMixin:
tenant_id = Column(UUID, nullable=False, index=True)

@event.listens_for(Session, "do_orm_execute")
def _filter_by_tenant(execute_state):
if execute_state.is_select:
tenant_id = current_tenant_id.get(None)
if tenant_id:
for mapper in execute_state.all_mappers:
if hasattr(mapper.class_, 'tenant_id'):
execute_state.statement = execute_state.statement.where(
mapper.class_.tenant_id == tenant_id
)

# FastAPI middleware
@app.middleware("http")
async def set_tenant(request, call_next):
tenant = extract_tenant_from_jwt(request)
current_tenant_id.set(tenant.id)
return await call_next(request)

🔍 Behind the Question: SaaS architecture and data isolation patterns. ❌ The Red Flags: No tenant filtering — Tenant A sees Tenant B's data (data breach).


Q16: How do you implement optimistic locking in SQLAlchemy?​

Technical Answer: Add a version_id column. SQLAlchemy's version_id_col feature automatically increments it on update and adds WHERE version_id = N to UPDATE statements. If another transaction updated the row first, the WHERE clause matches zero rows and SQLAlchemy raises StaleDataError. Code/Syntax Justification:

class Product(Base):
__tablename__ = 'products'
id = Column(Integer, primary_key=True)
name = Column(String)
price = Column(Numeric)
version_id = Column(Integer, nullable=False, default=1)

__mapper_args__ = {
"version_id_col": version_id
}

# Usage
product = session.get(Product, 1)
product.price = 29.99 # Increment version automatically

try:
session.commit()
# Generated SQL: UPDATE products SET price=29.99, version_id=2
# WHERE id=1 AND version_id=1
except StaleDataError:
session.rollback()
# Another transaction modified this row — handle conflict

🔍 Behind the Question: Concurrent write safety without pessimistic locking overhead. ❌ The Red Flags: No version check — last write wins, silently overwriting concurrent changes.


Q17: How do you bulk insert efficiently in SQLAlchemy?​

Technical Answer: Use session.bulk_insert_mappings() or session.execute(insert(Model), list_of_dicts) for raw bulk inserts. Avoid session.add() in a loop — it creates ORM objects with full lifecycle tracking (identity map, events), which is 10-50x slower for bulk operations. Code/Syntax Justification:

# SLOW — ORM object creation per row (1000 objects in identity map)
for data in large_dataset:
session.add(User(**data))
session.commit() # ~5 seconds for 10,000 rows

# FAST — bulk insert mappings (bypasses ORM overhead)
session.bulk_insert_mappings(User, large_dataset)
session.commit() # ~0.5 seconds for 10,000 rows

# FASTEST — Core insert (no ORM at all)
from sqlalchemy import insert
session.execute(insert(User), large_dataset)
session.commit() # ~0.1 seconds for 10,000 rows

# PostgreSQL COPY (fastest possible — psycopg2)
import io, csv
buffer = io.StringIO()
writer = csv.writer(buffer)
for row in large_dataset:
writer.writerow(row.values())
buffer.seek(0)
cursor.copy_from(buffer, 'users', sep=',')

🔍 Behind the Question: Performance awareness — ORM convenience has a cost at scale. ❌ The Red Flags: Using session.add() to insert 1 million rows — takes 30 minutes instead of 30 seconds.


Q18: How do you implement database connection pooling in SQLAlchemy?​

Technical Answer: SQLAlchemy uses QueuePool by default — maintains a pool of persistent connections reused across requests. Configure pool_size (max connections), max_overflow (temporary extras), pool_recycle (reconnect stale connections), and pool_pre_ping (check connection health before use). Code/Syntax Justification:

from sqlalchemy import create_engine

engine = create_engine(
"postgresql://user:pass@localhost/mydb",
pool_size=10, # Base pool of 10 connections
max_overflow=20, # Allow up to 30 total (10 + 20 overflow)
pool_recycle=3600, # Recycle connections older than 1 hour
pool_pre_ping=True, # Test connection health before checkout
pool_timeout=30, # Wait max 30s for available connection
echo_pool=True, # Log pool checkouts/checkins
)

# For async (SQLAlchemy 2.0+)
from sqlalchemy.ext.asyncio import create_async_engine

async_engine = create_async_engine(
"postgresql+asyncpg://user:pass@localhost/mydb",
pool_size=10,
max_overflow=20,
)

🔍 Behind the Question: Connection management and resource efficiency. ❌ The Red Flags: Creating a new engine per request — each one creates a new connection pool, exhausting database connections.


Q19: How do you implement polymorphic models (inheritance) in SQLAlchemy?​

Technical Answer: SQLAlchemy supports three inheritance strategies: (1) Single Table Inheritance (STI): one table, discriminator column. (2) Joined Table Inheritance: separate tables with foreign keys. (3) Concrete Table Inheritance: completely separate tables, no FK relationship. STI is most common and performant. Code/Syntax Justification:

# Single Table Inheritance
class Employee(Base):
__tablename__ = 'employees'
id = Column(Integer, primary_key=True)
name = Column(String)
type = Column(String(50))
__mapper_args__ = {
'polymorphic_identity': 'employee',
'polymorphic_on': type,
}

class Manager(Employee):
department = Column(String)
__mapper_args__ = {'polymorphic_identity': 'manager'}

class Engineer(Employee):
language = Column(String)
__mapper_args__ = {'polymorphic_identity': 'engineer'}

# Query returns correct subclass instances
employees = session.query(Employee).all()
# [<Manager>, <Engineer>, <Employee>] — automatically instantiated

engineers = session.query(Engineer).all()
# Only engineers — WHERE type='engineer' added automatically

🔍 Behind the Question: Advanced ORM modeling for domain hierarchies. ❌ The Red Flags: Duplicating 90% of columns across separate tables instead of using inheritance — maintenance nightmare.


Q20: How do you use SQLAlchemy events for audit logging?​

Technical Answer: SQLAlchemy's event system lets you intercept ORM operations at multiple levels: before_insert, after_update, after_delete on mappers, or before_flush on sessions. Use these to automatically capture audit trails without modifying business logic. Code/Syntax Justification:

from sqlalchemy import event, inspect

class AuditMixin:
created_at = Column(DateTime, default=datetime.utcnow)
updated_at = Column(DateTime, onupdate=datetime.utcnow)
updated_by = Column(String)

@event.listens_for(Session, "before_flush")
def audit_changes(session, flush_context, instances):
for obj in session.dirty:
if isinstance(obj, AuditMixin):
changes = {}
insp = inspect(obj)
for attr in insp.attrs:
hist = attr.history
if hist.has_changes():
changes[attr.key] = {
'old': hist.deleted[0] if hist.deleted else None,
'new': hist.added[0] if hist.added else None,
}
if changes:
AuditLog.create(
entity=obj.__tablename__,
entity_id=obj.id,
changes=json.dumps(changes),
user_id=get_current_user_id(),
)

🔍 Behind the Question: Cross-cutting concerns without polluting business logic. ❌ The Red Flags: Manually calling create_audit_log() in every service method — easy to forget, inconsistent coverage.


Section 3: Alembic Migrations Deep Dive​

Q21: What is Alembic and how does it integrate with SQLAlchemy?​

Technical Answer: Alembic is the official database migration tool for SQLAlchemy. It generates migration scripts by comparing SQLAlchemy model definitions against the current database schema (autogenerate). Migrations are versioned Python scripts that run upgrade() and downgrade() functions to evolve the schema. Code/Syntax Justification:

# Initialize Alembic
alembic init alembic

# Generate migration from model changes
alembic revision --autogenerate -m "add users table"

# Apply migrations
alembic upgrade head

# Rollback one step
alembic downgrade -1

# Show current revision
alembic current

# Show migration history
alembic history --verbose

🔍 Behind the Question: Schema evolution management in production. ❌ The Red Flags: Running Base.metadata.create_all() in production — no versioning, no rollback, no migration history.


Q22: How do you write a safe data migration in Alembic?​

Technical Answer: Data migrations modify existing data (not just schema). They must be: idempotent (safe to run twice), batched (process in chunks to avoid locking), and wrapped in transactions. Use op.execute() for SQL or op.get_bind() to use SQLAlchemy Core. Code/Syntax Justification:

from alembic import op
import sqlalchemy as sa

def upgrade():
# Schema change
op.add_column('users', sa.Column('full_name', sa.String(255)))

# Data migration — populate new column from existing columns
conn = op.get_bind()
users = sa.table('users',
sa.column('id', sa.Integer),
sa.column('first_name', sa.String),
sa.column('last_name', sa.String),
sa.column('full_name', sa.String),
)

# Batch update to avoid locking entire table
batch_size = 1000
while True:
result = conn.execute(
users.update()
.where(users.c.full_name.is_(None))
.values(full_name=users.c.first_name + ' ' + users.c.last_name)
.limit(batch_size)
)
if result.rowcount == 0:
break

def downgrade():
op.drop_column('users', 'full_name')

🔍 Behind the Question: Safe data transformations in production databases. ❌ The Red Flags: UPDATE users SET full_name = ... on 10 million rows without batching — locks the entire table for minutes.


Q23: How do you handle zero-downtime migrations with Alembic?​

Technical Answer: Split breaking migrations into multiple safe steps deployed across releases. Never rename or drop columns in one step. Pattern: (1) Add new column. Deploy code that writes to both old and new. (2) Backfill data. (3) Switch reads to new column. (4) Drop old column in a later release. Code/Syntax Justification:

# Release 1: Add new column (non-breaking)
def upgrade():
op.add_column('users', sa.Column('email_address', sa.String(255)))

# Deploy app code: write to BOTH 'email' and 'email_address'

# Release 2: Backfill (non-breaking)
def upgrade():
op.execute("UPDATE users SET email_address = email WHERE email_address IS NULL")

# Deploy app code: read from 'email_address', still write to both

# Release 3: Drop old column (safe — no code references it anymore)
def upgrade():
op.drop_column('users', 'email')

# NEVER do this in one migration:
# op.alter_column('users', 'email', new_column_name='email_address')
# ↑ Breaks all running code that references 'email'

🔍 Behind the Question: Production deployment safety and zero-downtime schema evolution. ❌ The Red Flags: Renaming a column in one migration while the app is running — instant 500 errors on all queries referencing the old name.


Q24: How do you handle Alembic migration conflicts in a team?​

Technical Answer: When two developers create migrations from the same head, Alembic creates a "branched" history. Resolve by: (1) one developer rebases their migration to depend on the other's (down_revision), or (2) use alembic merge to create a merge migration that depends on both heads. Code/Syntax Justification:

# Detect multiple heads
alembic heads
# abc123 (head)
# def456 (head) ← conflict!

# Option 1: Merge heads
alembic merge -m "merge heads" abc123 def456
# Creates a merge migration with two down_revisions

# Option 2: Manually rebase
# Edit def456's migration file:
# down_revision = 'abc123' # Instead of the original parent

# Verify single head
alembic heads
# ghi789 (head) ← single head, conflict resolved

🔍 Behind the Question: Team collaboration on database schema changes. ❌ The Red Flags: Multiple developers creating migrations without communicating — CI pipeline has conflicting heads.


Q25: How do you test Alembic migrations before running in production?​

Technical Answer: Use a test pipeline: (1) alembic upgrade head on a fresh database to test full migration history. (2) alembic downgrade -1 && alembic upgrade head to test rollback + re-apply. (3) Run against a production clone with real data volumes. (4) Add migration tests to CI. Code/Syntax Justification:

# pytest fixture for migration testing
import pytest
from alembic.config import Config
from alembic.command import upgrade, downgrade

@pytest.fixture
def alembic_config():
config = Config("alembic.ini")
config.set_main_option("sqlalchemy.url", TEST_DB_URL)
return config

def test_full_migration_upgrade(alembic_config):
"""Test that all migrations apply cleanly to an empty database."""
upgrade(alembic_config, "head")

def test_migration_rollback(alembic_config):
"""Test that every migration can be rolled back."""
upgrade(alembic_config, "head")
downgrade(alembic_config, "base")
upgrade(alembic_config, "head")

def test_latest_migration_idempotent(alembic_config):
"""Test the latest migration against a pre-existing schema."""
upgrade(alembic_config, "head") # Should succeed even if already at head

🔍 Behind the Question: Migration safety and CI/CD integration. ❌ The Red Flags: Running untested migrations directly in production — one bad migration can corrupt data irreversibly.


Q26: How do you use Alembic with async SQLAlchemy (SQLAlchemy 2.0)?​

Technical Answer: Alembic's env.py needs modification to use run_async() with an async engine. The migration scripts themselves remain synchronous — Alembic handles the async wrapping. Use connectable = async_engine with run_sync() to bridge sync Alembic operations with an async engine. Code/Syntax Justification:

# alembic/env.py (async version)
import asyncio
from sqlalchemy.ext.asyncio import create_async_engine

def run_migrations_online():
connectable = create_async_engine(DATABASE_URL)

async def run_async_migrations():
async with connectable.connect() as connection:
await connection.run_sync(do_run_migrations)

asyncio.run(run_async_migrations())

def do_run_migrations(connection):
context.configure(
connection=connection,
target_metadata=Base.metadata,
)
with context.begin_transaction():
context.run_migrations()

🔍 Behind the Question: Modern async Python ORM setup. ❌ The Red Flags: Using a sync engine for Alembic alongside an async engine for the app — separate connection pools, potential schema drift.


Q27: How do you handle large table migrations without downtime?​

Technical Answer: For tables with millions of rows: use ALTER TABLE ... ADD COLUMN ... DEFAULT NULL (instant in PostgreSQL 11+). Avoid NOT NULL columns without defaults — requires full table rewrite. For adding indexes, use CREATE INDEX CONCURRENTLY to avoid table locks. Code/Syntax Justification:

def upgrade():
# FAST — instant in PostgreSQL 11+ (metadata-only change)
op.add_column('huge_table', sa.Column('new_col', sa.String(255), nullable=True))

# FAST — concurrent index creation (no lock)
op.create_index(
'ix_huge_table_new_col',
'huge_table',
['new_col'],
postgresql_concurrently=True,
)

# SLOW AND DANGEROUS — rewrites entire table
# op.add_column('huge_table', sa.Column('new_col', sa.String, nullable=False, server_default=''))
# ↑ This locks the table for the entire rewrite duration

def downgrade():
op.drop_index('ix_huge_table_new_col', 'huge_table', postgresql_concurrently=True)
op.drop_column('huge_table', 'new_col')

🔍 Behind the Question: Database operations at scale — what's instant vs what locks the table. ❌ The Red Flags: Adding a NOT NULL column with a default to a 100-million-row table — 30-minute table lock, complete outage.


Q28: How do you implement Alembic migration versioning in a monorepo?​

Technical Answer: Use separate Alembic configurations per service/module with distinct version_locations. Each service has its own migration history, revision chain, and alembic.ini. Shared tables use a shared migration chain that all services depend on. Code/Syntax Justification:

# services/auth/alembic.ini
[alembic]
script_location = services/auth/migrations
version_locations = services/auth/migrations/versions services/shared/migrations/versions

# services/billing/alembic.ini
[alembic]
script_location = services/billing/migrations
version_locations = services/billing/migrations/versions services/shared/migrations/versions
# Run auth migrations
alembic -c services/auth/alembic.ini upgrade head

# Run billing migrations
alembic -c services/billing/alembic.ini upgrade head

🔍 Behind the Question: Monorepo organization and migration management at scale. ❌ The Red Flags: All services sharing one migration chain — deploying service A's migration blocks service B's deployment.


Section 4: Eloquent ORM (Laravel)​

Q29: What are Eloquent accessors, mutators, and casts?​

Technical Answer: Accessors transform attribute values when reading (get{Attribute}Attribute). Mutators transform values when writing (set{Attribute}Attribute). Casts automatically convert attribute types (JSON, datetime, enum, encrypted). In Laravel 10+, use the Attribute class for modern accessor/mutator syntax. Code/Syntax Justification:

class User extends Model
{
protected $casts = [
'preferences' => 'array', // JSON ↔ array
'email_verified_at' => 'datetime', // string ↔ Carbon
'is_admin' => 'boolean',
'role' => RoleEnum::class, // Enum cast (PHP 8.1+)
];

// Modern accessor (Laravel 10+)
protected function fullName(): Attribute
{
return Attribute::make(
get: fn () => "{$this->first_name} {$this->last_name}",
set: fn ($value) => [
'first_name' => explode(' ', $value)[0],
'last_name' => explode(' ', $value)[1] ?? '',
],
);
}
}

🔍 Behind the Question: Data transformation layer within the ORM. ❌ The Red Flags: Manually calling json_decode() everywhere instead of using a 'json' cast.


Q30: How does Eloquent's query scopes feature work?​

Technical Answer: Local scopes add reusable query constraints via scope{Name} methods on the model, chainable like query builder methods. Global scopes apply automatically to every query on the model (e.g., soft deletes). Use local scopes for opt-in filtering, global scopes for security/multi-tenancy. Code/Syntax Justification:

class Order extends Model
{
// Local scope — opt-in
public function scopeCompleted($query)
{
return $query->where('status', 'completed');
}

public function scopeRecent($query, int $days = 7)
{
return $query->where('created_at', '>=', now()->subDays($days));
}

// Global scope — automatic on every query
protected static function booted()
{
static::addGlobalScope('active', function ($query) {
$query->where('is_active', true);
});
}
}

// Usage
$orders = Order::completed()->recent(30)->get();
// SELECT * FROM orders WHERE status='completed' AND created_at >= '...' AND is_active=1

🔍 Behind the Question: Reusable query composition and domain-level query patterns. ❌ The Red Flags: Copy-pasting ->where('status', 'completed') in 50 controllers.


Q31: How do you implement Eloquent model observers?​

Technical Answer: Observers group model event handlers (creating, created, updating, updated, deleting, deleted, saving, saved) into a dedicated class. Register via Model::observe() or auto-discover with $observe in the EventServiceProvider. Code/Syntax Justification:

class OrderObserver
{
public function creating(Order $order): void
{
$order->reference_number = Str::uuid();
}

public function created(Order $order): void
{
NotifyWarehouse::dispatch($order);
AuditLog::record('order_created', $order);
}

public function updating(Order $order): void
{
if ($order->isDirty('status') && $order->status === 'shipped') {
$order->shipped_at = now();
}
}

public function deleted(Order $order): void
{
AuditLog::record('order_deleted', $order);
}
}

// Register in AppServiceProvider
Order::observe(OrderObserver::class);

🔍 Behind the Question: Separation of cross-cutting concerns from business logic. ❌ The Red Flags: Putting audit logging, notification dispatch, and cache invalidation directly in controllers.


Q32: How do you implement Eloquent polymorphic relationships?​

Technical Answer: Polymorphic relationships allow a model to belong to multiple other models via a single association. Uses {relationship}_type (morph class) and {relationship}_id (morph key) columns. Types: morphTo, morphOne, morphMany, morphToMany. Code/Syntax Justification:

// Comment can belong to Post OR Video
class Comment extends Model
{
public function commentable(): MorphTo
{
return $this->morphTo();
}
}

class Post extends Model
{
public function comments(): MorphMany
{
return $this->morphMany(Comment::class, 'commentable');
}
}

class Video extends Model
{
public function comments(): MorphMany
{
return $this->morphMany(Comment::class, 'commentable');
}
}

// Migration: comments table
// commentable_type (string): 'App\Models\Post' or 'App\Models\Video'
// commentable_id (integer): the related model's ID

$post->comments; // All comments on this post
$video->comments; // All comments on this video
$comment->commentable; // The Post or Video this comment belongs to

🔍 Behind the Question: Flexible relationship modeling. ❌ The Red Flags: Creating separate post_comments and video_comments tables with identical schemas.


Q33: How do you optimize Eloquent queries to avoid N+1?​

Technical Answer: Use with() for eager loading, withCount() for counting related models without loading them, load() for lazy eager loading after the initial query, and preventLazyLoading() in development to catch N+1 issues early. Code/Syntax Justification:

// N+1 detection in development
Model::preventLazyLoading(! app()->isProduction());

// Eager loading
$users = User::with(['orders', 'orders.items'])->get();
// 3 queries: users, orders WHERE user_id IN (...), items WHERE order_id IN (...)

// Selective eager loading (load only specific columns)
$users = User::with('orders:id,user_id,total,status')->get();

// Count without loading
$users = User::withCount('orders')->get();
foreach ($users as $user) {
echo $user->orders_count; // No extra query
}

// Conditional eager loading
$users = User::when($includeOrders, fn($q) => $q->with('orders'))->get();

🔍 Behind the Question: Query performance optimization in Eloquent. ❌ The Red Flags: $user->orders inside a Blade @foreach loop — N+1 queries invisible until page takes 10 seconds.


Q34: How does Elasticsearch indexing work and what is an inverted index?​

Technical Answer: An inverted index maps every unique term to the list of documents containing it. When you index a document, Elasticsearch tokenizes each field (splits text into terms), normalizes them (lowercase, stemming), and adds each term to the inverted index with the document ID and position. Code/Syntax Justification:

Documents:
doc1: "The quick brown fox"
doc2: "The quick blue car"

Inverted Index:
the → [doc1, doc2]
quick → [doc1, doc2]
brown → [doc1]
fox → [doc1]
blue → [doc2]
car → [doc2]

Search "quick fox":
quick → [doc1, doc2]
fox → [doc1]
Intersection/scoring → doc1 scores higher

🔍 Behind the Question: Understanding how full-text search actually works at the data structure level. ❌ The Red Flags: Thinking Elasticsearch uses LIKE '%query%' under the hood.


Q35: How do you design an Elasticsearch mapping for a product catalog?​

Technical Answer: Define explicit mappings with appropriate field types: text (analyzed, for full-text search), keyword (exact match, for filtering/aggregations), integer, date, nested (for arrays of objects). Use multi-fields to index the same field differently. Code/Syntax Justification:

{
"mappings": {
"properties": {
"name": {
"type": "text",
"analyzer": "english",
"fields": {
"raw": { "type": "keyword" },
"autocomplete": {
"type": "text",
"analyzer": "edge_ngram_analyzer"
}
}
},
"category": { "type": "keyword" },
"price": { "type": "float" },
"tags": { "type": "keyword" },
"created_at": { "type": "date" },
"variants": {
"type": "nested",
"properties": {
"size": { "type": "keyword" },
"color": { "type": "keyword" },
"stock": { "type": "integer" }
}
}
}
}
}

🔍 Behind the Question: Search schema design for e-commerce. ❌ The Red Flags: Using dynamic mappings in production — Elasticsearch guesses types, often wrong.


Q36: How do you implement autocomplete search in Elasticsearch?​

Technical Answer: Use edge_ngram tokenizer to index prefixes (e.g., "pro" → "product"). Alternatively, use the completion suggester for dedicated autocomplete with in-memory FST (Finite State Transducer) for sub-millisecond responses. Code/Syntax Justification:

{
"settings": {
"analysis": {
"analyzer": {
"autocomplete_analyzer": {
"tokenizer": "autocomplete_tokenizer",
"filter": ["lowercase"]
}
},
"tokenizer": {
"autocomplete_tokenizer": {
"type": "edge_ngram",
"min_gram": 2,
"max_gram": 15,
"token_chars": ["letter", "digit"]
}
}
}
},
"mappings": {
"properties": {
"name": {
"type": "text",
"analyzer": "autocomplete_analyzer",
"search_analyzer": "standard"
}
}
}
}
# Python query
result = es.search(index="products", body={
"query": {
"match": {
"name": {"query": "lapt", "analyzer": "standard"}
}
}
})
# Matches: "laptop", "laptop case", "laptop stand"

🔍 Behind the Question: Real-time search UX implementation. ❌ The Red Flags: Using match_phrase_prefix for autocomplete — slow, inconsistent, and doesn't scale.


Q37: How do you implement faceted search with Elasticsearch aggregations?​

Technical Answer: Faceted search shows filter counts alongside search results (e.g., "Brand: Nike (42), Adidas (38)"). Use terms aggregations for keyword facets, range aggregations for price ranges, and nested aggregations for nested objects. Post-filter the results while keeping aggregation counts unfiltered. Code/Syntax Justification:

result = es.search(index="products", body={
"query": {"match": {"name": "running shoes"}},
"aggs": {
"brands": {
"terms": {"field": "brand", "size": 20}
},
"price_ranges": {
"range": {
"field": "price",
"ranges": [
{"to": 50, "key": "under_50"},
{"from": 50, "to": 100, "key": "50_to_100"},
{"from": 100, "key": "over_100"},
]
}
},
"sizes": {
"terms": {"field": "sizes", "size": 10}
}
},
"post_filter": {"term": {"brand": "Nike"}} # Filter results, not aggs
})

# Response:
# hits: [Nike running shoes only]
# aggs.brands: {"Nike": 42, "Adidas": 38, "Puma": 15} ← unaffected by post_filter

🔍 Behind the Question: E-commerce search UX — the "filter sidebar" pattern. ❌ The Red Flags: Using filter instead of post_filter — facet counts change when user selects a filter, confusing the UI.


Q38: How do you keep Elasticsearch in sync with your primary database?​

Technical Answer: Three patterns: (1) Application-level sync: write to DB and ES in the same service (simple, risk of inconsistency). (2) CDC (Change Data Capture): use Debezium to stream DB changes to Kafka → ES connector (eventually consistent, reliable). (3) Outbox pattern: write changes to DB outbox table, background job publishes to ES. Code/Syntax Justification:

# Pattern 1: Application-level sync (Django signal)
from django.db.models.signals import post_save, post_delete

@receiver(post_save, sender=Product)
def sync_product_to_es(sender, instance, **kwargs):
es.index(index='products', id=instance.id, body=instance.to_es_document())

@receiver(post_delete, sender=Product)
def remove_product_from_es(sender, instance, **kwargs):
es.delete(index='products', id=instance.id, ignore=[404])

# Pattern 2: Debezium CDC pipeline
# PostgreSQL → Debezium → Kafka → Elasticsearch Sink Connector
# Zero application code changes, eventual consistency ~1-5 seconds

# Pattern 3: Periodic bulk reindex (simplest, highest latency)
# Cron job runs every 5 minutes
Product.objects.filter(updated_at__gte=five_minutes_ago).to_es_bulk()

🔍 Behind the Question: Data consistency between primary database and search index. ❌ The Red Flags: Only indexing on write — if ES is down, data is permanently missing until a full reindex.


Q39: How do you implement relevance tuning in Elasticsearch?​

Technical Answer: Use boost to weight fields differently (name^3 means name matches are 3x more important). Use function_score to incorporate business signals (popularity, recency, revenue). Use should clauses for optional boosts. Test with the _explain API. Code/Syntax Justification:

result = es.search(index="products", body={
"query": {
"function_score": {
"query": {
"multi_match": {
"query": "wireless headphones",
"fields": ["name^3", "description^1", "brand^2"],
"type": "best_fields"
}
},
"functions": [
{"field_value_factor": {"field": "sales_count", "modifier": "log1p", "factor": 2}},
{"exp": {"created_at": {"scale": "30d", "decay": 0.5}}},
{"filter": {"term": {"is_sponsored": True}}, "weight": 5},
],
"score_mode": "sum",
"boost_mode": "multiply"
}
}
})

🔍 Behind the Question: Search quality engineering — relevance is a business metric. ❌ The Red Flags: Default relevance scoring on an e-commerce site — obscure products outranking bestsellers.


Q40: How do you implement search synonyms and typo tolerance in Elasticsearch?​

Technical Answer: Use a synonym token filter for known equivalents ("laptop" = "notebook"). Use fuzziness parameter in match queries for typo tolerance (edit distance). Combine with phonetic analyzer for sound-alike matching. Code/Syntax Justification:

{
"settings": {
"analysis": {
"filter": {
"synonym_filter": {
"type": "synonym",
"synonyms": [
"laptop, notebook, portable computer",
"phone, mobile, cellphone, smartphone"
]
}
},
"analyzer": {
"synonym_analyzer": {
"tokenizer": "standard",
"filter": ["lowercase", "synonym_filter"]
}
}
}
}
}
# Fuzzy search (typo tolerance)
result = es.search(index="products", body={
"query": {
"match": {
"name": {
"query": "labtop", # Typo
"fuzziness": "AUTO" # Matches "laptop" (1 edit distance)
}
}
}
})

🔍 Behind the Question: Search quality improvements for real-world user input. ❌ The Red Flags: No fuzzy matching — searching "iphoen" returns zero results instead of "iPhone."


Section 6: Database Migration Patterns​

Q41: What is the expand-contract migration pattern?​

Technical Answer: The expand-contract pattern splits breaking schema changes into safe phases. Expand: add the new structure alongside the old (both exist). Migrate: copy data from old to new. Contract: remove the old structure. This ensures backward compatibility with running application instances during deployment. Code/Syntax Justification:

Phase 1 (Expand):
ADD COLUMN email_v2 VARCHAR(255);
-- Deploy app v2: writes to BOTH email and email_v2

Phase 2 (Migrate):
UPDATE users SET email_v2 = email WHERE email_v2 IS NULL;
-- Deploy app v3: reads from email_v2 only

Phase 3 (Contract):
ALTER TABLE users DROP COLUMN email;
ALTER TABLE users RENAME COLUMN email_v2 TO email;

🔍 Behind the Question: Zero-downtime schema evolution in production. ❌ The Red Flags: Renaming a column in a single migration while the app is running — instant breakage.


Q42: How do you implement reversible migrations?​

Technical Answer: Every upgrade() must have a corresponding downgrade() that undoes the change. For data migrations, store the original values in a temporary column or backup table. For irreversible changes (dropping a column), warn explicitly in the downgrade that data loss has occurred. Code/Syntax Justification:

def upgrade():
# Schema change
op.add_column('users', sa.Column('status', sa.String(20), server_default='active'))
# Data migration
op.execute("UPDATE users SET status = 'inactive' WHERE last_login < NOW() - INTERVAL '1 year'")

def downgrade():
# Reverse the data change (best effort — some data may be lost)
op.execute("UPDATE users SET status = 'active'")
# Remove the column
op.drop_column('users', 'status')

🔍 Behind the Question: Rollback safety and deployment confidence. ❌ The Red Flags: downgrade() that just says pass — no rollback possible when things go wrong.


Q43: How do you handle enum changes in database migrations?​

Technical Answer: PostgreSQL enums are tricky: you can ADD VALUE but can't remove values from an existing enum type. For removals or renames, create a new enum type, alter the column to use it, then drop the old type. In Alembic, use op.execute() for raw SQL. Code/Syntax Justification:

def upgrade():
# Adding a value — simple (PostgreSQL)
op.execute("ALTER TYPE status_enum ADD VALUE 'archived'")

# Removing a value — complex multi-step
op.execute("ALTER TYPE status_enum RENAME TO status_enum_old")
op.execute("CREATE TYPE status_enum AS ENUM('active', 'inactive')")
op.execute("ALTER TABLE users ALTER COLUMN status TYPE status_enum USING status::text::status_enum")
op.execute("DROP TYPE status_enum_old")

def downgrade():
op.execute("ALTER TYPE status_enum ADD VALUE 'deleted'")

🔍 Behind the Question: PostgreSQL-specific migration challenges. ❌ The Red Flags: Trying to DROP VALUE from a PostgreSQL enum — no such command exists.


Q44: How do you implement migration locking to prevent concurrent migrations?​

Technical Answer: Use advisory locks in PostgreSQL to ensure only one migration process runs at a time. Alembic doesn't provide this natively, but you can wrap it in a custom env.py that acquires an advisory lock before running migrations. Code/Syntax Justification:

# alembic/env.py
from sqlalchemy import text

def run_migrations_online():
with engine.connect() as connection:
# Acquire advisory lock (blocks if another migration is running)
connection.execute(text("SELECT pg_advisory_lock(12345)"))
try:
context.configure(connection=connection, target_metadata=target_metadata)
with context.begin_transaction():
context.run_migrations()
finally:
connection.execute(text("SELECT pg_advisory_unlock(12345)"))

# Alternative: use a migrations lock table
# CREATE TABLE migration_lock (id INT PRIMARY KEY, locked BOOLEAN, locked_at TIMESTAMP)

🔍 Behind the Question: Deployment safety in multi-instance environments. ❌ The Red Flags: Two Kubernetes pods running migrations simultaneously — deadlocks, partial migrations, corrupted schema.


Q45: How do you generate migrations from model changes in Django?​

Technical Answer: Django uses makemigrations to auto-detect model changes by comparing the current model state with the last migration state. It generates migration files with operations like AddField, AlterField, CreateModel. The migration state is a Python representation, not a DB diff. Code/Syntax Justification:

# Generate migration
python manage.py makemigrations myapp

# Show migration SQL without executing
python manage.py sqlmigrate myapp 0005

# Apply migrations
python manage.py migrate

# Show migration status
python manage.py showmigrations

# Squash old migrations (combine multiple into one)
python manage.py squashmigrations myapp 0001 0010
# Generated migration file
class Migration(migrations.Migration):
dependencies = [('myapp', '0004_auto')]
operations = [
migrations.AddField(
model_name='user',
name='phone',
field=models.CharField(max_length=20, blank=True, default=''),
),
]

🔍 Behind the Question: Django ORM migration workflow. ❌ The Red Flags: Editing migration files manually without understanding the dependency chain — breaks the migration graph.


Section 7: Advanced ORM Patterns​

Q46: What is the Repository pattern and how does it improve ORM usage?​

Technical Answer: The Repository pattern abstracts data access behind an interface, separating business logic from ORM queries. It allows: swapping ORMs without changing business logic, easier testing (mock the repository), and consistent query patterns across the codebase. Code/Syntax Justification:

# Abstract repository
from abc import ABC, abstractmethod

class UserRepository(ABC):
@abstractmethod
def get_by_id(self, user_id: int) -> User: ...

@abstractmethod
def find_active(self) -> list[User]: ...

@abstractmethod
def save(self, user: User) -> User: ...

# SQLAlchemy implementation
class SQLAlchemyUserRepository(UserRepository):
def __init__(self, session: Session):
self.session = session

def get_by_id(self, user_id: int) -> User:
return self.session.get(User, user_id)

def find_active(self) -> list[User]:
return self.session.query(User).filter(User.is_active == True).all()

def save(self, user: User) -> User:
self.session.add(user)
self.session.flush()
return user

# Service uses repository — no ORM knowledge
class UserService:
def __init__(self, repo: UserRepository):
self.repo = repo

def deactivate_user(self, user_id: int):
user = self.repo.get_by_id(user_id)
user.is_active = False
self.repo.save(user)

🔍 Behind the Question: Clean architecture and separation of concerns. ❌ The Red Flags: SQLAlchemy queries scattered across 50 service files — impossible to test without a database.


Q47: How do you implement database-level row locking with SQLAlchemy?​

Technical Answer: Use with_for_update() for SELECT ... FOR UPDATE — pessimistic locking that blocks other transactions from reading or modifying the locked rows until the transaction completes. Use nowait=True to fail immediately instead of waiting if the row is already locked. Code/Syntax Justification:

# Pessimistic lock — blocks concurrent access
with session.begin():
account = session.query(Account).filter(
Account.id == account_id
).with_for_update(nowait=True).one()
# Row is locked — no other transaction can modify it

if account.balance >= amount:
account.balance -= amount
session.add(Transaction(account_id=account_id, amount=-amount))
else:
raise InsufficientFundsError()

# Lock modes:
# .with_for_update() # Exclusive lock (default)
# .with_for_update(read=True) # Shared lock (FOR SHARE)
# .with_for_update(nowait=True) # Fail immediately if locked
# .with_for_update(skip_locked=True) # Skip locked rows (queue processing)

🔍 Behind the Question: Concurrent write safety for financial/critical operations. ❌ The Red Flags: No locking on balance deduction — two concurrent requests create a race condition, allowing overdrafts.


Q48: How do you implement database partitioning with an ORM?​

Technical Answer: Partitioning splits a large table into smaller physical tables (partitions) while maintaining a single logical table. PostgreSQL supports RANGE, LIST, and HASH partitioning. Define partitions in migrations; the ORM queries the parent table normally — PostgreSQL routes to the correct partition. Code/Syntax Justification:

# Alembic migration — create partitioned table
def upgrade():
op.execute("""
CREATE TABLE events (
id BIGSERIAL,
event_type VARCHAR(50),
created_at TIMESTAMPTZ NOT NULL,
payload JSONB,
PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);
""")

# Create monthly partitions
for month in range(1, 13):
start = f"2026-{month:02d}-01"
end = f"2026-{month+1:02d}-01" if month < 12 else "2027-01-01"
op.execute(f"""
CREATE TABLE events_2026_{month:02d}
PARTITION OF events
FOR VALUES FROM ('{start}') TO ('{end}');
""")

# SQLAlchemy model — queries work normally
class Event(Base):
__tablename__ = 'events'
id = Column(BigInteger, primary_key=True)
created_at = Column(DateTime(timezone=True), primary_key=True)
event_type = Column(String(50))
payload = Column(JSONB)

# Query — PostgreSQL auto-routes to correct partition
recent = session.query(Event).filter(Event.created_at >= '2026-03-01').all()

🔍 Behind the Question: Scaling tables with billions of rows. ❌ The Red Flags: A 2-billion-row events table without partitioning — queries scan the entire table, indexes are enormous.


Q49: How do you implement database connection health checks in an ORM?​

Technical Answer: Use pool_pre_ping=True in SQLAlchemy to test each connection before use (sends a lightweight SELECT 1 before checkout). For Django, use CONN_HEALTH_CHECKS = True (Django 4.1+). This prevents "connection reset by peer" errors from stale connections after database restarts or network issues. Code/Syntax Justification:

# SQLAlchemy — connection health check
engine = create_engine(
DATABASE_URL,
pool_pre_ping=True, # Check connection health before each use
pool_recycle=3600, # Recycle connections older than 1 hour
pool_size=10,
max_overflow=20,
)

# Django settings
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql',
'CONN_HEALTH_CHECKS': True, # Django 4.1+
'CONN_MAX_AGE': 600, # Keep connections for 10 minutes
'OPTIONS': {
'connect_timeout': 5,
},
}
}

🔍 Behind the Question: Connection pool reliability in production. ❌ The Red Flags: No pool_pre_ping — database restarts cause a wave of "closed connection" errors until all pooled connections are cycled.


Q50: How do you implement read replicas with an ORM?​

Technical Answer: Route read queries to replicas and write queries to the primary. In SQLAlchemy, use Session with bind per query type. In Django, use a database router. Always read from the primary for queries that must see the latest writes (e.g., immediately after a write). Code/Syntax Justification:

# SQLAlchemy — multiple engines
primary_engine = create_engine("postgresql://primary:5432/mydb")
replica_engine = create_engine("postgresql://replica:5432/mydb")

class RoutingSession(Session):
def get_bind(self, mapper=None, clause=None, **kwargs):
if self._flushing or self.info.get('use_primary'):
return primary_engine
return replica_engine

# Usage
session = RoutingSession()
users = session.query(User).all() # → replica
session.info['use_primary'] = True
user = session.query(User).get(1) # → primary (fresh data needed)

# Django database router
class PrimaryReplicaRouter:
def db_for_read(self, model, **hints):
return 'replica'

def db_for_write(self, model, **hints):
return 'default' # primary

def allow_relation(self, obj1, obj2, **hints):
return True

🔍 Behind the Question: Horizontal read scaling architecture. ❌ The Red Flags: Reading from a replica immediately after writing to the primary — user updates their profile, refreshes, and sees the old data (replication lag).


Section 8: Search Architecture​

Q51: How do you implement search-as-you-type with debouncing?​

Technical Answer: Frontend debounces keystrokes (wait 200-300ms after last keystroke before sending request). Backend uses edge_ngram or completion suggester. API endpoint returns top 5-10 suggestions with highlighting. Cache popular queries in Redis with short TTL. Code/Syntax Justification:

# FastAPI endpoint
@app.get("/api/search/suggest")
async def search_suggest(q: str = Query(..., min_length=2)):
cache_key = f"suggest:{q.lower()}"
cached = await redis.get(cache_key)
if cached:
return json.loads(cached)

result = es.search(index="products", body={
"suggest": {
"product_suggest": {
"prefix": q,
"completion": {
"field": "name_suggest",
"size": 5,
"fuzzy": {"fuzziness": "AUTO"}
}
}
}
})
suggestions = [s['text'] for s in result['suggest']['product_suggest'][0]['options']]
await redis.setex(cache_key, 60, json.dumps(suggestions))
return suggestions

🔍 Behind the Question: End-to-end search UX implementation. ❌ The Red Flags: Sending an Elasticsearch query on every keystroke — hammering the cluster with queries for "l", "la", "lap", "lapt", "lapto", "laptop".


Q52: How do you implement multi-language search in Elasticsearch?​

Technical Answer: Use language-specific analyzers (english, french, german) per field. Store translated content in separate fields or use language-specific sub-fields. At query time, boost the user's language field while still searching across all languages. Code/Syntax Justification:

{
"mappings": {
"properties": {
"title_en": { "type": "text", "analyzer": "english" },
"title_fr": { "type": "text", "analyzer": "french" },
"title_de": { "type": "text", "analyzer": "german" }
}
}
}
def search_products(query: str, user_lang: str = "en"):
lang_field = f"title_{user_lang}"
return es.search(index="products", body={
"query": {
"multi_match": {
"query": query,
"fields": [f"{lang_field}^3", "title_en", "title_fr", "title_de"],
"type": "best_fields"
}
}
})

🔍 Behind the Question: Internationalized search architecture. ❌ The Red Flags: Using the standard analyzer for all languages — no stemming, no stop words, poor relevance.


Q53: How do you implement search result highlighting in Elasticsearch?​

Technical Answer: Use the highlight parameter to return HTML-tagged snippets showing where the query matched. Configure pre_tags/post_tags for custom highlighting, and fragment_size to control snippet length. Code/Syntax Justification:

result = es.search(index="articles", body={
"query": {"match": {"body": "machine learning"}},
"highlight": {
"pre_tags": ["<mark>"],
"post_tags": ["</mark>"],
"fields": {
"body": {
"fragment_size": 150,
"number_of_fragments": 3
},
"title": {
"number_of_fragments": 0 # Return full title with highlights
}
}
}
})

for hit in result['hits']['hits']:
title = hit['highlight'].get('title', [hit['_source']['title']])[0]
snippets = hit['highlight'].get('body', [])
# title: "Introduction to <mark>Machine Learning</mark>"
# snippets: ["...uses <mark>machine learning</mark> algorithms to..."]

🔍 Behind the Question: Search result presentation and UX. ❌ The Red Flags: Displaying raw document text without highlighting — user can't see why a result matched their query.


Q54: How do you implement geo-spatial search in Elasticsearch?​

Technical Answer: Use geo_point field type for lat/lon coordinates. Query with geo_distance (radius), geo_bounding_box (rectangle), or geo_polygon. Sort by distance using geo_distance sort. Combine with text search for "restaurants near me" queries. Code/Syntax Justification:

{
"mappings": {
"properties": {
"name": { "type": "text" },
"location": { "type": "geo_point" }
}
}
}
# Find restaurants within 5km
result = es.search(index="restaurants", body={
"query": {
"bool": {
"must": {"match": {"cuisine": "italian"}},
"filter": {
"geo_distance": {
"distance": "5km",
"location": {"lat": 40.7128, "lon": -74.0060}
}
}
}
},
"sort": [
{"_geo_distance": {"location": {"lat": 40.7128, "lon": -74.0060}, "order": "asc"}}
]
})

🔍 Behind the Question: Location-based search implementation. ❌ The Red Flags: Calculating distances in Python by loading all documents — O(n) scan instead of indexed geo query.


Q55: How do you handle Elasticsearch index lifecycle management (ILM)?​

Technical Answer: ILM automates index transitions through phases: hot (actively written/searched), warm (read-only, less frequent searches), cold (infrequent access, compressed), frozen (rarely accessed, searchable snapshots), delete (removed). Attach ILM policies to index templates for automatic management. Code/Syntax Justification:

{
"policy": {
"phases": {
"hot": {
"actions": {
"rollover": { "max_size": "50gb", "max_age": "7d" },
"set_priority": { "priority": 100 }
}
},
"warm": {
"min_age": "30d",
"actions": {
"shrink": { "number_of_shards": 1 },
"forcemerge": { "max_num_segments": 1 },
"set_priority": { "priority": 50 }
}
},
"cold": {
"min_age": "90d",
"actions": {
"freeze": {},
"set_priority": { "priority": 0 }
}
},
"delete": {
"min_age": "365d",
"actions": { "delete": {} }
}
}
}
}

🔍 Behind the Question: Operational maturity and cost management for search infrastructure. ❌ The Red Flags: All log indices on hot storage forever — storage costs grow linearly without bound.


Section 9: Migration Testing & CI/CD​

Q56: How do you implement a CI pipeline for database migrations?​

Technical Answer: Run migrations against a fresh database in CI to verify the full migration chain. Test both upgrade and downgrade paths. Use a Docker PostgreSQL container for fast, disposable test databases. Fail the build on migration conflicts. Code/Syntax Justification:

# .github/workflows/migrations.yml
name: Test Migrations
on: [pull_request]

jobs:
test-migrations:
runs-on: ubuntu-latest
services:
postgres:
image: postgres:16
env:
POSTGRES_DB: test_db
POSTGRES_PASSWORD: test
ports: ["5432:5432"]

steps:
- uses: actions/checkout@v4
- name: Test full migration chain
run: |
alembic upgrade head
alembic downgrade base
alembic upgrade head

- name: Check for multiple heads
run: |
HEADS=$(alembic heads | wc -l)
if [ "$HEADS" -gt 1 ]; then
echo "ERROR: Multiple migration heads detected!"
alembic heads
exit 1
fi

🔍 Behind the Question: Migration safety automation. ❌ The Red Flags: No CI testing of migrations — discovering a broken migration during production deployment at 2 AM.


Q57: How do you implement database seeding for development and testing?​

Technical Answer: Create seed scripts that populate the database with realistic test data. Use factories (Factory Boy for Python, Faker for realistic data). Seed data should be idempotent — running twice should not create duplicates. Separate seed data from migration data. Code/Syntax Justification:

# Factory Boy + Faker for realistic seed data
import factory
from faker import Faker

fake = Faker()

class UserFactory(factory.alchemy.SQLAlchemyModelFactory):
class Meta:
model = User
sqlalchemy_session = Session

name = factory.LazyFunction(fake.name)
email = factory.LazyFunction(fake.email)
created_at = factory.LazyFunction(fake.date_time_this_year)

class OrderFactory(factory.alchemy.SQLAlchemyModelFactory):
class Meta:
model = Order
sqlalchemy_session = Session

user = factory.SubFactory(UserFactory)
total = factory.LazyFunction(lambda: round(random.uniform(10, 500), 2))
status = factory.Iterator(['pending', 'shipped', 'delivered'])

# Seed script
def seed_database():
users = UserFactory.create_batch(100)
for user in users:
OrderFactory.create_batch(random.randint(1, 10), user=user)
Session.commit()

🔍 Behind the Question: Development environment setup and test data management. ❌ The Red Flags: Using production data dumps for local development — GDPR/PII violations, huge file sizes.


Q58: How do you squash Alembic migrations?​

Technical Answer: Squashing combines many small migrations into a single migration that represents the final schema state. Create a new migration with op.create_all() based on current models, delete old migration files, and update the revision chain. Always verify squashed migrations against the full chain. Code/Syntax Justification:

# Django: built-in squash
python manage.py squashmigrations myapp 0001 0050

# Alembic: manual squash process
# 1. Record current schema
alembic upgrade head
pg_dump --schema-only mydb > schema_snapshot.sql

# 2. Create new base migration
alembic revision -m "squashed_base" --head base

# 3. In the new migration, create everything from scratch
def upgrade():
# Full schema creation based on current models
Base.metadata.create_all(op.get_bind())

# 4. Delete old migration files (keep squashed_base only)
# 5. Stamp existing databases to skip the squashed migration
alembic stamp squashed_base_revision_id

🔍 Behind the Question: Migration chain maintenance and CI speed optimization. ❌ The Red Flags: 500+ migration files taking 5 minutes to run in CI — squash to a single base migration.


Q59: How do you handle schema changes that require application coordination?​

Technical Answer: Use feature flags to coordinate schema changes with application code. Deploy in phases: (1) Add feature flag (OFF). (2) Deploy new schema (backward-compatible). (3) Deploy new code behind feature flag. (4) Turn on feature flag. (5) Remove old schema in next release. Code/Syntax Justification:

# Phase 1: Migration adds new column (nullable, backward-compatible)
def upgrade():
op.add_column('users', sa.Column('email_verified', sa.Boolean, nullable=True))

# Phase 2: Application code uses feature flag
from myapp.flags import is_enabled

def get_user_status(user):
if is_enabled('use_email_verified_column'):
return 'verified' if user.email_verified else 'unverified'
else:
return legacy_email_check(user) # Old code path

# Phase 3: Backfill data
def upgrade():
op.execute("UPDATE users SET email_verified = true WHERE email_confirmed_at IS NOT NULL")

# Phase 4: Turn on flag, verify in production
# Phase 5: Remove old column and feature flag code

🔍 Behind the Question: Safe schema evolution in production systems. ❌ The Red Flags: Deploying a schema change and code change simultaneously — if either fails, both are in an inconsistent state.


Q60: How do you implement database schema documentation?​

Technical Answer: Generate schema documentation automatically from ORM models and migration history. Use tools like eralchemy (ERD from SQLAlchemy), dbdocs (web-based DB documentation), or schemaspy (detailed HTML reports). Store generated docs in CI artifacts. Code/Syntax Justification:

# Generate ERD from SQLAlchemy models
pip install eralchemy2
eralchemy2 -i "postgresql://localhost/mydb" -o schema_erd.png

# Generate HTML documentation with SchemaSpy
docker run -v "$PWD:/output" schemaspy/schemaspy:latest \
-t pgsql \
-host localhost \
-db mydb \
-u postgres \
-p password \
-o /output/docs

# Or annotate models directly
class User(Base):
"""
Core user model.
Stores authentication credentials, profile data, and tenant association.
"""
__tablename__ = 'users'
__table_args__ = {'comment': 'Core user accounts with authentication data'}

id = Column(Integer, primary_key=True, comment='Auto-incrementing primary key')
email = Column(String(255), unique=True, comment='Unique email for authentication')
tenant_id = Column(UUID, ForeignKey('tenants.id'), comment='Owning tenant for multi-tenancy')

🔍 Behind the Question: Database knowledge management and onboarding. ❌ The Red Flags: No schema documentation — new developers spend 2 days understanding the 200-table database by reading raw migrations.


Section 10: SQLAlchemy 2.0 Modern Patterns​

Q61: What are the key differences between SQLAlchemy 1.x and 2.0?​

Technical Answer: SQLAlchemy 2.0 introduces: select() replaces session.query(), Mapped[] type annotations replace Column(), native async support with AsyncSession, DeclarativeBase replaces declarative_base(), and with_loader_criteria() for relationship-level filters. The 1.x query API is deprecated but still works. Code/Syntax Justification:

# SQLAlchemy 1.x style (deprecated)
users = session.query(User).filter(User.name == 'Alice').all()

# SQLAlchemy 2.0 style
from sqlalchemy import select
stmt = select(User).where(User.name == 'Alice')
users = session.scalars(stmt).all()

# 2.0 model definition with Mapped
from sqlalchemy.orm import Mapped, mapped_column, DeclarativeBase

class Base(DeclarativeBase):
pass

class User(Base):
__tablename__ = 'users'
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(100))
email: Mapped[str | None] = mapped_column(String(255)) # nullable

🔍 Behind the Question: Modern SQLAlchemy fluency and migration awareness. ❌ The Red Flags: Using session.query() in new code — deprecated, no type checking, no async support.


Q62: How do you use SQLAlchemy's async API with FastAPI?​

Technical Answer: Use create_async_engine with asyncpg or aiosqlite, AsyncSession for async database operations, and async_scoped_session for request-scoped sessions. All query operations must use await with the 2.0 select() API. Code/Syntax Justification:

from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession, async_sessionmaker
from sqlalchemy import select

engine = create_async_engine("postgresql+asyncpg://user:pass@localhost/db", pool_size=10)
async_session = async_sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

# FastAPI dependency
async def get_db():
async with async_session() as session:
async with session.begin():
yield session

# Usage in endpoint
@app.get("/users/{user_id}")
async def get_user(user_id: int, db: AsyncSession = Depends(get_db)):
stmt = select(User).where(User.id == user_id).options(selectinload(User.orders))
result = await db.execute(stmt)
user = result.scalar_one_or_none()
if not user:
raise HTTPException(404)
return user

🔍 Behind the Question: Async ORM integration with modern Python frameworks. ❌ The Red Flags: Using sync SQLAlchemy inside async FastAPI endpoints — blocks the event loop, kills concurrency.


Q63: How do you implement composite indexes and partial indexes in SQLAlchemy?​

Technical Answer: Composite indexes cover multiple columns for queries that filter/sort on combinations. Partial indexes (PostgreSQL) index only rows matching a condition, reducing index size. Define in __table_args__ or via Index(). Code/Syntax Justification:

from sqlalchemy import Index

class Order(Base):
__tablename__ = 'orders'
id: Mapped[int] = mapped_column(primary_key=True)
user_id: Mapped[int] = mapped_column(ForeignKey('users.id'))
status: Mapped[str] = mapped_column(String(20))
created_at: Mapped[datetime] = mapped_column(DateTime)

__table_args__ = (
# Composite index for user + status queries
Index('ix_orders_user_status', 'user_id', 'status'),

# Partial index — only index active orders (smaller, faster)
Index('ix_orders_active', 'user_id', 'created_at',
postgresql_where=text("status != 'cancelled'")),

# Covering index — includes extra columns to avoid table lookup
Index('ix_orders_covering', 'user_id', 'status',
postgresql_include=['created_at', 'total']),
)

🔍 Behind the Question: Advanced indexing strategies for query optimization. ❌ The Red Flags: Individual single-column indexes on user_id and status separately — the DB can't efficiently use both for a WHERE user_id=1 AND status='active' query.


Q64: How do you implement server-side defaults vs client-side defaults in SQLAlchemy?​

Technical Answer: server_default generates a SQL DEFAULT in the DDL — the database sets the value. default is a Python-side default — SQLAlchemy sets it before INSERT. Server defaults are visible in psql \d and work with raw SQL inserts; client defaults only work through the ORM. Code/Syntax Justification:

from sqlalchemy import func, text

class AuditLog(Base):
__tablename__ = 'audit_logs'
id: Mapped[int] = mapped_column(primary_key=True)

# Server-side default — DB generates this
created_at: Mapped[datetime] = mapped_column(
server_default=func.now() # DEFAULT NOW() in SQL
)
uuid: Mapped[str] = mapped_column(
server_default=text("gen_random_uuid()") # PostgreSQL UUID
)

# Client-side default — Python generates this
request_id: Mapped[str] = mapped_column(
default=lambda: str(uuid4()) # Generated in Python before INSERT
)

# onupdate — Python sets this on every UPDATE
updated_at: Mapped[datetime | None] = mapped_column(
onupdate=func.now() # Set by ORM on update, NOT by DB
)

🔍 Behind the Question: Understanding where default values are generated. ❌ The Red Flags: Using Python default=datetime.utcnow() (without parentheses as callable) — the same timestamp is used for every row because it's evaluated once at import time.


Q65: How do you implement custom SQLAlchemy types?​

Technical Answer: Create custom types by subclassing TypeDecorator for transforming values between Python and SQL, or UserDefinedType for entirely new database types. Use for: encrypted fields, JSON with validation, custom enums, or currency types. Code/Syntax Justification:

from sqlalchemy.types import TypeDecorator, String
from cryptography.fernet import Fernet

class EncryptedString(TypeDecorator):
impl = String
cache_ok = True

def __init__(self, key: bytes, *args, **kwargs):
super().__init__(*args, **kwargs)
self.fernet = Fernet(key)

def process_bind_param(self, value, dialect):
if value is not None:
return self.fernet.encrypt(value.encode()).decode()
return value

def process_result_value(self, value, dialect):
if value is not None:
return self.fernet.decrypt(value.encode()).decode()
return value

class User(Base):
__tablename__ = 'users'
id: Mapped[int] = mapped_column(primary_key=True)
ssn: Mapped[str] = mapped_column(EncryptedString(key=ENCRYPTION_KEY, length=500))
# Stored encrypted in DB, decrypted transparently on read

🔍 Behind the Question: Extending the ORM for domain-specific data handling. ❌ The Red Flags: Calling encrypt()/decrypt() manually in every query — error-prone, easy to forget.


Q66: How do you implement SQLAlchemy association proxy for many-to-many relationships?​

Technical Answer: association_proxy simplifies access to attributes across an association table. Instead of navigating user.user_roles[0].role.name, you access user.role_names directly. It can also create associated objects transparently. Code/Syntax Justification:

from sqlalchemy.ext.associationproxy import association_proxy

class User(Base):
__tablename__ = 'users'
id: Mapped[int] = mapped_column(primary_key=True)
user_roles: Mapped[list['UserRole']] = relationship(back_populates='user')

# Proxy: user.role_names returns list of role name strings
role_names = association_proxy('user_roles', 'role_name')

class UserRole(Base):
__tablename__ = 'user_roles'
user_id: Mapped[int] = mapped_column(ForeignKey('users.id'), primary_key=True)
role_id: Mapped[int] = mapped_column(ForeignKey('roles.id'), primary_key=True)
user: Mapped['User'] = relationship(back_populates='user_roles')
role: Mapped['Role'] = relationship()

@property
def role_name(self):
return self.role.name

# Usage
user = session.get(User, 1)
print(user.role_names) # ['admin', 'editor'] — no traversal needed

🔍 Behind the Question: ORM relationship simplification. ❌ The Red Flags: Writing [ur.role.name for ur in user.user_roles] everywhere — repetitive and triggers N+1 queries.


Q67: How do you implement write-through and write-behind caching with an ORM?​

Technical Answer: Write-through: write to cache AND database in the same operation. Write-behind: write to cache immediately, flush to database asynchronously. Implement with SQLAlchemy events (after_insert, after_update) to update Redis on every DB write. Code/Syntax Justification:

import redis
from sqlalchemy import event

r = redis.Redis()

class Product(Base):
__tablename__ = 'products'
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(255))
price: Mapped[float] = mapped_column(Numeric(10, 2))

# Write-through: update cache on every DB write
@event.listens_for(Product, 'after_insert')
@event.listens_for(Product, 'after_update')
def cache_product(mapper, connection, target):
r.setex(f"product:{target.id}", 3600, json.dumps({
'id': target.id, 'name': target.name, 'price': float(target.price)
}))

@event.listens_for(Product, 'after_delete')
def invalidate_product_cache(mapper, connection, target):
r.delete(f"product:{target.id}")

# Read with cache
def get_product(product_id: int) -> dict:
cached = r.get(f"product:{product_id}")
if cached:
return json.loads(cached)
product = session.get(Product, product_id)
return product.__dict__ if product else None

🔍 Behind the Question: Caching strategy integration with the ORM layer. ❌ The Red Flags: Manual cache invalidation in 50 different service methods — missing one means stale data for hours.


Q68: How do you implement database sharding with SQLAlchemy?​

Technical Answer: Sharding distributes data across multiple database instances by a shard key (e.g., tenant_id). SQLAlchemy's ShardedSession routes queries to the correct shard. Define a shard_chooser function that maps entities to shard identifiers based on the shard key. Code/Syntax Justification:

from sqlalchemy.ext.horizontal_shard import ShardedSession

engines = {
'shard_0': create_engine('postgresql://shard0/db'),
'shard_1': create_engine('postgresql://shard1/db'),
'shard_2': create_engine('postgresql://shard2/db'),
}

def shard_chooser(mapper, instance, clause=None):
if instance is not None:
return f"shard_{instance.tenant_id % 3}"
return None

def id_chooser(query, ident):
return ['shard_0', 'shard_1', 'shard_2'] # Query all shards

def execute_chooser(context):
return ['shard_0', 'shard_1', 'shard_2']

Session = sessionmaker(
class_=ShardedSession,
shards=engines,
shard_chooser=shard_chooser,
id_chooser=id_chooser,
execute_chooser=execute_chooser,
)

🔍 Behind the Question: Horizontal scaling strategy for databases. ❌ The Red Flags: Scaling vertically to the largest possible DB instance instead of sharding — single point of failure, hardware ceiling.


Q69: How do you implement query result caching in SQLAlchemy?​

Technical Answer: Use dogpile.cache (by the SQLAlchemy author) for query-level caching with configurable backends (Redis, Memcached, in-memory). Cache query results by query hash with TTL. Invalidate on writes to the cached entities. Code/Syntax Justification:

from dogpile.cache import make_region

cache_region = make_region().configure(
'dogpile.cache.redis',
arguments={'host': 'localhost', 'port': 6379, 'db': 0},
expiration_time=300,
)

@cache_region.cache_on_arguments()
def get_active_users(limit: int = 100):
stmt = select(User).where(User.is_active == True).limit(limit)
return session.scalars(stmt).all()

# Invalidate when user is updated
def update_user(user_id: int, data: dict):
user = session.get(User, user_id)
for key, value in data.items():
setattr(user, key, value)
session.commit()
get_active_users.invalidate(100) # Clear cache

🔍 Behind the Question: Query performance optimization via caching. ❌ The Red Flags: Executing the same expensive query 1000 times per minute without caching — unnecessary database load.


Q70: How do you implement SQLAlchemy model serialization for API responses?​

Technical Answer: Use Pydantic models (model_validate with from_attributes=True), marshmallow schemas, or custom to_dict() methods. Never return ORM objects directly — they contain internal state, lazy-load proxies, and can trigger unexpected queries during serialization. Code/Syntax Justification:

from pydantic import BaseModel, ConfigDict

# Pydantic schema for API response
class UserResponse(BaseModel):
model_config = ConfigDict(from_attributes=True)
id: int
name: str
email: str
created_at: datetime

class UserWithOrders(UserResponse):
orders: list['OrderResponse'] = []

# FastAPI endpoint
@app.get("/users/{user_id}", response_model=UserWithOrders)
async def get_user(user_id: int, db: AsyncSession = Depends(get_db)):
stmt = select(User).where(User.id == user_id).options(selectinload(User.orders))
user = (await db.execute(stmt)).scalar_one_or_none()
if not user:
raise HTTPException(404)
return user # Pydantic automatically converts ORM object

🔍 Behind the Question: API layer separation from persistence layer. ❌ The Red Flags: return jsonify(user.__dict__) — exposes _sa_instance_state, password hashes, and internal ORM metadata.


Section 11: Advanced Eloquent Patterns​

Q71: How do you implement Eloquent model factories for testing?​

Technical Answer: Factories generate fake model instances for testing. Laravel 11 uses class-based factories with definition(), states() for variations, and afterCreating() for related models. Use make() for in-memory instances, create() for persisted instances. Code/Syntax Justification:

class UserFactory extends Factory
{
protected $model = User::class;

public function definition(): array
{
return [
'name' => fake()->name(),
'email' => fake()->unique()->safeEmail(),
'password' => Hash::make('password'),
'email_verified_at' => now(),
];
}

public function unverified(): static
{
return $this->state(fn () => ['email_verified_at' => null]);
}

public function admin(): static
{
return $this->state(fn () => ['role' => 'admin']);
}

public function withOrders(int $count = 3): static
{
return $this->afterCreating(fn (User $user) =>
Order::factory()->count($count)->for($user)->create()
);
}
}

// Usage in tests
$user = User::factory()->admin()->withOrders(5)->create();
$users = User::factory()->unverified()->count(10)->make(); // Not persisted

🔍 Behind the Question: Testing data generation patterns. ❌ The Red Flags: Hardcoding test data in every test method — brittle, repetitive, not randomized.


Q72: How do you implement Eloquent custom casts?​

Technical Answer: Custom casts transform attribute values bidirectionally between the database and PHP. Implement CastsAttributes for simple transforms or CastsInboundAttributes for write-only transforms (e.g., hashing). Custom casts replace manual accessor/mutator boilerplate. Code/Syntax Justification:

use Illuminate\Contracts\Database\Eloquent\CastsAttributes;

class MoneyCast implements CastsAttributes
{
public function get($model, string $key, $value, array $attributes): Money
{
return new Money(
amount: $attributes[$key],
currency: $attributes["{$key}_currency"] ?? 'USD'
);
}

public function set($model, string $key, $value, array $attributes): array
{
if (!$value instanceof Money) {
throw new InvalidArgumentException('Value must be Money instance');
}
return [
$key => $value->amount,
"{$key}_currency" => $value->currency,
];
}
}

class Order extends Model
{
protected $casts = [
'total' => MoneyCast::class,
];
}

// Usage
$order->total = new Money(9999, 'USD');
echo $order->total->formatted(); // "$99.99"

🔍 Behind the Question: Domain object integration with the ORM. ❌ The Red Flags: Storing money as float — floating point rounding errors cause billing discrepancies.


Q73: How do you implement Eloquent query builder macros?​

Technical Answer: Macros extend the query builder with custom methods available on all models. Register in a service provider. Use for cross-model utility methods like date range filtering, search, or pagination patterns. Code/Syntax Justification:

// AppServiceProvider::boot()
use Illuminate\Database\Eloquent\Builder;

Builder::macro('whereDateBetween', function (string $column, $start, $end) {
return $this->whereBetween($column, [$start, $end]);
});

Builder::macro('search', function (string $column, ?string $query) {
if (!$query) return $this;
return $this->where($column, 'ILIKE', "%{$query}%");
});

Builder::macro('toCsv', function () {
$results = $this->get();
$csv = implode(',', array_keys($results->first()->toArray())) . "\n";
foreach ($results as $row) {
$csv .= implode(',', $row->toArray()) . "\n";
}
return $csv;
});

// Usage — available on ALL models
Order::whereDateBetween('created_at', '2026-01-01', '2026-12-31')
->search('customer_name', $request->q)
->toCsv();

🔍 Behind the Question: Framework extensibility patterns. ❌ The Red Flags: Duplicating the same whereBetween pattern in every repository class.


Q74: How do you implement Eloquent model pruning?​

Technical Answer: Laravel's Prunable and MassPrunable traits automatically delete old records on a schedule. Define prunable() to specify which records to remove. Run via php artisan model:prune in a scheduled command. Code/Syntax Justification:

use Illuminate\Database\Eloquent\Prunable;

class AuditLog extends Model
{
use Prunable;

public function prunable(): Builder
{
return static::where('created_at', '<', now()->subMonths(6));
}

protected function pruning(): void
{
// Cleanup before deletion (e.g., delete related files)
Storage::delete($this->file_path);
}
}

// MassPrunable for high-volume tables (uses DELETE WHERE, not per-row)
class TempToken extends Model
{
use MassPrunable;

public function prunable(): Builder
{
return static::where('expires_at', '<', now());
}
}

// Schedule: app/Console/Kernel.php
$schedule->command('model:prune')->daily();
$schedule->command('model:prune', ['--model' => TempToken::class])->hourly();

🔍 Behind the Question: Data lifecycle management and storage optimization. ❌ The Red Flags: Tables growing unboundedly — 500 million audit log rows from 3 years ago that nobody queries.


Q75: How do you implement Eloquent lazy collections for memory-efficient processing?​

Technical Answer: lazy() and cursor() return LazyCollection — records are hydrated one at a time from a database cursor, never loading the full result set into memory. Essential for processing millions of rows in batch jobs. Code/Syntax Justification:

// BAD: Loads all 1M users into memory at once
$users = User::all(); // 1M User objects in memory = OOM

// GOOD: Cursor — one row at a time from DB cursor
User::cursor()->each(function (User $user) {
$user->sendNewsletter();
}); // Memory: ~1 User at a time

// GOOD: Lazy + chunk — process in batches without full memory load
User::lazy(1000)->each(function (User $user) {
$user->recalculateStats();
}); // Loads 1000 at a time, processes, then loads next 1000

// With filtering
User::where('active', true)
->cursor()
->filter(fn ($user) => $user->needsUpdate())
->each(fn ($user) => $user->update(['last_check' => now()]));

🔍 Behind the Question: Memory management for large dataset processing. ❌ The Red Flags: User::all() on a table with 10 million rows — process killed by OOM.


Q76: How do you implement Eloquent API resources for complex transformations?​

Technical Answer: API Resources (JsonResource) transform Eloquent models into JSON responses with field selection, conditional attributes, relationship inclusion, pagination metadata, and nested resources. Use ResourceCollection for paginated lists. Code/Syntax Justification:

class UserResource extends JsonResource
{
public function toArray($request): array
{
return [
'id' => $this->id,
'name' => $this->name,
'email' => $this->when($request->user()?->isAdmin(), $this->email),
'avatar_url' => $this->avatar_url ?? '/default-avatar.png',
'orders_count' => $this->whenCounted('orders'),
'orders' => OrderResource::collection($this->whenLoaded('orders')),
'created_at' => $this->created_at->toISOString(),
'links' => [
'self' => route('users.show', $this->id),
'orders' => route('users.orders', $this->id),
],
];
}
}

// Controller
return UserResource::collection(
User::withCount('orders')
->with('orders')
->paginate(20)
);

🔍 Behind the Question: API response design and data transformation. ❌ The Red Flags: Returning $user->toArray() directly — exposes password hash, remember_token, and internal fields.


Section 12: Advanced Migration Strategies​

Q77: How do you implement blue-green database migrations?​

Technical Answer: Blue-green migrations run two schemas simultaneously. Blue (current) handles live traffic. Green (new) runs migrations. A migration script copies/transforms data from blue to green. Switch traffic from blue to green. Keep blue as rollback for N hours. Drop blue after verification. Code/Syntax Justification:

# Step 1: Create green schema
psql -c "CREATE SCHEMA green;"
psql -c "SET search_path TO green;"
alembic upgrade head # Apply all migrations to green schema

# Step 2: Copy data from blue to green
pg_dump --schema=blue mydb | sed 's/blue\./green\./g' | psql mydb

# Step 3: Apply data transformations
psql -c "UPDATE green.users SET email_verified = true WHERE green.users.email_confirmed_at IS NOT NULL;"

# Step 4: Switch application to green
# UPDATE app config: search_path = green

# Step 5: Keep blue for rollback (24-48 hours)
# Step 6: Drop blue
psql -c "DROP SCHEMA blue CASCADE;"

🔍 Behind the Question: Zero-downtime migration strategies at scale. ❌ The Red Flags: Running ALTER TABLE on a 500M row table during peak traffic — locks table, 30-minute outage.


Q78: How do you implement ghost table migrations (pt-online-schema-change)?​

Technical Answer: Ghost migrations create a new (shadow) table with the desired schema, copy data in small batches using triggers to capture concurrent changes, then atomically swap the tables. Tools: pt-online-schema-change (Percona), gh-ost (GitHub), pg_repack (PostgreSQL). Code/Syntax Justification:

# pt-online-schema-change (MySQL/Percona)
pt-online-schema-change \
--alter "ADD COLUMN phone VARCHAR(20)" \
--execute \
--chunk-size=1000 \
--max-lag=1s \
D=mydb,t=users

# gh-ost (GitHub's online schema migration)
gh-ost \
--alter="ADD COLUMN phone VARCHAR(20)" \
--database=mydb \
--table=users \
--execute \
--chunk-size=1000 \
--max-load=Threads_running=25

# PostgreSQL: pg_repack (online table rebuild)
pg_repack --table users --no-kill-backend mydb

🔍 Behind the Question: Large table migration without downtime. ❌ The Red Flags: ALTER TABLE users ADD COLUMN ... on a 1-billion-row MySQL table — locks the table for hours.


Q79: How do you implement forward-only migrations?​

Technical Answer: Forward-only migrations never run downgrade(). Instead of rolling back schema changes, you deploy a NEW forward migration that reverses the change. This avoids the complexity and risk of maintaining bidirectional migrations, especially for data migrations that are inherently irreversible. Code/Syntax Justification:

# Migration 001: Add column
def upgrade():
op.add_column('users', sa.Column('phone', sa.String(20)))

def downgrade():
raise NotImplementedError("Forward-only migration. Deploy a new migration to reverse.")

# If we need to undo:
# Migration 002: Remove column (forward migration that reverses 001)
def upgrade():
op.drop_column('users', 'phone')

def downgrade():
raise NotImplementedError("Forward-only migration.")

# Benefits:
# - No ambiguity about what "rollback" means for data migrations
# - Simpler CI (only test upgrade path)
# - Matches real-world deployment: you don't rollback, you fix forward

🔍 Behind the Question: Migration philosophy and deployment strategy. ❌ The Red Flags: A downgrade() that drops a column containing 2 years of customer data — irreversible data loss masquerading as a "rollback."


Q80: How do you implement multi-database migrations in Django?​

Technical Answer: Django supports multiple databases with DATABASE_ROUTERS. Migrations run per-database using --database flag. Use allow_migrate() in routers to control which models migrate to which database. Useful for separating read replicas, sharding, or legacy database integration. Code/Syntax Justification:

# settings.py
DATABASES = {
'default': {'ENGINE': 'django.db.backends.postgresql', 'NAME': 'primary'},
'analytics': {'ENGINE': 'django.db.backends.postgresql', 'NAME': 'analytics'},
'legacy': {'ENGINE': 'django.db.backends.mysql', 'NAME': 'legacy_app'},
}

class AnalyticsRouter:
route_app_labels = {'analytics'}

def db_for_read(self, model, **hints):
if model._meta.app_label in self.route_app_labels:
return 'analytics'
return None

def db_for_write(self, model, **hints):
if model._meta.app_label in self.route_app_labels:
return 'analytics'
return None

def allow_migrate(self, db, app_label, model_name=None, **hints):
if app_label in self.route_app_labels:
return db == 'analytics'
return None

# Run migrations per database
# python manage.py migrate --database=analytics
# python manage.py migrate --database=legacy

🔍 Behind the Question: Multi-database architecture in Django. ❌ The Red Flags: Running all migrations against all databases — analytics tables created in the primary DB, primary tables created in analytics.


Section 13: Elasticsearch Advanced Operations​

Q81: How do you implement zero-downtime Elasticsearch index reindexing?​

Technical Answer: Use index aliases: the application always queries the alias (e.g., products), not the physical index (products_v1). To reindex: create products_v2 with new mappings, reindex data from products_v1 to products_v2, then atomically swap the alias from v1 to v2. Code/Syntax Justification:

# Step 1: Create new index with updated mappings
es.indices.create(index='products_v2', body=new_mappings)

# Step 2: Reindex data
es.reindex(body={
'source': {'index': 'products_v1'},
'dest': {'index': 'products_v2'},
})

# Step 3: Atomic alias swap (zero downtime)
es.indices.update_aliases(body={
'actions': [
{'remove': {'index': 'products_v1', 'alias': 'products'}},
{'add': {'index': 'products_v2', 'alias': 'products'}},
]
})

# Step 4: Verify and delete old index
es.indices.delete(index='products_v1')

# Application always queries 'products' alias — no code change needed

🔍 Behind the Question: Production Elasticsearch operations. ❌ The Red Flags: Deleting and recreating an index with new mappings — search is down during reindexing.


Q82: How do you implement Elasticsearch scroll and search_after for deep pagination?​

Technical Answer: Standard from/size pagination degrades past ~10,000 results (each page scans from start). search_after uses the last result's sort values as a cursor for efficient deep pagination. scroll maintains a point-in-time view for bulk exports. Code/Syntax Justification:

# search_after — efficient cursor-based pagination
def search_page(query, sort_values=None, page_size=20):
body = {
'query': {'match': {'body': query}},
'sort': [{'created_at': 'desc'}, {'_id': 'asc'}], # Tiebreaker required
'size': page_size,
}
if sort_values:
body['search_after'] = sort_values

result = es.search(index='articles', body=body)
hits = result['hits']['hits']
next_cursor = hits[-1]['sort'] if hits else None
return hits, next_cursor

# scroll — for bulk data export
def export_all_documents(index):
result = es.search(index=index, scroll='5m', body={'query': {'match_all': {}}, 'size': 1000})
scroll_id = result['_scroll_id']

while True:
hits = result['hits']['hits']
if not hits:
break
yield from hits
result = es.scroll(scroll_id=scroll_id, scroll='5m')

es.clear_scroll(scroll_id=scroll_id)

🔍 Behind the Question: Efficient deep pagination in search systems. ❌ The Red Flags: from: 100000, size: 20 — Elasticsearch must score and sort 100,020 documents to return 20.


Q83: How do you implement Elasticsearch percolator queries?​

Technical Answer: Percolator reverses the search paradigm: instead of "find documents matching a query," it's "find stored queries matching a document." Use for: saved search alerts, content routing rules, and real-time notification when new content matches a user's saved filter. Code/Syntax Justification:

# Step 1: Store saved search queries
es.index(index='saved_searches', body={
'user_id': 'user_123',
'query': {
'bool': {
'must': [
{'match': {'category': 'electronics'}},
{'range': {'price': {'lte': 500}}},
]
}
}
})

# Step 2: When new product is added, find matching saved searches
result = es.search(index='saved_searches', body={
'query': {
'percolate': {
'field': 'query',
'document': {
'title': 'New iPhone Case',
'category': 'electronics',
'price': 29.99,
}
}
}
})

# Result: user_123's saved search matches — send notification
for hit in result['hits']['hits']:
notify_user(hit['_source']['user_id'], "New product matches your saved search!")

🔍 Behind the Question: Real-time alerting and content matching systems. ❌ The Red Flags: Running every user's saved search query against every new product — O(users × products) instead of O(1) percolation.


Technical Answer: Cross-cluster search (CCS) queries multiple Elasticsearch clusters from a single request. Configure remote clusters via cluster.remote settings. Query with cluster_name:index_name syntax. Use for: multi-region search, separating production/analytics clusters, or federated search. Code/Syntax Justification:

// Configure remote cluster
PUT _cluster/settings
{
"persistent": {
"cluster.remote.us_west": {
"seeds": ["es-us-west.internal:9300"]
},
"cluster.remote.eu_central": {
"seeds": ["es-eu.internal:9300"]
}
}
}
# Query across clusters
result = es.search(
index='local_index,us_west:products,eu_central:products',
body={'query': {'match': {'name': 'laptop'}}}
)
# Results merged from all three clusters

🔍 Behind the Question: Multi-region and federated search architectures. ❌ The Red Flags: Replicating all data to every region — storage costs multiply, sync complexity increases.


Q85: How do you implement Elasticsearch runtime fields for schema-on-read?​

Technical Answer: Runtime fields compute values at query time without reindexing. Define them in the search request or in the mapping. Use for: temporary computed fields, A/B testing new field logic, or extracting values from existing fields without a reindex. Code/Syntax Justification:

result = es.search(index='orders', body={
'runtime_mappings': {
'price_category': {
'type': 'keyword',
'script': """
if (doc['total'].value > 1000) emit('premium');
else if (doc['total'].value > 100) emit('standard');
else emit('budget');
"""
},
'age_days': {
'type': 'long',
'script': "emit(ChronoUnit.DAYS.between(doc['created_at'].value, Instant.now()));"
}
},
'query': {'term': {'price_category': 'premium'}},
'aggs': {
'by_category': {'terms': {'field': 'price_category'}},
}
})

🔍 Behind the Question: Flexible schema evolution without reindexing. ❌ The Red Flags: Reindexing 1 billion documents just to add a computed field — runtime fields do this instantly.


Section 14: ORM Performance Patterns​

Q86: How do you detect and fix N+1 queries in production?​

Technical Answer: Use query logging with count tracking: SQLAlchemy's echo=True, Django Debug Toolbar, or nplusone library. In production, log slow queries and high query counts per request. Set alerts on request-level query count thresholds (>20 queries/request). Code/Syntax Justification:

# Django: nplusone detection
# pip install nplusone
INSTALLED_APPS += ['nplusone.ext.django']
MIDDLEWARE += ['nplusone.ext.django.NPlusOneMiddleware']
NPLUSONE_RAISE = True # Raise exception on N+1 in development

# SQLAlchemy: custom query counter
from sqlalchemy import event
import threading

_query_count = threading.local()

@event.listens_for(engine, "before_cursor_execute")
def count_queries(conn, cursor, statement, parameters, context, executemany):
if not hasattr(_query_count, 'count'):
_query_count.count = 0
_query_count.count += 1

# FastAPI middleware to log query count
@app.middleware("http")
async def log_query_count(request, call_next):
_query_count.count = 0
response = await call_next(request)
if _query_count.count > 20:
logger.warning(f"High query count: {_query_count.count} for {request.url}")
return response

🔍 Behind the Question: Performance monitoring and regression detection. ❌ The Red Flags: 200+ queries per page load — invisible without tooling, only discovered when pages take 5 seconds.


Q87: How do you implement database query profiling in SQLAlchemy?​

Technical Answer: Enable SQLAlchemy's echo=True for development, use event.listens_for(engine, "before_cursor_execute") for timing, or integrate with Python profilers. For production, log queries exceeding a duration threshold and include EXPLAIN ANALYZE output. Code/Syntax Justification:

from sqlalchemy import event
import time

# Log slow queries
@event.listens_for(engine, "before_cursor_execute")
def before_execute(conn, cursor, statement, parameters, context, executemany):
conn.info['query_start'] = time.perf_counter()

@event.listens_for(engine, "after_cursor_execute")
def after_execute(conn, cursor, statement, parameters, context, executemany):
duration = time.perf_counter() - conn.info['query_start']
if duration > 0.5: # Log queries taking >500ms
logger.warning(
f"Slow query ({duration:.3f}s): {statement[:200]}",
extra={'duration': duration, 'params': str(parameters)[:200]}
)
# Auto-EXPLAIN for slow queries
if duration > 1.0:
cursor.execute(f"EXPLAIN ANALYZE {statement}", parameters)
plan = cursor.fetchall()
logger.warning(f"Query plan: {plan}")

🔍 Behind the Question: Database performance engineering. ❌ The Red Flags: No slow query logging — a missing index causes a 10-second query unnoticed until users complain.


Q88: How do you implement batch updates efficiently in SQLAlchemy?​

Technical Answer: Use session.execute(update(Model).where(...).values(...)) for bulk updates — single SQL statement instead of loading + modifying + flushing each object individually. For conditional updates, use case() expressions. Code/Syntax Justification:

from sqlalchemy import update, case

# SLOW: load every object, modify, flush (N queries)
users = session.query(User).filter(User.plan == 'trial').all()
for user in users:
user.plan = 'expired'
session.commit() # 10,000 individual UPDATEs

# FAST: single bulk UPDATE statement
stmt = update(User).where(User.plan == 'trial').values(plan='expired')
session.execute(stmt)
session.commit() # 1 single UPDATE

# Conditional bulk update with CASE
stmt = update(User).values(
plan=case(
(User.trial_ends_at < func.now(), 'expired'),
(User.usage > 10000, 'premium'),
else_='free'
)
)
session.execute(stmt)

🔍 Behind the Question: Write performance optimization. ❌ The Red Flags: Updating 100,000 rows by loading each one individually — takes 5 minutes instead of 1 second.


Q89: How do you implement database-level full-text search without Elasticsearch?​

Technical Answer: PostgreSQL provides built-in full-text search with tsvector (document) and tsquery (query) types. Use GIN indexes for fast lookups. Sufficient for small-to-medium datasets (<10M documents). No separate search infrastructure needed. Code/Syntax Justification:

from sqlalchemy import Column, Index, func
from sqlalchemy.dialects.postgresql import TSVECTOR

class Article(Base):
__tablename__ = 'articles'
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str] = mapped_column(String(255))
body: Mapped[str] = mapped_column(Text)
search_vector: Mapped[str] = mapped_column(
TSVECTOR,
Computed("to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))")
)

__table_args__ = (
Index('ix_articles_search', 'search_vector', postgresql_using='gin'),
)

# Full-text search query
stmt = select(Article).where(
Article.search_vector.match('python & async')
).order_by(
func.ts_rank(Article.search_vector, func.to_tsquery('english', 'python & async')).desc()
)
results = session.scalars(stmt).all()

🔍 Behind the Question: Right-sizing search infrastructure. ❌ The Red Flags: Running an Elasticsearch cluster for 1,000 articles — PostgreSQL FTS handles this with zero operational overhead.


Q90: How do you implement GraphQL resolvers with SQLAlchemy efficiently?​

Technical Answer: GraphQL's nested query structure causes severe N+1 problems. Use DataLoader (batched data loading) to aggregate individual get_user(id) calls into a single get_users(ids) query. Combine with SQLAlchemy's selectinload for relationship prefetching. Code/Syntax Justification:

from strawberry.dataloader import DataLoader
from sqlalchemy import select

# DataLoader batches individual loads into single queries
async def load_users(user_ids: list[int]) -> list[User]:
stmt = select(User).where(User.id.in_(user_ids))
result = await db.execute(stmt)
users_by_id = {u.id: u for u in result.scalars()}
return [users_by_id.get(uid) for uid in user_ids]

user_loader = DataLoader(load_fn=load_users)

@strawberry.type
class Order:
user_id: int

@strawberry.field
async def user(self) -> User:
return await user_loader.load(self.user_id)
# 100 orders → 1 query for all users (not 100 queries)

🔍 Behind the Question: GraphQL + ORM performance integration. ❌ The Red Flags: GraphQL resolvers doing session.get(User, order.user_id) per order — 100 orders = 100 DB queries.


Section 15: ORM Testing Patterns​

Q91: How do you implement database fixtures for integration tests?​

Technical Answer: Use pytest fixtures with transaction rollback for fast, isolated tests. Each test runs in a transaction that's rolled back after completion — no cleanup needed, no test interference. For SQLAlchemy, use session.begin_nested() with savepoint=True. Code/Syntax Justification:

import pytest
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

@pytest.fixture(scope='session')
def engine():
return create_engine(TEST_DATABASE_URL)

@pytest.fixture(scope='session')
def tables(engine):
Base.metadata.create_all(engine)
yield
Base.metadata.drop_all(engine)

@pytest.fixture
def db_session(engine, tables):
connection = engine.connect()
transaction = connection.begin()
session = sessionmaker(bind=connection)()

yield session

session.close()
transaction.rollback() # All changes rolled back — clean state
connection.close()

# Test — changes are automatically rolled back
def test_create_user(db_session):
user = User(name='Test', email='test@example.com')
db_session.add(user)
db_session.flush()
assert user.id is not None
assert db_session.query(User).count() == 1
# After test: transaction rolled back, table is empty again

🔍 Behind the Question: Fast, isolated database testing. ❌ The Red Flags: Each test truncating all tables in teardown — slow (10+ seconds) and error-prone.


Q92: How do you test database migrations in CI?​

Technical Answer: Test three things: (1) Full upgrade from empty database to head. (2) Full downgrade from head to base. (3) Each migration individually (upgrade + downgrade for each). Use a Docker PostgreSQL container in CI for fast, disposable databases. Code/Syntax Justification:

import pytest
from alembic.config import Config
from alembic import command
from alembic.script import ScriptDirectory

@pytest.fixture
def alembic_cfg():
cfg = Config("alembic.ini")
cfg.set_main_option("sqlalchemy.url", TEST_DB_URL)
return cfg

def test_full_upgrade(alembic_cfg):
command.upgrade(alembic_cfg, "head")

def test_full_downgrade(alembic_cfg):
command.upgrade(alembic_cfg, "head")
command.downgrade(alembic_cfg, "base")

def test_each_migration_reversible(alembic_cfg):
script = ScriptDirectory.from_config(alembic_cfg)
revisions = list(script.walk_revisions())
for rev in reversed(revisions):
command.upgrade(alembic_cfg, rev.revision)
if rev.down_revision:
command.downgrade(alembic_cfg, rev.down_revision)
command.upgrade(alembic_cfg, rev.revision)

🔍 Behind the Question: Migration reliability in CI/CD pipelines. ❌ The Red Flags: First time running downgrade is in production during an incident — untested rollback path.


Q93: How do you implement test database seeding strategies?​

Technical Answer: Three strategies: (1) Fixture files (JSON/YAML loaded before tests). (2) Factory pattern (Factory Boy — generate randomized data per test). (3) Snapshot testing (dump production schema, sanitize PII, use as test fixture). Factories are best for unit/integration tests. Code/Syntax Justification:

import factory
from factory.alchemy import SQLAlchemyModelFactory

class UserFactory(SQLAlchemyModelFactory):
class Meta:
model = User
sqlalchemy_session_persistence = 'commit'

name = factory.Faker('name')
email = factory.LazyAttribute(lambda o: f"{o.name.lower().replace(' ', '.')}@test.com")
plan = factory.Iterator(['free', 'pro', 'enterprise'])

class OrderFactory(SQLAlchemyModelFactory):
class Meta:
model = Order

user = factory.SubFactory(UserFactory)
total = factory.Faker('pydecimal', left_digits=3, right_digits=2, positive=True)
status = 'pending'

class Params:
completed = factory.Trait(status='completed', completed_at=factory.Faker('date_time_this_year'))

# Usage in tests
def test_completed_orders_report(db_session):
UserFactory.create_batch(5) # 5 users with random orders
completed = OrderFactory.create_batch(10, completed=True)
pending = OrderFactory.create_batch(3)

report = generate_report()
assert report['completed_count'] == 10

🔍 Behind the Question: Test data management and isolation. ❌ The Red Flags: Tests sharing a global test database without cleanup — test A's data causes test B to fail intermittently.


Section 16: Search Performance & Operations​

Q94: How do you implement Elasticsearch cluster sizing and capacity planning?​

Technical Answer: Calculate: (1) Storage = source_data × (1 + replicas) × 1.45 (overhead). (2) Shards = total_storage / max_shard_size (50GB max per shard). (3) Nodes = ensure no node holds >80% of its storage. (4) Heap = 50% of RAM, max 32GB. Monitor: CPU, JVM heap, disk I/O, search latency, indexing rate. Code/Syntax Justification:

Example: 500GB source data, 1 replica

Storage needed: 500GB × 2 (replica) × 1.45 = 1,450 GB
Shard count: 1,450 / 50 = ~30 shards (15 primary + 15 replica)
Node count: 3 data nodes × 500GB SSD each

Node sizing:
- r6g.xlarge (32GB RAM, 4 vCPU)
- Heap: 16GB (50% of RAM)
- Storage: 500GB gp3 SSD

Monitoring thresholds:
- CPU: alert at 80%
- Heap: alert at 85% (GC pressure)
- Disk: alert at 85% (watermark)
- Search latency p99: alert at 500ms

🔍 Behind the Question: Infrastructure planning for search systems. ❌ The Red Flags: Single-node Elasticsearch in production — no redundancy, shard allocation issues, single point of failure.


Q95: How do you optimize Elasticsearch indexing throughput?​

Technical Answer: For bulk indexing: increase refresh_interval to 30s (or -1 to disable during bulk load), increase index.translog.flush_threshold_size, use the _bulk API with 5-15MB batches, increase index.number_of_replicas to 0 during initial load, and use multiple indexing threads. Code/Syntax Justification:

# Optimize for bulk indexing
es.indices.put_settings(index='products', body={
'index': {
'refresh_interval': '-1', # Disable refresh during bulk load
'number_of_replicas': 0, # No replicas during bulk (re-enable after)
'translog.flush_threshold_size': '1gb',
}
})

# Bulk index with optimal batch size
from elasticsearch.helpers import bulk

def bulk_index(documents, batch_size=5000):
actions = [
{'_index': 'products', '_id': doc['id'], '_source': doc}
for doc in documents
]
success, errors = bulk(es, actions, chunk_size=batch_size, request_timeout=120)
print(f"Indexed {success} documents, {len(errors)} errors")

# Restore settings after bulk load
es.indices.put_settings(index='products', body={
'index': {'refresh_interval': '1s', 'number_of_replicas': 1}
})
es.indices.forcemerge(index='products', max_num_segments=5)

🔍 Behind the Question: Search infrastructure operations. ❌ The Red Flags: Indexing documents one at a time — 10,000x slower than bulk API.


Q96: How do you implement Elasticsearch security with field-level and document-level access control?​

Technical Answer: Use Elasticsearch Security (formerly X-Pack Security) with: roles that restrict access to specific indices, document-level security (DLS) that filters results by a query, and field-level security (FLS) that hides sensitive fields. Essential for multi-tenant search. Code/Syntax Justification:

{
"role_name": "tenant_a_reader",
"indices": [
{
"names": ["shared_products"],
"privileges": ["read"],
"field_security": {
"grant": ["name", "description", "price", "category"],
"except": ["internal_cost", "supplier_notes"]
},
"query": {
"term": { "tenant_id": "tenant_a" }
}
}
]
}

🔍 Behind the Question: Multi-tenant search security. ❌ The Red Flags: All tenants searching the same index without document-level security — Tenant A sees Tenant B's data.


Section 17: Django ORM Advanced​

Q97: How do you implement Django ORM custom managers and querysets?​

Technical Answer: Custom managers add model-level query methods. Custom querysets add chainable filter methods. Combine them for a fluent, domain-specific query API. Use as_manager() to create a manager from a queryset. Code/Syntax Justification:

class OrderQuerySet(models.QuerySet):
def active(self):
return self.filter(status__in=['pending', 'confirmed', 'shipped'])

def for_user(self, user):
return self.filter(user=user)

def high_value(self, threshold=1000):
return self.filter(total__gte=threshold)

def this_month(self):
return self.filter(created_at__month=timezone.now().month)

class OrderManager(models.Manager):
def get_queryset(self):
return OrderQuerySet(self.model, using=self._db)

def revenue_this_month(self):
return self.get_queryset().this_month().aggregate(
total=models.Sum('total'),
count=models.Count('id'),
)

class Order(models.Model):
objects = OrderManager()

# Chainable usage
orders = Order.objects.active().for_user(user).high_value(500).this_month()
stats = Order.objects.revenue_this_month()

🔍 Behind the Question: Query encapsulation and domain-specific APIs. ❌ The Red Flags: Order.objects.filter(status__in=[...], user=user, total__gte=500) duplicated in 20 views.


Q98: How do you implement Django ORM annotations and aggregations?​

Technical Answer: annotate() adds computed columns to each row. aggregate() computes a single summary value. Use F() for column references, Q() for complex filters, Subquery() for correlated subqueries, and Window() for window functions. Code/Syntax Justification:

from django.db.models import F, Q, Sum, Count, Avg, Case, When, Subquery, OuterRef, Window
from django.db.models.functions import Rank, RowNumber

# Annotate with computed fields
users = User.objects.annotate(
order_count=Count('orders'),
total_spent=Sum('orders__total'),
avg_order_value=Avg('orders__total'),
has_recent_order=Case(
When(orders__created_at__gte=thirty_days_ago, then=True),
default=False,
output_field=BooleanField(),
),
)

# Window function — rank users by spending
users_ranked = User.objects.annotate(
spending_rank=Window(
expression=Rank(),
order_by=F('total_spent').desc(),
)
)

# Subquery — latest order date per user
latest_order = Order.objects.filter(user=OuterRef('pk')).order_by('-created_at').values('created_at')[:1]
users = User.objects.annotate(last_order_date=Subquery(latest_order))

🔍 Behind the Question: Advanced query composition in Django ORM. ❌ The Red Flags: Loading all orders into Python to calculate totals — sum(o.total for o in user.orders.all()) — database should do this.


Q99: How do you implement Django ORM database functions?​

Technical Answer: Django provides database functions (Coalesce, Greatest, Now, Length, Upper, Concat, etc.) that compile to SQL functions. For custom SQL functions, use Func() or register custom functions. Code/Syntax Justification:

from django.db.models.functions import Coalesce, Greatest, Now, Length, Concat, ExtractMonth
from django.db.models import Value, CharField, F, Func

# Built-in functions
users = User.objects.annotate(
display_name=Coalesce('nickname', 'full_name', Value('Anonymous')),
name_length=Length('full_name'),
greeting=Concat(Value('Hello, '), 'full_name', output_field=CharField()),
account_age_days=Now() - F('created_at'),
signup_month=ExtractMonth('created_at'),
)

# Custom database function
class ArrayLength(Func):
function = 'array_length'
template = '%(function)s(%(expressions)s, 1)'

users = User.objects.annotate(
tag_count=ArrayLength('tags')
).filter(tag_count__gte=3)

# PostgreSQL-specific: JSONB operators
from django.contrib.postgres.fields.jsonb import KeyTextTransform
users = User.objects.annotate(
city=KeyTextTransform('city', 'address')
).filter(city='San Francisco')

🔍 Behind the Question: Leveraging database-level computation. ❌ The Red Flags: Doing string manipulation in Python after loading all rows — [u for u in User.objects.all() if len(u.name) > 50].


Technical Answer: select_related() uses SQL JOIN — one query, good for ForeignKey/OneToOne (single-valued). prefetch_related() uses separate queries with WHERE id IN (...) — good for ManyToMany/reverse ForeignKey (multi-valued). Wrong choice causes either slow joins or N+1 queries. Code/Syntax Justification:

# select_related — SQL JOIN (single-valued relationships)
orders = Order.objects.select_related('user', 'user__company').all()
# 1 query: SELECT orders.*, users.*, companies.* FROM orders JOIN users JOIN companies

# prefetch_related — separate queries (multi-valued relationships)
users = User.objects.prefetch_related('orders', 'orders__items').all()
# 3 queries:
# SELECT * FROM users
# SELECT * FROM orders WHERE user_id IN (1,2,3...)
# SELECT * FROM items WHERE order_id IN (4,5,6...)

# Custom prefetch with filtering
from django.db.models import Prefetch

users = User.objects.prefetch_related(
Prefetch('orders', queryset=Order.objects.filter(status='active').select_related('product'))
).all()

# Rule of thumb:
# ForeignKey / OneToOne → select_related (JOIN)
# ManyToMany / Reverse FK → prefetch_related (IN query)

🔍 Behind the Question: Query optimization fundamentals in Django. ❌ The Red Flags: Using select_related on a ManyToMany — massive cartesian product JOIN; using prefetch_related on a ForeignKey — unnecessary extra query.


Section 18: Migration Automation​

Q101: How do you implement automated migration generation in CI/CD?​

Technical Answer: Run makemigrations --check (Django) or compare model metadata to migration head (Alembic) in CI to detect missing migrations. Fail the build if a developer changes a model without generating a corresponding migration. Code/Syntax Justification:

# GitHub Actions: check for missing migrations
- name: Check for missing Django migrations
run: python manage.py makemigrations --check --dry-run
# Exits non-zero if there are model changes without migrations

# Alembic: check model-migration sync
- name: Check Alembic migration sync
run: |
alembic upgrade head
alembic check # Alembic 1.9+ — compares models to current DB schema
# Exits non-zero if models have changed without a migration

🔍 Behind the Question: Preventing schema drift in team development. ❌ The Red Flags: Deploying code that references a column that doesn't exist yet because someone forgot to generate the migration.


Q102: How do you implement migration dependency ordering in a microservices architecture?​

Technical Answer: When services share database schemas, migration ordering matters. Use: (1) a shared migration package that all services depend on, (2) API-based schema verification before deployment, or (3) a migration coordinator service that runs migrations in the correct order. Code/Syntax Justification:

# Deployment pipeline with migration ordering
steps:
- name: Run shared migrations first
run: alembic -c shared/alembic.ini upgrade head

- name: Verify shared schema
run: python verify_schema.py --expected-version=42

- name: Run service-specific migrations
run: |
alembic -c auth/alembic.ini upgrade head
alembic -c billing/alembic.ini upgrade head
alembic -c orders/alembic.ini upgrade head

- name: Deploy application
run: kubectl apply -f k8s/

🔍 Behind the Question: Schema management in distributed systems. ❌ The Red Flags: Service A deploys and creates a foreign key to a table that Service B hasn't migrated yet — deployment fails.


Q103: How do you implement database migration monitoring and alerting?​

Technical Answer: Track: migration duration (alert if >5 minutes), lock wait time, table size before/after, query performance before/after. Use pg_stat_activity to monitor lock contention during migration. Log migration events to an audit table. Code/Syntax Justification:

# Migration timing wrapper
import time
from alembic import context

def run_timed_migrations():
start = time.time()
context.run_migrations()
duration = time.time() - start

# Log to migration audit table
op.execute(f"""
INSERT INTO migration_audit (revision, duration_seconds, executed_at)
VALUES ('{context.get_current_revision()}', {duration}, NOW())
""")

if duration > 300: # 5 minutes
send_alert(f"Migration took {duration:.0f}s — investigate for lock contention")

# Monitor locks during migration
SELECT pid, state, query, wait_event_type, query_start
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
AND query LIKE '%ALTER TABLE%';

🔍 Behind the Question: Operational awareness during schema changes. ❌ The Red Flags: Running a migration that locks a critical table for 20 minutes with no monitoring — silent production outage.


Section 19: Search Relevance Engineering​

Q104: How do you implement learning-to-rank in Elasticsearch?​

Technical Answer: Learning-to-Rank (LTR) uses machine learning models to score search results based on features like text relevance, popularity, recency, and user behavior signals. Train a model offline (XGBoost/LightGBM), upload to Elasticsearch via the LTR plugin, and use sltr query to rescore results. Code/Syntax Justification:

# Define features for LTR model
features = [
{"name": "title_match", "template": {"match": {"title": "{{keywords}}"}}},
{"name": "popularity", "template": {"function_score": {"field_value_factor": {"field": "views"}}}},
{"name": "recency", "template": {"function_score": {"exp": {"created_at": {"scale": "30d"}}}}},
{"name": "price_normalized", "template": {"function_score": {"script_score": {"script": "1.0 / (1 + doc['price'].value)"}}}},
]

# Use trained model for re-ranking
result = es.search(index='products', body={
'query': {
'bool': {
'must': {'match': {'title': 'wireless headphones'}},
'should': {
'sltr': {
'model': 'product_ranking_model_v2',
'params': {'keywords': 'wireless headphones'},
}
}
}
},
'rescore': {
'window_size': 100,
'query': {
'rescore_query': {
'sltr': {'model': 'product_ranking_model_v2', 'params': {'keywords': 'wireless headphones'}}
}
}
}
})

🔍 Behind the Question: Search quality engineering with ML. ❌ The Red Flags: Static BM25 scoring for an e-commerce search — ignoring popularity, conversion rate, and business priorities.


Q105: How do you implement A/B testing for search relevance?​

Technical Answer: Route a percentage of users to experimental search configurations. Compare metrics: click-through rate (CTR), mean reciprocal rank (MRR), zero-result rate, and conversion rate. Use Elasticsearch index aliases to point different user segments to different index configurations. Code/Syntax Justification:

import hashlib

def get_search_variant(user_id: str) -> str:
# Deterministic assignment — same user always gets same variant
hash_val = int(hashlib.md5(user_id.encode()).hexdigest(), 16)
return 'variant_a' if hash_val % 100 < 50 else 'variant_b'

def search(user_id: str, query: str):
variant = get_search_variant(user_id)

if variant == 'variant_a':
# Control: default BM25 scoring
body = {'query': {'match': {'title': query}}}
else:
# Treatment: boosted function_score
body = {'query': {'function_score': {
'query': {'match': {'title': query}},
'functions': [{'field_value_factor': {'field': 'popularity', 'modifier': 'log1p'}}]
}}}

result = es.search(index='products', body=body)

# Log for analysis
log_search_event(user_id, query, variant, [h['_id'] for h in result['hits']['hits']])
return result

🔍 Behind the Question: Data-driven search improvement methodology. ❌ The Red Flags: Changing search relevance in production without A/B testing — no way to know if it improved or degraded.


Section 20: ORM Anti-Patterns​

Q106: What are the most common ORM anti-patterns?​

Technical Answer: (1) N+1 queries — lazy loading in loops. (2) Fat models — 5000-line model class. (3) SELECT * — loading all columns when you need 2. (4) No indexes — full table scans. (5) ORM for everything — using ORM for bulk operations or reports. (6) Ignoring the generated SQL. Code/Syntax Justification:

# Anti-pattern 1: N+1
for user in session.query(User).all():
print(user.orders) # SELECT per user

# Anti-pattern 3: SELECT * when you need 2 columns
users = session.query(User).all() # Loads 20 columns
names = [u.name for u in users]

# Fix: load only needed columns
names = session.scalars(select(User.name)).all()
# SELECT name FROM users (not SELECT * FROM users)

# Anti-pattern 5: ORM for bulk report
# SLOW: ORM loads 1M objects into memory
total = sum(o.total for o in session.query(Order).all())

# FAST: Database does the calculation
total = session.scalar(select(func.sum(Order.total)))
# SELECT SUM(total) FROM orders

🔍 Behind the Question: Recognizing and avoiding performance pitfalls. ❌ The Red Flags: All 6 anti-patterns present in a codebase — typical in ORMs used by developers who never look at generated SQL.


Q107: How do you implement query complexity limits to prevent ORM abuse?​

Technical Answer: Set per-request limits on: number of queries (max 50), total query time (max 1s), result set size (max 10,000 rows), and join depth (max 3). Use middleware to track and enforce limits. Log violations for review. Code/Syntax Justification:

import threading
from sqlalchemy import event

MAX_QUERIES_PER_REQUEST = 50
MAX_QUERY_TIME_MS = 1000

_request_stats = threading.local()

@event.listens_for(engine, "before_cursor_execute")
def track_query(conn, cursor, statement, parameters, context, executemany):
if not hasattr(_request_stats, 'count'):
_request_stats.count = 0
_request_stats.start = time.time()

_request_stats.count += 1

if _request_stats.count > MAX_QUERIES_PER_REQUEST:
logger.error(f"Query limit exceeded: {_request_stats.count} queries in single request")
raise TooManyQueriesError(f"Max {MAX_QUERIES_PER_REQUEST} queries per request")

# FastAPI middleware
@app.middleware("http")
async def enforce_query_limits(request, call_next):
_request_stats.count = 0
_request_stats.start = time.time()
response = await call_next(request)

total_time = (time.time() - _request_stats.start) * 1000
if total_time > MAX_QUERY_TIME_MS:
logger.warning(f"Slow request: {total_time:.0f}ms, {_request_stats.count} queries")

response.headers['X-Query-Count'] = str(_request_stats.count)
return response

🔍 Behind the Question: Production guardrails against ORM misuse. ❌ The Red Flags: A single API endpoint executing 500+ queries — takes 30 seconds, nobody notices until monitoring catches it.


Q108: How do you implement ORM query logging and analysis?​

Technical Answer: Log all ORM-generated SQL with execution time to identify: slow queries, N+1 patterns, missing indexes, and unnecessary queries. In development, use SQL formatter for readability. In production, sample slow queries and send to observability platform. Code/Syntax Justification:

import logging
import sqlparse
from sqlalchemy import event

sql_logger = logging.getLogger('sqlalchemy.engine')

@event.listens_for(engine, "after_cursor_execute")
def log_query(conn, cursor, statement, parameters, context, executemany):
duration = time.perf_counter() - conn.info.get('query_start', 0)

if duration > 0.1: # Log queries > 100ms
formatted = sqlparse.format(statement, reindent=True, keyword_case='upper')
sql_logger.warning(
f"Slow query ({duration:.3f}s):\n{formatted}\nParams: {parameters}"
)

# Django: database query logging
LOGGING = {
'loggers': {
'django.db.backends': {
'level': 'DEBUG',
'handlers': ['console'],
}
}
}

# Django Debug Toolbar — visual query analysis
INSTALLED_APPS += ['debug_toolbar']
MIDDLEWARE += ['debug_toolbar.middleware.DebugToolbarMiddleware']

🔍 Behind the Question: SQL-level observability through the ORM. ❌ The Red Flags: Never looking at generated SQL — "the ORM handles it" attitude until a 100ms endpoint becomes 10 seconds.


Section 21: Real-World ORM Scenarios​

Q109: Design an ORM schema for a multi-tenant SaaS application.​

Technical Answer: Use row-level tenancy with a tenant_id on every table. Enforce via: (1) PostgreSQL Row-Level Security (RLS) policies, (2) SQLAlchemy session events that auto-filter by tenant, (3) Django middleware that sets the tenant context. Always index tenant_id as the first column in composite indexes. Code/Syntax Justification:

# PostgreSQL RLS — database-level enforcement
"""
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
USING (tenant_id = current_setting('app.current_tenant_id')::uuid);
"""

# SQLAlchemy — auto-set tenant_id on insert
from contextvars import ContextVar
current_tenant = ContextVar('current_tenant')

@event.listens_for(Session, "do_orm_execute")
def filter_by_tenant(execute_state):
tenant_id = current_tenant.get(None)
if tenant_id and execute_state.is_select:
for mapper in execute_state.all_mappers:
if hasattr(mapper.class_, 'tenant_id'):
execute_state.statement = execute_state.statement.where(
mapper.class_.tenant_id == tenant_id
)

@event.listens_for(Base, "init", propagate=True)
def set_tenant_on_create(target, args, kwargs):
if hasattr(target, 'tenant_id') and 'tenant_id' not in kwargs:
target.tenant_id = current_tenant.get()

🔍 Behind the Question: SaaS architecture — the most critical design decision. ❌ The Red Flags: No tenant isolation — one missing WHERE tenant_id = ? exposes all customers' data.


Q110: Design an ORM schema for a role-based access control (RBAC) system.​

Technical Answer: Classic RBAC: User → UserRole → Role → RolePermission → Permission. Use many-to-many through tables. Add resource_type and resource_id for object-level permissions. Cache permission checks in Redis to avoid per-request DB queries. Code/Syntax Justification:

class User(Base):
__tablename__ = 'users'
id: Mapped[int] = mapped_column(primary_key=True)
roles: Mapped[list['Role']] = relationship(secondary='user_roles', back_populates='users')

def has_permission(self, permission: str, resource=None) -> bool:
for role in self.roles:
for perm in role.permissions:
if perm.name == permission:
if resource is None or perm.resource_matches(resource):
return True
return False

class Role(Base):
__tablename__ = 'roles'
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(50), unique=True)
permissions: Mapped[list['Permission']] = relationship(secondary='role_permissions')

class Permission(Base):
__tablename__ = 'permissions'
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(100)) # e.g., "orders:read", "orders:write"
resource_type: Mapped[str | None] = mapped_column(String(50))
resource_id: Mapped[int | None] = mapped_column(Integer)

# Check with caching
@cache_region.cache_on_arguments(expiration_time=300)
def check_permission(user_id: int, permission: str) -> bool:
user = session.get(User, user_id, options=[selectinload(User.roles).selectinload(Role.permissions)])
return user.has_permission(permission)

🔍 Behind the Question: Authorization system design. ❌ The Red Flags: Hardcoding if user.is_admin checks everywhere — no role hierarchy, no permission granularity.


Q111: Design an ORM schema for an event sourcing system.​

Technical Answer: Store every state change as an immutable event. The events table is append-only — no updates or deletes. Reconstruct current state by replaying events. Use materialized views (separate tables) for fast reads. Event table: aggregate_id, event_type, version, data, timestamp. Code/Syntax Justification:

class Event(Base):
__tablename__ = 'events'
id: Mapped[int] = mapped_column(primary_key=True)
aggregate_type: Mapped[str] = mapped_column(String(50))
aggregate_id: Mapped[UUID] = mapped_column(PgUUID)
event_type: Mapped[str] = mapped_column(String(100))
version: Mapped[int] = mapped_column(Integer)
data: Mapped[dict] = mapped_column(JSONB)
created_at: Mapped[datetime] = mapped_column(server_default=func.now())

__table_args__ = (
UniqueConstraint('aggregate_id', 'version'), # Optimistic concurrency
Index('ix_events_aggregate', 'aggregate_id', 'version'),
)

class EventStore:
def append(self, aggregate_id: UUID, events: list[dict], expected_version: int):
for i, event_data in enumerate(events):
event = Event(
aggregate_id=aggregate_id,
aggregate_type=event_data['type'].split('.')[0],
event_type=event_data['type'],
version=expected_version + i + 1,
data=event_data['data'],
)
session.add(event)
session.flush() # UniqueConstraint enforces optimistic concurrency

def get_events(self, aggregate_id: UUID) -> list[Event]:
return session.scalars(
select(Event).where(Event.aggregate_id == aggregate_id).order_by(Event.version)
).all()

🔍 Behind the Question: Advanced persistence patterns. ❌ The Red Flags: Mutable state with UPDATE — loses history, can't audit, can't debug race conditions.


Q112: How do you implement database-per-tenant with SQLAlchemy?​

Technical Answer: Each tenant gets their own database (or schema in PostgreSQL). Route connections dynamically based on tenant context. Benefits: complete data isolation, per-tenant backup/restore, per-tenant scaling. Drawback: operational complexity scales linearly with tenant count. Code/Syntax Justification:

from sqlalchemy import create_engine
from contextvars import ContextVar

current_tenant_id = ContextVar('current_tenant_id')
_engine_cache = {}

def get_tenant_engine(tenant_id: str):
if tenant_id not in _engine_cache:
db_url = f"postgresql://user:pass@localhost/{tenant_id}_db"
_engine_cache[tenant_id] = create_engine(db_url, pool_size=5)
return _engine_cache[tenant_id]

def get_tenant_session():
tenant_id = current_tenant_id.get()
engine = get_tenant_engine(tenant_id)
return Session(bind=engine)

# FastAPI middleware
@app.middleware("http")
async def set_tenant(request, call_next):
tenant_id = extract_tenant(request)
current_tenant_id.set(tenant_id)
return await call_next(request)

# Usage — automatically routes to correct tenant database
@app.get("/orders")
def get_orders():
session = get_tenant_session()
return session.scalars(select(Order)).all()

🔍 Behind the Question: Multi-tenancy isolation strategies. ❌ The Red Flags: 10,000 tenants × 5 connections each = 50,000 database connections — connection pool exhaustion.


Section 22: Data Migration Patterns​

Q113: How do you implement a data migration for splitting a table?​

Technical Answer: Split a table by extracting columns into a new table with a foreign key. Migration steps: (1) Create new table. (2) Copy data from old columns to new table. (3) Add foreign key to original table. (4) Deploy code that reads from new table. (5) Drop old columns. Code/Syntax Justification:

# Step 1: Create addresses table and copy data
def upgrade():
op.create_table('addresses',
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('user_id', sa.Integer, sa.ForeignKey('users.id'), unique=True),
sa.Column('street', sa.String(255)),
sa.Column('city', sa.String(100)),
sa.Column('state', sa.String(50)),
sa.Column('zip_code', sa.String(20)),
)

# Step 2: Copy data
op.execute("""
INSERT INTO addresses (user_id, street, city, state, zip_code)
SELECT id, street, city, state, zip_code FROM users
WHERE street IS NOT NULL
""")

# Step 3: Add FK (deployed in next release after code change)
# op.drop_column('users', 'street')
# op.drop_column('users', 'city')
# op.drop_column('users', 'state')
# op.drop_column('users', 'zip_code')

🔍 Behind the Question: Schema normalization in a live system. ❌ The Red Flags: Dropping old columns before updating the application code — instant 500 errors.


Q114: How do you implement a data migration for merging tables?​

Technical Answer: Merge tables by adding columns from the secondary table to the primary, copying data, updating foreign keys, and dropping the secondary table. Use the expand-contract pattern to avoid breaking running code. Code/Syntax Justification:

def upgrade():
# Expand: add columns to primary table
op.add_column('users', sa.Column('phone', sa.String(20)))
op.add_column('users', sa.Column('bio', sa.Text))

# Migrate data from secondary table
op.execute("""
UPDATE users u
SET phone = p.phone, bio = p.bio
FROM profiles p
WHERE p.user_id = u.id
""")

# Contract (separate migration, after code update):
# op.drop_table('profiles')

🔍 Behind the Question: Schema denormalization for performance. ❌ The Red Flags: Dropping the source table before verifying data was correctly copied — data loss.


Q115: How do you implement a data migration for changing a column type?​

Technical Answer: PostgreSQL can ALTER COLUMN TYPE for compatible changes (varchar length increase, int→bigint). For incompatible changes, use the expand-contract pattern: add new column, copy+transform data, switch reads, drop old column. Code/Syntax Justification:

# Compatible change — instant in PostgreSQL
def upgrade():
op.alter_column('users', 'name', type_=sa.String(500)) # Increase varchar length

# Incompatible change — expand-contract
def upgrade():
# Step 1: Add new column
op.add_column('orders', sa.Column('total_cents', sa.BigInteger))

# Step 2: Copy and transform data
op.execute("UPDATE orders SET total_cents = CAST(total * 100 AS BIGINT)")

# Step 3: (separate migration after code update)
# op.drop_column('orders', 'total')
# op.alter_column('orders', 'total_cents', new_column_name='total')

def downgrade():
op.drop_column('orders', 'total_cents')

🔍 Behind the Question: Safe type changes in production. ❌ The Red Flags: ALTER COLUMN total TYPE BIGINT on a table with 100M rows — full table rewrite, locks table for minutes.


Section 23: Full-Stack Search Architecture​

Q116: How do you architect a search system for a large e-commerce platform?​

Technical Answer: Source of truth: PostgreSQL. Search index: Elasticsearch. Sync: CDC (Debezium) or outbox pattern. Search features: autocomplete (completion suggester), faceted search (aggregations), personalized ranking (LTR), spell correction (fuzzy), synonyms. Cache: Redis for popular queries. Code/Syntax Justification:

Architecture:
PostgreSQL (source of truth)
→ Debezium CDC → Kafka → Elasticsearch Connector
→ Elasticsearch (search index)
→ Products index (optimized for search)
→ Categories index (facet navigation)
→ Suggestions index (autocomplete)

Query flow:
1. User types "lapt" → Completion Suggester → "laptop", "laptop bag"
2. User searches "laptop" → Full-text search with:
- Field boosting (title^3, description^1)
- Function score (popularity, recency, margin)
- Faceted aggregations (brand, price range, rating)
- Fuzzy matching for typos
- Synonym expansion
3. Redis cache for top 1000 queries (TTL: 5 minutes)
4. Click-through tracking → Kafka → ML pipeline → LTR model retraining

🔍 Behind the Question: End-to-end search system design. ❌ The Red Flags: Using PostgreSQL LIKE '%laptop%' for product search — no relevance scoring, no fuzzy matching, sequential scan.


Q117: How do you implement search analytics and monitoring?​

Technical Answer: Track: (1) zero-result queries (content gaps), (2) query latency distribution, (3) click-through rate per query, (4) search-to-conversion rate, (5) popular queries (trending), (6) failed queries (errors). Log every search event to Kafka → analytics pipeline. Code/Syntax Justification:

import time

async def search_with_analytics(query: str, user_id: str, filters: dict):
start = time.perf_counter()
results = es.search(index='products', body=build_query(query, filters))
duration = (time.perf_counter() - start) * 1000

# Log search event
analytics.log({
'event': 'search',
'query': query,
'user_id': user_id,
'filters': filters,
'result_count': results['hits']['total']['value'],
'duration_ms': duration,
'top_results': [h['_id'] for h in results['hits']['hits'][:5]],
'is_zero_results': results['hits']['total']['value'] == 0,
})

# Alert on zero-result queries spike
if results['hits']['total']['value'] == 0:
increment_counter('zero_result_queries')

return results

🔍 Behind the Question: Search quality measurement and improvement. ❌ The Red Flags: No search analytics — can't tell if search quality improved or degraded after a relevance change.


Technical Answer: Hybrid search combines traditional keyword search (BM25) with vector similarity search (dense vectors from ML embeddings). Elasticsearch 8.x+ supports knn queries natively. Use reciprocal rank fusion (RRF) to merge BM25 and kNN scores. Code/Syntax Justification:

from sentence_transformers import SentenceTransformer

model = SentenceTransformer('all-MiniLM-L6-v2')

# Index with both text and embedding
def index_product(product):
embedding = model.encode(f"{product['title']} {product['description']}")
es.index(index='products', body={
'title': product['title'],
'description': product['description'],
'embedding': embedding.tolist(),
})

# Hybrid search: keyword + semantic
def hybrid_search(query: str):
query_embedding = model.encode(query)
return es.search(index='products', body={
'query': {
'bool': {
'should': [
{'match': {'title': {'query': query, 'boost': 2}}},
{'knn': {
'field': 'embedding',
'query_vector': query_embedding.tolist(),
'k': 10,
'num_candidates': 100,
}}
]
}
}
})

🔍 Behind the Question: Next-generation search combining keyword and semantic understanding. ❌ The Red Flags: Pure keyword search for "comfortable work from home chair" — misses results titled "ergonomic office seating" because there's no keyword overlap.


Section 24: ORM with NoSQL​

Q119: How do you use SQLAlchemy-like patterns with MongoDB (MongoEngine/ODM)?​

Technical Answer: MongoEngine provides an ORM-like API for MongoDB: Document classes define schemas, QuerySet provides filtering/ordering, and EmbeddedDocument handles nested structures. While MongoDB is schemaless, MongoEngine enforces schema at the application level. Code/Syntax Justification:

from mongoengine import Document, EmbeddedDocument, fields, connect

connect('mydb', host='mongodb://localhost:27017')

class Address(EmbeddedDocument):
street = fields.StringField(required=True)
city = fields.StringField(required=True)
zip_code = fields.StringField()

class User(Document):
name = fields.StringField(required=True, max_length=100)
email = fields.EmailField(unique=True)
addresses = fields.EmbeddedDocumentListField(Address)
tags = fields.ListField(fields.StringField())
metadata = fields.DictField()

meta = {
'indexes': [
'email',
{'fields': ['name'], 'sparse': True},
{'fields': ['$name', '$email'], 'default_language': 'english'}, # Text search
]
}

# Usage
user = User(name='Alice', email='alice@example.com')
user.addresses.append(Address(street='123 Main St', city='SF'))
user.save()

# Querying
active_users = User.objects(tags__in=['active']).order_by('-created_at')[:20]

🔍 Behind the Question: Document database ORM patterns. ❌ The Red Flags: Using raw pymongo dictionaries everywhere — no validation, no schema enforcement, no IDE support.


Q120: How do you implement the repository pattern that supports both SQL and NoSQL backends?​

Technical Answer: Define an abstract repository interface with CRUD operations. Implement concrete repositories for SQLAlchemy (SQL) and MongoEngine (NoSQL). The service layer depends on the interface, not the implementation — swap backends without changing business logic. Code/Syntax Justification:

from abc import ABC, abstractmethod
from typing import TypeVar, Generic

T = TypeVar('T')

class Repository(ABC, Generic[T]):
@abstractmethod
def get(self, id: int | str) -> T | None: ...

@abstractmethod
def list(self, filters: dict = None, limit: int = 100) -> list[T]: ...

@abstractmethod
def create(self, entity: T) -> T: ...

@abstractmethod
def update(self, entity: T) -> T: ...

@abstractmethod
def delete(self, id: int | str) -> bool: ...

class SQLAlchemyUserRepo(Repository[User]):
def __init__(self, session: Session):
self.session = session

def get(self, id: int) -> User | None:
return self.session.get(User, id)

def list(self, filters=None, limit=100):
stmt = select(User).limit(limit)
if filters:
for key, value in filters.items():
stmt = stmt.where(getattr(User, key) == value)
return self.session.scalars(stmt).all()

class MongoUserRepo(Repository[UserDoc]):
def get(self, id: str) -> UserDoc | None:
return UserDoc.objects(id=id).first()

def list(self, filters=None, limit=100):
qs = UserDoc.objects(**filters) if filters else UserDoc.objects()
return list(qs.limit(limit))

🔍 Behind the Question: Clean architecture that is database-agnostic. ❌ The Red Flags: MongoDB-specific query syntax scattered across service files — impossible to migrate to PostgreSQL later.

Section 25: ORM Connection Pooling & Scaling​

QN: How does SQLAlchemy pool connections, and what are the pool types?​

Technical Answer: SQLAlchemy uses a connection pool to reuse database connections. Types: QueuePool (default, bounded queue), NullPool (no pooling, new connection every time), StaticPool (single connection reused, good for SQLite threading tests), AssertionPool (raises error if same connection used twice). Tuning pool size and max overflow is critical for high-concurrency apps.

Code/Syntax Justification:

from sqlalchemy import create_engine
engine = create_engine('postgresql://user:pw@host/db',
pool_size=10, max_overflow=20, pool_recycle=3600)

🔍 Behind the Question: Understanding that DB connections are expensive and pooling is essential for performance. ❌ The Red Flags: Setting pool_size=0 or using NullPool in production under load.

QN: What is pool_recycle and when do you need it?​

Technical Answer: pool_recycle is the time (seconds) after which a connection is discarded and re-established. It's needed for MySQL (which drops connections after 8 hours by default) and cloud databases like RDS behind a proxy that may drop idle connections. Without it, you get "MySQL server has gone away" errors.

Code/Syntax Justification: pool_recycle=3600 (1 hour) ensures connections are refreshed before hitting the MySQL 8-hour timeout.

🔍 Behind the Question: Managing long-lived connections in cloud environments. ❌ The Red Flags: Setting a very low recycle (e.g., 10s) causing constant reconnect overhead.

QN: How do you handle "Connection Leaks" in an ORM?​

Technical Answer: A leak happens when connections aren't returned to the pool (e.g., exceptions bypassing session.close()). Fix: Use context managers (with Session() as session:) or register atexit handlers. Monitor db_pool_exhausted metrics.

Code/Syntax Justification:

with Session() as session:
result = session.execute(select(User)).fetchall()
# session automatically closed and returned to pool here

🔍 Behind the Question: Resource management and production stability. ❌ The Red Flags: Relying on Python's garbage collector to close sessions (it's non-deterministic).

QN: Explain max_overflow in SQLAlchemy pooling.​

Technical Answer: pool_size is the number of connections normally kept open. max_overflow is the number of additional connections that can be created if the pool is full. Once the overflow connections are returned, they are discarded (not kept in the pool). This allows handling traffic spikes.

Code/Syntax Justification: pool_size=5, max_overflow=10 → Max 15 concurrent connections. Exceeding 15 blocks or errors out based on pool_timeout.

🔍 Behind the Question: Capacity planning for database connections. ❌ The Red Flags: Setting max_overflow to a huge number like 1000, which can overwhelm the database server.

QN: How does Django's CONN_MAX_AGE relate to pooling?​

Technical Answer: Django doesn't use a pool by default. CONN_MAX_AGE keeps a connection open for that many seconds (persistent connection). If set to 0, a new connection is created per request. For production, set it to 60-300s to avoid TCP handshake overhead.

Code/Syntax Justification:

DATABASES = {'default': {
'ENGINE': 'django.db.backends.postgresql',
'CONN_MAX_AGE': 300, # 5 minutes
}}

🔍 Behind the Question: Django-specific connection management. ❌ The Red Flags: Using CONN_MAX_AGE in serverless environments (each Lambda has its own process, persistent connections don't help).

QN: What is "Pooling" in PgBouncer and how does it differ from ORM pooling?​

Technical Answer: PgBouncer is an external connection pooler that sits between your app and PostgreSQL. It uses a "Session" mode (connection returned when client disconnects) or "Transaction" mode (connection returned after each transaction). ORM pools manage connections inside the app process; PgBouncer manages them at the network level, allowing thousands of clients to share fewer DB connections.

Code/Syntax Justification: pool_mode = transaction in pgbouncer.ini is best for server applications.

🔍 Behind the Question: Scaling PostgreSQL beyond the default 100 connections. ❌ The Red Flags: Using "Session" mode with an ORM that has its own pool (double pooling causes confusion).

QN: How do you monitor ORM connection pool health?​

Technical Answer: SQLAlchemy doesn't expose pool metrics by default. You can add event listeners (event.listen(engine, "checkout", my_func)) to track checked-out connections. Alternatively, monitor database-side metrics like pg_stat_activity count or use PgBouncer's SHOW POOLS command.

Code/Syntax Justification:

from sqlalchemy import event
@event.listens_for(engine, "checkout")
def ping_connection(dbapi_connection, connection_record, connection_proxy):
# Log or increment metric here

🔍 Behind the Question: Observability of database resources. ❌ The Red Flags: Having no visibility into connection pool usage until the app crashes with "TimeoutError."

QN: Explain pool_use_lifo in SQLAlchemy.​

Technical Answer: When True (default in newer versions), the pool behaves as a LIFO (Last In, First Out) stack, reusing the most recently used connection. This is more efficient because the DB server is more likely to have that connection's memory/state cached. When False, it's FIFO (round-robin).

Code/Syntax Justification: create_engine(..., pool_use_lifo=True)

🔍 Behind the Question: Subtle performance tuning. ❌ The Red Flags: Worrying about this before tuning pool_size or query performance.

QN: How do you handle "Deadlocks" in ORM transactions?​

Technical Answer: A deadlock occurs when two transactions lock resources in opposite order. Fix: 1. Always acquire locks in a consistent order. 2. Use READ COMMITTED isolation (Postgres default) or SELECT FOR UPDATE carefully. 3. Implement retry logic (decorator) for OperationalError with specific deadlock error codes.

Code/Syntax Justification:

from sqlalchemy.exc import OperationalError
for attempt in range(3):
try:
with Session() as s: s.commit()
break
except OperationalError as e:
if "deadlock detected" in str(e): continue
raise

🔍 Behind the Question: Database transaction expertise. ❌ The Red Flags: Infinite retry loops without backoff, or retrying non-idempotent operations blindly.

QN: What is echo_pool='debug' used for?​

Technical Answer: It's a SQLAlchemy logging setting that prints detailed pool checkout/checkin activity. Useful for debugging "Why is my connection not returning to the pool?" or "Why am I getting timeouts?".

Code/Syntax Justification: create_engine(..., echo_pool='debug') → Logs every checkout/checkin to stderr.

🔍 Behind the Question: Debugging connection pool issues. ❌ The Red Flags: Leaving this on in production (log noise and performance hit).

QN: How does Eloquent handle connection pooling in Laravel?​

Technical Answer: Laravel uses the underlying PDO (PHP Data Objects). It doesn't have an internal pool like SQLAlchemy. Instead, it relies on persistent connections in PDO (which is similar to a pool but managed by PHP-FPM or the web server process). For high scale, use an external pooler like PgBouncer.

Code/Syntax Justification:

'pgsql' => [
'driver' => 'pgsql',
'persistent' => true, // PDO persistent connection
]

🔍 Behind the Question: Understanding that pooling is language/runtime dependent. ❌ The Red Flags: Assuming Eloquent has a built-in pool that you can tune with pool_size.

QN: Explain "Connection Starvation" in serverless environments.​

Technical Answer: In Lambda, every concurrent execution creates a new DB connection (because each Lambda container is isolated). If you have 1000 concurrent Lambdas, you need 1000 DB connections. This starves the DB. Fix: Use RDS Proxy, which manages a pool and multiplexes Lambda connections to a few DB connections.

Code/Syntax Justification: RDS Proxy endpoint replaces the DB host in your Lambda DSN.

🔍 Behind the Question: Serverless-to-database scaling issues. ❌ The Red Flags: Connecting Lambda directly to RDS without a proxy in high-concurrency scenarios.

QN: How do you implement "Read/Write Splitting" in an ORM?​

Technical Answer: SQLAlchemy can bind specific sessions to different engines. You create two engines: engine_write (Primary) and engine_read (Replica). In your code, use Session(bind=engine_read) for queries and Session(bind=engine_write) for writes. Django has DATABASE_ROUTERS for this.

Code/Syntax Justification:

# SQLAlchemy
read_session = sessionmaker(bind=read_replica_engine)()

🔍 Behind the Question: High-availability and read scaling. ❌ The Red Flags: Writing to a read replica (causes replication errors).

QN: What is "Prepared Statement" caching in SQLAlchemy?​

Technical Answer: SQLAlchemy compiles SQL expressions into "compiled" objects. It caches these compilations. For raw SQL, use text() with bindparams() to allow the DB driver to prepare the statement once and reuse the plan.

Code/Syntax Justification:

from sqlalchemy import text
stmt = text("SELECT * FROM users WHERE id = :id")
result = session.execute(stmt, {'id': 5}) # DB can cache plan for ':id'

🔍 Behind the Question: Database performance optimization. ❌ The Red Flags: Using f-strings to build SQL (f"SELECT... {id}") which prevents plan caching and opens SQL injection.

QN: How do you configure SQLAlchemy for AWS Aurora Serverless v2?​

Technical Answer: Aurora Serverless v2 scales automatically. Use pool_size small (e.g., 5) and max_overflow large (e.g., 50) because the serverless DB can handle bursts. Use RDS Proxy if you have many concurrent clients. Set pool_pre_ping=True to validate connections.

Code/Syntax Justification: pool_pre_ping=True sends a simple "SELECT 1" before using a connection to ensure it's still alive.

🔍 Behind the Question: Cloud-native database configuration. ❌ The Red Flags: Using pool_recycle with Serverless v2 (it handles connection management internally).

QN: Explain "Transaction Nesting" in SQLAlchemy (savepoints).​

Technical Answer: SQLAlchemy supports nested transactions via begin_nested(), which uses SQL Savepoints. If the inner transaction rolls back, the outer transaction can still commit. This is useful for complex operations where a partial failure shouldn't kill the whole batch.

Code/Syntax Justification:

with sess.begin():
sess.add(User(name="A"))
with sess.begin_nested():
sess.add(User(name="B"))
# raise Exception("Fail B") -> Only B rolls back, A commits

🔍 Behind the Question: Advanced transaction control. ❌ The Red Flags: Assuming begin_nested works exactly like a real nested transaction in all databases (behavior varies).

QN: How do you handle "Cancellation" of long-running ORM queries?​

Technical Answer: There's no standard way in ORMs. At the DB level, you can find the PID and pg_cancel_backend(pid). In the app, use asyncio timeouts if using async drivers, or run the query in a thread and kill the thread (risky). Best practice: Use statement_timeout on the DB user or role.

Code/Syntax Justification: SET statement_timeout = '5s'; in PostgreSQL.

🔍 Behind the Question: Resource management for slow queries. ❌ The Red Flags: Letting a bad query run for hours, blocking table locks.

Section 26: Multi-Tenancy ORM Patterns​

QN: Compare Database-per-Tenant vs Schema-per-Tenant vs Row-Level Security.​

Technical Answer:

  • DB-per-Tenant: Maximum isolation, but high operational cost (backups, migrations per DB).
  • Schema-per-Tenant: Good isolation, one DB, use search_path to switch. Migrations run once but apply to all schemas.
  • Row-Level Security (RLS): Single table with tenant_id. Less isolation, but easiest to implement. Requires strict query filtering.

Code/Syntax Justification:

-- RLS Policy
CREATE POLICY tenant_isolation ON orders
USING (tenant_id = current_setting('app.current_tenant')::INT);

🔍 Behind the Question: SaaS architecture decisions. ❌ The Red Flags: Using RLS but forgetting to set the session variable, leading to data leaks.

QN: How do you implement Schema-based multi-tenancy in SQLAlchemy?​

Technical Answer: You dynamically set the search_path for the PostgreSQL session. You can do this in an event listener on checkout or at the start of a request. Each tenant gets a schema (e.g., tenant_123), and the tables inside are identical.

Code/Syntax Justification:

from sqlalchemy import event
@event.listens_for(engine, "checkout")
def set_search_path(dbapi_conn, conn_record, proxy_conn):
tenant_id = get_current_tenant() # From request context
dbapi_conn.cursor().execute(f"SET search_path TO tenant_{tenant_id}, public")

🔍 Behind the Question: Practical implementation of Schema-based tenancy. ❌ The Red Flags: Using string formatting without sanitizing tenant_id (SQL Injection via tenant ID).

QN: How does Django's tenant_schemas library work?​

Technical Answer: django-tenant-schemas or django-tenants middleware detects the hostname (e.g., client1.app.com). It sets the PostgreSQL search_path to the corresponding schema. It also provides a TenantAwareModel base class that automatically filters by schema.

Code/Syntax Justification:

# urls.py or middleware
from tenant_schemas.middleware import TenantMiddleware

🔍 Behind the Question: Django-specific multi-tenancy. ❌ The Red Flags: Using this library but manually writing SQL without the schema prefix, causing cross-tenant data access.

QN: What is the "Shared Table" tenancy pattern and its risks?​

Technical Answer: All tenants share tables with a tenant_id column. Risk: Forgetting a WHERE tenant_id = X clause in any query leads to a massive data breach. Mitigation: Use a global query filter (SQLAlchemy SessionEvents.before_flush or Django custom managers).

Code/Syntax Justification:

# SQLAlchemy automatic filter
@event.listens_for(Query, "before_compile", retval=True)
def filter_tenant(query):
# Inject tenant_id filter automatically

🔍 Behind the Question: Security by design in multi-tenant apps. ❌ The Red Flags: Relying on developers to "remember" to add the filter in every query.

QN: How do you handle migrations in a Schema-per-Tenant setup?​

Technical Answer: You run the migration once, but you must apply it to every schema. Tools like Alembic can loop through schemas: for schema in schemas: execute_migration(schema). Alternatively, use a "Template Schema" approach where new tenants get a brand new schema created from a template.

Code/Syntax Justification:

for tenant in Tenant.all():
engine.execute(f"SET search_path TO {tenant.schema_name}")
# Run Alembic upgrade

🔍 Behind the Question: Operational complexity of multi-tenancy. ❌ The Red Flags: Running migrations manually for 500 tenants via copy-paste.

QN: Explain "Data Isolation" in Row-Level Security (RLS).​

Technical Answer: RLS is a PostgreSQL feature where policies are attached to tables. Every query, regardless of how it's written, is forced to include the policy condition. Even if the ORM generates SELECT * FROM orders, Postgres appends WHERE tenant_id = current_setting('app.tenant').

Code/Syntax Justification:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

🔍 Behind the Question: Defense-in-depth security. ❌ The Red Flags: Enabling RLS but granting BYPASSRLS to the application user (defeats the purpose).

QN: How do you handle "Cross-Tenant Analytics" in a Schema-based setup?​

Technical Answer: You cannot easily JOIN across schemas in standard SQL. Solution: 1. Create a "Analytics Schema" that uses postgres_fdw to pull data from all tenant schemas into one view. 2. Or, use a Data Warehouse (Redshift/Snowflake) that ETLs data from all tenants.

Code/Syntax Justification:

CREATE SERVER tenant_1_srv FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'mydb', schema 'tenant_1');

🔍 Behind the Question: Analytics in SaaS products. ❌ The Red Flags: Querying each tenant schema sequentially in a Python loop for a "Global Report" (slow and error-prone).

QN: What is "Tenant Context Propagation" in a request lifecycle?​

Technical Answer: The tenant ID must be available throughout the entire request: Middleware (extract from hostname) -> ORM Session (set search_path) -> Logging (include tenant_id in logs). If any layer misses it, data isolation fails. Use contextvars in Python or thread-locals (though contextvars is preferred for async).

Code/Syntax Justification:

import contextvars
tenant_id_var = contextvars.ContextVar('tenant_id')

🔍 Behind the Question: Full-stack awareness of multi-tenancy. ❌ The Red Flags: Storing tenant_id in a global variable (breaks under concurrency/threads).

QN: How do you prevent "NoSQL Injection" in MongoDB (MongoEngine)?​

Technical Answer: Similar to SQL injection. Never use raw user input in query keys or regex without escaping. MongoEngine handles field lookups safely, but if you use __raw__ queries, validate the input strictly.

Code/Syntax Justification:

# UNSAFE
User.objects(__raw__={'name': user_input})
# SAFE
User.objects(name=user_input)

🔍 Behind the Question: Security in NoSQL contexts. ❌ The Red Flags: Using eval() or exec() on user input to build Mongo queries.

QN: Explain "Hierarchical Tenants" (Parent/Child).​

Technical Answer: A tenant (e.g., "Acme Corp") has sub-tenants (e.g., "Acme HR", "Acme Engineering"). The data model usually has a parent_tenant_id. Queries for "Acme Corp" must recursively include sub-tenants. This is complex for RLS; often handled in application logic.

Code/Syntax Justification: Recursive CTE in SQL to fetch all child tenant IDs.

🔍 Behind the Question: Advanced SaaS data modeling. ❌ The Red Flags: Hardcoding sub-tenant IDs in queries.

QN: How do you handle "Tenant Provisioning" in an ORM?​

Technical Answer: When a new tenant signs up: 1. Create DB records (Tenant model). 2. If Schema-based: CREATE SCHEMA tenant_X. 3. Run migrations on that schema. 4. Seed initial data. This should be an atomic operation (transaction or Saga).

Code/Syntax Justification:

with engine.begin() as conn:
conn.execute(text(f"CREATE SCHEMA IF NOT EXISTS tenant_{tid}"))
# Run DDL for that schema

🔍 Behind the Question: Onboarding automation. ❌ The Red Flags: Doing this synchronously in an HTTP request (can timeout).

QN: What is "Soft Deletion" and how does it interact with tenancy?​

Technical Answer: Instead of DELETE, you set deleted_at = NOW(). In a multi-tenant app, you must ensure your "global filter" (see QN 4 in this section) also respects deleted_at. Otherwise, soft-deleted data from Tenant A might show up in Tenant B's "active" queries if the filter is missing.

Code/Syntax Justification: session.query(User).filter(User.deleted_at == None) must be automatic.

🔍 Behind the Question: Data lifecycle management. ❌ The Red Flags: Implementing soft delete only in the ORM but the DB has a hard DELETE trigger from a batch job.

QN: How do you handle "Tenant-Specific Features" in an ORM?​

Technical Answer: Use a "Feature Flag" model linked to the Tenant. In the ORM layer, check tenant.has_feature('advanced_reporting') before allowing access. This is application logic, not DB logic, but the ORM defines the relationship.

Code/Syntax Justification:

if not request.tenant.features.filter(name='reporting').exists():
raise PermissionError

🔍 Behind the Question: SaaS product flexibility. ❌ The Red Flags: Using if tenant.id == 123: in code to enable features (unmaintainable).

QN: Explain "Database Sharding" vs Multi-Tenancy.​

Technical Answer: Sharding splits data horizontally (e.g., Users A-M on DB1, N-Z on DB2) for scale. Multi-tenancy isolates data for security/business reasons. They can overlap: You might shard your multi-tenant DBs (e.g., Tenants 1-100 on DB1, 101-200 on DB2).

Code/Syntax Justification: The ORM binds to different engines based on a "Shard Key" (usually tenant_id range).

🔍 Behind the Question: Scaling strategies. ❌ The Red Flags: Sharding too early when simple vertical scaling (bigger DB instance) would suffice.

QN: How do you test multi-tenant ORM code?​

Technical Answer: Use factories. Create Tenant A and Tenant B. Create data for A. Set context to B. Assert that B cannot see A's data. Test migrations by running them on a test schema and verifying tables exist.

Code/Syntax Justification:

def test_tenant_isolation():
set_tenant(tenant_a)
assert Order.query.count() == 0

🔍 Behind the Question: QA for SaaS. ❌ The Red Flags: Testing only with a single tenant and assuming isolation works.

QN: What is "Connection Pool Fragmentation" in multi-tenancy?​

Technical Answer: If you create a new Engine for every tenant (DB-per-tenant), you end up with 500 pools, each with its own connections. This wastes resources. Fix: Use a single engine but set the search_path dynamically (Schema approach) or use PgBouncer to consolidate connections.

Code/Syntax Justification: One Engine, many Schemas = One Pool.

🔍 Behind the Question: Resource efficiency. ❌ The Red Flags: Creating create_engine() inside a request handler for a specific tenant.

QN: How do you handle "Timezone" differences across tenants?​

Technical Answer: Always store data in UTC in the database. In the ORM, use sqlalchemy_utils.TimezoneType or store as TIMESTAMP WITH TIME ZONE. When presenting to the tenant, convert to their configured timezone in the application layer, not the DB layer (though AT TIME ZONE works in SQL).

Code/Syntax Justification:

# Store
created_at = Column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc))

🔍 Behind the Question: Global SaaS considerations. ❌ The Red Flags: Storing dates in local time without a timezone column (Ambiguous during DST).

Section 27: SQLAlchemy Core vs ORM Deep Dive​

QN: When should you use SQLAlchemy Core instead of the ORM?​

Technical Answer: Use Core for: 1. Bulk inserts/updates (faster, less overhead). 2. Complex reports with manual SQL control. 3. Migrating from raw SQL to SQLAlchemy. Use ORM for: 1. Standard CRUD with business logic. 2. Relationships (joins handled automatically). 3. When readability/maintainability is key.

Code/Syntax Justification:

# Core
conn.execute(users.insert().values(name="Bob"))
# ORM
session.add(User(name="Bob"))

🔍 Behind the Question: Choosing the right tool for the job. ❌ The Red Flags: Using ORM for a 1-million-row bulk insert (extremely slow); or using Core for a simple User profile update (unnecessary complexity).

QN: Explain the SQLAlchemy "Declarative" vs "Imperative" mapping.​

Technical Answer:

  • Declarative (Modern/Default): Define table + class in one step using Base = declarative_base().
  • Imperative (Classical): Define the class, define the table, then map them using mapper(Foo, foo_table).

Code/Syntax Justification:

# Declarative
class User(Base): __tablename__ = 'users'; id = Column(Integer, primary_key=True)
# Imperative
mapper(User, user_table)

🔍 Behind the Question: Understanding SQLAlchemy history and flexibility. ❌ The Red Flags: Mixing both styles in the same project without a very good reason.

QN: What is a "Hybrid Attribute" in SQLAlchemy?​

Technical Answer: A @hybrid_property works at two levels: 1. At the Python instance level (it's a method). 2. At the SQL expression level (it generates SQL). Example: full_name property that concatenates first_name and last_name. When you access user.full_name, it's Python. When you filter User.full_name, it becomes SQL.

Code/Syntax Justification:

from sqlalchemy.ext.hybrid import hybrid_property
@hybrid_property
def full_name(self):
return f"{self.first_name} {self.last_name}"
@full_name.expression
def full_name(cls):
return cls.first_name + " " + cls.last_name

🔍 Behind the Question: Advanced ORM features for DRY code. ❌ The Red Flags: Defining a Python @property and expecting it to work in a session.query().filter() (it won't).

QN: How do you handle "JSON/JSONB" columns in SQLAlchemy?​

Technical Answer: Use Column(JSON) or Column(JSONB) (Postgres specific). SQLAlchemy can store Python dicts directly. For querying JSONB fields in filters, use cast or the AsJSON element. In 2.0 style, you can use col.type attributes.

Code/Syntax Justification:

from sqlalchemy import JSON, cast
stmt = select(User).where(cast(User.metadata['age'], Integer) > 30)

🔍 Behind the Question: Modern PostgreSQL features in SQLAlchemy. ❌ The Red Flags: Storing JSON as a plain Text column and parsing it in Python (loses DB-side querying power).

QN: What is "Eager Loading" and its N+1 problem?​

Technical Answer: N+1 happens when you load a list of objects (1 query) and then loop through them accessing a relationship (N queries). Fix: Use joinedload (JOIN in SQL) or selectinload (separate IN query) to load relationships in the initial query.

Code/Syntax Justification:

from sqlalchemy.orm import selectinload
stmt = select(User).options(selectinload(User.addresses))

🔍 Behind the Question: Performance optimization 101 for ORMs. ❌ The Red Flags: Not knowing this exists and wondering why a simple page load does 500 SQL queries.

QN: Explain "Association Objects" vs Simple Relationships.​

Technical Answer: A simple Many-to-Many uses a hidden join table. An Association Object is when that join table has extra data (e.g., UserProject table with role column). In SQLAlchemy, you map this as a full class and use relationship with secondary pointing to the association object, or use explicit relationships to the assoc object.

Code/Syntax Justification:

class UserProject(Base):
__tablename__ = 'user_project'
user_id = Column(FK(User.id), primary_key=True)
project_id = Column(FK(Project.id), primary_key=True)
role = Column(String)

🔍 Behind the Question: Complex data modeling. ❌ The Red Flags: Trying to stuff extra data into a simple secondary table argument (syntax won't support it).

QN: How do you use "Window Functions" in SQLAlchemy ORM?​

Technical Answer: Use func.row_number().over(...) or func.rank(). These are SQLAlchemy Core constructs wrapped in the ORM query. Useful for "Top N per group" queries (e.g., Top 3 scores per student).

Code/Syntax Justification:

from sqlalchemy import func, over
stmt = select(
User.name,
func.row_number().over(partition_by=User.dept, order_by=User.salary.desc())
)

🔍 Behind the Question: Advanced SQL in an ORM. ❌ The Red Flags: Pulling all rows to Python and sorting there (slow and memory-heavy).

QN: What is "Identity Map" pattern in SQLAlchemy?​

Technical Answer: The Session maintains a map of {primary_key: object}. If you query the same ID twice within a transaction, the second query returns the same Python object from memory (identity check is passes). This ensures consistency but can cause issues if you mutate objects expecting them to be fresh from DB.

Code/Syntax Justification:

u1 = session.get(User, 1)
u2 = session.get(User, 1)
assert u1 is u2 # True

🔍 Behind the Question: Understanding ORM internals. ❌ The Red Flags: Assuming session.get always hits the database (it might not due to identity map).

QN: How do you use "Custom Types" (TypeDecorator) in SQLAlchemy?​

Technical Answer: Create a class inheriting from TypeDecorator to handle custom data conversion. Example: Encrypting a column. The process_bind_param encrypts on save, process_result_value decrypts on load.

Code/Syntax Justification:

from sqlalchemy import TypeDecorator
class EncryptedString(TypeDecorator):
impl = String
def process_bind_param(self, value, dialect):
return encrypt(value)

🔍 Behind the Question: Extending SQLAlchemy for specific needs. ❌ The Red Flags: Trying to encrypt data in the application layer but forgetting to handle the DB migration for the column type change.

QN: Explain "ORM Events" (Mapper Events vs Session Events).​

Technical Answer:

  • Mapper Events: Triggered by actions on the mapped class (e.g., load, before_insert). Decorator: @event.listens_for(User, "before_insert").
  • Session Events: Triggered by session actions (e.g., before_flush, after_commit).

Code/Syntax Justification:

from sqlalchemy import event
@event.listens_for(Session, "before_flush")
def receive_before_flush(session, flush_context, instances):
# Modify objects before they go to DB

🔍 Behind the Question: Hooking into the ORM lifecycle. ❌ The Red Flags: Doing heavy I/O (like HTTP calls) inside an event listener (blocks the DB transaction).

QN: What is "Bulk Operations" in SQLAlchemy 2.0?​

Technical Answer: session.bulk_save_objects(), bulk_insert_mappings(), bulk_update_mappings(). These bypass the ORM unit-of-work and identity map for speed. They don't trigger events or validation. Use only for high-volume data loading where performance is critical.

Code/Syntax Justification:

session.bulk_insert_mappings(User, [{"name": "A"}, {"name": "B"}])

🔍 Behind the Question: High-performance data ingestion. ❌ The Red Flags: Using bulk ops for single-row inserts (the overhead of setup isn't worth it).

QN: How do you handle "Enum" types in SQLAlchemy?​

Technical Answer: Use Enum from sqlalchemy (wraps Python enum.Enum) or postgresql.ENUM for DB-level enum types. DB-level enums are stricter but harder to migrate. Python enums are easier to change (just change the Python code), but the DB stores strings/ints.

Code/Syntax Justification:

from enum import Enum
class UserStatus(str, Enum): ACTIVE = "active"
class User(Base): status = Column(Enum(UserStatus))

🔍 Behind the Question: Type safety in databases. ❌ The Red Flags: Using raw strings in the DB and relying on comments to remember valid values.

QN: What is the difference between session.add() and session.merge()?​

Technical Answer: add() puts an object into the session (must be new or detached). merge() takes an object (even from outside the session) and reconciles it with the session. If the PK exists, it updates; if not, it inserts (or loads existing). merge is useful for "stateless" operations where you get a dict/object from an API.

Code/Syntax Justification:

# merge: "I don't care if it's in the session, make it match this state"
new_user = User(id=1, name="Updated")
session.merge(new_user)

🔍 Behind the Question: Session management nuances. ❌ The Red Flags: Using merge inside a loop for thousands of items (slower than bulk operations).

QN: How do you implement "Soft Delete" using ORM events?​

Technical Answer: Listen to before_flush. If an object is marked for deletion (session.delete(obj)), intercept it. Set obj.deleted_at = now() and change the state to "persistent" (effectively canceling the DELETE and issuing an UPDATE).

Code/Syntax Justification:

@event.listens_for(Session, "before_flush")
def soft_delete_handler(session, flush_context, instances):
for obj in session.deleted:
obj.deleted_at = datetime.now()
session.add(obj) # Re-add to cancel deletion

🔍 Behind the Question: Customizing ORM behavior. ❌ The Red Flags: Forgetting to filter out soft-deleted items in queries (they still exist in DB).

QN: Explain "Polymorphic Loading" in SQLAlchemy (Inheritance).​

Technical Answer: When using Joined Table Inheritance, querying the base class (select(Employee)) loads all subclasses. SQLAlchemy generates a massive LEFT JOIN. Fix: Use with_polymorphic to control which subclasses are joined, or accept the cost if you truly need all types.

Code/Syntax Justification:

emp = with_polymorphic(Employee, [Manager, Developer])
stmt = select(emp)

🔍 Behind the Question: Complex inheritance patterns. ❌ The Red Flags: Having deep inheritance hierarchies (3+ levels) in SQL—usually a sign of over-engineering.

QN: How do you handle "Timeouts" in SQLAlchemy queries?​

Technical Answer: Use execution_options(timeout=5) on the query or statement. For connections, use pool_timeout. Note: Query timeout is often DB-specific (e.g., PostgreSQL uses statement_timeout).

Code/Syntax Justification:

stmt = select(User).execution_options(timeout=5)

🔍 Behind the Question: Preventing hung queries. ❌ The Red Flags: Setting a global timeout of 1 second for all queries (fast queries are fine, but complex reports will fail).

QN: What is the "Generic Relationship" pattern in SQLAlchemy?​

Technical Answer: When a table needs to link to different parent tables (e.g., a Comment can belong to a Post or a Photo). Use polymorphic_identity and polymorphic_on columns to store the type of parent, plus a generic foreign key (often implemented with three columns: object_type, object_id, object_id_int).

Code/Syntax Justification:

class Comment(Base):
__tablename__ = 'comments'
id = Column(Integer, primary_key=True)
object_type = Column(String) # 'post' or 'photo'
object_id = Column(Integer)
# Mapper configuration for polymorphism

🔍 Behind the Question: Flexible data modeling. ❌ The Red Flags: Using raw string IDs and no foreign key constraints (breaks referential integrity).

Section 28: Advanced Alembic Operations​

QN: How do you handle "Branched Migrations" in Alembic?​

Technical Answer: By default, Alembic is linear. Branches happen if two developers create a migration independently. Fix: Use alembic merge heads to create a new migration that joins the branches. Or, adopt a "Rebase" workflow where devs always upgrade head before creating a new migration.

Code/Syntax Justification: alembic merge heads -m "merge branch a and b"

🔍 Behind the Question: Team workflow conflicts. ❌ The Red Flags: Manually editing migration files to change down_revision (breaks history).

QN: What is an "Offline" migration (SQL generation)?​

Technical Answer: Instead of connecting to the DB and running upgrade(), you run alembic upgrade head --sql. This prints the raw SQL to stdout. DBAs who don't allow direct app access to production DBs love this—they review the SQL and run it manually.

Code/Syntax Justification: alembic upgrade head --sql > migration.sql

🔍 Behind the Question: Enterprise deployment processes. ❌ The Red Flags: Generating SQL for Dev (Postgres 15) and running it on Prod (Postgres 12) without checking compatibility.

QN: How do you write "Data Migrations" (not just schema) in Alembic?​

Technical Answer: In the upgrade() function, after the schema change, use the op.get_bind() to get a connection, then execute raw SQL or use the SQLAlchemy table object to insert/update data.

Code/Syntax Justification:

def upgrade():
op.add_column('users', sa.Column('status', sa.String()))
conn = op.get_bind()
conn.execute(sa.text("UPDATE users SET status = 'active'"))

🔍 Behind the Question: Populating new columns or fixing data. ❌ The Red Flags: Writing a data migration that assumes the table is empty (it's not, and the update takes hours).

QN: How do you handle "Rollback" of a migration that failed halfway?​

Technical Answer: Alembic's downgrade() is manual. If a migration fails, you often have a "partial apply." Fix: Write downgrade() carefully. If the failure was mid-script, you might need to manually undo steps before running downgrade. Ideally, migrations should be atomic (transaction per migration).

Code/Syntax Justification: Wrap multiple operations in a transaction (Alembic does this by default for databases that support DDL transactions like Postgres).

🔍 Behind the Question: Failure recovery. ❌ The Red Flags: Assuming downgrade() will perfectly undo a failed upgrade() that crashed on step 5 of 10.

QN: What is the alembic.ini script_location?​

Technical Answer: It tells Alembic where to find the versions/ directory and env.py. If you have multiple databases or a complex project structure, you might have multiple script_locations or multiple Alembic configurations.

Code/Syntax Justification: script_location = db/migrations (a folder containing alembic.ini and env.py).

🔍 Behind the Question: Project structure. ❌ The Red Flags: Hardcoding paths in alembic.ini that only work on one developer's machine.

QN: How do you use context.configure() in env.py for multi-tenant runs?​

Technical Answer: env.py is the script that runs during upgrade. You can modify context.configure(target_metadata=...) to dynamically change the metadata. For multi-tenant (schema-based), you might loop through schemas and call context.run_migrations(schema_name=schema).

Code/Syntax Justification:

# env.py
for schema in schemas:
context.run_migrations(schema_name=schema)

🔍 Behind the Question: Scripting Alembic for complex environments. ❌ The Red Flags: Putting complex business logic inside env.py (keep it as a runner, not a logic container).

QN: Explain "Autogenerate" limitations in Alembic.​

Technical Answer: Autogenerate compares metadata to the DB and generates a migration. It misses: 1. Indexes on certain backends. 2. Changes to table/column comments. 3. Changes in constraint names. 4. Python-side defaults vs SQL-side defaults. Always review the generated file!

Code/Syntax Justification: alembic revision --autogenerate -m "add email column"

🔍 Behind the Question: Knowing what the tool can and cannot do. ❌ The Red Flags: Blindly applying autogenerated migrations without reading the upgrade() function.

QN: How do you handle "Column Renames" safely in Alembic?​

Technical Answer: Autogenerate might see "Drop Old Col" + "Create New Col" (data loss!). Fix: Manually write the migration to use op.alter_column('old_name', new_column_name='new_name'). This preserves data.

Code/Syntax Justification: op.alter_column('users', 'old_name', new_column_name='new_name')

🔍 Behind the Question: Data preservation during refactoring. ❌ The Red Flags: Letting Autogenerate drop and recreate a column with 1 million rows.

QN: What is the alembic_version table?​

Technical Answer: Alembic tracks the current version of the database in a table (default name alembic_version). It contains one row with one column: the version_num. If you lose this table, Alembic thinks the DB is at "base" (empty).

Code/Syntax Justification: SELECT version_num FROM alembic_version;

🔍 Behind the Question: Understanding migration state. ❌ The Red Flags: Manually updating the alembic_version table to skip a migration (dangerous).

QN: How do you run Alembic inside a Python application (not CLI)?​

Technical Answer: Use the alembic.config.Config object and alembic.command module. This is useful for serverless environments where you don't have a shell, or for running migrations on startup.

Code/Syntax Justification:

from alembic.config import Config
from alembic import command
alembic_cfg = Config("alembic.ini")
command.upgrade(alembic_cfg, "head")

🔍 Behind the Question: Programmatic migration control. ❌ The Red Flags: Running migrations on every request (should be once at startup/deployment).

QN: How do you handle "Large Table" migrations without downtime?​

Technical Answer: 1. Add column with NULL allowed + Default value in app code first. 2. Backfill in batches (small updates). 3. Add constraint (NOT NULL) after backfill. 4. Use CREATE INDEX CONCURRENTLY (Postgres) so the table isn't locked during index creation.

Code/Syntax Justification: op.create_index('idx_name', 'my_table', ['col'], postgresql_concurrently=True)

🔍 Behind the Question: Zero-downtime deployments. ❌ The Red Flags: Adding a UNIQUE constraint to a 50GB table in a single transaction (locks the table for hours).

QN: What is "Batch Mode" in Alembic?​

Technical Answer: For SQLite, you can't ALTER TABLE easily. Batch mode copies the data to a new table with the new schema, then drops the old one. It's slow but necessary for SQLite. Enable with with op.batch_alter_table('users') as batch_op:.

Code/Syntax Justification:

with op.batch_alter_table('users') as batch_op:
batch_op.add_column(sa.Column('age', sa.Integer()))

🔍 Behind the Question: SQLite specific limitations. ❌ The Red Flags: Using Batch Mode on PostgreSQL (unnecessary overhead).

QN: How do you test an Alembic migration?​

Technical Answer: 1. Create a test DB. 2. Run upgrade head. 3. Verify schema (introspect with SQLAlchemy inspect). 4. Run downgrade -1. 5. Verify rollback. Use pytest fixtures to set up the DB.

Code/Syntax Justification:

def test_migration(applied_db):
# Check column exists
insp = inspect(applied_db)
cols = [c['name'] for c in insp.get_columns('users')]
assert 'email' in cols

🔍 Behind the Question: Migration CI/CD. ❌ The Red Flags: "Testing" by running it on the Staging DB once and assuming it works.

QN: How do you handle "Environment Variables" in Alembic env.py?​

Technical Answer: env.py is pure Python. You can use os.environ.get('DATABASE_URL'). However, Alembic doesn't automatically load .env files. You need to add from dotenv import load_dotenv; load_dotenv() at the top of env.py if you use .env.

Code/Syntax Justification:

import os
from dotenv import load_dotenv
load_dotenv()
url = os.getenv("DATABASE_URL")

🔍 Behind the Question: Configuration management. ❌ The Red Flags: Committing the DATABASE_URL with production credentials into env.py (security leak).

QN: What is "Multiple Head" issue and how to fix?​

Technical Answer: Happens when alembic_version has multiple rows (corrupted state) or when the migration files have multiple heads (branching). Fix: alembic heads to see heads. alembic merge heads to unify them. If DB is corrupted, restore from backup or manually fix the version table.

Code/Syntax Justification: alembic merge heads

🔍 Behind the Question: Repairing migration state. ❌ The Red Flags: Deleting the alembic_version table and running upgrade head (re-applies ALL migrations, which may fail on duplicate keys).

QN: How do you use op.create_sp or op.create_trigger in Alembic?​

Technical Answer: Alembic doesn't have built-in operations for Stored Procedures or Triggers. You must use op.execute() with raw SQL. Make sure to handle the downgrade by dropping them.

Code/Syntax Justification:

def upgrade():
op.execute("CREATE PROCEDURE ...")
def downgrade():
op.execute("DROP PROCEDURE ...")

🔍 Behind the Question: Database logic beyond standard DDL. ❌ The Red Flags: Writing a trigger in SQL but not including it in migrations (it exists in Dev but not Prod).

QN: Explain "Transaction "per" Migration" vs "Single Transaction".​

Technical Answer: By default, Alembic (for Postgres) runs the entire upgrade in one transaction. If it fails, it rolls back everything. You can turn this off with context.configure(transaction_per_migration=True) if you want each migration file to commit separately (rarely needed).

Code/Syntax Justification: In env.py: context.configure(connection=connection, transaction_per_migration=False)

🔍 Behind the Question: Advanced migration control. ❌ The Red Flags: Relying on partial commits during a migration chain (leads to inconsistent state).

Section 29: Elasticsearch Index Management at Scale​

QN: How do you handle "Zero-Downtime" index reconfiguration in Elasticsearch?​

Technical Answer: Use the Alias pattern. Create a new index (e.g., products_v2). Set up settings/mappings. Use Reindex API to copy data from products_v1 to products_v2. Once done, atomically swap the alias: POST /_aliases { "actions": [ {"remove": {"index": "products_v1", "alias": "products"}}, {"add": {"index": "products_v2", "alias": "products"}} ] }.

Code/Syntax Justification:

POST /_aliases
{
"actions": [
{ "remove": { "index": "v1", "alias": "prod" } },
{ "add": { "index": "v2", "alias": "prod" } }
]
}

🔍 Behind the Question: High-availability search infrastructure. ❌ The Red Flags: Deleting and recreating an index while users are searching (causes 404 errors).

QN: What is "Rollover" in Elasticsearch and when do you use it?​

Technical Answer: Rollover creates a new index when the current one meets conditions (e.g., max age 1d, max size 50GB). Used for time-series data (logs, metrics). The write alias points to the new index automatically. This prevents "Hot Spots" on a single giant index.

Code/Syntax Justification:

POST /logs-alias/_rollover
{
"conditions": { "max_age": "1d", "max_size": "50gb" }
}

🔍 Behind the Question: Managing large-scale log/event data. ❌ The Red Flags: Having a single index grow to Terabytes (slow recovery, slow shard movement).

QN: How do you manage "Index Lifecycle Management" (ILM) via API vs ORM?​

Technical Answer: ILM is an Elasticsearch feature (not an ORM feature). Define a Policy (e.g., Hot for 1 day, Warm for 7 days, Delete after 30). Apply it to an Index Template. The ORM (e.g., elasticsearch-dsl) just writes to the alias; ILM handles the rest automatically.

Code/Syntax Justification:

PUT _ilm/policy/logs_policy
{ "policy": { "phases": { "hot": { "actions": { "rollover": {...} } } } } }

🔍 Behind the Question: Automated operations. ❌ The Red Flags: Trying to implement lifecycle logic in Python cron jobs (ELK does it better natively).

QN: Explain "Dynamic Mapping" vs "Explicit Mapping" in Elasticsearch.​

Technical Answer:

  • Dynamic: ES guesses the type (string -> text/keyword, number -> long). Risky: "123" might become a string in one doc and a number in another.
  • Explicit: You define the mapping (PUT /my_index/_mapping). Safer, ensures consistency.

Code/Syntax Justification:

PUT /my_index
{
"mappings": {
"properties": {
"price": { "type": "scaled_float", "scaling_factor": 100 }
}
}
}

🔍 Behind the Question: Schema control in NoSQL. ❌ The Red Flags: Relying on Dynamic Mapping for production financial data.

QN: How do you handle "Parent/Child" relationships in Elasticsearch?​

Technical Answer: ES doesn't do SQL joins. Use Parent/Child (separate types in same index or separate indices with join field) or Denormalization (flatten the data). Parent/Child is slower because it has to look up the parent doc. Denormalization is faster but uses more space.

Code/Syntax Justification:

PUT /my_index
{
"mappings": {
"properties": {
"my_join_field": { "type": "join", "relations": { "parent": "child" } }
}
}
}

🔍 Behind the Question: Modeling relational data in a search engine. ❌ The Red Flags: Trying to do a SQL-style JOIN query in Elasticsearch (it doesn't exist).

QN: What is "Reindexing" and why is it slow?​

Technical Answer: POST /_reindex copies documents from source to dest index. It's slow because it's a scroll + bulk insert. Speed up: 1. Increase slices (parallelism). 2. Set refresh=false on dest index. 3. Increase requests_per_second (throttle control). 4. Use wait_for_completion=false for async.

Code/Syntax Justification:

POST /_reindex?slices=auto&refresh=false
{
"source": { "index": "old" },
"dest": { "index": "new" }
}

🔍 Behind the Question: Data migration in search. ❌ The Red Flags: Running a reindex on a production cluster during peak hours without throttling (kills cluster performance).

QN: How do you handle "Mapping Conflicts" (different types for same field)?​

Technical Answer: If ES detects a conflict (e.g., price is long in doc A and text in doc B), it throws an error (MapperParsingException). Fix: 1. Use Multi-fields (price as long, price_text as keyword). 2. Reindex the problematic documents after fixing the mapping.

Code/Syntax Justification:

"price": {
"type": "long",
"fields": { "str": { "type": "keyword" } }
}

🔍 Behind the Question: Data quality in search. ❌ The Red Flags: Ignoring the error and letting the document be dropped (silent data loss).

QN: Explain "Shard Sizing" for Elasticsearch indices.​

Technical Answer: A shard is a Lucene index. Aim for 10GB-50GB per shard. Too many small shards = overhead. Too large = slow recovery/snapshots. For an index expected to be 100GB, use 3-5 primary shards.

Code/Syntax Justification:

PUT /my_index
{
"settings": { "number_of_shards": 3, "number_of_replicas": 1 }
}

🔍 Behind the Question: Capacity planning for search. ❌ The Red Flags: Using 1 shard for a 1TB index (impossible to distribute or move efficiently).

QN: How do you use "Ingest Pipelines" for data transformation?​

Technical Answer: Pipelines process documents before indexing (like DB triggers). Use processors: set, rename, remove, date, grok. This moves transformation logic from your Python app to Elasticsearch.

Code/Syntax Justification:

PUT _ingest/pipeline/my_pipeline
{
"processors": [ { "set": { "field": "ingested_at", "value": "{{_ingest.timestamp}}" } } ]
}

🔍 Behind the Question: Centralizing data logic. ❌ The Red Flags: Doing complex string parsing in Python when grok or dissect in an Ingest Pipeline is designed for it.

QN: What is "Index Template" and how does it help?​

Technical Answer: Automatically applies settings/mappings to new indices matching a pattern (e.g., logs-*). When you create logs-2023-10-27, the template auto-configures it. This ensures consistency without manual setup every time.

Code/Syntax Justification:

PUT _index_template/logs_template
{
"index_patterns": ["logs-*"],
"template": { "settings": { "number_of_shards": 1 } }
}

🔍 Behind the Question: Automation and consistency. ❌ The Red Flags: Manually creating indices with PUT /logs-xyz and forgetting to apply the standard settings.

QN: How do you handle "Searchable Snapshots" in Elasticsearch?​

Technical Answer: In Elastic Cloud/Self-managed, you can mount a snapshot as a "Searchable Snapshot." It's stored in cheaper storage (S3). Cold data is searchable but slower. Good for historical data you rarely query.

Code/Syntax Justification:

POST /_snapshot/my_repo/my_snap/_mount?wait_for_completion=false
{
"index": "old_data", "renamed_index": "old_data_restored"
}

🔍 Behind the Question: Cost optimization for old data. ❌ The Red Flags: Keeping 5 years of logs on hot storage (expensive).

QN: Explain "Tiered Storage" in Elasticsearch (Hot/Warm/Cold).​

Technical Answer:

  • Hot: Fast SSDs, actively written to.
  • Warm: Slower disks, no new writes, searchable.
  • Cold: S3/Archive, very cheap, searchable via Searchable Snapshots. ILM moves indices between tiers automatically based on age/size.

Code/Syntax Justification: ILM Phase warm: {"actions": {"allocate": {"require": {"data": "warm"}}}

🔍 Behind the Question: Enterprise storage strategy. ❌ The Red Flags: Running everything on "Hot" nodes because "it's easier" (blows the budget).

QN: How do you use "Multi-fields" in Elasticsearch mappings?​

Technical Answer: A field can be indexed multiple ways. Example: title as text (for full-text search) and keyword (for aggregations/sorting). Defined in mapping as fields sub-object.

Code/Syntax Justification:

"title": {
"type": "text",
"fields": {
"keyword": { "type": "keyword", "ignore_above": 256 }
}
}

🔍 Behind the Question: Versatile querying. ❌ The Red Flags: Creating two separate fields (title_text and title_keyword) manually instead of using the built-in feature.

QN: What is "Force Merge" and when should you use it?​

Technical Answer: Force Merge reduces the number of segments in a shard (Lucene optimization). It's like defragmenting a disk. Use: On read-only indices (e.g., after rolling over). Don't use: On active indices (kills performance).

Code/Syntax Justification: POST /old_index/_forcemerge?max_num_segments=1

🔍 Behind the Question: Search performance tuning. ❌ The Red Flags: Running Force Merge on a hot, actively written index (causes massive I/O).

QN: How do you handle "Nested Objects" vs "Parent/Child"?​

Technical Answer:

  • Nested: Objects stored in the same Lucene block. Good for lists of objects that belong to the parent (e.g., user.comments). Queries are fast but updating one nested doc updates the whole parent.
  • Parent/Child: Separate docs. Updates are independent, but queries are slower (join).

Code/Syntax Justification:

"comments": { "type": "nested", "properties": { "text": { "type": "text" } } }

🔍 Behind the Question: Data structure choices in ES. ❌ The Red Flags: Using "Object" type (default) for arrays of objects (loses relationship between object fields during search).

QN: Explain "Translog" in Elasticsearch.​

Technical Answer: The Translog is a write-ahead log (WAL). Every write goes to the Translog first, then the in-memory buffer. If the node crashes, ES replays the Translog to recover. It's automatically trimmed (fsync'd) every 5 seconds by default.

Code/Syntax Justification: index.translog.sync_interval: 5s

🔍 Behind the Question: Durability and recovery. ❌ The Red Flags: Disabling the Translog to "improve performance" (data loss risk).

QN: How do you handle "Cluster Recovery" after a full restart?​

Technical Answer: ES uses the "Gateway" module. It reads the translog and shard states. The gateway.recover_after_nodes setting tells ES: "Wait until X nodes join before starting recovery." This prevents assigning shards to a node that just temporarily left.

Code/Syntax Justification: gateway.recover_after_nodes: 3

🔍 Behind the Question: Operational resilience. ❌ The Red Flags: Manually deleting shard data on disk to "fix" a recovery issue (corrupts data).

Section 30: ORM with Microservices​

QN: How do you handle "Database per Service" in microservices with an ORM?​

Technical Answer: Each microservice has its own database and its own ORM models. They do not share a database or models. Communication happens via API or Events. The ORM is strictly internal to the service. This ensures loose coupling.

Code/Syntax Justification: Service A (User DB) -> API -> Service B (Order DB). No direct DB link.

🔍 Behind the Question: Microservice architecture fundamentals. ❌ The Red Flags: Having two microservices share the same users database table (tight coupling).

QN: How do you handle "Distributed Transactions" (Saga) with an ORM?​

Technical Answer: An ORM transaction is local to one DB. For a Saga (e.g., Create Order + Reserve Inventory), use an orchestrator (Step Functions) or choreography (Events). The ORM does its part (e.g., session.commit() for the local order), then emits an event. If a later step fails, a compensating transaction (another ORM commit) undoes the local change.

Code/Syntax Justification:

# Step 1
with Session() as s:
s.add(Order(...))
s.commit()
event_bus.publish('OrderCreated', ...)

🔍 Behind the Question: Distributed consistency. ❌ The Red Flags: Using 2PC (Two-Phase Commit) across microservices (rarely supported and brittle).

QN: How do you share "Data Models" between microservices?​

Technical Answer: You don't share ORM models (tight coupling). Do: Share "Schema Definitions" (e.g., OpenAPI/Protobuf/JSON Schema). Each service implements its own ORM model that conforms to that schema.

Code/Syntax Justification:

# openapi.yaml (shared)
User:
type: object
properties:
id: { type: integer }

🔍 Behind the Question: Contract-first development. ❌ The Red Flags: Copy-pasting Python ORM model classes between Git repos (divergence bug).

QN: What is "CQRS" (Command Query Responsibility Segregation) with an ORM?​

Technical Answer: Split reads and writes. The "Command" side uses the ORM to write to the primary DB. The "Query" side reads from a denormalized view (maybe even a different DB type, like Elastic). The ORM might not even be used on the Query side (raw SQL or no DB at all).

Code/Syntax Justification: Write: session.add(Order) -> DB. Read: GET /orders -> Elastic Search (No ORM).

🔍 Behind the Question: Scalability patterns. ❌ The Red Flags: Implementing CQRS for a simple CRUD app (over-engineering).

QN: How do you handle "Event Sourcing" with an ORM?​

Technical Answer: Instead of storing the current state (User.name = "John"), you store a sequence of events (UserCreated, NameChanged). The ORM writes to an "Event Store" table. To get current state, you replay events. OR, you update a "Read Model" (separate table) based on events.

Code/Syntax Justification:

class Event(Base):
__tablename__ = 'events'
id = Column(Integer, primary_key=True)
type = Column(String)
payload = Column(JSON)

🔍 Behind the Question: Advanced persistence patterns. ❌ The Red Flags: Storing events but never building a read model, making queries incredibly slow.

QN: How do you handle "Database Migrations" in a microservice CI/CD pipeline?​

Technical Answer: Migrations should run automatically on deploy. Strategy: The service on startup runs alembic upgrade head. If it fails, the deploy fails (rollback). For zero-downtime, use the "Expand/Contract" pattern: Deploy code that supports both old and new schema, then migrate data, then deploy code that uses only new schema.

Code/Syntax Justification: Startup script: alembic upgrade head || exit 1

🔍 Behind the Question: Production deployment safety. ❌ The Red Flags: Running migrations manually via a developer's laptop on production.

QN: How do you handle "Service Discovery" for DB connections in microservices?​

Technical Answer: Don't hardcode DB hosts. Use a Service Registry (Consul) or Kubernetes DNS. The ORM connects to a DNS name (e.g., db.orders.svc.cluster.local). If the DB is RDS, use the RDS endpoint URL.

Code/Syntax Justification: DATABASE_URL = "postgresql://user:pw@db.orders.svc.cluster.local/orders"

🔍 Behind the Question: Dynamic infrastructure. ❌ The Red Flags: Hardcoding 10.0.1.5 as the DB IP in the config.

QN: How do you handle "Private Data" in a shared ORM model?​

Technical Answer: If a microservice exposes a User model via API, ensure the ORM query selects only public fields. Use session.query(User.id, User.public_name) instead of session.query(User).all(). Do not rely on JSON serialization to hide fields (it's error-prone).

Code/Syntax Justification:

# Good
result = session.execute(select(User.id, User.name))
# Bad
user = session.get(User, 1) # Loads everything including password_hash

🔍 Behind the Question: Security in APIs. ❌ The Red Flags: Returning user.__dict__ directly in an API response.

QN: Explain "Outbox Pattern" with an ORM.​

Technical Answer: To ensure an event is sent after a DB commit, write the event to an "Outbox" table in the same local transaction as the business data. A separate process (or DB trigger) reads the Outbox and publishes to the message broker. This guarantees atomicity.

Code/Syntax Justification:

with Session() as s:
s.add(Order(...))
s.add(Outbox(event_type='OrderCreated', payload=...))
s.commit() # Both saved atomically

🔍 Behind the Question: Reliable event publishing. ❌ The Red Flags: Publishing the event before committing the DB transaction (if commit fails, event is sent for nothing).

QN: How do you handle "Pagination" in microservice APIs using an ORM?​

Technical Answer: Use "Keyset Pagination" (cursor-based) instead of Offset for large datasets. WHERE id > :last_id ORDER BY id LIMIT 100. It's stable even if new rows are inserted. Offset (LIMIT 100 OFFSET 1000) gets slower as offset grows.

Code/Syntax Justification:

# Keyset
stmt = select(User).where(User.id > last_id).order_by(User.id).limit(100)

🔍 Behind the Question: API performance. ❌ The Red Flags: Using OFFSET 10000 in an API that lists "All Users" (kills the DB).

QN: How do you handle "Testing" microservices that use an ORM?​

Technical Answer: Use testcontainers to spin up a real PostgreSQL/MySQL in a Docker container for integration tests. Run migrations on it. This ensures your ORM queries work against the real DB engine, not just SQLite.

Code/Syntax Justification:

import testcontainers.postgres
with PostgresContainer() as pg:
url = pg.get_connection_url()
# Run tests

🔍 Behind the Question: Integration testing. ❌ The Red Flags: Only running unit tests with a mocked ORM (misses SQL syntax errors).

QN: How do you handle "Cross-Service Queries" without breaking microservice rules?​

Technical Answer: You don't. If Service A needs data from Service B's DB, Service A should call Service B's API. If you need to join data, use "API Composition" (Service A calls B, gets list, then queries its own DB) or "CQRS Read Model" (a separate DB that joins data asynchronously).

Code/Syntax Justification:

# API Composition
user_ids = requests.get('http://user-service/active').json()
orders = session.query(Order).filter(Order.user_id.in_(user_ids)).all()

🔍 Behind the Question: Data ownership. ❌ The Red Flags: Opening a direct DB connection from Service A to Service B's database.

QN: How do you handle "Database Authentication" in a microservice mesh?​

Technical Answer: Use Vault or AWS Secrets Manager to fetch DB credentials at runtime. For high security, use "Dynamic Secrets" where Vault creates a temporary DB user/password for the microservice. The ORM connects using these short-lived creds.

Code/Syntax Justification:

creds = vault_client.get_secret('db-creds')
engine = create_engine(f"postgresql://{creds['user']}:{creds['password']}@host/db")

🔍 Behind the Question: Security automation. ❌ The Red Flags: Baking DB passwords into the Docker image.

QN: How do you handle "Schema Evolution" when multiple microservices read the same topic?​

Technical Answer: Use Schema Registry (Confluent) or Protobuf. When you change the schema (add a field), ensure it's backward compatible. Consumers (other microservices) should ignore unknown fields or handle missing fields gracefully. The ORM models in the consumer should have optional fields.

Code/Syntax Justification:

class IncomingEvent(BaseModel):
id: int
new_field: Optional[str] = None # Backward compatible

🔍 Behind the Question: Compatibility in distributed systems. ❌ The Red Flags: Deploying a producer with a new field and crashing all consumers because they don't expect it.

QN: How do you implement "Bulkhead" pattern for ORM connections?​

Technical Answer: Isolate connection pools. If Service A hogs the DB connection limit, Service B shouldn't fail. Implementation: Use PgBouncer with transaction pooling, or give each service a dedicated RDS user with a CONNECTION LIMIT.

Code/Syntax Justification: ALTER USER service_a CONNECTION LIMIT 20;

🔍 Behind the Question: Fault isolation. ❌ The Red Flags: All microservices sharing the postgres superuser account.

QN: How do you handle "Database Migrations" when rolling back a microservice deployment?​

Technical Answer: Migrations should be reversible. alembic downgrade should work. For "Expand/Contract", you rollback the app code first (which stops using the new column), then rollback the migration. Never do a "Data Loss" migration (drop column) in the same release as code that uses it.

Code/Syntax Justification: Release 1: Add column (nullable). Release 2: Code uses column. Release 3: Drop old column.

🔍 Behind the Question: Safe rollbacks. ❌ The Red Flags: Running a migration that drops a column, then realizing the "old" version of the app (if you rollback) crashes because the column is gone.

Section 31: Database Transactions & Isolation Levels​

QN: Explain the four SQL Transaction Isolation Levels.​

Technical Answer:

  1. Read Uncommitted: Dirty reads possible (rarely used).
  2. Read Committed: Default for Postgres/Oracle. Prevents dirty reads.
  3. Repeatable Read: Prevents non-repeatable reads (Postgres implements this via Snapshot).
  4. Serializable: Full isolation, transactions act as if sequential. Highest safety, lowest concurrency.

Code/Syntax Justification:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

🔍 Behind the Question: Database theory fundamentals. ❌ The Red Flags: Using SERIALIZABLE for simple CRUD (performance killer).

QN: What is a "Dirty Read"?​

Technical Answer: Transaction A reads data that Transaction B has modified but not yet committed. If B rolls back, A has read data that never officially existed. Most modern DBs (Postgres) don't allow this even at Read Uncommitted.

Code/Syntax Justification: Postgres: Read Uncommitted actually behaves like Read Committed.

🔍 Behind the Question: Understanding data anomalies. ❌ The Red Flags: Designing logic that relies on reading uncommitted data.

QN: What is a "Non-Repeatable Read"?​

Technical Answer: Transaction A reads a row. Transaction B updates and commits that row. Transaction A reads the row again and sees different data. Fix: Use Repeatable Read or Serializable.

Code/Syntax Justification: In Repeatable Read, A's snapshot is fixed at the start of the transaction.

🔍 Behind the Question: Consistency within a transaction. ❌ The Red Flags: Assuming Read Committed prevents this (it doesn't).

QN: What is a "Phantom Read"?​

Technical Answer: Transaction A queries a set of rows (e.g., WHERE age > 18). Transaction B inserts a new row that matches the criteria. Transaction A runs the query again and sees a "phantom" row. Fix: Serializable isolation.

Code/Syntax Justification: Serializable uses predicate locks (locks the "range" of the WHERE clause).

🔍 Behind the Question: Advanced anomaly understanding. ❌ The Red Flags: Thinking Repeatable Read prevents phantoms in all databases (InnoDB does, but standard definition says it doesn't).

QN: How do you set Isolation Level in SQLAlchemy?​

Technical Answer: Use isolation_level parameter in create_engine or per-session. Note: SQLAlchemy passes this to the DBAPI, which passes it to the DB.

Code/Syntax Justification:

engine = create_engine("postgresql://...", isolation_level="REPEATABLE READ")

🔍 Behind the Question: Configuring the ORM. ❌ The Red Flags: Setting it in the ORM but the DB driver ignores it (check driver support).

QN: What is "SELECT FOR UPDATE" and when do you use it?​

Technical Answer: It locks the selected rows against updates by other transactions until your transaction commits. Used in "Check then Update" scenarios (e.g., "Check inventory count, then decrement"). Without it, two requests might both read "10" and both decrement to "9" (race condition).

Code/Syntax Justification:

stmt = select(Inventory).where(Inventory.id==1).with_for_update()
item = session.execute(stmt).scalar_one()
item.quantity -= 1

🔍 Behind the Question: Preventing race conditions. ❌ The Red Flags: Using SELECT FOR UPDATE inside a long transaction (holds lock for too long).

QN: Explain "Deadlock" vs "Livelock".​

Technical Answer:

  • Deadlock: T1 holds Lock A, wants B. T2 holds Lock B, wants A. Both wait forever. DB detects this and kills one.
  • Livelock: Transactions keep retrying but always conflict due to logic (e.g., two processes trying to update the same counter with a "retry" loop that never succeeds because they keep stepping on each other).

🔍 Behind the Question: Concurrency theory. ❌ The Red Flags: Infinite retry loops without exponential backoff.

QN: How do you handle "Long-Running Transactions"?​

Technical Answer: They hold locks and prevent VACUUM (in Postgres), causing table bloat. Fix: Break the work into smaller transactions. If you must do a batch update of 1M rows, do it in chunks of 1000 with commits in between.

Code/Syntax Justification:

for chunk in chunks:
with Session() as s:
s.execute(update_stmt)
s.commit()

🔍 Behind the Question: Database maintenance health. ❌ The Red Flags: BEGIN; UPDATE huge_table; -- go to lunch -- COMMIT;

QN: What is "Two-Phase Commit" (2PC)?​

Technical Answer: A protocol to ensure atomicity across two resource managers (e.g., Database A and Database B). Phase 1: "Prepare" (both say "Ready"). Phase 2: "Commit" (both commit). If one fails prepare, both rollback. Rarely used in modern microservices (prefer Sagas).

Code/Syntax Justification: XA Transactions in enterprise databases.

🔍 Behind the Question: Distributed transaction protocols. ❌ The Red Flags: Implementing 2PC over HTTP (it's not designed for that).

QN: How does "MVCC" (Multi-Version Concurrency Control) work in Postgres?​

Technical Answer: Instead of locking rows for reads, Postgres keeps multiple versions of a row (via xmin/xmax system columns). Readers see a "snapshot" of the data as of the start of their transaction. Writers don't block readers, and vice versa.

Code/Syntax Justification: SELECT * FROM orders; sees the snapshot state.

🔍 Behind the Question: Modern database internals. ❌ The Red Flags: Assuming SELECT locks the table (it doesn't in MVCC).

QN: What is "Write Skew" anomaly?​

Technical Answer: Two transactions read the same data, make decisions based on it, and write back. The result is inconsistent. Example: Two doctors both see "On-call" count is 2, so both decide to go off-call, leaving 0 doctors. Serializable prevents this.

Code/Syntax Justification: Serializable isolation prevents this by detecting the "skew."

🔍 Behind the Question: Complex isolation anomalies. ❌ The Red Flags: Thinking Repeatable Read prevents Write Skew (it doesn't in standard SQL, though Postgres does prevent it via SIRead locks).

QN: How do you use "SAVEPOINT" in SQLAlchemy?​

Technical Answer: SAVEPOINT creates a checkpoint within a transaction. You can rollback to it without killing the whole transaction. In SQLAlchemy: session.begin_nested().

Code/Syntax Justification:

session.begin()
try:
session.begin_nested() # Savepoint
# do risky stuff
raise Exception
except:
session.rollback() # Rolls back to savepoint only
session.commit() # Commit still happens for other work

🔍 Behind the Question: Partial rollbacks. ❌ The Red Flags: Using savepoints in a loop without understanding that they accumulate (consume memory).

QN: What is "Optimistic Locking" vs "Pessimistic Locking"?​

Technical Answer:

  • Pessimistic: Lock the row (SELECT FOR UPDATE) immediately. Safe, but holds DB resources.
  • Optimistic: Add a version column. Read row (version=1). Update sets version = 2 WHERE version = 1. If rows updated = 0, someone else changed it.

Code/Syntax Justification:

# Optimistic
rows = session.execute(update(User).where(User.id==1, User.version==1).values(version=2))
if rows.rowcount == 0: raise ConflictError

🔍 Behind the Question: Concurrency control strategies. ❌ The Red Flags: Using Optimistic locking for high-contention resources (causes many retries).

QN: How do you handle "Idle in Transaction" sessions?​

Technical Answer: A session started a transaction but didn't commit/rollback, and the app is idle. This holds locks and prevents VACUUM. Fix: Set idle_in_transaction_session_timeout in Postgres to auto-kill these sessions.

Code/Syntax Justification: SET idle_in_transaction_session_timeout = '5min';

🔍 Behind the Question: Database connection hygiene. ❌ The Red Flags: Not monitoring pg_stat_activity for "idle in transaction" (they pile up).

QN: Explain "Statement Timeout" vs "Transaction Timeout".​

Technical Answer:

  • Statement Timeout: A single query takes too long (e.g., bad plan). Kills just that query.
  • Transaction Timeout: The whole transaction takes too long. Kills the transaction. Useful for batch jobs.

Code/Syntax Justification: SET statement_timeout = '10s';

🔍 Behind the Question: Resource guards. ❌ The Red Flags: Setting a 1-second statement timeout for a complex analytical query.

QN: What is "Read-Only Transactions"?​

Technical Answer: A transaction where you explicitly state you won't write. BEGIN READ ONLY;. This allows the DB to optimize (e.g., no need to assign a transaction ID in Postgres). Also prevents accidental writes.

Code/Syntax Justification:

with engine.connect().execution_options(isolation_level="READ COMMITTED", read_only=True) as conn:
result = conn.execute(select(...))

🔍 Behind the Question: Intent signaling to the database. ❌ The Red Flags: Starting a transaction, doing 50 SELECTs, and then realizing you forgot to commit (hanging transaction).

QN: How do you debug "Lock Waits" in PostgreSQL?​

Technical Answer: Query pg_locks joined with pg_stat_activity. Look for granted = false (waiting) and granted = true (holding). The pid tells you which process is blocking.

Code/Syntax Justification:

SELECT pid, state, query, wait_event_type, wait_event
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

🔍 Behind the Question: Operational debugging. ❌ The Red Flags: Killing the "Waiting" PID without identifying the "Blocking" PID (the real culprit).

Section 32: Advanced Query Optimization & EXPLAIN​

QN: How do you read a PostgreSQL EXPLAIN ANALYZE output?​

Technical Answer: EXPLAIN shows the plan. EXPLAIN ANALYZE runs it and shows actual times. Look for: 1. Seq Scan (full table scan - bad for large tables). 2. Index Scan (good). 3. Bitmap Heap Scan (often good). 4. Cost (estimated). 5. Actual Time (real). High "Actual Time" means slow.

Code/Syntax Justification:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'a@b.com';

🔍 Behind the Question: Performance debugging. ❌ The Red Flags: Looking only at "Cost" and ignoring "Actual Time" (Cost is an estimate).

QN: What is a "Bitmap Heap Scan"?​

Technical Answer: PostgreSQL uses this when multiple conditions might use different indexes (e.g., WHERE age > 20 AND city = 'NY'). It creates a "bitmap" of page numbers that match, then visits the heap (table) to fetch rows. More efficient than multiple random I/Os.

Code/Syntax Justification: Bitmap Heap Scan on users (cost=...) Recheck Cond: (age > 20)...

🔍 Behind the Question: Understanding complex query plans. ❌ The Red Flags: Thinking "Heap Scan" is always bad (Bitmap Heap Scan is optimized).

QN: Explain "Index Only Scan" vs "Index Scan".​

Technical Answer:

  • Index Scan: Reads the index, finds the row pointer (TID), then goes to the table heap to fetch the row data.
  • Index Only Scan: The index contains all the data needed (e.g., a composite index on (email, id) and you query SELECT id FROM users WHERE email = 'a@b.com'). It never touches the heap. Much faster.

Code/Syntax Justification: Create index: CREATE INDEX ON users (email) INCLUDE (id); allows Index Only Scan for SELECT id.

🔍 Behind the Question: Advanced indexing. ❌ The Red Flags: Creating a massive index that includes every column (defeats the purpose of a lean index).

QN: What is "Nested Loop" vs "Hash Join" vs "Merge Join"?​

Technical Answer:

  • Nested Loop: For every row in A, scan B. Good for small A, indexed B.
  • Hash Join: Build a hash table of B, then probe with A. Good for large joins, no index needed.
  • Merge Join: Sort both A and B, then merge. Good if both are already sorted (e.g., by index).

🔍 Behind the Question: Join algorithms. ❌ The Red Flags: Expecting a Hash Join on a tiny table (Nested Loop is usually faster for small data).

QN: How do you optimize a "Slow Count(*)" query?​

Technical Answer: SELECT COUNT(*) usually requires a Seq Scan. Fix: 1. If approximate is ok, use pg_class.reltuples. 2. If exact is needed, maintain a counter table (increment/decrement triggers). 3. Use indexed column COUNT(id) (might use Index Only Scan if id is primary key).

Code/Syntax Justification:

-- Approx
SELECT reltuples FROM pg_class WHERE relname = 'users';

🔍 Behind the Question: Aggregation performance. ❌ The Red Flags: Running SELECT COUNT(*) on a 100M row table in a web request.

QN: What is "Correlated Subquery" and why is it slow?​

Technical Answer: A subquery that references the outer query (e.g., SELECT * FROM orders WHERE amount > (SELECT AVG(amount) FROM orders o2 WHERE o2.user_id = orders.user_id)). The inner query runs for every row in the outer query. Fix: Rewrite as a JOIN with a derived table.

Code/Syntax Justification: Rewrite using WITH avg_per_user AS (...) SELECT ... JOIN avg_per_user ...

🔍 Behind the Question: Query refactoring. ❌ The Red Flags: Writing a correlated subquery in a loop (N+1 in SQL).

QN: How do you use "Partial Indexes"?​

Technical Answer: An index with a WHERE clause. It indexes only rows matching the condition. Smaller, faster to update, and faster to query for that specific condition. Example: Index WHERE deleted_at IS NULL.

Code/Syntax Justification: CREATE INDEX idx_active_users ON users (email) WHERE deleted_at IS NULL;

🔍 Behind the Question: Targeted optimization. ❌ The Red Flags: Creating a full index on a deleted_at column (most rows are NULL, waste of space).

QN: Explain "Expression Index" (Functional Index).​

Technical Answer: Index the result of a function or expression, not just a column. Example: CREATE INDEX ON users (lower(email)). Now WHERE lower(email) = 'test' uses the index. Without it, the function is applied to every row (Seq Scan).

Code/Syntax Justification:

CREATE INDEX idx_email_lower ON users (lower(email));

🔍 Behind the Question: Indexing computed data. ❌ The Red Flags: Querying WHERE lower(email) = 'X' when the index is on email (won't use index).

QN: What is "Table Bloat" and how do you fix it?​

Technical Answer: In Postgres, UPDATEs and DELETEs don't immediately free space; they create "dead tuples." Over time, the table file grows (bloat). Fix: Run VACUUM (or VACUUM FULL for aggressive reclaim, but it locks the table).

Code/Syntax Justification: VACUUM (VERBOSE, ANALYZE) my_table;

🔍 Behind the Question: Database maintenance. ❌ The Red Flags: Never running VACUUM and wondering why the 1GB table with 100 rows is still 1GB.

QN: How do you analyze "Query Performance" over time?​

Technical Answer: Use pg_stat_statements extension. It tracks total time, calls, rows returned for every normalized query. Sort by total_exec_time to find your worst queries.

Code/Syntax Justification:

SELECT query, total_exec_time, calls
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;

🔍 Behind the Question: Proactive monitoring. ❌ The Red Flags: Only looking at performance when a user complains.

QN: What is "Cardinality Estimation" error?​

Technical Answer: The Postgres planner estimates how many rows a step will return. If it's wrong (e.g., thinks 10 rows, actually 10,000), it might choose a Nested Loop when a Hash Join was better. Fix: Run ANALYZE to update statistics, or increase default_statistics_target.

Code/Syntax Justification: ANALYZE users; updates the stats for the planner.

🔍 Behind the Question: Planner tuning. ❌ The Red Flags: Disabling statistics updates because "they take too long" (leads to bad plans).

QN: How do you optimize "LIKE '%keyword%'" queries?​

Technical Answer: Standard B-Tree indexes don't help with leading wildcards. Fix: 1. Use PostgreSQL pg_trgm extension (Trigram index). 2. Use Full-Text Search (TSVECTOR) instead of LIKE. 3. Use ElasticSearch if search is heavy.

Code/Syntax Justification:

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_on_name ON users USING gin (name gin_trgm_ops);

🔍 Behind the Question: Text search optimization. ❌ The Red Flags: Using LIKE '%name%' on a 10M row table expecting it to be fast.

QN: Explain "CTE" (Common Table Expression) and Materialization.​

Technical Answer: WITH clause. In older Postgres, CTEs were always "materialized" (executed and stored in memory). In modern Postgres (12+), they are inlined (become part of the main query) unless you use MATERIALIZED. Materialization can be good if you want to force a specific execution order.

Code/Syntax Justification:

WITH cte AS MATERIALIZED (SELECT * FROM big_table)
SELECT * FROM cte JOIN small_table ...

🔍 Behind the Question: Controlling execution plans. ❌ The Red Flags: Assuming CTEs are "free" subqueries (they might be materialized and use temp space).

QN: What is "Foreign Data Wrapper" (FDW) performance impact?​

Technical Answer: FDW (like postgres_fdw) allows querying a remote DB as if it were local. Performance is usually bad because filtering/predicate pushdown might not work perfectly. Every row might be fetched locally then filtered. Fix: Use Materialized Views to cache remote data locally.

Code/Syntax Justification: CREATE FOREIGN TABLE remote_users ... SERVER remote_db;

🔍 Behind the Question: Distributed query performance. ❌ The Red Flags: Joining a local table with a remote FDW table in a hot query path.

QN: How do you handle "Parameter Sniffing" in Postgres?​

Technical Answer: Postgres doesn't have "Parameter Sniffing" exactly like SQL Server, but it does generic planning for prepared statements. If your data distribution is skewed (10 rows for ID 1, 1M for ID 2), a generic plan might be bad for one case. Fix: Use "Custom Plans" (don't prepare the statement) or SET plan_cache_mode = force_custom_plan.

Code/Syntax Justification: SET plan_cache_mode = force_custom_plan;

🔍 Behind the Question: Plan stability. ❌ The Red Flags: Preparing a statement and reusing it for widely different parameters.

QN: Explain "Parallel Query" in PostgreSQL.​

Technical Answer: Postgres can use multiple CPU cores for one query (since v9.6). It splits the work (e.g., Seq Scan) across workers. Limited by max_parallel_workers. Not all operations are parallelizable (e.g., CTEs, some joins).

Code/Syntax Justification: SET max_parallel_workers_per_gather = 4;

🔍 Behind the Question: Hardware utilization. ❌ The Red Flags: Expecting a simple indexed lookup to go parallel (it won't, it's too fast for that).

QN: How do you optimize "Pagination" past high offsets?​

Technical Answer: LIMIT 10 OFFSET 10000 is slow because it scans and discards 10,000 rows. Fix: Use "Keyset Pagination" (WHERE id > 10000 LIMIT 10). The DB uses the index to jump directly to 10000.

Code/Syntax Justification:

SELECT * FROM users WHERE id > 10000 ORDER BY id LIMIT 10;

🔍 Behind the Question: Efficient data retrieval. ❌ The Red Flags: Implementing "Infinite Scroll" with OFFSET on a table that grows rapidly.

Section 33: ORM Security (SQL Injection, Mass Assignment)​

QN: How does SQLAlchemy prevent SQL Injection?​

Technical Answer: By using parameterized queries. When you use select(User).where(User.id == input_id), SQLAlchemy passes input_id as a bound parameter to the DB driver, not as a string literal. The DB treats it as data, not code.

Code/Syntax Justification:

# SAFE (parameterized)
stmt = select(User).where(User.id == user_input)
# UNSAFE (NEVER DO THIS)
stmt = text(f"SELECT * FROM users WHERE id = {user_input}")

🔍 Behind the Question: Fundamental security. ❌ The Red Flags: Using text() with f-strings.

QN: What is "Mass Assignment" vulnerability in ORMs?​

Technical Answer: When a web framework passes user input directly to an ORM create/update method. Example: User.create(request.json()). If request.json includes is_admin: true, the user becomes an admin. Fix: Use "Allow-lists" (only update specific fields).

Code/Syntax Justification:

# UNSAFE
User.update(**request.json())
# SAFE
User.update(name=request.json()['name'], email=request.json()['email'])

🔍 Behind the Question: Web security basics. ❌ The Red Flags: Using **kwargs updates with raw user input in an ORM.

QN: How do you handle "Sensitive Data" (PII) in ORM models?​

Technical Answer: 1. Encrypt at rest (column encryption). 2. Minimize exposure: don't SELECT *. 3. Use access controls: ensure the ORM query filters by tenant_id or user_id. 4. Don't log the objects (PII in logs).

Code/Syntax Justification:

# Avoid
logger.info(f"User: {user}")
# Instead
logger.info(f"User ID: {user.id}")

🔍 Behind the Question: Data privacy (GDPR/CCPA). ❌ The Red Flags: Logging user.email or user.ssn to standard out.

QN: What is "Second-Order SQL Injection"?​

Technical Answer: Data that was previously inserted (safely) is now used in a dangerous way. Example: Username is "admin' OR 1=1 --". It was inserted safely. But later, code does text(f"SELECT * FROM logs WHERE user = '{user.name}'"). The injection happens on retrieval.

Code/Syntax Justification: Always use parameterized queries everywhere, even for data read from DB.

🔍 Behind the Question: Advanced injection vectors. ❌ The Red Flags: Thinking "I sanitized it on the way in, so it's safe on the way out."

QN: How do you prevent "IDOR" (Insecure Direct Object Reference) in ORM queries?​

Technical Answer: If a user requests /api/orders/123, verify that Order 123 belongs to that user. Don't just do Order.get(123). Do Order.query.filter_by(id=123, user_id=current_user.id).first().

Code/Syntax Justification:

# UNSAFE
order = session.get(Order, order_id)
# SAFE
order = session.execute(select(Order).where(Order.id==order_id, Order.user_id==current_user.id)).scalar_one_or_none()

🔍 Behind the Question: Authorization logic. ❌ The Red Flags: Checking permissions after fetching the object (too late, data is in memory).

QN: How do you handle "CORS" and "CSRF" in an ORM-backed API?​

Technical Answer: ORM is backend, but the API must handle these. CORS: Allow specific origins. CSRF: Use SameSite cookies or CSRF tokens. The ORM shouldn't be affected, but be careful with cookie-based auth sessions that the ORM might be used to store.

🔍 Behind the Question: Web security context. ❌ The Red Flags: Disabling CORS entirely for development and forgetting to re-enable it for production.

QN: What is "Least Privilege" for ORM Database Users?​

Technical Answer: The ORM connects with a DB user that has only the permissions it needs. E.g., a read-only reporting service should connect with a user that has SELECT only, not INSERT/UPDATE/DELETE.

Code/Syntax Justification:

CREATE USER reporter WITH PASSWORD '...';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporter;

🔍 Behind the Question: Defense in depth. ❌ The Red Flags: Connecting to the DB as the postgres superuser in production.

QN: How do you handle "Password Hashing" in ORM models?​

Technical Answer: Never store plain text passwords. Use bcrypt or argon2. In the ORM model, use a hybrid_property or a custom TypeDecorator that hashes on set and compares on check.

Code/Syntax Justification:

from werkzeug.security import generate_password_hash, check_password_hash
class User(Base):
_password = Column('password', String)
@property
def password(self): raise AttributeError('password is write-only')
@password.setter
def password(self, pw):
self._password = generate_password_hash(pw)

🔍 Behind the Question: Authentication security. ❌ The Red Flags: password = Column(String) without hashing.

QN: What is "Insecure Deserialization" in ORM contexts?​

Technical Answer: If you store Pickled Python objects in a JSON/Text column and load them back (pickle.loads), a malicious user who can write to that column can execute arbitrary code on your server. Fix: Use JSON (safe) or explicit serialization formats.

Code/Syntax Justification: Use Column(JSON) rather than Column(PickleType).

🔍 Behind the Question: Deserialization attacks. ❌ The Red Flags: Using PickleType for data that comes from external sources.

QN: How do you prevent "Race Conditions" in balance updates?​

Technical Answer: Use SELECT FOR UPDATE (pessimistic) or UPDATE ... RETURNING with a version check (optimistic). Simply reading the balance, calculating new balance in Python, and updating is a race condition.

Code/Syntax Justification:

# Atomic update
session.execute(update(Account).where(Account.id==1).values(balance=Account.balance - 100))

🔍 Behind the Question: Financial transaction safety. ❌ The Red Flags: balance = user.balance - 100; user.balance = balance (two requests = wrong math).

QN: What is "Log Injection" via ORM?​

Technical Answer: If you log ORM objects and a user manages to put a malicious string in the DB (e.g., newline + "FAKE LOG ENTRY"), they can corrupt your logs. Fix: Sanitize log output or use structured logging (JSON) which escapes strings.

🔍 Behind the Question: Logging security. ❌ The Red Flags: logger.info(f"User {user.name} logged in") where user.name is "\n[SECURITY BYPASS]" (pollutes logs).

QN: How do you handle "Soft Delete" visibility in ORM security?​

Technical Answer: If you implement soft delete, ensure every query includes where(deleted_at == None). If you forget, admins might see "deleted" users in the admin panel. Use a global filter (see Multi-tenancy section).

🔍 Behind the Question: Data lifecycle security. ❌ The Red Flags: Having a "Deleted Users" admin page that actually shows all users because the filter is missing.

QN: What is "Session Hijacking" prevention in ORM-served sessions?​

Technical Answer: If you use server-side sessions (stored via ORM in DB), ensure the Session ID is random, long, and the cookie is HttpOnly and Secure. Regenerate Session ID on login.

🔍 Behind the Question: Session management. ❌ The Red Flags: Using sequential session IDs (easy to guess).

QN: How do you handle "API Rate Limiting" with ORM quotas?​

Technical Answer: Not strictly ORM, but the ORM might track API usage User.api_calls_today. Before processing, check if user.api_calls_today >= LIMIT: raise RateLimit. Reset daily via a cron job updating the ORM.

🔍 Behind the Question: Resource protection. ❌ The Red Flags: Rate limiting only on the API gateway but not in the DB (user can bypass gateway).

QN: What is "Padding Oracle" or "Cryptographic" issues in ORM?​

Technical Answer: If you implement encryption in the ORM (see Custom Types), ensure you use a strong algorithm (AES-256-GCM) and a unique IV (Initialization Vector) per row. Reusing IVs leaks data patterns.

🔍 Behind the Question: Cryptography basics. ❌ The Red Flags: Using ECB (Electronic Codebook) mode for encryption (patterns remain visible).

QN: How do you handle "GDPR Right to Erasure" in ORM?​

Technical Answer: User requests deletion. If using Soft Delete, you must actually hard delete or anonymize. ORM makes this easy: session.delete(user) (hard) or user.email = 'anon@...'. Ensure cascading deletes (or manual cleanup) remove related data.

🔍 Behind the Question: Legal compliance. ❌ The Red Flags: Just setting is_deleted=True and thinking you've complied with GDPR (the data is still there).

QN: What is "SSRF" (Server-Side Request Forgery) via ORM URL fields?​

Technical Answer: If your ORM model has a website_url field and your backend fetches that URL (e.g., to grab a favicon), a user might input http://169.254.169.254/latest/meta-data/ (AWS credentials). Fix: Validate URLs, restrict to HTTP/HTTPS, block private IP ranges.

🔍 Behind the Question: Network security. ❌ The Red Flags: Fetching user-provided URLs without any validation.

Section 34: PostgreSQL Specific ORM Patterns​

QN: How do you use "JSONB" effectively in PostgreSQL with SQLAlchemy?​

Technical Answer: Use Column(JSONB). You can query nested keys: User.data['preferences']['theme'].astext == 'dark'. You can create GIN indexes on JSONB for fast lookups. It's great for flexible schemas (e.g., user settings) without altering tables.

Code/Syntax Justification:

from sqlalchemy import JSONB
class User(Base):
settings = Column(JSONB)
# Query
stmt = select(User).where(User.settings['theme'].astext == 'dark')

🔍 Behind the Question: Modern PostgreSQL features. ❌ The Red Flags: Using JSONB for data that is strictly structured and relational (use tables/columns).

QN: How do you use "Array" columns in PostgreSQL?​

Technical Answer: Use Column(ARRAY(...)). Great for tags, permissions, or simple lists. You can query if array contains an element: WHERE tags @> ARRAY['python'].

Code/Syntax Justification:

from sqlalchemy.dialects.postgresql import ARRAY
class Article(Base):
tags = Column(ARRAY(String))
# Query
stmt = select(Article).where(Article.tags.contains(['python']))

🔍 Behind the Question: PostgreSQL specific types. ❌ The Red Flags: Using a comma-separated string for tags instead of an Array (can't index efficiently).

QN: What is "Full Text Search" (TSVECTOR) in PostgreSQL?​

Technical Answer: PostgreSQL has built-in FTS. Create a TSVECTOR column (or use a generated column). Update it with to_tsvector('english', title || ' ' || body). Query with @@ to_tsquery('english', 'python & sqlalchemy').

Code/Syntax Justification:

from sqlalchemy.dialects.postgresql import TSVECTOR
class Doc(Base):
content = Column(Text)
tsv = Column(TSVECTOR)
# Update trigger or manual
update(Doc).values(tsv=func.to_tsvector('english', Doc.content))

🔍 Behind the Question: Search without Elastic. ❌ The Red Flags: Using LIKE '%python%' for search on a large table.

QN: How do you use "Materialized Views" in SQLAlchemy?​

Technical Answer: A Materialized View is a cached query result stored on disk. Refresh with REFRESH MATERIALIZED VIEW. Good for complex reports. In SQLAlchemy, you define it as a reflection or a custom construct. You query it like a table.

Code/Syntax Justification:

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT date_trunc('month', created_at) as month, SUM(amount)
FROM orders GROUP BY month;

Query in ORM: select(MonthlySales).where(...).

🔍 Behind the Question: Performance optimization for reads. ❌ The Red Flags: Refreshing the MV too often (defeats the purpose of caching).

QN: Explain "CTE" usage in SQLAlchemy ORM.​

Technical Answer: Use select(...).cte(). It creates a "WITH" clause. Good for breaking complex queries into named steps. You can reference the CTE in subsequent parts of the query.

Code/Syntax Justification:

cte = select(User.id, User.name).where(User.active==True).cte('active_users')
stmt = select(cte).join(Order, Order.user_id==cte.c.id)

🔍 Behind the Question: Advanced query composition. ❌ The Red Flags: Using CTEs for simple queries (unnecessary complexity).

QN: How do you handle "UUID" primary keys in SQLAlchemy?​

Technical Answer: Use Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4). UUIDs are good for distributed systems (no ID collision). They are larger than integers (16 bytes vs 4 bytes) and can fragment indexes if generated randomly.

Code/Syntax Justification:

import uuid
from sqlalchemy.dialects.postgresql import UUID
id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)

🔍 Behind the Question: Distributed ID generation. ❌ The Red Flags: Using random UUIDs for high-write tables (index fragmentation; use time-ordered UUIDs like uuid7).

QN: What is "Table Partitioning" in PostgreSQL and how to ORM it?​

Technical Answer: Split a large table into smaller chunks (partitions) by a key (e.g., date). In SQLAlchemy, you mostly interact with the parent table. The ORM doesn't need to know about partitions; Postgres handles routing. You define partitions in raw SQL (DDL).

Code/Syntax Justification:

CREATE TABLE orders (id serial, created_at date NOT NULL)
PARTITION BY RANGE (created_at);
CREATE TABLE orders_2023 PARTITION OF orders FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

🔍 Behind the Question: Managing huge tables. ❌ The Red Flags: Trying to manage partitions manually in the ORM (let the DB do it).

QN: How do you use "Enums" in PostgreSQL via SQLAlchemy?​

Technical Answer: Postgres has native Enums (DB-level). Use sqlalchemy.dialects.postgresql.ENUM. It creates a type in the DB. This is stricter than Python Enums (which store strings). Downside: Altering an enum is harder (requires DB migration).

Code/Syntax Justification:

from sqlalchemy.dialects.postgresql import ENUM
user_status = ENUM('active', 'inactive', name='user_status')
class User(Base):
status = Column(user_status)

🔍 Behind the Question: Type safety in Postgres. ❌ The Red Flags: Using a generic String column for status and relying on code comments to define valid values.

QN: What is "Generated Columns" (Stored/Virtual) in Postgres?​

Technical Answer: Columns computed from other columns. GENERATED ALWAYS AS (...) STORED. Stored means written to disk. SQLAlchemy can reflect these. They are read-only in the ORM (you can't insert into them).

Code/Syntax Justification:

ALTER TABLE products ADD COLUMN price_with_tax numeric
GENERATED ALWAYS AS (price * 1.2) STORED;

🔍 Behind the Question: Database-side computed data. ❌ The Red Flags: Computing price_with_tax in Python and saving it (it might get out of sync with price).

QN: How do you use "Triggers" with ORM patterns?​

Technical Answer: Triggers are DB-side logic (e.g., "Update updated_at on every update"). SQLAlchemy doesn't create them automatically (unless you use specific libraries). You define them in migrations (op.execute("CREATE TRIGGER...")). The ORM just sees the effect (e.g., updated_at changes).

Code/Syntax Justification:

# Migration
op.execute("""
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql;
CREATE TRIGGER set_updated_at BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_updated_at();
""")

🔍 Behind the Question: Database logic. ❌ The Red Flags: Relying on the ORM onupdate=func.now() for critical logic when a direct SQL update can bypass the ORM.

QN: Explain "PostGIS" integration with SQLAlchemy.​

Technical Answer: PostGIS adds geographic objects to Postgres. In SQLAlchemy, use geoalchemy2 extension. Columns are Geometry('POINT', srid=4326). You can query: session.query(Place).filter(Place.geom.ST_DWithin(center, radius)).

Code/Syntax Justification:

from geoalchemy2 import Geometry
class Place(Base):
location = Column(Geometry('POINT', srid=4326))

🔍 Behind the Question: Spatial data. ❌ The Red Flags: Storing Lat/Lng as two separate floats and calculating distance in Python (slow and inaccurate).

QN: How do you handle "Listen/Notify" in PostgreSQL with ORM?​

Technical Answer: Postgres can send notifications (NOTIFY channel, 'payload'). Python/SQLAlchemy can listen using raw DBAPI connection (conn.connection). This is useful for real-time triggers to the app without polling.

Code/Syntax Justification:

# Listen
conn = engine.raw_connection()
conn.connection.execute("LISTEN my_channel")
# In a loop
select.select([conn.connection], [], [], 5)
conn.connection.poll() # Checks for notifications

🔍 Behind the Question: Real-time DB events. ❌ The Red Flags: Polling a table every second to check for new rows (use Notify instead).

QN: What is "Unlogged Tables" and when to use them?​

Technical Answer: CREATE UNLOGGED TABLE skips the Write-Ahead Log (WAL). Faster writes, but data is lost on crash. Good for temporary data, caches, or sessions. SQLAlchemy: Table('...', metadata, ..., postgresql_unlogged=True).

Code/Syntax Justification:

Table('cache', metadata, Column('key', String), postgresql_unlogged=True)

🔍 Behind the Question: Performance vs durability trade-off. ❌ The Red Flags: Using Unlogged tables for critical business data (lost on crash).

QN: How do you use "hstore" vs "JSONB"?​

Technical Answer: hstore is an older key-value store (plain text key=>value). JSONB is binary JSON, supports indexing, nested structures, and more operators. Recommendation: Use JSONB unless you have a legacy system using hstore.

Code/Syntax Justification:

from sqlalchemy.dialects.postgresql import HSTORE
# Generally, prefer JSONB

🔍 Behind the Question: Choosing the right type. ❌ The Red Flags: Using hstore for complex nested data (it's flat).

QN: Explain "Recursive CTE" (Hierarchical Data) in Postgres.​

Technical Answer: Use WITH RECURSIVE to query trees (e.g., Employee -> Manager). SQLAlchemy supports this via select().cte(recursive=True).

Code/Syntax Justification:

emp_cte = select(Employee.id, Employee.manager_id).cte(name='subtree', recursive=True)
subq = emp_cte.union(select(Employee.id, Employee.manager_id).join(emp_cte, Employee.id==emp_cte.c.manager_id))

🔍 Behind the Question: Tree structures in SQL. ❌ The Red Flags: Fetching the whole table and building the tree in Python (performance nightmare).

QN: How do you handle "COPY" command in SQLAlchemy for bulk loading?​

Technical Answer: COPY is Postgres's fast bulk loader. SQLAlchemy doesn't have a native API for it, but you can use raw_connection() and the psycopg2.copy_expert method. Much faster than insert() for millions of rows.

Code/Syntax Justification:

with engine.raw_connection() as conn:
cur = conn.cursor()
with open('data.csv') as f:
cur.copy_expert("COPY my_table FROM STDIN WITH CSV", f)

🔍 Behind the Question: High-speed data loading. ❌ The Red Flags: Looping session.add() for 1 million rows (takes hours).

Section 35: Advanced PostgreSQL Performance​

QN: How do you use "Covering Indexes" in PostgreSQL?​

Technical Answer: A Covering Index includes all columns needed for a query, allowing an "Index Only Scan." Add included columns: CREATE INDEX ON users (email) INCLUDE (name, created_at). Now SELECT name FROM users WHERE email = 'a@b.com' uses Index Only Scan.

Code/Syntax Justification:

CREATE INDEX idx_email_covering ON users (email) INCLUDE (name, is_active);

🔍 Behind the Question: Eliminating heap access. ❌ The Red Flags: Adding every column to INCLUDE (defeats the purpose, index becomes too large).

QN: What is "Bloat" in indexes and how to fix?​

Technical Answer: Index bloat happens when rows are deleted/updated but space isn't reclaimed. Fix: REINDEX INDEX idx_name; (locks the index) or REINDEX CONCURRENTLY (no lock, slower). Regular VACUUM helps prevent it.

Code/Syntax Justification:

REINDEX CONCURRENTLY INDEX idx_users_email;

🔍 Behind the Question: Maintenance. ❌ The Red Flags: Never reindexing a table that has 50% updates/deletes.

QN: How do you optimize "LIKE" queries with Text Pattern?​

Technical Answer: B-Tree indexes don't help with leading wildcards (%keyword). Use pg_trgm extension with GIN index: CREATE INDEX ON table USING gin (col gin_trgm_ops). This enables fast LIKE '%word%'.

Code/Syntax Justification:

CREATE EXTENSION pg_trgm;
CREATE INDEX ON users USING gin (name gin_trgm_ops);

🔍 Behind the Question: Text search optimization. ❌ The Red Flags: Using B-Tree index for %keyword% (Seq Scan).

QN: What is "Parallel Index Creation" in PostgreSQL?​

Technical Answer: Since PostgreSQL 11, CREATE INDEX can use multiple CPUs if the table is large enough and max_parallel_workers allows it. This speeds up index creation significantly.

Code/Syntax Justification:

SET max_parallel_workers = 8;
CREATE INDEX ON huge_table (col);

🔍 Behind the Question: DDL performance. ❌ The Red Flags: Creating an index on a 1TB table during peak hours (use CONCURRENTLY).

QN: How do you handle "Query Plan" instability (Parameter Sniffing)?​

Technical Answer: In PostgreSQL, this is less common than SQL Server, but can happen with prepared statements. Fix: Use EXECUTE in PL/pgSQL to force custom plan, or SET plan_cache_mode = force_custom_plan.

Code/Syntax Justification:

SET plan_cache_mode = force_custom_plan;

🔍 Behind the Question: Plan stability. ❌ The Red Flags: Blindly forcing custom plans for everything (prepared plans are usually faster).

QN: What is "Foreign Data Wrapper" (FDW) performance tuning?​

Technical Answer: FDW queries can be slow because filtering happens locally. Push predicates down: Ensure the foreign server supports pushdown. Use IMPORT FOREIGN SCHEMA to get accurate table definitions.

Code/Syntax Justification:

CREATE FOREIGN TABLE remote_users (...) SERVER remote_db OPTIONS (table_name 'users');

🔍 Behind the Question: Distributed query optimization. ❌ The Red Flags: Joining a local 1M row table with a remote 1M row table (fetches all remote rows).

QN: How do you use "Materialized Views" for caching aggregations?​

Technical Answer: Pre-compute expensive aggregations (e.g., monthly sales). CREATE MATERIALIZED VIEW mv AS SELECT .... Refresh with REFRESH MATERIALIZED VIEW (locks) or CONCURRENTLY (no lock, Postgres 9.4+).

Code/Syntax Justification:

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT date_trunc('month', created_at) as m, SUM(amount) FROM orders GROUP BY m;
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;

🔍 Behind the Question: Read optimization. ❌ The Red Flags: Refreshing a materialized view during peak hours without CONCURRENTLY.

QN: What is "Table Partitioning" performance impact?​

Technical Answer: Partitioning (by range/list) can improve query performance because the planner can "prune" partitions. If you query WHERE date > '2023-01-01', it skips partitions for 2022.

Code/Syntax Justification:

-- Query automatically prunes irrelevant partitions
SELECT * FROM orders WHERE created_at > '2023-06-01';

🔍 Behind the Question: Large table management. ❌ The Red Flags: Partitioning a small table (overhead > benefit).

QN: How do you handle "Connection Pool Saturation"?​

Technical Answer: If all connections are used, new requests wait or fail. Monitor pg_stat_activity. If too many idle, check app connection handling. If too many active, scale DB or optimize queries.

Code/Syntax Justification:

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

🔍 Behind the Question: Resource exhaustion. ❌ The Red Flags: Setting max_connections to 10,000 (each connection uses RAM, even idle).

QN: What is "JIT" (Just-In-Time) compilation in PostgreSQL?​

Technical Answer: PostgreSQL 11+ can use LLVM to JIT compile query expressions. Enable with SET jit = on;. Good for long-running analytical queries. Usually off by default.

Code/Syntax Justification:

SET jit = on;
EXPLAIN ANALYZE SELECT SUM(amount) FROM huge_table;

🔍 Behind the Question: Advanced query acceleration. ❌ The Red Flags: Enabling JIT for simple 1-row lookups (overhead > benefit).

QN: How do you monitor "Buffer Cache Hit Ratio"?​

Technical Answer: A high cache hit ratio (99%+) means most data is read from RAM, not disk. Query pg_stat_database. Low ratio means shared_buffers is too small or working set > RAM.

Code/Syntax Justification:

SELECT datname, blks_hit::float / (blks_read + blks_hit) as ratio FROM pg_stat_database;

🔍 Behind the Question: Memory configuration. ❌ The Red Flags: Obsessing over 99.9% vs 99.8% (micro-optimization).

QN: What is "Wait Events" analysis in PostgreSQL?​

Technical Answer: When a query is slow, check pg_stat_activity.wait_event. Common waits: DataFileExtend (disk full/slow), ClientRead (network), Lock (blocked by another transaction). This tells you why it's slow.

Code/Syntax Justification:

SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE state = 'active';

🔍 Behind the Question: Deep performance diagnosis. ❌ The Red Flags: Looking only at EXPLAIN without checking if the query is waiting on locks.


This document covers 300 comprehensive Q&As on ORMs (SQLAlchemy 1.x/2.0, Django ORM, Eloquent, MongoEngine), Alembic migrations, Elasticsearch search, database migration patterns, search relevance engineering, multi-tenancy, performance optimization, testing strategies, connection pooling, advanced query optimization, transaction isolation, ORM security, PostgreSQL-specific patterns, and advanced architectural patterns for production SaaS applications.


Senior / Principal — Real World ORM & Migration Scenarios​

Q301: N+1 query explosion in production API​

Level: Senior

Scenario: A FastAPI endpoint returns 50 orders with customer and items. The page loads in 8 seconds. Database CPU spikes. Query log shows 1 + 50 + 50 = 101 queries for one endpoint.

Impact: Slow API. Database overload. Poor user experience under load.

Root Cause: The ORM lazy-loads relationships. One query fetches orders, then one query per order for customer, then one per order for items = N+1 problem.

Simple explanation: You order 50 receipts. For each receipt, you make a separate phone call to ask who bought it and what they bought. 101 calls instead of 1.

Technical Answer:

  1. Use selectinload / joinedload (SQLAlchemy) or select_related / prefetch_related (Django) to eager-load relationships.
  2. Add database indexes on foreign key columns.
  3. Monitor query count in tests (assert max queries per endpoint).
  4. Use EXPLAIN ANALYZE to confirm index usage.

Code/Syntax Justification:

# SQLAlchemy 2.0 eager loading
from sqlalchemy.orm import selectinload, joinedload

orders = db.query(Order).options(
selectinload(Order.customer),
selectinload(Order.items).selectinload(Item.product),
).all()

# Django equivalent
orders = Order.objects.select_related("customer").prefetch_related("items__product")

Workflow Diagram:

Without fix: With selectinload:
1 query: orders 1 query: orders
50 queries: customers 1 query: customers (JOIN/IN)
50 queries: items 1 query: items (all at once)
Total: 101 queries Total: 3 queries

🔍 Behind the Question: Eager loading, N+1 detection, selectinload vs joinedload, query counting.

❌ The Red Flags: Lazy-loading relationships in serializers; not measuring query count; missing FK indexes.


Q302: Migration locks table on large production table​

Level: Principal

Scenario: A deploy adds a non-nullable column with a default to a 50-million-row PostgreSQL table. ALTER TABLE rewrites the table and takes an ACCESS EXCLUSIVE lock. Site is read-only or down for 20 minutes.

Impact: Extended downtime. Failed deploy. Rollback stress.

Root Cause: Non-backward-compatible migration run during deploy. ALTER TABLE ... ADD COLUMN NOT NULL DEFAULT rewrites every row and locks the table.

Simple explanation: Replacing the foundation of a building while people are inside. Everyone must leave until it's done.

Technical Answer:

  1. Use backward-compatible migrations: add nullable column first, backfill in batches, then set NOT NULL in a later deploy.
  2. Run migrations separately from code deploy (migrate first, then deploy code using the new column).
  3. Use CONCURRENTLY for indexes (PostgreSQL) to avoid locks.
  4. Test migration on production-sized staging data.
  5. Use Alembic batch operations for SQLite (which doesn't support concurrent ALTER).

Code/Syntax Justification:

# Alembic migration: Step 1 - add nullable column
def upgrade():
op.add_column("orders", sa.Column("status", sa.String(20), nullable=True))
# Step 2 (separate migration): backfill
op.execute("UPDATE orders SET status = 'pending' WHERE status IS NULL")
# Step 3 (separate deploy): set NOT NULL
op.alter_column("orders", "status", nullable=False)

Workflow Diagram:

Bad deploy: Safe deploy:
ALTER TABLE add NOT NULL 1. Add nullable column (no lock)
lock table, rewrite 50M rows 2. Deploy code (writes to nullable col)
site down 20 min 3. Backfill in batches
4. Set NOT NULL (fast, no rewrite)
No downtime

🔍 Behind the Question: Zero-downtime migrations, backward compatibility, lock avoidance, CONCURRENTLY.

❌ The Red Flags: Adding NOT NULL column with default directly; running migrate + deploy simultaneously; not testing on production-sized data.


Q303: Connection pool exhaustion under async load​

Level: Senior

Scenario: A FastAPI app using SQLAlchemy async works fine normally. During traffic spike, all requests hang with "could not obtain connection from pool within 30 seconds." Database CPU is low.

Impact: All API calls hang or fail. Database healthy but unreachable.

Root Cause: Async pool size too small. Each request holds a connection for its entire duration including slow queries. Pool empties and new requests queue.

Simple explanation: Five checkout counters. Hundreds of customers. Each counter stays occupied because customers pay slowly. New customers can't check out.

Technical Answer:

  1. Size pool based on concurrent workers: pool_size = 2 * CPU cores * workers.
  2. Set pool_timeout to fail fast instead of hanging forever.
  3. Set pool_recycle and pool_pre_ping for stale connections.
  4. Keep DB transactions short — don't call external services inside a transaction.
  5. Monitor pool.checkedout() and alert at 80% utilization.

Code/Syntax Justification:

from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession

engine = create_async_engine(
DATABASE_URL,
pool_size=20,
max_overflow=10,
pool_timeout=30,
pool_recycle=1800,
pool_pre_ping=True,
)
async_session = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

Workflow Diagram:

Request arrives
|
v
[Check connection pool]
|
+-- available --> checkout -> run query -> return to pool
|
+-- none available --> wait up to pool_timeout
|
+-- freed --> proceed
|
+-- timeout --> 503 Service Unavailable

🔍 Behind the Question: Async pool sizing, fail-fast vs hang, short transactions, pool monitoring.

❌ The Red Flags: Default pool_size=5 under high load; external API calls inside DB transactions; no pool timeout.


Q304: Deadlock under concurrent updates​

Level: Senior

Scenario: Two workers process orders at the same time. Worker A updates order 1 then order 2. Worker B updates order 2 then order 1. Both wait for each other. Database aborts one with deadlock error.

Impact: One worker fails. Orders need reprocessing. Retry logic must handle it.

Root Cause: Transactions acquire row locks in different orders, forming a cycle.

Simple explanation: Two people each hold one end of a rope and want the other end. Neither lets go. Stuck forever.

Technical Answer:

  1. Always acquire locks in consistent global order (e.g., by ID ascending).
  2. Use SELECT ... FOR UPDATE to lock rows explicitly before updating.
  3. Keep transactions short to reduce lock hold time.
  4. Catch deadlock errors and retry with backoff.

Code/Syntax Justification:

from sqlalchemy import text

ids = sorted([order1_id, order2_id])
with db.begin() as transaction:
db.execute(text("SELECT * FROM orders WHERE id = :id FOR UPDATE"), {"id": ids[0]})
db.execute(text("SELECT * FROM orders WHERE id = :id FOR UPDATE"), {"id": ids[1]})
# safe to update both now

Workflow Diagram:

Without fix: With fix (consistent order):
A locks 1 A locks 1 (lower id first)
B locks 2 B waits for 1
A waits for 2 ---> B gets 1 after A commits
B waits for 1 B locks 2
DEADLOCK No deadlock

🔍 Behind the Question: Lock ordering, deadlock detection, retry handling, SELECT FOR UPDATE.

❌ The Red Flags: No consistent lock order; no retry on deadlock; long-running transactions holding locks.


Q305: Soft delete vs hard delete — data integrity and recovery​

Level: Principal

Scenario: A user deletes their account. Hard delete removes the row. Related orders now reference a non-existent user (orphaned) or cascade-delete removes orders unexpectedly. Support can't recover data if deletion was a mistake.

Impact: Data loss. Broken foreign keys. No audit trail. Compliance issues.

Root Cause: Hard delete destroys data immediately. No way to undo. No audit of who deleted what and when.

Simple explanation: Shredding a paper instead of putting it in a "to be archived" folder. If you need it later, it's gone.

Technical Answer:

  1. Use soft delete: add deleted_at timestamp column. Filter WHERE deleted_at IS NULL in queries.
  2. Use a base model mixin so all tables inherit soft delete.
  3. For true deletion (GDPR), schedule hard delete after retention period.
  4. Handle foreign keys: either soft-delete related rows too, or restrict delete if dependents exist.
  5. Index deleted_at for query performance.

Code/Syntax Justification:

from sqlalchemy import Column, DateTime
from sqlalchemy.sql import func

class SoftDeleteMixin:
deleted_at = Column(DateTime, nullable=True)

@classmethod
def not_deleted(cls):
return cls.deleted_at.is_(None)

# Usage
query = session.query(Order).filter(Order.not_deleted())
# To soft delete
order.deleted_at = func.now()
session.commit()

Workflow Diagram:

Hard delete: Soft delete:
DELETE FROM users UPDATE users SET deleted_at = NOW()
row gone forever row hidden, still recoverable
orphaned orders orders still reference user
no audit audit trail preserved

🔍 Behind the Question: Soft delete patterns, data retention, GDPR, foreign key handling, audit trail.

❌ The Red Flags: Hard delete without audit; cascading deletes destroying related data; no way to recover accidental deletion.


Q306: Alembic autogenerate drift — migration doesn't match model​

Level: Senior

Scenario: Developer adds a column to SQLAlchemy model but forgets to create migration. alembic upgrade head succeeds but the column doesn't exist in DB. App crashes with "column does not exist" on first use.

Impact: Deploy succeeds but app crashes at runtime. Debugging takes time. Production incident.

Root Cause: Model and DB schema are out of sync. Alembic autogenerate wasn't run, or migration wasn't reviewed/committed.

Simple explanation: You add a new drawer to the cabinet but don't tell the installer. The installer installs the old cabinet. You try to put things in the new drawer — it's not there.

Technical Answer:

  1. Always run alembic revision --autogenerate after model changes.
  2. Review generated migration before committing (check for dropped columns, data loss).
  3. Add CI check: alembic check or compare DB schema to model metadata.
  4. Test upgrade and downgrade on a staging copy of production data.
  5. Use alembic stamp only when manually syncing (rare).

Code/Syntax Justification:

# After model change
alembic revision --autogenerate -m "add status column to orders"
# Review the generated file, then commit
alembic upgrade head

# CI check: ensure migrations are up to date
alembic check # or custom script comparing metadata

Workflow Diagram:

Model change (add column)
|
v
Without autogenerate: With autogenerate:
app crashes (column missing) alembic revision --autogenerate
review migration file
commit + alembic upgrade head
schema matches model

🔍 Behind the Question: Schema drift, autogenerate, migration review, CI validation, upgrade/downgrade testing.

❌ The Red Flags: Manual schema changes outside Alembic; not reviewing autogenerate output; no CI check for drift.


Q307: Eloquent mass assignment vulnerability​

Level: Senior

Scenario: A Laravel API allows POST /users with all request fields. A user sends {"name": "Asha", "is_admin": true} and gains admin privileges because is_admin is not guarded.

Impact: Privilege escalation. Unauthorized admin access. Security breach.

Root Cause: Mass assignment without $fillable or $guarded. All request fields mapped directly to model attributes.

Simple explanation: A form lets you write any field. You write "is_admin = true" and the system believes you.

Technical Answer:

  1. Define $fillable (allow-list) or $guarded (deny-list) on every Eloquent model.
  2. Never use $request->all() directly in create/update without validation.
  3. Use Form Request validation to whitelist allowed fields.
  4. Audit models for unguarded sensitive fields (is_admin, role, balance).

Code/Syntax Justification:

// User.php
protected $fillable = ['name', 'email', 'password']; // allow-list only
// is_admin NOT in fillable -> cannot be mass-assigned

// Controller
$user = User::create($request->validated()); // validated fields only

Workflow Diagram:

Request: {name, email, is_admin: true}
|
v
Without fillable: With fillable (name, email):
User::create($request->all()) User::create($request->validated())
| |
v v
is_admin = true (escalation) is_admin ignored -> safe

🔍 Behind the Question: Mass assignment, fillable/guarded, input validation, privilege escalation.

❌ The Red Flags: $request->all() in create; empty $guarded; no validation before mass assignment.