10 ms·

It feels like Postgres is eating the world. In a thread a couple of days ago many argued that with the capability of efficiently storing and processing JSON we

It feels like Postgres is eating the world. In a thread a couple of days ago many argued that with the capability of efficiently storing and processing JSON we can do without MongoDB et al. Now we also replace vector databases. There are extensions that provide columnar storage and to be honest I never completely understood the need for specialized time series databases. It is a bit as if Postgres has become what Oracle wanted to be - a one-stop solution for all your data storage needs.

rewmie 3y ago
> (...) and to be honest I never completely understood the need for specialized time series databases. The need for specialized time series databases is explained by the basis of any software development process: reuse ready-made high-level abstractions instead of having to reinvent the wheel, poorly. There are a few key features of time series databases that you either go without (i.e., data compression) or have to reinvent the wheel (querying, resolution-based retention periods, statistical summary of time periods,etc). You might live without those features,but in some applications there's a hefty price tag tied to not having them. If you adopt a time series database, you benefit from all those features without having to implement a single one of them. Perhaps that has no value to you, but it does have for the world.
- starfallg 3y ago
  I think what OP was getting at is why it has to be specialized. Time-series features can be implemented in a general purpose DB engine like postgres or mongo.
  - nonameiguess 3y ago
    How? I just tried to look for time series in Postgres and it looks like TimescaleDB is the plugin you'd want? At least web search seems to love it. Their landing page is just marketing fluff and says next to nothing about their feature set. In a database that isn't column-oriented, how can they do automatic downsampling? Or do they just not?
    - hnmullany 3y ago
      You might want to check out their columnar compression & their continuous aggregate blog posts: https://www.timescale.com/blog/timescaledb-2-3-improving-columnar-compression-for-time-series-on-postgresql/ https://www.timescale.com/blog/timescaledb-2-3-improving-col... https://www.timescale.com/blog/massive-scale-for-time-series-workloads-introducing-continuous-aggregates-for-distributed-hypertables-in-timescaledb-2-5/ https://www.timescale.com/blog/massive-scale-for-time-series...
      - lordnacho 3y ago
        Have used it for finance, can recommend. No need for kdb, at least for now. Their Slack is very responsive too, top service.
  - marginalia_nu 3y ago
    They kinda can't though, not without losing a lot of performance and capability. Time series databases should be viewed in the context of being a fairly niche product however. The archetypal use case is in finance where you have a constant firehose of tick data and trades across a wide array of instruments you need to be able to index instantaneously and aggregate easily. The sheer volume of data can't be overstated here. To make matters worse, the records are often both wide and sparsely populated. A non-specialized really DBMS doesn't cut it in this case. You need to build the DBMS from the ground up to cater to this sort of a usecase. Slapping a columnar backing store onto something existing does not cut it.
  - nhourcard 3y ago
    Time-series features can be implemented in a general purpose DB engine, but the architecture of the database will be limiting for lots of use cases requiring performance, especially for high cardinality datasets. A columnar database engineered from the ground up for time-series should have better foundations to allows fast ingest (to the tune of million of rows/sec per server) and also be very efficient for time-based queries that can be done via languages such as SQL. Using QuestDB as an example: Data is stored in chronological order and is optimized for sequential ingestion with a timestamp component (re-ordering data on the fly if it comes out-of-order). The data is also partitioned by time. The InfluxDB Line Protocol is better suited for streaming type of ingest versus transactional inserts via Postgres.
