4 ms·

I’m assuming anyone who has to make that decision already knows this but, while PostgreSQL is great to host a production database, it isn’t a great choice for a

by bertil 7y ago

I’m assuming anyone who has to make that decision already knows this but, while PostgreSQL is great to host a production database, it isn’t a great choice for an analytic database at scale, or to train or store your machine learning features. It works, but it’s not great at scale. You can get away with having a scheduled pg_dump early on, some reports on that, while you figure out an ETL/Messaging process — but picking something that handles concurrent large-scale queries will matter fast.

yeellow 7y ago
Could you name some better alternatives for analytical db?
- DeathArrow 7y ago
  We use Teradata for our data warehouse. We have an enormous quantity of data in there (We are a multinational wholesale chain). We however plan to move to either BigQuery or an inhouse solution in the future. But for now Teradata is our workhorse and quite good at what it does.
- bertil 7y ago
  There are a lot of commercial solution that are geared towards reporting, from old-school Oracle, TeraData even SAS (would not recommend that one). Some options that came our recently when “Big Data” was a sentence people said: Hive, RedShift, S3, Cassandra. New players that abstract a lot of the problems from the others and add Machine-Learning capacities on top: Google BigQuery, Snowflake. There are non-tabular structure like the graph based ones, Neo4j e.g.: I’ve yet to find a business where that’s a viable option for analytics for the first five years but I like that those are bringing something new. Most of those are great and work: they have connectors to whatever language or tool you want to use. The most promising tool you’ll need to handle most of the transformation is either AirFlow or preferably dbt. Those are is independent from the database, so don’t worry too much about the features that vendors tout. One key thing: monitor all the queries going to that database, find the expensive ones, those with suspicious patterns, etc. Spin-up, response time can matter, but they are rarely a problem for most “slow” analytic use cases; for instance, Google BigQuery takes 10 seconds no matter what you query and it’s fine. On the other hand, concurrency has been an issue for me more than anything: all the analysts and managers trying to update their dashboards on Monday at 10 am. You rapidly get to a point where prices are high and negotiated, so you want to think about your likely usage in the next years before you step into that meeting. Key decision: by default, prefer the tech that is closest to the rest of your stack because ingress is the easiest factor to predict. Looker will be mentioned: it’s on top of all that, downstream from AirFlow/dbt.
