PCLabs Survey, Part II: Server Engine

Server Engine
- units of execution (granularity of multi-tasking engine)
- computes statistics while building index
- can run operations without logging
- can undo database structure modification (Data Definition Layer) commands
- can undo committed transactions if required
- locking
- levels of locking granulatity available (options are row, page, table,etc.)
- default locking level for queries
- readers of data can block writers of data (at default locking level)
- writers of data can block readers of data (at default locking level)
- can use different types of locks on index and data pages in one query
- can escalate many fine-grained locks to one coarser lock
- can use multiple CPUs
- within a query (intra-query parallelism)
- on database load
- on database index
- on database backup and restore
- on insert ... select ...
- on update
- on delete
- join and index selection
- joins
- nested loop join
- hash join
- merge join
- semi-join for star queries
- list any others:
- indicies
- B-tree
- clustered (data pages ordered by primary key value)
- allow secondary indices on a clustered index table
- bitmap
- hash
- list any others:
- can scan indices in reverse
- optimizer
- cost-based
- rule-based
- can use multiple indices in a single query
- can use just the index to satisfy the query (covered indices)
- automatically maintains necessary optimizer statistics
- cache and memory management
- dynamic SQL statements parameterized and cached for later use
- execution plans can be compiled once and then shared among all users
- can define dedicated caches for particular tables or tablespaces
- can mark tables as memory-resident
- dynamic memory resource sharing (e.g. cache can grow or shrink)
- scalability
- number of CPUs supported per server
- multi-machine clustering for performance
- clustered servers require shared disk subsystem
- clustered machines can run different operating systems
- availability
- failover server option
- primary and standby servers require shared disk subsystem or only a shared network connection
- primary and standby servers can run different operating systems
- failover server can be actively used (active/active configuration)
- client connection libraries support automatic failover to standby server
- ability to guarantee service (QoS)
- query resource consumption governor
- can assign priorities to different groups of users
- backup and restore
- restore to a particular point-in-time (partial restore)
- incremental backup
- recovery
- can control frequency of checkpointing by number of operations or estimated recovery time (list which)
- storage engine
- default disk data block size
- users can choose disk block size (list options)
- allow use of unformatted disks for extra speed
- parallel disk operations
- automatic disk read-ahead
- multi-task (asynchronous) disk operations
- data and log devices can grow when needed
- Year 2000 certified
- Euro currency support

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org