Thread

1. Storing thousands of csv files in postgresql

   Ion Alberdi <ion.alberdi@pricemoov.com> — 2022-02-15T08:15:28Z

   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.
   
   Best regards,
   Ion
   
   [1]: Like https://www.psycopg.org/docs/sql.html
   [2]: https://link.springer.com/content/pdf/bbm%3A978-1-4302-0018-5%2F1.pdf
   [3]:
   https://stackoverflow.com/questions/22395883/postgresql-what-is-the-maximum-number-of-tables-can-store-in-postgresql-databas
   [4]: We use RDS instances in AWS
   

2. Re: Storing thousands of csv files in postgresql

   Steve Midgley <science@misuse.org> — 2022-02-15T16:20:07Z

   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
   

3. Re: Storing thousands of csv files in postgresql

   Ion Alberdi <ion.alberdi@pricemoov.com> — 2022-02-15T19:38:15Z

   Thanks for these precious insights Steve!
   >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.
   Indeed!
   
   >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?
   So far I'd hope that https://www.citusdata.com/ would have features to do
   so. Reading the docs, they do not seem to provide such a federation though,
   I'll send them an email to be sure. Thanks again!
   
   Le mar. 15 févr. 2022 à 17:20, Steve Midgley <science@misuse.org> a écrit :
   
   >
   >
   > 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
   >
   

4. Re: Storing thousands of csv files in postgresql

   Erik Brandsberg <erik@heimdalldata.com> — 2022-02-15T20:11:09Z

   I'm going to challenge that the question is not one for Postgres, but you
   should be asking "What filesystem is best suited to having many files in
   the same directory" and let the technology deal with the problem.  Postgres
   is just dependent on the filesystem for this behavior.  And to that answer,
   I believe, is XFS.
   
   On Tue, Feb 15, 2022 at 3: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.
   >
   > Best regards,
   > Ion
   >
   > [1]: Like https://www.psycopg.org/docs/sql.html
   > [2]: https://link.springer.com/content/pdf/bbm%3A978-1-4302-0018-5%2F1.pdf
   > [3]:
   > https://stackoverflow.com/questions/22395883/postgresql-what-is-the-maximum-number-of-tables-can-store-in-postgresql-databas
   > [4]: We use RDS instances in AWS
   >
   

5. Re: Storing thousands of csv files in postgresql

   Marcos Pegoraro <marcos@f10.com.br> — 2022-02-15T20:27:31Z

   >
   > I'm going to challenge that the question is not one for Postgres, but you
   > should be asking "What filesystem is best suited to having many files in
   > the same directory" and let the technology deal with the problem.  Postgres
   > is just dependent on the filesystem for this behavior.  And to that answer,
   > I believe, is XFS.
   >
   > He didn't say how many different schemas will have.
   All those thousands of CSV files have a completely unknown structure ?
   Or you'll have just dozens or hundreds of structures ?
   
   Marcos
   

6. Re: Storing thousands of csv files in postgresql

   Ion Alberdi <ion.alberdi@pricemoov.com> — 2022-02-15T20:40:20Z

   >"What filesystem is best suited to having many files in the same
   directory" and let the technology deal with the problem.  Postgres is just
   dependent on the filesystem for this behavior.  And to that answer, I
   believe, is XFS.
   Given that we use AWS RDS instances, I don't think we have the option to
   choose the filesystem (at least there is no such info at
   https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/)
   Still, we'll keep that in mind thanks Erik!
   
   
   
   Le mar. 15 févr. 2022 à 21:11, Erik Brandsberg <erik@heimdalldata.com> a
   écrit :
   
   > I'm going to challenge that the question is not one for Postgres, but you
   > should be asking "What filesystem is best suited to having many files in
   > the same directory" and let the technology deal with the problem.  Postgres
   > is just dependent on the filesystem for this behavior.  And to that answer,
   > I believe, is XFS.
   >
   > On Tue, Feb 15, 2022 at 3: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.
   >>
   >> Best regards,
   >> Ion
   >>
   >> [1]: Like https://www.psycopg.org/docs/sql.html
   >> [2]:
   >> https://link.springer.com/content/pdf/bbm%3A978-1-4302-0018-5%2F1.pdf
   >> [3]:
   >> https://stackoverflow.com/questions/22395883/postgresql-what-is-the-maximum-number-of-tables-can-store-in-postgresql-databas
   >> [4]: We use RDS instances in AWS
   >>
   >
   

