Basic SQL Management Guide: CRUD, Indexing, and Backup Basics

Basic SQL Management Guide: CRUD, Indexing, and Backup Basics

Overview

A concise guide covering core SQL management tasks: creating, reading, updating, deleting (CRUD); basic indexing strategies to improve query performance; and straightforward backup & restore practices to protect data.

CRUD (Core Operations)

• Create: Use CREATE TABLE, CREATE DATABASE, INSERT INTO. Define appropriate data types, NOT NULL, and primary keys.
  • Example: CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(255) UNIQUE);
  • Insert pattern: INSERT INTO users (name, email) VALUES (‘Alice’,’[email protected]’);
• Read: SELECT queries with WHERE, JOIN, GROUP BY, HAVING, ORDER BY, LIMIT. Prefer explicit column lists over SELECT.
  • Example: SELECT id, name FROM users WHERE email LIKE ‘%@example.com’ ORDER BY name;
• Update: UPDATE with WHERE to avoid full-table changes; use transactions for multi-step updates.
  • Example: BEGIN; UPDATE users SET email = ‘[email protected]’ WHERE COMMIT;
• Delete: DELETE with WHERE or TRUNCATE for whole-table removal. Consider soft deletes (is_deleted flag) for recoverability.
  • Example: DELETE FROM users WHERE>

Indexing Basics

• Purpose: Speed up lookups, joins, and ORDER BY/GROUP BY operations; trade-off with slower writes and extra storage.
• Types: Single-column, composite (multi-column), unique, and full-text (for text searching).
• When to add: Columns used frequently in WHERE, JOIN, ORDER BY, or as foreign keys.
• Design tips:
  • Keep indexes narrow (few columns, small types).
  • Put the most selective column first in composite indexes.
  • Avoid indexing columns with low cardinality (e.g., booleans).
  • Monitor with EXPLAIN/EXPLAIN ANALYZE to verify query plans.
• Maintenance: Rebuild or reorganize fragmented indexes periodically (depends on DBMS). Remove unused indexes.

Backup & Restore Basics

• Backup types: Logical (SQL dumps) and physical (file-level snapshots). Use both when appropriate.
• Frequency: Base on RPO/RTO requirements; common patterns: daily full backups plus frequent incremental or WAL/transaction-log backups.
• Testing: Regularly test restores to a staging environment to validate backups.
• Automation & Storage: Automate backups, rotate retention, and store offsite or in durable cloud storage. Encrypt backups at rest.
• Restore steps (general):
  1. Stop writes or set DB to restore-safe state (if required).
  2. Restore base backup.
  3. Apply incremental logs or WAL files up to desired point.
  4. Validate data integrity and resume service.

Transactions & Concurrency

• Use transactions (BEGIN/COMMIT/ROLLBACK) for multi-step changes.
• Understand isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE) and pick one balancing consistency and concurrency.
• Use row-level locking and optimistic concurrency where supported.

Security & Access Control

• Grant least-privilege: users should have only needed permissions (GRANT/REVOKE).
• Hash and salt passwords; avoid storing plaintext secrets.
• Use parameterized queries or prepared statements to prevent SQL injection.

Monitoring & Maintenance

• Monitor slow queries and resource usage (CPU, memory, disk I/O).
• Use query logging and performance tools (EXPLAIN, pg_stat_statements, slow query log).
• Schedule regular maintenance: vacuuming (Postgres), optimizing tables (MySQL), statistics updates.

Quick Checklist

• Define schemas with appropriate types and constraints.
• Implement primary keys and necessary foreign keys.
• Index commonly queried columns; avoid over-indexing.
• Automate and test backups; encrypt and store offsite.
• Use transactions and proper