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

71–80 of 93 posts

Re: Index bloat reduced in PostgreSQL v14

#71

Post by cutemonster »

whoknowswhat11 wrote: post #55
scott113341 wrote: post #27

Earlier quoted context omitted.

I’m a huge fan of Postgres. This one is “user error”, but we still got bit pretty hard. A query plan changed, on a frequently-run query (~1k/sec) on a large table (~2B rows) without warning. Went from sub-millisecond to multi-second. The PG query planner is generally very good, but also very opaque. The statistics collected during an ANALYZE and used by the planner are subject to some significant caveats. Essentially…

Aurora PostgreSQL has something called Query Plan Management - which I like - is meant to address this type of issue especially for large tables that have key queries that you could blow up DB basically if they go haywire in planning. Would def be a feature that would be nice to see in PostgreSQL itself.

It'd be nice if one could tell pg to only change its query plans during certain change windows, say, the first Saturday each month, with on call staff ready.

And if there was a "Revert to o old plans" button

Re: Index bloat reduced in PostgreSQL v14

#72

Post by user5994461 »

potamic wrote: post #42
srgpqt wrote: post #16

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

When does a database need to maintain large number of open connections? You really don't need a lot of connections to serve high throughput. The database system typically becomes a bottleneck before the connections do. A good client-side pooling implementation will manage and limit connection usage.

Modern web frameworks generate a lot of connections.

Let's say, a typical application may run on 4 instances each one with 16 cores. That means a total of 64 processes because one process per core.

Each process opens 16 connections to the database because bad configuration or default to the number of cores. We're now facing a total of 1024 SQL connections out-of-the-box for nothing.

That's the reason databases are commonly subjected to insane amounts of connections.

Re: Index bloat reduced in PostgreSQL v14

#73

Post by pezezin »

eloff wrote: post #31
sudhirj wrote: post #23

Earlier quoted context omitted.

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.

PostgreSQL has master-slave replication (one read/write master, and multiple read-only standbys), the other users are talking about multi-master replication (all the databases are read/write).

Re: Index bloat reduced in PostgreSQL v14

#74

Post by fffweiuh332 »

srgpqt wrote: post #16

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

PostgreSQL made a poor decision to use processes instead of threads for connections. Processes are much more expensive, so you can't create as many connections as in other DBMSes.

Re: Index bloat reduced in PostgreSQL v14

#75

Post by moonchrome »

avereveard wrote: post #67
potamic wrote: post #42

Earlier quoted context omitted.

When does a database need to maintain large number of open connections? You really don't need a lot of connections to serve high throughput. The database system typically becomes a bottleneck before the connections do. A good client-side pooling implementation will manage and limit connection usage.

When you have junior devs insisting in using orm, then the connection setup costs become an issue

Don't ORMs use connection pooling by default to avoid these issues ?

Re: Index bloat reduced in PostgreSQL v14

#76

Post by sudhirj »

pezezin wrote: post #73
eloff wrote: post #31

Earlier quoted context omitted.

Postgres doesn't support synchronous replication yet? It's had it since 9.1. I think you're referring to something else.

PostgreSQL has master-slave replication (one read/write master, and multiple read-only standbys), the other users are talking about multi-master replication (all the databases are read/write).

Yup. The synchronous replication described in the docs will allow the configuration of a hot standby, but I don’t think it allows writing to the standby, which is what true master-master does.

Re: Index bloat reduced in PostgreSQL v14

#77

Post by Dylan16807 »

jashmatthews wrote: post #61
Dylan16807 wrote: post #57

Earlier quoted context omitted.

Why does it need up to date statistics to decide not to change anything? I mean, if you could freeze statistics entirely wouldn't that fix this problem?

Because the contents of the table is changing the statistics are becoming out of date.

That doesn't answer the question at all.

The old statistics said to use the index.

If it's still using old statistics, why does the behavior change?

Re: Index bloat reduced in PostgreSQL v14

#78

Post by eloff »

pezezin wrote: post #73
eloff wrote: post #31

Earlier quoted context omitted.

Postgres doesn't support synchronous replication yet? It's had it since 9.1. I think you're referring to something else.

PostgreSQL has master-slave replication (one read/write master, and multiple read-only standbys), the other users are talking about multi-master replication (all the databases are read/write).

I'm aware. What I'm asking is why multi master is required. You can have a read only slave that is promoted to the master. It's that little bit of downtime during failover in question? Doesn't pgpool make that possible without downtime?

If you use multi master and DNS failover you still have partial downtime for every session routed to the unhealthy master. That doesn't seem to solve the problem, just a little better.

Re: Index bloat reduced in PostgreSQL v14

#79

Post by mamcx »

ezst wrote: post #69
mamcx wrote: post #11

Earlier quoted context omitted.

Well I wish I could embed it as sqlite, but that is like me dreaming too big!

Can't you, though? I think I've seen several apps doing it, making the set-up and administration of the cluster abstracted away and transparent to the user (I can think of KDE's Akonadi suite for instance). That doesn't make the whole DB be contained in a single file, sure, but PG can then serve as the data backend of a versatile lot of applications

You can pack binaries yourself, but can't use PG as a library like with Sqlite or Firebird.

Re: Index bloat reduced in PostgreSQL v14

#80

Post by tomnipotent »

castorp wrote: post #53
tomnipotent wrote: post #39

Earlier quoted context omitted.

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

"hundreds" of concurrent connections isn't really a problem for Postgres. Several thousands are - at least up until now. V14 will improve this substantially.

Plenty of people are running on VPS or other shared hosting, with generally limited resources.
Post reply on HN