Postgres

Question 1

Tuning

Answer 1

• increase size of shared buffers
• effective_cache_size
• increase sort memory
• increase server memory so we have a greater cache hit ration
• create table indexes
• create views
• create table partitions
• turn on PG stats
• analyze query - explain analyze

Question 2

Database Isolation Levels

Answer 2

Read Committed - no dirty read / writes

Snap Shot Isolation

Question 3

Speedup OLAP

Answer 3

• use column databases

- materialized views

Question 4

OLAP vs OLTP

Answer 4

read pattern:
oltp - small record count fetched by key
olap - aggregates

write pattern:
oltp - random access, low latency writes,
olap - bulk import ETL

users
oltp - end users
olap - analysts

data
oltp - latest transactional
olap - historical

size
oltp - GB/TB
olap - TB/PB