4 ms·

Having done this with MySQL before, there‘s basically three things you can do: 1) Do what you proposed, but this kinda defeats the whole purpose, as that side t

Having done this with MySQL before, there‘s basically three things you can do: 1) Do what you proposed, but this kinda defeats the whole purpose, as that side table can get very, very large (and if it‘s not fitting into working memory anymore, good luck keeping up a sane insert rate). 2) Just don‘t load that kind of data into an RDS subject to these scaling limitations at all (what I would do nowadays, but in a lot of ways, easier said than done) 3) (what we did back then): If you can, accept a window of uniqueness instead of real, true global uniqueness. If you can live with that, define a cronjob to build you a VIEW on the last TODAY-N partitions and check for duplicates using that in a SELECT CTE before inserting.

Shelnutt2 8y ago
Using Spider storage engine in Mariadb it is possible to do this by having vertical partitions using different partition by clauses [1]. Essentially you will create sub tables with the partition by clauses, then use a spider table to reference these subtables. Spider than intelligently will select the partition to use based on the query and where clause. The downside of this is it will require storage of the data multiple times on disk, since each subtable needs the full data set. Spider does handle inserting the data, so once schemas are build, only the single spider table is needed for inserts/selects/updates. In addition to this, in 10.4 it was mentioned spider is working to have internal joins. This means it would be possible to have 1 table defined with the full data set, then a second table that just has id, datefield partition by id. When you select WHERE id = X, it will read from the small table partitioned by id, find the dates, then join against the larger table to pull in remaining columns/data. This isn't mentioned in the presentation linked, but it was brought up in discussion at the mariadb developers unconference. [1] page 6: https://mariadb.org/wp-content/uploads/2018/03/Merging_patches_into_MariaDB_10.4.pdf https://mariadb.org/wp-content/uploads/2018/03/Merging_patch... (note this is about pages for 10.4 but the VP functionality is in 10.3)
tinus_hn 8y ago
Wouldn’t the solution be to automatically partition the index you use for ensuring uniqueness, along with the main table? I don’t see how it would be very difficult, as long as the number of partitions is stable.

Shelnutt2 8y ago

Using Spider storage engine in Mariadb it is possible to do this by having vertical partitions using different partition by clauses [1]. Essentially you will create sub tables with the partition by clauses, then use a spider table to reference these subtables. Spider than intelligently will select the partition to use based on the query and where clause. The downside of this is it will require storage of the data multiple times on disk, since each subtable needs the full data set. Spider does handle inserting the data, so once schemas are build, only the single spider table is needed for inserts/selects/updates. In addition to this, in 10.4 it was mentioned spider is working to have internal joins. This means it would be possible to have 1 table defined with the full data set, then a second table that just has id, datefield partition by id. When you select WHERE id = X, it will read from the small table partitioned by id, find the dates, then join against the larger table to pull in remaining columns/data. This isn't mentioned in the presentation linked, but it was brought up in discussion at the mariadb developers unconference. [1] page 6: https://mariadb.org/wp-content/uploads/2018/03/Merging_patches_into_MariaDB_10.4.pdf https://mariadb.org/wp-content/uploads/2018/03/Merging_patch... (note this is about pages for 10.4 but the VP functionality is in 10.3)

tinus_hn 8y ago

Wouldn’t the solution be to automatically partition the index you use for ensuring uniqueness, along with the main table? I don’t see how it would be very difficult, as long as the number of partitions is stable.