Comparison of relational database management systems
Comparison of relational database management systems
Main page
C

Comparison of relational database management systems

logo
Community Hub0 subscribers
Read side by side
Comparison of relational database management systems
View on Wikipedia
from Wikipedia

The following tables compare general and technical information for a number of relational database management systems. Please see the individual products' articles for further information. Unless otherwise specified in footnotes, comparisons are based on the stable versions without any add-ons, extensions or external programs.

General information

[edit]
Maintainer First public release date Latest stable version Latest release date License Public issues list
4D (4th Dimension) 4D S.A.S. 1984 v16.0 2017-01-10[1] Proprietary No
ADABAS Software AG 1970 8.1 2013-06 Proprietary No
Adaptive Server Enterprise SAP AG 1987 16.0 SP03 PL07 2019-06-10 Proprietary No
Advantage Database Server (ADS) SAP AG 1992 12.0 2015 Proprietary No
Altibase Altibase Corp. 2000 7.1.0.1.2 2018-03-02 Proprietary No
Apache Derby Apache 2004 10.17.1.0[2] 2023-11-14 Apache License Yes[3]
ClustrixDB MariaDB Corporation 2010 v7.0 2015-08-19 Proprietary No
CockroachDB Cockroach Labs 2015 v24.1.0 2024-05-20 BSL,CCL,MIT,BSD Yes[4]
CUBRID CUBRID 2008-11 11.2.3 2023-01-31 Apache License 2.0, BSD license for APIs and GUI tools Yes[5]
Datacom CA, Inc. Early 70s[6] 14[7] 2012[8] Proprietary No
IBM Db2 IBM 1983 12.1[9] Edit this on Wikidata 2024-11-14; 11 months ago Proprietary No
Empress Embedded Database Empress Software Inc 1979 10.20 2010-03 Proprietary No
Exasol EXASOL AG 2004 7.1.1 2021-09-15; 4 years ago Proprietary No
FileMaker FileMaker, Inc., an Apple subsidiary 1985-04 19 2020-05-20 Proprietary No
Firebird Firebird project 2000-07-25 5.0.3[10] Edit this on Wikidata 2025-07-14; 3 months ago IPL[11] and IDPL[12] Yes[13]
GPUdb GIS Federal 2014 3.2.5 2015-01-14 Proprietary No
HSQLDB HSQL Development Group 2001 2.6.1 2021-10-21 BSD Yes[14]
H2 H2 Software 2005 2.3.232 2024-08-12 EPL and modified MPL Yes[15]
Informix Dynamic Server IBM / HCL Technologies 1981????1980 15.0.0.1 2025-03-15 Proprietary No
Ingres Actian(HCLSoftware) 1974 12.0.0[16] 2024-05-06 Proprietary No
InterBase Embarcadero Technologies 1984 XE7 v12.0.4.357 2015-08-12 Proprietary No
Linter SQL RDBMS RELEX Group 1990 6.0.17.53 2018-02-15 Proprietary Yes[17]
LucidDB The Eigenbase Project 2007-01 0.9.4 2012-01-05 GPL v2 No
MariaDB MariaDB Community 2010-02-01 12.0.2[18] Edit this on Wikidata 2025-08-07; 2 months ago GPL v2, LGPL (for client-libraries)[19] Yes[20]
MaxDB SAP AG 2003-05 7.9.0.8 2014 Proprietary Yes[21]
SingleStore (formerly MemSQL) SingleStore 2012-06 7.1.11 2020-10-12 Proprietary No
Microsoft Access (JET) Microsoft 1992 16 (2016) 2015-09-22 Proprietary No
Microsoft Visual Foxpro Microsoft 1984 9 (2005) 2007-10-11 Proprietary No
Microsoft SQL Server Microsoft 1989 2022[22] Edit this on Wikidata 2022-11-16; 2 years ago Proprietary No
Microsoft SQL Server Compact (Embedded Database) Microsoft 2000 2011 (v4.0) Proprietary No
Mimer SQL Mimer Information Technology 1978 11.0.9D 2025-07-16 Proprietary No
MonetDB MonetDB Foundation [23] 2004 Mar2025 [24] 2025-03-27 Mozilla Public License, version 2.0[25] Yes[26]
mSQL Hughes Technologies 1994 4.1[27] 2017-06-30 Proprietary No
MySQL Oracle Corporation 1995-11 8.0.43[28] Edit this on Wikidata 2025-10-21; 16 days ago GPL v2 or Proprietary Yes[29]
NexusDB NexusDB Pty Ltd 2003 4.00.14 2015-06-25 Proprietary No
HPE NonStop SQL Hewlett Packard Enterprise 1987 SQL/MX 3.4 Proprietary No
NuoDB NuoDB 2013 4.1 2020-08 Proprietary No
Omnis Studio TigerLogic Inc 1982-07 6.1.3 Release 1no 2015-12 Proprietary No
OpenEdge Progress Software Corporation 1984 12.8 2024-1 Proprietary No
OpenLink Virtuoso OpenLink Software 1998 7.2.14 2024-11-11 GPL v2 or Proprietary Yes[30]
Oracle DB Oracle Corporation 1979-11 23ai[31] Edit this on Wikidata 2023-09-19; 2 years ago Proprietary No
Oracle Rdb Oracle Corporation 1984 7.4.1.1[32] 2021-04-21[±] Proprietary No
Paradox Corel Corporation 1985 11 2009-09-07 Proprietary No
Percona Server for MySQL Percona 2006 8.0.37-29 2024-08-06[±] GPL v2 Yes
Actian Zen (PSQL) Actian 1982 v16 2024-06-30 Proprietary No
Polyhedra DBMS ENEA AB 1993 9.0 2015-06-24 Proprietary, with Polyhedra Lite available as Freeware[33] No
PostgreSQL PostgreSQL Global Development Group 1989-06 17.4 2025-02-21[34] Postgres License[35] No[36]
R:Base R:BASE Technologies 1982 10.0 2016-05-26 Proprietary No
SAP HANA SAP AG 2010 2.0 SPS04 2019-08-08 Proprietary No
solidDB UNICOM Global 1992 7.0.0.10 2014-04-29 Proprietary No
SQL Anywhere SAP AG 1992 17.0.0.48 2019-07-26 Proprietary No
SQLBase Unify Corp. 1982 11.5 2008-11 Proprietary No
SQLite D. Richard Hipp 2000-09-12 3.51.0[37] Edit this on Wikidata 2025-11-04; 2 days ago Public domain Yes[38]
SQream DB SQream Technologies 2014 2.1[39] 2018-01-15 Proprietary No
Superbase Superbase 1984 Classic 2003 Proprietary No
Superbase NG Superbase NG 2002 Superbase NG 2.10 2017 Proprietary Yes[40]
Teradata Teradata 1984 15 2014-04 Proprietary No
TiDB PingCAP Inc. 2016 8.5.3[41] Edit this on Wikidata 2025-08-14; 2 months ago Apache License Yes[42]
UniData Rocket Software 1988 8.2.1 2017-07 Proprietary No
Vector Actian(HCLSoftware) 2010 7.0[43] 2024-12-17 Proprietary No
YugabyteDB Yugabyte, Inc. 2018 2.20.1.3[44] 2024-01-25[±] Apache License Yes[45]
Actian Zen (PSQL) Actian 1982 v16 2024-06-30 Proprietary No
Maintainer First public release date Latest stable version Latest release date License Public issues list

Operating system support

[edit]

The operating systems that the RDBMSes can run on.

Windows macOS Linux BSD UNIX AmigaOS z/OS OpenVMS iOS Android
4th Dimension Yes Yes No No No No No No No No
ADABAS Yes No Yes No Yes No Yes No No No
Adaptive Server Enterprise Yes No Yes Yes Yes No No No No No
Advantage Database Server Yes No Yes No No No No No No No
Altibase Yes No Yes No Yes No No No No No
Apache Derby Yes Yes Yes Yes Yes No Yes No ? No
ClustrixDB No No Yes No Yes No No No No No
CockroachDB Yes Yes Yes No No No No No No No
CUBRID Yes Partial Yes No No No No No No No
IBM Db2 Yes Yes Yes No Yes No Yes No Yes No
Empress Embedded Database Yes Yes Yes Yes Yes No No No No Yes
EXASolution No No Yes No No No No No No No
FileMaker Yes Yes Yes No No No No No Yes No
Firebird Yes Yes Yes Yes Yes No Maybe No Yes[46] Yes
HSQLDB Yes Yes Yes Yes Yes No Yes No ? ?
H2 Yes Yes Yes Yes Yes No Yes No ? Yes
Informix Dynamic Server Yes No Yes No Yes (AIX) No No No No No
Ingres Yes Yes Yes Yes Yes No Partial Yes[47] No No
InterBase Yes Yes Yes No Yes (Solaris) No No No Yes Yes
Linter SQL RDBMS Yes Yes Yes Yes Yes No Under Linux on IBM Z Yes Yes Yes
LucidDB Yes Yes Yes No No No No No No No
MariaDB Yes Yes[48] Yes Yes Yes No No No ? Yes[49]
MaxDB Yes No Yes No Yes No Maybe No No No
Microsoft Access (JET) Yes No No No No No No No No No
Microsoft Visual Foxpro Yes No No No No No No No No No
Microsoft SQL Server Yes No Yes[50] No No No No No No No
Microsoft SQL Server Compact (Embedded Database) Yes No No No No No No No No No
Mimer SQL Yes Yes Yes No Yes No No Yes[51] No Yes
MonetDB Yes Yes Yes Yes Yes No No No No No
MySQL Yes Yes Yes Yes Yes Yes Yes No ? Yes[52]
Omnis Studio Yes Yes Yes No No No No No No No
OpenEdge Yes No Yes No Yes No No No No No
OpenLink Virtuoso Yes Yes Yes Yes Yes No No No No No
Oracle Yes Yes Yes No Yes No Yes Yes No No
Oracle Rdb No No No No No No No Yes No No
Actian Zen (PSQL) Yes Yes (OEM only) Yes No No No No No Yes Yes
Polyhedra Yes No Yes No Yes No No No No No
PostgreSQL Yes Yes Yes Yes Yes Yes (MorphOS)[53] Under Linux on IBM Z[54] No No Yes
R:Base Yes No No No No No No No No No
SAP HANA Yes No Yes No No No No No No No
solidDB Yes No Yes No Yes No Under Linux on IBM Z No No No
SQL Anywhere Yes Yes Yes No Yes No No No No Yes
SQLBase Yes No Yes No No No No No No No
SQLite Yes Yes Yes Yes Yes Yes Maybe No Yes Yes
SQream DB No No Yes No No No No No No No
Superbase Yes No No No No Yes No No No No
Superbase NG Yes No Yes No No No No No No No
Teradata Yes No Yes No Yes No No No No No
TiDB Yes Yes Yes Partial No No No No No No
UniData Yes No Yes No Yes No No No No No
UniVerse Yes No Yes No Yes No No No No No
YugabyteDB Yes Yes Yes No No No No No No No
Windows macOS Linux BSD UNIX AmigaOS z/OS OpenVMS iOS Android

Fundamental features

[edit]

Information about what fundamental RDBMS features are implemented natively.

Database Name ACID Referential integrity Transactions Fine-grained locking Multiversion concurrency control Unicode Interface Type inference
4th Dimension Yes Yes Yes ? ? Yes GUI & SQL Yes
ADABAS Yes No Yes ? ? Yes proprietary direct call & SQL (via 3rd party) Yes
Adaptive Server Enterprise Yes Yes Yes Yes (Row-level locking) Yes Yes API & GUI & SQL Yes
Advantage Database Server Yes Yes Yes Yes (Row-level locking) ? Yes4 API & SQL Yes
Altibase Yes Yes Yes Yes (Row-level locking) ? Yes API & GUI & SQL Yes
Apache Derby Yes Yes Yes Yes (Row-level locking) [55] ? Yes SQL Yes
ClustrixDB Yes Yes Yes Yes Yes Yes SQL Yes
CockroachDB Yes Yes Yes Yes (Row-level locking) Yes Yes SQL No
CUBRID Yes Yes Yes Yes (Row-level locking) Yes Yes GUI & SQL Yes
IBM Db2 Yes Yes Yes Yes (Row-level locking)[56] ? Yes GUI & SQL Yes
Empress Embedded Database Yes Yes Yes ? ? Yes API & SQL Yes
EXASolution Yes Yes Yes ? ? Yes API & GUI & SQL Yes
Firebird Yes Yes Yes ? Yes Yes API & SQL Yes
HSQLDB Yes Yes Yes ? Yes Yes SQL Yes
H2 Yes Yes Yes ? Yes[57] Yes SQL Yes
Informix Dynamic Server Yes Yes Yes Yes (Row-level locking) Yes Yes SQL, REST, MQ, and JSON Yes
Ingres Yes Yes Yes Yes (Row-level locking) Yes Yes SQL & QUEL Yes
InterBase Yes Yes Yes ? ? Yes SQL Yes
Linter SQL RDBMS Yes Yes Yes (Except for DDL) Yes (Row-level locking) ? Yes API & GUI & SQL Yes
LucidDB Yes No No ? ? Yes SQL Yes
MariaDB Yes2 Yes Yes2 except for DDL[58][59] Yes (Row-level locking) Yes Yes SQL Yes
MaxDB Yes Yes Yes ? ? Yes SQL Yes
Microsoft Access (JET) Yes Yes Yes ? ? Yes GUI & SQL Yes
Microsoft Visual FoxPro Yes Yes Yes Yes (Row-level locking SMB2) Yes No GUI & SQL Yes
Microsoft SQL Server Yes Yes Yes Yes (Row-level locking)[60] Yes Yes GUI & SQL Yes
Microsoft SQL Server Compact (Embedded Database) Yes Yes Yes ? ? Yes GUI & SQL Yes
Mimer SQL Yes Yes Yes Yes (Optimistic locking) Yes Yes API & GUI & SQL Yes
MonetDB Yes Yes Yes ? ? Yes API & SQL & MAL Yes
MySQL Yes2 Yes3 Yes2 except for DDL[58] Yes (Row-level locking)[61] Yes Yes GUI 5 & SQL Yes
OpenEdge Yes Yes6 Yes Yes (Row-level locking) ? Yes GUI & SQL Yes
OpenLink Virtuoso Yes Yes Yes ? ? Yes API & GUI & SQL Yes
Oracle Yes Yes Yes except for DDL[58] Yes (Row-level locking)[62] Yes Yes API & GUI & SQL Yes
Oracle Rdb Yes Yes Yes ? ? Yes SQL Yes
Actian Zen (PSQL) Yes Yes Yes ? ? Yes API & GUI & SQL Yes
Polyhedra DBMS Yes Yes Yes Yes (optimistic and pessimistic cell-level locking)[63] ? Yes API & SQL Yes
PostgreSQL Yes Yes Yes Yes (Row-level locking)[64] Yes Yes API & GUI & SQL No[65]
SAP HANA Yes Yes Yes Yes (Row-level locking) Yes Yes API & GUI & SQL Yes
solidDB Yes Yes Yes Yes (Row-level locking) ? Yes API & SQL Yes
SQL Anywhere Yes Yes Yes Yes (Row-level locking)[66] Yes[67] Yes API & GUI & HTTP(S) (REST & SOAP)[68] & SQL Yes
SQLBase Yes Yes Yes ? ? Yes API & GUI & SQL Yes
SQLite Yes Yes Yes No (Database-level locking)[69] No Optional[70] API & SQL Yes
Superbase NG ? ? ? Yes (Record-level locking) ? Yes GUI & Proprietary & ODBC Yes
Teradata Yes Yes Yes Yes (Hash and Partition) ? Yes SQL Yes
TiDB Yes Yes Yes except for DDL[58] Yes (Row-level locking)[71] Yes Yes GUI 5 & SQL Yes
UniData Yes No Yes ? ? Yes Multiple Yes
UniVerse Yes No Yes ? ? Yes Multiple Yes
Database Name ACID Referential integrity Transactions Fine-grained locking Multiversion concurrency control Unicode Interface Type inference
  • Note (1): Currently only supports read uncommitted transaction isolation. Version 1.9 adds serializable isolation and version 2.0 will be fully ACID compliant.
  • Note (2): MariaDB and MySQL provide ACID compliance through the default InnoDB storage engine.[72][73]
  • Note (3): "For other than InnoDB storage engines, MySQL Server parses and ignores the FOREIGN KEY and REFERENCES syntax in CREATE TABLE statements.[74]" "The CHECK clause (as of 8.0.16) [75] supports most core features for all storage engines."
  • Note (4): Support for Unicode is new in version 10.0.
  • Note (5): MySQL provides GUI interface through MySQL Workbench.
  • Note (6): OpenEdge SQL database engine uses Referential Integrity, OpenEdge ABL Database engine does not and is handled via database triggers.

Limits

[edit]

Information about data size limits.

