#database

Analyze and optimize PostgreSQL query performance. Steps: 1. Identify slow queries with pg_stat_statements. 2. Analyze Execution plans (EXPLAIN ANALYZE). 3. Create covering indexes for frequent queries. 4. Optimize JOIN operations. 5. Vacuum and analyze strategy. 6. Partition large tables. 7. Tune configuration parameters (work_mem, shared_buffers). 8. Connection pooling setup (PgBouncer). Include index maintenance plan.

Design efficient database schemas. Normalization forms: 1. 1NF (atomic values, no repeating groups). 2. 2NF (no partial dependencies). 3. 3NF (no transitive dependencies). Balance normalization with performance (denormalize for read-heavy). Design principles: Use surrogate keys (auto-increment IDs). Foreign keys for relationships. Indexes on frequently queried columns. Avoid NULL when possible. Use appropriate data types. Naming conventions (plural table names, singular columns). Document relationships (ERD diagrams). Consider ACID properties. Use constraints (UNIQUE, NOT NULL, CHECK). Plan for scalability (partitioning, sharding).

Generate a SQL schema for a simple e-commerce website. It should include tables for Products, Customers, Orders, and Order_Items. Define the columns for each table, specify data types, and set up primary and foreign key relationships.

I have a slow-running SQL query. Here is the query: [paste query here]. And here is the table schema: [paste schema here]. Analyze the query and suggest ways to optimize it. Consider adding indexes, rewriting the query, or denormalizing the data.

Debate the pros and cons of using an Object-Relational Mapper (ORM) like SQLAlchemy or TypeORM versus writing raw SQL queries. Discuss aspects like developer productivity, performance, security, and database abstraction. In what scenarios would you strongly prefer one over the other?

Design a comprehensive database backup strategy. Components: 1. Automated daily full backups. 2. Incremental backups every 6 hours. 3. Point-in-time recovery capability. 4. Offsite backup storage (S3 Glacier). 5. Encryption at rest and in transit. 6. Backup verification and integrity checks. 7. Documented restore procedures with RTO/RPO. Test restore process monthly. Use tools like pg_dump, mysqldump, or cloud-native solutions. Include retention policies (7 daily, 4 weekly, 12 monthly).

Optimize database performance with indexes. Strategies: 1. Index foreign keys. 2. Composite indexes for multi-column queries. 3. Covering indexes to avoid table lookups. 4. Partial indexes for filtered queries. 5. Monitor query plans (EXPLAIN). 6. Avoid over-indexing (write performance). 7. Index selectivity matters. 8. Regular index maintenance. Use for WHERE, JOIN, ORDER BY. Not for small tables or high-write scenarios.

Optimize a slow-running SQL query on a 50M+ row table. Techniques to apply: 1. Add appropriate indexes on WHERE and JOIN columns. 2. Replace subqueries with CTEs (Common Table Expressions). 3. Use EXPLAIN ANALYZE to identify bottlenecks. 4. Partition large tables by date for faster scans. 5. Rewrite correlated subqueries as window functions. Provide before/after execution times and explain each optimization decision.