When developing web applications or optimizing database indexes, testing against an empty schema or three trivial developer rows masks critical production defects. Pagination queries that respond in 2 milliseconds on 10 rows can take 8 seconds once the table reaches 500,000 records.

Similarly, mocking realistic data - with varied email domains, international phone numbers, valid ISO timestamps, and consistent foreign key references - is essential for testing frontend edge cases and stress-testing API endpoints.

In this guide, we explore how to generate high-volume synthetic datasets in MySQL and PostgreSQL using pure SQL, and how to create rich mock data visually using native macOS tooling.

Method 1: Generating Test Data in PostgreSQL via `generate_series()`

PostgreSQL contains one of the most powerful built-in set-generating functions in the relational world: generate_series().

Inserting 100,000 Realistic Mock Users in Seconds

INSERT INTO users (
    username, 
    email, 
    status, 
    signup_ip, 
    created_at
)
SELECT 
    'user_' || id AS username,
    'user_' || id || '@' || (ARRAY['gmail.com', 'outlook.com', 'icloud.com', 'proton.me'])[floor(random() * 4 + 1)] AS email,
    (ARRAY['active', 'pending', 'suspended'])[floor(random() * 3 + 1)] AS status,
    ('192.168.' || floor(random() * 255 + 1) || '.' || floor(random() * 255 + 1))::inet AS signup_ip,
    NOW() - (random() * (365 * 24 * 3600) * '1 second'::interval) AS created_at
FROM generate_series(1, 100000) AS id;

Key Techniques Used Here:

ARRAY[...] indexing: Pulls randomly distributed categorical statuses and email domains.
Random timestamp intervals: Generates realistic historical registrations spread evenly across the past 365 days.
Type casting: Native PostgreSQL casts like ::inet ensure synthesized IP addresses conform to binary network types.

Method 2: Generating Test Data in MySQL via Recursive CTEs

MySQL lacks generate_series(), but since MySQL 8.0, you can achieve equivalent high-speed record generation using a recursive Common Table Expression (CTE):

-- Increase recursion depth limit if generating > 1,000 rows
SET SESSION cte_max_recursion_depth = 50000;

INSERT INTO customers (
    full_name,
    email,
    balance_cents,
    is_verified,
    created_at
)
WITH RECURSIVE row_generator AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM row_generator WHERE n < 50000
)
SELECT 
    CONCAT('Customer_', n) AS full_name,
    CONCAT('test_', n, '@example.com') AS email,
    FLOOR(1000 + (RAND() * 99000)) AS balance_cents,
    IF(RAND() > 0.3, 1, 0) AS is_verified,
    NOW() - INTERVAL FLOOR(RAND() * 180) DAY AS created_at
FROM row_generator;

[!TIP] In MySQL, always wrap bulk inserts in an explicit START TRANSACTION; ... COMMIT; block. If autocommit is enabled, the InnoDB storage engine flushes the redo log to disk for each batch of inserts, dramatically slowing down throughput.

Method 3: Visual Mock Data Generation with SQLOZ (macOS Native)

Writing complex SQL expressions for every individual column and maintaining relational integrity across foreign key tables quickly becomes tedious.

SQLOZ provides an integrated, native macOS Mock Data Generator built directly into its database workspace.

SQLOZ Table Management and Mock Data Generator

How to Generate Test Data in SQLOZ:

Right-Click Table: In the SQLOZ sidebar or Table Data Grid, right-click any table and choose Generate Mock Data….
Column Heuristics & Faker Detection: SQLOZ automatically inspects your column names and types. A column named first_name defaults to authentic human names, country_code populates valid ISO Alpha-2 codes, and avatar_url generates CDN image links.
Foreign Key Awareness: If your table includes a foreign key constraint referencing companies.id, SQLOZ automatically pulls valid foreign key IDs from the parent table, preventing constraint violation failures.
Volume Slider: Specify anywhere from 10 to 500,000 rows. The generator utilizes Metal-accelerated Swift concurrency to compile and stream parameterized bulk inserts directly over the socket without blocking the macOS UI.

Preserving Production Anonymity: Masking vs. Generation

While synthetic dummy data is ideal for greenfield features, staging environments often require replicating the scale and quirks of production databases. However, syncing real customer credentials, PII (Personally Identifiable Information), and financial records to local machines violates privacy regulations like GDPR and HIPAA.

For this workflow, SQLOZ includes OzMask (Data Anonymization Suite), which sanitizes real production schemas during replication by replacing email addresses, names, and credit card numbers with cryptographically realistic pseudonyms while preserving foreign key consistency.

Summary

Accurate testing demands realistic data volumes. Whether you write recursive CTEs in MySQL, use generate_series() in PostgreSQL, or leverage the visual generator in SQLOZ, never deploy code tested only on five clean records.

For related workflows on verifying and comparing test environments, explore our guide on Migrating Dev SQLite to Staging MySQL in Seconds and learn how to Inspect and Filter Large Database Tables Without Writing SQL.

Method 1: Generating Test Data in PostgreSQL via generate_series()