Database size is ridiculous large (1.1Gb per 1k text rows 230k chars each) and unusable as in-memory DB

[vitonsky]

The problem

I've create PGlite instance and have create few tables with text field + tsvector + GIN index and then I've insert 1k rows with text downloaded from https://en.wikipedia.org/wiki/American_Civil_War?action=raw

Command SELECT pg_size_pretty(sum(pg_database_size(datname))) AS sum_all_db_sizes says my database size is just a 328Mb that is fine and well expected for this large texts

However, when i dump database with no compression, archive takes 1.1 GiB (1,130,903,040)

Here is archive content

It looks wal files adds this overhead. Also it is noticeable the wal files size is 1mb.

I've tried to do vacuum full and CHECKPOINT; SELECT pg_switch_wal(); it does not fix a problem.

The consequences

Such disk usage makes PGLite is unusable on real world applications with in-memory storage.

This problem looks even more fatal because of issue #810 that block us of implement own storage to sync only those files that has been changed. Currently we have to re-write 1.1GB on FS every time DB run any operation. Even with DB run in worker and debouncing for sync calls, it makes application freeze on a seconds.

Do you have any ideas why DB takes so much disk space and how to fix it?

[tdrz]

Thank you for this @vitonsky

Could you please paste the code that you are using? A full example would help us get up to speed much quicker.

[vitonsky]

@tdrz here is minimal example that reproduce a problem (i run it via Vitest)

/* eslint-disable spellcheck/spell-checker */

import { PGlite } from '@electric-sql/pglite';

describe('Naked DB', () => {
	const db = new PGlite();
	const sampleRows = 1000;

	test('Init database scheme', { timeout: 500_000 }, async () => {
		await db.exec(
			`
			CREATE TABLE "notes" (
				"id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
				"created_at" TIMESTAMP WITH TIME ZONE DEFAULT now(),
				"updated_at" TIMESTAMP WITH TIME ZONE DEFAULT now(),
				"title" TEXT NOT NULL,
				"text" TEXT NOT NULL
			);
			
			ALTER TABLE notes
				ADD COLUMN text_tsv tsvector
					GENERATED ALWAYS AS (to_tsvector('simple', title || ' ' || text)) STORED;
			CREATE INDEX notes_text_tsv_idx ON notes USING GIN (text_tsv);
			`,
		);
	});

	test('Insert texts', { timeout: 500_000 }, async () => {
		const demoText = await fetch(
			'https://en.wikipedia.org/wiki/American_Civil_War?action=raw',
		).then((r) => r.text());

		expect(demoText.length).toBeGreaterThan(220_000);
		expect(demoText.length).toBeLessThan(250_000);

		for (let i = 0; i < sampleRows; i++) {
			await db.query(`INSERT INTO notes(title, text) VALUES ($1,$2) RETURNING id`, [
				'Demo text',
				demoText,
			]);
		}

		await expect(db.query(`SELECT COUNT(*) as count FROM notes`)).resolves.toEqual(
			expect.objectContaining({
				rows: [expect.objectContaining({ count: sampleRows })],
			}),
		);

		await db
			.dumpDataDir('none')
			.then((data) => data.arrayBuffer())
			.then((buffer) => {
				console.log('DB size', buffer.byteLength);
			});

		await db.exec('vacuum full;');
		await db.exec('CHECKPOINT; SELECT pg_switch_wal();');
		await db
			.dumpDataDir('none')
			.then((data) => data.arrayBuffer())
			.then((buffer) => {
				console.log('DB size after cleanup', buffer.byteLength);
			});
	});

	test('Update rows', { timeout: 500_000 }, async () => {
		const demoText = await fetch(
			'https://en.wikipedia.org/wiki/American_Civil_War?action=raw',
		).then((r) => r.text());

		const { rows } = await db.query(`SELECT id FROM notes`);
		for (let i = 0; i < rows.length; i++) {
			const { id } = rows[i] as any;
			await db.query('UPDATE notes SET title=$1, text=$2 WHERE id=$3', [
				'Updated title',
				demoText + i,
				id,
			]);
		}

		await db
			.dumpDataDir('none')
			.then((data) => data.arrayBuffer())
			.then((buffer) => {
				console.log('DB size', buffer.byteLength);
			});

		await db.exec('vacuum full;');
		await db.exec('CHECKPOINT; SELECT pg_switch_wal();');
		await db
			.dumpDataDir('none')
			.then((data) => data.arrayBuffer())
			.then((buffer) => {
				console.log('DB size after cleanup', buffer.byteLength);
			});
	});
});

My output is

stdout | src/core/storage/database/pglite/PGLiteProblems.test.ts > Naked DB > Insert texts
DB size 862289408

stdout | src/core/storage/database/pglite/PGLiteProblems.test.ts > Naked DB > Insert texts
DB size after cleanup 1144729088

stdout | src/core/storage/database/pglite/PGLiteProblems.test.ts > Naked DB > Update rows
DB size 1639057920

