pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (5+ messages)  latest in thread

Message-ID: <4bb77358282da0509415de05079f118ef73f7033.camel@ucll.be>
Permalink:  ../4bb77358282da0509415de05079f118ef73f7033.camel@ucll.be/
Also on:    postgresql.org/message-id/4bb77358282da0509415de05079f118ef73f7033.camel@ucll.be

 · 

reply

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