- weinzierl 3y ago
  I'm curious to research if a dedicated TSDB is worth it for my project. Not to waste time could you recommend one that in your opinion would be worth it over just Postgres? I used InfluxDB a couple of years a ago but didn't really see the value, even though I loved the product. Should I give it another chance? Application is storage of a diverse set of (irregular) events. Lifecycle management and archival is more important than compression. Volume is moderate.
  - kvmet 3y ago
    I'll spare you my rant on why I don't like InfluxDB lol. In the end it depends how much data you're dealing with. If you're only ingesting dozens to hundreds of records per second per writer you might actually like AWS Time stream. If you're dealing with a million + rows per second from a low number of writers (e.g. scientific data) then you probably want Influx or Timescale. If you want to host it yourself and you have less than 1TB of data then Influx is okay or if you want it cloud hosted then Influx may be a good option. If you need full control, scalable, TB or PB of data, etc. Then Timescale is great but it's definitely more work.
    - weinzierl 3y ago
      Thanks s lot, that is a really helpful reply. I'm curious on your concerns about InfluxDB. I mean not to bash them, but it really comes down to trade-offs and it's always good to know the things they don't emphasize in their marketing.
  - zX41ZdbW 3y ago
    There is no need for specialized time-series databases, because normal OLAP databases work better for time-series. I see it from my experience with ClickHouse. It might sound paradoxical, but specialized time-series databases, such as TimescaleDB or InfluxDB, are not good at time-series, compared to normal (boring) OLAP databases.
  - jononor 3y ago
    We used standard Postgres for 200k rows/day (100M rows/year) time series. Worked just fine, even without partitioning. Maybe use partitioning (on time) from the start though, it was a bit tedious to migrate to it later. Now handling 10x these rates, probably good for another 10-100x. We have TimeScaleDB extension enabled now, but not using that many of the features yet, mostly stock Postgres.
quickthrower2 3y ago
I am glad open software is eating the world. GNU/Linux, Python, JS, Postgres
- Roark66 3y ago
  Me too. You know what's another good sign times are changing? A fortune 500 company gave me a Linux laptop to WFH. Even a decade ago this would be extremely improbable.
  - vkazanov 3y ago
    Surprisingly, even though a relatively at a relatively big public company my teams and eng managers get some pressure to get macs instead of linux machines. And at some point I thought we're past that...
    - LightHugger 3y ago
      There's a lot of developers who like macs because they do not value control, access or freedom to use their hardware the way they want. If it weren't coming at the cost of everyone else in the industry, i wouldn't mind this preference, not everyone has to be a hacker. As is, apple's existence is a net negative.
      - manfre 3y ago
        Alternative take as a dev that only uses Apple products for work, the local hardware doesn't matter that much. We've almost circled back to dumb terminals with everything running on a network connected server.
        Timshel 3y ago
        That's kind of an expensive dumb terminal ;).
        3y ago
devjab 3y ago
I think it’s the natural evolution of software development in an age where decoupled architectures make more and more sense. I’ve been around long enough to see a few pendulum swings, but I’m not sure we’re ever going back to a world where the monolith makes sense again. I know some people will disagree with me, but I think there is a fundamental issue with the OOP line of thought, to no fault of the philosophy itself. It’s simply that people don’t handle complexity well in the real world. The way I see it, you have two modes of operating. The right way and whatever the heck you’re doing when it’s Thursday afternoon and you’ve slept poorly all week. Sure there are ways around this, but in most cases your code-reviewer and quality control isn’t going to be allocated enough time (and neither are you for that matter) to actually deny things that work. Which is how you end up with 9000 different tables, views, stored procedures and what not in your giant mess of a database. Which was sort of fine in a world where nobody cared about data security. But it’s almost impossible to build a “monolith” database that lives up to the modern legislation requirements, at least within the EU. So we’re basically living in a world where the way we’ve done databases for the past 50 years, doesn’t work for the business. Unless you separate everything into business related domains in small kingdoms with total sovereignty over who comes and goes. Which is basically what micro services are. At the same time we’re also living in a world where it’s often cheaper (and performant enough) to put 95% of what you do in a container using volumes for persistence. If SQLite was ready for it, or maybe, if all the ORMs were ready for SQLite, you could probably do 90% or your database needs in it. Since that’s not the case, Postgres reigns supreme because it’s the next best thing (yes, you’re allowed to think it’s better). I view Vector DBs sort of similarity to this. We operate almost all of our databases in containers, the one exception is for the one services everyone calls where the performance loss of the container is too much. Well, that’s not entirely true, our dataware house isn’t operated in containers, at least not by us, but I rarely interact with the BI side of things except for data architecture so I’m actually not sure how their external partners do Ops. Anyway, I think Postgres will do 90% or (y)our vector needs and then we will need dedicated vector databases for those last bits that won’t fit into the “everything” box. I don’t think this is bad either, maybe Oracle wanted it to be theirs, but then Oracle should not have Oracles governance. I’m sure they didn’t foresee this future when they bought MySQL though, I know I didn’t. Because who could’ve foreseen that that way we used to do Databases would become sort of obsolete because of containers and EU legislation… and other things?
thumbuddy 3y ago
Postgres allowing Json support destroyed so many marketing campaigns from non-technicaleadership "sick of all the money spent on SQL, we gotta try this nosql thing"
- marcosdumay 3y ago
  It's not only that postgres supports json (plenty of RDBMS do), it's that postgres has better nosql performance than the databases behind those campaigns.
