7 ms·

If anyone has questions about our Postgres product please feel free to ask. I will be around to answer.

by samlambert 1y ago

attentionstinks 1y ago
How should one decide whether to go with MySQL or Postgres for a greenfield project?
- add-sub-mul-div 1y ago
  Pre-existing expertise with MySQL and lack of time or inclination to learn something new is the only reason I could think of not to go with Postgres.
- jedberg 1y ago
  At this point I'm not sure why anyone would choose MySQL. Any advantage it had pretty much evaporated with these hosted solutions. For example, MySQL was easier to get running and connect to. These cloud offerings (Planetscale, Supabase, Neon, even RDS) have solved that. MySQL was faster for read heavy loads. Also solved by the cloud vendors.
  - cortesoft 1y ago
    > At this point I'm not sure why anyone would choose MySQL Because I have used MySQL for over 20 years and it is what I know!
    - jedberg 1y ago
      Fair enough, but I assume most of that is in the administration of MySQL? Which is all now abstracted away by the cloud vendors. If you're running it yourself I could see why you'd do that, but if you're mostly just using it now, Postgres can do all the same things in the database pretty much the same way, plus a whole lot more.
      - cortesoft 1y ago
        Its both operating MySQL and creating applications that use it. Additionally, almost all my workloads run in our own datacenters, so I haven't yet been able to offload the administration bits to the cloud.
  - bri3d 1y ago
    At large scale I'd say MySQL is still a competitor for a few reasons: * Scale-out inertia: yes, cloud vendors provide similar shading and clustering features for Postgres, but they're all a lot newer. * Thus, hiring. It's easier to find extreme-scale MySQL experts (although this erodes year by year). * Write amplification, index bloat, and tuple/page bloat for extremely UPDATE heavy workloads. It is what it is. Postgres continues to improve, but it is fundamentally an MVCC database. If your workload is mostly UPDATEs and simple SELECTs, Postgres will eventually fall behind MySQL. * Replication. Postgres replication has matured a ridiculous amount in the last 5-10 years, and to your point, cloud hosting has somewhat reduced the need to care about it, but it's still different from MySQL in ways that can be annoying at scale. One of the biggest issues is performing hybrid OLAP+OLTP (think, a big database of Stuff with user-facing Dashboards of Stuff). In MySQL this is basically a non-event, but in Postgres this pattern requires careful planning to avoid falling afoul of max_standby_streaming_delay for example. * Neutral but different: documentation - Postgres has better-written user-facing documentation for user-facing functions, IMO. However, _if_ you don't like reading source code, MySQL has better internals documentation, and less magic. However, Postgres is _very_ well written and commented, so if you're comfortable reading source, it's a joy. A _lot_ of Postgres work, in my experience, is reading somewhat vague documentation followed by digging into the source code to find a whole bunch of arbitrary magic numbers. If you don't believe me , as an exercise, try to figure out what `default_statistics_target` _actually_ does. Anyway, I still would choose a managed Postgres solution almost universally for a new product. Unless I know _exactly_ what I'm going to be doing with a database up-front, Postgres will offer better flexibility, a nicer feature-set, and a completely acceptable scale story.
    - jashmatthews 1y ago
      > hybrid OLAP+OLTP .... in Postgres this pattern requires careful planning to avoid falling afoul of max_standby_streaming_delay for example This is a really gnarly problem at scale I've rarely seen anyone else bring up. Either you use max_standby_streaming_delay and queries that conflict with replication cause replication to lag or you use hot_standby_feedback and long running queries on the OLAP replica cause problems on the primary. Logical Decoding on a replica in also needs hot standby feedback which is a giant PITA for your ETL replica.
