Page size significantly affects the resulting database size, even when -USE_ALL_SPACE is used.

[Tommi Prami]

Yellow,

If 32K Page size is used, the resulting file is about 150MB, and with 8K page size about 100MB.

All space is not used, or is there some other thing that affects this. Verified that setting  DatabaseGrowthIncrement = 1M did not affect the Restored DB size.

-Tee- 

[Dimitry Sibiryakov]

Tommi Prami wrote 25.08.2025 13:38:
> If 32K Page size is used, the resulting file is about 150MB, and with 8K page
> size about 100MB

Didn't you mean "GB" instead of "MB"?

--
WBR, SD.

[Tommi Prami]

Nope, MB, mega bytes.

That db is put "master DB" with structure and some initial data.

Not empty but starting point 😂

-tee-

--
Support the ongoing development of Firebird! Consider donating to the Firebird Foundation and help ensure its future. Every contribution makes a difference. Learn more and donate here:
https://www.firebirdsql.org/donate
---
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion, visit https://groups.google.com/d/msgid/firebird-support/c7c4e407-8710-43dd-abe1-35503d95d856%40ibphoenix.com.

[Dimitry Sibiryakov]

Tommi Prami wrote 25.08.2025 14:09:
> Nope, MB, mega bytes.

This database is too small for a serious investigation. But if you are
curious and have enough time - use gstat + DBInfo from IBSurgeon to find out the
differences in page allocations.

--
WBR, SD.

[Elmar Haneke]

If 32K Page size is used, the resulting file is about 150MB, and with 8K page size about 100MB.

Especially for small BLOB lager page results in larger DB since for each a number of pages is allocated. 

E.g., an 6K BLOB dies occupy 8K vs. 32K in database.

Database definition does contain several BLOB of small size as BLR code for Views.

Is the effect any problem for you?

Page size should be chosen for production data, not for storing structure information.

[Tommi Prami]

Blobs could have some effect.

Have to check if I can analyse it bit for tomorrow.

As I said earlier, this is just a read-only db, that has the initial data. That is updated to production database if needed. One time thing for each new master DB version. Couple times in each year, so less that it takes from the disk, less it is problem for users Hard disk space...

-tee-

--

Support the ongoing development of Firebird! Consider donating to the Firebird Foundation and help ensure its future. Every contribution makes a difference. Learn more and donate here:
https://www.firebirdsql.org/donate
---
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion, visit https://groups.google.com/d/msgid/firebird-support/a40d6fa6-d23a-4ea8-a8d1-061e331c5866%40haneke.de.

[Mark Rotteveel]

If you have (a lot of) blobs, than 32K page size will likely waste more
space, especially if a lot of blobs are just a few kilobytes. But even
for very large blobs, the last blob page will likely waste more space.

In fact, that applies to most types of pages: a large page size can be
more efficient in terms of IO (more data on a single page), but it does
mean that pages that aren't entirely full have more "unused" space. Say
that each table, each index, and each blob has at least one page that is
just 50% full, that means that for page size 8K, you'll just have 4KiB
of unused space, but for page size 32K, that is 16KiB.

Mark
--
Mark Rotteveel

[Ertan Küçükoglu]

Why not save the compressed copy of the "idle" initial data database?

Uncompress when needed and once done, delete the uncompressed copy.

I believe you'll save a lot more space this way.

Tommi Prami <tommi...@gmail.com>, 25 Ağu 2025 Pzt, 18:57 tarihinde şunu yazdı:

To view this discussion, visit https://groups.google.com/d/msgid/firebird-support/CABwd-arMBcSQzmKerzOgRX2e1g5o5Ep55q4_pzJDYuajqb%3DsVw%40mail.gmail.com.

[liviuslivius]

If it is readonly database, simply do backup and restore db with use all space option.

Regards,

Karol Bieniaszewski

-------- Oryginalna wiadomość --------

Od: Ertan Küçükoglu <ertan.k...@gmail.com>

Data: 25.08.2025 22:20 (GMT+02:00)

Do: firebird...@googlegroups.com

Temat: Re: [SPAM] [firebird-support] Page size significantly affects the resulting database size, even when -USE_ALL_SPACE is used.

To view this discussion, visit https://groups.google.com/d/msgid/firebird-support/CAH2i4yeBEqNO0J4VNw2d_ZJc3RxpMyHcNOei-vAu4YCV5B%3D5GQ%40mail.gmail.com.

[Tommi Prami]

That is after backup and restore with the use all space option.

We had to abandon this, using the smaller page size and using all space to optimize size.

But... 

Is there any good way to test different page sizes? How it affects performance. All I can think of is to make copies with different page sizes, and find some set of queries and measure the time it takes.

But how to select those queries at first place? 

How does Page sizes affect Update and/or Insert?

-Tee-

To view this discussion, visit https://groups.google.com/d/msgid/firebird-support/4c9x0K3Y3kz21wH%40smtp.poczta.onet.pl.

[Alexey Kovyazin]

Hello, 

Each table has mandatory pair of system pages - pointer and index root. 

If you have many tables, 2x32k per table will be bigger than 2x8k per table. 

However, this is more like a theoretical discussion, since 32k page is designed for databases bigger than 500-1000Gb. 

If you are working with databases under 20-50gb of size, 8k page is the best option, then goes 16k, and then could be 32k.

To see if database structure has real problems (like too deep indices), login to cc.ib-aid.com, click on Database analysis (it works in trial mode for all accounts), upload gstat -r output, and check for alerts. 