einpoklum 3y ago
No it isn't. It's a transactions-focused DBMS that's no good for analytics. Orders of magnitude slower than available solutions and another order of magnitude or so slower than the academic state of the art, last time I checked (which admittedly was a few years back).
- sseagull 3y ago
  I guess I have never quite understood what is meant by analytics, and feel there is a different definition that is over my head. I do some “analytics” in a postgres database, with data and metrics stored in the same db, but I guess it’s not a huge amount - amount of data swamps the metrics by orders of magnitude. Seems to work ok for me, queries are 5ms or less, and it is only one thing to learn/deploy/maintain. It’s on a pretty powerful server, though.
  - tomnipotent 3y ago
    > what is meant by analytics Disk I/O. Most analytical workloads only need a subset of columns for the tables under query, but Postgres has to read in every column due to how data is organized. Columnar and hybrid columnar can significantly reduce disk I/O by orders of magnitude, which makes a big difference beyond a few dozen gigabytes. Even if all your data can fit into memory, Postgres will still be slower because it needs to loop over values for aggregations. Columnar databases can use SIMD instructions and better utilize the CPU cache across multiple aggregations in the same query. Postgres is also built for lookup queries and point updates, so every row needs a separate entry in b-tree indexes. Analytical databases can generally use sparse indexes that resolve to groups of rows, which means indexes take up both less space in memory and on disk that make range queries more efficient.
  - adgjlsfhk1 3y ago
    one of the biggest difference is whether you are doing more reads vs writes. reads want indices, writes don't. reads want denormalization, writes don't. reads often want column major, writes don't.
bhouston 3y ago
JSON in Postgres is less performant that MongoDB, that is what I found. Indices over JSON in Postgres leave a lot to be desired - so much so that it required us to design around it. My rule now is do not treat JSON in Postgress like MongoDB otherwise you will have a bad time (e.g. slowness.)
- victor106 3y ago
  I am surprised there’s no postgres extension to solve this.
- yowlingcat 3y ago
  If you need to index over data inside JSON, why not pull out the pieces that need to be indexed into normal relational columns? When I've used Postgres JSON fields in the past, I've used it for unstructured data that doesn't need to be indexed, as if there's anything that does need to be indexed, then it's important enough to extract out to a field for efficiency.
  - bhouston 3y ago
    Yes. We are saying the same thing. Treat json in Postgres as a little blob of unstructured data and do not design things in Postgres as you would in mongodb. Minimize json in Postgres or you will have a bad time.
- nesarkvechnep 3y ago
  Mongo costs more than the “slowness” of Postgres’ JSON.
0xDEF 3y ago
I am surprised that the "Postgres supports JSON so we don't need NoSQL systems" talking point is so common on Hacker News. MongoDB's marketing department is partially to blame for this because they marketed the negative trade-offs of MongoDB like non-SQL querying and lack of schema as "intentional" features. It was Google's BigTable paper (2006) and Amazon's Dynamo paper (2007) that led to the "NoSQL revolution" in the late 2000s. The goal was to make it easier to scale a lot of de-normalized data horizontally with the tradeoffs being lack of RDBMS-like querying and schema enforcement. In hindsight it was bizarre to market a whole category of horizontally scalable data systems based on one of their main negative trade-offs: Lack of SQL querying. Since then many of these data systems have added SQL-like querying.

