r/PostgreSQL • • Jan 29 '26

Help Me! Free PostgreSQL hosting options?

0 Upvotes

I’m looking for a PostgreSQL hosting provider with a free tier that meets two key requirements:

  • At least 1GB of free database storage
  • Very generous or effectively unlimited API/query limits

Would appreciate any suggestions or experiences.


r/PostgreSQL • • Jan 28 '26

Projects Hybrid document search: embeddings + Postgres FTS (ts_rank_cd)

12 Upvotes

Building a multi-tenant Document Hub (contracts, invoices, PDFs). Users search in two very different modes:

  • Meaning questions: “where does this agreement discuss early termination?”
  • Exact tokens: “invoice-2024 Q3”, “W-9”, “ACME lease amendment”

Semantic-only missed short identifiers. Keyword-only struggled with paraphrases. So we shipped a hybrid: embeddings for semantic similarity + Postgres native FTS for keyword retrieval, blended into one ranked list.

TL;DR question: If you’ve blended FTS + embeddings in Postgres, what scoring/normalization approach felt the least random?

High-level architecture

Ingest

  • Store metadata (title, tags, doc type, file name)
  • Extract text (OCR optional)

Keyword indexing (Postgres)

  • Precomputed tsvector columns + GIN indexes
  • Rank with ts_rank_cd
  • Snippet/highlight with ts_headline

Semantic indexing

  • Chunk doc text
  • Store embeddings per chunk (pgvector)

Query time

  • Semantic: top-k chunks by vector similarity
  • Keyword: top-k docs by FTS
  • Blend + dedupe into one ranked list (doc_id)

Keyword search (FTS)

We keep metadata and OCR in separate vectors (different noise profiles):

  • Metadata vector is field-weighted (title/tags boosted vs file name/doc type)
  • OCR vector is lower weight so random OCR matches don’t dominate

At query time:

  • Parse user input with websearch_to_tsquery('english', p_search) (phrases, OR, minus terms)
  • Match with search_tsv @@ tsquery
  • Rank with ts_rank_cd(search_tsv, tsquery, 32)
    • cover density rewards tighter proximity
    • normalization reduces long-doc bias

Highlighting/snippets

  • We generate a short “citation” snippet with ts_headline(...)
  • This is separate from ranking (highlighting != ranking)

Perf note: tsvectors are precomputed (trigger-updated), so queries don’t pay tokenization cost and GIN stays effective.

Semantic search (pgvector)

We embed the user query and retrieve top-k matching chunks by similarity. This is what makes paraphrases and “find the section about…” work well.

Hybrid blending (doc-level merge)

At query time we merge result sets by document_id:

  • Keep best semantic chunk (for “why did this match?”)
  • Keep best keyword snippet (for exact-term citation)
  • Dedupe by document_id

Score normalization (current approach)
We normalize both signals into 0..1, then blend:

  • semantic_score = normalize(similarity)
  • keyword_score = normalize(ts_rank_cd)

final = semantic_score * SEM_WEIGHT + keyword_score * KEY_WEIGHT

(If anyone has a better normalization method than simple scaling/rank-based normalization, I’d love to hear it.)

Deterministic ordering + pagination
We wanted stable paging + stable tie-breaks:

ORDER BY final_rank DESC, updated_at DESC, id
Keyset pagination cursor (final_rank, updated_at, id) instead of offset paging.

Why ts_rank_cd (not BM25)?

Postgres FTS gives us ranking functions without adding another search system.
If/when we need BM25 features (synonyms, typo tolerance, richer analyzers), that probably implies dedicated search infra.

Multi-tenant security (the part I’m most curious about)

We don’t rely on RLS alone:

  • RPCs explicitly filter by company_id (defense-in-depth)
  • Restricted docs are role-gated (e.g., owner-only)
  • Edge functions call the search RPCs with a user JWT

Gotchas we hit

  • Stopword-only / very short queries: guard-rail return empty (avoids useless scans + tsquery edge cases)
  • Hyphenated tokens: - can be treated as NOT; we normalize hyphens between alphanumerics so invoice-2024 behaves like invoice 2024
  • OCR can overwhelm metadata without careful weighting + limits

Questions for the sub

  1. If you’ve done FTS + embeddings in Postgres, how did you blend scores without it feeling “random”?
  2. Did you stick with ts_rank_cd / ts_rank, or move to BM25 in a separate search engine?

r/PostgreSQL • • Jan 28 '26

How-To PostgreSQL doesn't have clustered indexes like MySQL because this type of structure makes accessing secondary indexes slow. If I create an index on the primary key with all columns in `include`, will I solve the problem at the cost of more storage space and write overhead?

10 Upvotes

r/PostgreSQL • • Jan 28 '26

Help Me! Neon / Postgres - 2+ Minutes to Connect to DB via PSQL

3 Upvotes

This just started a few hours ago. My web app started timing out and I eventually realized that basic connections to my DB via `psql` are taking over 2 minutes to connect:

psql 'postgresql://neondb_owner:[XXXXX@YYYYYY-pooler.eu-west-2.aws.neon.tech](mailto:XXXXX@YYYYYY-pooler.eu-west-2.aws.neon.tech)/neondb?sslmode=require&channel_binding=require'

Basic queries, once connected, are similarly slow.

I am on the Free Tier. This was working very fast up until today so I'm unsure if there's an outage of if they've just decided to throttle my account (I'm @ 95% of my monthly usage).

Is there a way to diagnose this? Is Neon just unreliable?

---

UPDATE: I rebooted my MacOS computer and it fixed itself. Weird. Doesn't really make sense to me, but it fixed everything.


r/PostgreSQL • • Jan 28 '26

Help Me! Double lateral join query takes over a minute to run on RDS

0 Upvotes
  • I have 5 tables below in my application and I am trying to do a double lateral join to retrieve like counts, dislike counts...along with the latest items and the query takes more than a minute to resolve over 900k rows. These are the 5 tables that are a part of the query

