Relational vs NoSQLL Databases
Relational Databases
- Structured Data:
- Relational databases use SQL for defining and manipulating data.
- Table-based:
- Data is stored in tables consisting of rows and columns.
- Relational:
- SQL databases use relationships (foreign keys) to connect data across tables.
- ACID Compliance:
- SQL databases follow the ACID (Atomicity, Consistency, Isolation, Durability) properties for transaction management.
NoSQL Databases
- Schema-less:
- No predefined schema, allowing for flexible data models.
- Distributed Architecture:
- Designed for horizontal scaling and distributed data storage.
- Variety of Data Models:
- Supports multiple data models, including document, key-value, column-family, and graph.
- Eventual Consistency:
- Some NoSQL databases prioritize availability over immediate consistency i.e. Basically Available, Soft state, Eventual consistency (BASE).
- Designed for Big Data:
- Handles large volumes of unstructured or semi-structured data.
Don't promise ACID
- priortise availability
- Eventual consistency
- Initial driver for NoSQL was because of semi-structured data that could be growing, and big data
- 2014 eye-opening moment, financial transactions were targeted
Difficult query languages
Both MySQL and Postgres Support JSON
- Both MySQL and PostgreSQL offer robust support for JSON data, allowing for flexible and efficient storage, querying, and manipulation of JSON documents.
- MySQL provides a native JSON data type with functions for JSON manipulation and indexing support via generated columns.
- PostgreSQL offers both JSON and JSONB data types, with JSONB providing superior indexing and querying capabilities.
- This allows us to combine relational databases with json (document storage) powerful for handling semi-structured data.
Still have relational databases
- flexibility of JSON format
Query Optimisation
- Identify Slow Queries: Use database performance monitoring tools to identify slow-running queries. Common tools include EXPLAIN (MySQL, PostgreSQL) to analyze query execution plans.
- Check Existing Indexes: Review existing indexes to ensure they are being used effectively. Sometimes, an index might be present but not utilized by the query optimizer.
- Create Indexes on Frequently Queried Columns: Index columns that are frequently used in WHERE clauses, JOIN conditions, and ORDER BY clauses to speed up query performance.
- Run query EXPLAIN
- check indexing of your data
- Don't want every field in the table to be indexed