rewmie 3y ago

> (...) and to be honest I never completely understood the need for specialized time series databases. The need for specialized time series databases is explained by the basis of any software development process: reuse ready-made high-level abstractions instead of having to reinvent the wheel, poorly. There are a few key features of time series databases that you either go without (i.e., data compression) or have to reinvent the wheel (querying, resolution-based retention periods, statistical summary of time periods,etc). You might live without those features,but in some applications there's a hefty price tag tied to not having them. If you adopt a time series database, you benefit from all those features without having to implement a single one of them. Perhaps that has no value to you, but it does have for the world.

starfallg 3y ago
I think what OP was getting at is why it has to be specialized. Time-series features can be implemented in a general purpose DB engine like postgres or mongo.
- nonameiguess 3y ago
  How? I just tried to look for time series in Postgres and it looks like TimescaleDB is the plugin you'd want? At least web search seems to love it. Their landing page is just marketing fluff and says next to nothing about their feature set. In a database that isn't column-oriented, how can they do automatic downsampling? Or do they just not?
  - hnmullany 3y ago
    You might want to check out their columnar compression & their continuous aggregate blog posts: https://www.timescale.com/blog/timescaledb-2-3-improving-columnar-compression-for-time-series-on-postgresql/ https://www.timescale.com/blog/timescaledb-2-3-improving-col... https://www.timescale.com/blog/massive-scale-for-time-series-workloads-introducing-continuous-aggregates-for-distributed-hypertables-in-timescaledb-2-5/ https://www.timescale.com/blog/massive-scale-for-time-series...
    - lordnacho 3y ago
      Have used it for finance, can recommend. No need for kdb, at least for now. Their Slack is very responsive too, top service.
- marginalia_nu 3y ago
  They kinda can't though, not without losing a lot of performance and capability. Time series databases should be viewed in the context of being a fairly niche product however. The archetypal use case is in finance where you have a constant firehose of tick data and trades across a wide array of instruments you need to be able to index instantaneously and aggregate easily. The sheer volume of data can't be overstated here. To make matters worse, the records are often both wide and sparsely populated. A non-specialized really DBMS doesn't cut it in this case. You need to build the DBMS from the ground up to cater to this sort of a usecase. Slapping a columnar backing store onto something existing does not cut it.
- nhourcard 3y ago
  Time-series features can be implemented in a general purpose DB engine, but the architecture of the database will be limiting for lots of use cases requiring performance, especially for high cardinality datasets. A columnar database engineered from the ground up for time-series should have better foundations to allows fast ingest (to the tune of million of rows/sec per server) and also be very efficient for time-based queries that can be done via languages such as SQL. Using QuestDB as an example: Data is stored in chronological order and is optimized for sequential ingestion with a timestamp component (re-ordering data on the fly if it comes out-of-order). The data is also partitioned by time. The InfluxDB Line Protocol is better suited for streaming type of ingest versus transactional inserts via Postgres.
weinzierl 3y ago
I'm curious to research if a dedicated TSDB is worth it for my project. Not to waste time could you recommend one that in your opinion would be worth it over just Postgres? I used InfluxDB a couple of years a ago but didn't really see the value, even though I loved the product. Should I give it another chance? Application is storage of a diverse set of (irregular) events. Lifecycle management and archival is more important than compression. Volume is moderate.
- kvmet 3y ago
  I'll spare you my rant on why I don't like InfluxDB lol. In the end it depends how much data you're dealing with. If you're only ingesting dozens to hundreds of records per second per writer you might actually like AWS Time stream. If you're dealing with a million + rows per second from a low number of writers (e.g. scientific data) then you probably want Influx or Timescale. If you want to host it yourself and you have less than 1TB of data then Influx is okay or if you want it cloud hosted then Influx may be a good option. If you need full control, scalable, TB or PB of data, etc. Then Timescale is great but it's definitely more work.
  - weinzierl 3y ago
    Thanks s lot, that is a really helpful reply. I'm curious on your concerns about InfluxDB. I mean not to bash them, but it really comes down to trade-offs and it's always good to know the things they don't emphasize in their marketing.