- n_u 1y ago
  From my position MySQL pros: The MySQL docs on how the default storage engine InnoDB locks rows to support transaction isolation levels is fantastic. [1] This can help you better architect your system to avoid lock contention or understand why existing queries may be contending for locks. As far as I know Postgres does not have docs like that. MySQL uses direct I/O so it disables the OS page cache and uses its own buffer pool instead[2]. Whereas Postgres doesn't use direct I/O so the OS page cache will duplicate pages (called the "double buffering" problem). So it is harder to estimate how large of a dataset you can keep in memory in Postgres. They are working on it though [3] If you delete a row in MySQL and then insert another row, MySQL will look through the page for empty slots and insert there. This keeps your pages more compact. Postgres will always insert at the bottom of the page. If you have a workload that deletes often, Postgres will not be using the memory as efficiently because the pages are fragmented. You will have to run the VACUUM command to compact pages. [4] Vitess supports MySQL[5] and not Postgres. Vitess is a system for sharding MySQL that as I understand is much more mature than the sharding options for Postgres. Obviously this GA announcement may change that. Uber switched from MySQL to Postgres only to switch back. It's a bit old but it's worth a read. [6] Postgres pros: Postgres supports 3rd party extensions which allow you to add features like columnar storage, geo-spatial data types, vector database search, proxies etc.[7] You are more likely to find developers who have worked with Postgres.[8] Many modern distributed database offerings target Postgres compatibility rather than MySQL compatibility (YugabyteDB[9], AWS Aurora DSQL[10], pgfdb[11]). My take: I would highly recommend you read the docs on InnoDB locking then pick Postgres. [1] https://dev.mysql.com/doc/refman/8.4/en/innodb-locking.html https://dev.mysql.com/doc/refman/8.4/en/innodb-locking.html [2] https://dev.mysql.com/doc/refman/8.4/en/memory-use.html https://dev.mysql.com/doc/refman/8.4/en/memory-use.html [3] https://pganalyze.com/blog/postgres-18-async-io https://pganalyze.com/blog/postgres-18-async-io [4] https://www.percona.com/blog/postgresql-vacuuming-to-optimize-database-performance-and-reclaim-space/ https://www.percona.com/blog/postgresql-vacuuming-to-optimiz... [5] https://vitess.io/ https://vitess.io/ [6] https://www.uber.com/blog/postgres-to-mysql-migration/ https://www.uber.com/blog/postgres-to-mysql-migration/ [7] https://www.tigerdata.com/blog/top-8-postgresql-extensions https://www.tigerdata.com/blog/top-8-postgresql-extensions [8] https://survey.stackoverflow.co/2024/technology#1-databases https://survey.stackoverflow.co/2024/technology#1-databases [9] https://www.yugabyte.com/ https://www.yugabyte.com/ [10] https://aws.amazon.com/rds/aurora/dsql/ https://aws.amazon.com/rds/aurora/dsql/ [11] https://github.com/fabianlindfors/pgfdb https://github.com/fabianlindfors/pgfdb
- sgammon 1y ago
  planetscale now supports both :)
robraven 1y ago
Any benchmarks for using planetscale Postgres vs MySQL?
dangoodmanUT 1y ago
Postgres (esoterically?) has some issues with index bloat on high-insert workloads, does PlanetScale do anything special to tune for this by default, since it caters to higher-perf workloads (over something like supabase)?
- dangoodmanUT 1y ago
  On another, similar, vein - i'd be curious to know if XID wraparound and auto vacuum tuning was something you had to advise customers on up front consdering how often that issue rears its head for the same kinds of workloads.
- samlambert 1y ago
  We don't do anything special (yet) but we do have bloat detection that we warn you about. We've noticed that autovacuum works very well on our Metal product because of the extra resources.
  - hollylawly 1y ago
    More information about bloat detection here: https://planetscale.com/docs/postgres/monitoring/schema-recommendations#how-planetscale-detects-index-bloat https://planetscale.com/docs/postgres/monitoring/schema-reco...
- petergeoghegan 1y ago
  Can you provide more detail/a reference? I've done extensive work on improving the Postgres B-Tree code, over quite a number of releases. I'm not aware of any problems with high-insert workloads in particular. I have personally fixed a number of subtle issues that could lead to lower space utilization with such workloads [1][2] in the past, though. if there's a remaining problem in this area, then I'd very much like to know about it. [1] https://www.youtube.com/watch?v=p5RaATILoiE https://www.youtube.com/watch?v=p5RaATILoiE [2] https://speakerdeck.com/peterg/nbtree-arch-pgcon https://speakerdeck.com/peterg/nbtree-arch-pgcon
  - dangoodmanUT 1y ago
