200,000 tables in one database?
What?
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.
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.
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.
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.
Early in my career this pissed me off too. As I matured I learned that there were often valid reasons to do it the same way. E.g. External systems accessed the DB directly for reporting, special cases meant my proposed design would be fragile, etc.
To the younger folks here, when you encounter this realize constraints exist that you must workaround. If you feel strongly enough to push back then first seek out some senior resources to dig for a deeper understanding before going ahead and proposing an alternate.
As I grow older, I become less enthusiastic about protecting other people from the potential consequences of their decisions. At this point, I will still warn about those consequences, but only so that my company will get contracted again later, to do the thing I suggested in the first place.
I: You could speed up this workflow by doing X.
They: No... It needs to look exactly like this paper form.
I: Your wish is my command.
[3 months pass]
They: We want you to change this form by doing almost exactly X.
Boss: [holds out hand, rubs fingers together]
They: [writes fat check]
I: Your wish is my command.
They: Just make sure that it still looks like the form when I print it.
I: [dying a little inside] Your wish is stu... still my command.
[3 months pass]
They: The printed forms are crap. We want you to export the data to an Excel spreadsheet.
Boss: [holds out hand, rubs fingers together]
They: [writes fat check]
I: Your wish is my command.
[continue in this fashion until your death]And one of the worst technical failures I worked on was a project where they insisted on reports from live data, and put little bits of report data in every response from the server.
They couldn't grasp why the performance was so underwhelming. Every index in your tables adds time to every insert and update operation, and often slightly stale data is close enough for most users and can be pre-calculated on an interval.
If you get the bright idea that measuring how many people are online or exactly how many dollars you made this month, then you will have fewer users and make less money because of it.
Use reporting tables. Most people shouldn't be writing into your data anyway and if they are, run away. Make the reporting tables your external API, and then you can add or modify columns, split or combine tables whenever you want.
You got it :-)
Also, the state is part of the identifier already, however, you may want to pull it out into tables for a variety of reasons.
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-ho...
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.
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.
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.
For some extent, Postgres can do that. But I don't know the extent of flexibility/performance you'll need.
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.
(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.)
(We are, however, using MySQL, not Postgres, primarily because of the lack of a decent replication solution at the time the app was built.)