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

What’s so exciting about Postgres?

changelog.com

Post reply on HN
Print view View original

41–50 of 137 posts

Re: What’s so exciting about Postgres?

#41

Post by anarazel »

shay_ker wrote: post #35
anarazel wrote: post #25

Earlier quoted context omitted.

> Surprised they didn't cover transaction id wraparound, which has caused more than a few public outages at scale. There have been some significant improvements in the last couple years - most importantly the introduction of the freeze map in 9.6. But also quite a sprinkling of other incremental improvements. It can obviously still be a problem, but it's not as pronounced as it once was.

can you elaborate?

> can you elaborate?

On what exactly?

The freeze map bit I referenced above is the following commit: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...

    Commit a892234f830e832110f63fc0a2afce2fb21d1584 gave us enough
    infrastructure to avoid vacuuming pages where every tuple on the
    page is already frozen.  So, replace the notion of a scan_all or
    whole-table vacuum with the less onerous notion of an "aggressive"
    vacuum, which will pages that are all-visible, but still skip those
    that are all-frozen.
    
    This should greatly reduce the cost of anti-wraparound vacuuming
    on large clusters where the majority of data is never touched
    between one cycle and the next, because we'll no longer have to
    read all of those pages only to find out that we don't need to
    do anything with them.
This means that an anti-wraparound vacuum (automatically started, even when autovacuum is disabled) is cheaper than it used to be. Still not necessarily cheap, as indexes still need to be scanned (but see below).

Some of the additional changes were (reverse chronological order):

* 2020-03-28 - "Trigger autovacuum based on number of INSERTs" - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...

This is important because it will reduce the cost of an eventual index wraparound, as there will be less work in a later anti-wraparound vacuum

* 2020-01-20 - "Allow vacuum command to process indexes in parallel." - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...

As index processing can be expensive (no equivalent to scanning only changed parts of table), processing them in parallel can greatly reduce the time for a vacuum. Note that this isn't yet done by autovacuum.

* 2019-04-04 - "Allow VACUUM to be run with index cleanup disabled." - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...

This can be extremely useful for manual vacuum when getting close to a wraparound, since the index processing step is not necessary to stave of wraparound.

* 2018-04-04 - "Skip full index scan during cleanup of B-tree indexes when possible" - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...

This can make vacuums for pretty clean tables vastly cheaper.

There's also a number of changes that make indexes smaller / less likely to bloat. That in turn makes vacuuming more efficient. E.g.

* https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...

* https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...

Edit: formatting (gah, why is HN formatting so limited)

Re: What’s so exciting about Postgres?

#42

Post by pricechild »

baybal2 wrote: post #38
Animats wrote: post #11

Databases shouldn't be exciting. They should just work . The main reason webcrap works is because the databases underneath work. If the Javascript crowd had to manage data storage, nothing persistent would work reliably.

PG steamrolls every other general purpose database, and has tons of extensions, and frontends so you can use it in any database paradigm. With 30m minutes of time, you can turn it into a document db, log/time series db, columnar, graph, key value, whatever

I bet we'd both seriously consider (if not prefer) sqlite over postgres in a bunch of scenarios?

Re: What’s so exciting about Postgres?

#43

Post by [deleted] »

axegon_ wrote: post #27

Apart from being the most mature relational database, I would argue the code that runs it. It's pure poetry. I am yet to see code this clean anywhere. Just opening any source code file at random[1] will show you exactly what I mean. [1] https://github.com/postgres/postgres/blob/master/src/backend...

[deleted]

Re: What’s so exciting about Postgres?

#44

Post by soneca »

noir_lord wrote: post #39
systemvoltage wrote: post #19

Earlier quoted context omitted.

It is often downvoted to talk about the "javascript crowd" on HN, but I've worked in software industry long enough to unequivocally and with certainty say that it totally lives up to the cliches. It never gets old to scold people that are not serious, disciplined and principled in their endeavors that affect the rest of the world. Javascript community absolutely deserves the avalanche of criticisms they face. It is t…

I'm seeing this in action now, the difference between the backend code and the frontend code is stark and the backend code isn't up to my standard either (and my standards aren't that high, I know I'm merely competent for many places). It just seems to be the nature of the web side of things, lots of sloppiness. The devs are smart enough, they just don't think and it shows. That or they use something but clearly don'…

Should I assume from your comment that you never saw code worth improving other than those written by JavaScript developers?

Re: What’s so exciting about Postgres?

#45

Post by edoceo »

