590
edits
Places:FsyncApproach: Difference between revisions
Jump to navigation
Jump to search
→Partitioning Approach: Updated pros and cons
Comrade693 (talk | contribs) (→Partitioning Approach: Updated cons) |
Comrade693 (talk | contribs) (→Partitioning Approach: Updated pros and cons) |
||
| Line 11: | Line 11: | ||
Pros: | Pros: | ||
* Temp tables are in-memory, so there are no writes when writing to the temp table. | |||
Cons: | Cons: | ||
* Adds significant complexity to queries. However, this can be mitigated greatly by using VIEWs that join the temporary table with the permanent one. VIEWs cannot be written to though, so we'll either have to figure out which table to update (which can cause writes if it is the permanent table) or copy the data into the temp table. For the latter solution, when we sync the temporary table we would have to use INSERT OR REPLACE to update the permanent table. I'm not sure how this will effect UNION statements however (ordering may matter here - temp table first? UNION drops duplicated data as far as I know). | * Adds significant complexity to queries. However, this can be mitigated greatly by using VIEWs that join the temporary table with the permanent one. VIEWs cannot be written to though, so we'll either have to figure out which table to update (which can cause writes if it is the permanent table) or copy the data into the temp table. For the latter solution, when we sync the temporary table we would have to use INSERT OR REPLACE to update the permanent table. I'm not sure how this will effect UNION statements however (ordering may matter here - temp table first? UNION drops duplicated data as far as I know). | ||
* Have to track primary key max index ourself for inserts, since the temp table won't be able to AUTOINCREMENT like the main table. We can mitigate this by having sqlite start at a certain value. See [http://www.mail-archive.com/sqlite-users@sqlite.org/msg35131.html this e-mail thread] and [http://sqlite.org/autoinc.html this page] for more details. | * Have to track primary key max index ourself for inserts, since the temp table won't be able to AUTOINCREMENT like the main table. We can mitigate this by having sqlite start at a certain value. See [http://www.mail-archive.com/sqlite-users@sqlite.org/msg35131.html this e-mail thread] and [http://sqlite.org/autoinc.html this page] for more details. | ||
* How do we update existing data? [http://www.mail-archive.com/sqlite-users@sqlite.org/msg35184.html discussion list question] | |||
Tasks: | Tasks: | ||