Top 14 PostgreSQL Interview Questions And Answers

Question 1

How do I get the value of a `SERIAL` insert?

Accepted Answer

The simplest way is to retrieve the assigned `SERIAL` value with `RETURNING`. Using the example table in the previous question, it would look like this:

```sql
INSERT INTO person (name) VALUES ('Blaise Pascal') RETURNING id;
```

You can also call `nextval()` and use that value in the `INSERT`, or call `currval()` after the `INSERT`.

Question 2

How do I `SELECT` only the first few rows of a query? A _random_ row?

Accepted Answer

To retrieve only a few rows, if you know at the number of rows needed at the time of the `SELECT` use `LIMIT` . If an index matches the `ORDER BY` it is possible the entire query does not have to be executed. If you don't know the number of rows at `SELECT` time, use a cursor and `FETCH`.

To `SELECT` a random row, use:

```sql
SELECT col
FROM tab
ORDER BY random()
LIMIT 1;
```

Question 3

How do you change a column's data type?

Accepted Answer

Changing the data type of a column can be done easily in 8.0 and later with `ALTER TABLE ALTER COLUMN TYPE`.

In earlier releases, do this:
```sql
BEGIN;
ALTER TABLE tab ADD COLUMN new_col new_data_type;
UPDATE tab SET new_col = CAST(old_col AS new_data_type);
ALTER TABLE tab DROP COLUMN old_col;
COMMIT;
```

Question 4

In a query, how do I detect if a field is `NULL`?

Accepted Answer

You can test the value with `IS NULL` or `IS NOT NULL`, like this:

```sql
SELECT *
FROM tab
WHERE col IS NULL;
```

Question 5

What are OIDs in PostgreSQL and why are they useful?

Accepted Answer

**OIDs** basically give you a **built-in id for every row**, contained in a system column (as opposed to a user-space column). That's handy for tables where you don't have a primary key, have duplicate rows, etc. For example, if you have a table with two identical rows, and you want to delete the oldest of the two, you could do that using the oid column.

In a large or long-lived database, it is possible for the counter to wrap around. Hence, **it is bad practice to assume that OIDs are unique**, unless you take steps to ensure that this is the case.

In PostgreSQL 8.1 `default_with_oids` is off by default; in prior versions of PostgreSQL, it was on by default.The use of OIDs in user tables is considered deprecated, so most installations should leave this variable disabled.

Question 6

What is faster, one big query or many small queries?

Accepted Answer

In **Postgres** (and probably any RDBMS to a similar extent, MySQL to a lesser extent), **fewer queries are almost always _much_ faster.**

The overhead of parsing and planning multiple queries is already more than any possible gain in most cases.

Not to speak of additional work to be done in the client, combining the results, which is typically _much_ slower at that. An RDBMS specializes in that kind of task and operations are based on original data types. No casting to `text` and back for intermediate results or transforming to native types of the client, which may even lead to less correct (or incorrect!) results. Think of floating point numbers ...

You also transfer more data between DB server and client. This may be negligible for a hand full of values, or make a huge difference.

If multiple queries mean multiple round trips to the database server, you also collect multiple times the network latency and transaction overhead, possibly even connection overhead. Big, big loss.

Depending on your setup, network latency alone may take longer than all the rest by orders of magnitude.

Question 7

How does PostgreSQL use CPU resources?

Accepted Answer

The PostgreSQL server is process-based (not threaded). Each database session connects to a **single** PostgreSQL operating system (OS) **process**. Multiple sessions are automatically spread across all available CPUs by the OS. The OS also uses CPUs to handle disk I/O and run other non-database tasks. Client applications can use threads, each of which connects to a separate database process.

Since version 9.6, **portions** of some queries can be run in parallel, in separate OS processes, allowing use of multiple CPU cores. **Parallel queries** are enabled by default in version 10 (`max_parallel_workers_per_gather`), with additional parallelism expected in future releases.

Question 8

Is PostgreSQL as fast as SQL Server?

Accepted Answer

Yes and no. It can actually be faster. Granted, because SQL-Server has had much work done on the query optimizer, PostgreSQL might be a little slower when compared to the Enterprise Ultimate version of SQL-Server (or whatever the current name of the killer-edition is). But, who ever has that version?