Max DB size Max table size Max row size Max columns per row Max Blob/Clob size Max CHAR size Max NUMBER size Min DATE value Max DATE value Max column name size
4th Dimension Limited ? ? 65,135 200 GB (2 GiB Unicode) 200 GB (2 GiB Unicode) 64 bits ? ? ?
Advantage Database Server Unlimited 16 EiB 65,530 B 65,135 / (10+ AvgFieldNameLength) 4 GiB ? 64 bits ? ? 128
Apache Derby Unlimited Unlimited Unlimited 1,012 (5,000 in views) 2,147,483,647 chars 254 (VARCHAR: 32,672) 64 bits 0001-01-01 9999-12-31 128
ClustrixDB Unlimited Unlimited 64 MB on Appliance, 4 MB on AWS ? 64 MB 64 MB 64 MB 0001-01-01 9999-12-31 254
CUBRID 2 EB 2 EB Unlimited Unlimited Unlimited 1 GB 64 bits 0001-01-01 9999-12-31 254
IBM DB2 Unlimited 2 ZB 1,048,319 B 1,012 2 GB 32 KiB 64 bits 0001-01-01 9999-12-31 128
Empress Embedded Database Unlimited 263−1 bytes 2 GB 32,767 2 GB 2 GB 64 bits 0000-01-01 9999-12-31 32
EXASolution Unlimited Unlimited Unlimited 10,000 — 2 MB 128 bits 0001-01-01 9999-12-31 256
FileMaker 8 TB 8 TB 8 TB 256,000,000 4 GB 10,000,000 1 billion characters, 10−400 to 10400, ± 0001-01-01 4000-12-31 100
Firebird Unlimited1 ≈32 TB 65,536 B Depends on data types used 32 GB 32,767 B 128 bits 100 32768 63
HSQLDB 64 TB Unlimited8 Unlimited8 Unlimited8 64 TB7 Unlimited8 Unlimited8 0001-01-01 9999-12-31 128
H2 64 TB Unlimited8 Unlimited8 Unlimited8 64 TB7 Unlimited8 64 bits -99999999 99999999 Unlimited8
Max DB size Max table size Max row size Max columns per row Max Blob/Clob size Max CHAR size Max NUMBER size Min DATE value Max DATE value Max column name size
Informix Dynamic Server ≈0.5 YB12 ≈0,5YB12 32,765 bytes (exclusive of large objects) 32,765 4 TB 32,76514 10125 13 01/01/000110 12/31/9999 128 bytes
Ingres Unlimited Unlimited 256 KB 1,024 2 GB 32 000 B 64 bits 0001 9999 256
InterBase Unlimited1 ≈32 TB 65,536 B Depends on data types used 2 GB 32,767 B 64 bits 100 32768 31
Linter SQL RDBMS Unlimited 230 rows 64 KB (w/o BLOBs),
2GB (each BLOB value) 		250 2 GB 4000 B 64 bits 0001-01-01 9999-12-31 66
MariaDB Unlimited MyISAM storage limits: 256 TB;
Innodb storage limits: 64 TB;
Aria storage limits: ??? 		64 KB3 4,0964 4 GB (longtext, longblob) 64 KB (text) 64 bits 1000 9999 64[76]
Microsoft Access (JET) 2 GB 2 GB 16 MB 255 64 KB (memo field),
1 GB ("OLE Object" field) 		255 B (text field) 32 bits 0100 9999 64
Microsoft Visual Foxpro Unlimited 2 GB 65,500 B 255 2 GB 16 MB 32 bits 0001 9999 10
Microsoft SQL Server 524,272 TB (32 767 files × 16 TB max file size)

16ZB per instance

524,272 TB 8,060 bytes / 2 TB6 1,024 / 30,000(with sparse columns) 2 GB / Unlimited (using RBS/FILESTREAM object) 2 GB6 126 bits2 0001 9999 128
Microsoft SQL Server Compact (Embedded Database) 4 GB 4 GB 8,060 bytes 1024 2 GB 4000 154 bits 0001 9999 128
Mimer SQL Unlimited Unlimited 16000 (+lob data) 252 Unlimited 15000 45 digits 0001-01-01 9999-12-31 128
MonetDB Unlimited Unlimited Unlimited Unlimited 2 GB 2 GB 128 bits -4712-01-01 9999-12-31 1024
MySQL Unlimited MyISAM storage limits: 256 TB; Innodb storage limits: 64 TB 64 KB3 4,0964 4 GB (longtext, longblob) 64 KB (text) 64 bits 1000 9999 64
OpenLink Virtuoso 32 TB per instance
(Unlimited via elastic cluster) 		DB size (or 32 TB) 4 KB 200 2 GB 2 GB 231 0 9999 100
Oracle 2 PB (with standard 8k block)
8 PB (with max 32k block)
8 EB (with max 32k block and BIGFILE option) 		4 GB × block size
(with BIGFILE tablespace) 		8 KB 1,000 128 TB 32,767 B11 126 bits −4712 9999 128
Max DB size Max table size Max row size Max columns per row Max Blob/Clob size Max CHAR size Max NUMBER size Min DATE value Max DATE value Max column name size
Actian Zen (PSQL) 4 billion objects 256 GB 2 GB 1,536 2 GB 8,000 bytes 64 bits 01-01-0001 12-31-9999 128 bytes
Polyhedra Limited by available RAM, address space 232 rows Unlimited 65,536 4 GB (subject to RAM) 4 GB (subject to RAM) 64 bits 0001-01-01 8000-12-31 255
PostgreSQL[77] Unlimited 32 TB 1.6 TB 250–1600 depending on type 1 GB (text, bytea) stored inline or 4 TB using pg_largeobject

[78]

1 GB Unlimited −4,713

[79]

5,874,897 63
SAP HANA ? ? ? ? ? ? ? ? ? ?
solidDB 256 TB 256 TB 32 KB + BLOB data Limited by row size 4 GB 4 GB 64 bits -32768-01-01 32767-12-31 254
SQL Anywhere[80] 104 TB (13 files, each file up to 8 TB (32 KB pages)) Limited by file size Limited by file size 45,000 2 GB 2 GB 64 bits 0001-01-01 9999-12-31 128 bytes
SQLite 128 TB (231 pages × 64 KB max page size) Limited by file size Limited by file size 32,767 2 GB 2 GB 64 bits No DATE type9 No DATE type9 Unlimited
Teradata Unlimited Unlimited 64000 wo/lobs
(64 GB w/lobs) 		2,048 2 GB 64,000 38 digits 0001-01-01 9999-12-31 128
UniVerse Unlimited Unlimited Unlimited Unlimited Unlimited Unlimited Unlimited Unlimited Unlimited Unlimited
Max DB size Max table size Max row size Max columns per row Max Blob/Clob size Max CHAR size Max NUMBER size Min DATE value Max DATE value Max column name size
  • Note (1): Firebird 2.x maximum database size is effectively unlimited with the largest known database size >980 GB.[81] Firebird 1.5.x maximum database size: 32 TB.
  • Note (2): Limit is 1038 using DECIMAL datatype.[82]
  • Note (3): InnoDB is limited to 8,000 bytes (excluding VARBINARY, VARCHAR, BLOB, or TEXT columns).[83]
  • Note (4): InnoDB is limited to 1,017 columns.[83]
  • Note (6): Using VARCHAR (MAX) in SQL 2005 and later.[84]
  • Note (7): When using a page size of 32 KB, and when BLOB/CLOB data is stored in the database file.
  • Note (8): Java array size limit of 2,147,483,648 (231) objects per array applies. This limit applies to number of characters in names, rows per table, columns per table, and characters per CHAR/VARCHAR.
  • Note (9): Despite the lack of a date datatype, SQLite does include date and time functions,[85] which work for timestamps between 24 November 4714 B.C. and 1 November 5352.
  • Note (10): Informix DATETIME type has adjustable range from YEAR only through 1/10000th second. DATETIME date range is 0001-01-01 00:00:00.00000 through 9999-12-31 23:59:59.99999.
  • Note (11): Since version 12c. Earlier versions support up to 4000 B.
  • Note (12): The 0.5 YB limit refers to the storage limit of a single Informix server instance beginning with v15.0. Informix v12.10 and later versions support using sharding techniques to distribute a table across multiple server instances. A distributed Informix database has no upper limit on table or database size.
  • Note (13): Informix DECIMAL type supports up to 32 decimal digits of precision with a range of 10−130 to 10125. Fixed and variable precision are supported.
  • Note (14): The LONGLVARCHAR type supports strings up to 4TB.

Tables and views

[edit]

Information about what tables and views (other than basic ones) are supported natively.

Temporary table Materialized view
4th Dimension Yes No
ADABAS ? ?
Adaptive Server Enterprise Yes1 Yes – see precomputed result sets
Advantage Database Server Yes No (only common views)
Altibase Yes No (only common views)
Apache Derby Yes No
ClustrixDB Yes No
CUBRID Yes (only CTE) No (only common views)
IBM Db2 Yes Yes
Empress Embedded Database Yes Yes
EXASolution Yes No
Firebird Yes No (only common views)
HSQLDB Yes No
H2 Yes No (only common views)
Informix Dynamic Server Yes No2
Ingres Yes No
InterBase Yes No
Linter SQL RDBMS Yes Yes
LucidDB No No
MariaDB Yes No4
MaxDB Yes No
Microsoft Access (JET) No No
Microsoft Visual Foxpro Yes Yes
Microsoft SQL Server Yes Yes
Microsoft SQL Server Compact (Embedded Database) Yes No
Mimer SQL No No
MonetDB Yes No (only common views)
MySQL Yes No4
Oracle Yes Yes
Oracle Rdb Yes Yes
OpenLink Virtuoso Yes Yes
Actian Zen (PSQL) Yes No
Polyhedra DBMS No No (only common views)
PostgreSQL Yes Yes
SAP HANA Yes ?
solidDB Yes No (only common views)
SQL Anywhere Yes Yes
SQLite Yes No
Superbase Yes Yes
Teradata Yes Yes
UniData Yes No
UniVerse Yes No
Temporary table Materialized view
  • Note (1): Server provides tempdb, which can be used for public and private (for the session) temp tables.[86]
  • Note (2): Materialized views are not supported in Informix; the term is used in IBM's documentation to refer to a temporary table created to run the view's query when it is too complex, but one cannot for example define the way it is refreshed or build an index on it. The term is defined in the Informix Performance Guide.[87]
  • Note (4): Materialized views can be emulated using stored procedures and triggers.[88]

Indexes

[edit]

Information about what indexes (other than basic B-/B+ tree indexes) are supported natively.

