Software design notes — Data layer
Data base type
Relational
store system master data and relational data .
Mysql/MS-SQL/Oracle
Some SQL tuning tricks
Use property types
Char for fixed length field .
Text for large block of text.
Int for number < 2³²
Decimal for currency
Avoid store large blob in database
Set not null when applicable for searching performance
Reduce join and keep it simple/Avoid sub query/inner join over where/avoid select */avoid select distinct / try avoid ‘%%’ (both side)/nolock/
Document
Store the document which is JSON .each document fields no need to be same (fields could be added on the fly, store nested structure or array). Work well with JSON friendly ecosystem like nodejs/javascript/python .
MongoDB/counchDB
Key-value
Often used as cache .
Redis/memcached/rocksDB
Wide column store
Each row could means multiple ‘tables’ .for example row ‘1’ = everything of a user .Scalable . used often in distributed system and big data system like hadoop .
HBase, Cassandra .
Time Series
data grows by time .e.g. IOT system data
postgreSQL/influxDB/HBase/Elastic search
What is MVCC in database?
Every time update will take a snapshot become version. Opened a ‘thread’ of the version.
#1 multiple user able to access same data same time
#2 every user sees own isolated database snapshot
#3 changes is isolated each user ,until transaction committed
Vertical scale (Federation)
Horizontal Scale(Sharding)
sharding key :usually is the primary key of the growing table .e.g. userId/locationId/eventId/transactionId .
mongoDb objectId also could be a choice .
Consistent hashing
- Request always be served by same server
- A ring hashing table
- Two hash functions: one for data another for server
- support add/remove server and only impact necessary servers
Data Archiving/Hot-Cold database
archive historical data into cold database to keep hot database lesser rows for better performance .
