Indexes are the most important tool for query performance. A B-tree index—the most common kind—supports equality and range lookups, a hash index supports only equality, a unique index enforces distinctness, and a composite index spans multiple columns. A clustered index determines the physical order of rows in a table (one per table), while non-clustered indexes are separate structures that point back to rows. A covering index contains every column a query needs, enabling an index-only scan that never touches the underlying table. Higher index selectivity (a greater fraction of distinct values) generally makes an index more useful, but every index slows writes and consumes storage, so they must be added deliberately.
To understand why a query is slow, EXPLAIN shows the planned execution steps and EXPLAIN ANALYZE runs the query and reports actual timings. The query optimizer—the component that picks the plan—can choose between a sequential scan (reading every row, fine for small tables) and an index scan, and between join algorithms such as nested loop (good when one side is small), sort-merge (good when both inputs are pre-sorted), and hash join (good when one side fits in memory). On large databases, periodic maintenance like rebuilding fragmented indexes, vacuuming dead tuples in PostgreSQL via autovacuum, and updating statistics keeps the optimizer making good choices.
Transactions package a unit of work with the ACID guarantees: atomicity (all or nothing), consistency (invariants preserved), isolation (concurrent transactions don't interfere), and durability (commits survive crashes). The statements BEGIN, COMMIT, and ROLLBACK control the lifecycle, with SAVEPOINT allowing partial rollback within a transaction. SQL defines four isolation levels—READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE—each preventing a different set of anomalies: dirty reads, non-repeatable reads, and phantom reads. Concurrency control ranges from pessimistic row-level and table-level locks (including SELECT FOR UPDATE) to MVCC, where readers don't block writers; deadlocks arise when transactions lock each other in a cycle and must be detected and broken.
The operational side of a database is just as important as its querying model. OLTP systems optimize for many small transactions, while OLAP systems support complex analytical queries on large data through column-oriented storage, data warehouses organized into star or snowflake schemas of fact and dimension tables, and even data lakes for raw storage. Scaling out uses partitioning (splitting a table into smaller pieces), sharding (horizontal partitioning across databases), and replication with read replicas for distributing read traffic, where the primary handles writes and standbys stay read-only. Backups come in two flavors—logical SQL dumps and physical byte-level copies—and combined with the Write-Ahead Log enable point-in-time recovery. Schema changes are managed via migration tools such as Flyway, Liquibase, or Django migrations, ideally without downtime using patterns like the strangler approach. Finally, application-facing concerns include pagination—cursor or keyset pagination vastly outperforms OFFSET on large tables—ORM frameworks like SQLAlchemy, Hibernate, and Sequelize (and the N+1 problem they can introduce through lazy loading), and security: parameterized queries and prepared statements bind user input separately from SQL text, preventing SQL injection attacks.