Database Management System (DBMS) manages data efficiently and provides mechanisms for storing, retrieving, and updating data. The physical structure of a database defines how data is stored on disk and how efficiently it can be accessed. While logical design focuses on data modeling and relationships, the physical structure ensures data is organized for optimal performance, scalability, and security.
1. Storage Hierarchy
Data in databases is stored using a hierarchy of memory devices. These are:
-
Primary Storage (RAM): Used for temporary data and cache during query execution.
-
Secondary Storage (Hard Disk Drives, Solid-State Drives): Used for permanent data storage.
-
Tertiary Storage (Magnetic tapes, cloud storage): Used for backups and archival purposes.
2. Data Files and Pages
Database data is stored in data files, which are divided into pages or blocks. Each page typically contains rows of a table. Pages are the basic unit of data transfer between disk and memory. This design minimizes disk I/O operations, improving access speed.
3. Tables, Segments, and Extents
Within storage structures:
-
A table represents logical data.
-
A segment refers to a set of extents allocated for a specific object (like a table or index).
-
An extent is a collection of contiguous pages.
The DBMS allocates extents dynamically as data grows. This structure allows databases to manage large datasets efficiently while reducing fragmentation.
4. Data Storage Formats
Databases can store data in various formats:
-
Row-oriented Storage: Stores complete rows together. Suitable for transactional systems (OLTP).
-
Column-oriented Storage: Stores column data together. Suitable for analytical processing (OLAP).
Each format serves a specific purpose, balancing between fast reads and efficient writes.
5. Buffer Management
The buffer manager handles movement of data between disk and memory. Frequently accessed data pages are cached in the buffer pool to reduce disk I/O. The DBMS uses algorithms like LRU (Least Recently Used) to manage which pages remain in memory.
6. File Organization
Databases use several file organization methods:
-
Heap files: Records are stored in no particular order. Best for random inserts.
-
Sorted files: Records are stored based on a key attribute, improving range queries.
-
Hashed files: Records are stored based on a hash function applied to key fields, supporting fast equality searches.
Efficient file organization directly affects the performance of database operations like searching and updating.