noir_lord wrote: post #39
systemvoltage wrote: post #19

Earlier quoted context omitted.

It is often downvoted to talk about the "javascript crowd" on HN, but I've worked in software industry long enough to unequivocally and with certainty say that it totally lives up to the cliches. It never gets old to scold people that are not serious, disciplined and principled in their endeavors that affect the rest of the world. Javascript community absolutely deserves the avalanche of criticisms they face. It is t…

I'm seeing this in action now, the difference between the backend code and the frontend code is stark and the backend code isn't up to my standard either (and my standards aren't that high, I know I'm merely competent for many places). It just seems to be the nature of the web side of things, lots of sloppiness. The devs are smart enough, they just don't think and it shows. That or they use something but clearly don'…

Hello fellow "merely competent" engineer. I recommend we both remove "merely" from our title. Inexperience grows from the bottom, I've gotten "better" by simply being too stubborn to get worse. Keep up the mediocre work!

Re: What’s so exciting about Postgres?

#46

Post by erokar »

systemvoltage wrote: post #19
Animats wrote: post #11

Databases shouldn't be exciting. They should just work . The main reason webcrap works is because the databases underneath work. If the Javascript crowd had to manage data storage, nothing persistent would work reliably.

It is often downvoted to talk about the "javascript crowd" on HN, but I've worked in software industry long enough to unequivocally and with certainty say that it totally lives up to the cliches. It never gets old to scold people that are not serious, disciplined and principled in their endeavors that affect the rest of the world. Javascript community absolutely deserves the avalanche of criticisms they face. It is t…

Typical bitter backend developer spew. Works with Java or C# and hasn't explored much more than OO and procedural. Threatened by anything new, creative or explorative. Stuck in his ways and prefers to do mundane, uncreative and repetetive tasks, like writing boilerplate to get stuff in and out of a database. Soon to be automated away by serverless.

Re: What’s so exciting about Postgres?

#47

Post by noir_lord »

soneca wrote: post #44
noir_lord wrote: post #39

Earlier quoted context omitted.

I'm seeing this in action now, the difference between the backend code and the frontend code is stark and the backend code isn't up to my standard either (and my standards aren't that high, I know I'm merely competent for many places). It just seems to be the nature of the web side of things, lots of sloppiness. The devs are smart enough, they just don't think and it shows. That or they use something but clearly don'…

Should I assume from your comment that you never saw code worth improving other than those written by JavaScript developers?

No.

Re: What’s so exciting about Postgres?

#48

Post by craigkerstiens »

axegon_ wrote: post #27

Apart from being the most mature relational database, I would argue the code that runs it. It's pure poetry. I am yet to see code this clean anywhere. Just opening any source code file at random[1] will show you exactly what I mean. [1] https://github.com/postgres/postgres/blob/master/src/backend...

Fully agree with this. I once heard it stated by someone "I don't like writing C, but I don't mind at all writing Postgres C". It is definitely a quality codebase and the credit goes to a pretty small set of maintainers over the years.

Re: What’s so exciting about Postgres?

#49

Post by jschwartzi »

erokar wrote: post #46
systemvoltage wrote: post #19

Earlier quoted context omitted.

It is often downvoted to talk about the "javascript crowd" on HN, but I've worked in software industry long enough to unequivocally and with certainty say that it totally lives up to the cliches. It never gets old to scold people that are not serious, disciplined and principled in their endeavors that affect the rest of the world. Javascript community absolutely deserves the avalanche of criticisms they face. It is t…

Typical bitter backend developer spew. Works with Java or C# and hasn't explored much more than OO and procedural. Threatened by anything new, creative or explorative. Stuck in his ways and prefers to do mundane, uncreative and repetetive tasks, like writing boilerplate to get stuff in and out of a database. Soon to be automated away by serverless.

Good luck running serverless code in a lightbulb.

Re: What’s so exciting about Postgres?

#50

Post by api »

pricechild wrote: post #42
baybal2 wrote: post #38

Earlier quoted context omitted.

PG steamrolls every other general purpose database, and has tons of extensions, and frontends so you can use it in any database paradigm. With 30m minutes of time, you can turn it into a document db, log/time series db, columnar, graph, key value, whatever

I bet we'd both seriously consider (if not prefer) sqlite over postgres in a bunch of scenarios?

SQLite is amazing, and “lite” is really not fair. I’ve seen it handle 1tb+ with at least decent performance.

It’s not a replacement for a real DB but it is damn close.

Post reply on HN