From: Wim Bertels <wim.bertels@ucll.be>
To: mail@joeconway.com <mail@joeconway.com>
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
To: pat.trainor@gmail.com <pat.trainor@gmail.com>
Subject: Re: How To: A large [2D] matrix, 100,000+ rows/columns
Date: Fri, 9 Jun 2023 14:00:27 +0000
Message-ID: <4bb77358282da0509415de05079f118ef73f7033.camel@ucll.be> (raw)
In-Reply-To: <626833f2-50e2-342c-e8ea-cb16b0d01785@joeconway.com>
References: <CAK_m+w=zf1mLP4+N+rNDaG5xU1_MjqTUDYbf+D-RyiF4ek5mQw@mail.gmail.com>
<626833f2-50e2-342c-e8ea-cb16b0d01785@joeconway.com>
Joe Conway schreef op vr 09-06-2023 om 09:16 [-0400]:
> On 6/8/23 22:17, Pat Trainor wrote:
> > I need to have a very large matrix to maintain & query, and if not
> > (1,600 column limit), then how could such data be broken down to
> > work?
>
> 100,000 rows *
> 100,000 columns *
> 8 bytes (assuming float8)
> = about 80 GB per matrix if I got the math correct.
>
>
based on my personal experience i would not use postgres in the case
where you need many columns, u can work around this with json for
example, but it will likely end up being less easy to work with
as Joe replied: R or Python are probably a better fit,
or another database that can easily handle a lot of columns,
postgres is a great database, but not when you need a lot of columns
(as you noted+:
there might be another backend storage for postgres that can handle
this better (or in the future?), but i don't think there is one;
also there is the header for which standard 8K is provisioned anyway,
so that is the first bottleneck (you can change this value, if you
compile postgres yourself)
https://www.postgresql.org/docs/current/limits.html )
Wim
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: wim.bertels@ucll.be, mail@joeconway.com, pat.trainor@gmail.com
Subject: Re: How To: A large [2D] matrix, 100,000+ rows/columns
In-Reply-To: <4bb77358282da0509415de05079f118ef73f7033.camel@ucll.be>
References: <CAK_m+w=zf1mLP4+N+rNDaG5xU1_MjqTUDYbf+D-RyiF4ek5mQw@mail.gmail.com> <626833f2-50e2-342c-e8ea-cb16b0d01785@joeconway.com> <4bb77358282da0509415de05079f118ef73f7033.camel@ucll.be>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox