标签

 postgresql 

相关的文章:

Planet PostgreSQL -

Keith Fiske: Five Great Features of the PostgreSQL Partition Manager

After much testing and work the PostgreSQL Partition Manager, pg_partman, version 5 is now available for public release. Thanks to everyone involved for helping me get here! My recent post discusses many of the big changes, so please see that post or the CHANGELOG for a full summary of version 5. What I'd like to do today is take a step back and review five notable features that make pg_partman an important tool for managing large tables in PostgreSQL: Retention Background Worker Additional Constraint Exclusion Epoch Partitioning Template Table Retention One of the primary reasons to partition tables in PostgreSQL is to avoid the potentially very heavy overhead associated with deleting many rows in large batches. PostgreSQL's MVCC system means that when you delete rows, they're not actually gone until the VACUUM system comes through to mark them as reusable space for new rows. Even then that does not immediately (and may likely never) return the space to the file system. Vacuuming such large volumes of old rows can put a heavy strain on an already stressed system, which is what you may have been trying to alleviate by removing those rows. Instead of running delete statements to remove your old data, partitioning let's you slice the table up and then simply drop the old partitions that have data you don't need in the database anymore. This avoids the need to vacuum to clean up old rows and, more importantly, immediately returns that space to the file system with very little overhead. Another thing to note here is that if retention is your reason for partitioning, you don't have to make your partition interval very small. It just has to be enough to cover your retention policy for how much data you need to keep. If your policy is 30 days, you can try setting the partition interval to monthly. That doesn't necessarily mean you'll only have one month of data around (Ex. February). But it does meet your retention policy of at least 30 days, so you might just be keeping[...]

AI生成摘要 PostgreSQL Partition Manager的最新版本pg_partman 5发布,具有五个显著特点,包括保留、后台工作程序、额外约束排除、时代分区和模板表。这个扩展系统可以为PostgreSQL带来强大的功能。

相关推荐 去reddit讨论

Planet PostgreSQL -

Syed Salman Ahmed Bokhari: Setting up PostgreSQL cluster using pg_cirrus on AWS EC2 Instances

This blog outlines how to set up a 3-node HA PostgreSQL cluster on AWS EC2 Instances using pg_cirrus, an automated tool for this purpose. The post Setting up PostgreSQL cluster using pg_cirrus on AWS EC2 Instances appeared first on Stormatics.

AI生成摘要 本文介绍如何在AWS EC2实例上设置pg_cirrus。首先需要登录AWS控制台并创建安全组,然后启动EC2实例并设置网络和存储。接下来需要在所有实例上创建postgres用户并授予无密码sudo权限。在pgpool节点上,需要更新apt软件包管理器、安装Ansible、创建SSH密钥对、克隆pg_cirrus存储库、创建vault.yml文件并执行pg_cirrus。最后,可以通过连接到pgpool节点并运行tail命令来查看pgpool日志。

相关推荐 去reddit讨论

Planet PostgreSQL -

Chris Ellis: PGSQL Phriday 013 - Usecases and Why PostgreSQL

I've always been much more into the practical side of engineering. Caring far more about what I can build with tools, rather than which tool I'm using. On Friday, October 6th 2023, publish a post on your blog telling a story about what you (or your team, client) built with PostgreSQL and how PostgreSQL helped you deliver. I'd love to read about the weird and varied things that people are using PostgreSQL for. If you think your usecase is boring, I'm sure it will be of use to someone. Plus you can always focus more on the story and how PostgreSQL helped to deliver a project, or could have, or didn't!

AI生成摘要 本文介绍了作者对于 PostgreSQL 的使用和喜爱,并呼吁大家分享自己使用 PostgreSQL 的实际案例和故事。作者认为,好的工程需要使用合适的工具,而 PostgreSQL 是一个非常多样化和灵活的工具,可以应用于各种场景。作者希望大家分享自己使用 PostgreSQL 的经验和教训,让更多人了解 PostgreSQL 的优势和应用。同时,作者也鼓励大家参加 PostgreSQL 社区的活动和分享,分享自己的经验和见解。

相关推荐 去reddit讨论

Percona Database Performance Blog -

PostgreSQL Partitioning Made Easy Using pg_partman (TimeBased)

