PostgreSQL 在大型系统上的 扩展性 不佳，主要是因为基于进程的模型都消耗了资源。 并非所有数据库都有这个问题，如果不进行某种重大的 架构 更改，PostgreSQL 就无法停止这个问题。 PostgreSQL 数据库系统，它的历史可以追溯到1986 年。对具有如此悠久历史的大型代码库进行根本性更改从来都不是一件容易的事。随着根本性变化的发生，使 PostgreSQL 摆脱面向过程的模型并不是一件小事，但该项目正在认真考虑这一变化。 PostgreSQL 实例作为一大组协作进程运行，其中每个连接的客户端都有一个进程。这些进程使用复杂的库通过多个共享内存区域进行通信，该库允..

对于最终用户来说，物化视图基本上只是一个表，物化视图只是将结果 缓存 在磁盘上，这样就不需要每次都运行底层查询。 您可以使用类似的方法为分析师设置一些历史销售数据，他们不需要实时信息，只需要最近 5 年的销售额。它可能会占用大量磁盘空间，但最终与对生产数据运行实时查询相比，这在服务器上会更容易。 以下几个是物化视图几个特点： 查询优化 - 对于物化视图，由于数据是预先计算和存储的，优化器可以直接使用它，并且某些查询可以更快。 写入操作可能会增加开销 - 物化视图通常是只读的，基于数据快照。因此，对于提交刷新，将基表的更改同步到 mview 通常比动态视图更复杂。但是，如果您不..

Writing queries to retrieve the data from a database is probably the single most common task when it comes to working with data. Working with data in PostgreSQL is no exception. Further, PostgreSQL has an incredibly rich, wide, and varied set of mechanisms for retrieving data. From standard SELECT… FROM… WHERE to windowing functions and recursive queries, PostgreSQL has it all. I honestly can’t do it justice in a single article. Further, since so much of this functionality is effectively identical to where I’m more comfortable, SQL Server, I’m not turning this into a PostgreSQL 101 on the SELECT statement. Instead, for this series, I’m just going to assume I may have more than one article on querying PostgreSQL. For this entry in the series, I’m going to focus on the core behaviors of SELECT, FROM and WHERE with an emphasis on what’s different from SQL Server. This won’t be a fundamental how-to on querying PostgreSQL, but instead an exploration of the gotchas you’re likely to experience coming in with existing knowledge of how you think these things should work. And hoo boy, there’s some fun stuff in there. Let’s get stuck in. In the sample database I’ve created as a part of this ongoing series, I created a couple of schemas and organized my tables within them. If you wish to execute the code or look at the data structures, the code is in my ScaryDBA/LearningPostgreSQL repository here. The objects and database you will need can be created/reset using the CreateDatabase.sql script, then adding sample data using the SampleData.sql script. After executing that script, execute the Sample The rest of the code in this article is in the  10_Select folder. FROM I actually love how the PostgreSQL document defines what you’re doing in the FROM clause: Trivial table expressions simply refer to a table on disk, a so-called base table, but more complex expressions can be used to modify or combine base tables in various ways. While I wouldn’t myself define it this way, I find it to be interes[...]

It is very common to see many infrastructure layers standing between a PostgreSQL database and the Application server.  The most common ones are connection poolers, load balancers, routers, firewalls, etc.  We often forget or take for granted the network hops involved and the additional overhead it creates on the overall performance. But it could cause severe performance penalties in many cases and overall throughput deterioration. I have been trying to get a good estimate of this overhead for quite some time. Previously I had written about how the volume of data transmission as part of SQL execution, as well as the cursor location, affects the overall performance. Meanwhile, Hans-Jürgen Schönig’s presentation, which brought up the old discussion of Unix socket vs. TCP/IP connection, triggered me to write about other aspects of network impact on performance. He demonstrated a specific case of a 2x performance degradation while using TCP/IP connection. How to detect and measure the impact There is no easy mechanism for measuring the impact of network overhead. But a very close analysis of wait_events from pg_stat_activity can tell us the story as closely as possible. So we should be sampling the wait events. Many methods exist for wait-event sampling, including extensions. But I prefer not to install special tools or extensions on the user environment for the wait event sampling. At Percona Support, we use pg_gather as the method to collect and study the wait events because it is a standalone SQL script and doesn’t need to install anything on the database systems. It is designed to be very lightweight as well. There will be 2,000 samples collected per session. pg_gather analysis report can show wait events and other information associated with each session. But I will be discussing and highlighting only the wait events portion of it in this blog while going through different types types of workloads and how network performance shows up in wait events. Case 1: Query retrieving a large[...]

It is very common to see many infrastructure layers standing between a PostgreSQL database and the Application server.  The most common ones are connection poolers, load balancers, routers, firewalls, etc.  We often forget or take for granted the network hops involved and the additional overhead it creates on the overall performance. But it could cause […]