7. Re: Storing thousands of csv files in postgresql

   Erik Brandsberg <erik@heimdalldata.com> — 2022-02-15T20:42:58Z

   I was just about to call out in a followup you may not have control over
   the filesystem if you are in say RDS then.  In this case, another
   question--is there a core set of columns that will be used for all tables,
   and then a variable set for each?  It may make sense to use one table with
   a "table id" column, and then the common tables, then just use json storage
   for the variable columns.  More information on the nature of the data may
   help elicit a better answer however.
   
   On Tue, Feb 15, 2022 at 3:40 PM Ion Alberdi <ion.alberdi@pricemoov.com>
   wrote:
   
   > >"What filesystem is best suited to having many files in the same
   > directory" and let the technology deal with the problem.  Postgres is just
   > dependent on the filesystem for this behavior.  And to that answer, I
   > believe, is XFS.
   > Given that we use AWS RDS instances, I don't think we have the option to
   > choose the filesystem (at least there is no such info at
   > https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/)
   > Still, we'll keep that in mind thanks Erik!
   >
   >
   >
   > Le mar. 15 févr. 2022 à 21:11, Erik Brandsberg <erik@heimdalldata.com> a
   > écrit :
   >
   >> I'm going to challenge that the question is not one for Postgres, but you
   >> should be asking "What filesystem is best suited to having many files in
   >> the same directory" and let the technology deal with the problem.  Postgres
   >> is just dependent on the filesystem for this behavior.  And to that answer,
   >> I believe, is XFS.
   >>
   >> On Tue, Feb 15, 2022 at 3: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.
   >>>
   >>> Best regards,
   >>> Ion
   >>>
   >>> [1]: Like https://www.psycopg.org/docs/sql.html
   >>> [2]:
   >>> https://link.springer.com/content/pdf/bbm%3A978-1-4302-0018-5%2F1.pdf
   >>> [3]:
   >>> https://stackoverflow.com/questions/22395883/postgresql-what-is-the-maximum-number-of-tables-can-store-in-postgresql-databas
   >>> [4]: We use RDS instances in AWS
   >>>
   >>
   

8. Re: Storing thousands of csv files in postgresql

   Ion Alberdi <ion.alberdi@pricemoov.com> — 2022-02-15T20:43:42Z

   Hi Marcos,
   
   >All those thousands of CSV files have a completely unknown structure ?
   Exactly
   
   Le mar. 15 févr. 2022 à 21:28, Marcos Pegoraro <marcos@f10.com.br> a écrit :
   
   > I'm going to challenge that the question is not one for Postgres, but you
   >> should be asking "What filesystem is best suited to having many files in
   >> the same directory" and let the technology deal with the problem.  Postgres
   >> is just dependent on the filesystem for this behavior.  And to that answer,
   >> I believe, is XFS.
   >>
   >> He didn't say how many different schemas will have.
   > All those thousands of CSV files have a completely unknown structure ?
   > Or you'll have just dozens or hundreds of structures ?
   >
   > Marcos
   >
   

