The following list is a list of questions that a Database Administrator should ask when provisioning a new database. The questions are also useful when examining an existing database as well.
Data Ingestion and Format
- Source: Where will the data be coming from (e.g., applications, manual entry, external feeds)?
- Format: In what formats will the data be ingested (e.g., CSV, JSON, raw)?
- Frequency and Volume: How often and in what volume will the data be ingested (real-time, batch, scheduled; number of records, size per record)?
Data Usage and Performance
- Primary Purpose: What is the main purpose of storing this data (e.g., analytics, reporting, transactions)?
- Expected Queries and Performance Requirements: What kind of queries will be most frequent, and are there specific latency or throughput requirements (e.g., aggregations, joins, filtering)?
- Read/Write Ratio: What is the expected ratio of read vs. write operations?
Reporting, Access, and Security
- Reporting Needs and User Access: What kind of reports will be generated, and who needs access (e.g., dashboards, ad-hoc analysis; specific users, applications)?
- Authorization Levels: What level of access should different users have (e.g., read-only, read-write, full control)?
- Security Measures: What security measures are needed to protect the data (e.g., encryption, access control)?
Maintenance, Backup, and Disaster Recovery
- Retention Policy: How long should the data be retained (e.g., archival, legal requirements)?
- Backup Strategy and Disaster Recovery: How will the database be backed up, and what is the plan for recovery (e.g., frequency, location)?
PostgreSQL Specifics and Scalability
- Version and Extensions: Which version of PostgreSQL is required, and are any specific extensions needed?
- Table Partitioning and Indexing Strategy: Is table partitioning considered, and what indexing strategy is needed for optimal performance?
- Monitoring and Logging: How will database performance and activity be monitored and logged?
Additional Considerations and Integrations
- Future Growth and Scalability Needs: How much is the data expected to grow, and does the database need to be scalable?
- Integration with Other Systems: Does the database need to integrate with other systems or applications?
- Cost Considerations: What are the budget constraints for setting up and maintaining the database?
- Compliance Requirements: Are there any specific compliance regulations that need to be met?