performance.ts•7.99 kB
/**
* 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();