3 ms·

Parent responding: to be clear I was not impressed by my own row count, if anything, I was trying to make it clear this would not be a burden for a traditional

Parent responding: to be clear I was not impressed by my own row count, if anything, I was trying to make it clear this would not be a burden for a traditional postgres instance. I recall a postgres user group I attended where a guy had been working on handling a billion writes per second (consulting for Cymer if I recall). My whole dataset is less than 1 second worth of that guy's data. And since Cymer is in the photons business, I'm willing to bet they were downsampling heavily. My question is more the specific mix of problems: a DB, a ton of image data, and other adjacent DBs that people want us to play with. How would you set that up? I work on cancer, so, definitely not spy drones.

dikei 7y ago
For this amount of data, I would use good old Postgres, partition the data by ingestion time, then just detach the old partitions when you need to archive it. For joining data from multiple database, if the data is large, I would use something like Presto(https://prestosql.io/ https://prestosql.io/) to join and process the data. But that's partly because we have already had Presto clusters running.
jiggawatts 7y ago
Keep in mind that commercial databases are still substantially better for bulk data performance than most open source offerings, and tend to have better compatibility with other commercial databases. E.g.: Microsoft and Oracle are competitors, but it's always going to be a certainty that you can connect them directly to each other. Similarly, it's actually hard to beat MS SQL Server for OLTP workloads, especially at moderate (~1TB) scale or for ad-hoc queries that require parallelisation but not distribution to a cluster. In other words, it's great for "Medium Data". It does actually scale to large clusters with the new SQL Parallel Data Warehouse: https://docs.microsoft.com/en-us/sql/analytics-platform-system/parallel-data-warehouse-overview https://docs.microsoft.com/en-us/sql/analytics-platform-syst... That's also available as an Azure service if you want to have a play: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-overview-what-is https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sq... But realistically, distributed clusters are almost certainly not what you need. They're complex and slower for simple queries that could be answered by one box with a good indexing scheme. Just to reiterate: for large tables with hundreds of millions of rows, you want a modern, column-oriented database. I can't stress this enough: if you haven't yet played with SQL's ColumnStore, go spin up an instance in Azure or AWS and give it a go on one of your larger tables. It's crazy good. I've seen compression ratios of 50:1 and query performance improvements of 300:1 with basically zero hand-tuning of indexes or any such thing. There's a reason people pay $10k+ per core for Enterprise SQL Server licensing. But hey, if you're penny-pinching on a $10M project, then as I said, MySQL and PostgreSQL will work. They're better at replication, clustering, and MySQL (only) is better at low-latency for trivial queries. But they tend to be poor at connecting to commercial or otherwise quirky data sources. So then you'd probably have to layer something like Apache Drill on top: https://en.wikipedia.org/wiki/Apache_Drill https://en.wikipedia.org/wiki/Apache_Drill

dikei 7y ago

For this amount of data, I would use good old Postgres, partition the data by ingestion time, then just detach the old partitions when you need to archive it. For joining data from multiple database, if the data is large, I would use something like Presto(https://prestosql.io/ https://prestosql.io/) to join and process the data. But that's partly because we have already had Presto clusters running.

jiggawatts 7y ago

Keep in mind that commercial databases are still substantially better for bulk data performance than most open source offerings, and tend to have better compatibility with other commercial databases. E.g.: Microsoft and Oracle are competitors, but it's always going to be a certainty that you can connect them directly to each other. Similarly, it's actually hard to beat MS SQL Server for OLTP workloads, especially at moderate (~1TB) scale or for ad-hoc queries that require parallelisation but not distribution to a cluster. In other words, it's great for "Medium Data". It does actually scale to large clusters with the new SQL Parallel Data Warehouse: https://docs.microsoft.com/en-us/sql/analytics-platform-system/parallel-data-warehouse-overview https://docs.microsoft.com/en-us/sql/analytics-platform-syst... That's also available as an Azure service if you want to have a play: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-overview-what-is https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sq... But realistically, distributed clusters are almost certainly not what you need. They're complex and slower for simple queries that could be answered by one box with a good indexing scheme. Just to reiterate: for large tables with hundreds of millions of rows, you want a modern, column-oriented database. I can't stress this enough: if you haven't yet played with SQL's ColumnStore, go spin up an instance in Azure or AWS and give it a go on one of your larger tables. It's crazy good. I've seen compression ratios of 50:1 and query performance improvements of 300:1 with basically zero hand-tuning of indexes or any such thing. There's a reason people pay $10k+ per core for Enterprise SQL Server licensing. But hey, if you're penny-pinching on a $10M project, then as I said, MySQL and PostgreSQL will work. They're better at replication, clustering, and MySQL (only) is better at low-latency for trivial queries. But they tend to be poor at connecting to commercial or otherwise quirky data sources. So then you'd probably have to layer something like Apache Drill on top: https://en.wikipedia.org/wiki/Apache_Drill https://en.wikipedia.org/wiki/Apache_Drill