Database Administration and Operations Questions

Step-by-step plan

1. Prep & validation

• Validate source schema, nullability, types, encoding; sample-check row counts, checksums, and key uniqueness before load.
• Produce a CSV/BINARY extract with a row-count and MD5 per-file.

2. Strategy choice: COPY vs INSERT

• Use COPY (binary or CSV) — it's fastest and optimized for bulk import. Avoid INSERT (row-by-row) unless you must enforce triggers/complex FK checks.

3. Staging target: UNLOGGED or partitioned staging

• If replication durability isn’t required for the load and you can tolerate losing the load on crash, load into an UNLOGGED table to avoid WAL bloat:
  • CREATE UNLOGGED TABLE staging (...) LIKE target INCLUDING ALL;
• Safer alternative: load into regular tables on a replica or into new partitions to minimize impact on production.

4. Partitioning & parallelism

• If target is partitioned by a key, run multiple COPY jobs in parallel, each targeting a different partition/file. This scales IO and CPU and reduces lock contention.
• Example: 8 parallel workers each COPY into partition_N concurrently.

5. Disable/rebuild indexes & constraints

• Drop non-essential indexes on the target before final load (or build the target table without indexes).
• Load data, then create indexes after load. For minimal locking and to allow concurrent reads:
  • CREATE INDEX CONCURRENTLY idx_name ON target(col);
• For unique constraints, either validate uniqueness in staging or create a UNIQUE INDEX CONCURRENTLY.

6. maintenance_work_mem, work_mem

• Temporarily increase maintenance_work_mem to speed index builds:
  • SET maintenance_work_mem = '2GB';
• Increase max_parallel_workers_per_gather and max_parallel_workers if CPU allows.

7. COPY tuning and safe WAL settings

• Use COPY BINARY where supported to reduce CPU and parsing.
• Turn off synchronous_commit = off for the session to improve throughput (trade-off: acknowledged durability).
• You cannot safely set wal_level=minimal on a primary if replication is used; document constraints.
• If loading on standby/replica, consider using pg_basebackup approach or pg_restore with --no-acl --no-owner.

8. Finalize: analyze, swap, cleanup

• Run ANALYZE on new data.
• Create indexes CONCURRENTLY and re-enable constraints.
• If you loaded to an UNLOGGED staging table, perform validation then atomically swap:
  • BEGIN;
  • ALTER TABLE target RENAME TO target_old;
  • ALTER TABLE staging RENAME TO target;
  • COMMIT;
    (Be careful: RENAMES are fast but ensure dependent objects handled.)

9. Validation & verification

• Compare row counts, checksums, sample key lookups, and run queries against known aggregates.
• Check pg_stat_all_tables for tuples, and run VACUUM VERBOSE to inspect bloat.
• Monitor WAL generation during load (pg_stat_bgwriter, pg_wal_lsn_diff) and disk usage.

Risks & mitigations

• UNLOGGED table loses data on crash — mitigate by loading on replica or re-running extracts.
• CREATE INDEX CONCURRENTLY still generates WAL; expect heavy IO.
• Dropping indexes increases risk if load fails; keep backups and test plan on a staging environment.
• Synchronous_commit=off risks acknowledged but not flushed transactions; only use if acceptable.

Commands examples

-- fast load (session-level)
SET maintenance_work_mem = '2GB';
SET synchronous_commit = off;
COPY staging FROM '/data/part-000.csv' WITH (FORMAT csv, DELIMITER ',', HEADER true);
ANALYZE staging;
CREATE INDEX CONCURRENTLY idx_staging_col ON staging(col);

This approach balances minimal downtime and WAL bloat: use partitioned/parallel COPY into staging (prefer UNLOGGED if acceptable), postpone index builds until after bulk load, tune maintenance_work_mem and parallelism, and validate thoroughly before swapping into production.

How distributed transactions work

• SQL Server uses MSDTC for distributed transactions implementing two-phase commit (2PC). Coordinator (MSDTC) orchestrates prepare (voting) and commit/rollback across resource managers.

Configuration requirements

• Enable MSDTC on all participating machines, ensure network DTC access, allow RPC, and configure security (mutual authentication). When across AD domains, set up trust or use certificate-based authentication; configure firewall ports for RPC and DTC endpoints.

Performance & troubleshooting concerns