PostgreSQL Partition Manager — ‘pg_partman’ — is an open source extension freely available, widely supported, and actively maintained by the PostgreSQL community.pg_partman creates and manages both time-based and serial-based table partition sets. Users use it to automate and create partitions by breaking large tables into smaller partitions for better performance.In this post, initially, we will […]

AI生成摘要 本文介绍了使用 PostgreSQL 分区管理器 pg_partman 进行分区管理的方法,包括创建模式、使用本地分区和模板表、创建子分区、转移数据和自动维护分区表。pg_partman 可以提高查询性能和 VACUUM 效率,但只支持整数或日期/时间戳数据类型的分区。

相关推荐 去reddit讨论

Planet PostgreSQL -

Hans-Juergen Schoenig: Monitoring Performance for PostgreSQL with Citus

In the fast-paced world of data management, scalability rules supreme and Citus plays an ever greater role. The question now is: How can we see what is going on inside Citus? How can businesses leverage monitoring technology to optimize their PostgreSQL database performance? Monitoring PostgreSQL and Citus Database monitoring is important regardless of the extension you are using. The best way to find performance bottlenecks in PostgreSQL is definitely to use pg_stat_statements. I’ve written about detecting PostgreSQL performance problems in the past. pg_stat_statements is the single most powerful tool to spot slow queries and to ensure that you have all the insights you need to actually react to bad performance. However, if Citus comes into the picture, there is a bit more you have to know in order to inspect performance problems. Configuring Citus performance monitoring Enabling pg_stat_statements should be done on any PostgreSQL database deployment. The performance overhead is basically non-existent and therefore pg_stat_statements is truly a must. When Citus enters the picture, we have to change some additional variables. The most important one is citus.stat_statements_track, which should be set to all. Here’s how it works: postgres=# ALTER SYSTEM SET citus.stat_statements_track TO 'all'; ALTER SYSTEM postgres=# SELECT pg_reload_conf(); pg_reload_conf ---------------- t (1 row) After reconnecting to the database, we’ll be able to see information. In general, we also recommend turning on track_io_timing in PostgreSQL to gain some more insights about the time needed to perform I/O operations: postgres=# ALTER SYSTEM SET track_io_timing TO on; ALTER SYSTEM postgres=# SELECT pg_reload_conf(); pg_reload_conf ---------------- t (1 row) A restart is not needed in this context. Sending a signal (“reload”) is enough for all new connections coming in. Checking Citus performance data When Citus is enabled in your platform, you can call a set returning function w[...]

AI生成摘要 文章介绍了如何监控 PostgreSQL 和 Citus 数据库的性能,以及如何配置 Citus 性能监控。作者建议使用 pg_stat_statements 工具来检测 PostgreSQL 性能问题,并介绍了 citus.stat_statements_track 和 track_io_timing 等变量的设置方法。文章还介绍了如何检查 Citus 性能数据,并优化监控配置。最后,作者提醒读者订阅他们的新闻通讯或关注他们的社交媒体账号以获取 PostgreSQL 的重要更新。

相关推荐 去reddit讨论

Planet PostgreSQL -

David Z: Exploring Various Ways to Manage Configuration Parameters in PostgreSQL

1. Overview PostgreSQL provides a configuration file postgresql.conf for end users to customize parameters. You may need to change some parameters to tune performance or deploy a PostgreSQL server in your working environment. In this blog post, we’ll explore different ways to manage these parameters. 2. Managing Parameters in Different Ways PostgreSQL supports various parameters that allow users to customize behavior either globally (for the entire cluster) or locally (for one particular session, database, etc.). These parameters are mainly divided into five types: boolean, string, integer, floating point, and enum. Additionally, all parameter names are case-insensitive. Now, let’s explore how to manage parameters in four different ways with examples. 2.1. Changing Parameters in the Configuration File This is a simple way to customize the server and is commonly used by most users when first working with PostgreSQL. For example, if you want to start PostgreSQL on port 5433 instead of the default port 5432, you can either uncomment the line #port = 5432 in the postgresql.conf file, changing 5432 to 5433, or simply append a new line port = 5433 to the end of the file. Keep in mind, PostgreSQL will always use the last value found in the configuration file if there are duplicated configurations for the same parameter. 2.2. Changing Parameters via SQL PostgreSQL provides three SQL commands to change parameters in different scopes: ALTER SYSTEM, ALTER DATABASE, and ALTER ROLE. ALTER SYSTEM changes the global default settings and persists them into postgresql.auto.conf. These changes will be applied on the next start. Here is an example to change the log message output leve using ALTER SYSTEM: postgres=# ALTER SYSTEM SET log_min_messages='debug2'; ALTER SYSTEM postgres=# show log_min_messages; log_min_messages ------------------ warning (1 row) After restarting the PostgreSQL server, if you tail the log messages and manually issue a CHECKPOINT, you should see the me[...]