the_mitsuhiko 1y ago
If neki becomes available later, do you expect that customers will be able to easily migrate over to it?
- samlambert 1y ago
  we will aim to make it as easy as possible and doable as an online process. with sharding there is always a chance that some application changes are needed so there might be some work required there.
tacone 1y ago
Extensions! Which pg extensions are you going to make available?
- rcrowley 1y ago
  Many! <https://planetscale.com/docs/postgres/extensions https://planetscale.com/docs/postgres/extensions>
endorphine 1y ago
Since NVMe nodes are ephemeral on GCP, would you suggest SaaS with critical customer data to use Metal or persistent disks?
- deleted 1y ago
  [deleted]
- rcrowley 1y ago
  PlanetScale always maintains three (or more, if you want) copies of the data and only acknowledges writes after they've been written to at least two cloud provider availability zones. Durability is provided via replication at the database layer rather than hidden in the slow network-attached block device. Most of our largest customers use Metal for their most critical data and all of them saw their 99th percentile latency plummet when they migrated. I did an interview all about PlanetScale Metal a couple of months ago: <https://www.youtube.com/watch?v=3r9PsVwGkg4 https://www.youtube.com/watch?v=3r9PsVwGkg4>
bri3d 1y ago
* I saw your benchmark page at https://planetscale.com/benchmarks/aurora https://planetscale.com/benchmarks/aurora ; do you have something similar for Aurora Serverless? * Do you support something like Aurora Fast Cloning (whether a true CoW fast clone or detaching a replica _without_ promoting it into its own cluster / branch with its own replicas, incurring cost)? * Can PlanetScale Postgres set `max_standby_streaming_delay` to an indefinite amount? * The equivalent of Aurora blue/green would be to make a branch and then switch branches, right?
- samlambert 1y ago
  it should be pretty much the same for aurora serverless and likely even cheaper. we see some astronomically expensive auorora serverless bills. We have not made max_standby_streaming_delay configurable yet. What's your use case? I don't fully parse your question about blue/green. can you expand your question please? is this for online updrades?
  - bri3d 1y ago
    > I don't fully parse your question about blue/green. can you expand your question please? is this for online updrades? Online upgrade or migration DDL - both use cases. I think Amazon's blue/green is effectively the same thing as your "branch-and-commit" strategy for schema migration. I was just looking for whether there's a significant difference. > We have not made max_standby_streaming_delay configurable yet. What's your use case? This goes with >> Do you support something like Aurora Fast Cloning (whether a true CoW fast clone or detaching a replica _without_ promoting it into its own cluster / branch with its own replicas, incurring cost)? The use case is mixing transaction processing and long-running queries/analytics in the same database using read replicas. The easiest way to do this in a native Postgres cluster is by using a "soft-real-time" read-replica with max_standby_streaming_delay set to `-1`, which is allowed to fall behind worst-case by the duration of a dashboard query and then keep up again. This doesn't work in environments with more esoteric SAN-based replication strategies like Aurora, where max_standby_streaming_delay can't go beyond 30 seconds. In this case we have to use some combination of strategies: making CoW clones for each analytics task, architecting data to avoid leader/replication conflicts, for example by using partitioning, retrying replica queries until they don't access hot rows, or falling back to traditional analytics/data warehouse solutions at the application or logical replication layer. Not having to do these things would be a nice benefit over Aurora.
