MIME-Version: 1.0
References: 
 <CANbgw4BfmAUsMTsh0z2Y-97jusZObaC+Ez7Mm=papd-WUDd89A@mail.gmail.com>
In-Reply-To: 
 <CANbgw4BfmAUsMTsh0z2Y-97jusZObaC+Ez7Mm=papd-WUDd89A@mail.gmail.com>
From: Steve Midgley <science@misuse.org>
Date: Tue, 15 Feb 2022 08:20:07 -0800
Message-ID: 
 <CAJexoSL-RADZAFFtW6NgGNg+SiomNwvcApWpogwuW3EsdGHJXA@mail.gmail.com>
Subject: Re: Storing thousands of csv files in postgresql
To: Ion Alberdi <ion.alberdi@pricemoov.com>
Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000a3dd6505d810e8e6"
Archived-At: 
 <https://www.postgresql.org/message-id/CAJexoSL-RADZAFFtW6NgGNg%2BSiomNwvcApWpogwuW3EsdGHJXA%40mail.gmail.com>
Precedence: bulk

--000000000000a3dd6505d810e8e6
Content-Type: text/plain; charset="UTF-8"

On Tue, Feb 15, 2022 at 12:15 AM Ion Alberdi <ion.alberdi@pricemoov.com>
wrote:

> Hello to all,
>
> One of the use cases we need to implement requires
> storing and query-ing thousands (and more as the product grows) of csv
> files
> that have different schema-s (by schema we mean column names and their
> type).
>
> These csv would then need to be maintained with operations like:
> - add column,
> - add row,
> - delete row,
> - read: filter/sort/paginate,
> - write: edit column values.
>
> Let's assume that we store the definition of each schema in a dedicated
> table,
> with the schema defined in a json column. With this schema we'll be able
> translate the read/write/update queries to these imported csv files into
> related SQL queries.
>
> The remaining question is how to store the data of each file in the DB.
>
> As suggested by https://www.postgresql.org/docs/10/sql-copy.html there is
> a way to import a csv in its own table. By using this approach for each
> csv-s we see:
>
> Pros:
> - All postgresql types available:
> https://www.postgresql.org/docs/9.5/datatype.html,
> - Constraints on columns, among others unicity constraints,
>   that makes the DB guarantee rows will not duplicated (relevant to the
> add row use case),
> - Debuggability: enables using standard SQL to browse csv data,
> - Can reuse existing libraries to generate dynamic SQL queries [1]
>
> Cons:
> - Need to have as many tables as different schemas.
>
> Another solution could consist of implementing a document store in
> postgresql,
> by storing all columns of a row in a single jsonb column.
>
> Pros:
> - Single table to store all different imported csv-s.
>
> Cons:
> - Less types available
> https://www.postgresql.org/docs/9.4/datatype-json.html,
> - No constraint on columns, (no unicity or data validation constraints
> that should be delegated to the application),
> - Ramp-up on json* functions, (and I wonder whether there are libraries
> to safely generate dynamic SQL queries on json columns),
> (- Debuggability: this is not such a big con as json_to_record enables
> going back to a standard SQL experience)
>
> Based on this first pro/con list, we're wondering about the scalability
> limits faced by postgresql instances getting more tables in a given DB.
>
> Browsing the web, we saw two main issues:
> - One related to the OS "you may see some performance degradation
> associated
>   with databases containing many tables. PostgreSQL may use a large number
> of
>   files for storing the table data, and performance may suffer if the
> operating
>   system does not cope well with many files in a single directory." [1]
> - Related to that, the fact that some operations like autovacuum are O(N)
> on number of tables [3]
>
> On the other hand, reading timescaledb's architecture
> https://docs.timescale.com/timescaledb/latest/overview/core-concepts/hypertables-and-chunks/#partitioning-in-hypertables-with-chunks
> "Each chunk is implemented using a standard database table."
> it seems that their platform took such a direction, which may have proved
> the scalability of such an approach.
>
> My question is thus the following:
> how many of such tables can a single postgresql instance handle without
> trouble [4]?
>
> Any challenge/addition to the pro/cons list described above would be very
> welcome too.
>
>
Given that no matter what answer the community can give you about the
number of tables per DB is reasonable, if your project is successful,
you'll probably exceed that limit eventually. Why not plan for federation
at the start (a little, at low cost) by including the PG server URL and DB
name where the CSV is stored in your CSV table schema store? That way, for
now, you just store CSVs in the current PG server/DB, and should it get
overwhelmed, it's relatively easy to just point accessors to a different
server and/or DB in the future for some CSV resources? The main upfront
increased cost is that you'll need to solve for credential management for
the various PG servers. If you're in AWS, "Secrets Manager" would work -
but there are lots of equivalent solutions out there.

FWIW, I think your analysis of the pros and cons of tables vs documents is
excellent but slightly incomplete. In my experience with document DB, I
only postpone all the downsides, in order to get the immediate benefits
(kind of like a "sugar high"). Eventually you have to solve for everything
you solve for with the table-based solution. You just don't have to solve
for it upfront, like in the table approach. And, at least on the project
where I got bit by a document db architecture, it's a lot harder to solve
for many of these problems when you solve for them later in your project,
so it's better just to build using structured tables up front for a project
with meaningful structures and lots of data.

