Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Looking Ahead to Postgres 19

snowflake.com

Post reply on HN
Print view View original

91–100 of 138 posts

Re: Looking Ahead to Postgres 19

#91

Post by vovavili »

sega_sai wrote: post #36

Speaking as long-term (>15 years) user of Postgres in science, I am getting worried about the lack of columnar type of storage in Postgresql. As the datasets become bigger and bigger, the limitations of PG's storage are becoming more and more significant. I know there are various extensions (i.e. cetus) that may offer such functionality, but then you depend on that extension being supported in the future, as well add…

From a computer science perspective, I'm not exactly sure how a transactional database would implement a columnar type. Postgres + CDC + an actual analytical database like ClickHouse would be your strongest bet at scale.

Re: Looking Ahead to Postgres 19

#92

Post by masklinn »

tyre wrote: post #38
sega_sai wrote: post #36

Speaking as long-term (>15 years) user of Postgres in science, I am getting worried about the lack of columnar type of storage in Postgresql. As the datasets become bigger and bigger, the limitations of PG's storage are becoming more and more significant. I know there are various extensions (i.e. cetus) that may offer such functionality, but then you depend on that extension being supported in the future, as well add…

You might be using the wrong database if that’s what you’re hoping for. Columnar databases are a separate category. It’s like saying that you’re getting worried Apple doesn’t sell washing machines.

> You might be using the wrong database if that’s what you’re hoping for. Columnar databases are a separate category.

I don't want a columnar database, I have a few tables which would be excellent fits for columnar storage. And the relational model doesn't say anything about the way data is stored.

Re: Looking Ahead to Postgres 19

#93

Post by branko_d »

d125q wrote: post #24
aljgz wrote: post #14

I've used Postgres, Oracle, MsSql Server, and MySql in serious projects, no extensive experience with Sqlite, which I know is an amazing player. These days, I do myself a favor and always avoid Oracle and MySql/MariaDB. Postgres is amazing, and the two big things I wished it had: 1. lightweight connection; connection bouncers improve the situation, but you still have an unreasonably high memory footprint per concurre…

Care to share some examples where SQL Server's indexed views would shine? In my eyes they're similar to triggers, which incur a high performance overhead in OLTP systems and are shunned by developers. In OLAP systems custom ETL code will likely outperform them.

In OLTP, it's very difficult to guarantee correctness with triggers (very easy to have a race condition in concurrent environment). On a flip side, materialized views tend to lock more than you'd expect, especially when aggregates are involved.

The sweet spot is if you have a read-mostly database and use SNAPSHOT transaction isolation for the readers (which is SQL Server's implementation of MVCC). That way, writers may still block writers, but writers can never block readers, even when indexed views are being maintained.

Another neat trick is to "abuse" indexed views as multi-table CHECKs. The idea is to make a JOIN that would produce duplicated rows (and fail the indexed view's key) if some multi-table condition is not met.

Re: Looking Ahead to Postgres 19

#94

Post by johnisgood »

rmunn wrote: post #66
ksec wrote: post #29

Earlier quoted context omitted.

>I do myself a favor and always avoid Oracle and MySql/MariaDB. So what's wrong with MySQL or MariaDB?

And although you didn't ask, I'll list what's wrong with Oracle. It's very simple. Oracle treats empty strings as being NULL . Anyone who's never used Oracle before in their life is probably wondering if I'm making it up. I'm not. In Oracle, inserting '' in a VARCHAR column is exactly the same as inserting NULL. And if there's a NOT NULL column, you're not allowed to store the empty string in there. Which means that…

> Oracle treats empty strings as being NULL.

That is ridiculous. Do not they know the difference between "" and NULL?

Re: Looking Ahead to Postgres 19

#95

Post by zepearl »

aljgz wrote: post #14

I've used Postgres, Oracle, MsSql Server, and MySql in serious projects, no extensive experience with Sqlite, which I know is an amazing player. These days, I do myself a favor and always avoid Oracle and MySql/MariaDB. Postgres is amazing, and the two big things I wished it had: 1. lightweight connection; connection bouncers improve the situation, but you still have an unreasonably high memory footprint per concurre…

> Synchronously updated materialized views...

Oh yes, I'd love them too (if you're referring to, in Oracle slang, "...update on commit") - and it would be cool to have as well the option for a lazy update ("on demand" by taking into consideration only the records that have been changed since the last refresh, to handle multiple updates in a single pass - not sure how Oracle can achieve that technically...). This would be in my opinion a fantastic added functionality compared to basically all other (OLTP?) opensource DBs.

And: I'm really curious about the "OrioleDB" project... ( https://github.com/orioledb/orioledb/releases ) as a few years ago I was struggling a lot with "vacuum" of a kind-of-temporary table that had quite high amounts of continuous random inserts & deletes (problem solved by accumulating more changes in RAM before flushing them to the table therefore increasing amount of rows changed per "page", but I had to sweat a lot to find a good balance...).

Re: Looking Ahead to Postgres 19

#96

Post by gandreani »

zepearl wrote: post #95
aljgz wrote: post #14

I've used Postgres, Oracle, MsSql Server, and MySql in serious projects, no extensive experience with Sqlite, which I know is an amazing player. These days, I do myself a favor and always avoid Oracle and MySql/MariaDB. Postgres is amazing, and the two big things I wished it had: 1. lightweight connection; connection bouncers improve the situation, but you still have an unreasonably high memory footprint per concurre…

> Synchronously updated materialized views... Oh yes, I'd love them too (if you're referring to, in Oracle slang, "...update on commit") - and it would be cool to have as well the option for a lazy update ("on demand" by taking into consideration only the records that have been changed since the last refresh, to handle multiple updates in a single pass - not sure how Oracle can achieve that technically...). This woul…

