5 ms·

200,000 tables in one database? What?

by hobs 10y ago

barrkel 10y ago
If you have a lot of variable schema datasets that you need to be able to index and query, you can do a lot worse than create a table per data set. At my job, in our largest environment we only have 30,000 tables in our biggest database, but that's only a year or two's worth of data and the rate of increase is increasing.
- hobs 10y ago
  Interesting, without giving anything proprietary away, how large are they in terms of gb/rows/column count?
  - barrkel 10y ago
    It entirely depends on the user's data. 2,000 to 7 million rows, with anything from three columns (rare) to 200+, 80% range would probably be 20 to 60 columns. Raw on-disk sizes can be anywhere from tens of GB to probably TB level - I don't track operations that closely any more. We (du.co) make a reconciliation product for finance. Reconciliation is basically comparing two lists. The size of the data entirely depends on what the customer wants to compare.
- overcast 10y ago
  Can you give me an example of what you're doing exactly? Rapidly expanding the number of tables in a database doesn't make sense. Sounds like you need a flexible document store, and not a relational database.
  - barrkel 10y ago
    Have looked at what's on the market, and the performance characteristics of what's available isn't as good as a relational DB, in terms of queries that join the rest of our schema, static data, only a handful of warm tables but quick ramp-up time, etc. Specifically we don't want a cluster solution (too expensive in terms of hardware) and our data is almost completely static once it's loaded (so we don't need something that e.g. keeps stuff in memory to optimize updates). The closest is RethinkDB, but its memory requirements per table are too high. If you have other suggestions for something that can live on a smallish box (4G) yet support thousands of tables, fairly arbitrary queries, and joins against a relational DB, I'm open to suggestions. Fast bulk updates and inserts are a bonus; a relational DML is a requirement, since a good chunk of our logic is in SQL, and much more readable for it.
    - overcast 10y ago
      I was going to suggest RethinkDB, I've used it for tons of projects, and it's my current first go to DB. Combines the flexibility of a document store, with relational queries on them. Pretty awesome. Are the memory requirements that much more than having tens of thousands of tables open?
    - marcosdumay 10y ago
      The idea of a flexible document store is not using thousands of tables. Instead, you get a flexible "table" that can store a big number of different data types. For some extent, Postgres can do that. But I don't know the extent of flexibility/performance you'll need.
      - barrkel 10y ago
        We have 10s to 100s of thousands of tables - literally, tables, with rows and columns, each column of a consistent type, that need to be sortable and filterable - with each table having low thousands to low millions of rows. When browsing any one table, the entire table's contents - more or less - needs to be sortable on any column and served up in a paginated fashion. A single table with heterogeneous data, with lookup by ID or some simple key, is quite far away from what we need. What we need looks almost exactly like an RDBMS table. It even has foreign keys into tables that are much more conventional.
  - gaius 10y ago
    Agreed, RDBMSs are not really intended for this use case. I'd be tempted to just store the data as blobs in S3 in this scenario, since they're on RDS anyway.
    - barrkel 10y ago
      Finance: need separate virtual machines, virtual LAN etc. per customer at a minimum. No third-party cloud. Store needs to run fairly efficiently on small boxes to make the economics work. (FWIW, RDBMS has worked really well for this use case, for us. Having a fully supported relational language, efficient temporary tables that can spill out to disk without going into thrashing hell, a system designed to keep only the tip of the iceberg in memory, etc. can be useful.)
      - overcast 10y ago
        While I won't argue that you need some type of relational tables, I wouldn't consider creating thirty-thousand of them working really well. I'd be curious as to how RethinkDB would work for you.
        barrkel 10y ago
        What evidence would you require for proof that it's working well? We have the evidence we need already: a working solution well within its hardware constraints and performing excellently. There aren't many operations that scale worse than linearly in the number of tables (primarily schema queries), and not many operations are affected at all. The biggest thing to remember is to turn off the background schema queries your database client might try and do. (We are, however, using MySQL, not Postgres, primarily because of the lack of a decent replication solution at the time the app was built.)
        overcast 10y ago
        I don't really need any evidence, other than knowing someone is managing 30,000 tables, or worse, 200,000. I'm glad it's working for you, it just sounds like a complete nightmare to manage.