Steve

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">On Tue, Feb 15, 2022 at 12:15 AM Ion =
Alberdi &lt;<a href=3D"mailto:ion.alberdi@pricemoov.com">ion.alberdi@pricem=
oov.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"=
margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-lef=
t:1ex"><div dir=3D"ltr">Hello to all,<br><br>One of the use cases we need t=
o implement requires<br>storing and query-ing thousands (and more as the pr=
oduct grows) of csv files<br>that have different schema-s (by schema we mea=
n column names and their type).<br><br>These csv would then need to be main=
tained with operations like:<br>- add column,<br>- add row,<br>- delete row=
,<br>- read: filter/sort/paginate,<br>- write: edit column values.<br><br>L=
et&#39;s assume that we store the definition of each schema in a dedicated =
table,<br>with the schema defined in a json column. With this schema we&#39=
;ll be able translate the read/write/update queries to these imported csv f=
iles into related SQL queries.<br><br>The remaining question is how to stor=
e the data of each file in the DB.<br><br>As suggested by <a href=3D"https:=
//www.postgresql.org/docs/10/sql-copy.html" target=3D"_blank">https://www.p=
ostgresql.org/docs/10/sql-copy.html</a> there is a way to import a csv in i=
ts own table. By using this approach for each csv-s we see:<br><br>Pros:<br=
>- All postgresql types available: <a href=3D"https://www.postgresql.org/do=
cs/9.5/datatype.html" target=3D"_blank">https://www.postgresql.org/docs/9.5=
/datatype.html</a>,<br>- Constraints on columns, among others unicity const=
raints,<br>=C2=A0 that makes the DB guarantee rows will not duplicated (rel=
evant to the add row use case),<br>- Debuggability: enables using standard =
SQL to browse csv data,<br>- Can reuse existing libraries to generate dynam=
ic SQL queries [1]<br><br>Cons:<br>- Need to have as many tables as differe=
nt schemas.<br><br>Another solution could consist of implementing a documen=
t store in postgresql,<br>by storing all columns of a row in a single jsonb=
 column.<br><br>Pros:<br>- Single table to store all different imported csv=
-s.<br><br>Cons:<br>- Less types available <a href=3D"https://www.postgresq=
l.org/docs/9.4/datatype-json.html" target=3D"_blank">https://www.postgresql=
.org/docs/9.4/datatype-json.html</a>,<br>- No constraint on columns, (no un=
icity or data validation constraints<br>that should be delegated to the app=
lication),<br>- Ramp-up on json* functions, (and I wonder whether there are=
 libraries<br>to safely generate dynamic SQL queries on json columns),<br>(=
- Debuggability: this is not such a big con as json_to_record enables<br>go=
ing back to a standard SQL experience)<br><br>Based on this first pro/con l=
ist, we&#39;re wondering about the scalability<br>limits faced by postgresq=
l instances getting more tables in a given DB.<br><br>Browsing the web, we =
saw two main issues:<br>- One related to the OS &quot;you may see some perf=
ormance degradation associated<br>=C2=A0 with databases containing many tab=
les. PostgreSQL may use a large number of<br>=C2=A0 files for storing the t=
able data, and performance may suffer if the operating<br>=C2=A0 system doe=
s not cope well with many files in a single directory.&quot; [1]<br>- Relat=
ed to that, the fact that some operations like autovacuum are O(N) on numbe=
r of tables [3]<br><br>On the other hand, reading timescaledb&#39;s archite=
cture <a href=3D"https://docs.timescale.com/timescaledb/latest/overview/cor=
e-concepts/hypertables-and-chunks/#partitioning-in-hypertables-with-chunks"=
 target=3D"_blank">https://docs.timescale.com/timescaledb/latest/overview/c=
ore-concepts/hypertables-and-chunks/#partitioning-in-hypertables-with-chunk=
s</a><br>&quot;Each chunk is implemented using a standard database table.&q=
uot;<br>it seems that their platform took such a direction, which may have =
proved the scalability of such an approach.<br><br>My question is thus the =
following:<br>how many of such tables can a single postgresql instance hand=
le without trouble [4]?<br><div><br>Any challenge/addition to the pro/cons =
list described above would be very welcome too.<br><br></div></div></blockq=
uote><div>=C2=A0</div><div>Given that no matter what answer the community c=
an give you about the number of tables per DB is reasonable, if your projec=
t is successful, you&#39;ll probably exceed that limit eventually. Why not =
plan for federation at the start (a little, at low cost) by including the P=
G server URL and DB name where the CSV is stored in your CSV table schema s=
tore? That way, for now, you just store CSVs in the current PG server/DB, a=
nd should it get overwhelmed, it&#39;s relatively easy to just point access=
ors to a different server and/or DB in the future for some CSV resources? T=
he main upfront increased cost is that you&#39;ll need to solve for credent=
ial management for the various PG servers. If you&#39;re in AWS, &quot;Secr=
ets Manager&quot; would work - but there are lots of equivalent solutions o=
ut there.</div><div><br></div><div>FWIW, I think your analysis of the pros =
and cons of tables vs documents is excellent but slightly incomplete. In my=
 experience with document DB, I only postpone all the downsides, in order t=
o get the immediate benefits (kind of like a &quot;sugar high&quot;). Event=
ually you have to solve for everything you solve for with the table-based s=
olution. You just don&#39;t have to solve for it upfront, like in the table=
 approach. And, at least on the project where I got bit by a document db ar=
chitecture, it&#39;s a lot harder to solve for many of these problems when =
you solve for them later in your project, so it&#39;s better just to build =
using structured tables up front for a project with meaningful structures a=
nd lots of data.</div><div><br></div><div>Steve</div></div></div>

--000000000000a3dd6505d810e8e6--