5 ms·

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 flexibl

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.

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.