3 ms·

So, let's say I have a DB of a million rows, anticipate having 100M rows of archived data, then adding 5M rows per year; each of my rows has some metadata and p

• Znafon 7y ago
  I had lot of success with Clickhouse recently for tables that are 200+ millions row and that was with the Log table engine, not the Merge tree one so I would expect it to get even faster when we change. It's very easy to setup so you should be able to test it quickly to see if it fits your needs.
• jiggawatts 7y ago
  That's a hilariously small amount of relational data that your phone could probably handle with decent performance. I made larger databases than that back in 2005 on a single commodity server. I wouldn't be surprised to see PowerBI manipulating that in-memory on a desktop. Microsoft SQL Server with Clustered ColumnStore tables would make practically all queries fast on that, especially if most queries are only for subsets of the data. PostegreSQL could probably handle that too, no sweat. Also see "Your data fits in RAM": https://news.ycombinator.com/item?id=9581862 https://news.ycombinator.com/item?id=9581862 which would mean that you could do in-memory analytics of your relational data with SAP HANA or SQL Server if you really needed that kind of performance: https://docs.microsoft.com/en-us/archive/blogs/sqlserverstorageengine/real-time-operational-analytics-memory-optimized-table-and-columnstore-index https://docs.microsoft.com/en-us/archive/blogs/sqlserverstor... You can spin up either SQL or HANA in the cloud or on Linux, so you don't even need Windows. Both can be connected to just about any other database you can name, often directly for cross-database queries. SQL 2019 is particularly good at virtualizing external data: https://docs.microsoft.com/en-us/sql/relational-databases/polybase/data-virtualization?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/po... 10 gigapixel images are a completely separate problem. If you need individual images to be fast to view, you want some sort of hierarchical tiling like Google Maps does. If you're processing them with machine vision or something, then you want whatever makes the ML guys happy. PS: I hope you're not working on DARPA's spy drone, because then please disregard everything I said and delete your data for the good of humanity: https://www.extremetech.com/extreme/146909-darpa-shows-off-1-8-gigapixel-surveillance-drone-can-spot-a-terrorist-from-20000-feet https://www.extremetech.com/extreme/146909-darpa-shows-off-1...
  • killjoywashere 7y ago
    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