Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q7W68-0004XH-Dz for pgsql-general@arkaria.postgresql.org; Fri, 09 Jun 2023 06:59:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q7W65-0006ka-PK for pgsql-general@arkaria.postgresql.org; Fri, 09 Jun 2023 06:59:45 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q7W65-0006ip-Dv for pgsql-general@lists.postgresql.org; Fri, 09 Jun 2023 06:59:45 +0000 Received: from mail-ej1-x635.google.com ([2a00:1450:4864:20::635]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1q7W5y-001294-Dl for pgsql-general@lists.postgresql.org; Fri, 09 Jun 2023 06:59:44 +0000 Received: by mail-ej1-x635.google.com with SMTP id a640c23a62f3a-977e0fbd742so218870066b.2 for ; Thu, 08 Jun 2023 23:59:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1686293977; x=1688885977; h=to:references:message-id:content-transfer-encoding:cc:date :in-reply-to:from:subject:mime-version:from:to:cc:subject:date :message-id:reply-to; bh=g6UE/+LlWQsKA7dKYrxayvmv4EPKjYAwnNpVtA+GfPU=; b=NBrUN6nfhf6ENCc9Cl8sGa0L0ybeSN6HkfdVMAn9IWazYqqdxGFbZ1CM7fax1YwnFg n+fbeXD56ULyakZNUJJC+9C9hPuqJCNU+U6yR97zrCWd667Vj98AwTbt7SGvzJCvHphj g6+z3pxi4/avZWlQy6awX2EXLdrBUPiI67mO+0RkhQbdq04AVGtaqHcFMo3WkXuFLalf upuzv5qpRPFj+8HEuYgwSLvK9Xf7TRHoOEiogAw/e/6MuUi3/Qc0+ZTdgVAcLsEFDkF5 v6wB9jqaFG30uAjCmyOK9ebgL5948a7ebdYVhdnJe8w8pcOMIv7kPTle3ZdqyuITv5fD cMBg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1686293977; x=1688885977; h=to:references:message-id:content-transfer-encoding:cc:date :in-reply-to:from:subject:mime-version:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=g6UE/+LlWQsKA7dKYrxayvmv4EPKjYAwnNpVtA+GfPU=; b=awzFcCpunTEpZcQ8vFuRf1t9jbrnYYAInHtcMNv2Pzw/4uzFH5CR3CiaTYsv1Y/8H9 Zi365ZRHUaG5Qm8kj4OyQvC6w6J2wLTKVLfOILXE5uodHqRUhlnnR/s7lcKEKutIyCIL yI4kCiWeSNLPnupYaJoHaCuY6kOf9xIXamRY/Hk5T3OCW/JB7cH1V0pOnT6QNqT/Bcds H4AOToCBfDHelTzQ2Z/uqazmEbP0bmzSznHe0fuq17snjF7oRD7HpP6KlowcU6FpHxGF E0e6/G+B+0mak0QpQaYdzIMda8gHuqKzM7o9YoSSRejUKRQKLjRvQUqZAH875Uzvx1/r bpGg== X-Gm-Message-State: AC+VfDzu7f5mlVuX+140md7/RQCNrqa96qCpouoKRKForh3JAPVagMca r5a0o3iB/Uea0QLJ03vQPxI= X-Google-Smtp-Source: ACHHUZ7KIGgmaQkJgwKKaIB6xRxh/ofTHKeJ4lH3p04KDPc4IQmLAsMTDlA7XQ1gvtw7EonSyq/iig== X-Received: by 2002:a17:907:8690:b0:979:7624:1f6f with SMTP id qa16-20020a170907869000b0097976241f6fmr685948ejc.10.1686293976741; Thu, 08 Jun 2023 23:59:36 -0700 (PDT) Received: from smtpclient.apple (217-19-30-105.dsl.cambrium.nl. [217.19.30.105]) by smtp.gmail.com with ESMTPSA id a9-20020a17090680c900b0096f782f727esm900869ejx.140.2023.06.08.23.59.35 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Thu, 08 Jun 2023 23:59:36 -0700 (PDT) Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3731.500.231\)) Subject: Re: How To: A large [2D] matrix, 100,000+ rows/columns From: Alban Hertroys In-Reply-To: Date: Fri, 9 Jun 2023 08:59:25 +0200 Cc: pgsql-general@lists.postgresql.org Content-Transfer-Encoding: quoted-printable Message-Id: <3A7FE461-18C4-4109-9997-F28C3D4CD314@gmail.com> References: To: Pat Trainor X-Mailer: Apple Mail (2.3731.500.231) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 9 Jun 2023, at 04:17, Pat Trainor wrote: (=E2=80=A6) > Imagine something akin to stocks, where you have a row for every = stock, and a column for every stock. Except where the same stock is the = row & col, a number is at each X-Y (row/column), and that is the big = picture. 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? If your matrix contains values that are all of the same type, as = matrices usually do, then a matrix can be described as the Carthesian = product of rows and columns, with values connecting those. For rows and columns you could enumerate them using generate_series() or = a pair of recursive CTEs, or you could put them into their own table. For the values (or cells), a tuple of (row, column, value) would be = sufficient. Then in the end, the matrix would be a presentation of the left joins of = the Carthesian product of rows and columns with your cell values. The = left joins are to account for missing cell values (empty cells), or you = could explicitly add tuples for those with an =E2=80=98empty=E2=80=99 = value. For presentation, I would use something like Python Pandas and the = xlsxwriter. Data-entry is going to be a bit of a pain if you cannot automate it, and = it=E2=80=99s not going to be very space-efficient, but it does fit the = relational model this way and it would be easy to expand the matrix in = either direction. > By wanting postgresql as a solution, am I round-hole, square-pegging = myself? I expect that there are solutions that were explicitly designed for = handling (large) matrices and that those would perhaps perform better. > I don't mind keeping, say, a 1,500 column max per table, but then = adding new items (stocks in the above analogy) might make it difficult = to keep track of things...=20 That=E2=80=99s also a possibility, but that sort of pushes the column = lookups down to the catalog level and induces overhead on all other = catalog lookups as well. It=E2=80=99s not a huge amount though. An alternative approach would be a table of rows (or columns) with each = an array of values, especially since you don=E2=80=99t expect many = updates. That would be a first attempt at optimisation if the pure = relational model doesn=E2=80=99t work out. > Hoping someone has tackled this before, and performance isn't too big = a concern, as the data changes seldom. Not in practice, AFAIR, but I was thinking of a solution like this for = small matrices (Sudoku=E2=80=99s, I hate the things, but I need some = solutions to a few to help me with a number of test scripts). Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.