Postgresql与MySQL:它们的数据大小如何相互比较? [英] Postgresql vs. MySQL: how do their data sizes compare to each other?

问题描述

对于相同的数据集(主要是文本数据)，Postgresql的数据(表+索引)大小与MySQL相比如何?

For the same data set, with mostly text data, how do the data (table + index) size of Postgresql compared to that of MySQL?

• Postgresql使用MVCC，这表明其数据大小会更大

• Postgresql uses MVCC, that would suggest its data size would be bigger

在此演示中，日本最大的博客网站讨论了它们从Postgresql到MySQL的迁移.他们离开Postgresql的原因之一是Postgresql中的数据大小太大(第41页): 从日本最大的博客社区Cocolog上的PostgreSQL到MySQL

In this presentation, the largest blog site in Japan talked about their migration from Postgresql to MySQL. One of their reasons for moving away from Postgresql was that data size in Postgresql was too large (p. 41): Migrating from PostgreSQL to MySQL at Cocolog, Japan's Largest Blog Community

Postgresql具有数据压缩功能，因此应使数据大小更小.但是MySQL插件也有压缩功能.

Postgresql has data compression, so that should make the data size smaller. But MySQL Plugin also has compression.

是否有人对Postgresql&的数据大小有任何实际经验? MySQL彼此比较吗?

Does anyone have any actual experience about how the data sizes of Postgresql & MySQL compare to each other?

推荐答案

• MySQL也使用MVCC，只需检查一下 innoDB.但是，在PostgreSQL中，您可以 更改FILLFACTOR以腾出空间 以便将来进行更新.有了这个，你 可以创建一个有空间的数据库 对于当前数据，但对于某些数据 将来的更新和删除.什么时候 autovacuum和HOT做他们的事情 是的，数据库的大小可以 保持稳定.
• 博客是关于旧版本的，很多 发生了变化，PostgreSQL 在压缩方面做得更好 就像过去一样.
• 压缩取决于数据类型， 配置和速度也是如此.你 必须进行测试以查看其工作方式 针对您的情况.
• MySQL uses MVCC as well, just check innoDB. But, in PostgreSQL you can change the FILLFACTOR to make space for future updates. With this, you can create a database that has space for current data but also for some future updates and deletes. When autovacuum and HOT do their things right, the size of your database can be stable.
• The blog is about old versions, a lot of things have changed and PostgreSQL does a much better job in compression as it did in the old days.
• Compression depends on the datatype, configuration and speed as well. You have to test to see how it's working for you situation.

我做了几次从MySQL到PostgreSQL的转换，在所有这些情况下，PostgreSQL都缩小了大约10％(MySQL 5.0 => PostgreSQL 8.3和8.4).这10％用于更改最新表上的填充因子，将填充因子设置为60到70.速度要好得多(并发用户超过20个也不会出现问题)，数据大小也很稳定，没有MVCC失去控制或真空到远远落后的地方.

I did a couple of conversions from MySQL to PostgreSQL and in all these cases, PostgreSQL was about 10% smaller (MySQL 5.0 => PostgreSQL 8.3 and 8.4). This 10% was used to change the fillfactor on the most updated tables, these were set to a fillfactor 60 to 70. Speed was much better (no more problems with over 20 concurrent users) and data size was stable as well, no MVCC going out of control or vacuum to far behind.

MySQL和PostgreSQL是两个不同的野兽，PostgreSQL完全是关于MySQL流行的可靠性.

MySQL and PostgreSQL are two different beasts, PostgreSQL is all about reliability where MySQL is populair.

这篇关于Postgresql与MySQL:它们的数据大小如何相互比较?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！