性能调优 PostgreSQL
请记住，我是 sql/数据库领域的菜鸟。
我每秒插入/更新数千个对象。 这些对象会以多秒的间隔被主动查询。
我应该做哪些基本事情来调整我的（postgres）数据库的性能？
Keep in mind that I am a rookie in the world of sql/databases.
I am inserting/updating thousands of objects every second. Those objects are actively being queried for at multiple second intervals.
What are some basic things I should do to performance tune my (postgres) database?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
这是一个广泛的主题，因此这里有很多内容供您阅读。
当您用尽这些选项时：添加更多内存、更快的磁盘子系统等。硬件很重要，尤其是在较大的数据集上。
当然，请阅读 postgres/databases 上的所有其他线程。 :)
It's a broad topic, so here's lots of stuff for you to read up on.
And when you've exhausted those options: add more memory, faster disk-subsystem etc. Hardware matters, especially on larger datasets.
And of course, read all the other threads on postgres/databases. :)
首先也是最重要的，阅读官方手册的性能提示。
对所有查询运行 EXPLAIN 并了解其输出将让您了解您的查询是否尽可能快，以及是否应该添加索引。
完成此操作后，我建议您阅读服务器配置< /a> 手册的一部分。 有许多选项可以进行微调以进一步提高性能。 但请务必了解您设置的选项，因为如果设置不正确，它们也很容易影响性能。
请记住，每次您更改查询或选项时，都要测试和基准，以便了解每次更改的效果。
First and foremost, read the official manual's Performance Tips.
Running EXPLAIN on all your queries and understanding its output will let you know if your queries are as fast as they could be, and if you should be adding indexes.
Once you've done that, I'd suggest reading over the Server Configuration part of the manual. There are many options which can be fine-tuned to further enhance performance. Make sure to understand the options you're setting though, since they could just as easily hinder performance if they're set incorrectly.
Remember that every time you change a query or an option, test and benchmark so that you know the effects of each change.
实际上，有一些简单的规则可以让您在大多数情况下获得足够的性能：
索引是第一部分。 主键会自动建立索引。 我建议在所有外键上放置索引。 进一步在所有经常查询的列上建立索引，如果一个表上有大量查询，并且查询了多个列，则将这些列一起建立索引。
postgresql 安装中的内存设置。 将以下参数设置得更高：
.
如果是专用数据库机器，您可以轻松地将其中的前 3 个设置为 ram 的一半（在具有共享缓冲区的 linux 下要小心，也许您必须调整 shmmax 参数），在任何其他情况下，这取决于有多少 ram你想给 postgresql.
http://www.postgresql.org/docs/8.3/交互式/runtime-config-resource.html
Actually there are some simple rules which will get you in most cases enough performance:
Indices are the first part. Primary keys are automatically indexed. I recommend to put indices on all foreign keys. Further put indices on all columns which are frequently queried, if there are heavily used queries on a table where more than one column is queried, put an index on those columns together.
Memory settings in your postgresql installation. Set following parameters higher:
.
If it is a dedicated database machine you can easily set the first 3 of these to half the ram (just be carefull under linux with shared buffers, maybe you have to adjust the shmmax parameter), in any other cases it depends on how much ram you would like to give to postgresql.
http://www.postgresql.org/docs/8.3/interactive/runtime-config-resource.html
http://wiki.postgresql.org/wiki/Performance_Optimization
http://wiki.postgresql.org/wiki/Performance_Optimization
我推荐的绝对最小值是 EXPLAIN ANALYZE 命令。 它将显示子查询、连接等的详细信息，始终显示操作中消耗的实际时间量。 它还会提醒您顺序扫描和其他令人讨厌的麻烦。
这是最好的开始方式。
The absolute minimum I'll recommend is the EXPLAIN ANALYZE command. It will show a breakdown of subqueries, joins, et al., all the time showing the actual amount of time consumed in the operation. It will also alert you to sequential scans and other nasty trouble.
It is the best way to start.
如果你信任你的文件系统，请将 fsync = off 放入你的 posgresql.conf 中，否则每个 postgresql 操作将立即写入磁盘（使用 fsync 系统调用）。
十多年来，我们在许多生产服务器上都关闭了这个选项，并且从未发生过数据损坏。
Put fsync = off in your posgresql.conf, if you trust your filesystem, otherwise each postgresql operation will be imediately written to the disk (with fsync system call).
We have this option turned off on many production servers since quite 10 years, and we never had data corruptions.