从MVCC机制看PostgreSQL的应用场景

Application Scenarios of PostgreSQL Database According to MVCC Mechanism

PostgreSQL数据库因为其BSD授权方式成为目前各个行业替换Oracle的可选开源关系型数据库之一。Oracle、MySQL的多版本并发控制基于回滚段模式,而PostgreSQL是通过在数据表中保留多版本而实现,适合于大量数据插入的场景,同时,能通过版本回收方法进行空间的回收,因此也能满足删除修改数据的性能需求。本研究通过分析对比三种数据库的并发控制算法,对各种场景下的并发操作和对性能的影响进行了测试。实验表明,除了OLAP应用外,PostgreSQL还适合DML操作较多,有时间窗口可以定期进行空间回收操作的联机事务处理系统(OLTP)系统。

In various industries,PostgreSQL database becomes one of the optional open source relational databases to replace the Oracle because of its BSD authorized way.The Multi-Version Concurrency Control mechanism of Oracle,Mysql is based on the rollback segment model.PostgreSQL is based on retention of multi-version in the table,and is suitable for the scene of insert large number of data.At the same time,it can reclaim space through method named VACUUM to meet the performance requirements on scenarios of data deletion or modification.Through the comparison and analysis of three kinds of Concurrent control algorithm of these database,tested the concurrent operation and the effects on Performance of various scenarios successfully.The experimental shows that PostgreSQL database is suitable for the OLTP system,which has much DML operation and the time window to do reclaim space operation,in addition to OLAP system.