I have a confession to make: I'm a huge fan of Oracle. I absolutely adore the company and its technology. Admittedly, I'm not fond of their pricing practices, but overall, they offer excellent products. As an Oracle DBA for a major energy infrastructure company in California, I've spent years mastering Oracle databases. However, a recent decision from our executive team prompted a transition to PostgreSQL. This shift transformed me from an experienced Oracle DBA into a newcomer in the world of PostgreSQL. Having navigated through this migration, I feel compelled to share the insights I've gained along the way. This article details my journey, the challenges encountered, and the seven indispensable tools that facilitated this transition. My hope is that sharing these experiences will make your journey to PostgreSQL smoother. Overview of the Migration Process Migrating from Oracle to PostgreSQL isn't just a flip of a switch. It's a journey with a bunch of steps like schema conversion, data migration, application migration, and performance tuning. Each stage had its own hiccups, and I needed a toolbox of solutions to handle them. Ora2Pg Ora2Pg was my first ally. It's an open-source tool that converts Oracle database schemas into PostgreSQL format. As Ora2Pg is an open source project, you can see the popularity of the tool: Stars Commits Number of releases Used By License Lead contributor Ora2Pg 890 2,775  22 n/a GPL-3.0 license Gilles Darold Pros: Can handle a ton of Oracle objects Configurable through configuration files Limitations: Complex PL/SQL conversions might need manual intervention Large databases can take a long time to convert [...]

In the world of modern DevOps, deployment automation tools have become essential for streamlining processes and ensuring consistent, reliable deployments. GitOps and ArgoCD are at the cutting edge of deployment automation, making it easy to deploy complex applications and reducing the risk of human error in the deployment process. In this blog post, we will explore how to deploy the Percona Operator for PostgreSQL v2 using GitOps and ArgoCD. The setup we are looking for is the following: Teams or CICD roll out the manifests to Github ArgoCD reads the changes and compares the changes to what we have in Kubernetes ArgoCD creates/modifies Percona Operator and PostgreSQL custom resources Percona Operator takes care of day-1 and day-2 operations based on the changes pushed by ArgoCD to custom resources Prerequisites: Kubernetes cluster GitHub repository. You can find my manifests here. Start it up Deploy and prepare ArgoCD ArgoCD has quite detailed documentation explaining the installation process. I did the following: kubectl create namespace argocd kubectl apply -n argocd -f https://raw.githubusercontent.com/argoproj/argo-cd/stable/manifests/install.yaml Expose the ArgoCD server. You might want to use ingress or some other approach. I’m using a Load Balancer in a public cloud: kubectl patch svc argocd-server -n argocd -p '{"spec": {"type": "LoadBalancer"}}' Get the Load Balancer endpoint; we will use it later: kubectl -n argocd get svc argocd-server NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE argocd-server LoadBalancer 10.88.1.239 123.21.23.21 80:30480/TCP,443:32623/TCP 6h28m I’m not a big fan of Web User Interfaces, so I took the path of using argocd CLI. Install it by following the CLI installation documentation. Retrieve the admin password to log in using the CLI: argocd admin initial-password -n argocd Login to the server. Use the Load Balancer e[...]

In the world of modern DevOps, deployment automation tools have become essential for streamlining processes and ensuring consistent, reliable deployments. GitOps and ArgoCD are at the cutting edge of deployment automation, making it easy to deploy complex applications and reducing the risk of human error in the deployment process. In this blog post, we will explore how […]

This blog is about the CREATEROLE privilege and the new enhancements in PostgreSQL 16, part of the fine access controls for database security The post Understanding the CREATEROLE Privilege in PostgreSQL appeared first on Stormatics.

What if you could automatically shard your PostgreSQL database across any number of servers and get industry-leading performance at scale without any special data modelling steps? Our latest Citus open source release, Citus 12, adds a new and easy way to transparently scale your Postgres database: Schema-based sharding, where the database is transparently sharded by schema name. Schema-based sharding gives an easy path for scaling out several important classes of applications that can divide their data across schemas: Multi-tenant SaaS applications Microservices that use the same database Vertical partitioning by groups of tables Each of these scenarios can now be enabled on Citus using regular CREATE SCHEMA commands. That way, many existing applications and libraries (e.g. django-tenants) can scale out without any changes, and developing new applications can be much easier. Moreover, you keep all the other benefits of Citus, including distributed transactions, reference tables, rebalancing, and more. In this blog post, you’ll get a high-level overview of schema-based sharding and other new Citus 12 features: What is schema-based sharding? How to use Citus schema-based sharding for Postgres Benefits of schema-based sharding Choosing a sharding model for multi-tenant applications (schema-based vs. row-based) Migrating an existing schema-per-tenant application to Citus MERGE improvements Even more details available in the release notes on the 12.0 Updates page. And if you want to see demos of some of this functionality, be sure to join us for the livestream of the Citus 12.0 release party on Wed 02 Aug (mark your calendar and join us. Let’s dive in! What is schema-based sharding? Schema-based sharding means that tables from the same schema are placed on the same node, while different schemas may be on different nodes. That way, queries and transactions that involve a single schema can always be evaluate[...]