eddd 10y ago
I lifted both of my eyebrows while reading that sentence.
jimktrains2 10y ago
Image a case in which census data and the associated geometries. https://github.com/censusreporter/census-postgres https://github.com/censusreporter/census-postgres has 22 surveys, each with 230+ tables. That's 5000+ tables right there. Now, the TIGER tables for all of that is another 50 tables per year, so another 350 tables. If these were to be partitioned by state, instead of all records for all states in a single table, then we're looking at 270,000. So, it's not _that_ difficult.
- hobs 10y ago
  Are the surveys so fundamentally different that I cant just store the 280+ tables with a uniqueifier for the 22 different surveys? You want it by state, sounds like a column to add to my existing tables, not a way to make hundreds of new ones, or making a table with an FK and a state column.
  - logfromblammo 10y ago
    Obviously, you have not often worked with government customers. I can't even remember how many times I have explained what would be correct and maintainable, only to be told to do it exactly the same way the old system did it. So if I were working with the census, I'd consider myself lucky to not have to build a GUI for poking the holes in virtual Hollerith punch cards.
    - dsugarman 10y ago
      I worked with a small team (4 people) to rewrite the systems for determining GDP and other economic info for the US. We built an elegant system heavily utilizing dynamic SQL to keep the complexity in metadata and simplify the ridiculous amount of different ways information was collected and stored. In the end it went from taking days running ~20 COBOL programs in serial to around 20 minutes running one SQL script to get the results. I understand this may not be typical of government contracting, but it is not fair to say that you can't make quality systems for government customers.
    - josho 10y ago
sciurus 10y ago
As this post demonstrates, once you have a setup like that you'd really benefit from having someone on staff (or on retainer as a contractor) whose primary skill set is postgresql adminstration.
- falcolas 10y ago
  Or just DB administration in general. Even a basic understanding of DB theory and how that theory crashes with reality can be invaluable.
dmuth 10y ago
What indeed. Having that many tables in a database is just asking for trouble. My gut feeling for a database of this size is to suggest sharding. While I don't claim to be an expert in this sort of thing, here's how Pintrest approached a similar problem: https://engineering.pinterest.com/blog/sharding-pinterest-how-we-scaled-our-mysql-fleet/ https://engineering.pinterest.com/blog/sharding-pinterest-ho...
zwily 10y ago
Imagine your app uses 500 tables, and you use a separate schema per tenant on a single database. With 400 tenants, you now have 200,000 tables.
- hobs 10y ago
  Oh I understood that completely, but you probably dont want to make one giant database for all your customers unless you really love engineering the crap out of things.

200,000 tables in one database? What?

by hobs 10y ago

200,000 tables in one database? What?

If you have a lot of variable schema datasets that you need to be able to index and query, you can do a lot worse than create a table per data set. At my job, in our largest environment we only have 30,000 tables in our biggest database, but that's only a year or two's worth of data and the rate of increase is increasing.

hobs 10y ago
Interesting, without giving anything proprietary away, how large are they in terms of gb/rows/column count?
- barrkel 10y ago
  It entirely depends on the user's data. 2,000 to 7 million rows, with anything from three columns (rare) to 200+, 80% range would probably be 20 to 60 columns. Raw on-disk sizes can be anywhere from tens of GB to probably TB level - I don't track operations that closely any more. We (du.co) make a reconciliation product for finance. Reconciliation is basically comparing two lists. The size of the data entirely depends on what the customer wants to compare.
overcast 10y ago
Can you give me an example of what you're doing exactly? Rapidly expanding the number of tables in a database doesn't make sense. Sounds like you need a flexible document store, and not a relational database.
- barrkel 10y ago
  Have looked at what's on the market, and the performance characteristics of what's available isn't as good as a relational DB, in terms of queries that join the rest of our schema, static data, only a handful of warm tables but quick ramp-up time, etc. Specifically we don't want a cluster solution (too expensive in terms of hardware) and our data is almost completely static once it's loaded (so we don't need something that e.g. keeps stuff in memory to optimize updates). The closest is RethinkDB, but its memory requirements per table are too high. If you have other suggestions for something that can live on a smallish box (4G) yet support thousands of tables, fairly arbitrary queries, and joins against a relational DB, I'm open to suggestions. Fast bulk updates and inserts are a bonus; a relational DML is a requirement, since a good chunk of our logic is in SQL, and much more readable for it.
  - overcast 10y ago
    I was going to suggest RethinkDB, I've used it for tons of projects, and it's my current first go to DB. Combines the flexibility of a document store, with relational queries on them. Pretty awesome. Are the memory requirements that much more than having tens of thousands of tables open?
  - marcosdumay 10y ago
    The idea of a flexible document store is not using thousands of tables. Instead, you get a flexible "table" that can store a big number of different data types. For some extent, Postgres can do that. But I don't know the extent of flexibility/performance you'll need.
    - barrkel 10y ago
      We have 10s to 100s of thousands of tables - literally, tables, with rows and columns, each column of a consistent type, that need to be sortable and filterable - with each table having low thousands to low millions of rows. When browsing any one table, the entire table's contents - more or less - needs to be sortable on any column and served up in a paginated fashion. A single table with heterogeneous data, with lookup by ID or some simple key, is quite far away from what we need. What we need looks almost exactly like an RDBMS table. It even has foreign keys into tables that are much more conventional.
