SQL & PostgreSQL
Joins, window functions, transactions, and query optimization.
Is an index scan always faster than a sequential scan?
An index scan follows an index to locate matching rows, while a sequential scan reads the table directly. If a query needs much of a table, following many scattered row locations can cost more than scanning it. PostgreSQL's planner weighs expected row count, I/O and other costs rather than always preferring indexes.
What do VACUUM, ANALYZE and autovacuum do?
PostgreSQL keeps older row versions for concurrent readers, so updates and deletes can leave dead versions behind. VACUUM makes eligible dead space reusable and supports visibility and transaction-ID maintenance; ANALYZE collects statistics for planning queries. Autovacuum runs this maintenance in the background but can be limited by long-running transactions or workload settings.
JSON vs JSONB, and views vs materialized views?
JSON stores JSON input text, while JSONB stores a processed representation that supports efficient operations and indexing. A view stores a query definition, whereas a materialized view stores the query's result for later reads. These are separate choices about data representation and precomputed results.
What are schemas, roles, sequences, functions and triggers?
PostgreSQL schemas organize database objects, and roles with grants control access to those objects. Sequences generate numeric values, functions package executable database logic, and triggers run configured logic in response to events. These features add organization and automation, but hidden trigger behavior can make writes harder to reason about.
What does an ORM do underneath?
An ORM translates object-oriented data operations into database commands and maps results back into application objects. It reduces repetitive persistence code, but the database still executes real SQL with real locks, indexes and query costs. Understanding the generated query is essential when debugging correctness or performance.
How can I practice joins and window functions?
A useful SQL exercise starts with a small dataset whose expected answer can be checked manually. Window functions then demonstrate ranking while keeping each employee's fields available. The example below returns all employees tied for the highest salary in each department.
Difficulty is an editorial estimate. Completion means you chose to mark a question as understood.