https://dev.arvados.org/https://dev.arvados.org/favicon.ico?15576888422018-01-17T19:09:34ZArvadosArvados - Idea #12960: [API] Migrate remaining "properties" fields to jsonbhttps://dev.arvados.org/issues/12960?journal_id=594422018-01-17T19:09:34ZPeter Amstutzpeter.amstutz@curii.com
<ul><li><strong>Status</strong> changed from <i>New</i> to <i>In Progress</i></li></ul> Arvados - Idea #12960: [API] Migrate remaining "properties" fields to jsonbhttps://dev.arvados.org/issues/12960?journal_id=594432018-01-17T19:09:51ZPeter Amstutzpeter.amstutz@curii.com
<ul><li><strong>Status</strong> changed from <i>In Progress</i> to <i>New</i></li></ul> Arvados - Idea #12960: [API] Migrate remaining "properties" fields to jsonbhttps://dev.arvados.org/issues/12960?journal_id=594442018-01-17T19:10:40ZPeter Amstutzpeter.amstutz@curii.com
<ul><li><strong>Subject</strong> changed from <i>Migrate remaining "properties" fields to jsonb</i> to <i>[API] Migrate remaining "properties" fields to jsonb</i></li></ul> Arvados - Idea #12960: [API] Migrate remaining "properties" fields to jsonbhttps://dev.arvados.org/issues/12960?journal_id=594462018-01-17T19:15:42ZPeter Amstutzpeter.amstutz@curii.com
<ul><li><strong>Description</strong> updated (<a title="View differences" href="/journals/59446/diff?detail_id=56789">diff</a>)</li></ul> Arvados - Idea #12960: [API] Migrate remaining "properties" fields to jsonbhttps://dev.arvados.org/issues/12960?journal_id=619152018-04-18T14:03:56ZTom Morristfmorris@veritasgenetics.com
<ul><li><strong>Tracker</strong> changed from <i>Bug</i> to <i>Idea</i></li></ul> Arvados - Idea #12960: [API] Migrate remaining "properties" fields to jsonbhttps://dev.arvados.org/issues/12960?journal_id=619162018-04-18T14:07:07ZPeter Amstutzpeter.amstutz@curii.com
<ul><li><strong>Description</strong> updated (<a title="View differences" href="/journals/61916/diff?detail_id=59020">diff</a>)</li></ul> Arvados - Idea #12960: [API] Migrate remaining "properties" fields to jsonbhttps://dev.arvados.org/issues/12960?journal_id=619332018-04-18T15:32:55ZPeter Amstutzpeter.amstutz@curii.com
<ul><li><strong>Description</strong> updated (<a title="View differences" href="/journals/61933/diff?detail_id=59038">diff</a>)</li></ul> Arvados - Idea #12960: [API] Migrate remaining "properties" fields to jsonbhttps://dev.arvados.org/issues/12960?journal_id=627282018-05-16T14:41:04ZPeter Amstutzpeter.amstutz@curii.com
<ul></ul><p>Question about migrating other serialized fields such as mounts, etc → we don't plan on querying on it, the subproperty query API exposed by Arvados doesn't support deep queries. Are there other benefits to migrating serialized columns to jsonb, for example, is jsonb more compact or faster to retrieve in the database?</p> Arvados - Idea #12960: [API] Migrate remaining "properties" fields to jsonbhttps://dev.arvados.org/issues/12960?journal_id=630952018-06-04T16:01:34ZLucas Di Pentimalucas.dipentima@curii.com
<ul></ul>JSON pros over JSONB
<ul>
<li>Faster writes (no conversion process involved)</li>
<li>Input text is saved as is, so things like key ordering and indentation are preserved</li>
</ul>
JSONB pros over JSON
<ul>
<li>Duplicated attributes get deduplicated & whitespaces removed (potential storage space savings)</li>
<li>Significantly faster processing</li>
<li>Indexable / searchable</li>
</ul>
<p>From what I could read on docs and forum comments, the general rule for deciding whether to use JSON over JSONB is that if data will be processed on the application itself, JSON would be fine, otherwise it is probably more convenient to use JSONB.</p>
<p>I’ve found mixed references about which type is more compact/faster to retrieve, no clear answer to that.</p> Arvados - Idea #12960: [API] Migrate remaining "properties" fields to jsonbhttps://dev.arvados.org/issues/12960?journal_id=630972018-06-04T16:06:48ZLucas Di Pentimalucas.dipentima@curii.com
<ul><li><strong>Related to</strong> <i><a class="issue tracker-2 status-3 priority-4 priority-default closed parent" href="/issues/13562">Feature #13562</a>: [API] Add JSONB properties field on groups</i> added</li></ul> Arvados - Idea #12960: [API] Migrate remaining "properties" fields to jsonbhttps://dev.arvados.org/issues/12960?journal_id=630982018-06-04T16:07:33ZTom Morristfmorris@veritasgenetics.com
<ul><li><strong>Target version</strong> changed from <i>To Be Groomed</i> to <i>Arvados Future Sprints</i></li><li><strong>Story points</strong> set to <i>1.0</i></li></ul> Arvados - Idea #12960: [API] Migrate remaining "properties" fields to jsonbhttps://dev.arvados.org/issues/12960?journal_id=631352018-06-05T13:58:25ZPeter Amstutzpeter.amstutz@curii.com
<ul></ul><p>I should mention that indexing properties on container_requests is necessary to fully implement the Workflow Execution Service (WES) spec, which includes filtering on key-value tags.</p> Arvados - Idea #12960: [API] Migrate remaining "properties" fields to jsonbhttps://dev.arvados.org/issues/12960?journal_id=632152018-06-06T15:16:46ZTom Morristfmorris@veritasgenetics.com
<ul><li><strong>Target version</strong> changed from <i>Arvados Future Sprints</i> to <i>2018-06-20 Sprint</i></li></ul> Arvados - Idea #12960: [API] Migrate remaining "properties" fields to jsonbhttps://dev.arvados.org/issues/12960?journal_id=632162018-06-06T15:17:09ZPeter Amstutzpeter.amstutz@curii.com
<ul><li><strong>Assigned To</strong> set to <i>Peter Amstutz</i></li></ul> Arvados - Idea #12960: [API] Migrate remaining "properties" fields to jsonbhttps://dev.arvados.org/issues/12960?journal_id=632812018-06-07T18:39:05ZPeter Amstutzpeter.amstutz@curii.com
<ul></ul><p>12960-migrate-properties @ <a class="changeset" title="12960: Migrate properties fields to jsonb to support subproperty filters. Specifically: links.pr..." href="https://dev.arvados.org/projects/arvados/repository/arvados/revisions/a518eb2b3b743259fe51b1e61d2648677e583cac">a518eb2b3b743259fe51b1e61d2648677e583cac</a></p>
<p>Convert links.properties and container_requests.properties, nodes.properties, and nodes.info to jsonb and add gin index.</p> Arvados - Idea #12960: [API] Migrate remaining "properties" fields to jsonbhttps://dev.arvados.org/issues/12960?journal_id=632852018-06-07T18:42:28ZPeter Amstutzpeter.amstutz@curii.com
<ul><li><strong>Status</strong> changed from <i>New</i> to <i>In Progress</i></li></ul> Arvados - Idea #12960: [API] Migrate remaining "properties" fields to jsonbhttps://dev.arvados.org/issues/12960?journal_id=633002018-06-08T17:02:27ZTom Cleggtom@curii.com
<ul></ul><p>LGTM.</p>
<p>Strangely, the db:migrate output is verbose about the container_requests.properties iteration but silent about the others.</p>
<pre>
== 20180607175050 PropertiesToJsonb: migrating ================================
-- execute("DROP INDEX IF EXISTS container_requests_full_text_search_idx")
-> 0.0004s
-- execute("CREATE INDEX container_requests_full_text_search_idx ON container_requests USING gin(to_tsvector('english', coalesce(uuid,'') || ' ' || coalesce(owner_uuid,'') || ' ' || coalesce(modified_by_client_uuid,'') || ' ' || coalesce(modified_by_user_uuid,'') || ' ' || coalesce(name,'') || ' ' || coalesce(description,'') || ' ' || coalesce(properties::text,'') || ' ' || coalesce(state,'') || ' ' || coalesce(requesting_container_uuid,'') || ' ' || coalesce(container_uuid,'') || ' ' || coalesce(runtime_constraints::text,'') || ' ' || coalesce(container_image,'') || ' ' || coalesce(environment::text,'') || ' ' || coalesce(cwd,'') || ' ' || coalesce(command::text,'') || ' ' || coalesce(output_path,'') || ' ' || coalesce(filters,'') || ' ' || coalesce(scheduling_parameters::text,'') || ' ' || coalesce(output_uuid,'') || ' ' || coalesce(log_uuid,'') || ' ' || coalesce(output_name,'')))")
-> 0.0083s
== 20180607175050 PropertiesToJsonb: migrated (0.3391s) =======================
</pre>
<p>...but test.log shows all of the changes, so I guess it's ok.</p>
<pre>
Migrating to PropertiesToJsonb (20180607175050)
(0.2ms) BEGIN
(0.3ms) DROP INDEX IF EXISTS nodes_full_text_search_idx
(55.3ms) ALTER TABLE nodes ALTER COLUMN properties TYPE jsonb USING properties::jsonb
(0.5ms) CREATE INDEX nodes_index_on_properties ON nodes USING gin (properties)
(0.1ms) DROP INDEX IF EXISTS nodes_full_text_search_idx
(390.1ms) ALTER TABLE nodes ALTER COLUMN info TYPE jsonb USING info::jsonb
(0.4ms) CREATE INDEX nodes_index_on_info ON nodes USING gin (info)
(0.3ms) DROP INDEX IF EXISTS container_requests_full_text_search_idx
(61.6ms) ALTER TABLE container_requests ALTER COLUMN properties TYPE jsonb USING properties::jsonb
(0.4ms) CREATE INDEX container_requests_index_on_properties ON container_requests USING gin (properties)
(0.1ms) DROP INDEX IF EXISTS links_full_text_search_idx
(91.3ms) ALTER TABLE links ALTER COLUMN properties TYPE jsonb USING properties::jsonb
(0.4ms) CREATE INDEX links_index_on_properties ON links USING gin (properties)
(0.2ms) DROP INDEX IF EXISTS container_requests_full_text_search_idx
(1.5ms) CREATE INDEX container_requests_full_text_search_idx ON container_requests USING gin(to_tsvector('english', coalesce(uuid,'') || ' ' || coalesce(owner_uuid,'') || ' ' || coalesce(modified_by_client_uuid,'') || ' ' || coalesce(modified_by_user_uuid,'') || ' ' || coalesce(name,'') || ' ' || coalesce(description,'') || ' ' || coalesce(properties::text,'') || ' ' || coalesce(state,'') || ' ' || coalesce(requesting_container_uuid,'') || ' ' || coalesce(container_uuid,'') || ' ' || coalesce(runtime_constraints::text,'') || ' ' || coalesce(container_image,'') || ' ' || coalesce(environment::text,'') || ' ' || coalesce(cwd,'') || ' ' || coalesce(command::text,'') || ' ' || coalesce(output_path,'') || ' ' || coalesce(filters,'') || ' ' || coalesce(scheduling_parameters::text,'') || ' ' || coalesce(output_uuid,'') || ' ' || coalesce(log_uuid,'') || ' ' || coalesce(output_name,'')))
SQL (0.2ms) INSERT INTO "schema_migrations" ("version") VALUES ($1) [["version", "20180607175050"]]
(3.4ms) COMMIT
</pre>
<p>Also noticed it's impossible to db:migrate a test db, because test fixtures are still being inserted as YAML. But db:drop + db:setup + db:fixtures:load works ok.</p> Arvados - Idea #12960: [API] Migrate remaining "properties" fields to jsonbhttps://dev.arvados.org/issues/12960?journal_id=633132018-06-08T18:30:52ZPeter Amstutzpeter.amstutz@curii.com
<ul></ul><p>Due diligence: <a class="external" href="https://ci.curoverse.com/job/developer-run-tests/744/">https://ci.curoverse.com/job/developer-run-tests/744/</a></p> Arvados - Idea #12960: [API] Migrate remaining "properties" fields to jsonbhttps://dev.arvados.org/issues/12960?journal_id=633142018-06-08T18:38:03ZPeter Amstutzpeter.amstutz@curii.com
<ul></ul><p>It is also not logging ALTER TABLE. But I agree it seems to be an ActiveRecord quirk rather than an actual problem. I will merge if Jenkins is happy.</p> Arvados - Idea #12960: [API] Migrate remaining "properties" fields to jsonbhttps://dev.arvados.org/issues/12960?journal_id=633162018-06-08T19:07:37ZPeter Amstutzpeter.amstutz@curii.com
<ul></ul><p>Oops, jenkins is not happy. Trivial different in serialization from jsonb failed a test. Running again @ <a class="changeset" title="12960: Fix test due to slightly different serialization from jsonb Arvados-DCO-1.1-Signed-off-by..." href="https://dev.arvados.org/projects/arvados/repository/arvados/revisions/cefddd8efdc88c3d33e5aad4c02e96c64cbe8242">cefddd8efdc88c3d33e5aad4c02e96c64cbe8242</a></p>
<p><a class="external" href="https://ci.curoverse.com/job/developer-run-tests/746/">https://ci.curoverse.com/job/developer-run-tests/746/</a></p> Arvados - Idea #12960: [API] Migrate remaining "properties" fields to jsonbhttps://dev.arvados.org/issues/12960?journal_id=633492018-06-11T19:32:40ZPeter Amstutzpeter.amstutz@curii.com
<ul><li><strong>Status</strong> changed from <i>In Progress</i> to <i>Resolved</i></li><li><strong>% Done</strong> changed from <i>0</i> to <i>100</i></li></ul><p>Applied in changeset <a class="changeset" title="Merge branch '12960-migrate-properties' closes #12960 Arvados-DCO-1.1-Signed-off-by: Peter Amstu..." href="https://dev.arvados.org/projects/arvados/repository/arvados/revisions/1e03ea4765bee6db9331a1265c8abf2c3e45caff">arvados|1e03ea4765bee6db9331a1265c8abf2c3e45caff</a>.</p> Arvados - Idea #12960: [API] Migrate remaining "properties" fields to jsonbhttps://dev.arvados.org/issues/12960?journal_id=647212018-07-23T18:41:44ZTom Morristfmorris@veritasgenetics.com
<ul><li><strong>Release</strong> set to <i>13</i></li></ul>