depesz

'SELECT * from depesz'[1] is another great resource for weird sql capabilities and performance-related stuff, focused heavily on Postgresql [1] https://www.depesz.com/

Maybe ping depesz (http://www.depesz.com/) about it. He has that great "waiting for postgres X.X" column at his blog, where he illustrates new things.

As a tool to read the output: https://explain.depesz.com/ Not entirely necessary when you're more familiar with it, but still useful. And when starting it's much easier to read. https://www.depesz.com/tag/unexplainable/ This series of blog posts is one option. But my impression is that most of this comes simply with experience. At first the main thing you'd look for is simply whether it uses the indexes you want it to use, or if it does full table scans. I'd also recommend to simply read the Postgres manual, at least the parts that are not entirely about the syntax of various commands. There is a lot of information in there that will help to understand how Postgres works.

Given the input, it can't be - it's just a different visualization. Great one byt the looks of it, too! https://explain.depesz.com/ is an awesome tool too, and I especially love the "Explaining the unexplainable" series: https://www.depesz.com/tag/unexplainable/

Indeed, a great resouce! I really liked how he explained and benchmarked an implementation for foreign keys to partitioned data (sadly not available natively yet) in this multi-part series from last year: https://www.depesz.com/2018/10/02/foreign-key-to-partitioned-table/

Exactly this. It‘s so hard that it took PostgreSQL almost two decades to finally implement that feature and they had to support a lot of internal features first, like speculative insertion. If you want to do UPSERT 100% correctly and ACID, the list of edge cases just grows and grows. Here‘s a great article describing these problems in detail if you‘re interested: https://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/

I think this post only gives half of the story. The thing is you need to manage not just your schema (and reference data) but also your DB code - i.e. stored proc etc. For schema, the approach recommended by the OP seems sensible - check diff scripts into VC and consider them immutable (see also http://www.depesz.com/2010/08/22/versioning/ for a lightweight Postgres approach). For stored procs etc, I think you need an approach much more akin to normal code - here you want to be able to leverage your VCS just as you do with normal code - so for these you want to consider them mutable. One of the best presentations I've seen on this topic is: http://www.slideshare.net/OleksiiKliukin/pgconf-us-2015-alter-database…

BRIN (Block Range) Indices look really interesting. Instead of storing the whole B-Tree (and spending time updating it) just store summary of ranges (pages). This for example, would be great for a time series database that is write heavy but not read as often. I found these benchmarks here explaining the differences: http://www.depesz.com/2014/11/22/waiting-for-9-5-brin-block-range-inde… --- Creating 650MB tables: * btree: 626.859 ms. * brin: 208.754 ms (3x speedup) Updating 30% of values: * btree: 8398.461 ms. * brin: 1398.711 ms. (4x speedup) Extra bonus: * size of btree index: 28MB * size of brin: 64kb Search (for a range): $ select count(*) from table where id between 600000::int8 and 650000::int8; * btree between: 9.574 ms * brin between: 21.090 ms ---

One of the little-noticed (yet) features of the upcoming 9.4 release greatly increases the ability to optimize moving aggregates for certain types of numerical operations, including custom aggregates. See http://www.depesz.com/2014/04/13/waiting-for-9-4-provide-moving-aggreg… for a better explanation.

Good article. For generally following what's coming in Postgres, I find that Hubert "depesz" Lubaczewski's blog [1] is excellent; he has a whole series called "Waiting for [version]" that covers upcoming features, and he always includes examples of usage. For example, he has two pretty extensive articles on pg_prewarm [2] and the ordered-set features [3]. [1] http://www.depesz.com/tag/postgresql/ [2] http://www.depesz.com/2014/01/10/waiting-for-9-4-pg_prewarm-a-contrib-… [3] http://www.depesz.com/2014/01/11/waiting-for-9-4-support-ordered-set-w…

Sigh. The usual downvotes because... why, you can pick holes in what I said, or some people's identity is so tied up with a piece of open-source software that they can't tolerate apparent criticism of it? Fact: the optimiser in an SQL engine is almost the quintessence of it. If you don't understand that then you don't understand that the potential cost of the declarative nature of SQL. Fact: SQL optimisers are complex. New fact: microsoft has pumped fantastic amounts of cash into MSSQL. Posgres just doesn't have those resources (and other resources, such as the late lamented Jim Gray). Other fact. Postgres CTEs were an optimisation barrier until PG11 or PG12 (https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-allow-user… AFAIK MSSQL's CTEs have never been an optimisation barrier, and I've used them long enough to know. I'm also trying to understand the cost of MVCC, which PG uses but MSSQL now has as an option, to try and understand where that costs, because depending on the scenario, it will. PG does not have READ UNCOMMITTED. Fact: The guy I quoted had used postgres on large data a while back and said it wasn't as good as MSSQL. He had experience, which is the bottom line. I also acknowledged this may have changed recently. Other fact: I don't dislike postgres and recently have decided learn PG also, because, for very good reason, MSSQL has its problems (the ridiculous cost not being the…

For those who may be interested: I was curious about the difference between MERGE [1] and UPSERT, and why database engines seem to support a custom (nonstandard) UPSERT instead of the MERGE introduced in standard SQL in 2003, so I did some web research and came across a reasonable-sounding answer in a comment on the following article. https://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/ > As noted, MERGE is complicated and slow. If two different database servers implement it, then it is going to be implemented with different schematics [sic]. Oracle and MySQL already have different implementation’s, and to nobody’s surprise, MySQL’s implementation allows for risky behavior that does not protect the data. PostgreSQL users expect that any data put into the database will reliably come out of the database. So MERGE is slow and inconsistently implemented. The correct thing to do is make the application aware of if a record is new or existing. Any application making use of MERGE should open a bug to replace it with more predictable and faster logic using INSERT and UPDATE. > If an application does use MERGE, it has to account for implementation specific behavior and not will not be portable, in a safe way, to other database servers. So it is not suitable for ORM’s or database independent applications. > So what is a legitimate use case for MERGE where not knowing if a record is new or existing is not possible prior to the transaction? They only thing that I can…

MongoDB accrued a lot of bad-will due to some extremely questionable defaults, which remain defaults to this day. There's no question that you can write a fast database when there's no guarantee that data ever hits the disk, but developers tend not to like it when a database accepts their write and then silently loses data. It's also great for toy problems and 15-minute-demos... but then you inevitably run into its limitations and end up re-implementing a database in your app. Even at its best, there is essentially no reason to choose MongoDB over Postgres with JSONB-type columns. They are essentially the same data model but Postgres gives you better guarantees of data consistency, plus a forward migration path to relational data when the day inevitably arrives when you need to model relationships between entities. At this point Postgres is where most open-source RDBMS development work is concentrated. It's not only a solid codebase, it's piling up features pretty quickly and there are relatively few niches it doesn't fill at least adequately. All of these niches are covered by some commercial products built on top of Postgres (eg EnterpriseDB or CitusDB). It's pretty much a one-stop shop for application development. You can use it for everything from GIS to machine learning [0] pretty efficiently, and it pretty much will just do the right thing without you watching. NoSQL really fits best around the margins, like as an auxiliary system for analytics. There is…

I don't think upsert has taken so long because of the community and processes, but because the Postgresql team has high requirements for it: http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/ For example, upsert that is worth releasing should: * Work with all unique indexes that may exist on a table, not only a primary key, * Be correct even at highly concurrent writes, at any isolation level from Read Uncommitted to Serializable, * Be fast even at highly concurrent writes, so no table locks, locks may not be held for more than an instant, and no re-running the entire transaction in a loop, * Never throw an "another transaction modified data" exception at Read Committed isolation level, it should always correctly succeed as an insert or update even if another parallel transaction inserted or deleted the matching row. I'm not familiar much with the other database engines, but my impression has been that their upsert or SQL merge do not provide all of these.

Here's a great article[0] regarding different ways to accomplish to upsert in Postgres. I bring it up because the author adds his thought on why Postgres hasn't implemented it unlike the other open source RDBMS: > As a final word – yeah, but why PostgreSQL doesn't handle it internally, as the other database? > Answer lies somewhere between "developers have other things to do first" and "it's complicated, because there are many ways to do it, and the most correct one is slow – so there is a trade off decision to be made". I'm not sure how upsert is implemented in MySql but given its reputation and the fact that it is open source; I would imagine the Postgres team has considered their solution and have decided it wasn't good enough for their standards. Postgres may not have an out-of-the-box solution but there are solutions out there which are mostly pretty trivial functions. Although they may add a bit of bloat since it seems you'd need a function for each table you'd want to use upsert on. [0] http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/

No word that PostgreSQL 19 introduces native application-time temporal data support based on the SQL:2011 standard? https://www.depesz.com/2026/04/02/waiting-for-postgresql-19-add-update…

Logical replication is how this is typically done. If you have some infra-as-code setup, you create a new cluster with identical settings except for the major version, import the schema, start copying data from a read-replica running the old version, stop accepting writes from the old version (downtime starts), sync the sequence numbers, and point your services to the new cluster (downtime ends). If you use something like CloudNativePG they automate parts of the process with cli tools and declarative syntax. Otherwise you take the time to figure it out by hand. It might sound complicated, but just practice on your staging DB, and if all goes well you do the same procedure in prod. Edit: Apparently Postgres 19 has a patch for one-shot logical replication of sequences! https://www.depesz.com/2025/11/11/waiting-for-postgresql-19-sequence-s…

It's coming in the Postgres 18. https://www.depesz.com/2025/02/28/waiting-for-postgresql-18-virtual-ge…

As of Postgres 12 (Oct. 2019) CTEs are no longer materialized by default and are eligible for predicate push down. The planner may still struggle but it’s not true that a CTE is an optimization fence. https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-allow-user…

IDEA if you want to use it for other things (or any other JetBrains IDE). Nothing comes close feature-wise. If you don't: - https://www.depesz.com/2022/09/21/prettify-sql-queries-from-command-li… - https://gitlab.com/depesz/pg-sql-prettyprinter Or https://paste.depesz.com for one-off use.

True enough, and the solutions for lighter-weight schema changes have evolved and weren't always so good, but early stage startups often don't really have the sort of data-weight issues that make this hard, unless they're starting with large data sets already. Even at the scale of small hundreds of millions of rows per table (like the company I'm in charge of the database for) it's not much of an issue. I had to look it up, apparently adding a column with a non-null default was "fixed" in PG 11 (2018), but with a null default it had been fast for a while: https://www.depesz.com/2018/04/04/waiting-for-postgresql-11-fast-alter…

Part 2 - https://www.depesz.com/2021/01/28/how-to-run-some-tasks-without-user-i…

PG community had a similar patch, which got reverted from PG 15 on the last minute: https://www.depesz.com/2022/04/06/waiting-for-postgresql-15-json_table…

Just to flag this behaviour changes in v12: https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-allow-user…

CTEs used to be "optimization fences" in PostgreSQL, but that changed with v12. https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-allow-user…

I find it interesting, as recently I was reading about how complex it would/could be to create an index suggester. https://www.depesz.com/2021/10/22/why-is-it-hard-to-automatically-sugg…

Of course they are now adding merge anyway: https://www.depesz.com/2022/03/31/waiting-for-postgresql-15-add-suppor…

Statement about default value for new column is not actual anymore starting from Postgres 11 https://www.depesz.com/2018/04/04/waiting-for-postgresql-11-fast-alter…

Wasn't this solved in Postgres 12?[0] [0] https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-allow-user…

There has been a lot of improvement in this release. https://www.depesz.com/2020/08/25/waiting-for-postgresql-14-improvemen…