2

Let us say we have two users running a query against the same table in PostgreSQL. So,

User 1: SELECT * FROM table WHERE year = '2020' and

User 2: SELECT * FROM table WHERE year = '2019'

Are they going to be executed at the same time as opposed to executing one after the other?

I would expect that if I have 2 processors, I can run both at the same time. But I am thinking that matters become far more complicated depending on where the data is located (e.g. disk) given that it is the same table, whether there is partitioning, configurations, transactions, etc. Can someone help me understand how I can ensure that I get my desired behaviour as far as PostgreSQL is concerned? Under which circumstances will I get my desired behaviour and under which circumstances will I not?

EDIT: I have found this other question which is very close to what I was asking - https://dba.stackexchange.com/questions/72325/postgresql-if-i-run-multiple-queries-concurrently-under-what-circumstances-wo. It is a bit old and doesn't have much answers, would appreciate a fresh outlook on it.

Zeruno
  • 1,391
  • 2
  • 20
  • 39
  • related: https://stackoverflow.com/q/21440048, https://stackoverflow.com/a/32643911 – djvg Sep 01 '23 at 07:28

1 Answers1

3

If the two users have two independent connections and they don't go out of their way to block each other, then the queries will execute at the same time. If they need to access the same buffer at the same time, or read the same disk page into a buffer at the same time, they will use very fast locking/coordination methods (LWLocks, spin locks, or atomic operations like CAS) to coordinate that. The exact techniques vary from version to version, as better methods become widely available on supported platforms and as people find the time to change the implementation to use those better methods.

I can ensure that I get my desired behaviour as far as PostgreSQL is concerned?

You should always get the correct answer to your query (Or possibly some kind of ERROR indicating a failure to serialize if you are using the highest (and non-default) isolation level, but that doesn't seem to be a risk if each of those queries is run in a single-statement transaction.)

I think you are overthinking this. The point of using a database management system is that you don't need to micromanage it.

Also, "parallel-query" refers to a single query using multiple CPUs, not to different queries running at the same time.

jjanes
  • 37,812
  • 5
  • 27
  • 34
  • I am specifically interested in the micromanagement aspect because I am learning about the internals of the system. Parallel query is an overarching term for "inter-" and "intra-" query parallelism, so it can refer to both a single query using multiple CPUs or to multiple queries running at the same time. – Zeruno Feb 13 '20 at 21:02
  • I have found this other question which is very close to what I was asking - https://dba.stackexchange.com/questions/72325/postgresql-if-i-run-multiple-queries-concurrently-under-what-circumstances-wo. However, it is a bit old and doesn't have much answers, would appreciate a fresh outlook on it. Can you help? – Zeruno Feb 18 '20 at 17:01