stdout | src/core/storage/database/pglite/PGLiteProblems.test.ts > Naked DB > Update rows
DB size after cleanup 1376445952

 ✓ src/core/storage/database/pglite/PGLiteProblems.test.ts (3 tests) 109330ms
   ✓ Naked DB > Init database scheme  641ms
   ✓ Naked DB > Insert texts  51703ms
   ✓ Naked DB > Update rows  56983ms

 Test Files  1 passed (1)
      Tests  3 passed (3)
   Start at  22:48:19
   Duration  110.09s (transform 124ms, setup 32ms, collect 111ms, tests 109.33s, environment 0ms, prepare 40ms)

 PASS  Waiting for file changes...
       press h to show help, press q to quit

This is a simplified data model of real world app. Here we creates a generated field with tsvector + index for fast full text search. So eventually we have text field + tsvector of that text (for search purposes) + index.

Original text would take about ~214 MB disk space ( 220_000 * 1000 / 1000 / 1024).
DB with generated tsvector and index takes 1376MB.

Due to problem #810 we currently always re-write whole buffer on disk + keep whole DB in RAM, so our app is additionally limited by available RAM size on user side. It means for example, that we cannot run our app on mobile in case user have 1k notes with 220k chars each (that's real use case, because user may sync data between devices).

[tdrz]

@vitonsky thank you for the full repro!

I understand that your use case is different, but would you be willing to try with pg_dump instead?

[vitonsky]

@tdrz i just tried, it throw error

Error: pg_dump failed with exit code 239. 
Error message: could not find a "pg_dump" to execute
# 2381: connectDBStart
# 2420: connectDBComplete Begin /workspace/postgresql-REL_17_5_WASM/src/interfaces/libpq/fe-connect.c

 ❯ Mt node_modules/@electric-sql/pglite-tools/src/pg_dump.ts:194:11
 ❯ src/core/storage/database/pglite/PGLiteProblems.test.ts:58:16
     56| 
     57| 
     58|   const dump = await pgDump({ pg: db });
       |                ^
     59|   console.log("Dump size", dump.size);

I think pg_dump cannot to resolve a problem and would even add overhead, since as i know it dumps only data, not indexes, so DB startup time will be even longer because DB will have to create indexes for all rows with large texts.

I think the fundamental problem here is generated field text_tsv takes a lot of disk space that is fine on server, because we have hundreds terabytes disk space, but it is critical for client side DB because

1. 1Gb disk space may be critical for user, especially on mobile or small windows laptop
2. In case user will try to dump 1Gb DB every N seconds, it anyways will take a huge time in sum
3. For InMemory DB with no custom storage user also have to keep in RAM a bytes with size equal to DB size (killer factor for mobile devices)

Technically, we could get rid field text_tsv, in that case we have

stdout | src/core/storage/database/pglite/PGLiteProblems.test.ts > Naked DB > Insert texts
DB size 277963776

stdout | src/core/storage/database/pglite/PGLiteProblems.test.ts > Naked DB > Insert texts
DB size after cleanup 412580864

stdout | src/core/storage/database/pglite/PGLiteProblems.test.ts > Naked DB > Update rows
DB size 537345536

stdout | src/core/storage/database/pglite/PGLiteProblems.test.ts > Naked DB > Update rows
DB size after cleanup 540839936

 ✓ src/core/storage/database/pglite/PGLiteProblems.test.ts (3 tests) 19374ms
   ✓ Naked DB > Init database scheme  701ms
   ✓ Naked DB > Insert texts  8490ms
   ✓ Naked DB > Update rows  10182ms

 Test Files  1 passed (1)
      Tests  3 passed (3)
   Start at  09:57:35
   Duration  19.39s

As you see DB size is 3x less for text insertion, but then after update rows and vacuum only x2 less.

This is original problem described in current issue. The problem is our actual data takes only 277Mb, but DB ops makes it 540Mb. Even with no TSV or indexes. I make accent here, to keep in mind the original problem, out of bounds of specific use case.

Now go back to use case, to figure out resolution strategy and priority.

Yes tsvector looks huge, but we cannot refuse it, because we need in full text search on large texts. Actually this is important factor why we choose pglite among other databases. Unlike SQLite a PGLite have rich types system and extensions that let us implement full text search and search in vectors that is important for similarity search among embeddings.

So this is important feature of PGLite. If we would drop tsvectors and will use anything like https://www.npmjs.com/package/flexsearch for search in large texts

• we would have to integrate few databases to work together, with all problems like iterative search between few DBs and similar headache
• we could use any other database like SQLite or even no SQL

This is why we have to use tsvector. If you know another solutions in pg for fast full text search on large texts + fuzzy search, please share.

Summary

It looks in our project we doomed to use tsvector field and some disk overhead is just a consequence of that requirement, so that's not a problem of PGLite.

Even with no tsvector DB takes additional disk space for every row update as shown above, that is critical. This is what we fight against in current issue.

From my perspective it looks we could address 2/3 problems of our use case if implement custom storage (issue #810) because:
2. We could not use "dump" feature at all, and continuously save changes in specific small files. So instead of save 1.1gb every N seconds, we would save few kb every N seconds. That's huge benefit. Also, in custom FS we could compress specific files, to minimize disk usage. We could also contribute such solutions back to PGLite as open source packages.
3. If we keep files in custom FS, we have not to keep in RAM 1.1gb (or even more).

It seems the custom FS (#810) have more priority from perspective of management large databases.

On other hand current issue (#819) is critical from perspective of management any database with frequent updates.

I'm not sure in reasons why from 200mb DB makes 500mb even after vacuum and explicit chekpointing.