If you can use all your CPUs with all cores and all the RAM you want with PostgreSQL, you will always be faster than some cut-down Express or Standard version of SQL-Server with 1 CPU and 1 to a few GB of RAM (windows limits you to 64GB in 64-Bit afaik, with Linux you can exceed the 4 GB RAM per application limit even on a 32 bit processor with the bigmem kernel and can get it to address 64 GB RAM on a 32 bit processor, not to mention the 64-Bit limits), no matter how super-over-optimized the query optimizer is on SQL-Server.

That said, my personal opinion from using both pg and ms is that PostgreSql has the better query optimizer, but that's just my opinion, I have no data to back that up.

Question 9

What is the maximum size for a row, a table, and a database?

Accepted Answer

These are the limits:

* Maximum size for a database? - unlimited (32 TB databases exist)
* Maximum size for a table? - 32 TB
* Maximum size for a row? - 400 GB
* Maximum size for a field? - 1 GB
* Maximum number of rows in a table? - unlimited
* Maximum number of columns in a table? - 250-1600 depending on column types
* Maximum number of indexes on a table? - unlimited

Of course, these are not actually unlimited, but limited to available disk space and memory/swap space. Performance may suffer when these values get unusually large.

The maximum table size of 32 TB does not require large file support from the operating system. Large tables are stored as multiple 1 GB files so file system size limits are not important.

The maximum table size, row size, and maximum number of columns can be quadrupled by increasing the default block size to 32k. The maximum table size can also be increased using table partitioning.

One limitation is that indexes can not be created on columns longer than about 2,000 characters. Fortunately, such indexes are rarely needed. Uniqueness is best guaranteed by a function index of an MD5 hash of the long column, and full text indexing allows for searching of words within the column.

Question 10

How can I sort on whether a field is `NULL` or not?

Accepted Answer

To sort by the `NULL` status, use an `IS NULL` or `IS NOT NULL` test in your `ORDER BY` clause. Things that are `true` will sort higher than things that are `false`, so the following will put `NULL` entries at the front of the output:

```sql
SELECT *
FROM tab
ORDER BY (col IS NOT NULL), col;
```

In PostgreSQL 8.3 and up, you can also control sort ordering of `NULL`s using the recently-standardized `NULLS FIRST`/`NULLS LAST` modifiers, like this:

```sql
SELECT *
FROM tab
ORDER BY col NULLS FIRST;
```

Top 14 PostgreSQL Interview Questions

What is the maximum size for a row, a table, and a database?

What are OIDs in PostgreSQL and why are they useful?

How does PostgreSQL use CPU resources?

What is faster, one big query or many small queries?

Is PostgreSQL as fast as SQL Server?

Why are my queries slow? Why don't they use my indexes?

Which is better for `JOIN` & `INSERT` - PostgreSQL or MySQL?

How do you make schema changes to a live database without downtime?

Why you should never use GUIDs as part of clustered index?

How do I get the value of a `SERIAL` insert?

How do I `SELECT` only the first few rows of a query? A random row?

How do you change a column's data type?

In a query, how do I detect if a field is `NULL`?

How can I sort on whether a field is `NULL` or not?

Top 14 PostgreSQL Interview Questions

What is the maximum size for a row, a table, and a database?

What are OIDs in PostgreSQL and why are they useful?

How does PostgreSQL use CPU resources?

What is faster, one big query or many small queries?

Is PostgreSQL as fast as SQL Server?

Why are my queries slow? Why don't they use my indexes?

Which is better for JOIN & INSERT - PostgreSQL or MySQL?

How do you make schema changes to a live database without downtime?

Why you should never use GUIDs as part of clustered index?

How do I get the value of a SERIAL insert?

How do I SELECT only the first few rows of a query? A random row?

How do you change a column's data type?

In a query, how do I detect if a field is NULL?

How can I sort on whether a field is NULL or not?

Which is better for `JOIN` & `INSERT` - PostgreSQL or MySQL?

How do I get the value of a `SERIAL` insert?

How do I `SELECT` only the first few rows of a query? A random row?

In a query, how do I detect if a field is `NULL`?

How can I sort on whether a field is `NULL` or not?