AI生成摘要 本文介绍了四种不同的方法来管理PostgreSQL服务器的参数配置,包括通过配置文件、SQL命令、Shell命令和外部文件来修改参数。文章详细介绍了每种方法的具体操作步骤,并提供了相应的示例。通过本文的介绍,读者可以更好地管理PostgreSQL服务器的配置参数,并在运行时进行故障排除。

相关推荐 去reddit讨论

Planet PostgreSQL -

Paul Ramsey: Rolling the Dice with the PostgreSQL Random Functions

Generating random numbers is a surprisingly common task in programs, whether it's to create test data or to provide a user with a random entry from a list of items. PostgreSQL comes with just a few simple foundational functions that can be used to fulfill most needs for randomness. Almost all your random-ness needs will be met with the random() function. Uniformity The random() function returns a double precision float in a continuous uniform distribution between 0.0 and 1.0. What does that mean? It means that you could get any value between 0.0 and 1.0, with equal probability, for each call of random(). Here's five uniform random numbers between 0.0 and 1.0. SELECT random() FROM generate_series(1, 5) 0.3978842227698167 0.7438732417540841 0.3875091442400458 0.4108009373061563 0.5524543763568912 Yep, those look pretty random! But, maybe not so useful? Random Numbers Most times when people are trying to generate random numbers, they are looking for random integers in a range, not random floats between 0.0 and 1.0. Say you wanted random integers between 1 and 10, inclusive. How do you get that, starting from random()? Start by scaling an ordinary random() number up be a factor of 10! Now you have a continuous distribution between 0 and 10. SELECT 10 * random() FROM generate_series(1, 5) 3.978842227698167 7.438732417540841 3.875091442400458 4.108009373061563 5.5245437635689125 Then, if you push every one of those numbers up to the nearest integer using ceil() you'll end up with a random integer between 1 and 10. SELECT ceil(10 * random()) FROM generate_series(1, 5) 4 8 4 5 6 If you wanted a random integer between 0 and 9, you could do the same thing, but pushing the floating numbers down to the next lowest integer using floor(). SELECT floor(10 * random()) FROM generate_series(1, 5) 3 7 3 4 5 Random Rows and Values Sometimes the things you are trying to do randomly aren't numbers. How do you get a random entry out of a string? [...]

AI生成摘要 本文介绍了在PostgreSQL中生成随机数的方法。使用random()函数可以生成0到1之间的均匀分布的随机数,通过对其进行缩放和取整可以生成指定范围内的随机整数。此外,还介绍了如何从数组中随机选择一个元素,以及如何从表中随机选择一行。文章还介绍了如何生成正态分布的随机数,并解释了PostgreSQL使用的伪随机数生成器的工作原理。

相关推荐 去reddit讨论

Planet PostgreSQL -

cary huang: All PostgreSQL Replication Explained – How to Do Them Right.

PostgreSQL Streaming Replication is a crucial feature that enhances availability, performance and data integrity of your database. It is a fundamental component in modern database deployment. Replication refers to a technique that copies data and state from one database instance to another. PostgreSQL supports 2 major replication techniques: Streaming replication (also known as physical replication) Logical replication In this blog, I will explain the purposes of both replication techniques based on PostgreSQL 16 and how to properly set them up with the right examples. So, let’s begin. PostgreSQL Streaming Replication Streaming replication replicates entire database in real-time, which consists of One primary node – can read from and write to database One or more standby nodes (also called replica nodes. Please do not call them slave nodes) – can only read from database The replication works by having the primary node send new WAL segments (when a change is made) to all standby nodes. The standby nodes can replicate primary’s database state and data simply by replaying the WAL segments (also called REDO). Why PostgreSQL Streaming Replication? High availability – if the primary node is down, a standby node can promote to be the new primary and continue database operation. Patroni is a popular orchestration tool for this purpose. Data redundancy – all nodes store copies of the same data. Load balancing – application can distribute read requests among all standby nodes and send write requests only to primary node. Pgpool is a popular orchestration tool for this purpose. Scalability – more standby nodes can be added to distribute higher loads of read requests. PostgreSQL Streaming Replication Setup Example The example below uses default database postgres and default username $USER to set up the replication. If you would like to use it on another user and database, you will have to update pg_hba.conf as well. [...]

