The tech preview version of the pg_tde extension for PostgreSQL 16 was released on March 28th, with many improvements compared to our previous MVP release. In addition to lots of bug fixes, the new release supports multi-tenancy and key rotation with a simplified configuration mechanism. Note that the Tech Preview release is not recommended for […]

Learn how to ensure PCI DSS compliance in your PostgreSQL database with these 7 crucial considerations. The post 7 considerations for PCI DSS compliance in PostgreSQL appeared first on Stormatics.

In an era where data breaches are both costly and damaging to reputations, implementing best practices and robust security measures is crucial. PostgreSQL has emerged as one of the most successful and highly adopted open-source databases, competing with the enterprise standards of Oracle and SQL Server. Its increasing popularity is evident from DBEngine rankings, Stackoverflow […] The post Announcing PGDSAT to satisfy CIS benchmarks for PostgreSQL appeared first on HexaCluster.

有时，最小的补丁也会引起最大的讨论。PostgreSQL 社区（通常不是一个容易产生冗长、措辞激烈的大型讨论的群体）解决是否合并一个添加了新配置参数的简短补丁的问题，就是一个很好的例子。

UUID通常用作数据库表主键。它们易于生成，易于在分布式系统之间共享并保证唯一性。考虑到 UUID 的大小，这是否是一个正确的选择值得怀疑，但通常这不是由我们决定的。本文的重点不是“ UUID 是否是键的正确格式”，而是如何有效地使用UUID作为PostgreSQL的主键。用于 UUID 的 Postgres 数据类型UUID 可以看作是一个字符串，因此很容易将其存储为字符串。Postgres 有一种用于存储字符串的灵活数据类型：文本，它经常被用作存储 UUID 值的主键。这是一种正确的数据类型吗？肯定不是。Postgres 有一种专门用于 UUID 的数据类型：uuid。UUID 是 128

When data is everything, the sophistication of cybersecurity threats casts a shadow over the world of data security, including for those using PostgreSQL as their database of choice. Although renowned for its reliability, flexibility, and strong feature set, in the face of relentless cyber-attacks, even users of PostgreSQL can find themselves in a situation where […]

The first two articles in this series demonstrated how PostgreSQL is a capable tool for ELT – taking raw input and transforming it into usable data for querying and analyzing. We used sample data from the Advent of Code 2023 to demonstrate some of the ELT techniques in PostgreSQL. In the first article, we discussed functions and features of SQL in PostgreSQL that can be used together to transform and analyze data. In the second article, we introduced Common Table Expressions (CTE) as a method to build a transformation query one step at a time to improve readability and debugging. In this final article, I’ll tackle one last feature of SQL that allows us to process data in a loop, referencing previous iterations to perform complex calculations: Recursive CTE’s. SQL is Set-based SQL is primarily a set-based, declarative language. Using standard ANSII SQL and platform-specific functions, a SQL developer declares the desired outcome of a query, not the process by which the database should retrieve and process the data. The query planner typically uses statistics about the distribution of data to determine the best plan to get the desired result and return the full set of rows. While CTE’s and LATERAL joins make it feel like we can use the output of one query to impact another, those are always a one-shot opportunity. As a set-based language, it’s impossible to do algorithmic calculations, the ability to use the output of a query as input and control to another in a loop. Stated another way, early versions of the SQL standard did not have procedural capabilities. To do that, most database platforms use their own superset of SQL that provides procedural capabilities. By default, this is T-SQL in SQL Server and pl/pgsql in PostgreSQL. That changed with the SQL:1999 standard. With this new feature, implemented by all major databases, SQL became a Turing-complete language that can solve complex calculations in a single query. Recursive Common Table Expressions (aka. Hierarchical Queries) [...]

pg_tle - A Must-Know for DevelopersPostgreSQL is a fantastic database, packed with features. But sometimes, you need to add a little something extra – a custom function, a specialized data type, or maybe a procedure written in your favorite programming language. That's traditionally where PostgreSQL extensions come in. But for developers working with managed databases (like Amazon RDS),

What is Visibility? Simply put, the visibility refers to whether a row of data (Heap Tuple by default) should be displayed to the user in certain states, backend processes, or transactions. For example, A user changes a data record from ‘A’ to ‘B’ via UPDATE PostgreSQL handles this by INSERTing a new record call ‘B’ while marking record ‘A’ as ‘invisible’ Both records exist in the system, but ‘B’ is visible, while ‘A’ is not. A deleted or invisible record is also referred as a ‘dead’ tuple One of the responsibilities of VACUUM process is to clear these ‘dead’ tuples to free up space. MVCC (Multi-Version Concurrency Control) is a method in which each write operation creates a “new version” of the data while retaining the “old version”. This allows concurrent read and write operations without blocking each other. PostgreSQL uses a variant of MVCC, also called Snapshot Isolation to isolate concurrent transactions. So, it is possible that a single piece of data could have multiple “versions” of it, and it is PostgreSQL’s responsibility to determine which ‘version’ shall be presented to the user based on multiple factors. This act is also known as the “visibility check” or “visibility control” In this blog, we will dive into PostgreSQL’s visibility check mechanism to understand how it works. Visibility Indicators To my knowledge today, PostgreSQL determines a data’s visibility based on the following indicators: Transaction ID xmin xmax cid transaction snapshot CLOG (Commit Log) hintbit what exactly are these? Let’s find out. transaction ID This should be self-explanatory. All transactions in PostgreSQL are associated with a ID number called transaction ID. This is mainly used to check if a data in question is inserted or deleted in the current transaction. We will explore more on this in the later part of the blog. xmin, xmax, cid and hintbit These indicators are categorized together because they are all [...]