<blockquote>
<p>Sure billion node partitions might reduce the time from a day to a few hours say, if there's enough I/O concurrency to get a more or less linear speedup, but it's still not something you'd want to do in production.</p>
</blockquote>
<p>Unless you're saying that the tables can never be changed, we have to be able to have locks and read-only periods for maintenance. This lessens them.</p>
<blockquote>
<p>Also, if the indexes are separate doesn't that mean that all lookups become more expensive because (apart from any index that includes the id) you now have to check every partition for matches on every query...</p>
</blockquote>
<p>Not really. My experience is that index size and performance remains about the same, unless there is a strong correlation between the partition criteria and the indexed value, in which case, it is faster. It's at most like a fraction of an extra node on the btree.</p>

<p style="font-size:small;-webkit-text-size-adjust:none;color:#666;">—<br />You are receiving this because you are subscribed to this thread.<br />Reply to this email directly, <a href="https://github.com/openstreetmap/openstreetmap-website/issues/2076#issuecomment-442843828">view it on GitHub</a>, or <a href="https://github.com/notifications/unsubscribe-auth/ABWnLXGS-1hDFHd7oL5BfY0gcXJBFNrVks5uz-jYgaJpZM4Y4AnY">mute the thread</a>.<img src="https://github.com/notifications/beacon/ABWnLV2ZwDVUEhmH-_EqQwRbWfw5CS6Mks5uz-jYgaJpZM4Y4AnY.gif" height="1" width="1" alt="" /></p>
<script type="application/json" data-scope="inboxmarkup">{"api_version":"1.0","publisher":{"api_key":"05dde50f1d1a384dd78767c55493e4bb","name":"GitHub"},"entity":{"external_key":"github/openstreetmap/openstreetmap-website","title":"openstreetmap/openstreetmap-website","subtitle":"GitHub repository","main_image_url":"https://assets-cdn.github.com/images/email/message_cards/header.png","avatar_image_url":"https://assets-cdn.github.com/images/email/message_cards/avatar.png","action":{"name":"Open in GitHub","url":"https://github.com/openstreetmap/openstreetmap-website"}},"updates":{"snippets":[{"icon":"PERSON","message":"@pnorman in #2076: \u003e Sure billion node partitions might reduce the time from a day to a few hours say, if there's enough I/O concurrency to get a more or less linear speedup, but it's still not something you'd want to do in production.\r\n\r\nUnless you're saying that the tables can never be changed, we have to be able to have locks and read-only periods for maintenance. This lessens them.\r\n\r\n\r\n\r\n\u003e Also, if the indexes are separate doesn't that mean that all lookups become more expensive because (apart from any index that includes the id) you now have to check every partition for matches on every query...\r\n\r\nNot really. My experience is that index size and performance remains about the same, unless there is a strong correlation between the partition criteria and the indexed value, in which case, it is faster. It's at most like a fraction of an extra node on the btree."}],"action":{"name":"View Issue","url":"https://github.com/openstreetmap/openstreetmap-website/issues/2076#issuecomment-442843828"}}}</script>
<script type="application/ld+json">[
{
"@context": "http://schema.org",
"@type": "EmailMessage",
"potentialAction": {
"@type": "ViewAction",
"target": "https://github.com/openstreetmap/openstreetmap-website/issues/2076#issuecomment-442843828",
"url": "https://github.com/openstreetmap/openstreetmap-website/issues/2076#issuecomment-442843828",
"name": "View Issue"
},
"description": "View this Issue on GitHub",
"publisher": {
"@type": "Organization",
"name": "GitHub",
"url": "https://github.com"
}
},
{
"@type": "MessageCard",
"@context": "http://schema.org/extensions",
"hideOriginalBody": "false",
"originator": "AF6C5A86-E920-430C-9C59-A73278B5EFEB",
"title": "Re: [openstreetmap/openstreetmap-website] Partition large tables (#2076)",
"sections": [
{
"text": "",
"activityTitle": "**Paul Norman**",
"activityImage": "https://assets-cdn.github.com/images/email/message_cards/avatar.png",
"activitySubtitle": "@pnorman",
"facts": [

]
}
],
"potentialAction": [
{
"name": "Add a comment",
"@type": "ActionCard",
"inputs": [
{
"isMultiLine": true,
"@type": "TextInput",
"id": "IssueComment",
"isRequired": false
}
],
"actions": [
{
"name": "Comment",
"@type": "HttpPOST",
"target": "https://api.github.com",
"body": "{\n\"commandName\": \"IssueComment\",\n\"repositoryFullName\": \"openstreetmap/openstreetmap-website\",\n\"issueId\": 2076,\n\"IssueComment\": \"{{IssueComment.value}}\"\n}"
}
]
},
{
"name": "Close issue",
"@type": "HttpPOST",
"target": "https://api.github.com",
"body": "{\n\"commandName\": \"IssueClose\",\n\"repositoryFullName\": \"openstreetmap/openstreetmap-website\",\n\"issueId\": 2076\n}"
},
{
"targets": [
{
"os": "default",
"uri": "https://github.com/openstreetmap/openstreetmap-website/issues/2076#issuecomment-442843828"
}
],
"@type": "OpenUri",
"name": "View on GitHub"
},
{
"name": "Unsubscribe",
"@type": "HttpPOST",
"target": "https://api.github.com",
"body": "{\n\"commandName\": \"MuteNotification\",\n\"threadId\": 417335768\n}"
}
],
"themeColor": "26292E"
}
]</script>