⚡ OLTP vs. OLAP
Two fundamental database paradigms. OLTP processes transactions in real time (INSERT, UPDATE, DELETE). OLAP analyzes large volumes of historical data (SELECT with massive aggregations). Mixing the two in the same database is a recipe for disaster.
🎯 Transactional vs. Analytical
OLTP (Online Transaction Processing) is optimized for fast, point-in-time operations: adding a customer, recording a sale, updating inventory. It uses row store (row-oriented data).
OLAP (Online Analytical Processing) is optimized for scanning millions of records and generating aggregations: "what was the revenue by region over the last 3 years?" It uses column-store (column-oriented data), which lets you compress and read only the columns you need.
- • Row-store: Great for retrieving an entire record (SELECT * WHERE id = X). PostgreSQL, MySQL, Oracle.
- • Column store: Great for aggregating an entire column (SUM, AVG, COUNT). BigQuery, Redshift, ClickHouse.
- • Hybrid (HTAP): Tries to combine both. TiDB, CockroachDB, AlloyDB. Complex but promising.
✅ DO
- • Separate OLTP and OLAP workloads into different databases
- • Use read replicas for analytical queries
- • Consider a column store for heavy dashboards
- • Define different SLAs for each workload
❌ DO NOT DO
- • Run heavy reports on the production database
- • Assuming a single database can handle everything
- • Ignoring write latency in OLAP systems
- • Confusing a slow row store with a “bad database”
📦 Partitioning
Partitioning divides a large table into smaller pieces (partitions) based on a criterion. The database knows which partition to search, avoiding a scan of the entire table. Essential for tables with millions or billions of rows.
💻 Range Partitioning (PostgreSQL)
CREATE TABLE pedido (
id SERIAL,
total NUMERIC,
criado_em DATE
) PARTITION BY RANGE (criado_em);
CREATE TABLE pedido_2024 PARTITION OF pedido
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE pedido_2025 PARTITION OF pedido
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');🔍 Types of Partitioning
Range
Divides by value ranges (dates, IDs). The most common type. Ideal for time-based data.
Hash
Distributes uniformly based on a column’s hash. Good for balancing load across partitions.
List
Divides by discrete values (country, status, category). Each value goes to its partition.
💡 Practical Tip
Partition only tables with millions of records. Small tables don't benefit, and the overhead of managing partitions isn't worthwhile. The partition column must be present in practically every query with WHERE for the partition pruning works.
🗄️ Archiving and Retention
Data cannot stay in the database forever. Retention Policies define how long to keep each type of data. Archiving move old data to cheaper storage. It’s a matter of compliance, cost, and performance.
🎯 Compliance: LGPD and GDPR
Data protection laws require personal data to be kept only for as long as necessary. Keeping data longer than necessary is legal and financial risk.
- • LGPD (Brazil): Personal data must be deleted when its purpose has been fulfilled or consent is revoked
- • GDPR (Europe): Data minimization principle - collect and retain only what is strictly necessary
- • Cold storage: Historical data can go to low-cost storage (S3 Glacier, Azure Archive) with occasional access
- • Purge Jobs: Scheduled jobs that automatically remove expired data. Without purging, the database only grows.
💡 Practical Tip
Create a table for retention policies that maps each data type to its maximum retention period. Implement a cron job (or pg_cron) that runs weekly, checks the retention periods, and automatically archives or purges the data. Document everything—auditors will ask.
📊 Data Warehouse
One Data Warehouse and a centralized repository optimized for analytical queries. While OLTP databases serve the application, the DW serves the business intelligence. Data from multiple sources is consolidated into a model optimized for analysis.
🎯 Star Schema
The most widely used model in data warehouses. A fact table central (metrics, events) surrounded by dimension tables (descriptive context).
Fact Table (fact)
Contains quantitative metrics: sale amount, quantity, cost. Foreign keys to dimensions. Many rows, few columns.
Dimension Table (dim)
Contains descriptive attributes: product name, city, category. Few rows, many columns. Answers “who, what, where, when.”
🔍 Data Warehouse Ecosystem
- BigQuery (Google): Serverless, column-store, pay per query. Scales automatically.
- Redshift (AWS): Managed cluster, column store, native S3 integration.
- Snowflake: Multi-cloud, separates storage from compute, native concurrency.
- ClickHouse: Open source, extremely fast for aggregations, used by Cloudflare and Uber.
🔄 ETL and Pipelines
ETL (Extract, Transform, Load) is the process of moving data between systems. Raw data leaves the source, is cleaned and transformed, then loaded into the destination. Without a reliable pipeline, your Data Warehouse is garbage with a pretty interface.
🔍 ETL vs. ELT
ETL (classic)
Transforms before loading. Data arrives clean at the destination. More control, slower. Tools: Airflow, Informatica, Talend.
ELT (modern)
Loads raw data and transforms it at the destination (which has computing power). Faster, more flexible. Tools: dbt, Fivetran + BigQuery.
🔄 ETL Pipeline in 3 Steps
Extract
Collects data from sources: APIs, OLTP databases, CSV files, logs, webhooks. The goal is to capture everything without losing anything. Incremental is better than full load.
Transform (Transform)
Cleans, normalizes, aggregates, and enriches data. Removes duplicates, standardizes formats, and calculates derived metrics. dbt is the go-to tool for SQL transformations.
Load (Load)
Loads the transformed data into the destination (Data Warehouse, Data Lake). This can be batch (daily/hourly) or streaming (real time with Kafka/Kinesis).
💡 Practical Tip
Start with Airflow to orchestrate pipelines and dbt for SQL transformations. Both are open source and the industry standard. Always implement data quality checks between the steps—bad data propagated through the pipeline contaminates everything downstream.
📡 Observability
Observability is the ability to understand the database’s internal state through external signals. The three pillars: metrics, logs, and traces. An unmonitored database is a ticking time bomb. When an incident happens—and it will—you need to know where to look.
🎯 The 3 Pillars of Observability
Metrics
Metrics over time: p95/p99 latency, throughput (queries/s), active connections, disk usage, cache hit ratio. pg_stat_statements is essential.
Logs
Event logs: slow queries, errors, deadlocks, autovacuum. Configure log_min_duration_statement to capture slow queries automatically.
Traces
End-to-end tracking of a request: from the frontend to the database and back. Identifies bottlenecks across the entire chain. OpenTelemetry is the standard.
🚨 Common Monitoring Failures
- • Alerts only for "disk full": When the alert fires, it’s already too late. Monitor the growth rate, not just absolute usage.
- • Without a baseline: If you don't know what "normal" looks like, you can't detect anomalies. Collect metrics for weeks before setting thresholds.
- • Alert fatigue: 200 alerts/day = zero useful alerts. Prioritize and silence noise. Fewer alerts, more action.
- • Monitor only the database: The problem may be in the network, the app, or the connection pool. Observability is end-to-end.
🏛️ Governance
Data governance is the framework of policies, processes, and responsibilities that ensures data is treated as a strategic asset. Without governance, data becomes expensive garbage. Quality, security, and compliance depend on it.
🎯 Governance Pillars
- • Data Catalog: Inventory of all datasets, tables, columns, owners, and descriptions. "What data do we have and where is it?" DataHub, Amundsen, OpenMetadata.
- • Data Lineage: Tracking the source and transformation of data. "Where did this number in the report come from?" Essential for auditing and debugging.
- • RBAC (Role-Based Access Control): Each person/system accesses only what they need. Principle of least privilege. GRANT/REVOKE in PostgreSQL.
- • Data classification: Public, internal, confidential, restricted. Each level has different access, retention, and encryption rules.
- • Auditing: Logs of who accessed what, when, and why. A legal requirement (LGPD/GDPR) and the basis for incident investigations.
💡 Practical Tip
Governance doesn't have to start big. First step: document who the owner for each table/dataset. Second: implement basic RBAC with roles in the database. Third: connect audit logging. These three steps already cover 80% of what audits ask for.