pgaddict

9 ms·

61.
by pgaddict 3y ago
I respectfully disagree with the notion that sharding makes resource usage somehow less important. Sure, it allows you to overcome the limits that would apply to a single node, but if you stop caring about using resources efficiently (e.g.
62.
by pgaddict 3y ago
The one problem with this "perfectly" sequential UUIDs is that it can easily lead to index bloat. Imagine you have such sequential UUIDs generated over a year, for example. And then you delete e.g. 99% of old data (say, everything
63.
by pgaddict 3y ago
That was just an example calculation, to illustrate the write amplification factor, of course. You can scale it up pretty arbitrarily. I mentioned only WAL for simplicity, but it also has to modify and write out the index pages themselves,
64.
by pgaddict 3y ago
It is not a matter of a couple milliseconds. The loss of locality for reads is bad, especially for data sets that don't fit into cache / RAM (while the active set would). Where it really bites you is writes, because it can trigger
65.
by pgaddict 3y ago
I think the main limitation of our docs is that it mostly explains what the pieces do, not how to use them to achieve a particular goal. For example, we have pretty good documentation of all the pieces to do HA, we just don't tell peop
66.
by pgaddict 5y ago
> If the page size is too small, rows won’t fit inside the page and if it’s too large there is risk of write failure because hardware generally can only guarantee atomicity for a fixed size blocks which can vary disk to disk (usually ran
67.
by pgaddict 5y ago
Well, the simple truth is adding efficient compression to the row storage (which is what heap does) is not really possible. Or more precisely - you can do that outside the database by using a filesystem with a compression (like zfs), and do
68.
by pgaddict 5y ago
Yeah, VACUUM only shuffles rows within a page, to maximize the amount of free space available for new data. VACUUM FULL essentially rebuilds the segment files - it creates new files and shovels all rows from the old ones. In the past it was
69.
by pgaddict 5y ago
I think import/export of stats is pretty doable. Not a tiny amount of work, because of how many stats there may be (regular, extended), but I don't see any obvious major challenges ... Similarly for disabling autoanalyze. We kinda
70.
by pgaddict 5y ago
What do you mean "architecturally broken for some large tables"? How is the architecture broken, which large tables?
71.
by pgaddict 5y ago
No, we don't collect any optimizer stats during query execution. It's trickier that it seems, because (a) collecting the stats is actually pretty expensive, and (b) when using indexes, you may actually see just a tiny part of the
72.
by pgaddict 5y ago
Well, what exactly would you expect for better visibility into the planner? I mean, you have the source code, and I'm not sure how to visualize the extreme number of combinations considered by the planner. Any examples of databases doi
73.
by pgaddict 5y ago
I'm not quite sure why you consider this "user error"? I work on the optimizer a bit, and I wouldn't say it's a fault of the user ... OTOH I'm not sure it's a fault of the DB either :-( The statistics coll
74.
by pgaddict 5y ago
Sorry, but that ignores about 99% of the context when that decision was done. Postgres started in early 90s (1996 is the first open source release). We may have fast threading libraries now, but that was not the case when the decision was m
75.
by pgaddict 5y ago
Well, I'd argue that's more an issue of the web application. If you're on a system with limited resources, and the webapp insists on opening hundreds of connections, assuming they're free of charge, it's a bit silly
76.
by pgaddict 5y ago
Yeah, the absence of a sudden cliff is very nice. I think we fixed the main causes back in ~9.5. But the gradient at the end is pretty clear, and even at 10k the throughput is already less than 50% of the max. And it's dropping faster
77.
by pgaddict 5y ago
The fact that some other part of the software stack does something silly does not mean the database has to cater for that. If it's a misconfiguration, fix the misconfiguration.
78.
by pgaddict 5y ago
But no one says you need a separate connection pool for each client application. There are cases when it's the right thing, but you can just as well have a single connection pool for each PostgreSQL instance (and all apps will go throu
79.
by pgaddict 5y ago
> Then other products will emerge and overtake some of PostreSQL's marketshare in the long run. It's already happening in fact. Just like more efficient and easier to configure webservers like nginx and caddy are gaining market
80.
by pgaddict 5y ago
There are two parts of this - the memory allocated by OS and internally. At the OS level, we can't really do much, I'm afraid :-( I don't think we're wasting too much memory there, exactly because a lot of the memory is
81.
by pgaddict 5y ago
I disagree, for a number of reasons. Firstly, it's not the goal of the PostgreSQL project to overtake MySQL or other databases, but to serve the existing/new users. This also means we're investing the development effort in a
82.
by pgaddict 5y ago
It's not clear to me if the OP want's to run without any connection pool (incl. a built-in one), or just without a separate one. In an ideal world PostgreSQL would handle infinite number of connections without a connection pool. U
83.
by pgaddict 5y ago
What do you mean by "went into the semantics"?
84.
by pgaddict 5y ago
Be careful as it actually runs the query, so if it's a DELETE/INSERT/UPDATE it'll change the data. So run it in BEGIN/ROLLBACK block.
85.
by pgaddict 5y ago
That's hardly a PostgreSQL issue. If your container tech does not allow installing both old and new version of the binaries, it's a silly container tech.
86.
by pgaddict 5y ago
Can you elaborate / quantify the memory requirements a bit? I don't have much experience with MSQQL in this respect, so I'm curious how big the difference is.
87.
by pgaddict 5y ago
It isn't solved, and no one claimed it to be solved. The scalability improvement is related to how we build MVCC snapshots (i.e. information which transactions are visible to a session). That may reduce the memory usage a bit, but it&#
88.
by pgaddict 6y ago
I'm pretty sure you want reasonable meaningful commits. On tiny projects it may not matter, but on larger projects it's definitely a huge benefit, because chances are you'll have to investigate a bug in that code, re-learn wh
89.
by pgaddict 6y ago
Google the names of the committers and major contributors, and you'll see many are working for quite large enterprise-y companies ;-) Obviously, if more companies start supporting the community, that'd be even better.
90.
by pgaddict 6y ago
Keep in mind that "core team" does not mean "core developers" here. The PostgreSQL core team's responsibilities are more about governing the community (see https://www.postgresql.org/developer/c

More ›