Clickhouse offers the same through https://clickhouse.com/docs/materialized-view/incremental-ma.... I personally LOVE this feature and concur with the gp that they're really elegant solutions to difficult problems.

Just to give an example, I ingest otel trace spans individually and in a materialized view calculate the total duration of the whole trace among other things.

Re: Looking Ahead to Postgres 19

#97

Post by brightball »

aljgz wrote: post #14

I've used Postgres, Oracle, MsSql Server, and MySql in serious projects, no extensive experience with Sqlite, which I know is an amazing player. These days, I do myself a favor and always avoid Oracle and MySql/MariaDB. Postgres is amazing, and the two big things I wished it had: 1. lightweight connection; connection bouncers improve the situation, but you still have an unreasonably high memory footprint per concurre…

> 2. Synchronously updated materialized views (Sql Server calls them indexed views). These are incredible tools in complex data situations. I saw a project struggle with complex technical implementations that would be elegant, trivial and always correct with indexed views.

I believe you can do this with the pg-trickle extension.

https://github.com/trickle-labs/pg-trickle

Re: Looking Ahead to Postgres 19

#98

Post by olavgg »

bob1029 wrote: post #31
aljgz wrote: post #14

I've used Postgres, Oracle, MsSql Server, and MySql in serious projects, no extensive experience with Sqlite, which I know is an amazing player. These days, I do myself a favor and always avoid Oracle and MySql/MariaDB. Postgres is amazing, and the two big things I wished it had: 1. lightweight connection; connection bouncers improve the situation, but you still have an unreasonably high memory footprint per concurre…

SQLite and MSSQL are my two solutions for relational storage problems. If I am going to use a "free" provider, SQLite is impossible to beat. They cover a majority of use cases today. SQLite starts to fall apart with backup, replication and tooling. If I am on the hook for things like system availability and disaster recovery, I don't have a problem spending money to cover my ass. If I am going to pay any amount of mo…

The MSSQL developer experience is ok, until you want to pipe the backup over ssh. Then you understand that the Linux support is a joke, with no stdout(https://linux.die.net/man/3/stdout) support.

Re: Looking Ahead to Postgres 19

#99

Post by jhoechtl »

sega_sai wrote: post #36

Speaking as long-term (>15 years) user of Postgres in science, I am getting worried about the lack of columnar type of storage in Postgresql. As the datasets become bigger and bigger, the limitations of PG's storage are becoming more and more significant. I know there are various extensions (i.e. cetus) that may offer such functionality, but then you depend on that extension being supported in the future, as well add…

In this regard I do find DuckDB amazing.

Re: Looking Ahead to Postgres 19

#100

Post by jhoechtl »

tudorg wrote: post #77
sega_sai wrote: post #36

Speaking as long-term (>15 years) user of Postgres in science, I am getting worried about the lack of columnar type of storage in Postgresql. As the datasets become bigger and bigger, the limitations of PG's storage are becoming more and more significant. I know there are various extensions (i.e. cetus) that may offer such functionality, but then you depend on that extension being supported in the future, as well add…

A bit of a shameless plug, but I've been working on this for a few months in the form of an extension: https://github.com/xataio/deltax When I started I thought there's too much inherent overhead in using Postgres tables for storage and using the Postgres executor, so figured it would be pretty cool to match Timescale in performance. I didn't think it would be possible to get close to dedicated analytical DBs. But as…

Thanks for sharing. That looks promising, good luck!
Post reply on HN