Catalog
concept#Data#Architecture#Reliability#Security

SQL Database

Relational database concept that stores structured data in tables and uses SQL for querying and manipulation.

An SQL database is a relational database system that stores structured data in tables and uses SQL as its query language.
Established
Medium

Classification

  • Medium
  • Technical
  • Architectural
  • Intermediate

Technical context

ORM libraries (e.g., Hibernate, Sequelize)ETL/streaming tools (e.g., Debezium, Kafka Connect)Backup and replication solutions (e.g., pg_basebackup)

Principles & goals

Design data model first; normalize for consistencyUse transactions for consistent multi-step operationsUse indexes selectively to accelerate queries
Build
Enterprise, Domain, Team

Use cases & scenarios

Compromises

  • Missing index management leads to performance degradation
  • Unplanned schema changes can cause lockups and downtime
  • Misconfiguration increases attack surface and risk of data loss
  • Perform schema migrations in small, reversible steps
  • Maintain indexes based on real query profiles
  • Schedule regular backups and recovery drills

I/O & resources

  • Data model / schema definition
  • Capacity and performance requirements
  • SLAs for consistency, availability and latency
  • Persistent relational records
  • Transactional integrity guarantees
  • Interfaces (SQL) for querying and manipulation

Description

An SQL database is a relational database system that stores structured data in tables and uses SQL as its query language. It provides transactions, integrity constraints and relational modeling. SQL databases are suited for consistent OLTP workloads, while design choices affect latency, scalability and operational effort.

✔

  • Strong consistency via ACID transactions
  • Mature query language (SQL) with broad tool support
  • Efficient relational modeling of complex business rules

✖

  • Horizontal scaling can be complex and costly
  • Rigid schemas hinder rapid, schema-less iteration
  • Not optimal for highly parallel, distributed analytics without adaptation

  • Query latency (median / p95)

    Measure of response time for typical queries to assess performance.

  • Transactions per second (TPS)

    Number of committed transactions per time unit as a throughput metric.

  • Transaction failure rate

    Percentage of failed or rolled-back transactions for reliability assessment.

PostgreSQL in a web application

PostgreSQL used as primary transactional store for user and order data.

MySQL as configurable metadata service

MySQL stores global configurations and feature flags with replication for availability.

Oracle for critical business applications

Oracle database in a financial application with strict integrity and compliance requirements.

1

Specify requirements and data model

2

Design schema and indexes

3

Provision and configure production instance

4

Set up replication, backup and monitoring

5

Test migration and rollout strategy

⚠️ Technical debt & bottlenecks

  • Unstructured migration scripts without version control
  • Missing or stale indexes in production tables
  • Monolithic database without a clear partitioning strategy
Indexing and query optimizationStorage and I/O bottlenecksNetwork latency for replication
  • Using an SQL database for highly parallel distributed analytics without adaptation
  • Missing transaction logic and manual data repairs
  • Untested sharding strategies in critical paths
  • Ignoring lock contention in concurrent writes
  • Relying on defaults without scaling evaluation
  • Insufficient observability for slow queries
Relational modeling and normalizationSQL tuning and index strategiesOperations, backup and high-availability configuration
Data integrity and transaction safetyOperational availability and recoverabilityPerformance requirements for read and write loads
  • • Predefined schema requires migration processes
  • • Transaction isolation can limit concurrency
  • • Regulatory requirements for privacy and auditing