We all know about different types of Databases. Just highlighting them to start off the topic.
Relational DB:
- Fixed table schema and relations (primary and foreign key)
- Strong transactional capability
- Update all table data in one transaction and commit
- Failure of one, implies rollback in all.
- Since its strong in transactions, Relational DB is preferred for OLTP (Online transaction processing) systems like banking.
- Lots of transactions (large number of users and small transactions per user)
- Data is stored in row storage
- In Google Cloud we have
- Cloud SQL
- Supports MySQL, SQL server and PostgreSQL
- Regional Database
- Multi zone for High availability
- Can't create Global Cloud SQL DB
- Data upto a few TB
- Fully managed (replication, patch management, DB management etc)
- Public IP provided to connect to the DB
- Cloud shell option also provided in console (gcloud sql command)
- Enable Cloud SQL Admin API as a pre-requisite
- Cloud Spanner
- Unlimited scale
- Horizontal scaling
- High availability
- Global applications (Globally consistent)
- Fully managed
- Multi region
- Option to add compute capacity when being configured
- 1 node = 1000 processing units
- Compute cost is the hourly cost charge for nodes or processing units.
- Storage cost is separate and billed per GB per month
- Min 100 processing units or 1 node
- Can be used for Analytics as well (OLAP - Online Analytics Processing)
- Used to analyze huge amount of data
- Reporting, data warehouse
- Uses columnar storage (not row) [High compression]
- Since data is stored via columns, it can be distributed
- Queries can run over multiple nodes (efficient execution for complex queries)
- GCP Managed service is BigQuery
NO SQL DB: (Not only SQL)
- Flexible schema
- Scales horizontally
- Highly scalable
- High performance
- GCP managed No SQL service:
- Cloud Firestore (Data store)
- Serverless
- Document DB
- Can run SQL like queries
- Used for high transactions
- Mobile and web applications
- Small to medium DB (few TB)
- BigTable
- Scalable
- Wide column DB
- Not serverless
- Data size > 10TB
- Does not support multi row transaction. Supports only single row.
- Not to be used for transactional applications
In Memory Database
- Faster to retrieve data since data is not on the disk
- Low latency (microseconds)
- Persistent data stored in memory
- Use for caching or session management
- GCP service is Managed Store
