Some questions about Postgres
Hi all,
I am new to Postgres. I read an old article comparing MySQL to Postgres and
wanted to get answers to following questions. All questions are w.r.t.
Postgres 14:
- do postgres secondary indexes point directly to tuples on disk?
- does postgres use a separate process for each connection?
- does postgres use any application cache similar to innodb_buffer_pool?
Thanks a lot. If you can provide references to documentation, that would be
great.
On Nov 3, 2022, at 10:38, Siddharth Jain <siddhsql@gmail.com> wrote:
I read an old article comparing MySQL to Postgres and wanted to get answers to following questions.
Given your questions, I suspect that you read the technical note from Uber about why they switched back to MySQL from PostgreSQL. There are quite a few responses out there to it, including:
https://thebuild.com/presentations/uber-perconalive-2017.pdf
To answer the specific questions:
1. Yes, all PostgreSQL indexes point directly to tuples on disk. They do not point to a primary key (which a table might not even have).
2. Yes, each backend connection to PostgreSQL is a separate process. The usual way of mitigating any performance issues this might introduce is to use a pooler such as pgbouncer.
3. This is equivalent to PostgreSQL's shared buffers.
Thanks Christophe. Yes that is the article and I read the response as well.
On Thu, Nov 3, 2022 at 10:42 AM Christophe Pettus <xof@thebuild.com> wrote:
Show quoted text
On Nov 3, 2022, at 10:38, Siddharth Jain <siddhsql@gmail.com> wrote:
I read an old article comparing MySQL to Postgres and wanted to getanswers to following questions.
Given your questions, I suspect that you read the technical note from Uber
about why they switched back to MySQL from PostgreSQL. There are quite a
few responses out there to it, including:https://thebuild.com/presentations/uber-perconalive-2017.pdf
To answer the specific questions:
1. Yes, all PostgreSQL indexes point directly to tuples on disk. They do
not point to a primary key (which a table might not even have).2. Yes, each backend connection to PostgreSQL is a separate process. The
usual way of mitigating any performance issues this might introduce is to
use a pooler such as pgbouncer.3. This is equivalent to PostgreSQL's shared buffers.
On Thu, 2022-11-03 at 10:42 -0700, Christophe Pettus wrote:
On Nov 3, 2022, at 10:38, Siddharth Jain <siddhsql@gmail.com> wrote:
I read an old article comparing MySQL to Postgres and wanted to get answers to following questions.Given your questions, I suspect that you read the technical note from Uber about why they switched back
to MySQL from PostgreSQL. There are quite a few responses out there to it, including:https://thebuild.com/presentations/uber-perconalive-2017.pdf
This is also insightful: https://use-the-index-luke.com/blog/2016-07-29/on-ubers-choice-of-databases
Yours,
Laurenz Albe
Thanks Laurenz.
On Fri, Nov 4, 2022 at 1:55 AM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
Show quoted text
On Thu, 2022-11-03 at 10:42 -0700, Christophe Pettus wrote:
On Nov 3, 2022, at 10:38, Siddharth Jain <siddhsql@gmail.com> wrote:
I read an old article comparing MySQL to Postgres and wanted to getanswers to following questions.
Given your questions, I suspect that you read the technical note from
Uber about why they switched back
to MySQL from PostgreSQL. There are quite a few responses out there to
it, including:
https://thebuild.com/presentations/uber-perconalive-2017.pdf
This is also insightful:
https://use-the-index-luke.com/blog/2016-07-29/on-ubers-choice-of-databasesYours,
Laurenz Albe