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

Postgres is eating the database world

medium.com

Post reply on HN
Print view View original

41–50 of 147 posts

Re: Postgres is eating the database world

#41

Post by elp »

patrickdavey wrote: post #32

I have a handful of sites I run on a VPS with a basic setup, including MySQL. One thing I've always liked about MySQL is that it pretty much looks after itself, whereas with Postgres I've had issues before doing upgrades (this was with brew though) and I'm not clear on whether it looks after itself for vacuuming etc. Should I just give it a go the next time I'm upgrading? It does seem like a tool I need to get famili…

Its got a lot better over the years.

25+ years ago MySQL was fast and easy to admin but didn't have rollback and a bunch of other features. At the same time Postgres had the features but was horrible for performance and usability. Those days are LONG gone. Mysql obviously has all the features and PG is great to admin and the auto-vacuum works well out of the box.

I run a bunch of clusters of pg servers around the world and they need almost no maintenance. In place upgrades without needing to go the dump/restore route work well, 5 minutes on a TB sized database, just make very VERY sure you do a reindex afterwards or you will be in a world of pain.

Re: Postgres is eating the database world

#42

Post by Vonng »

lmm wrote: post #31

Postgres is still single-node-first, and while Citus exists I'm skeptical that it can ever become as easy to administer as a true HA-first datastore. For me the reason to use something like Cassandra or Kafka was never "big data" per se, it was having true master-master fault tolerance out of the box in a way that worked with everything.

Patroni has native HA support for citus horizontal cluster since v3. Which means your can create a HA citus cluster as simple as: https://pigsty.io/docs/pgsql/config/#citus-cluster

Re: Postgres is eating the database world

#43

Post by dbacar »

TOMDM wrote: post #19

Postgres is such a great tool. The feature I'd love to see added that has been kicking around the mailing list for ages now would be incremental view maintenance. Being able to keep moderately complex analysis workloads fresh in realtime would be such a boon.

Did you used this in production, how do you rate it?

Re: Postgres is eating the database world

#44

Post by iTokio »

TOMDM wrote: post #19

Postgres is such a great tool. The feature I'd love to see added that has been kicking around the mailing list for ages now would be incremental view maintenance. Being able to keep moderately complex analysis workloads fresh in realtime would be such a boon.

Is this feature even possible? How can the database unravel a complex queries with derived data to minimal updates from its normalized parts?

The easy way is to rebuild everything if any “from table” as been modified.

The manual way is to create triggers that perform the minimal updates.

Re: Postgres is eating the database world

#45

Post by benjaminwootton »

iTokio wrote: post #44
TOMDM wrote: post #19

Postgres is such a great tool. The feature I'd love to see added that has been kicking around the mailing list for ages now would be incremental view maintenance. Being able to keep moderately complex analysis workloads fresh in realtime would be such a boon.

Is this feature even possible? How can the database unravel a complex queries with derived data to minimal updates from its normalized parts? The easy way is to rebuild everything if any “from table” as been modified. The manual way is to create triggers that perform the minimal updates.

There are lots of projects who have managed to achieve this.

Streaming frameworks like Kafka Streams and Flink have incrementally updating tables in memory.

Materialize is built around the concept with a Postgres compatible API.

ClickHouse materialized views act like insert triggers which update when the base table is updated.

Re: Postgres is eating the database world

#46

Post by shpongled »

iTokio wrote: post #44
TOMDM wrote: post #19

Postgres is such a great tool. The feature I'd love to see added that has been kicking around the mailing list for ages now would be incremental view maintenance. Being able to keep moderately complex analysis workloads fresh in realtime would be such a boon.

Is this feature even possible? How can the database unravel a complex queries with derived data to minimal updates from its normalized parts? The easy way is to rebuild everything if any “from table” as been modified. The manual way is to create triggers that perform the minimal updates.

I believe differential data flow techniques (Materialize, Noria) enable this kind of stuff

Re: Postgres is eating the database world

#48

Post by jesterson »

elp wrote: post #41
patrickdavey wrote: post #32

I have a handful of sites I run on a VPS with a basic setup, including MySQL. One thing I've always liked about MySQL is that it pretty much looks after itself, whereas with Postgres I've had issues before doing upgrades (this was with brew though) and I'm not clear on whether it looks after itself for vacuuming etc. Should I just give it a go the next time I'm upgrading? It does seem like a tool I need to get famili…

Its got a lot better over the years. 25+ years ago MySQL was fast and easy to admin but didn't have rollback and a bunch of other features. At the same time Postgres had the features but was horrible for performance and usability. Those days are LONG gone. Mysql obviously has all the features and PG is great to admin and the auto-vacuum works well out of the box. I run a bunch of clusters of pg servers around the wor…

> PG is great to admin

What do you use for it? Is there anything like phpmyadmin for postgres with similar simplicity?

Re: Postgres is eating the database world

#49

Post by LAC-Tech »

thinkerswell wrote: post #26

Is there an extension that can make it compete with TigerBeetle for transaction processing speed?

That would surely be impossible. TB is designed from the ground up to do one thing and do that one thing well. Postgres is a swiss army knife, and TB is a knife.

Re: Postgres is eating the database world

#50

Post by juliusdavies »

Izkata wrote: post #17
teaearlgraycold wrote: post #13

Earlier quoted context omitted.

Yup, used a nice EXPLAIN GUI tool as well to try and help.

Every time stuff like this comes up I wonder how much the people having issues would be willing to share - because every time I've fought with the postgres query planner, it eventually turned out what I wanted to do had massively worse performance* because of something I didn't take into account that postgres did. And each time, once I learned what that thing was, I was able to fix it the right way and get the query…

I always set this:

SET enable_seqscan = OFF;

Post reply on HN