Regards, 

Alexey Kovyazin

 

Tommi Prami <tommi...@gmail.com>:

To view this discussion, visit https://groups.google.com/d/msgid/firebird-support/CABwd-ar%2BFzqms8oV6dY5Cp403j0O9%2ByP98r5A8O2wdXfvkyZqA%40mail.gmail.com.

[Tommi Prami]

OK

Thanks for the information.

If you are working with databases under 20-50gb of size, 8k page is the best option, then goes 16k, and then could be 32k.

Maybe we should scale the Page Size down. Customers don't have HUGE databases. Strill would like to do some profiling/analysis on this... Without measuring this it would be more than. Maybe collect all Queries that takes more than second, with parameters, and then do small test app that runs all those queries and then measure.,..

 

To see if database structure has real problems (like too deep indices), login to cc.ib-aid.com, click on Database analysis (it works in trial mode for all accounts), upload gstat -r output, and check for alerts. 

Have to check that out. 

Thank you very much mr Kovyazin!

-Tee-

[Ertan Küçükoglu]

Alexey Kovyazin <alexey....@gmail.com>, 26 Ağu 2025 Sal, 08:44 tarihinde şunu yazdı:

However, this is more like a theoretical discussion, since 32k page is designed for databases bigger than 500-1000Gb. 

If you are working with databases under 20-50gb of size, 8k page is the best option, then goes 16k, and then could be 32k.

Hello,

Why is 8k page size best for a 20GB or less size database?

Is there such a table somewhere to help us users choose more optimal page size?

I have several databases less than 500MB which uses 32k page size.

I also don't know if this is any "bad" other than having large database size on disk.

Server RAM is enough to hold complete databases in memory (for now at least).

Thanks.

[Pavel Cisar]

Dne 26. 08. 25 v 8:18 Ertan Küçükoglu napsal(a):

>
> Why is 8k page size best for a 20GB or less size database?
> Is there such a table somewhere to help us users choose more optimal
> page size?

Actually, it's not best. It's just a good starting point, that's all.

The page size plays key role in many limits that could be reached even
in small databases, like max index key size (especially with UTF8 and
compound indices) or max. record size that reasonably fits to single
page (at least three records should fit on single page, but the more the
better). You may also reach index depth 4 and more with 8K pages even on
small databases.

So, if your database will never exceed 20GB, you should definitely start
with 8K pages and verify that it's ok for your schema and data volumes.
There is a good chance that 8K is optimal size for you.

best regards
Pavel Císař
IBPhoenix

[liviuslivius]

Near to always i use 16k page size. I can create then indexes on quite large varchar column and also index deep is small then. For db in size >50GB i can consider 32kb, bui this depend only on the deep of indexes for me, sometimes for intensive insert, update scenario i can consider to stay on 16gb for longer time and only if crucial indexes got 4 or more index deep i switch to 32gb.

Regards,

Karol Bieniaszewski

-------- Oryginalna wiadomość --------

Od: Ertan Küçükoglu <ertan.k...@gmail.com>

Data: 26.08.2025 09:18 (GMT+02:00)

Do: firebird...@googlegroups.com

Temat: Re: [SPAM] [SPAM] [firebird-support] Page size significantly affects the resulting database size, even when -USE_ALL_SPACE is used.

Alexey Kovyazin <alexey....@gmail.com>, 26 Ağu 2025 Sal, 08:44 tarihinde şunu yazdı:

However, this is more like a theoretical discussion, since 32k page is designed for databases bigger than 500-1000Gb. 

If you are working with databases under 20-50gb of size, 8k page is the best option, then goes 16k, and then could be 32k.

Hello,

Why is 8k page size best for a 20GB or less size database?

Is there such a table somewhere to help us users choose more optimal page size?

I have several databases less than 500MB which uses 32k page size.

I also don't know if this is any "bad" other than having large database size on disk.

Server RAM is enough to hold complete databases in memory (for now at least).

Thanks.

--

Support the ongoing development of Firebird! Consider donating to the Firebird Foundation and help ensure its future. Every contribution makes a difference. Learn more and donate here:
https://www.firebirdsql.org/donate
---
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion, visit https://groups.google.com/d/msgid/firebird-support/CAH2i4ye2GTMK%2BcZin9q7SuHjzG-qKjdVP0FEPDioWLB4it1Q0g%40mail.gmail.com.

[Ann Harrison]

A Firebird database has many different page types - header page, data pages, index pages, index root pages, pointer pages, etc. Each page holds only data of that type.  An empty database  has only system information, but lots of different types of pages holding that information.  For example, RDB$RELATIONS has a pointer page, a data page, an index root page, an index page for each index declared for that table, and probably more.  Each system table has the same number of specialized pages.   For an empty database, all the information of each type takes up less than 8KB. So with a 32KB page there are lots of mostly empty pages.

USE ALL SPACE tells Firebird not to reserve space on data pages for changes to the records on the page. It does nothing to allow pages of different types to be consolidated. 

Cheers,

Ann

On Aug 25, 2025, at 8:09 AM, Tommi Prami <tommi...@gmail.com> wrote:



To view this discussion, visit https://groups.google.com/d/msgid/firebird-support/CABwd-aoJBH_4dTQf%2BNoLzpNDTY-fBG%2B4SLQDtFbJN8PdGZKTAQ%40mail.gmail.com.