PostgreSQL Enterprise Server (PgES) provides more features compares to the community version of PostgreSQL:
Advanced Rollback: PgES permanently maintains a log of all operations performed on data files. This log is called WAL (Write-Ahead Log) and exists mainly to ensure incident tolerance. If the database suffers an incident, its state can be consistently restored to a previous point in time [Point-in-Time Recovery (PITR)] without the need to restore from a backup, take periodic snapshots, or the maintenance by user procedures of some copies of the data. PgES allows data to be restored at the transaction, table or database level using the PostgreSQL WAL.
Advanced Audit: PgES allows detailed auditing of all operations performed by clients within the database using the pgAudit extension. The following audit types are available:
READ for auditing SELECT and COPY queries.
WRITE: for auditing INSERT, UPDATE, DELETE, TRUNCATE, and COPY queries
FUNCTION: for auditing stored procedures and functions.
ROLES: for auditing queries related to roles and privileges: GRANT, REVOKE, CREATE/ALTER/DROP ROLE.
DDL: for the audit of DDL operations
MISC: for auditing other types of operations such as DISCARD, FETCH, CHECKPOINT, VACUUM, SET.
MISC_SET: for auditing SET type operations, e.g. ROLLER SET.
ALL: includes all of the above
The audit can be active at the level of the entire database, at the level of a database object, at the level of a role or at the level of a user's session. Additional filters can be configured to audit only certain scenarios such as a certain user, a certain IP, client or accessing certain information from a table.
Change history: PgES natively offers a mechanism for recording all changes made to the data or structure of a table of tables. Activation of the recording is done using the syntax
SELECT history.record_table('target_table_name');
Database Vault: PgES includes a vault-type component called Database Vault for securely accessing secrets within PgES databases. A secret is anything you want to control access to, such as API keys, passwords, certificates, database objects, and more. Vault provides a unified interface for any secret, while providing tight access control and recording a detailed audit log:
Safe storage of secrets in an encrypted and audited manner
Dynamic generation of secrets, on demand, according to defined policies, for controlled access to the PgES database. Policies can be defined based on IP, operation, date and time, user type, location, group, etc.
Automatic rotation of user passwords for access to the database of any type of user
All secrets in the Database Vault have an associated lease. At the end of the lease, Vault will automatically revoke that secret.
Built-in support for revoking secrets. Vault can revoke not only individual secrets, but also a tree of secrets, for example, all secrets available to a particular user or all secrets of a particular type. Revocation helps block access to the database in the event of an intrusion.
Resource Management: PgES includes a Resource Manager that allows defining groups of resources with priority schemes, including limiting the number of processors used by the database. With the help of Resource Manager, resource-consuming operations can be temporarily suspended, with the possibility of resuming them when the system allows. The core component of Resource Manager is a resource group. A resource group is a group defined at the database level and is available to all databases in a PgES instance. A resource group allows the definition of resource usage limits by group members and the PgES processes that are part of a certain resource group are then controlled by the Resource Manager. A resource group can be created at the role level or at the level of the entire database. At the level of a group, limits can be imposed on the consumption of CPU resources. Example:
// create a new resource group
CREATE RESOURCE GROUP res_grp1
// associate the current process to the new resource group res_grp1
SET resource_group TO res_grp1
// set CPU limit to 0.5 for res_grp1 to limit resource consumption inside the resource group to
max. 50% CPU
ALTER RESOURCE GROUP res_grp1 SET cpu_rate_limit TO .5
The Resource Manager can be configured to automatically suspend processes that consume a significant number of resources, they can be automatically resumed once the system allows this.
Vector storage: PgES includes a component for storing and querying vector data, widely used in AI applications for storing and querying vector embeddings. Embeddings capture the degree of relationship of a multimedia element (text, image, video) and can be used to quickly search for similar elements, classifications, recommendations or clusters of close elements.
Multimedia data types: PgES extends PostgreSQL's text-based multimedia capabilities with additional data types and operations for image, audio, and video data, as follows:
Images: width, height, mime_type, exposure_time, focal_length, iso, colorspace, thumbnail, resize, crop, rotate
Video: width, height, video_codec, audio_codec, frame_rate, bit_rate, color_space, split, concat
Audio: width, height, audio_codec, channels, sample_rate, split, concat, resample
Append-only tables: PgES allows the definition of "append-only" tables where the data in these tables cannot be modified, only insert operations being allowed. Example:
CREATE TABLE my_table(...) USING pg_appendonly
Append-only tables behave like normal tables, only INSERT and COPY operations are allowed, and those that modify data are restricted: UPDATE, DELETE and MERGE. To guarantee the "append-only" mechanism, PgES uses a special storage type for these tables, in which only INSERT and COPY operations are allowed at the data file level. The data in the "append-only" tables also supports compression to optimize disk space, being usually used as tables that capture read-only information such as those related to logs or audit data.
Low-code tooling: PgES employs the ToolJet low-code visual tool, which allows the rapid development of applications without the need for advanced development knowledge, with the following capabilities:
Visual editor with over 40 built-in responsive widgets: tables, charts, lists, forms, progress bars and more.
Multi-page: Create an app with as many pages as you want.
Multiplayer Editing: Multiple users can use the app builder at the same time.
External data sources: Connect to other external databases, cloud storage and APIs.
Desktop and mobile: the size of the application can be customized to support different screens and resolutions.
Collaboration: Add comments anywhere in the workspace and tag team members.
Version Control: Each application has different versions with appropriate release cycle.
Run JS and Python code: Ability to customize JavaScript and Python snippets
Granular group-level and application-level access control.
Code-free query editors for all supported data sources
Associate and Transform Data: Transform query results using JavaScript/Python.
PgES native storage: the tool only acts as a proxy and does not store data, it is stored in the PgES database
SSO: Supports multiple SSO providers - OpenAPI & OAuth2
REST Services: PgES completes community PostgreSQL functionalities by integrating a web server (PostgREST) for converting data from the database into full RESTful services, both at table level and for stored procedures and functions. Thus, the REST services provided through PgES have the following capabilities:
Serialization/Deserialization from the SQL response directly into JSON
Supported operations: read, insert, upsert, delete
Data validation
Data filtering and pagination
Data ordering
Adding and modifying data
Authentication and authorization
Stateless to provide horizontal scalability
Automatic generation of API documentation using the OpenAPI standard
Advanced Monitoring: PgES allows monitoring of a single database or an entire cluster, allowing administrators to observe metrics related to performance, queries and security in order to take preventive actions. Monitoring and diagnostic data are collected in a unified manner, providing over 350 metrics as follows:
PgES server: version, system identifier, timeline, transaction id wraparound, checkpoint lag
Replication: primary-side, standby-side, physical and logical replication slots
WAL archiving: archive rate, wal and ready file counts, last success and fail time
BG Writer: checkpoint rate, total checkpoints (schedule+req), buffers
Vacuum: ongoing auto/manual vacuum progress, last analyze/vacuum, settings
Tablespaces: location, size, disk and inode usage of filesystem
Database: size, bloat, disabled triggers, installed extensions, temp files, transaction id wraparound, deadlocks, conflicts
Roles: Users, Groups, Membership
Active backends: transaction running too long, idling in transaction, waiting for locks
Tables: vacuum, analyze, row estimates, idx and seq scans, cache hit ratio, hot update ratio, size, bloat
Indexes: cache hit ratio, scans, rows read/scan, rows fetched/scan
Sequences: cache hit ratio
System: cores, load average, memory and disk usage
System parameters: current and default values where different
Slow queries
Locks: granted and waiting locks
Job progress: progress of analyze, backup, cluster, copy, create index and vacuum jobs
Blocked Queries: Blocked queries, along with the queries they are waiting for
PgBouncer: clients, Xacts, queries, client_wait
Server Log: query_execution_plans, auto_vacuum, deadlocks and the queries that caused the deadlock
The collected monitoring and diagnostic data can optionally be saved by PgES in the pges_stats system table and can be exported in JSON and CSV format.
Slow query tuning: PgES can automatically identify slow queries and offer optimization suggestions by creating indexes or partitioning data. PgES interprets the execution plan of slow queries and provides suggestions based on creating indexes and partitions. Example:
query: select * from foo.bar where id2 = $1 and id4 = $2
recmnded_action: CREATE INDEX ON foo.bar USING btree (id2, id4)
query: select * from foo.bar where id3 = $1
recmnded_action: CREATE INDEX ON foo.bar USING btree (id3)
query: SELECT * FROM measurement WHERE date < '2012-04-12'::date AND date > '2012-01-01'::date;
recmnded_action: ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02 FOR VALUES FROM ('2012-01-01') TO ('2012-04-12' );
Job scheduling: PgES allows scheduling jobs for automatic execution at certain time intervals, creation of execution chains and protection for concurrent execution. Execution can be programmed using CRON expressions for maximum flexibility.
Advanced Backup/Restore: PgES complements PostgreSQL's backup and restore capabilities by offering an online, centralized and easy-to-administer method for all backup and restore operations:
Parallel processing: the transfer and compression operations of the backup files are performed in parallel, to speed up the backup procedure. The backup operation can be limited to the level of resource consumption (e.g. max 25% CPU), and the restoration is done using all CPUs available on the database server.
Supports local or remote operation mode using TLS/SSH
Full, differential and incremental backup at file or data block level. Block-level backups save space by copying only the parts of files that have changed.
Local or remote backup storage: you can opt for a local storage, in the same data center for quick restoration and a long-term storage, with retention for a longer period of time, located in another data center.
Backup file rotation and retention: Retention policies can be defined for full and differential backups to cover any time interval. The WAL archive can be maintained for all backups or strictly for the most recent backups.
Integrity: Checksums are calculated for each backup file and checked again during a restore or check. After a backup procedure finishes copying files, PgES waits until each WAL segment required for the integrity of that backup has consistently reached the archive.
Pause and continue: an interrupted backup operation can continue from where it left off. Files that have already been copied are compared to checksums in to ensure integrity. Since this operation can take place entirely on the machine being backed up, it significantly reduces the database load and saves time, since the checksum calculation is faster than compressing and retransmitting the data.
Compression and checksums in transit: data compression using the lz4 or zstd algorithms and the calculation of checksums is performed as the data is copied to the backup repository, regardless of whether the repository is located locally or remotely.
Delta Restore: The manifest contains checksums for each file in the backup, so during a restore it is possible to use these checksums to speed up the restore. In a delta restore, all files not present in the backup are first removed and then checksums are generated for the remaining files. The files that match the backup are left in place and the rest of the files are restored as usual. Parallel processing can lead to a drastic reduction in restoration times.
Parallel and asynchronous WAL file moving: dedicated commands are included for adding and getting WAL files to/from the archive. Both commands support parallel execution to speed up processing and run asynchronously to provide the fastest possible database response time.
Full support for tablespaces: on restore, tablespaces can be remapped to a different location. Also, it is possible to remap all location tablespaces with a single command, a useful operation in the application development process.
Point-in-Time-Recovery (PITR): Allows playback of WAL from the last backup to a specific point in time, a specific transaction, or a specified restore point. For common recovery scenarios, time-based recovery is arguably the most useful. A typical recovery scenario is restoring a table or data that was accidentally deleted.
Security: PgES can encrypt the backup repository to secure backup files, wherever they are stored.
Easy Administration: PgES offers an advanced, web-based administration and monitoring tool based on pgAdmin, with which administrators can easily manage PgES databases. It allows:
Administration of users, roles and permissions
Connection to one or more PgES instances
PgES cluster administration
Administration of database objects
Running and debugging SQL queries
Editing data from tables
Comparison of schemes
Execution of backup and restoration procedures
View system parameters
View log files
View database system processes
Performing administrative actions: VACUUM, ANALYZE, REINDEX, CLUSTER
Database storage administration
Monitoring of all system parameters in real time
Visualization of slow queries and resource-consuming operations, as well as optimization recommendations
Viewing active sessions and queries
Creating alerts with the reporting of configurable thresholds exceeded
Kubernetes Operator: PgES includes a Kubernetes operator for managing PgES clusters within a Kubernetes cluster (K8s) with the following capabilities:
PgES automatic deployment
Support for rolling upgrades
Resize data volumes
Connection pooling with PgBouncer
Cluster cloning and restoration
Backup and restoration procedures
Automatic management of users and credentials
Deployment: PgES can be installed in any infrastructure:
Support for a variety of CPU architectures: x86, x86_64, IA64, PowerPC, PowerPC 64, S/390, S/390x, Sparc, Sparc 64, ARM, MIPS, MIPSEL, and PA-RISC
Virtual machines
Bare-metal
Within a Kubernetes cluster, through the included Kubernetes operator
Containerized environments using Docker