9. Re: Storing thousands of csv files in postgresql

   Steve Midgley <science@misuse.org> — 2022-02-15T20:58:34Z

   On Tue, Feb 15, 2022 at 11:38 AM Ion Alberdi <ion.alberdi@pricemoov.com>
   wrote:
   
   > Thanks for these precious insights Steve!
   > >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.
   > Indeed!
   >
   > >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?
   > So far I'd hope that https://www.citusdata.com/ would have features to do
   > so. Reading the docs, they do not seem to provide such a federation though,
   > I'll send them an email to be sure. Thanks again!
   >
   > Le mar. 15 févr. 2022 à 17:20, Steve Midgley <science@misuse.org> a
   > écrit :
   >
   >>
   >>
   >> 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.
   >>
   >>
   I don't think you need a "federated" postgres network like Citus at all - I
   think this solves a different use case. For your design problem, I think
   that having a bunch of independent Pg servers would be fine - as long as
   you don't need to run searches across CSV tables stored across different
   databases (in which case you do need index/search federation of some kind).
   
   Regarding Erik Brandsberg's point about XFS, I think this is a useful
   alternative approach, if I understand the idea. Instead of storing your CSV
   files in Postgres, just store them as CSV files on the file system. You can
   still store the schemas in Pg, but each schema would just point to a file
   in the file system and you'd manipulate the files in the filesystem using
   whatever language is appropriate (I find ruby to be excellent for managing
   CSV files). If you need to index those files to run searches against them,
   I'd direct your attention to https://prestodb.io/ which is the core
   technology that runs Amazon Athena. This allows you to search CSV files
   with various schema (among other data bindings). So you might find that Pg
   as your schema storage, XFS (or any modern FS) to store large numbers of
   CSV files, and Presto/Athena to index/search those files, along with some
   CSV management language (like Ruby or something even higher level) to
   manage the data.
   
   I think if I were dealing with less than 10k CSV files (and therefore Pg
   tables), I might use Pg, and if I were dealing with 10k+ files, I'd start
   looking at file systems + Presto. But that's a WAG.
   
   Steve
   
   
   
   On Tue, Feb 15, 2022 at 11:38 AM Ion Alberdi <ion.alberdi@pricemoov.com>
   wrote:
   
   > Thanks for these precious insights Steve!
   > >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.
   > Indeed!
   >
   > >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?
   > So far I'd hope that https://www.citusdata.com/ would have features to do
   > so. Reading the docs, they do not seem to provide such a federation though,
   > I'll send them an email to be sure. Thanks again!
   >
   > Le mar. 15 févr. 2022 à 17:20, Steve Midgley <science@misuse.org> a
   > écrit :
   >
   >>
   >>
   >> 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
   >>
   >
   

10. Re: Storing thousands of csv files in postgresql

    Ion Alberdi <ion.alberdi@pricemoov.com> — 2022-02-15T21:12:28Z

    >I don't think you need a "federated" postgres network like Citus at all -
    I think this solves a different use case. For your design problem, I think
    that having a bunch of independent Pg servers would be fine - as long as
    you don't need to run searches across CSV tables stored across different
    databases (in which case you do need index/search federation of some kind).
    Indeed
    
    >I think if I were dealing with less than 10k CSV files (and therefore Pg
    tables), I might use Pg, and if I were dealing with 10k+ files, I'd start
    looking at file systems + Presto. But that's a WAG.
    Got it, this setup would require a heavier dev investment compared to using
    multiple pg instances though.
    
    Thanks for these additional insights!
    
    
    
    Le mar. 15 févr. 2022 à 21:58, Steve Midgley <science@misuse.org> a écrit :
    
    >
    >
    > On Tue, Feb 15, 2022 at 11:38 AM Ion Alberdi <ion.alberdi@pricemoov.com>
    > wrote:
    >
    >> Thanks for these precious insights Steve!
    >> >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.
    >> Indeed!
    >>
    >> >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?
    >> So far I'd hope that https://www.citusdata.com/ would have features to
    >> do so. Reading the docs, they do not seem to provide such a federation
    >> though,
    >> I'll send them an email to be sure. Thanks again!
    >>
    >> Le mar. 15 févr. 2022 à 17:20, Steve Midgley <science@misuse.org> a
    >> écrit :
    >>
    >>>
    >>>
    >>> 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.
    >>>
    >>>
    > I don't think you need a "federated" postgres network like Citus at all -
    > I think this solves a different use case. For your design problem, I think
    > that having a bunch of independent Pg servers would be fine - as long as
    > you don't need to run searches across CSV tables stored across different
    > databases (in which case you do need index/search federation of some kind).
    >
    > Regarding Erik Brandsberg's point about XFS, I think this is a useful
    > alternative approach, if I understand the idea. Instead of storing your CSV
    > files in Postgres, just store them as CSV files on the file system. You can
    > still store the schemas in Pg, but each schema would just point to a file
    > in the file system and you'd manipulate the files in the filesystem using
    > whatever language is appropriate (I find ruby to be excellent for managing
    > CSV files). If you need to index those files to run searches against them,
    > I'd direct your attention to https://prestodb.io/ which is the core
    > technology that runs Amazon Athena. This allows you to search CSV files
    > with various schema (among other data bindings). So you might find that Pg
    > as your schema storage, XFS (or any modern FS) to store large numbers of
    > CSV files, and Presto/Athena to index/search those files, along with some
    > CSV management language (like Ruby or something even higher level) to
    > manage the data.
    >
    > I think if I were dealing with less than 10k CSV files (and therefore Pg
    > tables), I might use Pg, and if I were dealing with 10k+ files, I'd start
    > looking at file systems + Presto. But that's a WAG.
    >
    > Steve
    >
    >
    >
    > On Tue, Feb 15, 2022 at 11:38 AM Ion Alberdi <ion.alberdi@pricemoov.com>
    > wrote:
    >
    >> Thanks for these precious insights Steve!
    >> >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.
    >> Indeed!
    >>
    >> >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?
    >> So far I'd hope that https://www.citusdata.com/ would have features to
    >> do so. Reading the docs, they do not seem to provide such a federation
    >> though,
    >> I'll send them an email to be sure. Thanks again!
    >>
    >> Le mar. 15 févr. 2022 à 17:20, Steve Midgley <science@misuse.org> a
    >> écrit :
    >>
    >>>
    >>>
    >>> 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
    >>>
    >>
    