sethreno 1y ago
Will Neki be a branch of [Citus](https://github.com/citusdata/citus https://github.com/citusdata/citus) or is it more based on Vitess?
- samlambert 1y ago
  Inspired by Vitess seeing as we are the company behind Vitess but it's a new codebase.
Ozzie_osman 1y ago
Any recommendations on how to best test our current workload of reads and writes? Also, if we are pretty certain we will need horizontal partitioning / sharding, would it be better to wait for Neki before considering a move? For context we are on Aurora Postgres right now, with several read replicas.
cocoflunchy 1y ago
Hi, any idea of the timing to launch in europe-west1 on GCP? Also does branching work on Postgres?

If anyone has questions about our Postgres product please feel free to ask. I will be around to answer.

attentionstinks 1y ago

How should one decide whether to go with MySQL or Postgres for a greenfield project?

add-sub-mul-div 1y ago
Pre-existing expertise with MySQL and lack of time or inclination to learn something new is the only reason I could think of not to go with Postgres.
jedberg 1y ago
At this point I'm not sure why anyone would choose MySQL. Any advantage it had pretty much evaporated with these hosted solutions. For example, MySQL was easier to get running and connect to. These cloud offerings (Planetscale, Supabase, Neon, even RDS) have solved that. MySQL was faster for read heavy loads. Also solved by the cloud vendors.
- cortesoft 1y ago
  > At this point I'm not sure why anyone would choose MySQL Because I have used MySQL for over 20 years and it is what I know!
  - jedberg 1y ago
    Fair enough, but I assume most of that is in the administration of MySQL? Which is all now abstracted away by the cloud vendors. If you're running it yourself I could see why you'd do that, but if you're mostly just using it now, Postgres can do all the same things in the database pretty much the same way, plus a whole lot more.
    - cortesoft 1y ago
      Its both operating MySQL and creating applications that use it. Additionally, almost all my workloads run in our own datacenters, so I haven't yet been able to offload the administration bits to the cloud.
- bri3d 1y ago
  At large scale I'd say MySQL is still a competitor for a few reasons: * Scale-out inertia: yes, cloud vendors provide similar shading and clustering features for Postgres, but they're all a lot newer. * Thus, hiring. It's easier to find extreme-scale MySQL experts (although this erodes year by year). * Write amplification, index bloat, and tuple/page bloat for extremely UPDATE heavy workloads. It is what it is. Postgres continues to improve, but it is fundamentally an MVCC database. If your workload is mostly UPDATEs and simple SELECTs, Postgres will eventually fall behind MySQL. * Replication. Postgres replication has matured a ridiculous amount in the last 5-10 years, and to your point, cloud hosting has somewhat reduced the need to care about it, but it's still different from MySQL in ways that can be annoying at scale. One of the biggest issues is performing hybrid OLAP+OLTP (think, a big database of Stuff with user-facing Dashboards of Stuff). In MySQL this is basically a non-event, but in Postgres this pattern requires careful planning to avoid falling afoul of max_standby_streaming_delay for example. * Neutral but different: documentation - Postgres has better-written user-facing documentation for user-facing functions, IMO. However, _if_ you don't like reading source code, MySQL has better internals documentation, and less magic. However, Postgres is _very_ well written and commented, so if you're comfortable reading source, it's a joy. A _lot_ of Postgres work, in my experience, is reading somewhat vague documentation followed by digging into the source code to find a whole bunch of arbitrary magic numbers. If you don't believe me , as an exercise, try to figure out what `default_statistics_target` _actually_ does. Anyway, I still would choose a managed Postgres solution almost universally for a new product. Unless I know _exactly_ what I'm going to be doing with a database up-front, Postgres will offer better flexibility, a nicer feature-set, and a completely acceptable scale story.
  - jashmatthews 1y ago
    > hybrid OLAP+OLTP .... in Postgres this pattern requires careful planning to avoid falling afoul of max_standby_streaming_delay for example This is a really gnarly problem at scale I've rarely seen anyone else bring up. Either you use max_standby_streaming_delay and queries that conflict with replication cause replication to lag or you use hot_standby_feedback and long running queries on the OLAP replica cause problems on the primary. Logical Decoding on a replica in also needs hot standby feedback which is a giant PITA for your ETL replica.
n_u 1y ago
From my position MySQL pros: The MySQL docs on how the default storage engine InnoDB locks rows to support transaction isolation levels is fantastic. [1] This can help you better architect your system to avoid lock contention or understand why existing queries may be contending for locks. As far as I know Postgres does not have docs like that. MySQL uses direct I/O so it disables the OS page cache and uses its own buffer pool instead[2]. Whereas Postgres doesn't use direct I/O so the OS page cache will duplicate pages (called the "double buffering" problem). So it is harder to estimate how large of a dataset you can keep in memory in Postgres. They are working on it though [3] If you delete a row in MySQL and then insert another row, MySQL will look through the page for empty slots and insert there. This keeps your pages more compact. Postgres will always insert at the bottom of the page. If you have a workload that deletes often, Postgres will not be using the memory as efficiently because the pages are fragmented. You will have to run the VACUUM command to compact pages. [4] Vitess supports MySQL[5] and not Postgres. Vitess is a system for sharding MySQL that as I understand is much more mature than the sharding options for Postgres. Obviously this GA announcement may change that. Uber switched from MySQL to Postgres only to switch back. It's a bit old but it's worth a read. [6] Postgres pros: Postgres supports 3rd party extensions which allow you to add features like columnar storage, geo-spatial data types, vector database search, proxies etc.[7] You are more likely to find developers who have worked with Postgres.[8] Many modern distributed database offerings target Postgres compatibility rather than MySQL compatibility (YugabyteDB[9], AWS Aurora DSQL[10], pgfdb[11]). My take: I would highly recommend you read the docs on InnoDB locking then pick Postgres. [1] https://dev.mysql.com/doc/refman/8.4/en/innodb-locking.html https://dev.mysql.com/doc/refman/8.4/en/innodb-locking.html [2] https://dev.mysql.com/doc/refman/8.4/en/memory-use.html https://dev.mysql.com/doc/refman/8.4/en/memory-use.html [3] https://pganalyze.com/blog/postgres-18-async-io https://pganalyze.com/blog/postgres-18-async-io [4] https://www.percona.com/blog/postgresql-vacuuming-to-optimize-database-performance-and-reclaim-space/ https://www.percona.com/blog/postgresql-vacuuming-to-optimiz... [5] https://vitess.io/ https://vitess.io/ [6] https://www.uber.com/blog/postgres-to-mysql-migration/ https://www.uber.com/blog/postgres-to-mysql-migration/ [7] https://www.tigerdata.com/blog/top-8-postgresql-extensions https://www.tigerdata.com/blog/top-8-postgresql-extensions [8] https://survey.stackoverflow.co/2024/technology#1-databases https://survey.stackoverflow.co/2024/technology#1-databases [9] https://www.yugabyte.com/ https://www.yugabyte.com/ [10] https://aws.amazon.com/rds/aurora/dsql/ https://aws.amazon.com/rds/aurora/dsql/ [11] https://github.com/fabianlindfors/pgfdb https://github.com/fabianlindfors/pgfdb
sgammon 1y ago
planetscale now supports both :)

