• PG采用的是MVCC，更新操作不是in-place update，而是重新创建tuple， 可见性判断 • Robert Haas 2018, “DO or UNDO - there is no VACUUM”: zheap, in-place update PostgreSQL和Greenplum采用的策略 思考： 1. MySQL同样采用MVCC，事务恢复的时候为什么需要undo log：分布式事务提交日志 • 用于判断分布式事务是否提交，作用和PG 的commit log类似，基于simple LRU实现 • 分布式死锁检测 • 本地事务的管理：创建、提交、状态迁移 等 • 加锁和MVCC • 本地事务的死锁检测 • xlog、commit log(CLOG) • 对PREPARE、COMMIT/ABORT PREPARED语句的处理 Greenplum在PG的基础上实现

hreshold = 5000000（资料依据项目而定）  Truncate操作不会丢失字段级统计信息，在适当条件下可仅针对系统字段执行Analyze 垃圾空间回收 • GPDB采用MVCC机制，UPDATE 或 DELETE并非物理删除，而只是对无效记 录做标记； • Update/delete操作后，数据库不会自动释放这些空间，这些垃圾空间的回收方 式： 1）Vacuum 查询视图GP_TOOLKIT.GP_BLOAT_DIAG可监控垃圾空间的膨胀系数 • REINDEX:回收索引的垃圾空间 AGE监控和管理  PostgreSQL的MVCC事务语意依赖于比较事务ID(XID)的数值： 一条带有大于当前事务的XID的插入 XID的行版本是―属于未来的‖， 并且不应为当前事务可见。  PostgreSQL使用特殊的 XID(FrozenXID)与普通的XID进行区分。

Isolation Level 171 Removing Dead Rows from Tables 172 Example of Managing Transaction IDs 173 Simple MVCC Example 173 Managing Simultaneous Transactions 174 Managing XIDs and the Frozen XID 175 Example Control in Greenplum Database Greenplum Database uses the PostgreSQL Multiversion Concurrency Control (MVCC) model to manage concurrent transactions for heap tables. About Parallel Data Loading This topic Control in Greenplum Database Greenplum Database uses the PostgreSQL Multiversion Concurrency Control (MVCC) model to manage concurrent transactions for heap tables. Concurrency control in a database management

Isolation Level 320 Removing Dead Rows from Tables 321 Example of Managing Transaction IDs 322 Simple MVCC Example 322 Managing Simultaneous Transactions 323 Managing XIDs and the Frozen XID 324 Example Control in Greenplum Database Greenplum Database uses the PostgreSQL Multiversion Concurrency Control (MVCC) model to manage concurrent transactions for heap tables. About Parallel Data Loading This topic Control in Greenplum Database Greenplum Database uses the PostgreSQL Multiversion Concurrency Control (MVCC) model to manage concurrent transactions for heap tables. Concurrency control in a database management

Isolation Modes 247 Removing Dead Rows from Tables 248 Example of Managing Transaction IDs 249 Simple MVCC Example 250 Managing Simultaneous Transactions 250 Managing XIDs and the Frozen XID 251 Example Control in Greenplum Database Greenplum Database uses the PostgreSQL Multiversion Concurrency Control (MVCC) model to manage concurrent transactions for heap tables. About Parallel Data Loading This topic Control in Greenplum Database Greenplum Database uses the PostgreSQL Multiversion Concurrency Control (MVCC) model to manage concurrent transactions for heap tables. Concurrency control in a database management

Isolation Level 288 Removing Dead Rows from Tables 289 Example of Managing Transaction IDs 290 Simple MVCC Example 290 Managing Simultaneous Transactions 291 Managing XIDs and the Frozen XID 292 Example Control in Greenplum Database Greenplum Database uses the PostgreSQL Multiversion Concurrency Control (MVCC) model to manage concurrent transactions for heap tables. About Parallel Data Loading This topic Control in Greenplum Database Greenplum Database uses the PostgreSQL Multiversion Concurrency Control (MVCC) model to manage concurrent transactions for heap tables. Concurrency control in a database management

Isolation Modes 276 Removing Dead Rows from Tables 278 Example of Managing Transaction IDs 279 Simple MVCC Example 280 Managing Simultaneous Transactions 280 Managing XIDs and the Frozen XID 281 Example Control in Greenplum Database Greenplum Database uses the PostgreSQL Multiversion Concurrency Control (MVCC) model to manage concurrent transactions for heap tables. About Parallel Data Loading This topic Control in Greenplum Database Greenplum Database uses the PostgreSQL Multiversion Concurrency Control (MVCC) model to manage concurrent transactions for heap tables. Concurrency control in a database management

Isolation Modes 273 Removing Dead Rows from Tables 274 Example of Managing Transaction IDs 275 Simple MVCC Example 276 Managing Simultaneous Transactions 276 Managing XIDs and the Frozen XID 277 Example Control in Greenplum Database Greenplum Database uses the PostgreSQL Multiversion Concurrency Control (MVCC) model to manage concurrent transactions for heap tables. About Parallel Data Loading This topic Control in Greenplum Database Greenplum Database uses the PostgreSQL Multiversion Concurrency Control (MVCC) model to manage concurrent transactions for heap tables. Concurrency control in a database management

Isolation Level 306 Removing Dead Rows from Tables 307 Example of Managing Transaction IDs 308 Simple MVCC Example 308 Managing Simultaneous Transactions 309 Managing XIDs and the Frozen XID 310 Example Control in Greenplum Database Greenplum Database uses the PostgreSQL Multiversion Concurrency Control (MVCC) model to manage concurrent transactions for heap tables. About Parallel Data Loading This topic Control in Greenplum Database Greenplum Database uses the PostgreSQL Multiversion Concurrency Control (MVCC) model to manage concurrent transactions for heap tables. Concurrency control in a database management

空间 数据库、R、Madlib、pgcrypto 各类加密算法、gptext 全文检索都 是通过这种方式实现功能扩展的。 4) 在诸如 ACID 事物处理、数据强一致性保证、数据类型支持、独特 的 MVCC 带来高效数据更新能力等还有很多方面，Postgresql 似 乎在这些 OLAP 功能上都比 mysql 更甚一筹。 5) Postgresql 许可是仿照 BSD 许可模式的，没有被大公司控制，社区 过脚本定时批量收集统计信息，或者两者相结合。针对分区表日常 可按需收集子分区的统计信息，可节省时间提升效率。 ·表倾斜：表倾斜情况应该 DBA 的关注点之一，但无需每天处理。 ·表膨胀：基于 postgresql 的 MVCC 机制，表膨胀情况不能忽视。 重点应该关注日常更新和删除操作的表。 ·报错信息：在日志中错误信息多种多样，大部分不是 DBA 需要关 注的。应该重点关注 PANIC、OOM、Internal error