/**
* Performance Comparison: JSON vs JSONB
*
* This script demonstrates the performance differences between JSON and JSONB columns:
* - Insert performance
* - Query performance
* - Index impact on query performance
*/
import dbService from './services/db-service.js';
/**
* Function to measure execution time
* @param {Function} fn - The function to measure
* @returns {Promise<number>} Execution time in milliseconds
*/
async function measureExecutionTime(fn: () => Promise<void>): Promise<number> {
const start = process.hrtime.bigint();
await fn();
const end = process.hrtime.bigint();
return Number(end - start) / 1_000_000; // Convert to milliseconds
}
/**
* Function to run and display a performance test
* @param {string} name - The name/description of the test
* @param {Function} testFn - The test function to execute
*/
async function runPerformanceTest(
name: string,
testFn: () => Promise<void>
): Promise<number> {
console.log(`\n--- ${name} ---`);
const executionTime = await measureExecutionTime(testFn);
console.log(`Execution time: ${executionTime.toFixed(2)}ms`);
return executionTime;
}
async function main(): Promise<void> {
try {
// Initialize the database service
await dbService.initialize();
// First, let's check if the table already exists and drop it to start fresh
await dbService.query('DROP TABLE IF EXISTS performance_test;');
// Create a table with JSON and JSONB columns
await dbService.query(`
CREATE TABLE performance_test (
id SERIAL PRIMARY KEY,
data_json JSON,
data_jsonb JSONB
);
`);
console.log(
'Created table with data_json (JSON) and data_jsonb (JSONB) columns'
);
// Generate sample data
const sampleData = {
user: {
name: 'John Doe',
email: 'john@example.com',
address: {
street: '123 Main St',
city: 'Boston',
country: 'USA'
},
roles: ['admin', 'user', 'editor'],
preferences: {
theme: 'dark',
language: 'en',
notifications: true
},
stats: {
logins: 42,
last_active: '2024-03-15'
}
},
metadata: {
version: '1.0.0',
created_at: '2024-01-01',
updated_at: '2024-03-15'
}
};
// Convert to JSON string for insertion
const jsonString = JSON.stringify(sampleData);
// Test 1: Insert Performance
console.log('\n----- INSERT PERFORMANCE -----');
// Insert into JSON column
const jsonInsertTime = await runPerformanceTest(
'Insert into JSON column',
async () => {
for (let i = 0; i < 1000; i++) {
await dbService.query(
'INSERT INTO performance_test (data_json) VALUES ($1)',
[jsonString]
);
}
}
);
// Insert into JSONB column
const jsonbInsertTime = await runPerformanceTest(
'Insert into JSONB column',
async () => {
for (let i = 0; i < 1000; i++) {
await dbService.query(
'INSERT INTO performance_test (data_jsonb) VALUES ($1)',
[jsonString]
);
}
}
);
console.log(
`\nInsert Performance Summary:
JSON: ${jsonInsertTime.toFixed(2)}ms
JSONB: ${jsonbInsertTime.toFixed(2)}ms
Difference: ${(((jsonbInsertTime - jsonInsertTime) / jsonInsertTime) * 100).toFixed(2)}%`
);
// Test 2: Query Performance
console.log('\n----- QUERY PERFORMANCE -----');
// Query JSON column
const jsonQueryTime = await runPerformanceTest(
'Query JSON column (no index)',
async () => {
for (let i = 0; i < 100; i++) {
await dbService.query(`
SELECT * FROM performance_test
WHERE data_json->'user'->>'name' = 'John Doe'
`);
}
}
);
// Query JSONB column
const jsonbQueryTime = await runPerformanceTest(
'Query JSONB column (no index)',
async () => {
for (let i = 0; i < 100; i++) {
await dbService.query(`
SELECT * FROM performance_test
WHERE data_jsonb->'user'->>'name' = 'John Doe'
`);
}
}
);
console.log(
`\nQuery Performance Summary (no index):
JSON: ${jsonQueryTime.toFixed(2)}ms
JSONB: ${jsonbQueryTime.toFixed(2)}ms
Difference: ${(((jsonbQueryTime - jsonQueryTime) / jsonQueryTime) * 100).toFixed(2)}%`
);
// Test 3: Index Impact
console.log('\n----- INDEX IMPACT -----');
// Create GIN index on JSONB column
await runPerformanceTest('Create GIN index on JSONB column', async () => {
await dbService.query(`
CREATE INDEX idx_performance_test_jsonb
ON performance_test USING GIN (data_jsonb);
`);
});
// Query JSONB column with index
const jsonbQueryWithIndexTime = await runPerformanceTest(
'Query JSONB column (with GIN index)',
async () => {
for (let i = 0; i < 100; i++) {
await dbService.query(`
SELECT * FROM performance_test
WHERE data_jsonb->'user'->>'name' = 'John Doe'
`);
}
}
);
console.log(
`\nQuery Performance Summary (with GIN index):
JSON (no index): ${jsonQueryTime.toFixed(2)}ms
JSONB (no index): ${jsonbQueryTime.toFixed(2)}ms
JSONB (with index): ${jsonbQueryWithIndexTime.toFixed(2)}ms
Improvement: ${(((jsonbQueryTime - jsonbQueryWithIndexTime) / jsonbQueryTime) * 100).toFixed(2)}%`
);
// Test 4: JSONB-specific operators performance
console.log('\n----- JSONB-SPECIFIC OPERATORS PERFORMANCE -----');
// Query using containment operator
const jsonbContainmentTime = await runPerformanceTest(
'Query using JSONB containment operator @>',
async () => {
for (let i = 0; i < 100; i++) {
await dbService.query(`
SELECT * FROM performance_test
WHERE data_jsonb->'user'->'preferences' @> '{"theme": "dark"}'
`);
}
}
);
// Query using exists operator
const jsonbExistsTime = await runPerformanceTest(
'Query using JSONB exists operator ?',
async () => {
for (let i = 0; i < 100; i++) {
await dbService.query(`
SELECT * FROM performance_test
WHERE data_jsonb->'metadata' ? 'version'
`);
}
}
);
console.log(
`\nJSONB-specific Operators Performance Summary:
Containment (@>): ${jsonbContainmentTime.toFixed(2)}ms
Exists (?): ${jsonbExistsTime.toFixed(2)}ms`
);
console.log(`
\n----- Performance Comparison Summary -----
1. Insert Performance:
✓ JSON is generally faster for inserts
✓ JSONB requires additional processing (validation and binary conversion)
2. Query Performance (no index):
✓ JSONB is generally faster for queries
✓ JSONB's binary format is more efficient to process
3. Index Impact:
✓ JSONB supports GIN indexing
✓ GIN indexes significantly improve query performance
✓ JSON does not support indexing
4. JSONB-specific Operators:
✓ Containment (@>) and exists (?) operators are efficient
✓ These operators are not available for JSON
Recommendation:
- Use JSON when:
* Insert performance is critical
* You need to preserve exact format and key order
* You don't need complex queries or indexing
- Use JSONB when:
* Query performance is critical
* You need to use JSONB-specific operators
* You need to create indexes on JSON data
* Storage space is a concern
* You don't need to preserve key order
`);
// Close the database connection
await dbService.close();
} catch (error) {
console.error('Error in performance demo:', error);
// Make sure to close the connection in case of error
await dbService.close();
}
}
// Run the main function
main();
