-- Database Optimization Script for Large-Scale Seeding
-- Run these commands before seeding large amounts of data
-- MySQL/MariaDB Optimizations
-- ===========================
-- Disable foreign key checks during bulk operations
SET
    FOREIGN_KEY_CHECKS = 0;

-- Disable unique checks during bulk operations
SET
    UNIQUE_CHECKS = 0;

-- Disable autocommit for better performance
SET
    AUTOCOMMIT = 0;

-- Increase bulk insert buffer size
SET
    SORT_BUFFER_SIZE = 256 * 1024 * 1024;

-- 256MB
-- Increase key buffer size
SET
    KEY_BUFFER_SIZE = 512 * 1024 * 1024;

-- 512MB
-- Increase read buffer size
SET
    READ_BUFFER_SIZE = 2 * 1024 * 1024;

-- 2MB
-- Increase read rnd buffer size
SET
    READ_RND_BUFFER_SIZE = 8 * 1024 * 1024;

-- 8MB
-- Increase innodb buffer pool size (adjust based on available RAM)
-- SET GLOBAL innodb_buffer_pool_size = 1G;
-- Disable binary logging during bulk operations (if safe to do so)
-- SET sql_log_bin = 0;
-- PostgreSQL Optimizations
-- ========================
-- Disable autovacuum during bulk operations
-- ALTER TABLE table_name SET (autovacuum_enabled = false);
-- Increase work_mem for sorting
-- SET work_mem = '256MB';
-- Increase maintenance_work_mem
-- SET maintenance_work_mem = '1GB';
-- Increase checkpoint_segments
-- SET checkpoint_segments = 32;
-- Disable synchronous_commit
-- SET synchronous_commit = off;
-- SQLite Optimizations
-- ====================
-- Enable WAL mode for better concurrency
-- PRAGMA journal_mode = WAL;
-- Increase cache size
-- PRAGMA cache_size = 10000;
-- Disable synchronous writes (faster but less safe)
-- PRAGMA synchronous = OFF;
-- Increase page size
-- PRAGMA page_size = 4096;
-- Disable foreign key constraints
-- PRAGMA foreign_keys = OFF;
-- Re-enable optimizations after bulk operations
-- =============================================
-- MySQL/MariaDB
-- SET FOREIGN_KEY_CHECKS = 1;
-- SET UNIQUE_CHECKS = 1;
-- SET AUTOCOMMIT = 1;
-- SET sql_log_bin = 1;
-- PostgreSQL
-- ALTER TABLE table_name SET (autovacuum_enabled = true);
-- SET synchronous_commit = on;
-- SQLite
-- PRAGMA foreign_keys = ON;
-- PRAGMA synchronous = FULL;
