BUG - Needs : PG::DiskFull: ERROR: could not resize shared memory segment

[clairezed]

Edit 07/02 : ticket commencé, mais pas encore fini, compter encore une ou deux 1/2 journée (je crains d'avoir fait le + facile)

Principales sources de ces erreurs :

Pages besoin

Soupçon : un omnisearch trop gourmand ? (le referer indique dans tous les cas vus un paramètre omnisearch)
-> revoir la configuration de pg_search pour Need

Peut-être aussi un pb de N+1. Cf sentry :

SELECT "subjects".*
FROM "subjects"
LEFT OUTER JOIN "themes" ON "themes"."id" = "subjects"."theme_id"
WHERE "subjects"."theme_id" = $1 AND "subjects"."archived_at" IS NULL
ORDER BY "themes"."interview_sort_order" ASC, "themes"."id" ASC, "subjects"."interview_sort_order"
  ASC, "subjects"."id" ASC

ou

SELECT 1 AS one
FROM "user_rights"
WHERE "user_rights"."user_id" = $1 AND "user_rights"."category" = $2
LIMIT $3

/app/app/models/user.rb in is_admin? at line 282

Ressources

• https://www.crunchydata.com/blog/postgres-troubleshooting-diskfull-error-could-not-resize-shared-memory-segment

Pg_search

Ts vector ?

adding a pre-calculated ts_vector column that is automatically written on each INSERT/UPDATE, adding an index to use the new column

1 Materialized Views

Materialized views are essentially SQL queries whose results can be cached to a table, indexed, and periodically refreshed when desired.

One option would be to cache the tsvectors using a materialized view.
Materialized views may be a good option for your data.
One downside is that the entire view must be refreshed with:

REFRESH MATERIALIZED VIEW view_name;

That may be a good fit in some scenarios, perhaps run daily as a cron or Scheduler job.

2. Update tsvector with a trigger and cache result

• implique de changer le format du schéma => Since we can’t dump a tsvector column to schema.rb, we need to switch to the SQL schema format in our config/application.rb: config.active_record.schema_format = :sql (plus de db/schema.rb)

3. Index !

---

https://caspg.com/blog/optimizing-full-text-search-with-postgres-materialized-view-in-rails

• materialized view with scenic

pg_search_scope(
  :search,
  against: [:title, :description],
  associated_against: { skills: :name, company: :name },
  using: {
    tsearch: {
      dictionary: 'english',
    },
  },
)

---

https://web.archive.org/web/20150329002818/http://altoros.github.io/2013/implementing-and-improving-postgresql-fulltext-search/

pg_search_scope :search,
                against: [:title, :content],
                associated_against: {
                  author: :name,
                  comments: :content
                },
                using: {
                  tsearch: {
                    dictionary: 'english',
                    any_word: true,
                    prefix: true
                  }
                }

  new.search_vector :=
  setweight(to_tsvector('pg_catalog.english', coalesce(new.title, '')), 'A')                  ||
  setweight(to_tsvector('pg_catalog.english', coalesce(new.content, '')), 'B')                ||
  setweight(to_tsvector('pg_catalog.english', coalesce(article_author.name, '')), 'B')        ||
  setweight(to_tsvector('pg_catalog.english', coalesce(article_comments.content, '')), 'B');

return new;

---

https://pganalyze.com/blog/full-text-search-ruby-rails-postgres

• creation d'une colonne tsvector

pg_search_scope :search_job,
                  against: { title: 'A', description: 'B' },
                  using: { tsearch: { dictionary: 'english' } }

    execute <<-SQL
    ALTER TABLE jobs
    ADD COLUMN searchable tsvector GENERATED ALWAYS AS (
      setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
      setweight(to_tsvector('english', coalesce(description,'')), 'B')
    ) STORED;
  SQL

---

https://github.com/Casecommons/pg_search/wiki/Building-indexes

pg_search_scope :search_full_text,
                :against => {
                  name: "A",
                  keywords: "B",
                  body: "C"
                },
                :using => {
                  :tsearch => {
                    :prefix => true,
                    :any_word => true,
                    :dictionary => "english",
                  }
                }

CREATE INDEX "ts_vector_index_on_pages" ON "pages" USING gin (
  setweight(to_tsvector('english', coalesce("pages"."name"::text, '')), 'A') || 
  setweight(to_tsvector('english', coalesce("pages"."keywords"::text, '')), 'B') || 
  setweight(to_tsvector('english', coalesce("pages"."body"::text, '')), 'C')
)

---

https://thoughtbot.com/blog/optimizing-full-text-search-with-postgres-tsvector-columns-and-triggers

• creation d'une colonne tsvector
• index de cette colonne (gin)
• mise en place d'un trigger

---

https://hybrd.co/posts/full-text-search-in-postgres-with-view-backed-models-in-rails

• utilisation d'une view scenic
• perf pas ouf

---

Need.omnisearch("Claire")
  Need Load (3194.9ms)  SELECT "needs".* FROM "needs" INNER JOIN (
    SELECT "needs"."id" AS pg_search_id, (
      ts_rank((
        to_tsvector('simple', unaccent(coalesce(("needs"."content")::text, ''))) || 
        to_tsvector('simple', unaccent(coalesce((pg_search_494d24c1a9beb1d8e6b4b3.pg_search_ed8dc47054d1e6d31010d1)::text, ''))) || 
        to_tsvector('simple', unaccent(coalesce((pg_search_494d24c1a9beb1d8e6b4b3.pg_search_92086b0aa3ccd60968fbd8)::text, ''))) || 
        to_tsvector('simple', unaccent(coalesce((pg_search_70802142d8a2acc990bd88.pg_search_ec314d2e365ec97b0a4160)::text, ''))) || 
        to_tsvector('simple', unaccent(coalesce((pg_search_70802142d8a2acc990bd88.pg_search_6726fc8027f40badcdf781)::text, ''))) || 
        to_tsvector('simple', unaccent(coalesce((pg_search_867ecf45510664bbb368dc.pg_search_38fa5739bafa415fc2289c)::text, ''))) || 
        to_tsvector('simple', unaccent(coalesce((pg_search_28c82e8dc93bdf5313b92b.pg_search_d3d680e076bc1b30107182)::text, '')))
      ), 
      (to_tsquery('simple', ''' ' || unaccent('Claire') || ' ''' || ':*')), 0)) 
    AS rank 
    FROM "needs" LEFT OUTER JOIN (
      SELECT "needs"."id" AS id, 
      "contacts"."full_name"::text AS pg_search_ed8dc47054d1e6d31010d1, 
      "contacts"."email"::text AS pg_search_92086b0aa3ccd60968fbd8 
      FROM "needs" INNER JOIN "diagnoses" ON "diagnoses"."id" = "needs"."diagnosis_id" INNER JOIN "contacts" ON "contacts"."id" = "diagnoses"."visitee_id"
    ) pg_search_494d24c1a9beb1d8e6b4b3 ON pg_search_494d24c1a9beb1d8e6b4b3.id = "needs"."id" LEFT OUTER JOIN (
      SELECT "needs"."id" AS id, 
      "companies"."name"::text AS pg_search_ec314d2e365ec97b0a4160, 
      "companies"."siren"::text AS pg_search_6726fc8027f40badcdf781 
      FROM "needs" INNER JOIN "diagnoses" ON "diagnoses"."id" = "needs"."diagnosis_id" INNER JOIN "facilities" ON "facilities"."id" = "diagnoses"."facility_id" INNER JOIN "companies" ON "companies"."id" = "facilities"."company_id"
    ) pg_search_70802142d8a2acc990bd88 ON pg_search_70802142d8a2acc990bd88.id = "needs"."id" LEFT OUTER JOIN (
      SELECT "needs"."id" AS id, 
      "facilities"."readable_locality"::text AS pg_search_38fa5739bafa415fc2289c 
      FROM "needs" INNER JOIN "diagnoses" ON "diagnoses"."id" = "needs"."diagnosis_id" INNER JOIN "facilities" ON "facilities"."id" = "diagnoses"."facility_id"
    ) pg_search_867ecf45510664bbb368dc ON pg_search_867ecf45510664bbb368dc.id = "needs"."id" LEFT OUTER JOIN (
      SELECT "needs"."id" AS id, 
      "subjects"."label"::text AS pg_search_d3d680e076bc1b30107182 
      FROM "needs" INNER JOIN "subjects" ON "subjects"."id" = "needs"."subject_id"
    ) pg_search_28c82e8dc93bdf5313b92b ON pg_search_28c82e8dc93bdf5313b92b.id = "needs"."id" WHERE ((
      to_tsvector('simple', unaccent(coalesce(("needs"."content")::text, ''))) || 
      to_tsvector('simple', unaccent(coalesce((pg_search_494d24c1a9beb1d8e6b4b3.pg_search_ed8dc47054d1e6d31010d1)::text, ''))) || 
      to_tsvector('simple', unaccent(coalesce((pg_search_494d24c1a9beb1d8e6b4b3.pg_search_92086b0aa3ccd60968fbd8)::text, ''))) || 
      to_tsvector('simple', unaccent(coalesce((pg_search_70802142d8a2acc990bd88.pg_search_ec314d2e365ec97b0a4160)::text, ''))) || 
      to_tsvector('simple', unaccent(coalesce((pg_search_70802142d8a2acc990bd88.pg_search_6726fc8027f40badcdf781)::text, ''))) || 
      to_tsvector('simple', unaccent(coalesce((pg_search_867ecf45510664bbb368dc.pg_search_38fa5739bafa415fc2289c)::text, ''))) || 
      to_tsvector('simple', unaccent(coalesce((pg_search_28c82e8dc93bdf5313b92b.pg_search_d3d680e076bc1b30107182)::text, '')))
    ) @@ (
      to_tsquery('simple', ''' ' || unaccent('Claire') || ' ''' || ':*')))
    ) AS pg_search_266d1c70a4cbe7cdb557b6 ON "needs"."id" = pg_search_266d1c70a4cbe7cdb557b6.pg_search_id 
    /* loading for pp */ ORDER BY pg_search_266d1c70a4cbe7cdb557b6.rank DESC, "needs"."id" ASC LIMIT $1  [["LIMIT", 11]]