Views, Procedures & Server-Side Logic

Grant SELECT on a view that projects only the allowed columns and rows, and grant nothing on the base table. Because the view typically executes with its owner's privileges, callers can read exactly what it projects and no more. Limits: it does not stop anyone holding direct base-table rights, and clever predicates can leak information.

Use a generated column whenever the value is a deterministic function of the same row - the engine guarantees it can never be wrong or bypassed, and there is no procedural code to maintain. Use a trigger only when the derivation needs other rows, other tables, non-deterministic input, or must freeze at insert time.

Keep every procedure body in version control as the source of truth, deploy it through the same ordered migration pipeline as schema changes, test against a real database in CI, keep signatures backward compatible across rolling deploys, and treat rollback as a forward migration re-applying the old body.

A plain refresh typically takes an exclusive lock and rebuilds the view, so readers block for the whole refresh. A concurrent refresh builds the new result separately and merges the differences row by row, letting readers keep querying — at the cost of a slower refresh and a required unique index.

CACHE n preallocates n values into memory so most allocations avoid touching and persisting shared sequence state. Bigger cache means less contention and fewer writes, but larger gaps after a crash and, across instances, values issued out of global order.

Engines cache plans for statements inside a routine and reuse them across calls, planning them against the first parameter values seen. If the data is skewed, a plan optimal for one value is terrible for another — parameter sniffing. Remedies: force a per-call replan, use a generic plan, split the routine, or restructure the query.

The optimizer usually cannot see inside the function, so it uses a fixed default row estimate instead of statistics. A wrong cardinality picks the wrong join order and join method — typically nested loops over millions of rows. Fixes: get the function inlined, declare a row estimate, or materialize its output into a temp table with statistics.

Each creation writes system-catalog rows and allocates storage, so at high rates the catalog itself becomes the hot spot: latch or lock contention on metadata, catalog bloat, and constant plan recompilation. Fixes: create the table once per session and reuse it, drop the staging step entirely, or move the intermediate inline.

Row-level triggers get single-row images, conventionally OLD and NEW: INSERT has only NEW, DELETE only OLD, UPDATE both. Statement-level triggers have no per-row image; transition tables expose the whole set of changed rows as two readable relations you can join and aggregate in one set-based statement.

An INSTEAD OF trigger replaces the engine's automatic rewrite: instead of executing the INSERT, UPDATE or DELETE, the engine runs your code, which issues explicit statements against the base tables. Risks: you own correctness, multi-row semantics, ordering, error handling, and returned row counts.

A table is key-preserved in a join view when its primary key stays unique in the view result — each of its rows appears at most once. Only key-preserved tables can be updated through the view, because only then does a view row identify exactly one base row.

Stacked views accumulate joins and columns nobody downstream needs, and inner aggregations or DISTINCTs block predicate pushdown so the bottom of the stack scans everything. Read the expanded plan, find the fence and the joins contributing nothing, then flatten the query for that consumer.

Compare where the work is cheapest to add capacity. Application servers are stateless and scale horizontally for money; the primary database is one writable node, shared by every query, and often licensed per core. Move work down only when it removes far more data movement than the CPU it adds.

On-commit refresh keeps the view nearly current but charges every writing transaction and serialises writers on shared aggregate rows. On-demand refresh batches the cost but leaves a staleness window. Choose by the consumer's tolerance for stale data against the write throughput you can afford to lose.

Triggers capture every writer atomically with the change but tax every write and roughly double log volume. Log-based capture reads the transaction log the engine already writes, so the write path pays nothing, but it is asynchronous and loses application context. An outbox gives typed business events in the same transaction, but only for writers that cooperate.

Introduce the new tables alongside the old shape, then expose a view carrying the old name and column names over the new structure so existing readers keep working unchanged. Migrate consumers to new views over time, then retire the compatibility view. It runs out on writes, on plan quality, and on the fact that a shim nobody is forced to leave lives forever.