MIME-Version: 1.0
References: 
 <CY8PR05MB1010861EAD48ED098786C9690C4002@CY8PR05MB10108.namprd05.prod.outlook.com>
In-Reply-To: 
 <CY8PR05MB1010861EAD48ED098786C9690C4002@CY8PR05MB10108.namprd05.prod.outlook.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Tue, 19 May 2026 10:41:42 -0400
Message-ID: 
 <CANzqJaA4S080-9tSOLZaWnfY6QbxZc8WQHv2JB4dOveWOkZh4g@mail.gmail.com>
Subject: Re: scaling up from t1n to 60 million records
To: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000022eaca06522cae72"
Archived-At: 
 <https://www.postgresql.org/message-id/CANzqJaA4S080-9tSOLZaWnfY6QbxZc8WQHv2JB4dOveWOkZh4g%40mail.gmail.com>
Precedence: bulk

--00000000000022eaca06522cae72
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tue, May 19, 2026 at 10:27=E2=80=AFAM Martin Mueller <
martinmueller@northwestern.edu> wrote:

> I use Postgres with a GUI frontend (Aquafold) as a very large spreadsheet
> on steroids that analyzes rare or defective spellings in a corpus of 65,0=
00
> texts and1.5 billion words.  I typically extract  data from the corpus wi=
th
> python scripts, turn them into tables and load them into the database.
>
>
> On my Mac with 32 GB of memory performance is OK with queries that
> typically within seconds extract data rows from tables  with up to ten
> million rows.  If the result set is large, I suspect that most of time
> machine's time is spent displaying result sets. I have used indexing
> sparingly. While it helps, the time savings often don't matter much.
>
>
> I am thinking about scaling up to table with about 60 million rows.  Are
> there things to do or watch out for?
>

Use the correct tool for the task at hand, even if you are not a carpenter
and thus only know how to use a hammer.

Or should I proceed on the assumption that that 60 million records are
> within scope and that the added timecost is roughly linear?
>

In my experience, database performance shows a hockey stick graph: good
while stuff fits in memory, and then suddenly not so good.

The correct tool for full text search is PG's Full Text Search (ts_vector)
facility, paired with GIN indexes.  Do you use them?  Probably not, based
on your comments, but that would "keep 'everything' in memory", thus
staving off performance degradation.

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

--00000000000022eaca06522cae72
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, May 19, 2026 at 10:27=E2=80=AFAM =
Martin Mueller &lt;<a href=3D"mailto:martinmueller@northwestern.edu">martin=
mueller@northwestern.edu</a>&gt; wrote:</div><div class=3D"gmail_quote gmai=
l_quote_container"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0p=
x 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">


<div>
<p class=3D"MsoNormal" style=3D"margin:0in;font-family:Aptos,sans-serif;fon=
t-size:12pt">
<span style=3D"font-size:14pt;color:rgb(0,0,0)">I use Postgres with a GUI f=
rontend (Aquafold) as a very large spreadsheet on steroids that analyzes ra=
re or defective spellings in a corpus of 65,000 texts and1.5 billion words.=
=C2=A0 I typically extract =C2=A0data
 from the corpus with python scripts, turn them into tables and load them i=
nto the database.</span></p>
<p class=3D"MsoNormal" style=3D"margin:0in;font-family:Aptos,sans-serif;fon=
t-size:12pt">
<span style=3D"font-size:14pt;color:rgb(0,0,0)"><br>
</span></p>
<p class=3D"MsoNormal" style=3D"margin:0in;font-family:Aptos,sans-serif;fon=
t-size:12pt">
<span style=3D"font-size:14pt;color:rgb(0,0,0)">On my Mac with 32 GB of mem=
ory performance is OK with queries that typically within seconds extract da=
ta rows from tables =C2=A0with up to ten million rows.=C2=A0 If the result =
set is large, I suspect that most of time
 machine&#39;s time is spent displaying result sets. I have used indexing s=
paringly. While it helps, the time savings often don&#39;t matter much.=C2=
=A0</span></p>
<p class=3D"MsoNormal" style=3D"margin:0in;font-family:Aptos,sans-serif;fon=
t-size:12pt">
<span style=3D"font-size:14pt;color:rgb(0,0,0)"><br>
</span></p>
<p class=3D"MsoNormal" style=3D"margin:0in;font-family:Aptos,sans-serif;fon=
t-size:12pt">
<span style=3D"font-size:14pt;color:rgb(0,0,0)">I am thinking about scaling=
 up to table with about 60 million rows.=C2=A0 Are there things to do or wa=
tch out for? </span></p></div></blockquote><div>=C2=A0</div><div>Use the co=
rrect tool for the task at hand, even if you are not a carpenter and thus o=
nly know how to use a hammer.</div><div><br></div><blockquote class=3D"gmai=
l_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,20=
4,204);padding-left:1ex"><div><p class=3D"MsoNormal" style=3D"margin:0in;fo=
nt-family:Aptos,sans-serif;font-size:12pt"><span style=3D"font-size:14pt;co=
lor:rgb(0,0,0)">Or should I proceed on the assumption that that 60 million =
records are within scope and that the
 added timecost is roughly linear?</span></p></div></blockquote><div></div>=
</div><div><br></div><div>In my experience, database performance shows a ho=
ckey stick graph: good while stuff fits in memory, and then suddenly not so=
 good.</div><div><br class=3D"gmail-Apple-interchange-newline">The correct =
tool for full text search is PG&#39;s Full Text Search (ts_vector) facility=
, paired with GIN indexes.=C2=A0 Do you use them?=C2=A0 Probably not, based=
 on your comments, but that would &quot;keep &#39;everything&#39; in memory=
&quot;, thus staving off performance degradation.</div><div><br></div><span=
 class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"g=
mail_signature"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and butter sauc=
e.<div>Don&#39;t boil me, I&#39;m still alive.<br><div><div>&lt;Redacted&gt=
; lobster!</div></div></div></div></div></div>

--00000000000022eaca06522cae72--