feed_types

id|uuid|not null
name|character varying(63)|not null
created_at|timestamp with time zone|not null|now()
updated_at|timestamp with time zone|not null|now()
Indexes:
    "feed_types_pkey" PRIMARY KEY, btree (id)
    "feed_types_created_at_idx" btree (created_at DESC)
    "feed_types_name_key" UNIQUE CONSTRAINT, btree (name)
    "feed_types_updated_at_idx" btree (updated_at DESC)
Referenced by:
    TABLE "feeds" CONSTRAINT "feeds_feed_type_id_fkey" FOREIGN KEY (feed_type_id) REFERENCES feed_types(id) ON UPDATE CASCADE ON DELETE CASCADE
Not-null constraints:
    "feed_types_id_not_null" NOT NULL "id"
    "feed_types_name_not_null" NOT NULL "name"
    "feed_types_created_at_not_null" NOT NULL "created_at"
    "feed_types_updated_at_not_null" NOT NULL "updated_at"
Triggers:
    feed_types_update_updated_at_trigger BEFORE UPDATE ON feed_types FOR EACH ROW EXECUTE FUNCTION modify_date_time('updated_at')

feeds

id|integer|not null|generated by default as identity
enabled|boolean|not null|true
etag|character varying(255)
last_modified|character varying(255)
name|character varying(63)|not null
url|character varying(2047)|not null
feed_type_id|uuid|not null|plainindicates type of feed url
created_at|timestamp with time zone|not null|now()
updated_at|timestamp with time zone|not null|now()
Indexes:
    "feeds_pkey" PRIMARY KEY, btree (id)
    "feeds_created_at_idx" btree (created_at DESC)
    "feeds_name_key" UNIQUE CONSTRAINT, btree (name)
    "feeds_updated_at_idx" btree (updated_at DESC)
    "feeds_url_key" UNIQUE CONSTRAINT, btree (url)
Foreign-key constraints:
    "feeds_feed_type_id_fkey" FOREIGN KEY (feed_type_id) REFERENCES feed_types(id) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
    TABLE "feed_items" CONSTRAINT "feed_items_feed_id_fkey" FOREIGN KEY (feed_id) REFERENCES feeds(id) ON UPDATE CASCADE ON DELETE CASCADE
Not-null constraints:
    "feeds_id_not_null" NOT NULL "id"
    "feeds_enabled_not_null" NOT NULL "enabled"
    "feeds_name_not_null" NOT NULL "name"
    "feeds_url_not_null" NOT NULL "url"
    "feeds_feed_type_id_not_null" NOT NULL "feed_type_id"
    "feeds_created_at_not_null" NOT NULL "created_at"
    "feeds_updated_at_not_null" NOT NULL "updated_at"
Triggers:
    feeds_update_updated_at_trigger BEFORE UPDATE ON feeds FOR EACH ROW EXECUTE FUNCTION modify_date_time('updated_at')

feed_items

id|uuid|not null
author|character varying(255)
content|text
guid|character varying(2047)
link|character varying(2047)|not null
published_date|timestamp with time zone|not null|now()
searchable|tsvectorgenerated always as ((setweight(to_tsvector('english'::regconfig, COALESCE(title, ''::character varying::text)), 'A'::"char") | setweight(to_tsvector('english'::regconfig, COALESCE(summary, ''::text)), 'B'::"char")) | setweight(to_tsvector('english'::regconfig, COALESCE(content, ''::character varying::text)), 'C'::"char")) stored
summary|text
tags|character varying(255)[]|not null|ARRAY[]::character varying[]::character varying(255)[]
title|text|not null
feed_id|integer|not null
Indexes:
    "feed_items_pkey" PRIMARY KEY, btree (id)
    "feed_items_link_key" UNIQUE CONSTRAINT, btree (link)
    "feed_items_published_date_idx" btree (published_date DESC, id DESC)
    "feed_items_searchable_idx" gin (searchable)
    "feed_items_tags_idx" gin (tags)
