Databases Intermediate

What is Stored Procedure?

A precompiled collection of SQL statements stored in the database that can be executed as a single unit.

Stored procedures encapsulate complex database logic on the server side. They accept parameters, perform operations, and return results. Benefits include reduced network traffic, code reuse, and centralized business logic.

In PostgreSQL, functions (CREATE FUNCTION) serve a similar purpose, supporting PL/pgSQL, PL/Python, and other languages. Modern development often favors application-level logic, but stored procedures remain valuable for complex data operations.

A technique where the database maintains multiple versions of data to allow concurrent reads and writes without locking.

Database Sharding

A horizontal scaling strategy that distributes data across multiple database servers based on a partition key.

Row-Level Security (RLS)

A database feature that restricts which rows a user can access in a table based on security policies.

Query Optimization

The process of improving database query performance through indexing, query rewriting, and schema design techniques.

Time-Series Database

A database optimized for storing and querying timestamped data points like metrics, sensor readings, and event logs.

Replication

The process of copying and maintaining database data across multiple servers for redundancy, failover, and read scaling.

View All Databases Terms →