How To: A large [2D] matrix, 100,000+ rows/columns

[Pat Trainor]

Experts,

A very high level question...  Subject sums it up.

I love PGSQL, but I don't know if it is a good fit for this back-end... I
hope it is.

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?

By wanting postgresql as a solution, am I round-hole, square-pegging myself?

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...

Hoping someone has tackled this before, and performance isn't too big a
concern, as the data changes seldom.

I know it's a weird one, so thanks!

:-)

Question about where to deploy the business logics for data processing

[Nim Li]

Hello.

We have a PostgreSQL database with many tables, as well as foreign table,
dblink, triggers, functions, indexes, etc, for managing the business logics
of the data within the database.  We also have a custom table for the
purpose of tracking the slowly changing dimensions (type 2).

Currently we are looking into using TypeORM (from Nest JS framework) to
connect to the database for creating a BE that provides web service.  Some
reasons of using TypeORM are that it can update the database schema without
any SQL codes, works very well with Git, etc.  And from what I am reading,
Git seems to work better with TypeORM, rather than handling individual
batch files with SQL codes (I still need to find out more about this)  Yet
I do not think the ORM concept deals with database specify functions, such
as dblink and/or trigger-function, etc, which handles the business logics
or any ETL automation within the database itself (I should read more about
this as well.)

Anyway, in our team discussion, I was told that in modern programming
concept, the world is moving away from deploying programming logics within
the database (eg, by using PL/SQL).  Instead, the proper way should be to
deploy all the programming logics to the framework which is used to connect
to the database, such as NestJS in our case.  So, all we need in a database
should be only the schema (managed by ORM), and we should move all the
existing business logics (currently managed by things like the database
triggers, functions, dblink, etc.) to the Typescript codes within the
NestJS framework.

I wonder if anyone in the community has gone through changes like this?  I
mean ... moving the business logics from PL/SQL within the database to the
codes in NestJS framework, and reply on only the TypeORM to manage the
update of the database without any SQL codes?  Any thoughts about such a
change?

Thank you!!