• 2PC adds latency (prepare phase) and holds locks longer, increasing contention and blocking. Network instability can cause in-doubt transactions requiring manual resolution. Logging overhead increases.
• Troubleshoot with MS DTC logs, use dtctester, and check system event logs and system resource bottlenecks.

Alternatives if distributed transactions infeasible

• Saga pattern / application-level compensating transactions: orchestrate multi-step workflow with idempotent operations and rollback compensations.
• Transactional outbox: write intent to local table within local transaction, then a reliable background process (or CDC) publishes to remote system—ensures eventual consistency.
• Two-phase commit alternatives at app layer: use idempotent upserts and id-based dedup keys; use idempotent messaging with Kafka/Service Bus and exactly-once patterns.
• Use distributed consensus/databases that support global transactions if strict atomicity across instances is required.

Recommendation for Data Engineer

• Prefer avoiding MSDTC across domains due to ops complexity. Use transactional outbox + reliable messaging or sagas for cross-instance updates; reserve MSDTC for legacy scenarios where strict atomicity is mandatory and environment supports stable DTC configuration.

1. Approach: use window function to rank salaries and pick the third. For duplicates decide whether to count ties.

2. Query (includes duplicates i.e. 3rd row by dense position):

SELECT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
  FROM employees
) t
WHERE dr = 3;

3. Behavior with duplicates:

• DENSE_RANK returns the 3rd distinct salary (skips ranks only when ties occur). If you want the 3rd highest row regardless of ties, use ROW_NUMBER and filter row_number = 3.

4. To return distinct salaries only (third distinct highest) use DENSE_RANK as above. To ensure exactly one value even if fewer than 3 distinct salaries exist, wrap with TOP 1 or RETURN NULL when no row.

Alternate: use OFFSET/FETCH on distinct salaries:

SELECT salary FROM (
  SELECT DISTINCT salary FROM employees ORDER BY salary DESC
) x
OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY;

Situation / Goal: Implement a sliding-window purge (>90 days) with minimal blocking using partitioning and partition SWITCH.

Steps (high-level):

1. Create a partition function on a date column (e.g., event_date) with daily/monthly boundaries and a partition scheme on filegroups.
2. Create a staging table with the same schema and indexes on the same filegroup as the partition to be switched in.
3. Load new daily data into the staging table (bulk insert/minimally logged where possible), validate.
4. Use ALTER TABLE ... SWITCH PARTITION to move staging partition into main table (very fast metadata operation).
5. To purge, create an empty table matching schema and indexes, then SWITCH old partition out of main table into that empty table and DROP TRUNCATE it.

Index/Constraint considerations:

• Target and source objects must have identical column order, nullability, clustered index, and nonclustered indexes (names can differ but definitions must match).
• Constraints: CHECK constraints and foreign keys must be compatible; disabled or use WITH CHECK as needed. Partition-aligned indexes (partitioned index) are best.
• For nonaligned indexes you may need to rebuild or create partition-aligned copies.

Operational notes:

• Pre-create partitions ahead of time to avoid schema changes during load.
• Run SWITCH outside high-concurrency windows; partition SWITCH is quick and typically avoids row locks.
• Test on staging environment with transaction log and permissions validated.

Clustered vs Nonclustered (summary for OLTP writes)

• Clustered index: defines physical row order. Only one per table. Primary key often clustered. Range scans are efficient. Inserts can cause page splits if new rows out-of-order.
• Nonclustered index: separate structure with key + row locator (clustered key or RID). Multiple per table. Good for point lookups; narrower indexes improve write performance.

Effects on storage & operations

• Physical storage: Clustered stores rows in sorted order; nonclustered stores keys plus pointer.
• Lookup performance: Covering nonclustered indexes speed reads; if nonclustered doesn't cover query, lookups require key lookup (extra I/O).
• Insert/update/delete cost: Every index must be maintained. Wide or many nonclustered indexes increase write cost and IO.

When to choose for OLTP with frequent writes

• Keep clustered index narrow, static, and sequential (e.g., identity or composite increasing key) to minimize page splits.
• Limit number and width of nonclustered indexes—index only the columns needed for frequent read patterns.
• Consider fillfactor and monitor fragmentation; for heavy concurrent inserts consider GUIDs only with sequential GUID strategy (NEWSEQUENTIALID) or use integer keys.

As a Data Engineer, balance read latency for analytical queries vs write throughput for ingestion; prefer minimal necessary indexes and optimize clustered key for insert pattern.