Why did the removal of a UNIQUE constraint reduce the size of my database to less than half?-CodePudding

Here's an example that reproduces the results. The database goes from 144.4 MB to 60.6 MB.

My UNIQUE column typically contains several paragraphs of text (e.g. 500 words).

import sqlite3
import os

db_file = 'databases/test.db'
conn = sqlite3.connect(db_file) # file path
cur = conn.cursor()

# CREATE table
cur.execute('''CREATE TABLE orig_table (
               key TEXT PRIMARY KEY,
               unq TEXT UNIQUE
           )''')
records = []
for i, record in enumerate(range(10000)):
    records.append(('primary'   str(i), 500 * (' unique'   str(i))))
cur.executemany("INSERT INTO orig_table VALUES (?,?)", records)
conn.commit()

print('Original size:', str( round(os.path.getsize(db_file)/1000000,1))   ' MB')

# Remove UNIQUE constraint
cur.execute('''CREATE TABLE new_table (
               key TEXT PRIMARY KEY,
               unq TEXT
           )''')
cur.execute('''INSERT INTO new_table (key, unq)
               SELECT key, unq FROM orig_table
            ''')
cur.execute('DROP TABLE orig_table')
conn.commit()
conn.execute('VACUUM')
conn.close()

print('Final size:', str( round(os.path.getsize(db_file)/1000000,1))   ' MB')

CodePudding user response：

The UNIQUE constraint uses an index of the column to speed up the uniqueness verification for each new value. The size difference corresponds to the size of the index.