3 key features in EDB PostgreSQL 15

EnterpriseDB (EDB) provides enterprise-class software and services that enable organizations to harness the full power of Postgres, the popular open source database. EDB’s contributions to the recent Postgres 15 release, most notably the introduction of the MERGE SQL command, demonstrate the company’s continuous commitment to the Postgres community and to innovation in Postgres.

EDB’s new release, EDB Tools and Extensions Release for PostgreSQL 15.1 (EDB PG 15), makes it easier than ever for enterprises to deploy Postgres as their enterprise database standard. With the largest number of new extensions and tools, this release helps enterprises build new, modern applications using the latest version of PostgreSQL.

With EDB PG 15, EDB supports PostgreSQL 15.1 wherever enterprises want to deploy, whether on-premises or in the cloud, self-managed or fully managed with EDB BigAnimal. The EDB PG 15 release also supports EDB Postgres for Kubernetes, which leverages CloudNativePG for speed, efficiency, and protection for infrastructure modernization.

Three notable features in EDB PG 15 further extend the open-source Postgres database. These are EDB Advanced Storage Pack, EDB Postgres Tuner, and EDB LDAP Sync. Let’s take a look.  

EDB Advanced Storage Pack

Comprised of Reference Data Storage Optimization and Auto Clustering Storage Optimization, EDB Advanced Storage Pack delivers faster access to clustered data as well as increased performance and scalability for foreign key relationships.

EDB is releasing its first two platform-agnostic storage engines, or Table Access Methods (TAMs) in PostgreSQL speak. The storage engines are designed to optimize how data is stored and accessed on disk depending on various use cases. No specialized hardware is needed, and the optimizations work whether you run your own servers or in the public cloud. The TAMs are delivered as extensions to the database, and EDB customers can take advantage of them on both community PostgreSQL and EDB Postgres Advanced Server.

EDB Auto Cluster and EDB Ref Data are the first two TAMs released, speeding acces to clustered data and optimizing foreign key relationships, respectively. EDB is developing more TAMs to optimize ingestion and storage for IoT, auditing, data logging, and event and process data workloads. Like EDB Auto Cluster and EDB Ref Data, these future extensions will not require special hardware or a particular cloud provider.

The EDB Auto Cluster TAM keeps track of the last inserted row for any value in a side table. This allows new rows to be added to the same data blocks as previous rows, thus keeping the data clustered and reducing access time to related data.

An example use case would be a Trades table where the application access pattern was to retrieve all the trades for a given stock symbol. Here the Auto Cluster TAM could be used to have the inserts store the rows of the given stock symbol in the same location of the database. This would require fewer pages to be accessed to retrieve all the trades, resulting in more efficient use of the database page cache and delivering results from the database to the application faster.

Below is the execution plan of the query highlighted by the independent Performance Engineering Team within EDB, with and without using the Auto Cluster TAM (system cache cleared before the execution).

With Auto Cluster:

 <code>
Limit&nbsp; (cost=27058.18..27058.43 rows=100 width=49) (actual time=67.934..67.952 rows=100 loops=1)
&nbsp;&nbsp; Buffers: shared hit=6 read=77
&nbsp;&nbsp; -&gt;&nbsp; Sort&nbsp; (cost=27058.18..27115.67 rows=22996 width=49) (actual time=67.932..67.942 rows=100 loops=1)
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sort Key: size DESC
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sort Method: top-N heapsort&nbsp; Memory: 48kB
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Buffers: shared hit=6 read=77
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Index Scan using i_file_user on file&nbsp; (cost=0.57..26179.28 rows=22996 width=49) (actual time=3.384..67.095 rows=5884 loops=1)
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond: (&quot;user&quot; = 667)
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Buffers: shared hit=3 read=77
&nbsp;Planning:
&nbsp;&nbsp; Buffers: shared hit=103 read=19
&nbsp;Planning Time: 10.887 ms
&nbsp;Execution Time: 68.836 ms
</code>

Without Auto Cluster:

 <code>
Limit&nbsp; (cost=7216.82..7217.07 rows=100 width=49) (actual time=3071.083..3071.104 rows=100 loops=1)
&nbsp;&nbsp; Buffers: shared hit=7 read=6059
&nbsp;&nbsp; -&gt;&nbsp; Sort&nbsp; (cost=7216.82..7232.15 rows=6134 width=49) (actual time=3071.081..3071.094 rows=100 loops=1)
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sort Key: size DESC
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sort Method: top-N heapsort&nbsp; Memory: 49kB
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Buffers: shared hit=7 read=6059
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Index Scan using i_file_user on file&nbsp; (cost=0.57..6982.38 rows=6134 width=49) (actual time=3.800..3068.449 rows=5988 loops=1)
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond: (&quot;user&quot; = 667)
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Buffers: shared hit=4 read=6059
&nbsp;Planning:
&nbsp;&nbsp; Buffers: shared hit=110 read=17
&nbsp;Planning Time: 8.473 ms
&nbsp;Execution Time: 3071.149 ms
</code>