11. Re: Storing thousands of csv files in postgresql

    Rob Sargent <robjsargent@gmail.com> — 2022-02-15T21:13:30Z

    > I don't think you need a "federated" postgres network like Citus at 
    > all - I think this solves a different use case. For your design 
    > problem, I think that having a bunch of independent Pg servers would 
    > be fine - as long as you don't need to run searches across CSV tables 
    > stored across different databases (in which case you do need 
    > index/search federation of some kind).
    >
    > Regarding Erik Brandsberg's point about XFS, I think this is a useful 
    > alternative approach, if I understand the idea. Instead of storing 
    > your CSV files in Postgres, just store them as CSV files on the file 
    > system. You can still store the schemas in Pg, but each schema would 
    > just point to a file in the file system and you'd manipulate the files 
    > in the filesystem using whatever language is appropriate (I find ruby 
    > to be excellent for managing CSV files). If you need to index those 
    > files to run searches against them, I'd direct your attention to 
    > https://prestodb.io/ which is the core technology that runs Amazon 
    > Athena. This allows you to search CSV files with various schema (among 
    > other data bindings). So you might find that Pg as your schema 
    > storage, XFS (or any modern FS) to store large numbers of CSV files, 
    > and Presto/Athena to index/search those files, along with some CSV 
    > management language (like Ruby or something even higher level) to 
    > manage the data.
    >
    > I think if I were dealing with less than 10k CSV files (and therefore 
    > Pg tables), I might use Pg, and if I were dealing with 10k+ files, I'd 
    > start looking at file systems + Presto. But that's a WAG.
    >
    > Steve
    >
    >
    I think the add/remove column requirement alone justifies NOT using 
    files.  The CSV approach will temp the system to handle some versioning 
    nonsense. Using tables also provides some protection against the 
    inevitable garbage data in the CSVs.