- zX41ZdbW 3y ago
  There is no need for specialized time-series databases, because normal OLAP databases work better for time-series. I see it from my experience with ClickHouse. It might sound paradoxical, but specialized time-series databases, such as TimescaleDB or InfluxDB, are not good at time-series, compared to normal (boring) OLAP databases.
- jononor 3y ago
  We used standard Postgres for 200k rows/day (100M rows/year) time series. Worked just fine, even without partitioning. Maybe use partitioning (on time) from the start though, it was a bit tedious to migrate to it later. Now handling 10x these rates, probably good for another 10-100x. We have TimeScaleDB extension enabled now, but not using that many of the features yet, mostly stock Postgres.

quickthrower2 3y ago

I am glad open software is eating the world. GNU/Linux, Python, JS, Postgres

Roark66 3y ago
Me too. You know what's another good sign times are changing? A fortune 500 company gave me a Linux laptop to WFH. Even a decade ago this would be extremely improbable.
- vkazanov 3y ago
  Surprisingly, even though a relatively at a relatively big public company my teams and eng managers get some pressure to get macs instead of linux machines. And at some point I thought we're past that...
  - LightHugger 3y ago
    There's a lot of developers who like macs because they do not value control, access or freedom to use their hardware the way they want. If it weren't coming at the cost of everyone else in the industry, i wouldn't mind this preference, not everyone has to be a hacker. As is, apple's existence is a net negative.
    - manfre 3y ago
      Alternative take as a dev that only uses Apple products for work, the local hardware doesn't matter that much. We've almost circled back to dumb terminals with everything running on a network connected server.
      - Timshel 3y ago
        That's kind of an expensive dumb terminal ;).
        3y ago

devjab 3y ago

I think it’s the natural evolution of software development in an age where decoupled architectures make more and more sense. I’ve been around long enough to see a few pendulum swings, but I’m not sure we’re ever going back to a world where the monolith makes sense again. I know some people will disagree with me, but I think there is a fundamental issue with the OOP line of thought, to no fault of the philosophy itself. It’s simply that people don’t handle complexity well in the real world. The way I see it, you have two modes of operating. The right way and whatever the heck you’re doing when it’s Thursday afternoon and you’ve slept poorly all week. Sure there are ways around this, but in most cases your code-reviewer and quality control isn’t going to be allocated enough time (and neither are you for that matter) to actually deny things that work. Which is how you end up with 9000 different tables, views, stored procedures and what not in your giant mess of a database. Which was sort of fine in a world where nobody cared about data security. But it’s almost impossible to build a “monolith” database that lives up to the modern legislation requirements, at least within the EU. So we’re basically living in a world where the way we’ve done databases for the past 50 years, doesn’t work for the business. Unless you separate everything into business related domains in small kingdoms with total sovereignty over who comes and goes. Which is basically what micro services are. At the same time we’re also living in a world where it’s often cheaper (and performant enough) to put 95% of what you do in a container using volumes for persistence. If SQLite was ready for it, or maybe, if all the ORMs were ready for SQLite, you could probably do 90% or your database needs in it. Since that’s not the case, Postgres reigns supreme because it’s the next best thing (yes, you’re allowed to think it’s better). I view Vector DBs sort of similarity to this. We operate almost all of our databases in containers, the one exception is for the one services everyone calls where the performance loss of the container is too much. Well, that’s not entirely true, our dataware house isn’t operated in containers, at least not by us, but I rarely interact with the BI side of things except for data architecture so I’m actually not sure how their external partners do Ops. Anyway, I think Postgres will do 90% or (y)our vector needs and then we will need dedicated vector databases for those last bits that won’t fit into the “everything” box. I don’t think this is bad either, maybe Oracle wanted it to be theirs, but then Oracle should not have Oracles governance. I’m sure they didn’t foresee this future when they bought MySQL though, I know I didn’t. Because who could’ve foreseen that that way we used to do Databases would become sort of obsolete because of containers and EU legislation… and other things?