robraven 1y ago

Any benchmarks for using planetscale Postgres vs MySQL?

dangoodmanUT 1y ago

Postgres (esoterically?) has some issues with index bloat on high-insert workloads, does PlanetScale do anything special to tune for this by default, since it caters to higher-perf workloads (over something like supabase)?

dangoodmanUT 1y ago
On another, similar, vein - i'd be curious to know if XID wraparound and auto vacuum tuning was something you had to advise customers on up front consdering how often that issue rears its head for the same kinds of workloads.
samlambert 1y ago
We don't do anything special (yet) but we do have bloat detection that we warn you about. We've noticed that autovacuum works very well on our Metal product because of the extra resources.
- hollylawly 1y ago
  More information about bloat detection here: https://planetscale.com/docs/postgres/monitoring/schema-recommendations#how-planetscale-detects-index-bloat https://planetscale.com/docs/postgres/monitoring/schema-reco...
petergeoghegan 1y ago
Can you provide more detail/a reference? I've done extensive work on improving the Postgres B-Tree code, over quite a number of releases. I'm not aware of any problems with high-insert workloads in particular. I have personally fixed a number of subtle issues that could lead to lower space utilization with such workloads [1][2] in the past, though. if there's a remaining problem in this area, then I'd very much like to know about it. [1] https://www.youtube.com/watch?v=p5RaATILoiE https://www.youtube.com/watch?v=p5RaATILoiE [2] https://speakerdeck.com/peterg/nbtree-arch-pgcon https://speakerdeck.com/peterg/nbtree-arch-pgcon
- dangoodmanUT 1y ago

