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

Index bloat reduced in PostgreSQL v14

cybertec-postgresql.com

Post reply on HN
Print view View original

31–40 of 93 posts

Re: Index bloat reduced in PostgreSQL v14

#31

Post by eloff »

sudhirj wrote: post #23
eloff wrote: post #14

Earlier quoted context omitted.

Why would you need multi master to be "always on"?

If for always on or High Availability setups you want two DBs running that are in constant communication, with every write being committed on both machines synchronously - so if one of them goes down the other can take its place immediately with no downtime. Postgres has no built in support yet, but tools like STOLON exist.

Postgres doesn't support synchronous replication yet? It's had it since 9.1.

I think you're referring to something else.

Re: Index bloat reduced in PostgreSQL v14

#32

Post by saurik »

srgpqt wrote: post #16

It’s unfortunate pg is unable to maintain large numbers of connections open, necessitating pgbouncer in those setups.

Is this really such a problem? I feel like you'd want pgbouncer anyway in order to mitigate connection establishment latency. Like, in my infrastructure I generally have not one but two layers of pgbouncer, one on either side of the network, so the topology of underlying network connections could be reasonably static.

Re: Index bloat reduced in PostgreSQL v14

#33

Post by Zababa »

smt88 wrote: post #28
Zababa wrote: post #19

Earlier quoted context omitted.

Speaking about mistakes, do you have any good resources on Postgres to ensure that you don't make the same kind of mistakes?

Unfortunately database design and database administration are entire fields of expertise unto themselves, and I've never seen a resource that covers 100% of the need-to-know info. Oracle, SQL Server, Postgres, and MySQL are all massive, complex, and old, and they have lots of hidden footguns in them. You should start by knowing how other people design the type of DB you're building. A reporting DB is going to work ve…

Thank you for all of these. I find database-related knowledge usually hard to find compared to programming-related knowledge, so this helps a lot.

Re: Index bloat reduced in PostgreSQL v14

#34

Post by jasfi »

osigurdson wrote: post #22
srgpqt wrote: post #16

It’s unfortunate pg is unable to maintain large numbers of connections open, necessitating pgbouncer in those setups.

This appears to be improving as well - especially idle connections. https://pganalyze.com/blog/postgres-14-performance-monitorin...

Postgres is improving rapidly. I started developing my own DB a few years ago, but decided to do something else. One of the main reasons was because of the progress this DB is making on all fronts.

Re: Index bloat reduced in PostgreSQL v14

#35

Post by eloff »

smt88 wrote: post #24
eloff wrote: post #13

Earlier quoted context omitted.

In what way did they make basic mistakes?

My DB knowledge is too patchy to give a good summary, but it was extensively discussed on HN several times[1]. In general it seems like they could have solved all of their problems within Postgres, but someone at Uber seemed to just know MySQL better and want to switch. They also made other major changes (like removing schemas from certain data stores) that likely gave them more of an advantage than switching could h…

I remember the article and the discussion, but the fundamental issue seemed to be the difference between MySQL and Postgres indices and the different trade-offs made. Postgres indices are better for read heavy workloads, and MySQL for write heavy. I don't see how this is representative of them not knowing what they are doing. Frankly it's condescending to think you understand their engineering problems better than they do.

Re: Index bloat reduced in PostgreSQL v14

#37

Post by jandrewrogers »

edoceo wrote: post #2

I'm sure we'll get a bunch of (well deserved) praise for PG here but, does anyone have a case where PG really shit the bed? (Besides the Uber one) (which is its own long thread)

PostgreSQL is robust across a wide range of applications but it does have some architectural sharp edges that can cause serious operational problems in practice if you run into them. Most of these only show up at scale. Only a few do not have any viable workaround in practice.

The worst one, in my experience, is that the statistics collector is architecturally broken for some large tables, which can cause the query planner to go haywire. As a consequence, query performance can be erratic, varying by a couple orders of magnitude each time the table is analyzed. There are no workarounds in many cases, and the architectural defect that causes it is decidedly non-trivial to address (otherwise it would have been fixed years ago).

This is the only defect that consistently rears its head in operational environments in my experience. As data volumes grow, more Postgres instances are experiencing this problem. At a minimum there needs to be a way to easily override the query planner when it goes insane.

Re: Index bloat reduced in PostgreSQL v14

#38

Post by thih9 »

edoceo wrote: post #2

I'm sure we'll get a bunch of (well deserved) praise for PG here but, does anyone have a case where PG really shit the bed? (Besides the Uber one) (which is its own long thread)

> (Besides the Uber one) (which is its own long thread)

I’m out of the loop; does anyone have a link or could anyone summarize?

Re: Index bloat reduced in PostgreSQL v14

#39

Post by tomnipotent »

saurik wrote: post #32
srgpqt wrote: post #16

It’s unfortunate pg is unable to maintain large numbers of connections open, necessitating pgbouncer in those setups.

Is this really such a problem? I feel like you'd want pgbouncer anyway in order to mitigate connection establishment latency. Like, in my infrastructure I generally have not one but two layers of pgbouncer, one on either side of the network, so the topology of underlying network connections could be reasonably static.

You'd be surprised how websites are out there running Wordpress/phpBB and managing 100+ concurrent connections with per-process pooling.

Re: Index bloat reduced in PostgreSQL v14

#40

Post by desdiv »

thih9 wrote: post #38
edoceo wrote: post #2

I'm sure we'll get a bunch of (well deserved) praise for PG here but, does anyone have a case where PG really shit the bed? (Besides the Uber one) (which is its own long thread)

> (Besides the Uber one) (which is its own long thread) I’m out of the loop; does anyone have a link or could anyone summarize?

I believe they are referring to this: https://eng.uber.com/postgres-to-mysql-migration/

Though keep in mind that this was back in 2016 and Postgres has changed a lot since then.

Previous HN discussions:

https://news.ycombinator.com/item?id=17280239

https://news.ycombinator.com/item?id=12166585

https://news.ycombinator.com/item?id=26283348

Post reply on HN