12. Re: Storing thousands of csv files in postgresql

    Steve Midgley <science@misuse.org> — 2022-02-15T21:21:46Z

    On Tue, Feb 15, 2022 at 1:13 PM Rob Sargent <robjsargent@gmail.com> wrote:
    
    >
    > I don't think you need a "federated" postgres network like Citus at all -
    > I think this solves a different use case. For your design problem, I think
    > that having a bunch of independent Pg servers would be fine - as long as
    > you don't need to run searches across CSV tables stored across different
    > databases (in which case you do need index/search federation of some kind).
    >
    > Regarding Erik Brandsberg's point about XFS, I think this is a useful
    > alternative approach, if I understand the idea. Instead of storing your CSV
    > files in Postgres, just store them as CSV files on the file system. You can
    > still store the schemas in Pg, but each schema would just point to a file
    > in the file system and you'd manipulate the files in the filesystem using
    > whatever language is appropriate (I find ruby to be excellent for managing
    > CSV files). If you need to index those files to run searches against them,
    > I'd direct your attention to https://prestodb.io/ which is the core
    > technology that runs Amazon Athena. This allows you to search CSV files
    > with various schema (among other data bindings). So you might find that Pg
    > as your schema storage, XFS (or any modern FS) to store large numbers of
    > CSV files, and Presto/Athena to index/search those files, along with some
    > CSV management language (like Ruby or something even higher level) to
    > manage the data.
    >
    > I think if I were dealing with less than 10k CSV files (and therefore Pg
    > tables), I might use Pg, and if I were dealing with 10k+ files, I'd start
    > looking at file systems + Presto. But that's a WAG.
    >
    > Steve
    >
    >
    >
    > I think the add/remove column requirement alone justifies NOT using
    > files.  The CSV approach will temp the system to handle some versioning
    > nonsense. Using tables also provides some protection against the inevitable
    > garbage data in the CSVs.
    >
    
    I missed that requirement - totally agree. Adding columns to file-based
    CSVs is a huge PITA. Agree on your other point as well: just having the
    right charset requirements in Pg can help trim or identify some of the bad
    data that comes in from CSVs but the opportunities for all kinds of
    efficient data validation recommends storing the CSV data in Pg.
    
    I'll add that I've used the postgres COPY command to move CSV data into
    tables so fast that I had to double take the rows/sec to believe it. I
    actually shared a gist to demonstrate how to implement this using Ruby and
    a low level postgres driver a few months ago:
    https://gist.github.com/science/393907d4123c87ed767bc81e9dd5a7da  Maybe
    useful..
    

13. Re: Storing thousands of csv files in postgresql

    Ion Alberdi <ion.alberdi@pricemoov.com> — 2022-02-15T21:21:59Z

    >I think the add/remove column requirement alone justifies NOT using
    files.  The CSV approach will temp the system to handle some versioning
    nonsense. Using tables also provides some protection against the inevitable
    garbage data in the CSVs.
    
    Indeed, in addition to that all the features related to types/constraints
    and links to other tables* available in postgresql would be lost (that
    would need to be re-implemented in the app layer).
    Thanks Rob!
    
    *: on the pros, I forgot to add the "on delete cascade on foreign keys"
    that is very welcome too.
    
    Le mar. 15 févr. 2022 à 22:13, Rob Sargent <robjsargent@gmail.com> a écrit :
    
    >
    > I don't think you need a "federated" postgres network like Citus at all -
    > I think this solves a different use case. For your design problem, I think
    > that having a bunch of independent Pg servers would be fine - as long as
    > you don't need to run searches across CSV tables stored across different
    > databases (in which case you do need index/search federation of some kind).
    >
    > Regarding Erik Brandsberg's point about XFS, I think this is a useful
    > alternative approach, if I understand the idea. Instead of storing your CSV
    > files in Postgres, just store them as CSV files on the file system. You can
    > still store the schemas in Pg, but each schema would just point to a file
    > in the file system and you'd manipulate the files in the filesystem using
    > whatever language is appropriate (I find ruby to be excellent for managing
    > CSV files). If you need to index those files to run searches against them,
    > I'd direct your attention to https://prestodb.io/ which is the core
    > technology that runs Amazon Athena. This allows you to search CSV files
    > with various schema (among other data bindings). So you might find that Pg
    > as your schema storage, XFS (or any modern FS) to store large numbers of
    > CSV files, and Presto/Athena to index/search those files, along with some
    > CSV management language (like Ruby or something even higher level) to
    > manage the data.
    >
    > I think if I were dealing with less than 10k CSV files (and therefore Pg
    > tables), I might use Pg, and if I were dealing with 10k+ files, I'd start
    > looking at file systems + Presto. But that's a WAG.
    >
    > Steve
    >
    >
    >
    > I think the add/remove column requirement alone justifies NOT using
    > files.  The CSV approach will temp the system to handle some versioning
    > nonsense. Using tables also provides some protection against the inevitable
    > garbage data in the CSVs.
    >
    

