How to Get SQLite Database Size

SQLite is a lightweight and serverless SQL database. It is widely used in mobile devices and embedded systems due to its simplicity and efficiency. To understand how to manage SQLite databases efficiently, it is very important to know the Database size in SQLite. In this article, we will learn about different approaches to get the SQLite Database Size.

To retrieve the size of a SQLite database, you can use the PRAGMA statement along with the page_count and page_size properties.

Syntax:

Database Size = page_count * page_size

• page_count is the total number of pages in the database.
• page_size is the size of each page in bytes.

How to Get SQLite Database Size

SQLite database size management is crucial for optimizing performance and storage utilization. We’ll explore three approaches to address this challenge.

• Using PRAGMA Statements
• Using a Custom Query

Setting Up An Environment

Let’s create a simple table and insert some values into it to demonstrate how to obtain the database size.

Query:

CREATE TABLE Users (
    ID INTEGER PRIMARY KEY,
    Name TEXT,
    Age INTEGER
);
INSERT INTO Users (Name, Age) VALUES ('Alice', 30);
INSERT INTO Users (Name, Age) VALUES ('Bob', 25);
INSERT INTO Users (Name, Age) VALUES ('Charlie', 35);

Output:

create table

1. Using PRAGMA Statements

Using the following formula, we can calculate the database size in SQLite Database.

We will use two PRAGMA statements:

• PRAGMA page_count: This statement retrieves the total number of pages in the SQLite database.
• PRAGMA page_size: This statement retrieves the size of each page in bytes.

After executing the PRAGMA statements, we will use the values obtained to calculate the total size of the database. This is done by multiplying the total number of pages (page_count) by the size of each page (page_size).

Finally, the total size of the SQLite database in bytes, is calculated using the retrieved page count and page size.

Query:

PRAGMA page_count;
PRAGMA page_size;

Output:

Using PRAGMA Statements

We then calculate the database size by multiplying the page count by the page size:

Database Size = 4 * 4096 = 16384 bytes

Explanation:

• The PRAGMA page_count: Statement returns the total number of pages in the database, which in this case is 4.
• The PRAGMA page_size: Statement returns the size of each page in bytes, which is 4096.

2. Using a Custom Query

1. Calculating SQLite Database Size Using a Custom Query

We will start by querying the sqlite_master table. Now in subquery:

We calculate the estimated number of pages (pgs) required for each table by considering the length of its SQL definition (sql) and converting it to an approximate number of pages.

Now, join the information obtained from the previous subquery with the pragma_page_count() pragma, which provides the total number of pages (page_count) in the database. Then calculate the size of each table (pgcnt) by multiplying the estimated page count (pgs) with the total page count (pgsz). Finally, obtaining the size of each table, we use the SUM function to sum up all the table sizes

Query:

SELECT 
    SUM(pgsize) AS "Total Size (Bytes)"
FROM (
    SELECT 
        name,
        (pgcnt * 4096) AS pgsize
    FROM (
        SELECT 
            name,
            (pgs * pgsz) AS pgcnt
        FROM (
            SELECT 
                name,
                ((1 + ((length(sql) + 1) / 1024)) / 1024) AS pgs
            FROM 
                sqlite_master
        ),
        (SELECT 
            page_count AS pgsz
        FROM 
            pragma_page_count())
    )
    WHERE 
        type='table'
);

Output:

Using a Custom Query

Explanation: The SQL query calculates the total size of all tables in the SQLite database. It estimates the number of pages needed for each table based on its SQL definition length. By multiplying this estimated page count with the total page count obtained from pragma_page_count(), it computes the size of each table. Finally, the SUM function aggregates all table sizes to determine the total database size in bytes.

2. Using a Custom Query to Calculate SQLite Database Size

Using the following formula, we can calculate the database size.

We will begin by querying the sqlite_master table, which contains metadata about all the tables in the database then exclude system tables by filtering out those whose names start with “sqlite_“. We will join the information obtained from sqlite_master, and finally calculate the size of each table by multiplying the number of pages.

After obtaining the size of each table, we will use the SUM function to sum up all the table sizes, resulting in the total size of the database.

SELECT 
    SUM(total_bytes) AS "Total Size (Bytes)"
FROM (
    SELECT 
        name,
        (page_count * page_size) AS total_bytes
    FROM 
        pragma_page_size
    JOIN 
        pragma_page_count ON 1 = 1
    JOIN 
        sqlite_master ON type = 'table'
    WHERE 
        name NOT LIKE 'sqlite_%'
);

Output:

How to Get SQLite Database Size using a custom query

Explanation: This SQL query computes the total size, in bytes, of user-defined tables in the SQLite database. It joins information from pragma_page_size and pragma_page_count with sqlite_master to calculate the total bytes occupied by each table. The SUM function then aggregates these values to provide the total database size in bytes, excluding system tables.

Note:

• The PRAGMA statements in Example 1 provide information about the physical structure of the database, such as the total number of pages and the size of each page. These values are inherent to the database file itself and are not influenced by the data stored in the tables.
• The alternative approach in Example 2 and 3, estimates the size of the database by analyzing the metadata of the tables and making assumptions about their size based on their SQL definitions. This approach may provide a different result because it’s based on estimations rather than direct measurements of the database file.

Conclusion

In this article, we learnt how to get the size of a SQLite database using PRAGMA statements and simple calculations. By understanding the size of the database is very important for performance optimization and resource management. we can easily manage and monitor the database size in SQLite using above mentioned queries.