<p><a class="user-mention" data-hovercard-type="user" data-hovercard-url="/hovercards?user_id=1011860" data-octo-click="hovercard-link-click" data-octo-dimensions="link_type:self" href="https://github.com/simonpoole">@simonpoole</a> : I don't know if you read my updated comment in <a class="issue-link js-issue-link" data-error-text="Failed to load issue title" data-id="430010870" data-permission-text="Issue title is private" data-url="https://github.com/openstreetmap/openstreetmap-website/issues/2201" data-hovercard-type="issue" data-hovercard-url="/openstreetmap/openstreetmap-website/issues/2201/hovercard?comment_id=484513542&comment_type=issue_comment" href="https://github.com/openstreetmap/openstreetmap-website/issues/2201#issuecomment-484513542">#2201 (comment)</a>, if not, I'd recommend to take a look at it. There are indeed situations, in which an automated retry occurs without user intervention (no, I'm not talking about JOSM). So delegating the decision to the user may not really work out.</p>
<p>I don't want to bore folks here with lots of data, but in case you're still interested: I tried persisting a diffResult with 10k entries as JSONB. Saving the changeset took 2 seconds in total (that's on cgimap, not the Rails code that takes way longer). Out of those two seconds, saving the diffResult took 14 milliseconds. I used the SQL statement below to asses the size impact: it's at most 80k.</p>
<p>So the only remaining factors to asses the space requirements are now:</p>
<ul>
<li>how many changesets do we have in status "open" at any one time?</li>
<li>what is the maximum number of changed objects in that timeframe?</li>
</ul>
<p>I guess the majority of changesets will have less than 500-700 changes - all of them fit on one page, and don't require and TOASTing.</p>
<pre><code>SELECT *, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
FROM (
( SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
, c.reltuples AS row_estimate
, pg_total_relation_size(c.oid) AS total_bytes
, pg_indexes_size(c.oid) AS index_bytes
, pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r'
) a
) a where table_name = 'changeset_idempotency_cache';
</code></pre>
<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/2201#issuecomment-485139771">view it on GitHub</a>, or <a href="https://github.com/notifications/unsubscribe-auth/AAK2OLLWSWSIBHCGPAVFFULPRM63RANCNFSM4HEAKOPQ">mute the thread</a>.<img src="https://github.com/notifications/beacon/AAK2OLN647PXPIIAJLEFMBLPRM63RANCNFSM4HEAKOPQ.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://github.githubassets.com/images/email/message_cards/header.png","avatar_image_url":"https://github.githubassets.com/images/email/message_cards/avatar.png","action":{"name":"Open in GitHub","url":"https://github.com/openstreetmap/openstreetmap-website"}},"updates":{"snippets":[{"icon":"PERSON","message":"@mmd-osm in #2201: @simonpoole : I don't know if you read my updated comment in https://github.com/openstreetmap/openstreetmap-website/issues/2201#issuecomment-484513542, if not, I'd recommend to take a look at it. There are indeed situations, in which an automated retry occurs without user intervention (no, I'm not talking about JOSM). So delegating the decision to the user may not really work out.\r\n\r\nI don't want to bore folks here with lots of data, but in case you're still interested: I tried persisting a diffResult with 10k entries as JSONB. Saving the changeset took 2 seconds in total (that's on cgimap, not the Rails code that takes way longer). Out of those two seconds, saving the diffResult took 14 milliseconds. I used the SQL statement below to asses the size impact: it's at most 80k.\r\n\r\nSo the only remaining factors to asses the space requirements are now:\r\n\r\n* how many changesets do we have in status \"open\" at any one time?\r\n* what is the maximum number of changed objects in that timeframe? \r\n\r\nI guess the majority of changesets will have less than 500-700 changes - all of them fit on one page, and don't require and TOASTing.\r\n\r\n\r\n```\r\nSELECT *, pg_size_pretty(total_bytes) AS total\r\n , pg_size_pretty(index_bytes) AS INDEX\r\n , pg_size_pretty(toast_bytes) AS toast\r\n , pg_size_pretty(table_bytes) AS TABLE\r\n FROM (\r\n( SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (\r\n SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME\r\n , c.reltuples AS row_estimate\r\n , pg_total_relation_size(c.oid) AS total_bytes\r\n , pg_indexes_size(c.oid) AS index_bytes\r\n , pg_total_relation_size(reltoastrelid) AS toast_bytes\r\n FROM pg_class c\r\n LEFT JOIN pg_namespace n ON n.oid = c.relnamespace\r\n WHERE relkind = 'r'\r\n ) a\r\n) a where table_name = 'changeset_idempotency_cache';\r\n```\r\n\r\n"}],"action":{"name":"View Issue","url":"https://github.com/openstreetmap/openstreetmap-website/issues/2201#issuecomment-485139771"}}}</script>
<script type="application/ld+json">[
{
"@context": "http://schema.org",
"@type": "EmailMessage",
"potentialAction": {
"@type": "ViewAction",
"target": "https://github.com/openstreetmap/openstreetmap-website/issues/2201#issuecomment-485139771",
"url": "https://github.com/openstreetmap/openstreetmap-website/issues/2201#issuecomment-485139771",
"name": "View Issue"
},
"description": "View this Issue on GitHub",
"publisher": {
"@type": "Organization",
"name": "GitHub",
"url": "https://github.com"
}
}
]</script>