#sql

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 a dbt (data build tool) project for analytics engineering. Structure: 1. Staging models (raw data cleaning). 2. Intermediate models (business logic transformations). 3. Mart models (final aggregated tables). 4. Tests for data quality (unique, not_null, relationships). 5. Documentation with schema.yml and descriptions. Implement incremental models for large tables and use Jinja macros for reusable logic. Include CI/CD integration.

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?

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.