Hello,
I'm not a DBA nor an architect. I learn from the wise and by making mistakes.
I'm in the process of developing a web application and need some advice from you all,
Requirements:
==========
* Application runs 24/7
* Application must support either PostgreSQL or another commercial enterprise database (the "suits" want this)
* Application supports 100's of customers on the server and few users (~25) per customer
* Application can support only one customer if needed
* Application will have audit data
* Some data is common to all customers (default values, help information)
* Hibernate will be used for database abstraction
* Performance more important than disk usage
* Max database size per customer will be ~18GB with 25000 rows and ~40 tables
Some Design thoughts (I could have gotten a few facts wrong):
============================================
Multiple schema in one database vs Multiple databases in one cluster:
This has probably been talked several times on the list.
I'm convinced about multiple databases in a cluster because,
(a) Cross database support
(b) Database for X customer can be removed or moved to another server easily
(c) Audit data can be separate schema on the same database
(d) Data security by default (Pg does not allow cross database access) (Ex: cust1 connects to db1 and so on. user1, user2 in db1)
(e) Simple design, flexible and easy to maintain (vacuum per database)
Why i dont want multiple schema per database
(i) Database owner can access all schema by default
(2) Multiple schema for same customer (say audit) becmes complex
(3) Can get complex with grants/revokes soon for fine-grained permissions
(4) If application gets compromised, possibility of more data exposure (dbuser connects to database with multiple schemas for customers. If dbuser is compromised, all schema in the datbase is compromised?)
(5) Schema may not be well supported on other databases?
Storing binary in database vs filesystem:
===============================
This again has been probably beated to death.
Application will store less frequently used binary files on filesystem and more frequently used binary data in the database, Binary data is ~300-400KB.
Compressing text data:
=================
I'd like the text data (a few KB's) that could be stored as TEXT compressed during inserts to save diskspace. This will impose CPU overhead vs IO which is acceptable. LZO compression could be appropriate (speed vs size). thoughts?
A DBA will review requirements and propose a design. In the mean time, I wanted to take a stab at this.
Steve