Database
DBMS
We have compared PostgreSQL to MySQL for our needs:
| Feature | PostgreSQL | MySQL |
|---|---|---|
| Array data type | supported | unsupported |
| support by webhosters | few | many |
| Clustering support | active-passive replication stable; active-active (pgcluster) working | stable, but size is limited to RAM size |
PostgreSQL supports searching the entries of a variable size array within a single column [1]. For k-objects with many variable size attributes this avoids lots of tables and (local) table joins.
We have chosen to use PostgreSQL.
Connection pooling
Statement pooling
- Using PostgreSQL SQL statements can be PREPAREd (and then EXECUTEd several times). This requires that the same session ist used throughout all EXECUTEs.
Tables
Configuration
Replication
The first version of the software shall have neither replication nor more than one node. In the long run we need replication however. With PostgreSQL there are currently three options, each with drawbacks:
- Slony-I is a "master to multiple slaves" replication system with cascading and failover. (Slony-II might become many-master.)
- pgpool is a connection pool manager. The recovery of a failed node unfortunately requires a downtime.
- pgcluster is a multi-master replication system.
What we need is a highly available database system with active synchronous replication, automatic failover and transparent recovery.
An alternative would be to use a MySQL cluster, but currently MySQL has no array data type and a size limit for databases: they must fit into RAM.
Continue with distributed storage
