performance expectations for table(s) with 2B recs

From: David Gauthier <davegauthierpg(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: performance expectations for table(s) with 2B recs
Date: 2021-12-08 19:44:47
Message-ID: CAMBRECAWFQ+o+Bmt4jXpGC++gM_VQBTZURNbvB=7L1OEjVvg3A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

11.5 on linux
server = VM provided by our IT dept (IOW, can be grown if needed)
DB itself is on NFS

So far, the tables I have in my DB have relatively low numbers of records
(most are < 10K, all are < 10M). Things have been running great in terms
of performance. But a project is being brainstormed which may require some
tables to contain a couple billion records.

I'm familiar with the need to properly index columns in these tables which
will be used for table joining and query performance (candidates are
columns used in query predicate, etc...). Let's assume that's done right.
And let's assume that the biggest table will have 20 varchars (<= 16 chars
per) and 20 ints. No blobs or jsonb or anything like that.

What else should I be worried about ?

I suspect that part of why things are running really well so far is that
the relatively small amounts of data in these tables ends up in the DB
cache and disk I/O is kept at a minimum. Will that no longer be the case
once queries start running on these big tables ?

What about DBA stuff... vacuum and fragmentation and index maintenance,
etc... ?

I don't want to step into this completely blind. Any warnings/insights
would be appreciated.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter J. Holzer 2021-12-08 22:14:13 Re: performance expectations for table(s) with 2B recs
Previous Message Roxanne Reid-Bennett 2021-12-08 17:59:19 Re: How to allow null as an option when using regexp_matches?