Performance Test for JSONB vs. Traditional Table Structure

This project is designed to compare the performance of inserting data into a PostgreSQL database using two different approaches: a JSONB column and a traditional table structure.

Project Structure

• scripts/jsonb_test.sql: Contains SQL commands to create a table with a JSONB column and insert sample data for performance testing.
• scripts/table_test.sql: Contains SQL commands to create a proper table structure with defined columns and insert sample data for performance testing.
• scripts/performance_test.js: A Node.js script that connects to the database, executes the SQL scripts, measures the time taken for inserts, and compares the performance of both approaches.
• tsconfig.json: TypeScript configuration file specifying compiler options and files to include.
• package.json: npm configuration file listing dependencies and scripts for the project.

Setup Instructions

1. Install Dependencies: Run the following command to install the necessary dependencies:

   npm install
   

2. Database Configuration: Ensure you have a PostgreSQL database set up and update the connection details in performance_test.js.

3. Run Performance Tests: Execute the performance test script using Node.js:

   node scripts/performance_test.js
   

4. Review Results: After running the tests, review the console output for the time taken for inserts using both JSONB and traditional table structures.

Conclusion

This project aims to provide insights into the performance implications of using JSONB columns versus traditional table structures in PostgreSQL, particularly focusing on insert operations.

Results npm run test

Run 2025-05-14T07:54:59.284Z

JSONB Insert Performance: 6.035s

Table Insert Performance: 18.844s

JSONB Insert Performance: 5.681s

Table Insert Performance: 18.947s