- gaius 10y ago
  Agreed, RDBMSs are not really intended for this use case. I'd be tempted to just store the data as blobs in S3 in this scenario, since they're on RDS anyway.
  - barrkel 10y ago
    Finance: need separate virtual machines, virtual LAN etc. per customer at a minimum. No third-party cloud. Store needs to run fairly efficiently on small boxes to make the economics work. (FWIW, RDBMS has worked really well for this use case, for us. Having a fully supported relational language, efficient temporary tables that can spill out to disk without going into thrashing hell, a system designed to keep only the tip of the iceberg in memory, etc. can be useful.)
    - overcast 10y ago
      While I won't argue that you need some type of relational tables, I wouldn't consider creating thirty-thousand of them working really well. I'd be curious as to how RethinkDB would work for you.
      - barrkel 10y ago
        What evidence would you require for proof that it's working well? We have the evidence we need already: a working solution well within its hardware constraints and performing excellently. There aren't many operations that scale worse than linearly in the number of tables (primarily schema queries), and not many operations are affected at all. The biggest thing to remember is to turn off the background schema queries your database client might try and do. (We are, however, using MySQL, not Postgres, primarily because of the lack of a decent replication solution at the time the app was built.)
        overcast 10y ago
        I don't really need any evidence, other than knowing someone is managing 30,000 tables, or worse, 200,000. I'm glad it's working for you, it just sounds like a complete nightmare to manage.

eddd 10y ago

I lifted both of my eyebrows while reading that sentence.

jimktrains2 10y ago

Image a case in which census data and the associated geometries. https://github.com/censusreporter/census-postgres https://github.com/censusreporter/census-postgres has 22 surveys, each with 230+ tables. That's 5000+ tables right there. Now, the TIGER tables for all of that is another 50 tables per year, so another 350 tables. If these were to be partitioned by state, instead of all records for all states in a single table, then we're looking at 270,000. So, it's not _that_ difficult.

hobs 10y ago
Are the surveys so fundamentally different that I cant just store the 280+ tables with a uniqueifier for the 22 different surveys? You want it by state, sounds like a column to add to my existing tables, not a way to make hundreds of new ones, or making a table with an FK and a state column.
- logfromblammo 10y ago
  Obviously, you have not often worked with government customers. I can't even remember how many times I have explained what would be correct and maintainable, only to be told to do it exactly the same way the old system did it. So if I were working with the census, I'd consider myself lucky to not have to build a GUI for poking the holes in virtual Hollerith punch cards.
  - dsugarman 10y ago
    I worked with a small team (4 people) to rewrite the systems for determining GDP and other economic info for the US. We built an elegant system heavily utilizing dynamic SQL to keep the complexity in metadata and simplify the ridiculous amount of different ways information was collected and stored. In the end it went from taking days running ~20 COBOL programs in serial to around 20 minutes running one SQL script to get the results. I understand this may not be typical of government contracting, but it is not fair to say that you can't make quality systems for government customers.
  - josho 10y ago

sciurus 10y ago

As this post demonstrates, once you have a setup like that you'd really benefit from having someone on staff (or on retainer as a contractor) whose primary skill set is postgresql adminstration.

falcolas 10y ago
Or just DB administration in general. Even a basic understanding of DB theory and how that theory crashes with reality can be invaluable.

dmuth 10y ago

What indeed. Having that many tables in a database is just asking for trouble. My gut feeling for a database of this size is to suggest sharding. While I don't claim to be an expert in this sort of thing, here's how Pintrest approached a similar problem: https://engineering.pinterest.com/blog/sharding-pinterest-how-we-scaled-our-mysql-fleet/ https://engineering.pinterest.com/blog/sharding-pinterest-ho...

zwily 10y ago

Imagine your app uses 500 tables, and you use a separate schema per tenant on a single database. With 400 tenants, you now have 200,000 tables.

hobs 10y ago
Oh I understood that completely, but you probably dont want to make one giant database for all your customers unless you really love engineering the crap out of things.