AI生成摘要 本文介绍了PostgreSQL的两种主要复制技术:流复制和逻辑复制。流复制可以实时复制整个数据库,适用于高可用性、数据冗余、负载均衡和可扩展性。逻辑复制可以选择性地复制数据,适用于跨平台复制、数据聚合和更改跟踪。本文提供了两种复制技术的设置示例。流复制示例包括初始化数据库集群、创建主节点和从节点、创建复制槽和订阅对象。逻辑复制示例包括创建发布和订阅对象、创建逻辑复制插件和使用pg_recvlogical命令订阅更改。

相关推荐 去reddit讨论

Planet PostgreSQL -

Umair Shahid: Incremental Sort in PostgreSQL: A Developer’s Guide

By following best practices and query optimization techniques, you can leverage incremental sort in PostgreSQL and enhance query performance. The post Incremental Sort in PostgreSQL: A Developer’s Guide appeared first on Stormatics.

AI生成摘要 PostgreSQL 13 introduced "incremental sort", a feature that allows sorting to be done incrementally during query execution. This feature reduces memory usage, speeds up query execution, and improves resource utilization. Developers don't need to take any explicit action to use incremental sort, but they can optimize their queries, maintain proper indexing, analyze tables, use the ORDER BY clause, consider LIMIT and OFFSET, monitor query performance, use a recent version of PostgreSQL, and ensure adequate hardware resources. By doing so, developers can create an environment where PostgreSQL is more likely to employ incremental sort to enhance query performance.

相关推荐 去reddit讨论

Planet PostgreSQL -

Pavlo Golub: Announcing pgxmock v3 - Elevating Mocking for PostgreSQL in Go!

I am thrilled to introduce the much-anticipated major release of pgxmock v3! This release represents a significant leap forward in enhancing your PostgreSQL database mocking experience in Go. Here’s a quick overview of the exciting changes and additions in this release: High-Priority New Features Rewrite with findExpectationFunc() I’ve completely overhauled the internals of pgxmock by rewriting all methods using findExpectationFunc(). This improvement allows us to add even more custom expectations without writing a duplicate code. The only thing we will need is a custom callback function to compare a new kind of expectation. Enhanced Expectations I’ve expanded the capabilities of all expectations with new methods like WillDelayFor(), WillReturnError(), and WillPanic(). Now you have even more control over how your mock database behaves in various scenarios. Introducing Times() and Maybe() I’m introducing the Times() method, allowing you to specify how many times an expectation should be met. This feature enhances the flexibility of your mock database interactions. Meanwhile Maybe() method allows the expectation to be optional. Not calling an optional method will not cause an error while asserting expectations. New Features Improved Test Coverage I’ve extended test coverage, ensuring that pgxmock remains a reliable choice for your testing needs. To make the testing even more comprehensive, I’ve added several new test cases, e.g., TestRowsConn() and TestPanic(). These scenarios cover a wider range of potential use cases. They will help us to eliminate possible bugs in the future. Keeping Up with the Times I’m staying up-to-date with the latest developments. In this release, I’ve bumped up the Go version to v1.21, ensuring compatibility and leveraging the newest features of the language. Introducing CallModifier Interface I’ve introduced the CallModifier interface to expectations, providing greater control and customization when working with mock databas[...]

AI生成摘要 Pgxmock v3 is a major release that enhances the PostgreSQL database mocking experience in Go. It includes new features such as enhanced expectations, Times() and Maybe() methods, and improved test coverage. The CallModifier interface is also introduced to provide greater control and customization when working with mock database calls. Users are encouraged to upgrade to take advantage of these enhancements and improvements.

相关推荐 去reddit讨论