<p>From <a class="issue-link js-issue-link" data-error-text="Failed to load issue title" data-id="17027409" data-permission-text="Issue title is private" data-url="https://github.com/openstreetmap/openstreetmap-website/issues/375" data-hovercard-type="issue" data-hovercard-url="/openstreetmap/openstreetmap-website/issues/375/hovercard?comment_id=441660239&comment_type=issue_comment" href="https://github.com/openstreetmap/openstreetmap-website/issues/375#issuecomment-441660239">#375 (comment)</a></p>
<blockquote>
<p>We can't just say that we'll never change anything in our tables ever again! If this does involve large table changes, then we can work out what the zero-downtime migration path would be, and do that. We could practise multi-stage migrations on the smaller tables where there is less impact.</p>
</blockquote>
<p>Whenever I describe our large API tables to a PostgreSQL, the number one comment is to partition them. This allows maintenance to be done without needing to sequentially scan billions of rows.</p>
<p>Are there tools for doing this easily in ruby? Should this be a site-specific thing to osm.org? Most users won't have multi-TB tables and it's possible to partition in a way that the rails port doesn't see it.</p>
<p>The benefits of partitioning are</p>
<ul>
<li>maintenance is possible</li>
<li>indexes are smaller</li>
<li>cache locality is generally improved</li>
<li>table bloat is lessened</li>
<li>index bloat is lessened</li>
</ul>

<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">view it on GitHub</a>, or <a href="https://github.com/notifications/unsubscribe-auth/ABWnLZMfQ5SAnLwXxx2bBhTriQ2BnuiOks5uzr4cgaJpZM4Y4AnY">mute the thread</a>.<img src="https://github.com/notifications/beacon/ABWnLdczKv2Xuqi5jUeKPAQtfkGQp9Wzks5uzr4cgaJpZM4Y4AnY.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":"DESCRIPTION","message":"Partition large tables (#2076)"}],"action":{"name":"View Issue","url":"https://github.com/openstreetmap/openstreetmap-website/issues/2076"}}}</script>
<script type="application/ld+json">[
{
"@context": "http://schema.org",
"@type": "EmailMessage",
"potentialAction": {
"@type": "ViewAction",
"target": "https://github.com/openstreetmap/openstreetmap-website/issues/2076",
"url": "https://github.com/openstreetmap/openstreetmap-website/issues/2076",
"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": "Partition large tables (#2076)",
"sections": [
{
"text": "",
"activityTitle": "**Paul Norman**",
"activityImage": "https://assets-cdn.github.com/images/email/message_cards/avatar.png",
"activitySubtitle": "@pnorman",
"facts": [
{
"name": "Repository: ",
"value": "openstreetmap/openstreetmap-website"
},
{
"name": "Issue #: ",
"value": 2076
}
]
}
],
"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"
}
],
"@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>