Both execution plans are identical. There is a slight difference in the number of rows returned by the index scan (~1.74%), but we can see a big difference in buffers read: 77 vs. 6059. The execution time is decreased by 97.7% when using Auto Cluster.

Here is an example of how EDB Ref Data would work in practice.

 <code>
CREATE TABLE department (
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; department_id SERIAL PRIMARY KEY,
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; department_name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TEXT
) USING refdata;
CREATE TABLE employee (
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ...
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; department_id NOT NULL REFERENCES department(department_id)
);
</code>

The employee table is just a standard heap table; only the department table uses the Ref Data TAM. Inserts and updates of the employee table don’t take out row-level locks on the department table, thereby saving query time, avoiding the need to update the rows in the department table, and avoiding the need to write out the referred-to department table rows to disk and to the write-ahead log. 

EDB Postgres Tuner

EDB Postgres Tuner increases performance for users by drawing on 15-plus years of EDB Postgres tuning expertise for automated recommendations.

For over 15 years, EDB has supported customers running Postgres in mission-critical environments. EDB’s Performance Engineering Team runs a wide variety of real-world performance tests that feed into the recommendations that EDB Support provides to customers, into ideas for enhancing the core database server, and into performance deep dives like Harnessing Shared Buffers (and Reaping the Performance Benefits) as one example.

With EDB Postgres Tuner, EDB takes much of that experience, support, and performance research and delivers it to EDB customers as an extension. Postgres has nearly 350 configuration parameters, and while most rarely need to be adjusted, some directly affect the performance of a database and the ability to keep the database running optimally after years of data changes. This extension will dramatically reduce the overhead on DBAs for tuning PostgreSQL.  

EDB has separated distinct classes of configuration parameters, such as static parameters that only ever change if system hardware has changed, and dynamic configuration parameters that vary based on the activity in the database using algorithms developed at EDB.

EDB Postgres Tuner enables you to apply tuning recommendations automatically or to view the tuning recommendations and selectively apply them. Better suggestions will likely be given over time on a busy system with workload changes. For example, a Postgres parameter like checkpoint_completion_target will always have the same recommendation to ensure consistent I/O. In contrast, a parameter like max_wal_size balances keeping checkpoints timed while not running out of disk space. These two factors require knowing the latest conditions of the database server as described in detail in EDB’s article on tuning max_wal_size.

As noted in the article, max_wal_size can have a profound effect on performance. With EDB Postgres Tuner, it’s now possible to run the most write-intensive workloads against Postgres without having to know the details about performance-affecting parameters like max_wal_size.

EDB LDAP Sync

EDB LDAP Sync simplifies LDAP support for enterprises by eliminating the need to manage users in two places: the database and LDAP.

EDB works with many customers who authenticate database users with LDAP or Active Directory credentials. For Postgres authentication to happen against LDAP, the user must be manually added to the Postgres database. This means that, although authentication happens against a single source, users still have to be managed in both Postgres and LDAP.

EDB LDAP Sync is a collection of tools that removes the burden of managing users in the Postgres database by scheduling jobs in the database and calling the popular open source ldap2pg tool to create roles or users from LDAP based on the results of an LDAP search. This capability also guarantees a fast response if a user is removed from your organization by dropping that user from the database at the next scheduled run when their account is no longer in LDAP, which is often the source of truth for employees in a company.

Coming soon from EDB

After EDB PG 15, EDB’s next major release arrives in early 2023 with EDB Postgres Advanced Server (EPAS) and EDB Postgres Distributed. This release will include Transparent Data Encryption (TDE), a highly requested security feature that encrypts data at the database level, giving full control to the DBAs. TDE can aid in safeguarding confidential data and other cloud data assets from accidental exposure and unauthorized access by threat actors lacking the necessary decryption keys. This security feature will be especially beneficial for large businesses that have accelerated their cloud journey.

As one of the leading contributors to Postgres and the Postgres community, EDB is committed to driving technology innovation. With EDB PG 15, the new products and capabilities extending the database will support enterprises everywhere they want to use Postgres.

Adam Wright is the product manager of core database, extensions, and backup/restore at EDB.

—

New Tech Forum provides a venue to explore and discuss emerging enterprise technology in unprecedented depth and breadth. The selection is subjective, based on our pick of the technologies we believe to be important and of greatest interest to InfoWorld readers. InfoWorld does not accept marketing collateral for publication and reserves the right to edit all contributed content. Send all inquiries to newtechforum@infoworld.com.