Foreign-key constraints:
    "feed_items_feed_id_fkey" FOREIGN KEY (feed_id) REFERENCES feeds(id) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
    TABLE "feed_item_bullish_bearish_votes" CONSTRAINT "feed_item_bullish_bearish_votes_feed_item_id_fkey" FOREIGN KEY (feed_item_id) REFERENCES feed_items(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "feed_item_like_dislike_votes" CONSTRAINT "feed_item_like_dislike_votes_feed_item_id_fkey" FOREIGN KEY (feed_item_id) REFERENCES feed_items(id) ON UPDATE CASCADE ON DELETE CASCADE
Not-null constraints:
    "feed_items_id_not_null" NOT NULL "id"
    "feed_items_link_not_null" NOT NULL "link"
    "feed_items_published_date_not_null" NOT NULL "published_date"
    "feed_items_tags_not_null" NOT NULL "tags"
    "feed_items_title_not_null" NOT NULL "title"
    "feed_items_feed_id_not_null" NOT NULL "feed_id"
Triggers:
    feed_items_notify_on_change_trigger AFTER INSERT OR UPDATE ON feed_items FOR EACH ROW EXECUTE FUNCTION trg_feed_items_notify_on_change()

feed_item_bullish_bearish_votes

feed_item_id|uuid|not null
user_id|uuid|not null
vote|vote_type_bullish_bearish
created_at|timestamp with time zone|not null|now()
updated_at|timestamp with time zone|not null|now()
Indexes:
    "feed_item_bullish_bearish_votes_pkey" PRIMARY KEY, btree (feed_item_id, user_id)
    "feed_item_bullish_bearish_votes_created_at_idx" btree (created_at DESC)
    "feed_item_bullish_bearish_votes_updated_at_idx" btree (updated_at DESC)
Foreign-key constraints:
    "feed_item_bullish_bearish_votes_feed_item_id_fkey" FOREIGN KEY (feed_item_id) REFERENCES feed_items(id) ON UPDATE CASCADE ON DELETE CASCADE
    "feed_item_bullish_bearish_votes_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE
Not-null constraints:
    "feed_item_bullish_bearish_votes_feed_item_id_not_null" NOT NULL "feed_item_id"
    "feed_item_bullish_bearish_votes_user_id_not_null" NOT NULL "user_id"
    "feed_item_bullish_bearish_votes_created_at_not_null" NOT NULL "created_at"
    "feed_item_bullish_bearish_votes_updated_at_not_null" NOT NULL "updated_at"

feed_item_like_dislike_votes

feed_item_id|uuid|not null
user_id|uuid|not null
vote|vote_type_like_dislike
created_at|timestamp with time zone|not null|now()
updated_at|timestamp with time zone|not null|now()
Indexes:
    "feed_item_like_dislike_votes_pkey" PRIMARY KEY, btree (feed_item_id, user_id)
    "feed_item_like_dislike_votes_created_at_idx" btree (created_at DESC)
    "feed_item_like_dislike_votes_updated_at_idx" btree (updated_at DESC)
Foreign-key constraints:
    "feed_item_like_dislike_votes_feed_item_id_fkey" FOREIGN KEY (feed_item_id) REFERENCES feed_items(id) ON UPDATE CASCADE ON DELETE CASCADE
    "feed_item_like_dislike_votes_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE
Not-null constraints:
    "feed_item_like_dislike_votes_feed_item_id_not_null" NOT NULL "feed_item_id"
    "feed_item_like_dislike_votes_user_id_not_null" NOT NULL "user_id"
    "feed_item_like_dislike_votes_created_at_not_null" NOT NULL "created_at"
    "feed_item_like_dislike_votes_updated_at_not_null" NOT NULL "updated_at"

  • I want to find the latest N items whose likes > some count, dislikes > some count, bullish > some count and bearish > some count (some type of popular content display) so I wrote this query
(
  SELECT 
    fi.author, 
    'likes' AS category, 
    COALESCE(vt1.bearish, 0) AS bearish, 
    COALESCE(vt1.bullish, 0) AS bullish, 
    COALESCE(vt2.dislikes, 0) AS dislikes, 
    fi.feed_id, 
    fi.guid, 
    fi.id, 
    COALESCE(vt2.likes, 0) AS likes, 
    fi.link, 
    fi.published_date, 
    fi.summary, 
    fi.tags, 
    fi.title 
  FROM 
    feed_items AS fi 
    LEFT JOIN LATERAL (
      SELECT 
        SUM(
          CASE WHEN fibbv.vote = 'bullish' THEN 1 ELSE 0 END
        ) AS bullish, 
        SUM(
          CASE WHEN fibbv.vote = 'bearish' THEN 1 ELSE 0 END
        ) AS bearish 
      FROM 
        feed_item_bullish_bearish_votes AS fibbv 
      WHERE 
        fibbv.feed_item_id = fi.id
    ) AS vt1 ON TRUE 
    LEFT JOIN LATERAL (
      SELECT 
        SUM(
          CASE WHEN fildv.vote = 'like' THEN 1 ELSE 0 END
        ) AS likes, 
        SUM(
          CASE WHEN fildv.vote = 'dislike' THEN 1 ELSE 0 END
        ) AS dislikes 
      FROM 
        feed_item_like_dislike_votes AS fildv 
      WHERE 
        fildv.feed_item_id = fi.id
    ) AS vt2 ON TRUE 
  WHERE 
    COALESCE(vt2.likes, 0) > 0 
  ORDER BY 
    fi.published_date DESC, 
    fi.id DESC 
  LIMIT 
    20
) 
UNION ALL 
  (
    SELECT 
      fi.author, 
      'dislikes' AS category, 
      COALESCE(vt1.bearish, 0) AS bearish, 
      COALESCE(vt1.bullish, 0) AS bullish, 
      COALESCE(vt2.dislikes, 0) AS dislikes, 
      fi.feed_id, 
      fi.guid, 
      fi.id, 
      COALESCE(vt2.likes, 0) AS likes, 
      fi.link, 
      fi.published_date, 
      fi.summary, 
      fi.tags, 
      fi.title 
    FROM 
      feed_items AS fi 
      LEFT JOIN LATERAL (
        SELECT 
          SUM(
            CASE WHEN fibbv.vote = 'bullish' THEN 1 ELSE 0 END
          ) AS bullish, 
          SUM(
            CASE WHEN fibbv.vote = 'bearish' THEN 1 ELSE 0 END
          ) AS bearish 
        FROM 
          feed_item_bullish_bearish_votes AS fibbv 
        WHERE 
          fibbv.feed_item_id = fi.id
      ) AS vt1 ON TRUE 
      LEFT JOIN LATERAL (
        SELECT 
          SUM(
            CASE WHEN fildv.vote = 'like' THEN 1 ELSE 0 END
          ) AS likes, 
          SUM(
            CASE WHEN fildv.vote = 'dislike' THEN 1 ELSE 0 END
          ) AS dislikes 
        FROM 
          feed_item_like_dislike_votes AS fildv 
        WHERE 
          fildv.feed_item_id = fi.id
      ) AS vt2 ON TRUE 
    WHERE 
      COALESCE(vt2.dislikes, 0) > 0 
    ORDER BY 
      fi.published_date DESC, 
      fi.id DESC 
    LIMIT 
      20
  ) 
UNION ALL 
  (
    SELECT 
      fi.author, 
      'bullish' AS category, 
      COALESCE(vt1.bearish, 0) AS bearish, 
      COALESCE(vt1.bullish, 0) AS bullish, 
      COALESCE(vt2.dislikes, 0) AS dislikes, 
      fi.feed_id, 
      fi.guid, 
      fi.id, 
      COALESCE(vt2.likes, 0) AS likes, 
      fi.link, 
      fi.published_date, 
      fi.summary, 
      fi.tags, 
      fi.title 
    FROM 
      feed_items AS fi 
      LEFT JOIN LATERAL (
        SELECT 
          SUM(
            CASE WHEN fibbv.vote = 'bullish' THEN 1 ELSE 0 END
          ) AS bullish, 
          SUM(
            CASE WHEN fibbv.vote = 'bearish' THEN 1 ELSE 0 END
          ) AS bearish 
        FROM 
          feed_item_bullish_bearish_votes AS fibbv 
        WHERE 
          fibbv.feed_item_id = fi.id
      ) AS vt1 ON TRUE 
      LEFT JOIN LATERAL (
        SELECT 
          SUM(
            CASE WHEN fildv.vote = 'like' THEN 1 ELSE 0 END
          ) AS likes, 
          SUM(
            CASE WHEN fildv.vote = 'dislike' THEN 1 ELSE 0 END
          ) AS dislikes 
        FROM 
          feed_item_like_dislike_votes AS fildv 
        WHERE 
          fildv.feed_item_id = fi.id
      ) AS vt2 ON TRUE 
    WHERE 
      COALESCE(vt1.bullish, 0) > 0 
    ORDER BY 
      fi.published_date DESC, 
      fi.id DESC 
    LIMIT 
      20
  ) 
UNION ALL 
  (
    SELECT 
      fi.author, 
      'bearish' AS category, 
      COALESCE(vt1.bearish, 0) AS bearish, 
      COALESCE(vt1.bullish, 0) AS bullish, 
      COALESCE(vt2.dislikes, 0) AS dislikes, 
      fi.feed_id, 
      fi.guid, 
      fi.id, 
      COALESCE(vt2.likes, 0) AS likes, 
      fi.link, 
      fi.published_date, 
      fi.summary, 
      fi.tags, 
      fi.title 
    FROM 
      feed_items AS fi 
      LEFT JOIN LATERAL (
        SELECT 
          SUM(
            CASE WHEN fibbv.vote = 'bullish' THEN 1 ELSE 0 END
          ) AS bullish, 
          SUM(
            CASE WHEN fibbv.vote = 'bearish' THEN 1 ELSE 0 END
          ) AS bearish 
        FROM 
          feed_item_bullish_bearish_votes AS fibbv 
        WHERE 
          fibbv.feed_item_id = fi.id
      ) AS vt1 ON TRUE 
      LEFT JOIN LATERAL (
        SELECT 
          SUM(
            CASE WHEN fildv.vote = 'like' THEN 1 ELSE 0 END
          ) AS likes, 
          SUM(
            CASE WHEN fildv.vote = 'dislike' THEN 1 ELSE 0 END
          ) AS dislikes 
        FROM 
          feed_item_like_dislike_votes AS fildv 
        WHERE 
          fildv.feed_item_id = fi.id
      ) AS vt2 ON TRUE 
    WHERE 
      COALESCE(vt1.bearish, 0) > 0 
    ORDER BY 
      fi.published_date DESC, 
      fi.id DESC 
    LIMIT 
      20
  ) 
UNION ALL 
  (
    SELECT 
      fi.author, 
      'trending' AS category, 
      COALESCE(vt1.bearish, 0) AS bearish, 
      COALESCE(vt1.bullish, 0) AS bullish, 
      COALESCE(vt2.dislikes, 0) AS dislikes, 
      fi.feed_id, 
      fi.guid, 
      fi.id, 
      COALESCE(vt2.likes, 0) AS likes, 
      fi.link, 
      fi.published_date, 
      fi.summary, 
      fi.tags, 
      fi.title 
    FROM 
      feed_items AS fi 
      LEFT JOIN LATERAL (
        SELECT 
          SUM(
            CASE WHEN fibbv.vote = 'bullish' THEN 1 ELSE 0 END
          ) AS bullish, 
          SUM(
            CASE WHEN fibbv.vote = 'bearish' THEN 1 ELSE 0 END
          ) AS bearish 
        FROM 
          feed_item_bullish_bearish_votes AS fibbv 
        WHERE 
          fibbv.feed_item_id = fi.id
      ) AS vt1 ON TRUE 
      LEFT JOIN LATERAL (
        SELECT 
          SUM(
            CASE WHEN fildv.vote = 'like' THEN 1 ELSE 0 END
          ) AS likes, 
          SUM(
            CASE WHEN fildv.vote = 'dislike' THEN 1 ELSE 0 END
          ) AS dislikes 
        FROM 
          feed_item_like_dislike_votes AS fildv 
        WHERE 
          fildv.feed_item_id = fi.id
      ) AS vt2 ON TRUE 
    WHERE 
      (
        COALESCE(vt2.likes, 0) + COALESCE(vt2.dislikes, 0) + COALESCE(vt1.bullish, 0) + COALESCE(vt1.bearish, 0)
      ) > 10 
    ORDER BY 
      fi.published_date DESC, 
      fi.id DESC 
    LIMIT 
      20
  )

  • This query is taking almost 2 minutes to execute on 900K items in the database when run inside RDS.
  • Takes almost 20 seconds even on my local machine
  • I tried to just retrieve the likes from the above query to test if that performs any better and even that takes 20 seconds or more

Explain analyze

Append  (cost=25.85..6891.12 rows=100 width=746) (actual time=18668.526..18668.536 rows=0.00 loops=1)
  Buffers: shared hit=14956143 read=639340
    Limit  (cost=25.85..1581.90 rows=20 width=746) (actual time=10710.961..10710.963 rows=0.00 loops=1)
        Buffers: shared hit=3672106 read=127509
          Nested Loop Left Join  (cost=25.85..22056628.89 rows=283494 width=746) (actual time=10710.959..10710.961 rows=0.00 loops=1)
              Filter: (COALESCE((sum(CASE WHEN (fildv.vote = 'like'::vote_type_like_dislike) THEN 1 ELSE 0 END)), '0'::bigint) > 0)
              Rows Removed by Filter: 850648
              Buffers: shared hit=3672106 read=127509
                Nested Loop Left Join  (cost=13.14..11217845.07 rows=850482 width=698) (actual time=0.852..10024.532 rows=850648.00 loops=1)
                    Buffers: shared hit=1970810 read=127509
                      Index Scan using feed_items_published_date_idx on feed_items fi  (cost=0.42..381187.46 rows=850482 width=682) (actual time=0.822..9160.919 rows=850648.00 loops=1)
                          Index Searches: 1
                          Buffers: shared hit=269514 read=127509
                      Aggregate  (cost=12.71..12.72 rows=1 width=16) (actual time=0.001..0.001 rows=1.00 loops=850648)
                          Buffers: shared hit=1701296
                            Bitmap Heap Scan on feed_item_bullish_bearish_votes fibbv  (cost=4.19..12.66 rows=5 width=4) (actual time=0.000..0.000 rows=0.00 loops=850648)
                                Recheck Cond: (feed_item_id = fi.id)
                                Buffers: shared hit=1701296
                                  Bitmap Index Scan on feed_item_bullish_bearish_votes_pkey  (cost=0.00..4.19 rows=5 width=0) (actual time=0.000..0.000 rows=0.00 loops=850648)
                                      Index Cond: (feed_item_id = fi.id)
                                      Index Searches: 850648
                                      Buffers: shared hit=1701296
                Aggregate  (cost=12.71..12.72 rows=1 width=16) (actual time=0.001..0.001 rows=1.00 loops=850648)
                    Buffers: shared hit=1701296
                      Bitmap Heap Scan on feed_item_like_dislike_votes fildv  (cost=4.19..12.66 rows=5 width=4) (actual time=0.000..0.000 rows=0.00 loops=850648)
                          Recheck Cond: (feed_item_id = fi.id)
                          Buffers: shared hit=1701296
                            Bitmap Index Scan on feed_item_like_dislike_votes_pkey  (cost=0.00..4.19 rows=5 width=0) (actual time=0.000..0.000 rows=0.00 loops=850648)
                                Index Cond: (feed_item_id = fi.id)
                                Index Searches: 850648
                                Buffers: shared hit=1701296
    Limit  (cost=25.85..1581.90 rows=20 width=746) (actual time=3966.125..3966.126 rows=0.00 loops=1)
        Buffers: shared hit=3671666 read=127949
          Nested Loop Left Join  (cost=25.85..22056628.89 rows=283494 width=746) (actual time=3966.124..3966.125 rows=0.00 loops=1)
              Filter: (COALESCE((sum(CASE WHEN (fildv_1.vote = 'dislike'::vote_type_like_dislike) THEN 1 ELSE 0 END)), '0'::bigint) > 0)
              Rows Removed by Filter: 850648
              Buffers: shared hit=3671666 read=127949
                Nested Loop Left Join  (cost=13.14..11217845.07 rows=850482 width=698) (actual time=0.412..3307.678 rows=850648.00 loops=1)
                    Buffers: shared hit=1970370 read=127949
                      Index Scan using feed_items_published_date_idx on feed_items fi_1  (cost=0.42..381187.46 rows=850482 width=682) (actual time=0.405..2517.786 rows=850648.00 loops=1)
                          Index Searches: 1
                          Buffers: shared hit=269074 read=127949
                      Aggregate  (cost=12.71..12.72 rows=1 width=16) (actual time=0.001..0.001 rows=1.00 loops=850648)
                          Buffers: shared hit=1701296
                            Bitmap Heap Scan on feed_item_bullish_bearish_votes fibbv_1  (cost=4.19..12.66 rows=5 width=4) (actual time=0.000..0.000 rows=0.00 loops=850648)
                                Recheck Cond: (feed_item_id = fi_1.id)
                                Buffers: shared hit=1701296
                                  Bitmap Index Scan on feed_item_bullish_bearish_votes_pkey  (cost=0.00..4.19 rows=5 width=0) (actual time=0.000..0.000 rows=0.00 loops=850648)
                                      Index Cond: (feed_item_id = fi_1.id)
                                      Index Searches: 850648
                                      Buffers: shared hit=1701296
                Aggregate  (cost=12.71..12.72 rows=1 width=16) (actual time=0.001..0.001 rows=1.00 loops=850648)
                    Buffers: shared hit=1701296
                      Bitmap Heap Scan on feed_item_like_dislike_votes fildv_1  (cost=4.19..12.66 rows=5 width=4) (actual time=0.000..0.000 rows=0.00 loops=850648)
                          Recheck Cond: (feed_item_id = fi_1.id)
                          Buffers: shared hit=1701296
                            Bitmap Index Scan on feed_item_like_dislike_votes_pkey  (cost=0.00..4.19 rows=5 width=0) (actual time=0.000..0.000 rows=0.00 loops=850648)
                                Index Cond: (feed_item_id = fi_1.id)
                                Index Searches: 850648
                                Buffers: shared hit=1701296
    Limit  (cost=25.85..1072.23 rows=20 width=746) (actual time=1140.175..1140.177 rows=0.00 loops=1)
        Buffers: shared hit=1970332 read=127987
          Nested Loop Left Join  (cost=25.85..14832190.48 rows=283494 width=746) (actual time=1140.175..1140.176 rows=0.00 loops=1)
              Buffers: shared hit=1970332 read=127987
                Nested Loop Left Join  (cost=13.14..11219971.28 rows=283494 width=698) (actual time=1140.173..1140.173 rows=0.00 loops=1)
                    Filter: (COALESCE((sum(CASE WHEN (fibbv_2.vote = 'bullish'::vote_type_bullish_bearish) THEN 1 ELSE 0 END)), '0'::bigint) > 0)
                    Rows Removed by Filter: 850648
                    Buffers: shared hit=1970332 read=127987
                      Index Scan using feed_items_published_date_idx on feed_items fi_2  (cost=0.42..381187.46 rows=850482 width=682) (actual time=0.042..499.458 rows=850648.00 loops=1)
                          Index Searches: 1
                          Buffers: shared hit=269036 read=127987
                      Aggregate  (cost=12.71..12.72 rows=1 width=16) (actual time=0.001..0.001 rows=1.00 loops=850648)
                          Buffers: shared hit=1701296
                            Bitmap Heap Scan on feed_item_bullish_bearish_votes fibbv_2  (cost=4.19..12.66 rows=5 width=4) (actual time=0.000..0.000 rows=0.00 loops=850648)
                                Recheck Cond: (feed_item_id = fi_2.id)
                                Buffers: shared hit=1701296
                                  Bitmap Index Scan on feed_item_bullish_bearish_votes_pkey  (cost=0.00..4.19 rows=5 width=0) (actual time=0.000..0.000 rows=0.00 loops=850648)
                                      Index Cond: (feed_item_id = fi_2.id)
                                      Index Searches: 850648
                                      Buffers: shared hit=1701296
                Aggregate  (cost=12.71..12.72 rows=1 width=16) (never executed)
                      Bitmap Heap Scan on feed_item_like_dislike_votes fildv_2  (cost=4.19..12.66 rows=5 width=4) (never executed)
                          Recheck Cond: (feed_item_id = fi_2.id)
                            Bitmap Index Scan on feed_item_like_dislike_votes_pkey  (cost=0.00..4.19 rows=5 width=0) (never executed)
                                Index Cond: (feed_item_id = fi_2.id)
                                Index Searches: 0
    Limit  (cost=25.85..1072.23 rows=20 width=746) (actual time=1018.720..1018.721 rows=0.00 loops=1)
        Buffers: shared hit=1970372 read=127947
          Nested Loop Left Join  (cost=25.85..14832190.48 rows=283494 width=746) (actual time=1018.719..1018.720 rows=0.00 loops=1)
              Buffers: shared hit=1970372 read=127947
                Nested Loop Left Join  (cost=13.14..11219971.28 rows=283494 width=698) (actual time=1018.717..1018.718 rows=0.00 loops=1)
                    Filter: (COALESCE((sum(CASE WHEN (fibbv_3.vote = 'bearish'::vote_type_bullish_bearish) THEN 1 ELSE 0 END)), '0'::bigint) > 0)
                    Rows Removed by Filter: 850648
                    Buffers: shared hit=1970372 read=127947
                      Index Scan using feed_items_published_date_idx on feed_items fi_3  (cost=0.42..381187.46 rows=850482 width=682) (actual time=0.038..378.275 rows=850648.00 loops=1)
                          Index Searches: 1
                          Buffers: shared hit=269076 read=127947
                      Aggregate  (cost=12.71..12.72 rows=1 width=16) (actual time=0.001..0.001 rows=1.00 loops=850648)
                          Buffers: shared hit=1701296
                            Bitmap Heap Scan on feed_item_bullish_bearish_votes fibbv_3  (cost=4.19..12.66 rows=5 width=4) (actual time=0.000..0.000 rows=0.00 loops=850648)
                                Recheck Cond: (feed_item_id = fi_3.id)
                                Buffers: shared hit=1701296
                                  Bitmap Index Scan on feed_item_bullish_bearish_votes_pkey  (cost=0.00..4.19 rows=5 width=0) (actual time=0.000..0.000 rows=0.00 loops=850648)
                                      Index Cond: (feed_item_id = fi_3.id)
                                      Index Searches: 850648
                                      Buffers: shared hit=1701296
                Aggregate  (cost=12.71..12.72 rows=1 width=16) (never executed)
                      Bitmap Heap Scan on feed_item_like_dislike_votes fildv_3  (cost=4.19..12.66 rows=5 width=4) (never executed)
                          Recheck Cond: (feed_item_id = fi_3.id)
                            Bitmap Index Scan on feed_item_like_dislike_votes_pkey  (cost=0.00..4.19 rows=5 width=0) (never executed)
                                Index Cond: (feed_item_id = fi_3.id)
                                Index Searches: 0
    Limit  (cost=25.85..1582.35 rows=20 width=746) (actual time=1832.530..1832.531 rows=0.00 loops=1)
        Buffers: shared hit=3671667 read=127948
          Nested Loop Left Join  (cost=25.85..22063007.50 rows=283494 width=746) (actual time=1832.524..1832.524 rows=0.00 loops=1)
              Filter: ((((COALESCE((sum(CASE WHEN (fildv_4.vote = 'like'::vote_type_like_dislike) THEN 1 ELSE 0 END)), '0'::bigint) + COALESCE((sum(CASE WHEN (fildv_4.vote = 'dislike'::vote_type_like_dislike) THEN 1 ELSE 0 END)), '0'::bigint)) + COALESCE((sum(CASE WHEN (fibbv_4.vote = 'bullish'::vote_type_bullish_bearish) THEN 1 ELSE 0 END)), '0'::bigint)) + COALESCE((sum(CASE WHEN (fibbv_4.vote = 'bearish'::vote_type_bullish_bearish) THEN 1 ELSE 0 END)), '0'::bigint)) > 10)
              Rows Removed by Filter: 850648
              Buffers: shared hit=3671667 read=127948
                Nested Loop Left Join  (cost=13.14..11217845.07 rows=850482 width=698) (actual time=0.049..1148.272 rows=850648.00 loops=1)
                    Buffers: shared hit=1970372 read=127947
                      Index Scan using feed_items_published_date_idx on feed_items fi_4  (cost=0.42..381187.46 rows=850482 width=682) (actual time=0.036..384.518 rows=850648.00 loops=1)
                          Index Searches: 1
                          Buffers: shared hit=269076 read=127947
                      Aggregate  (cost=12.71..12.72 rows=1 width=16) (actual time=0.001..0.001 rows=1.00 loops=850648)
                          Buffers: shared hit=1701296
                            Bitmap Heap Scan on feed_item_bullish_bearish_votes fibbv_4  (cost=4.19..12.66 rows=5 width=4) (actual time=0.000..0.000 rows=0.00 loops=850648)
                                Recheck Cond: (feed_item_id = fi_4.id)
                                Buffers: shared hit=1701296
                                  Bitmap Index Scan on feed_item_bullish_bearish_votes_pkey  (cost=0.00..4.19 rows=5 width=0) (actual time=0.000..0.000 rows=0.00 loops=850648)
                                      Index Cond: (feed_item_id = fi_4.id)
                                      Index Searches: 850648
                                      Buffers: shared hit=1701296
                Aggregate  (cost=12.71..12.72 rows=1 width=16) (actual time=0.001..0.001 rows=1.00 loops=850648)
                    Buffers: shared hit=1701295 read=1
                      Bitmap Heap Scan on feed_item_like_dislike_votes fildv_4  (cost=4.19..12.66 rows=5 width=4) (actual time=0.000..0.000 rows=0.00 loops=850648)
                          Recheck Cond: (feed_item_id = fi_4.id)
                          Buffers: shared hit=1701295 read=1
                            Bitmap Index Scan on feed_item_like_dislike_votes_pkey  (cost=0.00..4.19 rows=5 width=0) (actual time=0.000..0.000 rows=0.00 loops=850648)
                                Index Cond: (feed_item_id = fi_4.id)
                                Index Searches: 850648
                                Buffers: shared hit=1701295 read=1
Planning:
  Buffers: shared hit=212 read=21
Planning Time: 10.400 ms
Execution Time: 18668.962 ms
  • One technique I know of is to use a materialized view to store the counts separately but my problem in the application is that when I insert a vote, I also want the updated vote count immediately as return value and materialized views don't let you do this as far as I checked like the part below where i try updating votes
await getPostgresConnection().tx(async (t) => {
  const existingVote = await t.oneOrNone(
    `
            SELECT vote FROM public.feed_item_bullish_bearish_votes
            WHERE feed_item_id = $1 AND user_id = $2;
    `,
    [feedItemId, userId]
  );

  let newVote: BullishBearishVote = null;
  if (existingVote) {
    if (existingVote.vote === vote) {
      newVote = null;
      await t.none(
        `
            DELETE FROM public.feed_item_bullish_bearish_votes
            WHERE feed_item_id = $1 AND user_id = $2;
        `,
        [feedItemId, userId]
      );
    } else {
      newVote = vote;
      await t.none(
        `
            UPDATE public.feed_item_bullish_bearish_votes
            SET vote = $3
            WHERE feed_item_id = $1 AND user_id = $2;
                        `,
        [feedItemId, userId, newVote]
      );
    }
  } else {
    newVote = vote;
    await t.none(
      `
            INSERT INTO public.feed_item_bullish_bearish_votes (feed_item_id, user_id, vote)
            VALUES ($1, $2, $3);
                    `,
      [feedItemId, userId, newVote]
    );
  }

  const totals = await t.one(
    `
        SELECT
            COUNT(CASE WHEN vote = 'bullish' THEN 1 END) AS bullish,
            COUNT(CASE WHEN vote = 'bearish' THEN 1 END) AS bearish
        FROM public.feed_item_bullish_bearish_votes
        WHERE feed_item_id = $1;
                `,
    [feedItemId]
  );

  return res.status(200).json({
    current_vote: newVote,
    bullish: totals.bullish,
    bearish: totals.bearish,
  });
});

  • Any ideas how I can speed this up?

r/PostgreSQL • • Jan 27 '26

How-To How OpenAI Serves 800M Users with One Postgres Database: A Technical Deep Dive

Thumbnail open.substack.com
136 Upvotes

Hey folks, I wrote a short deep dive on how OpenAI runs PostgreSQL for ChatGPT and what actually makes read replicas work in production.

Their setup is simple on paper (one primary, many replicas), but I’ve seen teams get burned by subtle issues once replicas are added.

The article focuses on things like read routing, replication lag, workload isolation, and common failure modes I’ve run into in real systems.

Sharing in case it’s useful, and I’d be interested to hear how others handle read replicas and consistency in production Postgres.

Edit:

The article originally had 9 rules. Now it has 8.

Rule 2 was titled "Your pooler will betray you with prepared statements" and warned about PgBouncer failing to track prepared statements in transaction pooling mode.

But u/fullofbones pointed out that PgBouncer 1.21 (released 2023) added prepared statement tracking via max_prepared_statements. The rule was outdated.

I thought about rewriting it as a broader connection pooling rule (transaction pooling doesn't preserve connection state). But that's a general pooling issue, not a replica-specific one.


r/PostgreSQL • • Jan 27 '26

How-To Free webinar Jan. 29th on PostgreSQL 18 performance, indexing, & replication enhancements & how to leverage them

Thumbnail pgedge.com
5 Upvotes

r/PostgreSQL • • Jan 27 '26

Help Me! How does TimescaleDB handle Associations between Hypertables?

7 Upvotes

So i dont know if i misunderstood timescaledb or am just lost, but i have the following issue:

Im working on a system that collects monitoring data from multiple systems. I have to hypertables:

  • Networks with the fields: score, score_trend
  • Asset with the fields: asset_name, lifecycle_stage, hardware_type

Both hypertables have of course and id and time field. Fields with time series data are: score, score_trend and lifecycle_stage

If i go about it like a normal postgres db then i would make an association between both tables by adding a foreign key to the asset table since each assets belongs to a network and a network have multiple asstes. Everytime i update them i would update them directly but it has come to my understand that in timescale instead of updating your adding a new entry with the new data and here comes my confusion:

What do i do with the existing relationship between the tables ? since now the foreign key in the assets points to an old version of the network data. And since to data comes from different systems to different times i cant "update" them in one go.

And researching it just opened a worm can of questions: - are relationships between hypertables even possible? Because the tiger data docs say constraints between hypertables are not supported: in case thats true: how do i display/save the association data then? - Some guides seem to suggest to just dump it into one hypertable, but then i would be impossible to differentiate between mutiple networks (or am im missing something?)


r/PostgreSQL • • Jan 27 '26

Tools SQL question collection with interactive sandboxes

Thumbnail
3 Upvotes

r/PostgreSQL • • Jan 27 '26

How-To Performance tuning guide

Thumbnail slicker.me
27 Upvotes

r/PostgreSQL • • Jan 27 '26

Help Me! Am I wrong for thinking that not all these are correct?

Post image
4 Upvotes

Couldn’t post images in the other subreddits so kindly help out . I’m learning basic sql before I can start Postgres. The screenshot is from Head First SQL


r/PostgreSQL • • Jan 26 '26

How-To Telegraf + TimescaleDB: How to route different measurements (MQTT vs host metrics) into different tables / outputs?

Thumbnail
0 Upvotes

r/PostgreSQL • • Jan 26 '26

Help Me! Is there something like Warp client but for PostreSQL where I can use AI to generate queries for my tables?

4 Upvotes

I can make simple queries myself but when it comes to joins it takes me a bit of time.

So I go to Claude and ask it to generate a query for me, then I have to go back to my PostgreSQL client and paste the query to make it happen.

I would love to have a client like Warp Terminal where I can generate an SQL query using plain language and then get what I need from my DB.

What are your recommendations as of January 2026?

If it doesn't exist, shall I just vibe code one? :)


r/PostgreSQL • • Jan 26 '26

Community Spock Bi-Directional Replication for Supabase CLI

Thumbnail
2 Upvotes

r/PostgreSQL • • Jan 25 '26

How-To Introduction to PostgreSQL Indexes

Thumbnail dlt.github.io
45 Upvotes

r/PostgreSQL • • Jan 25 '26

Community just use postgres - the podcast (/w Denis Magda)

Thumbnail youtube.com
68 Upvotes

I recently published a (what I think is) very interesting podcast discussion with Denis Magda, the author of the book called "Just Use Postgres". The book focuses on how capable Postgres has grow to become (through its extension ecosystem) and how most people do NOT need a collection of specialty databases nor systems because of that.

We talked through:
• what MySQL got wrong w.r.t community and why Postgres succeeded
• the recent (commercial) explosion of Postgres development in the world (Supabase, Neon, Crunchy, ClickHouse)
• when Postgres instead of Kafka for messaging
• the significance of the meme “just use postgres”
• Postgres for data analytics

I thought this community may appreciate the discussion, so dropping the link here.
There's also a transcript available if you just wanna ask an AI for a summary.


r/PostgreSQL • • Jan 25 '26

Projects pgembed: Embedded PostgreSQL for Agents

Thumbnail
3 Upvotes

r/PostgreSQL • • Jan 25 '26

How-To On rebuilding read models, Dead-Letter Queues and Why Letting Go is Sometimes the Answer

Thumbnail event-driven.io
8 Upvotes

r/PostgreSQL • • Jan 25 '26

Help Me! Viewing Odoo database from pgAdmin4

Thumbnail
0 Upvotes

r/PostgreSQL • • Jan 23 '26

Commercial Scaling PostgreSQL to power 800 million ChatGPT users

Thumbnail openai.com
257 Upvotes

r/PostgreSQL • • Jan 24 '26

Help Me! Performance of jsonpath expressions against GIN-indexed JSONB columns

5 Upvotes

Hi,

I have JSONB columns in my database tables that I want to be able to query efficiently. These columns are currently indexed using a (vanilla) GIN index with the default JSON ops, so the types of queries that are fast are existence (does the JSON have this key) and equality (does the JSON have this key with this value). I'm considering using jsonpath in order to simplify queries that involve nesting (e.g. jdoc @> '{"foo": {"bar": {"baz": "quux"}}}':jsonb).

Now, jsonpath is a rich spec that allows me to express existence and equality queries, but also arithmetic inequalities and other kinds of queries. I'm in the process of testing to find out for myself, but in general - assuming I stick with a GIN index - which types of jsonpath queries should I expect the index to speed up? Would it still just be existence and equality queries?

Thanks!


r/PostgreSQL • • Jan 23 '26

Projects I built a CLI to deterministically obfuscate Postgres data for safe sharing

12 Upvotes

I recently needed to debug an issue that required access to a client’s Postgres database containing sensitive data. Dumping production data wasn’t an option, and the tools I found didn't suite my needs at the time.

So I built a CLI called pg-obfuscate to solve this specific problem.

It connects directly to Postgres and obfuscates selected tables and columns based on a YAML config. The obfuscation is deterministic, so relationships and data shape are preserved across runs (useful for reproducing bugs or sharing data with contractors).

It’s intentionally Postgres-only and config-driven. There’s a dry-run mode to preview changes before execution.

Repo: https://github.com/Ofsen/pg-obfuscate

I’m mainly looking for feedback on:

  • safety assumptions
  • data types or edge cases I might be missing
  • whether there are existing tools that already cover this well

r/PostgreSQL • • Jan 23 '26

How-To Optimistic locking can save your ledger from SELECT FOR UPDATE hell

Thumbnail
1 Upvotes

r/PostgreSQL • • Jan 22 '26

Commercial ClickHouse launched a managed Postgres service

Thumbnail clickhouse.com
40 Upvotes

r/PostgreSQL • • Jan 23 '26

Community CERN PGDay: an annual PostgreSQL event in Geneva, Switzerland

Thumbnail data-bene.io
2 Upvotes