Conversation

Notices

1. Embed this notice
   Sexy Moon (moon@shitposter.club)'s status on Thursday, 28-Dec-2023 20:23:59 JST Sexy Moon
   I am not convinced though that postgresql can index json properties as effectively as breaking data out into columns and relational tables.
   • Haelwenn /элвэн/ :triskell: and kaia like this.
   • Embed this notice
     on-lain ✔ᵛᵉʳᶦᶠᶦᵉᵈ (lain@lain.com)'s status on Thursday, 28-Dec-2023 20:26:42 JST on-lain ✔ᵛᵉʳᶦᶠᶦᵉᵈ

     in reply to
     @Moon if you do a regular functional index (that is, something like an index on data->>'actor'), there is no difference. Full json indexing with gin (so one can use the @ operator) is a different matter.
   • Embed this notice
     Sexy Moon (moon@shitposter.club)'s status on Thursday, 28-Dec-2023 20:27:20 JST Sexy Moon

     in reply to

     • on-lain ✔ᵛᵉʳᶦᶠᶦᵉᵈ
     @lain I am trying to wrap my head around cases like where you have an embedded array. compared to a child table.
   • Embed this notice
     your new favourite fungus (duponin@udongein.xyz)'s status on Thursday, 28-Dec-2023 20:28:00 JST your new favourite fungus

     in reply to
     @Moon it can but uses it takes much more storage
     Sexy Moon likes this.
   • Embed this notice
     Haelwenn /элвэн/ :triskell: (lanodan@queer.hacktivis.me)'s status on Thursday, 28-Dec-2023 20:29:29 JST Haelwenn /элвэн/ :triskell:

     in reply to
     @Moon Yeah, if you have fairly a fairly static json schema it's better to go traditional.
   • Embed this notice
     Sexy Moon (moon@shitposter.club)'s status on Thursday, 28-Dec-2023 20:31:15 JST Sexy Moon

     in reply to

     • on-lain ✔ᵛᵉʳᶦᶠᶦᵉᵈ
     @lain for example let's say you have an AP object with a list of actors in the "to" field and you're gonna send updates to everybody in the to field, but you want to filter the ones where the accounts are disabled, or the server rejects you. it seems simpler to just have a child table and filter in the query
   • Embed this notice
     Sexy Moon (moon@shitposter.club)'s status on Thursday, 28-Dec-2023 20:33:57 JST Sexy Moon

     in reply to

     • Haelwenn /элвэн/ :triskell:
     @lanodan I am fiddling around with ideas and thinking that I'll have an objects table for most of the json, but expected fields will be broken out into columns or child tables.
     Haelwenn /элвэн/ :triskell: likes this.
   • Embed this notice
     on-lain ✔ᵛᵉʳᶦᶠᶦᵉᵈ (lain@lain.com)'s status on Thursday, 28-Dec-2023 20:35:56 JST on-lain ✔ᵛᵉʳᶦᶠᶦᵉᵈ

     in reply to
     @Moon depending on the situation this can lead to erratic performance because i think postgresql still can't properly get statistics for the whole jsonb object (see https://blog.anayrat.info/en/2017/11/26/postgresql-jsonb-and-statistics/). But if you make a functional index on it it's exactly the same as having a column.
     Sexy Moon likes this.
   • Embed this notice
     on-lain ✔ᵛᵉʳᶦᶠᶦᵉᵈ (lain@lain.com)'s status on Thursday, 28-Dec-2023 20:38:26 JST on-lain ✔ᵛᵉʳᶦᶠᶦᵉᵈ

     in reply to

     • on-lain ✔ᵛᵉʳᶦᶠᶦᵉᵈ
     @Moon and i'm not sure how true that still is, see https://www.postgresql.org/message-id/c9c4bd20-996c-100e-25e7-27e27bb1da7c@enterprisedb.com. If you're working with 'documents' like activties, i think it's perfectly fine to have essentially just jsonb and put indexes on the expected fields.
     Sexy Moon likes this.
   • Embed this notice
     Sexy Moon (moon@shitposter.club)'s status on Thursday, 28-Dec-2023 20:39:47 JST Sexy Moon

     in reply to

     • on-lain ✔ᵛᵉʳᶦᶠᶦᵉᵈ

     @lain probably no problem with things like this?

     SELECT t.* FROM unnest(ARRAY[1,2,3,2,3,5]) item_id LEFT JOIN items t on t.id=item_id
   • Embed this notice
     Sexy Moon (moon@shitposter.club)'s status on Thursday, 28-Dec-2023 20:41:03 JST Sexy Moon

     in reply to

     • on-lain ✔ᵛᵉʳᶦᶠᶦᵉᵈ
     @lain thanks this is all very helpful
   • Embed this notice
     Sexy Moon (moon@shitposter.club)'s status on Thursday, 28-Dec-2023 20:41:54 JST Sexy Moon

     in reply to

     • on-lain ✔ᵛᵉʳᶦᶠᶦᵉᵈ
     @lain I think I am giving up exploring nosql json stores
   • Embed this notice
     on-lain ✔ᵛᵉʳᶦᶠᶦᵉᵈ (lain@lain.com)'s status on Thursday, 28-Dec-2023 20:46:40 JST on-lain ✔ᵛᵉʳᶦᶠᶦᵉᵈ

     in reply to
     @Moon yeah that's no issue
   • Embed this notice
     on-lain ✔ᵛᵉʳᶦᶠᶦᵉᵈ (lain@lain.com)'s status on Thursday, 28-Dec-2023 20:47:35 JST on-lain ✔ᵛᵉʳᶦᶠᶦᵉᵈ

     in reply to
     @Moon postgres comes out ahead in nearly all benchmarks against any nosql stores, so i don't think it's worth it unless you really know that your specific use case is more performant AND you really need it. jsonb support in postgres is by now really mature, too.
     Sexy Moon and kaia like this.
   • Embed this notice
     Sexy Moon (moon@shitposter.club)'s status on Thursday, 28-Dec-2023 20:48:30 JST Sexy Moon

     in reply to

     • on-lain ✔ᵛᵉʳᶦᶠᶦᵉᵈ
     @lain the main thing that drew me to them was that they have more advanced mechanisms for updating materialized views when data changes
   • Embed this notice
     on-lain ✔ᵛᵉʳᶦᶠᶦᵉᵈ (lain@lain.com)'s status on Thursday, 28-Dec-2023 20:51:42 JST on-lain ✔ᵛᵉʳᶦᶠᶦᵉᵈ

     in reply to
     @Moon i haven't really found much use for materialized views, but your use case my be different. Do you know generated columns? (https://www.postgresql.org/docs/current/ddl-generated-columns.html)
     Sexy Moon likes this.
   • Embed this notice
     Sexy Moon (moon@shitposter.club)'s status on Thursday, 28-Dec-2023 20:51:55 JST Sexy Moon

     in reply to

     • on-lain ✔ᵛᵉʳᶦᶠᶦᵉᵈ
     @lain yeah, I might use them soon for something.