thumbuddy 3y ago

Postgres allowing Json support destroyed so many marketing campaigns from non-technicaleadership "sick of all the money spent on SQL, we gotta try this nosql thing"

marcosdumay 3y ago
It's not only that postgres supports json (plenty of RDBMS do), it's that postgres has better nosql performance than the databases behind those campaigns.

einpoklum 3y ago

No it isn't. It's a transactions-focused DBMS that's no good for analytics. Orders of magnitude slower than available solutions and another order of magnitude or so slower than the academic state of the art, last time I checked (which admittedly was a few years back).

sseagull 3y ago
I guess I have never quite understood what is meant by analytics, and feel there is a different definition that is over my head. I do some “analytics” in a postgres database, with data and metrics stored in the same db, but I guess it’s not a huge amount - amount of data swamps the metrics by orders of magnitude. Seems to work ok for me, queries are 5ms or less, and it is only one thing to learn/deploy/maintain. It’s on a pretty powerful server, though.
- tomnipotent 3y ago
  > what is meant by analytics Disk I/O. Most analytical workloads only need a subset of columns for the tables under query, but Postgres has to read in every column due to how data is organized. Columnar and hybrid columnar can significantly reduce disk I/O by orders of magnitude, which makes a big difference beyond a few dozen gigabytes. Even if all your data can fit into memory, Postgres will still be slower because it needs to loop over values for aggregations. Columnar databases can use SIMD instructions and better utilize the CPU cache across multiple aggregations in the same query. Postgres is also built for lookup queries and point updates, so every row needs a separate entry in b-tree indexes. Analytical databases can generally use sparse indexes that resolve to groups of rows, which means indexes take up both less space in memory and on disk that make range queries more efficient.
- adgjlsfhk1 3y ago
  one of the biggest difference is whether you are doing more reads vs writes. reads want indices, writes don't. reads want denormalization, writes don't. reads often want column major, writes don't.

bhouston 3y ago

JSON in Postgres is less performant that MongoDB, that is what I found. Indices over JSON in Postgres leave a lot to be desired - so much so that it required us to design around it. My rule now is do not treat JSON in Postgress like MongoDB otherwise you will have a bad time (e.g. slowness.)

victor106 3y ago
I am surprised there’s no postgres extension to solve this.
yowlingcat 3y ago
If you need to index over data inside JSON, why not pull out the pieces that need to be indexed into normal relational columns? When I've used Postgres JSON fields in the past, I've used it for unstructured data that doesn't need to be indexed, as if there's anything that does need to be indexed, then it's important enough to extract out to a field for efficiency.
- bhouston 3y ago
  Yes. We are saying the same thing. Treat json in Postgres as a little blob of unstructured data and do not design things in Postgres as you would in mongodb. Minimize json in Postgres or you will have a bad time.
nesarkvechnep 3y ago
Mongo costs more than the “slowness” of Postgres’ JSON.

0xDEF 3y ago

I am surprised that the "Postgres supports JSON so we don't need NoSQL systems" talking point is so common on Hacker News. MongoDB's marketing department is partially to blame for this because they marketed the negative trade-offs of MongoDB like non-SQL querying and lack of schema as "intentional" features. It was Google's BigTable paper (2006) and Amazon's Dynamo paper (2007) that led to the "NoSQL revolution" in the late 2000s. The goal was to make it easier to scale a lot of de-normalized data horizontally with the tradeoffs being lack of RDBMS-like querying and schema enforcement. In hindsight it was bizarre to market a whole category of horizontally scalable data systems based on one of their main negative trade-offs: Lack of SQL querying. Since then many of these data systems have added SQL-like querying.