High-Performance PostgreSQL Retail Database
Budget: $1,500 – $3,000 CAD
The goal is to design a future-proof relational backbone for a multi-channel retail platform, built natively on PostgreSQL. I already run an operational system, and its live transactional records will have to be migrated into the new structure without downtime. Everything else—users, inventory, orders—will start fresh.
Core design expectations
The operational schema should be fully normalised to 3NF and optimised from day one for high concurrency. Think table partitioning (range and list), well-chosen GIN and B-Tree indexes, plus JSONB columns where semi-structured flexibility makes sense. Triggers, constraints and stored procedures must enforce business logic consistently, so the application tier can stay lightweight.
Analytics layer
Alongside the OLTP schema I need a separate reporting layer—either star or snowflake—that supports fast OLAP queries. It should draw cleanly from the operational tables and be easy to extend for future data-engineering workloads.
Migration considerations
Only transactional records from the existing system move over; user and inventory data can be regenerated or imported later. I’ll handle the extract, but I’ll need guidance on staging tables and repeatable load scripts so cut-over is painless.
Deliverables
• Professional ERD covering both operational and analytical schemas
• Well-commented DDL scripts ready to run in PostgreSQL 15+
• Written notes on migration workflow, partition strategy, index rationale and any non-trivial stored procedures
Acceptance
I’ll spin up a test instance and load a sample data set. Queries for order look-ups, inventory availability and a sample sales cube must all return within agreed thresholds before sign-off.
If any clarifications are needed, let me know early so we can lock the design quickly.
Core design expectations
The operational schema should be fully normalised to 3NF and optimised from day one for high concurrency. Think table partitioning (range and list), well-chosen GIN and B-Tree indexes, plus JSONB columns where semi-structured flexibility makes sense. Triggers, constraints and stored procedures must enforce business logic consistently, so the application tier can stay lightweight.
Analytics layer
Alongside the OLTP schema I need a separate reporting layer—either star or snowflake—that supports fast OLAP queries. It should draw cleanly from the operational tables and be easy to extend for future data-engineering workloads.
Migration considerations
Only transactional records from the existing system move over; user and inventory data can be regenerated or imported later. I’ll handle the extract, but I’ll need guidance on staging tables and repeatable load scripts so cut-over is painless.
Deliverables
• Professional ERD covering both operational and analytical schemas
• Well-commented DDL scripts ready to run in PostgreSQL 15+
• Written notes on migration workflow, partition strategy, index rationale and any non-trivial stored procedures
Acceptance
I’ll spin up a test instance and load a sample data set. Queries for order look-ups, inventory availability and a sample sales cube must all return within agreed thresholds before sign-off.
If any clarifications are needed, let me know early so we can lock the design quickly.