PostgreSQL Database Designer / Architect

We are looking for an experienced PostgreSQL Database Designer / Architect to design and develop a production-grade PostgreSQL database. The ideal candidate should have deep hands-on experience with PostgreSQL schema design, complex SQL, PL/pgSQL, stored procedures, functions, database security, Row-Level Security (RLS), indexing, and performance optimization. This is a specialized PostgreSQL role. We are looking for someone who understands database architecture and can make sound design decisions rather than simply writing SQL queries. Responsibilities ============ - Design scalable and maintainable - PostgreSQL database schemas based on business requirements. - Design tables, relationships, primary keys, foreign keys, constraints, and appropriate data types. - Apply appropriate normalization and database design principles. - Design efficient indexes and indexing strategies. - Write complex and optimized PostgreSQL SQL queries. - Develop PL/pgSQL functions and stored procedures. - Develop triggers and database-level business logic where appropriate. - Create views and materialized views where appropriate. - Design aggregation and reporting structures for large datasets. - Implement PostgreSQL security, including: -- Row-Level Security (RLS) -- RLS policies -- Roles and privileges -- GRANT / REVOKE -- Schema permissions -- Function permissions and security -- Secure database access patterns - Analyze and optimize query performance using EXPLAIN and EXPLAIN ANALYZE. - Design appropriate partitioning strategies when required. - Review existing database structures and recommend improvements. - Ensure database objects follow consistent naming and coding standards. - Create clean, maintainable, and well-documented SQL. - Document the database architecture, relationships, security model, and important database logic. Required PostgreSQL Expertise ========================== The candidate must have strong practical experience with: - PostgreSQL - Relational database architecture - Database normalization - Schema design - Complex SQL - PL/pgSQL - Stored procedures - PostgreSQL functions - Triggers - Views - Materialized views - Primary and foreign keys - Constraints - Indexes - Query optimization - EXPLAIN / EXPLAIN ANALYZE - Transactions and concurrency - Locking - Row-Level Security (RLS) - RLS policies - PostgreSQL roles and privileges - GRANT / REVOKE - PostgreSQL schemas - Database security - JSON / JSONB - Partitioning - Large-volume data management What We Are Looking For ==================== We are looking for a PostgreSQL specialist, not a general software developer. We value good database architecture, security, performance, and maintainability over simply producing working SQL. You should be able to take a business requirement and determine: - What tables are required - How tables should be related - Which constraints should be enforced - What indexes are required - What logic belongs in functions or stored procedures - Where triggers are appropriate - How to implement database-level security - How RLS should be designed - How permissions and roles should be structured - How the database will perform as data volume increases - How concurrent transactions should be handled - How reporting and aggregation should be designed efficiently Nice to Have ========== - Experience designing databases with millions of records. - Experience with large-scale PostgreSQL systems. - Experience designing multi-tenant PostgreSQL databases. - Experience with advanced RLS implementations. - Experience with PostgreSQL partitioning. - Experience designing audit and logging systems. - Experience optimizing complex database workloads. - Experience with PostgreSQL database migrations and version management. Deliverables ========== Depending on project requirements, deliverables may include: - PostgreSQL database architecture - Schema and relationship design - CREATE TABLE scripts - Constraints and indexes - Functions - Stored procedures - Triggers - Views - Materialized views - RLS policies - Roles and permissions - Query optimization - Partitioning strategies - Migration scripts - Database documentation How to Apply =========== Please provide: - A brief summary of your PostgreSQL experience. - Examples of PostgreSQL database architectures you have personally designed. - Your experience with PL/pgSQL functions and stored procedures. - Your experience implementing Row-Level Security (RLS). - Describe a complex PostgreSQL performance problem you solved. - Explain how you approach designing a PostgreSQL schema from business requirements. Please do not send a generic application. We are specifically looking for a PostgreSQL database specialist with strong hands-on experience in database architecture, security, views, stored procedures, functions, and performance optimization.