- mountainview 7y ago
  Shameless plug: You might be interested in TiDB (https://github.com/pingcap/tidb https://github.com/pingcap/tidb), an open-source distributed HTAP database. - For OLTP workload and some ad-hoc queries, you can use TiDB + TiKV. One of our adopters has a production cluster with 300+ TB data and can easily cope with the spike caused by the brought by COVID-19. - For more complex queries, TiSpark+ TiKV might work well; for heavier queries, we added a columnar store, TiFlash, see https://pingcap.com/blog/delivering-real-time-analytics-and-true-htap-by-combining-columnstore-and-rowstore/ https://pingcap.com/blog/delivering-real-time-analytics-and-...
- jacques_chester 7y ago
  There are a lot of products that either fork or directly extend PostgreSQL for OLAP workloads. Greenplum[0], Materialize[1], Citus[2], PipelineDB[3] etc etc. [0] https://greenplum.org/ https://greenplum.org/ [1] https://materialize.io/ https://materialize.io/ [2] https://www.citusdata.com/ https://www.citusdata.com/ (edit: I said TimescaleDB, I was thinking of Citus) [3] https://github.com/pipelinedb/pipelinedb https://github.com/pipelinedb/pipelinedb Disclosure: I work for VMware, which sponsors Greenplum development and sells commercial offerings.
hnarn 7y ago
> PostgreSQL is great to host a production database, it isn’t a great choice for an analytic database at scale How do you define an "analytic" database? Time series data, or something else?
- bertil 7y ago
  I’d say hosting data that you don’t need to run your live ergodic (non-ML) software service, but that you’d need for other services: informing the business, flagging operational issues, training ML models. Some of those can be done with back-end tools — say, if your software also contact to your customer service, and the managers of that center monitor their activity on a solution developed in-house, that solution isn’t “analytical” but should rather be part of the main architecture. The main distinction that I’d make is: would it be a problem for your service if that data wasn’t available for a second, a minute, an hour? Anything visible on your app or website? A second is probably stretching it. Monitoring logistic operations, say drivers at Ubers? Up to a minute is probably fine. You want to retrain a ML model because you have a new idea, but database is down for maintenance? For an hour? You can go and grab coffee, or lunch — you are fine. Serving that same model for recommendations on a e-commerce website, that’s obviously not something that can take the same delay.
  - GlennS 7y ago
    I actually think that part of the popularity of Postgres - and relational databases (RDBMS) in general - is that they are good enough for most analysis work. Not specialist stuff, Postgres isn't really a competitor if your business is large or data-focused enough to be running OLAP (someone will probably link an OLAP extension now...). But, most businesses just aren't that sophisticated in how they use their data.

by bertil 7y ago

yeellow 7y ago

Could you name some better alternatives for analytical db?

DeathArrow 7y ago
We use Teradata for our data warehouse. We have an enormous quantity of data in there (We are a multinational wholesale chain). We however plan to move to either BigQuery or an inhouse solution in the future. But for now Teradata is our workhorse and quite good at what it does.
bertil 7y ago
There are a lot of commercial solution that are geared towards reporting, from old-school Oracle, TeraData even SAS (would not recommend that one). Some options that came our recently when “Big Data” was a sentence people said: Hive, RedShift, S3, Cassandra. New players that abstract a lot of the problems from the others and add Machine-Learning capacities on top: Google BigQuery, Snowflake. There are non-tabular structure like the graph based ones, Neo4j e.g.: I’ve yet to find a business where that’s a viable option for analytics for the first five years but I like that those are bringing something new. Most of those are great and work: they have connectors to whatever language or tool you want to use. The most promising tool you’ll need to handle most of the transformation is either AirFlow or preferably dbt. Those are is independent from the database, so don’t worry too much about the features that vendors tout. One key thing: monitor all the queries going to that database, find the expensive ones, those with suspicious patterns, etc. Spin-up, response time can matter, but they are rarely a problem for most “slow” analytic use cases; for instance, Google BigQuery takes 10 seconds no matter what you query and it’s fine. On the other hand, concurrency has been an issue for me more than anything: all the analysts and managers trying to update their dashboards on Monday at 10 am. You rapidly get to a point where prices are high and negotiated, so you want to think about your likely usage in the next years before you step into that meeting. Key decision: by default, prefer the tech that is closest to the rest of your stack because ingress is the easiest factor to predict. Looker will be mentioned: it’s on top of all that, downstream from AirFlow/dbt.
mountainview 7y ago
Shameless plug: You might be interested in TiDB (https://github.com/pingcap/tidb https://github.com/pingcap/tidb), an open-source distributed HTAP database. - For OLTP workload and some ad-hoc queries, you can use TiDB + TiKV. One of our adopters has a production cluster with 300+ TB data and can easily cope with the spike caused by the brought by COVID-19. - For more complex queries, TiSpark+ TiKV might work well; for heavier queries, we added a columnar store, TiFlash, see https://pingcap.com/blog/delivering-real-time-analytics-and-true-htap-by-combining-columnstore-and-rowstore/ https://pingcap.com/blog/delivering-real-time-analytics-and-...
jacques_chester 7y ago
There are a lot of products that either fork or directly extend PostgreSQL for OLAP workloads. Greenplum[0], Materialize[1], Citus[2], PipelineDB[3] etc etc. [0] https://greenplum.org/ https://greenplum.org/ [1] https://materialize.io/ https://materialize.io/ [2] https://www.citusdata.com/ https://www.citusdata.com/ (edit: I said TimescaleDB, I was thinking of Citus) [3] https://github.com/pipelinedb/pipelinedb https://github.com/pipelinedb/pipelinedb Disclosure: I work for VMware, which sponsors Greenplum development and sells commercial offerings.

hnarn 7y ago

> PostgreSQL is great to host a production database, it isn’t a great choice for an analytic database at scale How do you define an "analytic" database? Time series data, or something else?

bertil 7y ago
I’d say hosting data that you don’t need to run your live ergodic (non-ML) software service, but that you’d need for other services: informing the business, flagging operational issues, training ML models. Some of those can be done with back-end tools — say, if your software also contact to your customer service, and the managers of that center monitor their activity on a solution developed in-house, that solution isn’t “analytical” but should rather be part of the main architecture. The main distinction that I’d make is: would it be a problem for your service if that data wasn’t available for a second, a minute, an hour? Anything visible on your app or website? A second is probably stretching it. Monitoring logistic operations, say drivers at Ubers? Up to a minute is probably fine. You want to retrain a ML model because you have a new idea, but database is down for maintenance? For an hour? You can go and grab coffee, or lunch — you are fine. Serving that same model for recommendations on a e-commerce website, that’s obviously not something that can take the same delay.
- GlennS 7y ago
  I actually think that part of the popularity of Postgres - and relational databases (RDBMS) in general - is that they are good enough for most analysis work. Not specialist stuff, Postgres isn't really a competitor if your business is large or data-focused enough to be running OLAP (someone will probably link an OLAP extension now...). But, most businesses just aren't that sophisticated in how they use their data.