fatcat/rust/migrations, branch x-attic-preview [no description] https://git.bnewbold.net/fatcat/atom?h=x-attic-preview 2018-09-23T02:03:41+00:00 fix isbn13 size check (!) 2018-09-23T02:03:41+00:00 Bryan Newbold bnewbold@robocracy.org 2018-09-23T02:02:23+00:00 urn:sha1:26f05eaf914a4a967f85f13dab33c65ea53d3f51 container_name (not title), and shrink some BIGINTs 2018-09-23T00:26:23+00:00 Bryan Newbold bnewbold@robocracy.org 2018-09-23T00:26:23+00:00 urn:sha1:7c53a08666d28f2a2f4c9572f63ae09a362b017b fix CHECK clauses 2018-09-17T20:56:36+00:00 Bryan Newbold bnewbold@robocracy.org 2018-09-17T20:56:36+00:00 urn:sha1:8baa2d8808797e423829fb040d70206d24b6156e UNIQUE constraint on edits (ident per editgroup) 2018-09-17T20:21:20+00:00 Bryan Newbold bnewbold@robocracy.org 2018-09-17T20:21:20+00:00 urn:sha1:5feeb70f930e733587ad7fa33d361cf24602abca use timestamptz (not WITHOUT TIME ZONE) 2018-09-17T20:19:48+00:00 Bryan Newbold bnewbold@robocracy.org 2018-09-17T20:19:48+00:00 urn:sha1:1c708d6a4ea155710c7f1b752e9bcb01ac75f98d CHAR/VARCHAR -> TEXT w/ CHECK 2018-09-17T20:13:16+00:00 Bryan Newbold bnewbold@robocracy.org 2018-09-17T20:13:16+00:00 urn:sha1:c292aa9ef819ec47bf129ff61686e1be782abf70 update extid columns: varchar size limits, no char columns 2018-09-16T00:00:12+00:00 Bryan Newbold bnewbold@robocracy.org 2018-09-16T00:00:10+00:00 urn:sha1:be3a4333c5880f3af1a4e6adbba31a030a5ebc72 In PostgreSQL, there is no query or size differences between CHAR, VARCHAR, and TEXT: they use the same backend. CHAR will pad out with spaces and VARCHAR enforces a size limit. The enforced VARCHAR size limit seems like ok defensive programming/schema here, preventing large values from getting stored for some columns. I have some FUD concern about fixed-sized identifiers and indexes, so switching old CHARs (eg, sha1 hex encoded columns) to VARCHAR instead. use JSONB over JSON 2018-09-11T02:46:05+00:00 Bryan Newbold bnewbold@robocracy.org 2018-09-11T02:46:02+00:00 urn:sha1:b15fa552e288bec5bbc2b07a3e11bab9235a1e7c Sort of an impulsive "why not" decision. From earlier tests, expect this to consume a few % more disk (but not index)... adds a lot more query flexibility if we want that in the future. Seems worth the price. sql: add an index from cockroach branch 2018-09-11T02:44:20+00:00 Bryan Newbold bnewbold@robocracy.org 2018-09-11T02:44:20+00:00 urn:sha1:3e1823a4ae0fee443996b10b9d82a9edabc621fa remove 'IS NOT NULL' identifier index constraints 2018-09-10T23:46:52+00:00 Bryan Newbold bnewbold@robocracy.org 2018-09-10T23:46:49+00:00 urn:sha1:2e335344e59de0ff42f1c2797a4e4ffbf3f4b30b These seemed to be resulting in table scans on 404s in QA with postgres 10, despite the adding "IS NOT NULL" WHERE clauses earlier. Query time was very significant, even for the creator table (1.2 seconds or so on SSD). I looked at using hash indices (which have improved in postgres 10), which could save index size (disk and RAM) and potentially be faster for these trivial exact lookups, but didn't go for it at this time.