database

Query optimization maximizes performance through efficient execution plans. I analyze queries with EXPLAIN ANALYZE. Understanding sequential scans vs index scans guides optimization. Join order affects performance dramatically. Subquery optimization v

Aggregation performs database-level calculations efficiently. I use aggregate() for single results across entire queryset (like average or total) and annotate() to add calculated fields to each object. Common aggregates include Count, Sum, Avg, Min, a

Migrations version control database schema changes, making them trackable and reversible. Each migration file contains up() and down() methods defining changes and rollbacks. I use the Schema builder's fluent API to create tables, add columns, define

Backups protect against data loss from failures, corruption, or human error. I use full backups for complete database snapshots. Incremental backups save only changes since last backup. Point-in-time recovery restores to specific moments. Logical back

Hard deletes make data recovery impossible and complicate audit trails. Soft deletes mark records as deleted without removing them from the database, preserving history and enabling undo functionality. The paranoia gem adds a deleted_at timestamp colu

For queries too complex for the ORM, I use raw SQL. The raw() method returns model instances. I use cursor.execute() for non-model queries. I always use parameterized queries to prevent SQL injection—never string interpolation. For reporting, raw SQL

Window functions perform calculations across rows related to the current row. I use them for running totals, rankings, and moving averages. Django's Window expression with functions like RowNumber, Rank, DenseRank provide SQL window function support.

Rails migrations evolve database schema over time. I use change method for reversible migrations. Migrations create tables, add/remove columns, add indices. up and down methods provide explicit control. Irreversible migrations like data transformation

Complex reporting queries with multiple joins and aggregations can become unmaintainable in ActiveRecord. PostgreSQL views encapsulate query complexity in the database layer and appear as regular tables to Rails. I create views for common reporting ne

Q objects enable complex query logic with OR, AND, and NOT operations. I combine them with | for OR and & for AND. The ~Q() syntax negates a condition. This is cleaner than raw SQL for dynamic filters. I build Q objects conditionally based on user

Data archival moves old data to cheaper storage. I use partitioning for time-based archival. Detach old partitions, export to S3, drop table. Archive tables store historical data with reduced indexes. Understanding retention policies prevents runaway

Database security protects data from unauthorized access. I use GRANT/REVOKE for permissions—SELECT, INSERT, UPDATE, DELETE. Role-based access control groups permissions. Row-level security filters data per user. Column-level security restricts sensit