Re: [Firebird-devel] Partitioning (was: Tablespaces proposal)
Hi, Dne 12. 10. 21 v 15:21 Dimitry Sibiryakov napsal(a): BACKUP: Backup of separate partition is something nobody asked for so far. At least I cannot remember a ticket for gbak accepting filter condition inside of a table, only filter for whole tables and (since it is already implemented)... is anybody aware of it?.. Actually, it may make a lot of sense. Imagine that partitioning is used to separate historic records to partitions by years (for example). As historic data are not changed, the current year is (new records), so it may make sense to backup only the "active" partitions. In light of that, it may be a nice feature if partition would have an "updated" flag, and gbak an option to backup only changed partitions. RESTORE: How backup of single partition is supposed to be restored or otherwise used? For gbak it does not really matter, as it restores what is found in backup file (he can't recognize that it's not complete source database). So user would get "partial" database which might be useful. For example it could be a way to move archive data, i.e. backup historic partitions and restore them to historic database for some analytics. It may also open an opportunity to create in-place restore for gbak. It would restore to existing database, replacing partitions found there with content of partitions found in backup file (would require unique partition ID). Certainly, using selective partition backup/restore must be paired with proper database design and partitioning to make it work properly, but it's certainly a nice tool for handling long-living databases and historic data management. best regards Pavel Cisar Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Partitioning (was: Tablespaces proposal)
12.10.2021 16:21, Dimitry Sibiryakov wrote: INSERT: Nothing except time wasting to calculation partitioning key and creation of a new partition if necessary (ignorable). Slightly faster inserts into indices. UPDATE: DELETE: Faster index GC after them. BACKUP: Backup of separate partition is something nobody asked for so far. At least I cannot remember a ticket for gbak accepting filter condition inside of a table It makes zero sense for gbak, because it restores the database as a whole and cannot append/replace records in existing database. But it may be possible with partitions in different files and nbackup. Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Partitioning (was: Tablespaces proposal)
Dmitry Yemanov wrote 12.10.2021 15:02: Partition is a page set. Different page sets may be surely stored inside a single database file, but they may also be stored in different files. It makes a lot of sense to nbackup only some partition(s), or store them on slow drives, etc -- the same as for tablespaces. Ok, let's talk about it a little. Lets' define partitioning as "a split of table into pieces according to some condition". Call this condition "a partitioning key". What benefits can it have to database operations? SELECT: If query condition match partitioning key then natural scan can be reduced to subset of pages. This is effect similar to IOT. Local index can be used that have smaller depth and size. Parallel reading of different partitions... not going to happen. INSERT: Nothing except time wasting to calculation partitioning key and creation of a new partition if necessary (ignorable). UPDATE: Effectively become INSERT+DELETE if partitioning key is changed, otherwise nothing. DELETE: Can be reduced to TRUNCATE if the only query condition matches partitioning condition. BACKUP: Backup of separate partition is something nobody asked for so far. At least I cannot remember a ticket for gbak accepting filter condition inside of a table, only filter for whole tables and (since it is already implemented)... is anybody aware of it?.. RESTORE: How backup of single partition is supposed to be restored or otherwise used? PS: The case of slow drives was already commented. It is an internal feature of modern hybrid storage systems and Firebird doesn't need another "hand-made RAID". -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