R-/R+ tree Hash Expression Partial Reverse Bitmap GiST GIN Full-text Spatial Forest of Trees Index Duplicate index prevention
4th Dimension ? Cluster ? ? ? ? ? ? Yes ? ? No
ADABAS ? ? ? ? ? ? ? ? ? ? ? No
Adaptive Server Enterprise No No Yes No Yes No No No Yes ? ? No
Advantage Database Server No No Yes No Yes Yes No No Yes ? ? No
Apache Derby No No No No No No No No No[89] ? ? No
ClustrixDB No Yes No No No No No No No No ? No
CUBRID No No Yes[90] Yes[90] Yes No No No No No No No
IBM Db2 Yes Yes Yes No Yes Yes No No Yes[91] ? ? No
Empress Embedded Database Yes No No Yes No Yes No No No ? ? No
EXASolution No Yes No No No No No No No ? ? No
Firebird No No Yes Yes Yes No No No No[92] ? ? No
HSQLDB No No No No No No No No No ? ? No
H2 No Yes No No No No No No Yes[93] Yes[94] ? No
Informix Dynamic Server Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes[95] Yes
Ingres Yes Yes Ingres v10 No No Ingres v10 No No No ? ? No
InterBase No No No No No No No No No ? ? No
Linter SQL RDBMS10 No Yes temporary indexes for equality joins Yes for some scalar functions like LOWER and UPPER No No No No No Yes[96] No No Yes
LucidDB No No No No No Yes No No No ? ? No
MariaDB Aria and MyISAM tables and, since v10.2.2, InnoDB tables only[97] MEMORY,[98] InnoDB,5 tables only PERSISTENT virtual columns only[99] No No No No No Yes[100] Aria and MyISAM tables and, since v10.2.2, InnoDB tables only[97] ? No
MaxDB No No No No No No No No No ? ? No
Microsoft Access (JET) No No No No No No No No No[101] ? ? No
Microsoft Visual Foxpro No No Yes Yes Yes2 Yes No No No ? ? No
Microsoft SQL Server Spatial Indexes Yes4 Yes3 Yes on Computed columns3 Bitmap filter index for Star Join Query No No Yes[102] Yes[103] ? No
Microsoft SQL Server Compact (Embedded Database) No No No No No No No No No[104] ? ? No
Mimer SQL No No No No Yes No No No Yes Yes No No
MonetDB No Yes No No No No No No No No No No
MySQL Spatial Indexes[105] MEMORY, Cluster (NDB), InnoDB,5 tables only No[106] No No No No No MyISAM tables[107] and, since v5.6.4, InnoDB tables[108] MyISAM tables[109] and, since v5.7.5, InnoDB tables[110] ? No
OpenLink Virtuoso Yes Cluster Yes Yes No Yes No No Yes Yes (Commercial only) No No
Oracle Yes 11 Cluster Tables Yes Yes 6 Yes Yes No No Yes[111] Yes[112] ? Yes[113]
Oracle Rdb No Yes ? No No ? No No ? ? ? No
Actian Zen (PSQL) No No No No No No No No No No No No
Polyhedra DBMS No Yes No No No No No No No No ? No
PostgreSQL Yes Yes Yes Yes Yes7 Yes Yes[114] Yes Yes[115] PostGIS[116] No No
SAP HANA ? ? ? ? ? ? ? ? ? ? ? No
solidDB No No No No Yes No No No No No No No
SQL Anywhere No No Yes No No No No No Yes Yes ? Yes
SQLite Yes[117] No Yes[118] Yes No No No No Yes[119] SpatiaLite[120] ? No
SQream DB ? ? ? ? Yes ? ? ? ? ? ? No
Teradata No Yes Yes Yes No Yes No No ?[121] ? ? No
UniVerse Yes Yes Yes3 Yes3 Yes3 No No No ? Yes[122] ? No
R-/R+ tree Hash Expression Partial Reverse Bitmap GiST GIN Full-text Spatial Forest of Trees Index Duplicate index prevention
  • Note (1): The users need to use a function from freeAdhocUDF library or similar.[123]
  • Note (2): Can be implemented for most data types using expression-based indexes.
  • Note (3): Can be emulated by indexing a computed column[124] (doesn't easily update) or by using an "Indexed View"[125] (proper name not just any view works[126]).
  • Note (4): Used for InMemory ColumnStore index, temporary hash index for hash join, Non/Cluster & fill factor.
  • Note (5): InnoDB automatically generates adaptive hash index[127] entries as needed.
  • Note (6): Can be implemented using Function-based Indexes in Oracle 8i and higher, but the function needs to be used in the sql for the index to be used.
  • Note (7): A PostgreSQL functional index can be used to reverse the order of a field.
  • Note (10): B+ tree and full-text only for now.
  • Note (11): R-Tree indexing available in base edition with Locator but some functionality requires Personal Edition or Enterprise Edition with Spatial option.
  • Note (12): FOT or Forest of Trees indexes is a type of B-tree index consisting of multiple B-trees which reduces contention in multi-user environments.[128]

Database capabilities

[edit]
Union Intersect Except Inner joins Outer joins Inner selects Merge joins Blobs and clobs Common table expressions Windowing functions Parallel query System-versioned tables
4th Dimension Yes Yes Yes Yes Yes No No Yes ? ? ? ?
ADABAS Yes ? ? ? ? ? ? ? ? ? ? ?
Adaptive Server Enterprise Yes ? ? Yes Yes Yes Yes Yes ? ? Yes ?
Advantage Database Server Yes No No Yes Yes Yes Yes Yes ? No ? ?
Altibase Yes Yes Yes, via MINUS Yes Yes Yes Yes Yes No No No ?
Apache Derby Yes Yes Yes Yes Yes Yes ? Yes No No ? ?
ClustrixDB Yes No No Yes Yes Yes No Yes Yes Yes Yes ?
CUBRID Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes[90] ? ?
IBM Db2 Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes[129] Yes[130]
Empress Embedded Database Yes Yes Yes Yes Yes Yes Yes Yes ? ? ? ?
EXASolution Yes Yes Yes Yes Yes Yes Yes No Yes Yes Yes ?
Firebird Yes No No Yes Yes Yes Yes Yes Yes Yes ? ?
HSQLDB Yes Yes Yes Yes Yes Yes Yes[131] Yes Yes No Yes[131] ?
H2 Yes Yes Yes Yes Yes Yes No Yes experimental[132] Yes[133] ? ?
Informix Dynamic Server Yes Yes Yes, via MINUS Yes Yes Yes Yes Yes Yes Yes Yes[134] ?
Ingres Yes No No Yes Yes Yes Yes Yes Yes[135] Yes[136] Yes[137] ?
InterBase Yes ? ? Yes Yes ? ? Yes ? ? ? ?
Linter SQL RDBMS Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes No No
LucidDB Yes Yes Yes Yes Yes Yes Yes No ? ? ? ?
MariaDB Yes 10.3+[138] 10.3+[139] Yes Yes Yes No Yes Yes[140] Yes[141] No[142] Yes[130]
MaxDB Yes ? ? Yes Yes Yes No Yes ? ? ? ?
Microsoft Access (JET) Yes No No Yes Yes Yes No Yes No No ? ?
Microsoft Visual Foxpro Yes ? ? Yes Yes Yes ? Yes ? ? ? ?
Microsoft SQL Server Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes[143] Yes[144] Yes[130]
Microsoft SQL Server Compact (Embedded Database) Yes No No Yes Yes ? No Yes No No ? ?
Mimer SQL Yes Yes Yes Yes Yes Yes ? Yes Yes No No ?
MonetDB Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes No
MySQL Yes 8+[145] 8+[146] Yes Yes Yes No Yes 8+[147] 8+[148] No[142] No[130]
OpenLink Virtuoso Yes Yes Yes Yes Yes Yes ? Yes ? ? Yes ?
Oracle Yes Yes Yes, via MINUS Yes Yes Yes Yes Yes Yes 1 Yes Yes[149] Yes[150]
Oracle Rdb Yes Yes Yes Yes Yes Yes Yes Yes ? ? ? ?
Actian Zen (PSQL) Yes No No Yes Yes ? ? Yes No No No ?
Polyhedra DBMS Yes Yes Yes Yes Yes No No Yes No No No ?
PostgreSQL Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes[151] No[130]
SAP HANA ? ? ? ? ? ? ? ? ? ? ? ?
solidDB Yes Yes Yes Yes Yes Yes Yes Yes Yes No No ?
SQL Anywhere Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes ?
SQLite Yes Yes Yes Yes 3.43.0+[152] Yes No Yes 3.8.3+[153] 3.25+[154] No No[130]
SQream DB ALL only No No Yes Yes Yes Yes No Yes Yes No ?
Teradata Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes ?
UniVerse Yes Yes Yes Yes Yes Yes Yes No No No ? ?
Union Intersect Except Inner joins Outer joins Inner selects Merge joins Blobs and clobs Common table expressions Windowing functions Parallel query System-versioned tables
  • Note (1): Recursive CTEs introduced in 11gR2 supersedes similar construct called CONNECT BY.

Data types

[edit]
Type system Integer Floating point Decimal String Binary Date/Time Boolean Other
4th Dimension Static UUID (16-bit), SMALLINT (16-bit), INT (32-bit), BIGINT (64-bit), NUMERIC (64-bit) REAL, FLOAT REAL, FLOAT CLOB, TEXT, VARCHAR BIT, BIT VARYING, BLOB DURATION, INTERVAL, TIMESTAMP BOOLEAN PICTURE
Altibase[155] Static SMALLINT (16-bit), INTEGER (32-bit), BIGINT (64-bit) REAL (32-bit), DOUBLE (64-bit) DECIMAL, NUMERIC, NUMBER, FLOAT CHAR, VARCHAR, NCHAR, NVARCHAR, CLOB BLOB, BYTE, NIBBLE, BIT, VARBIT DATE GEOMETRY
ClustrixDB[156] Static TINYINT (8-bit), SMALLINT (16-bit), MEDIUMINT (24-bit), INT (32-bit), BIGINT (64-bit) FLOAT (32-bit), DOUBLE DECIMAL CHAR, BINARY, VARCHAR, VARBINARY, TEXT, TINYTEXT, MEDIUMTEXT, LONGTEXT TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB DATETIME, DATE, TIMESTAMP, YEAR BIT(1), BOOLEAN ENUM, SET,
CUBRID[157] Static SMALLINT (16-bit), INTEGER (32-bit), BIGINT (64-bit) FLOAT, REAL(32-bit), DOUBLE(64-bit) DECIMAL, NUMERIC CHAR, VARCHAR, NCHAR, NVARCHAR, CLOB BLOB DATE, DATETIME, TIME, TIMESTAMP BIT MONETARY, BIT VARYING, SET, MULTISET, SEQUENCE, ENUM
IBM Db2 ? SMALLINT (16-bit), INTEGER (32-bit), BIGINT (64-bit) DECFLOAT, REAL, DOUBLE DECIMAL CLOB, CHAR, VARCHAR BINARY, VARBINARY, BLOB DATE, TIME, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITHOUT TIME ZONE BOOLEAN XML, GRAPHIC, VARGRAPHIC, DBCLOB, ROWID
Empress Embedded Database Static TINYINT, SQL_TINYINT, or INTEGER8; SMALLINT, SQL_SMALLINT, or INTEGER16; INTEGER, INT, SQL_INTEGER, or INTEGER32; BIGINT, SQL_BIGINT, or INTEGER64 REAL, SQL_REAL, or FLOAT32; DOUBLE PRECISION, SQL_DOUBLE, or FLOAT64; FLOAT, or SQL_FLOAT; EFLOAT DECIMAL, DEC, NUMERIC, SQL_DECIMAL, or SQL_NUMERIC; DOLLAR CHARACTER, ECHARACTER, CHARACTER VARYING, NATIONAL CHARACTER, NATIONAL CHARACTER VARYING, NLSCHARACTER, CHARACTER LARGE OBJECT, TEXT, NATIONAL CHARACTER LARGE OBJECT, NLSTEXT BINARY LARGE OBJECT or BLOB; BULK DATE, EDATE, TIME, ETIME, EPOCH_TIME, TIMESTAMP, MICROTIMESTAMP BOOLEAN SEQUENCE 32, SEQUENCE
EXASolution Static TINYINT, SMALLINT, INTEGER, BIGINT, REAL, FLOAT, DOUBLE DECIMAL, DEC, NUMERIC, NUMBER CHAR, NCHAR, VARCHAR, VARCHAR2, NVARCHAR, NVARCHAR2, CLOB, NCLOB N/A DATE, TIMESTAMP, INTERVAL BOOLEAN, BOOL GEOMETRY
FileMaker[158] Static Not Supported Not Supported NUMBER TEXT CONTAINER TIMESTAMP Not Supported
Firebird[159] ? INT128, INT64, INTEGER, SMALLINT DOUBLE, FLOAT DECIMAL, NUMERIC, DECIMAL(38, 4), DECIMAL(10, 4) BLOB, CHAR, CHAR(x) CHARACTER SET UNICODE_FSS, VARCHAR(x) CHARACTER SET UNICODE_FSS, VARCHAR BLOB SUB_TYPE TEXT, BLOB DATE, TIME, TIMESTAMP (without time zone and with time zone) BOOLEAN TIMESTAMP, TIMESTAMP WITH TIME ZONE, CHAR(38), User defined types (Domains)
Type system Integer Floating point Decimal String Binary Date/Time Boolean Other
HSQLDB[160] Static TINYINT (8-bit), SMALLINT (16-bit), INTEGER (32-bit), BIGINT (64-bit) DOUBLE (64-bit) DECIMAL, NUMERIC CHAR, VARCHAR, LONGVARCHAR, CLOB BINARY, VARBINARY, LONGVARBINARY, BLOB DATE, TIME, TIMESTAMP, INTERVAL BOOLEAN OTHER (object), BIT, BIT VARYING, ARRAY
Informix Dynamic Server[161] Static + UDT SMALLINT (16-bit), INT (32-bit), INT8 (64-bit proprietary), BIGINT (64-bit) SMALLFLOAT (32-bit), FLOAT (64-bit) DECIMAL (32 decimal digits float/fixed, range 10130 to +10125), MONEY CHAR, VARCHAR, NCHAR, NVARCHAR, LVARCHAR, CLOB, TEXT, LONGLVARCHAR TEXT, BYTE, BLOB, CLOB DATE, DATETIME, INTERVAL BOOLEAN SET, LIST, MULTISET, ROW, TIMESERIES, SPATIAL, GEODETIC, NODE, JSON, BSON, USER DEFINED TYPES
Ingres[162] Static TINYINT (8-bit), SMALLINT (16-bit), INTEGER (32-bit), BIGINT (64-bit) FLOAT4 (32-bit), FLOAT (64-bit) DECIMAL C, CHAR, VARCHAR, LONG VARCHAR, NCHAR, NVARCHAR, LONG NVARCHAR, TEXT BYTE, VARBYTE, LONG VARBYTE (BLOB) DATE, ANSIDATE, INGRESDATE, TIME, TIMESTAMP, INTERVAL N/A MONEY, OBJECT_KEY, TABLE_KEY, USER-DEFINED DATA TYPES (via OME)
Linter SQL RDBMS Static + Dynamic (in stored procedures) SMALLINT (16-bit), INTEGER (32-bit), BIGINT (64-bit) REAL(32-bit), DOUBLE(64-bit) DECIMAL, NUMERIC CHAR, VARCHAR, NCHAR, NVARCHAR, BLOB BYTE, VARBYTE, BLOB DATE BOOLEAN GEOMETRY, EXTFILE
MariaDB[163] Static TINYINT (8-bit), SMALLINT (16-bit), MEDIUMINT (24-bit), INT (32-bit), BIGINT (64-bit) FLOAT (32-bit), DOUBLE (aka REAL) (64-bit) DECIMAL CHAR, BINARY, VARCHAR, VARBINARY, TEXT, TINYTEXT, MEDIUMTEXT, LONGTEXT TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB DATETIME, DATE, TIMESTAMP, YEAR BIT(1), BOOLEAN (aka BOOL) = synonym for TINYINT ENUM, SET, GIS data types (Geometry, Point, Curve, LineString, Surface, Polygon, GeometryCollection, MultiPoint, MultiCurve, MultiLineString, MultiSurface, MultiPolygon)
Microsoft SQL Server[164] Static TINYINT, SMALLINT, INT, BIGINT FLOAT, REAL NUMERIC, DECIMAL, SMALLMONEY, MONEY CHAR, VARCHAR, TEXT, NCHAR, NVARCHAR, NTEXT BINARY, VARBINARY, IMAGE, FILESTREAM, FILETABLE DATE, DATETIMEOFFSET, DATETIME2, SMALLDATETIME, DATETIME, TIME BIT CURSOR, TIMESTAMP, HIERARCHYID, UNIQUEIDENTIFIER, SQL_VARIANT, XML, TABLE, Geometry, Geography, Custom .NET datatypes
Microsoft SQL Server Compact (Embedded Database)[165] Static TINYINT, SMALLINT, INT, BIGINT FLOAT, REAL NUMERIC, DECIMAL, MONEY NCHAR, NVARCHAR, NTEXT BINARY, VARBINARY, IMAGE DATETIME BIT TIMESTAMP, ROWVERSION, UNIQUEIDENTIFIER, IDENTITY, ROWGUIDCOL
Mimer SQL Static SMALLINT, INT, BIGINT, INTEGER(n) FLOAT, REAL, DOUBLE, FLOAT(n) NUMERIC, DECIMAL CHAR, VARCHAR, NCHAR, NVARCHAR, CLOB, NCLOB BINARY, VARBINARY, BLOB DATE, TIME, TIMESTAMP, INTERVAL BOOLEAN DOMAINS, USER-DEFINED TYPES (including the pre-defined spatial data types location, latitude, longitude and coordinate, and UUID)
MonetDB Static, extensible TINYINT, SMALLINT, INT, INTEGER, BIGINT, HUGEINT, SERIAL, BIGSERIAL FLOAT, FLOAT(n), REAL, DOUBLE, DOUBLE PRECISION DECIMAL, NUMERIC CHAR, CHAR(n), VARCHAR, VARCHAR(n), CLOB, CLOB(n), TEXT, STRING BLOB, BLOB(n) DATE, TIME, TIME WITH TIME ZONE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, INTERVAL YEAR, INTERVAL MONTH, INTERVAL DAY, INTERVAL HOUR, INTERVAL MINUTE, INTERVAL SECOND BOOLEAN JSON, JSON(n), URL, URL(n), INET, UUID, GIS data types (Geometry, Point, Curve, LineString, Surface, Polygon, GeometryCollection, MultiPoint, MultiCurve, MultiLineString, MultiSurface, MultiPolygon), User Defined Types
MySQL[156] Static TINYINT (8-bit), SMALLINT (16-bit), MEDIUMINT (24-bit), INT (32-bit), BIGINT (64-bit) FLOAT (32-bit), DOUBLE (aka REAL) (64-bit) DECIMAL CHAR, BINARY, VARCHAR, VARBINARY, TEXT, TINYTEXT, MEDIUMTEXT, LONGTEXT TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB DATETIME, DATE, TIMESTAMP, YEAR BIT(1), BOOLEAN (aka BOOL) = synonym for TINYINT ENUM, SET, GIS data types (Geometry, Point, Curve, LineString, Surface, Polygon, GeometryCollection, MultiPoint, MultiCurve, MultiLineString, MultiSurface, MultiPolygon)
OpenLink Virtuoso[166] Static + Dynamic INT, INTEGER, SMALLINT REAL, DOUBLE PRECISION, FLOAT, FLOAT(n) DECIMAL, DECIMAL(n), DECIMAL(m, n), NUMERIC, NUMERIC(n), NUMERIC(m, n) CHARACTER, CHAR(n), VARCHAR, VARCHAR(n), NVARCHAR, NVARCHAR(n) BLOB TIMESTAMP, DATETIME, TIME, DATE N/A ANY, REFERENCE (IRI, URI), UDT (User Defined Type), GEOMETRY (BOX, BOX2D, BOX3D, BOXM, BOXZ, BOXZM, CIRCULARSTRING, COMPOUNDCURVE, CURVEPOLYGON, EMPTY, GEOMETRYCOLLECTION, GEOMETRYCOLLECTIONM, GEOMETRYCOLLECTIONZ, GEOMETRYCOLLECTIONZM, LINESTRING, LINESTRINGM, LINESTRINGZ, LINESTRINGZM, MULTICURVE, MULTILINESTRING, MULTILINESTRINGM, MULTILINESTRINGZ, MULTILINESTRINGZM, MULTIPOINT, MULTIPOINTM, MULTIPOINTZ, MULTIPOINTZM, MULTIPOLYGON, MULTIPOLYGONM, MULTIPOLYGONZ, MULTIPOLYGONZM, POINT, POINTM, POINTZ, POINTZM, POLYGON, POLYGONM, POLYGONZ, POLYGONZM, POLYLINE, POLYLINEZ, RING, RINGM, RINGZ, RINGZM)
Type system Integer Floating point Decimal String Binary Date/Time Boolean Other
Oracle[167] Static + Dynamic (through ANYDATA) NUMBER BINARY_FLOAT, BINARY_DOUBLE NUMBER CHAR, VARCHAR2, CLOB, NCLOB, NVARCHAR2, NCHAR, LONG (deprecated) BLOB, RAW, LONG RAW (deprecated), BFILE DATE, TIMESTAMP (with/without TIME ZONE), INTERVAL N/A SPATIAL, IMAGE, AUDIO, VIDEO, DICOM, XMLType, UDT, JSON
Actian Zen (PSQL)[168] Static BIGINT, INTEGER, SMALLINT, TINYINT, UBIGINT, UINTEGER, USMALLINT, UTINYINT BFLOAT4, BFLOAT8, DOUBLE, FLOAT DECIMAL, NUMERIC, NUMERICSA, NUMERICSLB, NUMERICSLS, NUMERICSTB, NUMERICSTS CHAR, LONGVARCHAR, VARCHAR BINARY, LONGVARBINARY, VARBINARY DATE, DATETIME, TIME BIT CURRENCY, IDENTITY, SMALLIDENTITY, TIMESTAMP, UNIQUEIDENTIFIER
Polyhedra[169] Static INTEGER8 (8-bit), INTEGER(16-bit), INTEGER (32-bit), INTEGER64 (64-bit) FLOAT32 (32-bit), FLOAT (aka REAL; 64-bit) N/A VARCHAR, LARGE VARCHAR (aka CHARACTER LARGE OBJECT) LARGE BINARY (aka BINARY LARGE OBJECT) DATETIME BOOLEAN N/A
PostgreSQL[170] Static SMALLINT (16-bit), INTEGER (32-bit), BIGINT (64-bit) REAL (32-bit), DOUBLE PRECISION (64-bit) DECIMAL, NUMERIC CHAR, VARCHAR, TEXT BYTEA DATE, TIME (with/without TIME ZONE), TIMESTAMP (with/without TIME ZONE), INTERVAL BOOLEAN ENUM, POINT, LINE, LSEG, BOX, PATH, POLYGON, CIRCLE, CIDR, INET, MACADDR, BIT, UUID, XML, JSON, JSONB, arrays, composites, ranges, custom
SAP HANA Static TINYINT, SMALLINT, INTEGER, BIGINT SMALLDECIMAL, REAL, DOUBLE, FLOAT, FLOAT(n) DECIMAL VARCHAR, NVARCHAR, ALPHANUM, SHORTTEXT VARBINARY, BINTEXT, BLOB DATE, TIME, SECONDDATE, TIMESTAMP BOOLEAN CLOB, NCLOB, TEXT, ARRAY, ST_GEOMETRY, ST_POINT, ST_MULTIPOINT, ST_LINESTRING, ST_MULTILINESTRING, ST_POLYGON, ST_MULTIPOLYGON, ST_GEOMETRYCOLLECTION, ST_CIRCULARSTRING
solidDB Static TINYINT (8-bit), SMALLINT (16-bit), INTEGER (32-bit), BIGINT (64-bit) REAL (32-bit), DOUBLE (64-bit), FLOAT (64-bit) DECIMAL, NUMERIC (51 digits) CHAR, VARCHAR, LONG VARCHAR, WCHAR, WVARCHAR, LONG WVARCHAR BINARY, VARBINARY, LONG VARBINARY DATE, TIME, TIMESTAMP
SQLite[171] Dynamic INTEGER (64-bit) REAL (aka FLOAT, DOUBLE) (64-bit) N/A TEXT (aka CHAR, CLOB) BLOB N/A N/A N/A
SQream DB[172] Static TINYINT (8-bit), SMALLINT (16-bit), INTEGER (32-bit), BIGINT (64-bit) REAL (32-bit), DOUBLE (aka FLOAT) (64-bit) N/A CHAR, VARCHAR, NVARCHAR N/A DATE, DATETIME (aka TIMESTAMP) BOOL N/A
Type system Integer Floating point Decimal String Binary Date/Time Boolean Other
Teradata Static BYTEINT (8-bit), SMALLINT (16-bit), INTEGER (32-bit), BIGINT (64-bit) FLOAT (64-bit) DECIMAL, NUMERIC (38 digits) CHAR, VARCHAR, CLOB BYTE, VARBYTE, BLOB DATE, TIME, TIMESTAMP (w/wo TIME ZONE) PERIOD, INTERVAL, GEOMETRY, XML, JSON, UDT (User Defined Type)
UniData Dynamic N/A N/A N/A N/A N/A N/A N/A N/A
UniVerse Dynamic N/A N/A N/A N/A N/A N/A N/A N/A
Type system Integer Floating point Decimal String Binary Date/Time Boolean Other

Other objects

[edit]

Information about what other objects are supported natively.

Data domain Cursor Trigger Function1 Procedure1 External routine1
4th Dimension Yes No Yes Yes Yes Yes
ADABAS ? Yes ? Yes? Yes? Yes
Adaptive Server Enterprise Yes Yes Yes Yes Yes Yes
Advantage Database Server Yes Yes Yes Yes Yes Yes
Altibase Yes Yes Yes Yes Yes Yes
Apache Derby No Yes Yes Yes2 Yes2 Yes2
ClustrixDB No Yes No Yes Yes Yes
CUBRID Yes Yes Yes Yes Yes2 Yes
Empress Embedded Database Yes via RANGE CHECK Yes Yes Yes Yes Yes
EXASolution Yes No No Yes Yes Yes
IBM Db2 Yes via CHECK CONSTRAINT Yes Yes Yes Yes Yes
Firebird Yes Yes Yes Yes Yes Yes
HSQLDB Yes No Yes Yes Yes Yes
H2 Yes No Yes2 Yes2 Yes2 Yes
Informix Dynamic Server Yes via CHECK Yes Yes Yes Yes Yes 5
Ingres Yes Yes Yes Yes Yes Yes
InterBase Yes Yes Yes Yes Yes Yes
Linter SQL RDBMS No Yes Yes Yes Yes No
LucidDB No Yes No Yes2 Yes2 Yes2
MariaDB Yes[173] Yes Yes Yes Yes Yes
MaxDB Yes Yes Yes Yes Yes ?
Microsoft Access (JET) Yes No No No Yes, But single DML/DDL Operation Yes
Microsoft Visual Foxpro No Yes Yes Yes Yes Yes
Microsoft SQL Server Yes Yes Yes Yes Yes Yes
Microsoft SQL Server Compact (Embedded Database) No Yes No No No No
Mimer SQL Yes Yes Yes Yes Yes No
MonetDB No No Yes Yes Yes Yes
MySQL No 3 Yes Yes Yes Yes Yes
Oracle Yes Yes Yes Yes Yes Yes
Oracle Rdb Yes Yes Yes Yes Yes Yes
OpenLink Virtuoso Yes Yes Yes Yes Yes Yes
Actian Zen (PSQL) Yes Yes Yes Yes Yes No
Polyhedra DBMS No No Yes Yes Yes Yes
PostgreSQL Yes Yes Yes Yes Yes Yes
SAP HANA ? ? ? ? ? ?
solidDB Yes Yes Yes Yes Yes Yes
SQL Anywhere Yes Yes Yes Yes Yes Yes
SQLite No No Yes No No Yes
Teradata No Yes Yes Yes Yes Yes
UniData No No Yes Yes Yes Yes
UniVerse No No Yes Yes Yes Yes
Data domain Cursor Trigger Function1 Procedure1 External routine1
  • Note (1): Both function and procedure refer to internal routines written in SQL and/or procedural language like PL/SQL. External routine refers to the one written in the host languages, such as C, Java, Cobol, etc. "Stored procedure" is a commonly used term for these routine types. However, its definition varies between different database vendors.
  • Note (2): In Derby, H2, LucidDB, and CUBRID, users code functions and procedures in Java.
  • Note (3): ENUM datatype exists. CHECK clause is parsed, but not enforced in runtime.
  • Note (5): Informix supports external functions written in Java, C, & C++.

Partitioning

[edit]

Information about what partitioning methods are supported natively.

Range Hash Composite (Range+Hash) List Expression Round Robin
4th Dimension ? ? ? ? ? ?
ADABAS ? ? ? ? ? ?
Adaptive Server Enterprise Yes Yes No Yes ? ?
Advantage Database Server No No No No ? ?
Altibase Yes Yes No Yes ? ?
Apache Derby No No No No ? ?
ClustrixDB Yes No No No No ?
CUBRID Yes Yes No Yes ? ?
IBM Db2 Yes Yes Yes Yes Yes ?
Empress Embedded Database No No No No ? ?
EXASolution No Yes No No No ?
Firebird No No No No ? ?
HSQLDB No No No No ? ?
H2 No No No No ? ?
Informix Dynamic Server Yes Yes Yes Yes Yes Yes
Ingres Yes Yes Yes Yes ? ?
InterBase No No No No ? ?
Linter SQL RDBMS No No No No No ?
MariaDB Yes Yes Yes Yes ? ?
MaxDB No No No No ? ?
Microsoft Access (JET) No No No No ? ?
Microsoft Visual Foxpro No No No No ? ?
Microsoft SQL Server Yes via computed column via computed column Yes via computed column ?
Microsoft SQL Server Compact (Embedded Database) No No No No ? ?
Mimer SQL No No No No No ?
MonetDB Yes No No No Yes ?
MySQL Yes Yes Yes Yes ? ?
Oracle Yes Yes Yes Yes via Virtual Columns ?
Oracle Rdb Yes Yes ? ? ? ?
OpenLink Virtuoso Yes Yes Yes Yes Yes ?
Actian Zen (PSQL) No No No No No ?
Polyhedra DBMS No No No No No ?
PostgreSQL Yes Yes Yes Yes Yes ?
SAP HANA Yes Yes Yes Yes Yes ?
solidDB Yes No No No ? ?
SQL Anywhere No No No No ? ?
SQLite No No No No ? ?
Teradata Yes Yes Yes Yes ? ?
UniVerse Yes Yes Yes Yes ? ?
Range Hash Composite (Range+Hash) List Expression Round Robin

Access control

[edit]

Information about access control functionalities.

Native network encryption1 Brute-force protection Enterprise directory compatibility Password complexity rules2 Patch access3 Run unprivileged4 Audit
Resource limit
Separation of duties
(RBAC)5
Security Certification
4D Yes (with SSL) ? Yes ? Yes Yes ? ? ? ? ?
Adaptive Server Enterprise Yes (optional; to pay) Yes Yes (optional ?) Yes Partial (need to register; depend on which product)[174] Yes Yes Yes Yes Yes (EAL4+ 1) ?
Advantage Database Server Yes No No No Yes Yes No No Yes ? ?
CUBRID Yes (with SSL) ? No No Yes Yes Yes Yes Yes ? ?
IBM Db2 Yes ? Yes (LDAP, Kerberos...) Yes ? Yes Yes Yes Yes Yes (EAL4+6) ?
Empress Embedded Database ? ? No No Yes Yes Yes No Yes No ?
EXASolution No Yes Yes (LDAP) Yes Yes Yes Yes Yes Yes No ?
Firebird Yes Yes[175] Yes (Windows trusted authenification) Yes (by custom plugin) Yes (no security page)[176] Yes Yes[177] Yes No7 ? ?
HSQLDB Yes No Yes Yes Yes Yes No No Yes No ?
H2 Yes Yes ? No ? Yes ? Yes Yes No ?
Informix Dynamic Server Yes ? Yes10 ?10 Yes Yes Yes Yes Yes ? Yes
Linter SQL RDBMS Yes (with SSL) Yes Yes Yes (length only) Yes Yes Yes Yes Yes Yes Yes
MariaDB Yes (SSL) No Yes (with 5.2, but not on Windows servers) Yes[178][179] Yes[180] Yes ? ? ?8 No ?
Microsoft SQL Server Yes ? Yes (Microsoft Active Directory) Yes Yes Yes Yes (From 2008) Yes Yes Yes (EAL4+11) ?
Microsoft SQL Server Compact (Embedded Database) No (not relevant, only file permissions) No (not relevant) No (not relevant) No (not relevant) Yes Yes (file access) Yes Yes No ? ?
Mimer SQL Yes ? ? ? Yes Yes (depending on OS) Yes ? Yes ? Yes
MySQL Yes (SSL with 4.0) No Yes (with 5.5, but only in commercial edition) No Partial (no security page)[181] Yes ? ? ?8 Yes ?
OpenLink Virtuoso Yes Yes Yes Yes (optional) Yes (optional) Yes Yes (optional) Yes (optional) Yes No Yes (optional)
Oracle Yes Yes Yes Yes ? Yes Yes Yes Yes Yes (EAL21) ?
Actian Zen (PSQL) Yes ? No No Yes Yes Yes 12 No No No ?
Polyhedra DBMS Yes (with SSL. Optional) No No No No Yes Yes 13 Yes Yes 13 No ?
PostgreSQL Yes Yes Yes (LDAP, Kerberos...9) Yes (with passwordcheck module) Yes[182] Yes Yes (with pgaudit extension)[183] Yes Yes Yes (EAL2+1) ?
SAP HANA ? ? ? ? ? ? ? ? ? ? ?
solidDB No No Yes No No Yes Yes No No No No
SQL Anywhere Yes ? Yes (Kerberos) Yes ? Yes Yes No Yes Yes (EAL2+1 as Adaptive Server Anywhere) ?
SQLite No (not relevant, only file permissions) No (not relevant) No (not relevant) No (not relevant) Partial (no security page)[184] Yes (file access) Yes Yes No No ?
Teradata Yes No Yes (LDAP, Kerberos...) Yes ? Yes Yes Yes Yes Yes Yes
Native network encryption1 Brute-force protection Enterprise directory compatibility Password complexity rules2 Patch access3 Run unprivileged4 Audit
Resource limit
Separation of duties
(RBAC)5
Security Certification
  • Note (1): Network traffic could be transmitted in a secure way (not clear-text, in general SSL encryption). Precise if option is default, included option or an extra modules to buy.
  • Note (2): Options are present to set a minimum size for password, respect complexity like presence of numbers or special characters.
  • Note (3): How do you get security updates? Is it free access, do you need a login or to pay? Is there easy access through a Web/FTP portal or RSS feed or only through offline access (mail CD-ROM, phone).
  • Note (4): Does database process run as root/administrator or unprivileged user? What is default configuration?
  • Note (5): Is there a separate user to manage special operation like backup (only dump/restore permissions), security officer (audit), administrator (add user/create database), etc.? Is it default or optional?
  • Note (6): Common Criteria certified product list.[185]
  • Note (7): FirebirdSQL seems to only have SYSDBA user and DB owner. There are no separate roles for backup operator and security administrator.
  • Note (8): User can define a dedicated backup user but nothing particular in default install.[186]
  • Note (9): Authentication methods.[187]
  • Note (10): Informix Dynamic Server supports PAM and other configurable authentication. By default uses OS authentication.
  • Note (11): Authentication methods.[188]
  • Note (12): With the use of Pervasive AuditMaster.
  • Note (13): User-based security is optional in Polyhedra, but when enabled can be enhanced to a role-based model with auditing.[189]

Databases vs schemas (terminology)

[edit]

The SQL specification defines what an "SQL schema" is; however, databases implement it differently. To compound this confusion the functionality can overlap with that of a parent database. An SQL schema is simply a namespace within a database; things within this namespace are addressed using the member operator dot ".". This seems to be a universal among all of the implementations.

A true fully (database, schema, and table) qualified query is exemplified as such: SELECT * FROM database.schema.table

Both a schema and a database can be used to isolate one table, "foo", from another like-named table "foo". The following is pseudo code:

  • SELECT * FROM database1.foo vs. SELECT * FROM database2.foo (no explicit schema between database and table)
  • SELECT * FROM [database1.]default.foo vs. SELECT * FROM [database1.]alternate.foo (no explicit database prefix)

The problem that arises is that former MySQL users will create multiple databases for one project. In this context, MySQL databases are analogous in function to PostgreSQL-schemas, insomuch as PostgreSQL deliberately lacks off-the-shelf cross-database functionality (preferring multi-tenancy) that MySQL has. Conversely, PostgreSQL has applied more of the specification implementing cross-table, cross-schema, and then left room for future cross-database functionality.

MySQL aliases schema with database behind the scenes, such that CREATE SCHEMA and CREATE DATABASE are analogs. It can therefore be said that MySQL has implemented cross-database functionality, skipped schema functionality entirely, and provided similar functionality into their implementation of a database. In summary, PostgreSQL fully supports schemas and multi-tenancy by strictly separating databases from each other and thus lacks some functionality MySQL has with databases, while MySQL does not even attempt to support standard schemas.

Oracle has its own spin where creating a user is synonymous with creating a schema. Thus a database administrator can create a user called PROJECT and then create a table PROJECT.TABLE. Users can exist without schema objects, but an object is always associated with an owner (though that owner may not have privileges to connect to the database). With the 'shared-everything' Oracle RAC architecture, the same database can be opened by multiple servers concurrently. This is independent of replication, which can also be used, whereby the data is copied for use by different servers. In the Oracle implementation, a 'database' is a set of files which contains the data while the 'instance' is a set of processes (and memory) through which a database is accessed.

Informix supports multiple databases in a server instance like MySQL. It supports the CREATE SCHEMA syntax as a way to group DDL statements into a single unit creating all objects created as a part of the schema as a single owner. Informix supports a database mode called ANSI mode which supports creating objects with the same name but owned by different users.

PostgreSQL and some other databases have support for foreign schemas, which is the ability to import schemas from other servers as defined in ISO/IEC 9075-9 (published as part of SQL:2008). This appears like any other schema in the database according to the SQL specification while accessing data stored either in a different database or a different server instance. The import can be made either as an entire foreign schema or merely certain tables belonging to that foreign schema.[190] While support for ISO/IEC 9075-9 bridges the gap between the two competing philosophies surrounding schemas, MySQL and Informix maintain an implicit association between databases while ISO/IEC 9075-9 requires that any such linkages be explicit in nature.

See also

[edit]

References

[edit]
[edit]
Revisions and contributorsEdit on WikipediaRead on Wikipedia

Comparison of relational database management systems

View on Grokipedia
from Grokipedia
A comparison of relational database management systems (RDBMS) involves evaluating software platforms that manage relational databases by organizing data into structured tables of rows and columns, where relationships between data points are defined using keys, enabling efficient querying and manipulation primarily through Structured Query Language (SQL).[1] These comparisons assess critical attributes such as performance, scalability, ACID (Atomicity, Consistency, Isolation, Durability) compliance for transaction processing, support for SQL standards, data types, indexing capabilities, replication options, security features, and licensing models (open-source versus proprietary), aiding organizations in selecting systems suited to specific workloads like transactional processing or analytics.[2] The relational model underpinning these systems was first formalized by IBM researcher E. F. Codd in 1970, revolutionizing data storage by emphasizing logical independence from physical implementation.[3][4] Historically, RDBMS evolved from early systems like IBM's System R in the 1970s, which introduced SQL, to commercial products such as Oracle (1979) and IBM DB2 (1983), establishing the foundation for enterprise data management.[3] By the 1990s, open-source alternatives like MySQL (1995) and PostgreSQL (1996) emerged, democratizing access and fostering widespread adoption in web and application development. Modern comparisons increasingly incorporate cloud deployment, multi-model support (e.g., integrating relational with document or graph data), and integration with AI-driven features like automated tuning and vector search, reflecting shifts toward hybrid and distributed architectures.[5] Key RDBMS in current evaluations include both traditional on-premises solutions and cloud-native offerings, with popularity measured by metrics such as search frequency, technical discussions, and job postings. According to the DB-Engines Ranking for November 2025, Oracle holds the top position with a score of 1239.78, followed by MySQL (865.82), Microsoft SQL Server (718.87), and PostgreSQL (651.36), while cloud-focused systems like Snowflake (197.84) demonstrate rapid growth in analytical use cases.[6] In the 2024 Gartner Magic Quadrant for Cloud Database Management Systems, leaders such as AWS (with Amazon RDS and Aurora), Oracle, Google Cloud (AlloyDB and Cloud SQL), and Microsoft (Azure SQL Database) were positioned highest for their vision and ability to execute, emphasizing scalability across mission-critical workloads.[7] These rankings and analyses underscore ongoing trends like the rise of managed services and open-source dominance in cost-sensitive environments.[8]

Overview and Classification

Historical Development

The relational model for database management was first proposed by Edgar F. Codd in 1970, marking a foundational shift from hierarchical and network models to a declarative approach based on mathematical relations.[4] Codd's paper, "A Relational Model of Data for Large Shared Data Banks," introduced the concept of data organized into tables with rows and columns, emphasizing data independence, normalization to reduce redundancy, and query capabilities through relational algebra.[4] This model addressed limitations in earlier systems like IBM's IMS (1968), which relied on pointer-based navigation, by enabling users to manipulate data without specifying physical storage details.[3] Adoption was initially slow due to skepticism within IBM, but Codd's work laid the theoretical groundwork for modern RDBMS, influencing subsequent research and standards.[9] In the mid-1970s, research prototypes demonstrated the feasibility of relational systems. IBM's System R project, initiated in 1973 at the San Jose Research Laboratory, produced the first full-function RDBMS prototype by 1974, incorporating a query language called SEQUEL (later renamed SQL to avoid trademark issues).[3] System R validated key relational principles, including query optimization and integrity constraints, through three phases of development ending in 1979, and its SQL dialect became a de facto standard.[10] Concurrently, the University of California, Berkeley's INGRES project (1973–1979), led by Michael Stonebraker, developed a relational system using QUEL as its query language and introduced innovations like rule-based query optimization.[11] These prototypes proved relational databases could handle complex queries efficiently on hardware of the era, paving the way for commercialization despite performance challenges compared to navigational systems.[10] Commercial RDBMS emerged in the late 1970s and 1980s, driven by demand for scalable enterprise data management. Oracle Version 2, released in 1979 by Relational Software Inc. (later Oracle Corporation), was the first commercially available SQL-based RDBMS, supporting basic queries, joins, and portability across platforms.[12] IBM followed with SQL/DS in 1981 for mainframes and DB2 in 1983, integrating relational features into its ecosystem while retaining compatibility with legacy systems.[13] Other early entrants included Relational Technology Inc.'s Ingres (1980, derived from the Berkeley prototype) and Tandem's NonStop SQL (1985).[3] By the mid-1980s, over 100 relational systems existed, fueled by falling hardware costs and the ANSI SQL standard (1986), which formalized core syntax for portability.[14] This era solidified RDBMS dominance, with SQL evolving through ISO standards (e.g., SQL-92) to include advanced features like outer joins and recursion.[15] The 1990s and beyond saw RDBMS maturation amid distributed computing and internet growth. PostgreSQL (1996), an open-source evolution of Ingres, added extensibility for user-defined types and functions.[16] MySQL (1995), initially developed for web applications, gained popularity for web applications due to its GPL licensing and simplicity.[16] Microsoft SQL Server (1989) integrated with Windows, emphasizing ease of administration.[3] Standardization efforts continued with SQL:1999 introducing object-relational extensions, enabling hybrid systems to handle semi-structured data.[17] By the 2000s, RDBMS like Oracle and DB2 supported massive scalability through clustering (e.g., Oracle RAC in 2001), though they faced competition from NoSQL alternatives for big data workloads.[12] Today, relational principles remain central, with ongoing enhancements for cloud-native deployment and AI integration.[15]

Major RDBMS and Categorization

The major relational database management systems (RDBMS) dominate data management in enterprise, web, and embedded applications, with popularity measured by factors including technical discussions, job postings, and search interest. As of November 2025, the DB-Engines Ranking identifies Oracle as the leading RDBMS with a score of 1239.78, followed by MySQL (865.82), Microsoft SQL Server (718.87), and PostgreSQL (651.36). These systems adhere to the relational model, using SQL for querying structured data in tables with defined schemas, ensuring ACID compliance for transaction reliability.[12] Categorization of RDBMS often focuses on licensing (proprietary vs. open-source), deployment (on-premises, cloud, or embedded), and primary use cases (transactional processing vs. analytics), reflecting their evolution from monolithic servers to scalable cloud services.[18] Commercial Proprietary RDBMS
These systems are developed and supported by corporations, often requiring licenses for full features, and are optimized for high-availability enterprise environments. Oracle Database, the market leader, is a multi-model RDBMS that supports relational data alongside JSON and spatial types, with advanced features like Real Application Clusters for clustering and high availability.[12] Microsoft SQL Server provides integrated business intelligence tools, including Analysis Services for OLAP and tight integration with Azure for hybrid deployments, making it suitable for Windows-centric organizations.[18] IBM Db2 emphasizes mainframe compatibility and AI-infused analytics, scoring 119.28 in popularity for mission-critical applications. Teradata, with a focus on data warehousing, uses massively parallel processing for petabyte-scale analytics. Open-Source RDBMS
Licensed under permissive terms like GPL, these RDBMS benefit from community contributions and are widely adopted for cost-effective scalability. MySQL, originally developed by MySQL AB and now maintained by Oracle, excels in web applications with its InnoDB storage engine for transactional support and replication features.[19] PostgreSQL, evolved from the Berkeley POSTGRES project, offers object-relational extensions including custom data types, full-text search, and extensibility via procedural languages like PL/pgSQL, achieving ACID compliance through multi-version concurrency control.[20] MariaDB, a fork of MySQL, provides enhanced performance in storage engines like Aria and ColumnStore for analytics, serving as a compatible alternative. Cloud-Native and Data Warehouse RDBMS
Designed for cloud elasticity, these separate storage and compute for pay-per-use scaling, often prioritizing analytics over traditional OLTP. Snowflake operates as a fully managed service on AWS, Azure, or Google Cloud, using a multi-cluster architecture that allows independent scaling of storage and virtual warehouses, with native support for semi-structured data like JSON.[21] Google BigQuery provides serverless querying on petabyte-scale datasets via SQL, leveraging Google's infrastructure for real-time analytics without managing infrastructure. Microsoft Azure SQL Database extends SQL Server to the cloud with automatic scaling and built-in high availability, scoring 76.38 for hybrid scenarios. Embedded and Lightweight RDBMS
These are integrated directly into applications without a separate server, ideal for mobile, IoT, or desktop use. SQLite, a public-domain library, implements a self-contained SQL engine in a single file, supporting transactions and up to 281 terabytes per database with zero-configuration setup.[22] Microsoft Access, though declining in rank (78.29), remains used for small-scale desktop databases with graphical query tools.
CategoryExamplesKey StrengthsPopularity Score (Nov 2025)
Commercial ProprietaryOracle, SQL Server, Db2Enterprise scalability, support1239.78 (Oracle)
Open-SourceMySQL, PostgreSQL, MariaDBCommunity-driven, cost-free865.82 (MySQL)
Cloud-Native/Data WarehouseSnowflake, BigQuery, Azure SQLElastic scaling, analytics focus197.84 (Snowflake)
Embedded/LightweightSQLite, AccessServerless, simple integration104.19 (SQLite)
This table summarizes representative examples, with scores from DB-Engines indicating relative adoption.

Platform Support and Deployment

Operating System Compatibility

Operating system compatibility is a critical factor in selecting a relational database management system (RDBMS), as it determines the deployment flexibility across diverse environments, from on-premises servers to cloud infrastructures. Most modern RDBMS support multiple platforms to accommodate varying enterprise needs, but proprietary systems often prioritize specific ecosystems, while open-source alternatives emphasize broader portability. This compatibility influences portability of applications, maintenance costs, and integration with hardware architectures like x86-64, ARM, or POWER.[23][24][25] The following table summarizes the supported operating systems for major RDBMS as of late 2025, based on official documentation. Support typically includes recent major versions, with specifics varying by release; for instance, kernel levels and package dependencies must align with vendor guidelines.
RDBMSSupported Operating SystemsArchitecturesNotes
PostgreSQLLinux (various distributions), Windows, FreeBSD, OpenBSD, NetBSD, DragonFlyBSD, macOS, Solaris, AIXx86-64, ARM64, POWER, othersHighly portable open-source system; tested on current OS versions with community ports for additional Unix-like systems.[23]
MySQLLinux (Oracle Linux, RHEL, Rocky Linux 8/9/10; Ubuntu, SLES), Windows, macOS, Solarisx86-64, ARM64Official support focuses on enterprise Linux distributions; community builds extend to more platforms like Debian.[24]
Oracle DatabaseLinux (Oracle Linux, RHEL 7/8/9, SLES 12/15), Windows (Server 2019/2022, 10/11), Solaris (SPARC/x86), AIX, HP-UXx86-64, SPARC, POWER, ItaniumEnterprise-focused; requires certified OS versions and patches for full support, with limited legacy Unix options.[25][26]
Microsoft SQL ServerWindows (Server 2016/2019/2022, 10/11), Linux (RHEL 8/9, Ubuntu 20.04/22.04, SLES 15)x86-64Native Windows integration; Linux support added since 2017 for containerized and server deployments.[27]
IBM Db2Linux (RHEL, SLES, Ubuntu), AIX, Windows, Solaris, HP-UX; macOS for developmentx86-64, POWER, ZBroad Unix heritage; supports virtualized environments across editions, with z/OS for mainframes.[28][29]
SQLiteAll major OS including Windows, macOS, Linux (all distributions), Android, iOS, embedded RTOS (VxWorks, etc.)x86-64, ARM, MIPS, othersEmbeddable library with no server process; cross-platform file format ensures portability across 50+ environments.[30][31]
Open-source RDBMS like PostgreSQL and MySQL offer the widest compatibility, enabling deployment on commodity hardware and cloud providers without licensing restrictions tied to specific vendors. For example, PostgreSQL's support for BSD variants and older Unix systems facilitates use in specialized or legacy setups, while MySQL's ARM64 compatibility aids edge computing on devices like Raspberry Pi.[23][24] In contrast, proprietary systems such as Oracle Database and Microsoft SQL Server emphasize certified configurations on enterprise-grade OS, ensuring stability but potentially increasing setup complexity; Oracle's SPARC and POWER support, for instance, targets high-end hardware like SPARC servers.[26][27] IBM Db2 maintains strong multi-platform support rooted in its Unix origins, accommodating hybrid environments with mainframe integration via z/OS, though client development on macOS is limited to non-production use.[29] SQLite stands out for its universal embeddability, requiring no installation and functioning identically across OS boundaries due to its single-file database format, making it ideal for mobile and IoT applications.[32] Overall, compatibility trends reflect a shift toward Linux dominance in cloud-native deployments, with Windows retaining relevance for Microsoft-centric ecosystems.[28]

Deployment Options and Environments

Relational database management systems (RDBMS) support a range of deployment options to accommodate diverse infrastructure preferences, from self-managed on-premises installations to fully managed cloud services. These options typically include single-node setups for development and testing, clustered environments for high availability and scalability, containerized deployments for portability, and hybrid models combining on-premises and cloud resources. Deployment environments vary by vendor, with considerations for operating system compatibility, automation levels, and integration with orchestration tools like Kubernetes. Open-source RDBMS like MySQL and PostgreSQL emphasize flexibility and community-driven cloud integrations, while commercial offerings such as Oracle Database, Microsoft SQL Server, and IBM Db2 provide enterprise-grade managed services alongside traditional installations.[33][34][35][36][37] Key differences arise in the degree of management automation and supported environments. For example, cloud PaaS options offload maintenance tasks like patching and backups to the provider, enabling faster scaling but potentially limiting customization compared to IaaS or on-premises deployments. High availability (HA) environments often rely on replication, failover clustering, or multi-zone architectures to ensure minimal downtime, with quantitative targets like 99.99% uptime in managed cloud services. Hybrid deployments facilitate data sovereignty by keeping sensitive workloads on-premises while leveraging cloud for burst capacity.[38][33] The following table summarizes deployment options for major RDBMS, focusing on representative examples:
RDBMSOn-Premises SupportCloud PaaS (Managed)Cloud IaaS (VMs)ContainerizedKey HA Environments
Oracle DatabaseYes (e.g., Exadata engineered systems for integrated hardware-software setups)[33]Yes (Autonomous Database on Oracle Cloud Infrastructure for self-driving, self-securing services; also AWS, Azure, Google Cloud)[33]Yes (virtual machines with full control)[33]Yes (Docker, Kubernetes via Oracle Container Engine)[33]Clustered (Real Application Clusters), multi-cloud hybrid with Data Guard for disaster recovery[33]
MySQLYes (binary/source installs on Linux, Windows, macOS, Solaris)[34]Yes (MySQL HeatWave on Oracle Cloud for analytics; Amazon RDS, Azure Database for MySQL)[39][34]Yes (self-managed on VMs)[34]Yes (official Docker images; Kubernetes via operators)[34]InnoDB Cluster for group replication; multi-AZ in RDS with read replicas[39]
PostgreSQLYes (packages for Linux distributions like Debian/Ubuntu, Red Hat; Windows, macOS, BSD, Solaris)[35]Yes (Amazon RDS for PostgreSQL with versions 11–17; Azure Database for PostgreSQL, Google Cloud SQL)[38][35]Yes (self-managed on VMs)[35]Yes (official Docker images; Kubernetes operators like Crunchy Data)[35]Streaming replication for hot standby; multi-AZ deployments with read replicas in RDS for 99.99% availability[38]
Microsoft SQL ServerYes (all editions on Windows/Linux; Developer/Express free for non-production)[36]Yes (Azure SQL Database/Managed Instance for serverless scaling; pay-as-you-go)[36]Yes (SQL Server on Azure VMs for lift-and-shift)[36]Yes (Docker images; Kubernetes via Azure Kubernetes Service)[36]Always On Availability Groups; failover clustering in Enterprise edition[36]
IBM Db2Yes (Community/Standard/Advanced editions on Linux, UNIX, Windows, AIX)[37]Yes (Db2 on IBM Cloud, AWS, Azure; containerized via Cloud Pak for Data)[37]Yes (VMs with unlimited cores in Advanced edition)[37]Yes (Docker on OpenShift; Kubernetes-native)[37]PureScale clustering for HA; HADR for disaster recovery in all editions[37]
These options enable RDBMS to adapt to modern DevOps practices, such as blue-green deployments in cloud environments for zero-downtime updates. For instance, PostgreSQL on AWS RDS supports blue/green deployments to test changes without impacting production. Vendors like Oracle and IBM emphasize hybrid capabilities for regulated industries, allowing seamless data movement between environments via tools like Oracle GoldenGate or Db2 replication.[38][33][37]

Core Data Structures

Tables and Views

In relational database management systems (RDBMS), tables serve as the fundamental data structures for organizing information into rows and columns, adhering to the relational model defined by E.F. Codd. All major RDBMS, including Oracle, MySQL, PostgreSQL, and Microsoft SQL Server, support permanent tables that persist data across sessions and transactions, with metadata stored in the system catalog.[40][41][42][43] Temporary tables provide session- or transaction-specific storage for intermediate results, reducing the need for repeated queries on permanent data. These vary in scope and persistence across systems:
RDBMSPermanent TablesTemporary Tables Scope and Features
OracleStandard relational, object, index-organized, external, and clustered tables; support partitioning and compression.[41]Global temporary tables (GTT) visible to all sessions but with session- or transaction-specific data (ON COMMIT DELETE ROWS/PRESERVE ROWS); private temporary tables for session-only visibility.[41]
MySQLInnoDB (default), MyISAM, MEMORY, and other engine-specific tables; supports partitioning up to 8192 partitions (including subpartitions).[40][44]Session-specific only (CREATE TEMPORARY TABLE); automatically dropped at session end; no global option natively.
PostgreSQLStandard, unlogged (faster but not crash-safe), partitioned, and typed tables; supports inheritance.[42]Session-specific (TEMPORARY or TEMP); can be transaction-specific with ON COMMIT DROP; exist in a special schema, with temporary indexes.[42]
SQL ServerHeap, clustered index, and columnstore tables; supports partitioning and temporal tables for history tracking.[43]Local (session-specific, prefixed #) and global (visible to all sessions, prefixed ##); automatically dropped at session end for local, or when no references remain for global.[45]
Views act as virtual tables derived from queries on base tables or other views, enabling abstraction, security, and simplified access without storing data physically (except in specialized forms). They conform to SQL standards but differ in advanced capabilities like updatability and materialization.[46][47][48][49] Standard views are universally supported for read-only querying, often with joins and aggregates. Updatable views allow INSERT, UPDATE, or DELETE operations that propagate to base tables, subject to restrictions like single-table sources without aggregates. Materialized views store query results physically for performance, requiring periodic refreshes, while indexed views add indexes to materialized data for optimization.
FeatureOracleMySQLPostgreSQLSQL Server
Standard ViewsYes, including object, XMLType, and editioning views; WITH CHECK OPTION for updates.[48]Yes; supports ALGORITHM (MERGE/TEMPTABLE) and SQL SECURITY (DEFINER/INVOKER).[47]Yes, including recursive and temporary views; security_barrier and check_option options.[46]Yes; supports schema-bound for dependency enforcement.[49]
Updatable ViewsYes, if key-preserved and simple (no aggregates, DISTINCT).[48]Yes, for simple single-table views; WITH CHECK OPTION enforces WHERE clauses; restrictions on subqueries and unions.Yes, for simple cases via direct mapping; complex updatability via rules or INSTEAD OF triggers.[46]Yes, with restrictions (no subqueries, aggregates, or multiple tables in some cases).[49]
Materialized ViewsYes, with refresh options (ON COMMIT, ON DEMAND, FAST/COMPLETE); supports query rewrite.No native support; simulated via summary tables with triggers or scheduled events for refresh.[47]Yes (CREATE MATERIALIZED VIEW); concurrent refresh with REFRESH MATERIALIZED VIEW CONCURRENTLY; requires unique index for concurrency.No native; approximated by indexed views.[49]
Indexed ViewsNo; use materialized views with indexes.[48]No.[47]No; materialized views can be indexed separately.Yes, with unique clustered index; stores data and supports non-clustered indexes; limited to Enterprise Edition.[50]
These differences influence use cases: Oracle and PostgreSQL excel in advanced view materialization for data warehousing, while SQL Server's indexed views optimize aggregations in OLTP environments. MySQL's simpler views suit web applications prioritizing ease over complex persistence.[51]

Indexes and Constraints

Indexes in relational database management systems (RDBMS) are data structures that improve query performance by allowing faster data retrieval, particularly for SELECT operations involving WHERE clauses, JOINs, and ORDER BY. They are typically built on one or more columns and can be clustered (where the table data is physically ordered by the index key) or non-clustered (separate from the table data). Common index types across RDBMS include B-tree indexes, which support equality and range queries, and are the default in most systems due to their versatility. Hash indexes, suited for exact-match queries, are supported in select cases but less common for their limitations on range operations. Specialized indexes like bitmap, full-text, and spatial cater to specific data types or query patterns, such as low-cardinality columns or geometric data. The choice of index type impacts storage overhead, maintenance costs during inserts/updates/deletes, and query optimization, with systems employing query planners to select the most efficient index for a given workload.[52][53][54][55] Major RDBMS vary in their index offerings, reflecting differences in architecture and target use cases. For instance, Oracle Database supports a broad range, including B-tree (default for general retrieval), bitmap (compact for low-distinct-value columns like gender or status), function-based (for expressions in queries), and domain indexes (extensible for custom applications like text or spatial). PostgreSQL emphasizes flexibility with B-tree (for ranges and sorts), hash (equality only), GiST and SP-GiST (for geometric and proximity searches), GIN (inverted for arrays and full-text), BRIN (block-range summaries for large ordered tables), and the Bloom extension for probabilistic multi-column filtering. MySQL primarily relies on B-tree indexes for InnoDB and MyISAM engines (covering primary keys, unique, and full-text via inverted lists), with hash limited to MEMORY tables and R-tree for spatial data. SQL Server distinguishes clustered (physically sorts table rows via B-tree) from nonclustered indexes, adding columnstore (column-oriented for analytics), filtered (subset-specific), spatial, XML, and full-text types. IBM DB2 includes unique (enforces no duplicates), clustered (optimizes key-order traversal), bidirectional (forward/reverse scans), and expression-based indexes (for computed values). These variations allow optimization for OLTP (e.g., B-tree in Oracle) versus OLAP (e.g., columnstore in SQL Server).[52][53][54][55][56]
RDBMSB-treeHashBitmapFull-textSpatialClusteredOther Notable Types
OracleYes (default)NoYesYes (via domain)Yes (R-tree)Yes (cluster)Function-based, Reverse-key, Domain
PostgreSQLYesYesNoYes (GIN)Yes (GiST/SP-GiST)NoBRIN, Bloom (ext.)
MySQLYes (default)Yes (MEMORY)NoYes (inverted)Yes (R-tree)Yes (InnoDB primary)Descending
SQL ServerYesYes (in-memory)NoYesYesYesColumnstore, Filtered, XML
DB2YesNoNoYes (text)YesYesExpression-based, Bidirectional
Constraints in RDBMS enforce data integrity rules at the database level, ensuring consistency without relying solely on application logic. They include declarative definitions applied during INSERT, UPDATE, or DELETE operations, with violations typically raising errors. Standard constraints, aligned with SQL standards, comprise NOT NULL (prevents null values), UNIQUE (ensures no duplicates, allowing nulls in some systems), PRIMARY KEY (combines UNIQUE and NOT NULL, limited to one per table), FOREIGN KEY (maintains referential integrity by linking to another table's key), and CHECK (validates against a Boolean expression). These are universally supported but differ in enforcement details, such as transaction handling or extensibility. For example, PostgreSQL allows deferrable constraints (checked at transaction end rather than immediately) and exclusion constraints (using operators like overlap for non-standard uniqueness), enhancing flexibility for complex transactions. MySQL supports all standard types but limits CHECK and FOREIGN KEY to transactional engines like InnoDB; MyISAM ignores them, and non-strict SQL modes may coerce invalid data instead of rejecting it. Oracle provides advanced features like deferrable constraints and cascading actions for FOREIGN KEY, with CHECK supporting complex expressions. SQL Server enforces constraints immediately, with UNIQUE allowing one null by default and CHECK operating at the row level but skipping nulls. DB2 mirrors standard support, emphasizing clustered enforcement via indexes for PRIMARY KEY and UNIQUE. Overall, these mechanisms prevent anomalies like orphans or invalid domains, with PostgreSQL offering the most SQL-compliant extensibility.[57][58][59][60]

Data Handling and Limits

Supported Data Types

Relational database management systems (RDBMS) provide a range of built-in data types to define the nature of data stored in columns, ensuring appropriate storage, validation, and operations. These types generally fall into categories such as numeric, character, date/time, binary, and specialized types like JSON or spatial, but implementations differ in precision, storage efficiency, and extensions. For instance, while most systems support standard SQL types like INTEGER and VARCHAR, variations exist in handling large objects, Unicode, and approximate numerics, influencing portability and performance in multi-system environments.[61][62][63][64][65][66] The core numeric types across major RDBMS emphasize exact integers and decimals for precision-critical applications, with floating-point options for approximations. PostgreSQL offers signed integers from SMALLINT (2 bytes, -32,768 to 32,767) to BIGINT (8 bytes, -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807), plus NUMERIC for arbitrary precision up to 131,072 digits. MySQL provides TINYINT (1 byte) to BIGINT (8 bytes), with DECIMAL supporting up to 65 digits. Oracle's NUMBER handles up to 38 digits of precision with scales from -84 to 127, while SQL Server's DECIMAL/NUMERIC uses 1-38 precision and 5-17 bytes storage. SQLite uses flexible INTEGER storage (0-8 bytes) under NUMERIC affinity, and DB2 includes SMALLINT (2 bytes) to BIGINT (8 bytes) with DECIMAL up to 31 digits.[67][68][69][70][71][65][66] Character and binary string types manage textual and raw data, with limits on length and encoding support. PostgreSQL's VARCHAR and TEXT handle variable-length strings up to 1 GB, with BYTEA for binary data. MySQL supports CHAR/VARCHAR (up to 65,535 bytes) and BLOB/TEXT variants up to 4 GB. Oracle's VARCHAR2 reaches 32,767 bytes (extended), with RAW for binary up to the same limit and CLOB/BLOB for up to 4 GB. SQL Server uses VARCHAR (up to 8,000 bytes) and VARBINARY, with IMAGE deprecated in favor of VARBINARY(MAX) for large binaries. SQLite stores TEXT and BLOB without strict length limits, relying on affinity for coercion. DB2's VARCHAR goes to 32,672 bytes, VARBINARY to 32,740, and BLOB/CLOB to 2 GB. Unicode support is native in most via NCHAR/NVARCHAR equivalents.[72][73][70][65][66] Date and time types facilitate temporal data handling, often with timezone awareness and fractional seconds. PostgreSQL includes DATE, TIMESTAMP (up to microseconds), and INTERVAL for spans, with TIMESTAMPTZ for timezones (8 bytes). MySQL's DATETIME and TIMESTAMP (up to microseconds since 8.0) range to 9999, with TIME supporting hours beyond 24. Oracle's DATE (7 bytes, century to seconds) extends to TIMESTAMP WITH TIME ZONE (13 bytes, nanoseconds). SQL Server offers DATETIME2 (up to 100 nanoseconds) and DATETIMEOFFSET for timezones, alongside legacy DATETIME (3.33 ms precision). SQLite lacks native types, storing dates as TEXT (ISO strings), REAL (Julian days), or INTEGER (Unix time) under NUMERIC affinity, processed via functions. DB2's TIMESTAMP includes microseconds, with DATE and TIME as fixed formats.[70][65][66] Specialized types address modern needs like semi-structured data and geometry. PostgreSQL natively supports JSON/JSONB (binary for indexing), XML, geometric primitives (POINT, BOX), and ranges (e.g., INT4RANGE). MySQL includes JSON (up to 1 GB) and spatial types like POINT and POLYGON via OpenGIS. Oracle features XMLTYPE and SDO_GEOMETRY for spatial data. SQL Server has XML (with schema support), GEOGRAPHY/GEOMETRY for spatial, and HIERARCHYID for trees. SQLite handles JSON via extensions but core uses TEXT/BLOB; spatial requires add-ons. DB2 supports XML and DECFLOAT for decimal floating-point, with spatial via extensions. Boolean types are standard (e.g., BOOLEAN in PostgreSQL, BIT/TINYINT in others as 0/1). These extensions enhance RDBMS versatility but require compatibility checks for migrations.[74][75][70][65][66]
CategoryPostgreSQLMySQLOracleSQL ServerSQLiteDB2
Exact NumericINTEGER (4 bytes), NUMERIC (variable)INT, DECIMAL (up to 65 digits)NUMBER (up to 38 digits)INT, DECIMAL (1-38 precision)INTEGER (0-8 bytes affinity)INTEGER, DECIMAL (up to 31 digits)
Approximate NumericREAL (4 bytes), DOUBLE PRECISION (8 bytes)FLOAT (4 bytes), DOUBLE (8 bytes)BINARY_FLOAT (4 bytes), BINARY_DOUBLE (8 bytes)FLOAT, REALREAL (8 bytes affinity)REAL (4 bytes), DOUBLE (8 bytes)
Character StringCHAR/VARCHAR (up to 1 GB), TEXTCHAR/VARCHAR (up to 65 KB)CHAR/VARCHAR2 (up to 32 KB)CHAR/VARCHAR (up to 8 KB)TEXT (affinity)CHAR/VARCHAR (up to 32 KB)
Binary StringBYTEA (up to 1 GB)BINARY/VARBINARY, BLOB (up to 4 GB)RAW (up to 32 KB), BLOB (up to 4 GB)BINARY/VARBINARY (up to 8 KB)BLOB (affinity)BINARY/VARBINARY, BLOB (up to 2 GB)
Date/TimeDATE, TIMESTAMP (8 bytes), INTERVALDATE, DATETIME (5-8 bytes)DATE (7 bytes), TIMESTAMP (7-11 bytes)DATE (3 bytes), DATETIME2 (6-8 bytes)TEXT/REAL/INTEGER (affinity)DATE, TIMESTAMP (10 bytes)
BooleanBOOLEAN (1 byte)TINYINT(1) as 0/1NUMBER(1) as 0/1BIT (1 bit)INTEGER as 0/1 (affinity)No native; use SMALLINT as 0/1
JSON/XMLJSON/JSONB, XMLJSONXMLTYPEXMLJSON via extension, XML via TEXTXML
SpatialPOINT, BOX, etc. (geometric)POINT, LINESTRING (OpenGIS)SDO_GEOMETRYGEOGRAPHY, GEOMETRYVia extensionVia extension
This table highlights representative types; full details vary by version and configuration.[61][62][63][64][65][66]

Storage Limits and Scalability Constraints

Storage limits in relational database management systems (RDBMS) encompass the maximum capacities for core data structures, including databases, tables, rows, and columns, which are determined by factors such as the storage engine, page size, and underlying file system. These limits prevent overflow and maintain performance but can impose practical constraints on data-intensive applications. For example, row size limits ensure that data fits within memory pages during operations, while table size caps arise from file allocation mechanisms. Scalability constraints, meanwhile, address how RDBMS handle growth beyond these limits, primarily through vertical scaling (upgrading hardware on a single instance) or horizontal scaling (distributing data across nodes via replication or sharding), though the latter often introduces challenges like maintaining ACID properties and handling distributed transactions.[76][77][78][79][80] Different RDBMS exhibit distinct storage profiles tailored to their use cases, from embedded applications to enterprise-scale deployments. Open-source systems like MySQL and PostgreSQL prioritize flexibility with limits often tied to the operating system, enabling petabyte-scale databases on modern file systems, whereas proprietary systems like Oracle and SQL Server offer optimized high-end capacities for vertical scaling up to exabytes in clustered setups. SQLite, designed for lightweight, single-file storage, caps at terabytes to suit mobile and edge computing. The table below compares representative storage limits across these systems, highlighting conceptual differences rather than exhaustive variants.
Limit TypeMySQL (InnoDB)PostgreSQLOracle DatabaseSQL ServerSQLiteDB2
Maximum Database SizeFilesystem-dependent (e.g., petabytes across multiple tablespaces)No practical limit (filesystem-dependent)Filesystem-dependent (up to several exabytes with bigfile tablespaces)524 PB281 TB (default)Filesystem-dependent (up to 1 TB per tablespace in standard editions)
Maximum Table Size64 TB (default 16 KB page size)32 TBFilesystem-dependent (up to 128 TB per data file)No specific limit (up to 524 PB database size)281 TB (database-wide)1 TB (per table in standard)
Maximum Row Size65,535 bytes (excluding off-page BLOBs)1 GB per field (with TOAST compression for larger)Dependent on block size (~16 KB max)8,060 bytes (with row-overflow storage)1 GB (default)Dependent on page size (up to 32 KB)
Maximum Columns per Table1,0171,600 (limited by page fit)1,000 (non-virtual)1,024 (up to 30,000 sparse)2,000 (default)1,012
These limits are derived from default configurations and can vary with custom builds or editions; for instance, MySQL's InnoDB tablespace size scales to 256 TB with a 64 KB page size, but real-world constraints often stem from hardware.[76][77][81][79][80][82][83] Regarding scalability, most RDBMS excel in vertical scaling, where adding CPU, RAM, or storage to a single server extends capacity up to hardware limits—such as SQL Server's 128 GB RAM cap in Standard Edition or Oracle's support for 128 TB files on compatible file systems. However, horizontal scaling introduces constraints: distributed setups like MySQL Cluster or PostgreSQL's logical replication maintain consistency via two-phase commits, but suffer from increased latency and complexity in join-heavy workloads compared to NoSQL alternatives. Oracle Real Application Clusters (RAC) mitigates this through shared storage, scaling to thousands of nodes, yet requires significant infrastructure investment. In practice, exceeding single-instance limits often necessitates partitioning or sharding, with ACID guarantees limiting throughput in high-concurrency scenarios.

MariaDB vs MySQL 8.0: Performance Comparison in VPS and Container Environments

MariaDB (e.g., version 10.11+) and MySQL 8.0 exhibit notable differences in resource efficiency and concurrency handling, especially in constrained environments like VPS and container deployments where RAM and CPU cores are limited.

Memory Footprint

A clean MySQL 8.0 instance typically idles at 400–500 MB of RAM due to additional features and structures in the 8.0 release. In contrast, MariaDB idles closer to 150 MB, providing a significant advantage on low-memory VPS plans (e.g., 1–2 GB total RAM).

Connection and Thread Handling

MySQL 8.0 defaults to a one-thread-per-connection model, spawning a new OS thread for each client connection. Under high concurrency (e.g., 500+ simultaneous connections common in web applications), this leads to substantial CPU overhead from thread context switching and scheduling. MariaDB includes a built-in thread pool (enabled with thread_handling = pool-of-threads), which maintains a fixed pool of worker threads to service a much larger number of connections. This reduces overhead and prevents CPU thrashing, making MariaDB more performant in connection-heavy workloads without requiring external pooling layers.

Benchmark Insights and Use Cases

VPS-focused benchmarks indicate MariaDB often achieves higher throughput and lower latency in read/write mixes typical of web applications. For example:
  • WordPress and similar CMS platforms with many concurrent users benefit from MariaDB's lower memory usage and efficient thread management, allowing more headroom for caching and application processes.
  • High-concurrency applications (e.g., API backends, forums) see reduced risk of performance degradation under load spikes.
MySQL 8.0 may perform comparably or better in workloads leveraging its enhanced JSON functions, window functions, or when using enterprise-grade thread pool plugins (not available in community edition).

Configuration Recommendations

  • MariaDB on resource-limited VPS/containers: Set thread_handling = pool-of-threads, tune thread_pool_size to roughly 2–4× CPU cores (e.g., 16–32 on a 4-core instance), and allocate conservative innodb_buffer_pool_size (50–70% of available RAM after OS overhead).
  • MySQL 8.0: Rely on application-level or middleware connection pooling (e.g., ProxySQL, application frameworks), limit max_connections conservatively, and monitor for thread-related CPU spikes.
These differences stem from architectural priorities: MariaDB emphasizes efficiency in open-source, community-driven scenarios, while MySQL 8.0 aligns with Oracle's enterprise feature set. Real-world results vary by workload—always benchmark specific use cases. Source: MariaDB vs. MySQL 8.0: Performance Benchmarks & Configuration Guide for VPS

Advanced Storage and Organization

Partitioning Techniques

Partitioning techniques in relational database management systems (RDBMS) enable the subdivision of large tables into smaller, logical units known as partitions, which enhances manageability, query performance through partition pruning and elimination, and scalability by allowing independent maintenance operations on subsets of data. These techniques distribute data horizontally based on a partitioning key, typically a column or set of columns, and are particularly useful for very large databases (VLDBs) where full-table scans become inefficient. Major RDBMS implement partitioning to support high-volume workloads, such as time-series data or geographic distributions, but vary in supported methods, granularity, and integration with storage structures.[84][85][86] The primary partitioning strategies include range, list, and hash partitioning, with many systems supporting composite or subpartitioning for hybrid approaches. Range partitioning divides data into partitions based on contiguous ranges of values in the partition key, such as date ranges (e.g., monthly partitions for sales data), allowing efficient pruning for queries targeting specific intervals. This method is widely adopted for its alignment with natural data distributions like timestamps. List partitioning assigns rows to partitions based on explicit lists of discrete values in the key (e.g., regions like 'North America' or 'Europe'), ideal for categorical data with known, non-overlapping sets. Hash partitioning uses a hash function on the key to evenly distribute rows across a fixed number of partitions, promoting load balancing but offering less predictable pruning for range-based queries. Composite partitioning combines these (e.g., range subpartitioned by hash) to leverage multiple dimensions for finer control.[84][85][87] Support for these techniques differs across leading RDBMS, as summarized in the following table, which highlights native capabilities for table-level partitioning (excluding database-level sharding like DB2's DPF or SQL Server's Always On). Oracle provides the most extensive options, including automatic interval partitioning that extends range dynamically for sequential data like dates. PostgreSQL uses declarative partitioning since version 10, enforcing constraints on child tables for integrity. MySQL emphasizes storage engine compatibility (e.g., InnoDB), with KEY partitioning as a hash variant using internal functions for non-exact keys. Microsoft SQL Server focuses on range partitioning through user-defined partition functions and schemes mapped to filegroups, enabling sliding window scenarios for archiving but lacking native list or hash without custom implementations. IBM DB2 supports range partitioning in universal table spaces with options for partition-by-growth or partition-by-range, integrating with multidimensional clustering (MDC) for hash-like distribution within partitions.[84][85][87][88][89]
RDBMSRange PartitioningList PartitioningHash PartitioningComposite/SubpartitioningNotable Features/Extensions
OracleYesYesYesYes (e.g., range-hash)Interval (auto-extending range), reference (foreign key-based), system (user-defined). Supports up to 1024K partitions.[84]
PostgreSQLYesYesYesYes (multi-level)Declarative; partitions as child tables with constraints. No hard limit specified; practical limits due to performance impacts.[85]
MySQL (InnoDB)Yes (columns variant for non-integers)Yes (columns variant)YesYes (subpartitioning)KEY (hash on multiple columns); limited to 8192 partitions. Not supported in all storage engines.[87]
SQL ServerYes (left/right boundaries)No (emulatable via computed columns)No (emulatable via modulo)Limited (via schemes)Partition functions/schemes on filegroups; up to 15K partitions. Focuses on alignment for I/O performance.[88]
IBM DB2Yes (range/growth)NoPartial (via MDC)Yes (MDC with range)Attach/detach for maintenance; integrates with universal table spaces. Up to 32,767 partitions.[89]
Implementation details further differentiate systems: in Oracle and PostgreSQL, partitions can be individually indexed and maintained (e.g., via ALTER TABLE ... DROP PARTITION), reducing downtime for large-scale operations. MySQL requires the partition key to be the primary key prefix or unique index for enforcement, limiting flexibility in some schemas. SQL Server's partitioning is tightly coupled with storage filegroups, optimizing for parallel I/O but requiring careful scheme design to avoid hotspots. DB2 emphasizes data skew mitigation through rolling partitions for time-based data, with built-in support for partition elimination in queries. Overall, selection of a technique depends on data characteristics, query patterns, and administrative needs, with range being the most universally supported for its pruning efficiency in temporal workloads.[84][85][87][88][89]

Replication and Distribution

Replication and distribution are critical features in relational database management systems (RDBMS) for achieving high availability, fault tolerance, load balancing, and scalability. Replication involves duplicating data across multiple database instances to ensure redundancy and continuity, while distribution refers to partitioning and spreading data across multiple nodes or shards to handle large-scale workloads. Traditional RDBMS primarily emphasize replication for disaster recovery and read scaling, with varying levels of support for synchronous and asynchronous modes. Distribution, often through sharding, is less uniformly implemented in core RDBMS engines, relying on extensions or specialized configurations for horizontal scaling beyond single-instance limits.[90] In PostgreSQL, replication is built into the core engine and supports both physical and logical modes. Physical replication uses write-ahead logging (WAL) to stream changes asynchronously from a primary server to one or more standbys, enabling hot standby for read queries and automatic failover. Synchronous replication is configurable via parameters like synchronous_commit, ensuring zero data loss at the cost of higher latency. Logical replication, available since version 10, employs a publish-subscribe model to replicate specific tables or databases based on primary keys, supporting cross-version and heterogeneous setups. For distribution, PostgreSQL lacks native sharding but offers declarative partitioning for tables, and extensions like Citus enable distributed queries across shards for horizontal scaling.[91][92] MySQL provides robust asynchronous replication as its foundational mechanism, where a source (master) server logs binary changes that replicas apply in near real-time, facilitating read scaling across multiple slaves. Semi-synchronous replication ensures the source waits for at least one replica to acknowledge receipt before committing, balancing consistency and performance. Group Replication introduces multi-primary synchronous replication for high availability clusters, using Paxos-based consensus for automatic failover. Sharding is not built into standard MySQL but can be achieved via MySQL Cluster (NDB storage engine), which distributes data across data nodes using hash-based partitioning, or external tools like Vitess for proxy-based sharding in large deployments.[93][94] Oracle Database excels in enterprise-grade replication through Oracle Data Guard, which maintains physical or logical standbys for disaster recovery. Physical standby replication is block-for-block synchronous or asynchronous, achieving zero data loss in maximum protection mode via redo transport. Logical replication via SQL Apply supports selective data movement for reporting. For advanced distribution, Oracle Sharding—introduced in 12.2—horizontally partitions tables across independent database shards using a sharding key, with global services for routing queries and automatic rebalancing. Oracle GoldenGate complements this with real-time, low-latency capture and delivery for heterogeneous replication across systems.[95][96] Microsoft SQL Server supports three primary replication types: snapshot (initial full data copy), transactional (ongoing change propagation via log reader and distributor), and merge (bi-directional changes with conflict resolution for mobile scenarios). Transactional replication operates asynchronously but can be tuned for near-synchronous behavior. Always On Availability Groups provide synchronous or asynchronous commit across replicas for high availability, integrating failover clustering. Distribution in SQL Server relies on table partitioning rather than native sharding; horizontal scaling often uses federation in Azure SQL Database, where data is distributed across elastic pools or shards via application logic.[97][98]
RDBMSReplication TypesSync/Async SupportNative Sharding/Distribution
PostgreSQLPhysical (streaming WAL), Logical (publish-subscribe)Async default; sync optionalPartitioning; extensions (e.g., Citus) for sharding
MySQLMaster-slave, Group, Semi-syncAsync primary; semi-sync; sync in NDBNDB Cluster partitioning; external tools
OracleData Guard (physical/logical), GoldenGateSync/asyncBuilt-in Oracle Sharding
SQL ServerSnapshot, Transactional, Merge; Always On GroupsAsync primary; sync in Always OnTable partitioning; federation in cloud
These features highlight trade-offs: open-source systems like PostgreSQL and MySQL prioritize flexibility and cost-effectiveness for replication, while proprietary ones like Oracle and SQL Server offer integrated high-availability suites with stronger enterprise distribution options. Selection depends on workload demands, such as read-heavy analytics favoring asynchronous replication or mission-critical applications requiring synchronous modes.[99][100]

Procedural and Extension Features

Stored Procedures and Triggers

Stored procedures and triggers are key procedural extensions in relational database management systems (RDBMS), enabling server-side logic execution for tasks like data validation, auditing, and business rule enforcement. Stored procedures are precompiled routines that encapsulate SQL and procedural code, callable by name to promote code reuse, reduce network traffic, and enhance security by limiting direct table access. Triggers, conversely, are event-driven mechanisms that automatically execute in response to data manipulation language (DML) or data definition language (DDL) events, ensuring data integrity without client-side intervention. While the SQL standard (ISO/IEC 9075) outlines basic support for both via SQL/PSM, implementations vary significantly across RDBMS, reflecting proprietary extensions and performance optimizations.

Stored Procedures

Major RDBMS provide robust stored procedure support, but differ in language, parameter handling, transaction control, and integration features. Oracle Database uses PL/SQL for procedures, which can be standalone or packaged, supporting input/output parameters and editioning for schema evolution; procedures are created via CREATE [OR REPLACE] PROCEDURE and invoked with EXECUTE, offering automatic recompilation for performance. MySQL employs a SQL/PSM-compliant dialect for procedures, defined with CREATE PROCEDURE and supporting IN, OUT, and INOUT parameters, deterministic declarations, and definer/invoker security contexts; however, it lacks package structures and advanced debugging tools found in Oracle. PostgreSQL introduced true procedures in version 11 via CREATE [OR REPLACE] PROCEDURE, using PL/pgSQL or other languages like SQL or Python; unlike functions, procedures support autonomous transactions (COMMIT/ROLLBACK) and multiple OUT parameters but do not return values directly, invoked via CALL for better encapsulation of multi-statement operations. Microsoft SQL Server leverages Transact-SQL (T-SQL) for procedures, created with CREATE PROCEDURE and featuring optional encryption, schema binding, and EXECUTE AS for impersonation; it excels in CLR integration for .NET code, temporary procedures (# or ## prefixed), and automatic plan caching for efficiency, though recompilation may be needed for schema changes. These differences impact portability: for instance, Oracle and SQL Server procedures often include vendor-specific constructs like autonomous transactions or extended stored procedures (deprecated in SQL Server), while MySQL and PostgreSQL adhere closer to standards but require adaptation for complex logic. Benefits across systems include reduced client-server roundtrips—e.g., a single CALL replaces multiple queries—and enhanced security through privilege isolation, as procedures execute under definer rights to prevent SQL injection.
RDBMSLanguage/ExtensionsKey FeaturesLimitations
OraclePL/SQLPackages, editioning, OR REPLACERequires direct privilege grants; no native CLR
MySQLSQL/PSMIN/OUT/INOUT params, deterministicNo packages; functions can't handle transactions
PostgreSQLPL/pgSQL, SQLAutonomous transactions, multi-languageNo return value; functions preferred for scalars
SQL ServerT-SQL, CLREncryption, schema binding, temp procsExtended procs deprecated; nesting limits

Triggers

Triggers enforce reactive logic, with variations in timing, scope, and event support. Oracle supports DML triggers (BEFORE/AFTER/INSTEAD OF on INSERT/UPDATE/DELETE), DDL triggers (on schema/database events), and system triggers (e.g., LOGON), implemented in PL/SQL and fired automatically; compound triggers allow shared state across timing points, and they support enabling/disabling for maintenance. MySQL limits triggers to row-level DML (BEFORE/AFTER for INSERT/UPDATE/DELETE on tables only), using CREATE TRIGGER with OLD/NEW row references and optional FOLLOWS/PRECEDES ordering; it lacks statement-level or INSTEAD OF triggers, and cascading foreign keys do not activate them. PostgreSQL offers versatile triggers: row/statement-level BEFORE/AFTER/INSTEAD OF for INSERT/UPDATE/DELETE/TRUNCATE on tables/views, plus constraint triggers (deferred AFTER ROW); CREATE TRIGGER uses EXECUTE FUNCTION with WHEN conditions and REFERENCING for transition tables, enabling advanced auditing without recursion risks. SQL Server categorizes triggers as DML (FOR/AFTER/INSTEAD OF on tables/views), DDL (database/server-wide on schema changes), and logon triggers (on session establishment); CREATE TRIGGER supports up to 32 nesting levels, CLR triggers for .NET, and recursive firing (configurable via sp_configure), but DML triggers cannot issue certain DDL statements like CREATE INDEX. Comparative strengths include PostgreSQL's flexibility for views (INSTEAD OF) and conditions, ideal for complex integrity checks, versus MySQL's simplicity for basic row auditing but limited event coverage. Performance considerations arise from trigger overhead—e.g., Oracle and SQL Server allow disabling to bypass during bulk loads—while all systems use definer security to align with procedural privileges, ensuring audited actions remain controlled.
RDBMSTrigger TypesTiming/Level OptionsKey Limitations
OracleDML, DDL, systemBEFORE/AFTER/INSTEAD OF; row/statementRole privileges ineffective inside
MySQLDML onlyBEFORE/AFTER; row onlyNo views, no TRUNCATE, no cascading
PostgreSQLDML, constraintBEFORE/AFTER/INSTEAD OF; row/statementNo SELECT events; subqueries in WHEN restricted
SQL ServerDML, DDL, logonFOR/AFTER/INSTEAD OF; rowNo temp table DDL events; 32-level nesting max

Additional Objects and Extensions

Relational database management systems (RDBMS) often extend core relational features with additional objects and capabilities to handle specialized data types, advanced querying, and custom functionalities, enabling support for modern applications like geospatial analysis, document storage, and full-text retrieval. These extensions vary significantly across systems, reflecting differences in design philosophy, such as PostgreSQL's emphasis on extensibility through modular add-ons and Oracle's integration of enterprise-grade object-oriented elements. User-defined types (UDTs), for instance, allow developers to create custom data structures, while full-text search and spatial extensions address non-relational data needs without requiring external tools. A key area of differentiation is support for user-defined types and domains. PostgreSQL provides robust UDTs, including composite types, enums, ranges, and domains for constraint validation, allowing inheritance and extensibility via procedural languages like PL/pgSQL.[61] In contrast, Oracle Database supports object-relational types through CREATE TYPE, including object tables, nested tables, and VARRAYs for complex hierarchical data.[101] Microsoft SQL Server enables UDTs via Common Language Runtime (CLR) integration, permitting .NET-based custom types with methods and properties.[102] MySQL offers limited UDT support through user-defined functions (UDFs) and plugins but lacks native object-oriented typing, relying instead on JSON or spatial types for semi-structured data.[103] Full-text search capabilities further highlight extensibility differences. PostgreSQL's built-in full-text search uses tsvector and tsquery types with ranking functions like ts_rank, supporting multiple languages and stemming via extensions.[104] Oracle Text provides advanced indexing with theme extraction, fuzzy matching, and integration with SQL via the CONTAINS operator for large-scale document management.[105] SQL Server's Full-Text Search service includes semantic search and proximity operators, optimized for integration with Windows environments.[106] MySQL supports full-text indexing on InnoDB and MyISAM tables with natural language and Boolean modes, though it requires explicit column configuration and lacks advanced ranking without plugins.[107] Spatial data extensions are crucial for geographic information systems (GIS). PostgreSQL relies on the PostGIS extension for geometry, geography, and raster types, enabling spatial indexing with GIST and operations compliant with Open Geospatial Consortium (OGC) standards. Oracle Spatial and Graph offers SDO_GEOMETRY for vector data, 3D modeling, and network analysis, with built-in topology support.[108] SQL Server includes geometry and geography types for planar and geodetic calculations, supporting spatial indexes and OGC methods.[109] MySQL provides spatial types like POINT, LINESTRING, and POLYGON with MyISAM or InnoDB storage, including basic functions but limited compared to dedicated extensions.[110] Support for semi-structured data through XML and JSON represents another extension layer. All major RDBMS handle JSON natively: PostgreSQL with json and jsonb types for querying and indexing; Oracle via JSON datatypes and SQL/JSON functions; SQL Server with JSON functions like ISJSON and JSON_MODIFY; and MySQL using a native JSON type with path-based extraction. XML support is more mature in enterprise systems—Oracle's XML DB repository enables XQuery and schema validation,[111] while SQL Server offers XML data types with XQuery and type constraints;[112] PostgreSQL provides basic XML functions but recommends extensions for advanced use; MySQL includes XML utilities like ExtractValue but lacks a dedicated XML type.[113] Procedural extensions and plugins enhance customization. PostgreSQL's architecture allows loading extensions like pg_trgm for trigram searches or hstore for key-value pairs, with support for multiple procedural languages (PL/pgSQL, PL/Perl, PL/Python). Oracle uses PL/SQL packages for modular code, including built-in ones like DBMS_XMLGEN for XML generation.[114] SQL Server's CLR allows .NET assemblies for extended procedures. MySQL supports UDFs in C/C++ and plugins for storage engines or authentication, though extensibility is more server-focused than object-oriented. These features enable RDBMS to adapt to domain-specific needs, such as scientific computing or web applications, without full system replacement.[115][102][116]
FeaturePostgreSQLMySQLOracle DatabaseSQL Server
User-Defined TypesComposite, enums, ranges, domains; inheritance support[61]Limited via UDFs/plugins; no native objects[103]Object types, nested tables, VARRAYs[101]CLR-based UDTs with methods[102]
Full-Text SearchBuilt-in tsvector/tsquery; multilingual[104]InnoDB/MyISAM indexing; Boolean mode[107]Oracle Text with themes/fuzzy matching[105]Full-Text service; semantic search[106]
Spatial ExtensionsPostGIS for OGC compliance[117]Native types (POINT, etc.); basic functions[110]SDO_GEOMETRY; topology/3D[108]Geometry/geography types; OGC methods[109]
XML SupportBasic functions; extension-recommendedXML utilities (ExtractValue)[113]XML DB with XQuery[111]XML type with XQuery[112]
JSON Supportjson/jsonb with indexingNative JSON type; path queries[74]JSON datatypes; SQL/JSON[118]JSON functions (ISJSON, etc.)[119]
Procedural ExtensionsPL/pgSQL, PL/Perl, PL/Python; extensionsUDFs in C++; plugins[116]PL/SQL packages[114]CLR assemblies[102]

Security and Access Management

Authentication Mechanisms

Relational database management systems (RDBMS) employ authentication mechanisms to verify the identity of users or applications attempting to connect, ensuring only authorized entities access data. These mechanisms typically fall into internal methods, which rely on credentials stored within the database, and external methods, which integrate with operating systems, directory services, or cryptographic protocols for enhanced security and single sign-on (SSO) capabilities. Internal authentication often uses username-password pairs with hashing to protect against interception, while external options like LDAP or Kerberos delegate verification to enterprise identity providers, reducing credential management overhead.[120][121] Major RDBMS vary in their supported methods, balancing simplicity, security, and integration needs. For instance, password-based authentication is universal but vulnerable to brute-force attacks if not paired with strong hashing or multi-factor requirements. External integrations like Kerberos enable ticket-based SSO in networked environments, particularly in Windows domains, while certificate authentication leverages public key infrastructure (PKI) for mutual verification without transmitting secrets. The choice depends on deployment context, such as on-premises versus cloud, with modern systems increasingly supporting multi-factor authentication (MFA) to mitigate risks from compromised credentials.[122]
RDBMSInternal Password AuthenticationOS AuthenticationLDAPKerberos/GSSAPICertificate/PKIMFA Support
PostgreSQLYes (MD5, SCRAM-SHA-256 hashing)Yes (peer, ident for local)YesYes (GSSAPI)Yes (SSL client certs)No native; via extensions
MySQLYes (caching_sha2_password default, mysql_native_password)Partial (PAM on Unix)Yes (simple, SASL)Yes (via plugins)Partial (X.509)Yes (up to 3 factors)
Oracle DatabaseYes (12C with PBKDF2/SHA-512)Yes (OS groups, e.g., OPS$)Yes (via OID)Yes (Kerberos v5)Yes (TLS/PKI wallets)Yes (push, OTP via Duo/RADIUS)
SQL ServerYes (SQL logins with hashing)Yes (Windows integrated)Partial (via Azure AD)Yes (via Kerberos in AD)Yes (asymmetric keys)Yes (via Azure AD)
PostgreSQL offers flexible, pluggable methods configurable via pg_hba.conf, emphasizing SCRAM-SHA-256 for salted hashing to resist offline attacks, though it lacks built-in MFA, relying on PAM or external proxies for additional factors. MySQL's pluggable architecture allows seamless switching between hashing schemes, with caching_sha2 providing RSA-based encryption for secure transmission, and Enterprise Edition adding advanced LDAP/AD integration for SSO. Oracle excels in enterprise environments with robust PKI support using wallets for certificate storage and Kerberos for cross-realm authentication, alongside profile-based password policies enforcing expiration and complexity. SQL Server prioritizes Windows Authentication for seamless AD integration, using Kerberos delegation for service accounts, while SQL logins serve standalone scenarios; Azure AD integration extends this to cloud MFA without on-premises dependencies.[121][122][120][123] These mechanisms evolve to address threats like credential stuffing, with all systems recommending TLS encryption for connections and regular audits. For example, Oracle and SQL Server's external options facilitate compliance with standards like GDPR through centralized identity management, whereas open-source systems like PostgreSQL and MySQL provide cost-effective alternatives via community extensions.[124]

Authorization and Role-Based Controls

Authorization in relational database management systems (RDBMS) refers to the process of determining what actions authenticated users or processes can perform on database objects, such as tables, views, and schemas, while role-based access control (RBAC) implements this by assigning collections of privileges to roles, which are then granted to users or other roles for simplified management.[125][126] This approach reduces administrative overhead by avoiding individual privilege assignments and supports the principle of least privilege, where users receive only necessary permissions.[127][128] Major RDBMS vary in their RBAC implementations, including role attributes, activation mechanisms, and integration with finer-grained controls like row-level security. In PostgreSQL, roles serve as both users and groups, unifying identity and authorization management; privileges are granted directly or inherited from parent roles by default, with options like NOINHERIT to restrict automatic inheritance.[128] Key attributes include SUPERUSER for bypassing checks, CREATEDB for database creation, and BYPASSRLS for evading row-level security policies, which enable granular data access restrictions based on user roles.[128] Roles support connection limits and replication privileges, making them versatile for enterprise environments.[128] MySQL introduced roles in version 8.0 to group privileges, allowing administrators to create custom roles (e.g., CREATE ROLE 'app_developer') and grant them to users via GRANT, with privileges like SELECT or INSERT on specific schemas.[127] Unlike direct user grants, roles must be explicitly activated using SET ROLE (e.g., SET ROLE ALL), and mandatory roles—defined in the mandatory_roles system variable—are automatically applied and irrevocable for all users.[127] This activation model prevents unintended privilege escalation but requires application-level handling for seamless use.[127] Oracle Database employs a mature RBAC system where roles bundle system, object, and administrative privileges, granted via GRANT statements that can be local (to a pluggable database) or common (across a container database).[125] Predefined roles such as DBA (full administrative access), CONNECT (basic connectivity), and RESOURCE (object creation privileges) simplify setup, while secure application roles—enabled only through trusted PL/SQL packages—add an extra layer by tying activation to application context, reducing risks from password exposure.[125] Roles can be nested, with indirect grants activating implicitly upon parent role enablement via SET ROLE.[125] Microsoft SQL Server distinguishes between server-level and database-level roles for hierarchical authorization; fixed server roles like sysadmin manage instance-wide permissions (e.g., creating databases), while database-level fixed roles such as db_owner (full database control) and db_datareader (read-only access) handle schema-specific actions.[129][126] User-defined roles, created with CREATE ROLE, allow custom permission sets using GRANT and DENY, supporting flexible RBAC without altering fixed roles to avoid escalation.[126] Since SQL Server 2022, additional least-privilege server roles (e.g., ##MS_LoginManager## for login creation) enhance security granularity.[129] IBM DB2 supports RBAC through roles that aggregate privileges, assignable to users, groups, or PUBLIC via GRANT ROLE, with predefined roles like DBADM for database administration and SECADM for security management.[130] Authorization checks occur at the database level, integrating roles with authorization IDs for fine-tuned control over SQL operations and data access.[130] In contrast, lightweight systems like SQLite lack native RBAC, relying on file-system permissions for access control, which limits multi-user scenarios without custom application logic.[131]
RDBMSRole Creation and GrantingPredefined Roles ExamplesActivation/Inheritance MechanismAdvanced Features
PostgreSQLCREATE ROLE with attributes; GRANT for privileges/rolesSUPERUSER, CREATEDBDefault inheritance; NOINHERIT option; SET ROLERow-level security (RLS) bypass
MySQLCREATE ROLE; GRANT to users/rolesNone fixed; custom mandatory rolesExplicit SET ROLE; auto-activation on loginMandatory roles; default role assignment
OracleCREATE ROLE; GRANT local/commonDBA, CONNECT, RESOURCESET ROLE; implicit for nested; package-basedSecure application roles; CDB/PDB scoping
SQL ServerCREATE ROLE (user-defined); fixed rolesServer: sysadmin; DB: db_ownerAutomatic for members; no explicit activationServer vs. DB levels; least-privilege roles
DB2CREATE ROLE; GRANT ROLE to IDs/groupsDBADM, SECADMImplicit via assignment; nested rolesIntegration with auth IDs; PUBLIC grants
SQLiteNo native support; app-implementedNoneN/AFile-system only; custom hooks possible

Standards and Terminology

SQL Compliance and Dialects

Relational database management systems (RDBMS) implement the Structured Query Language (SQL) as standardized by the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO), with the latest iteration being SQL:2023 (ISO/IEC 9075:2023).[132] These standards define core features for data definition, manipulation, and control to promote portability across systems, divided into levels such as Entry, Intermediate, and Full conformance. However, no RDBMS achieves complete adherence due to proprietary extensions that enhance performance, scalability, or integration with vendor ecosystems. Compliance levels vary, with dialects introducing syntax variations, additional functions, and non-standard constructs that can impact query portability.[133] PostgreSQL demonstrates the highest compliance among major open-source RDBMS, supporting at least 170 of 177 mandatory features in the SQL:2023 Core specification as of version 18 (released September 2025).[20] Its dialect closely mirrors standard SQL syntax while extending it with advanced object-relational capabilities, such as user-defined types, arrays, and full support for Common Table Expressions (CTEs) in all DML operations. Procedural extensions via PL/pgSQL enable complex logic akin to full programming languages, including support for multiple procedural languages like Python and Perl. These features make PostgreSQL suitable for applications requiring strict standards adherence without sacrificing extensibility. MySQL provides partial compliance with ANSI/ISO SQL standards, implementing core elements from SQL:1999 through SQL:2011 but prioritizing optimization for web-scale workloads over full conformance (as of MySQL 8.4).[134] An optional ANSI mode (sql_mode='ANSI') aligns behavior closer to standards by treating double quotes as identifiers and pipes as concatenation, but standard mode includes non-compliant shortcuts like implicit joins. The MySQL dialect features unique extensions such as the HANDLER statement for direct table access and JSON functions compliant with SQL:2016 drafts, alongside storage engine-specific optimizations like InnoDB's row-level locking. This results in a lightweight, performant dialect but requires mode adjustments for standard SQL portability. Oracle Database achieves core conformance to SQL:2016 (ISO/IEC 9075-2:2016 and -11:2016), covering foundational data manipulation and schema features while recognizing legacy constructs as vendor extensions (as of Oracle Database 26ai, released October 2025).[135] The PL/SQL dialect integrates procedural programming deeply, supporting advanced constructs like autonomous transactions and hierarchical queries via CONNECT BY, which extend beyond standard SQL for enterprise analytics. Oracle's extensions emphasize scalability, including analytic functions and XML/SQL integration per SQL/XML standards, making it ideal for large-scale, mission-critical environments despite syntax divergences from pure ANSI SQL.[136] Microsoft SQL Server's T-SQL dialect supports the Entry level of SQL:2008 with substantial optional features from later standards, including window functions, CTEs, and MERGE statements, enabled through ANSI compatibility options like SET ANSI_DEFAULTS (as of SQL Server 2025).[137] It diverges with Microsoft-specific enhancements, such as TOP with ties for ranking and spatial data types, optimized for integration with .NET and Azure services. While not claiming full ISO conformance, T-SQL's extensions facilitate advanced analytics and security features, like row-level security, but may require rewrites for cross-platform migration due to non-standard behaviors in string handling and date functions. The table below summarizes key compliance and dialect aspects for these systems, highlighting representative differences:
RDBMSPrimary Compliance LevelKey Standard SupportDialect NameNotable Extensions/Differences
PostgreSQLSQL:2023 Core (170/177 features)Full CTEs, window functions (ROWS/RANGE)PL/pgSQLCustom types, multi-language procedures, full-text search with stemming[20][138]
MySQLPartial (SQL:2011 core)CTEs in DML statements, basic window functionsMySQL SQLHANDLER for direct access, JSON per RFC 7159, ANSI mode for quotes/pipes[134][138]
OracleSQL:2016 CoreAnalytic functions, SQL/XMLPL/SQLCONNECT BY for hierarchies, autonomous transactions, partitioning syntax[135]
SQL ServerSQL:2008 Entry + extensionsMERGE, window functions, spatial typesT-SQLTOP with ties, integrated analytics, case-sensitive defaults[137][139]
These variations underscore the trade-offs between standards fidelity and vendor-specific optimizations, influencing choices based on application needs like portability versus performance. Cloud-managed variants of these RDBMS (e.g., Amazon Aurora for MySQL/PostgreSQL, Azure SQL for SQL Server) generally inherit base compliance but introduce additional proprietary extensions for scalability and integration.[5][138]

Database vs. Schema Concepts

In relational database management systems (RDBMS), the concepts of "database" and "schema" provide hierarchical organization for data structures such as tables, views, and indexes, but their definitions and relationships differ significantly across implementations, leading to potential confusion in cross-system comparisons.[140] According to the ANSI SQL standard, a schema is a named collection of database objects that share the same namespace, while a database encompasses one or more such schemas, serving as the top-level container for persistent data storage.[141] However, major RDBMS vendors interpret these terms variably, often aligning them with their architecture for user management, security, and resource allocation. In MySQL, the terms "database" and "schema" are effectively synonymous, with a database acting as a logical container (implemented as a directory under the data directory) that holds tables, views, and other objects without a distinct sub-level for schemas (as of MySQL 8.4).[142] The CREATE SCHEMA statement is merely an alias for CREATE DATABASE, allowing users to create isolated environments for related objects while requiring the CREATE privilege.[142] This simplification suits MySQL's design for web applications and smaller-scale deployments, where multiple "databases" (schemas) can coexist within a single server instance, but it deviates from standards by lacking a separate schema namespace within a database. PostgreSQL adheres more closely to the ANSI SQL model, treating a database as an independent unit within a cluster that contains one or more schemas, each serving as a namespace for objects like tables, functions, and data types (as of version 18).[140] Schemas enable logical grouping and prevent naming conflicts across different projects or modules within the same database, with access controlled via roles and privileges at the schema level.[140] For instance, a single PostgreSQL database might include a public schema for general use and custom schemas like sales or inventory for specialized data, promoting modularity without necessitating multiple physical databases.[143] Oracle Database defines a schema as a logical container for schema objects (e.g., tables, indexes, procedures) owned by a specific user account, with the schema name matching the username (as of Oracle Database 26ai).[144] The database itself is the overarching structure comprising multiple schemas, tablespaces for physical storage, and non-schema elements like roles and system dictionaries, allowing schemas from different users to coexist while objects are distributed across tablespaces for performance optimization.[144] This user-schema linkage integrates security and ownership, making it ideal for enterprise environments with fine-grained access controls, though it requires explicit grants for cross-schema interactions. Microsoft SQL Server positions schemas as owned namespaces within a database, grouping related objects under a principal such as a user or role, with the default dbo schema handling unassigned items (as of SQL Server 2025).[145] A database serves as the primary container for multiple schemas, log files, and data, enabling logical separation for security and maintenance without physical isolation.[145] Ownership can be transferred via ALTER [AUTHORIZATION](/page/Authorization), but the schema owner retains overarching control, facilitating scenarios like multi-tenant applications where schemas delineate tenant boundaries.[145]
RDBMSDatabase RoleSchema RoleKey Relationship/Differences
MySQLTop-level container (directory) for objectsSynonymous with database; no sub-namespaceNo hierarchy; CREATE SCHEMA ≡ CREATE DATABASE[142]
PostgreSQLIndependent unit in cluster containing schemasNamespace for objects within a databaseMultiple schemas per database; promotes modularity[140]
OracleOverarching structure with users, tablespacesUser-owned container for objectsSchema tied to user; spans tablespaces, not vice versa[144]
SQL ServerContainer for schemas, files, and logsOwned namespace within databaseSchemas group objects; default dbo; transferable ownership[145]

References

  1. What is a Relational Database? - IBM
  2. What is a Database? | IBM
  3. The relational database - IBM
  4. A relational model of data for large shared data banks
  5. Gartner Magic Quadrant for Cloud Database Management Systems
  6. DB-Engines Ranking
  7. AWS, Oracle, Google, Microsoft Top Cloud DBMS Market: Gartner
  8. DB-Engines shares Q1 2025 database industry rankings and top ...
  9. https://ieeexplore.ieee.org/document/7436682
  10. A history and evaluation of System R | Communications of the ACM
  11. [PDF] A History and Evaluation of System R
  12. Introduction to Oracle Database
  13. SQL/DS: IBM's First RDBMS - ACM Digital Library
  14. [PDF] DATABASE SYSTEMS: A BRIEF TIMELINE - s2.SMU
  15. 50 Years of Queries - Communications of the ACM
  16. MySQL Introduction - ACM Digital Library
  17. Early History of SQL - IEEE Xplore
  18. What Is SQL Server? - SQL Server - Microsoft Learn
  19. MySQL: Understanding What It Is and How It's Used - Oracle
  20. PostgreSQL: About
  21. Snowflake key concepts and architecture
  22. About SQLite
  23. Documentation: 18: 17.6. Supported Platforms - PostgreSQL
  24. Supported Platforms: MySQL Database
  25. Operating System Checklist for Oracle Database Installation on Linux
  26. Supported Operating Systems - Oracle Help Center
  27. SQL Server 2022: Hardware & software requirements - Microsoft Learn
  28. System requirements for IBM Db2 for Linux, UNIX, and Windows
  29. Supported operating systems for database application development
  30. https://www.sqlite.org/whentouse.html
  31. SQLite Home Page
  32. https://www.sqlite.org/
  33. Database Deployment Options? | Oracle
  34. MySQL :: MySQL 8.0 Reference Manual :: 2 Installing MySQL
  35. PostgreSQL: Downloads
  36. Editions and Supported Features of SQL Server 2022 - Microsoft Learn
  37. Db2 database product editions and offerings - IBM
  38. Amazon RDS for PostgreSQL - AWS
  39. MySQL :: MySQL Products
  40. MySQL :: MySQL 8.0 Reference Manual :: 15.1.20 CREATE TABLE Statement
  41. SQL Language Reference
  42. CREATE TABLE
  43. None
  44. https://dev.mysql.com/doc/refman/8.4/en/partitioning-limitations.html
  45. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16#temporary-tables
  46. CREATE VIEW
  47. MySQL :: MySQL 8.0 Reference Manual :: 15.1.23 CREATE VIEW Statement
  48. SQL Language Reference
  49. Views - SQL Server
  50. https://learn.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-ver16
  51. SQL VIEW Comparison in SQL Server, Oracle and PostgreSQL
  52. Managing Indexes - Oracle Help Center
  53. Documentation: 18: 11.2. Index Types - PostgreSQL
  54. MySQL :: MySQL 8.0 Reference Manual :: 10.3.1 How MySQL Uses Indexes
  55. Indexes - SQL Server
  56. https://www.ibm.com/docs/en/db2/11.5?topic=indexes-types
  57. 5.5. Constraints
  58. MySQL :: MySQL 8.0 Reference Manual :: 1.6.3 How MySQL Deals with Constraints
  59. Unique constraints and check constraints - SQL Server
  60. https://www.ibm.com/docs/en/db2/11.5?topic=constraints-check
  61. Documentation: 18: Chapter 8. Data Types - PostgreSQL
  62. MySQL :: MySQL 8.0 Reference Manual :: 13 Data Types
  63. Data Types - Oracle Help Center
  64. Data types (Transact-SQL) - SQL Server - Microsoft Learn
  65. Datatypes In SQLite
  66. https://www.ibm.com/docs/en/db2/11.5?topic=elements-data-types
  67. https://www.postgresql.org/docs/current/datatype-numeric.html
  68. https://dev.mysql.com/doc/refman/8.0/en/integer-types.html
  69. https://dev.mysql.com/doc/refman/8.0/en/fixed-point-types.html
  70. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Data-Types.html#GUID-A3C0D836-BADB-44E5-A5D4-265BA5968483
  71. decimal and numeric (Transact-SQL) - SQL Server
  72. https://dev.mysql.com/doc/refman/8.0/en/char.html
  73. https://dev.mysql.com/doc/refman/8.0/en/blob.html
  74. https://dev.mysql.com/doc/refman/8.0/en/json.html
  75. https://dev.mysql.com/doc/refman/8.0/en/spatial-type-overview.html
  76. MySQL :: MySQL 8.0 Reference Manual :: 17.22 InnoDB Limits
  77. Appendix K. PostgreSQL Limits
  78. Database Administrator's Reference
  79. Maximum Capacity Specifications for SQL Server - SQL Server
  80. Implementation Limits For SQLite
  81. https://docs.oracle.com/en/database/oracle/oracle-database/23/refrn/physical-database-limits.html
  82. https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_page_size
  83. https://www.ibm.com/docs/en/db2/11.5?topic=limits-specification
  84. MariaDB vs. MySQL 8.0: Performance Benchmarks & Configuration Guide for VPS
  85. 2 Partitioning Concepts - Oracle Help Center
  86. Documentation: 18: 5.12. Table Partitioning - PostgreSQL
  87. MySQL 8.4 Reference Manual :: 26.1 Overview of Partitioning in ...
  88. MySQL 8.4 Reference Manual :: 26.2 Partitioning Types
  89. Partitioned Tables and Indexes - SQL - Microsoft Learn
  90. Table partitioning - IBM
  91. Database Management Systems (DBMS) Comparison: MySQL, Postgr
  92. Chapter 26. High Availability, Load Balancing, and Replication
  93. Documentation: 18: Chapter 29. Logical Replication - PostgreSQL
  94. MySQL :: MySQL 8.0 Reference Manual :: 19 Replication
  95. https://dev.mysql.com/doc/refman/8.0/en/mysql-cluster.html
  96. Concepts and Administration
  97. Overview of Oracle Sharding
  98. Types of Replication - SQL Server - Microsoft Learn
  99. SQL Server Replication - Microsoft Learn
  100. https://www.severalnines.com/blog/basic-administration-comparison-between-oracle-mssql-mysql-postgresql/
  101. PostgreSQL vs MySQL: In-depth Comparison & Performance Analysis
  102. https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/CREATE-TYPE.html
  103. https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration/clr-integration-overview
  104. https://dev.mysql.com/doc/refman/8.0/en/server-loadable-functions.html
  105. https://www.postgresql.org/docs/current/textsearch.html
  106. https://docs.oracle.com/en/database/oracle/oracle-database/23/ccapp/index.html
  107. https://learn.microsoft.com/en-us/sql/relational-databases/search/full-text-search
  108. https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
  109. https://docs.oracle.com/en/database/oracle/oracle-database/23/spatl/index.html
  110. https://learn.microsoft.com/en-us/sql/relational-databases/spatial/spatial-data-sql-server
  111. https://dev.mysql.com/doc/refman/8.0/en/spatial-types.html
  112. https://docs.oracle.com/en/database/oracle/oracle-database/23/adlob/index.html
  113. https://learn.microsoft.com/en-us/sql/relational-databases/xml/xml-data-type-and-columns-sql-server
  114. https://dev.mysql.com/doc/refman/8.0/en/xml-functions.html
  115. https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/index.html
  116. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/toc.htm
  117. https://dev.mysql.com/doc/refman/8.0/en/server-plugins.html
  118. https://www.postgresql.org/docs/current/contrib.html
  119. https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/JSON.html
  120. https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server
  121. 3 Configuring Authentication - Database - Oracle Help Center
  122. 20.3. Authentication Methods
  123. MySQL :: MySQL 8.0 Reference Manual :: 8.2.17 Pluggable Authentication
  124. https://learn.microsoft.com/en-us/sql/relational-databases/security/choose-an-authentication-mode?view=sql-server-ver16
  125. 20 Introduction to Strong Authentication - Oracle Help Center
  126. Configuring Privilege and Role Authorization - Oracle Help Center
  127. Database-level roles - SQL Server - Microsoft Learn
  128. MySQL :: MySQL 8.0 Reference Manual :: 8.2.10 Using Roles
  129. 21.2. Role Attributes
  130. Server-Level Roles - SQL - Microsoft Learn
  131. Roles - IBM
  132. https://www.sqlite.org/forum/info/2e4b58ca45b0de363d3d652fc7ebcfed951daa8b0e585187df92b37a229d5dc5
  133. The SQL Standard - ISO/IEC 9075:2023 (ANSI X3.135)
  134. https://www.postgresql.org/docs/current/features.html
  135. https://dev.mysql.com/doc/refman/8.4/en/compatibility.html
  136. https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/Oracle-and-Standard-SQL.html
  137. https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/SQL-Standards.html
  138. https://learn.microsoft.com/en-us/sql/t-sql/statements/set-ansi-defaults-transact-sql?view=sql-server-ver17
  139. PostgreSQL vs MySQL 2025: Complete Database Comparison
  140. SQL Server, PostgreSQL, MySQL: What's the Difference? - DataCamp
  141. 5.10. Schemas
  142. Documentation: 18: Chapter 35. The Information Schema - PostgreSQL
  143. https://dev.mysql.com/doc/refman/8.4/en/create-database.html
  144. Documentation: 18: CREATE SCHEMA - PostgreSQL
  145. https://docs.oracle.com/en/database/oracle/oracle-database/26/dbiad/db_schemas.html
  146. https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-database-schema?view=sql-server-ver17
User Avatar
No comments yet.