14. Re: Storing thousands of csv files in postgresql

    Ion Alberdi <ion.alberdi@pricemoov.com> — 2022-02-15T21:30:37Z

    >I missed that requirement - totally agree. Adding columns to file-based
    CSVs is a huge PITA. Agree on your other point as well: just having the
    right charset requirements in Pg can help trim or identify some of the bad
    data that comes in from CSVs but the opportunities for all kinds of
    efficient data validation recommends storing the CSV data in Pg.
    +1
    
    >I'll add that I've used the postgres COPY command to move CSV data into
    tables so fast that I had to double take the rows/sec to believe it. I
    actually shared a gist to demonstrate how to implement this using Ruby and
    a low level postgres driver a few months ago:
    https://gist.github.com/science/393907d4123c87ed767bc81e9dd5a7da  Maybe
    useful..
    Thanks for the gist Steve!
    
    Le mar. 15 févr. 2022 à 22:22, Steve Midgley <science@misuse.org> a écrit :
    
    >
    >
    > On Tue, Feb 15, 2022 at 1:13 PM Rob Sargent <robjsargent@gmail.com> wrote:
    >
    >>
    >> I don't think you need a "federated" postgres network like Citus at all -
    >> I think this solves a different use case. For your design problem, I think
    >> that having a bunch of independent Pg servers would be fine - as long as
    >> you don't need to run searches across CSV tables stored across different
    >> databases (in which case you do need index/search federation of some kind).
    >>
    >> Regarding Erik Brandsberg's point about XFS, I think this is a useful
    >> alternative approach, if I understand the idea. Instead of storing your CSV
    >> files in Postgres, just store them as CSV files on the file system. You can
    >> still store the schemas in Pg, but each schema would just point to a file
    >> in the file system and you'd manipulate the files in the filesystem using
    >> whatever language is appropriate (I find ruby to be excellent for managing
    >> CSV files). If you need to index those files to run searches against them,
    >> I'd direct your attention to https://prestodb.io/ which is the core
    >> technology that runs Amazon Athena. This allows you to search CSV files
    >> with various schema (among other data bindings). So you might find that Pg
    >> as your schema storage, XFS (or any modern FS) to store large numbers of
    >> CSV files, and Presto/Athena to index/search those files, along with some
    >> CSV management language (like Ruby or something even higher level) to
    >> manage the data.
    >>
    >> I think if I were dealing with less than 10k CSV files (and therefore Pg
    >> tables), I might use Pg, and if I were dealing with 10k+ files, I'd start
    >> looking at file systems + Presto. But that's a WAG.
    >>
    >> Steve
    >>
    >>
    >>
    >> I think the add/remove column requirement alone justifies NOT using
    >> files.  The CSV approach will temp the system to handle some versioning
    >> nonsense. Using tables also provides some protection against the inevitable
    >> garbage data in the CSVs.
    >>
    >
    > I missed that requirement - totally agree. Adding columns to file-based
    > CSVs is a huge PITA. Agree on your other point as well: just having the
    > right charset requirements in Pg can help trim or identify some of the bad
    > data that comes in from CSVs but the opportunities for all kinds of
    > efficient data validation recommends storing the CSV data in Pg.
    >
    > I'll add that I've used the postgres COPY command to move CSV data into
    > tables so fast that I had to double take the rows/sec to believe it. I
    > actually shared a gist to demonstrate how to implement this using Ruby and
    > a low level postgres driver a few months ago:
    > https://gist.github.com/science/393907d4123c87ed767bc81e9dd5a7da  Maybe
    > useful..
    >