Design suggestion for BLOBs, GUID columns

[Ertan Küçükoglu]

Hello,

I will be using FirebirdSQL 3 embedded 32Bit. There will be a main database and 2-3 additional databases. Normally there will be a single user, but possibly it will switch over to multi-user in the future. I expect to see PCs with total 4-8GB RAM later being more less.

All databases will have UTF8 as default character set.

My application will save about 100-300KiB XML files (E-Invoices) in one of the tables as BLOB binary (for UTF8 and such reasons). There will be additional columns in that same table like company name, company id, GUID, date etc. I do not expect daily record growth to be more than 300, but you never know. That table with the BLOB column will only grow. There will be very rare row deletions from it. It will be mainly queried for reports, heavily queried for GUID values existence. There will be BLOB data readings, too. But, will not be as much as other queries

When considering performance and only performance;

1) Is it advisable to use a separate foreign key table for BLOB data only, or do I just continue and save everything in a single table?

2) I would like to have suggestions about GUIDs and indexes on varchar GUID columns. What I read here and there is that it is better not to use indexes on such columns (other database system users like SQL Server or PostgreSQL suggest that if my memory serves me well). My case, I must use them for this project. Also Firebird might have a different handling of indexes which is immune to such column values. I do not know.

I appreciate any suggestions, real life experiences.

Thanks & Regards,

Ertan Küçükoğlu

P.S. I cannot directly jump to FirebirdSQL 4.0 as my version of the database connection library does not support it.

[Dimitry Sibiryakov]

Ertan Küçükoglu wrote 03.11.2021 19:14:
> My application will save about 100-300KiB XML files (E-Invoices) in one of the
> tables as BLOB binary (for UTF8 and such reasons). There will be additional
> columns in that same table like company name, company id, GUID, date etc. I do
> not expect daily record growth to be more than 300, but you never know. That
> table with the BLOB column will only grow. There will be very rare row deletions
> from it. It will be mainly queried for reports, heavily queried for GUID values
> existence. There will be BLOB data readings, too. But, will not be as much as
> other queries
>
> When considering performance and only performance;
> 1) Is it advisable to use a separate foreign key table for BLOB data only, or do
> I just continue and save everything in a single table?

With BLOB size of 100-300KiB it doesn't matter unless one BLOB can be
referenced by more than one record from main table.

> 2) I would like to have suggestions about GUIDs and indexes on varchar GUID
> columns.

1) Use binary GUID representation instead of text (VARCHAR(16) CHARACTER SET OCTETS)
2) Generate GUIDs using UuidCreateSequential
3) Reverse order of bytes before inserting it into table (or create additional
field with data filled with REVERSE() function) for indexing

In this case GUIDs will be fine for indexing.

--
WBR, SD.

[Ertan Küçükoglu]

Hello,

Thanks for the suggestions. For my part of generated UUIDs, I can use the suggested method. However, there will be incoming invoices with guids already placed in them that I must also save in my table. 

Incoming and outgoing invoices will be on separate tables but still.

My observations, these are mostly v4 GUID values.

I guess I will have to live with that. At least, I will be using 16 bytes and not varchar(38) as I was initially planning.

Thanks.

Dimitry Sibiryakov <s...@ibphoenix.com>, 3 Kas 2021 Çar, 21:47 tarihinde şunu yazdı:

--
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 on the web, visit https://groups.google.com/d/msgid/firebird-support/76f5d3b4-b21d-ac3b-5b20-ccd70d3f8fae%40ibphoenix.com.

[Dimitry Sibiryakov]

Ertan Küçükoglu wrote 03.11.2021 20:22:
> However, there will be incoming invoices with guids already placed in them
> that I must also save in my table.

As I said you can use an additional field filled with REVERSE() function in
"before insert" trigger.

--
WBR, SD.

[Mark Rotteveel]

On 2021-11-03 19:14, Ertan Küçükoglu wrote:
> My application will save about 100-300KiB XML files (E-Invoices) in
> one of the tables as BLOB binary (for UTF8 and such reasons).

You could just use BLOB SUB_TYPE TEXT CHARACTER SET UTF8 if your only
concern is UTF8. However, if you receive XML with different character
sets, then using BINARY is a good idea.

[..]

> When considering performance and only performance;
>
> 1) Is it advisable to use a separate foreign key table for BLOB data
> only, or do I just continue and save everything in a single table?

Larger blobs are always stored separately from the row data, and are
retrieved separately. The row itself only contains an 8 byte pointer to
the blob. So using a separate table is not necessary.

> 2) I would like to have suggestions about GUIDs and indexes on varchar
> GUID columns. What I read here and there is that it is better not to
> use indexes on such columns (other database system users like SQL
> Server or PostgreSQL suggest that if my memory serves me well). My
> case, I must use them for this project. Also Firebird might have a
> different handling of indexes which is immune to such column values. I
> do not know.

Given your size requirements, I don't think you need to worry about this
at all. But I would recommend using CHAR(16) CHARACTER SET OCTETS
instead of CHAR(36) CHARACTER SET ASCII. As far as I know, the primary
concern with using UUIDs as primary keys is for index-organized (aka
clustered) tables. For heap-organized tables like Firebird uses, that is
not a big concern, though there might be some churn for index updates,
but I would guess (but have never tested this!) the impact of this is
negligible (especially in the low volume of inserts you mention). The
suggestion of Dimitry to use a 'serial' UUID and reverse it for the
primary key adds a lot of complications that are not warranted for your
situation.

Mark

[Mark Rotteveel]

On 2021-11-03 19:14, Ertan Küçükoglu wrote:

> P.S. I cannot directly jump to FirebirdSQL 4.0 as my version of the
> database connection library does not support it.

The only problem I can think of are the new data types introduced in
Firebird 4.0, and that can be addressed by using setting
`DataTypeCompatibility` in firebird.conf or database.conf (if you want
to control it per database), or the `SET BIND` statement, or the
isc_dpb_set_bind property.

Mark

[Valdir Stiebe Junior]

For item 1. 

We like to have a separate table for large BLOB data to allow us to skip this table on a backup created for debugging/development purposes.

https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/bk02ch11s03.html

[Mark Rotteveel]

On 19-11-2021 13:03, Valdir Stiebe Junior wrote:
> For item 1.
> We like to have a separate table for large BLOB data to allow us to skip
> this table on a backup created for debugging/development purposes.
> https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/bk02ch11s03.html

Those are outdated release notes. The latest version is
https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rlsnotes30.html#rnfb30-util-gbak

I'll see if I can add redirects for the old files.

Mark
--
Mark Rotteveel