the_mitsuhiko 1y ago

If neki becomes available later, do you expect that customers will be able to easily migrate over to it?

samlambert 1y ago
we will aim to make it as easy as possible and doable as an online process. with sharding there is always a chance that some application changes are needed so there might be some work required there.

tacone 1y ago

Extensions! Which pg extensions are you going to make available?

rcrowley 1y ago
Many! <https://planetscale.com/docs/postgres/extensions https://planetscale.com/docs/postgres/extensions>

endorphine 1y ago

Since NVMe nodes are ephemeral on GCP, would you suggest SaaS with critical customer data to use Metal or persistent disks?

deleted 1y ago
[deleted]
rcrowley 1y ago
PlanetScale always maintains three (or more, if you want) copies of the data and only acknowledges writes after they've been written to at least two cloud provider availability zones. Durability is provided via replication at the database layer rather than hidden in the slow network-attached block device. Most of our largest customers use Metal for their most critical data and all of them saw their 99th percentile latency plummet when they migrated. I did an interview all about PlanetScale Metal a couple of months ago: <https://www.youtube.com/watch?v=3r9PsVwGkg4 https://www.youtube.com/watch?v=3r9PsVwGkg4>

bri3d 1y ago

* I saw your benchmark page at https://planetscale.com/benchmarks/aurora https://planetscale.com/benchmarks/aurora ; do you have something similar for Aurora Serverless? * Do you support something like Aurora Fast Cloning (whether a true CoW fast clone or detaching a replica _without_ promoting it into its own cluster / branch with its own replicas, incurring cost)? * Can PlanetScale Postgres set `max_standby_streaming_delay` to an indefinite amount? * The equivalent of Aurora blue/green would be to make a branch and then switch branches, right?

samlambert 1y ago
it should be pretty much the same for aurora serverless and likely even cheaper. we see some astronomically expensive auorora serverless bills. We have not made max_standby_streaming_delay configurable yet. What's your use case? I don't fully parse your question about blue/green. can you expand your question please? is this for online updrades?
- bri3d 1y ago
  > I don't fully parse your question about blue/green. can you expand your question please? is this for online updrades? Online upgrade or migration DDL - both use cases. I think Amazon's blue/green is effectively the same thing as your "branch-and-commit" strategy for schema migration. I was just looking for whether there's a significant difference. > We have not made max_standby_streaming_delay configurable yet. What's your use case? This goes with >> Do you support something like Aurora Fast Cloning (whether a true CoW fast clone or detaching a replica _without_ promoting it into its own cluster / branch with its own replicas, incurring cost)? The use case is mixing transaction processing and long-running queries/analytics in the same database using read replicas. The easiest way to do this in a native Postgres cluster is by using a "soft-real-time" read-replica with max_standby_streaming_delay set to `-1`, which is allowed to fall behind worst-case by the duration of a dashboard query and then keep up again. This doesn't work in environments with more esoteric SAN-based replication strategies like Aurora, where max_standby_streaming_delay can't go beyond 30 seconds. In this case we have to use some combination of strategies: making CoW clones for each analytics task, architecting data to avoid leader/replication conflicts, for example by using partitioning, retrying replica queries until they don't access hot rows, or falling back to traditional analytics/data warehouse solutions at the application or logical replication layer. Not having to do these things would be a nice benefit over Aurora.

sethreno 1y ago

Will Neki be a branch of [Citus](https://github.com/citusdata/citus https://github.com/citusdata/citus) or is it more based on Vitess?

samlambert 1y ago
Inspired by Vitess seeing as we are the company behind Vitess but it's a new codebase.

Ozzie_osman 1y ago

Any recommendations on how to best test our current workload of reads and writes? Also, if we are pretty certain we will need horizontal partitioning / sharding, would it be better to wait for Neki before considering a move? For context we are on Aurora Postgres right now, with several read replicas.

cocoflunchy 1y ago

Hi, any idea of the timing to launch in europe-west1 on GCP? Also does branching work on Postgres?