Does column order matter in PostgreSQL tables?

Question

For example, I have table users(id, username, email).

Is there any difference between following queries ? (maybe performance, or anything else)

SELECT id, username, email FROM users

and

SELECT id, email, username FROM users

Generally, does column order matter creating PostgreSQL tables ?

@RavinderReddy, postgresql is not mentioned there. Is this answer true for postgresql? — Teimuraz, Dec 24 '15 at 12:17
If you're interested in physical representation of rows in PostgreSQL (including space and performance issues), see this presentation: https://github.com/dhyannataraj/tuple-internals-presentation — Egor Rogov, Dec 24 '15 at 16:11
Does this answer your question? [Calculating and saving space in PostgreSQL](https://stackoverflow.com/questions/2966524/calculating-and-saving-space-in-postgresql) — mlt, Aug 09 '20 at 01:09

Mike Sherrill 'Cat Recall' · Answer 1 · 2015-12-25T11:15:54.110

The order of columns doesn't matter in creating tables in PostgreSQL, but it does matter sometimes in creating indexes in PostgreSQL.

PostgreSQL implements primary keys with an underlying unique index. If the primary key contains more than one column, then PostgreSQL creates the underlying index with columns in the order you declare them. Some queries against part of that primary key will be able to use the index; some will not.

This quote is from the docs on multicolumn indexes.

A multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns, but the index is most efficient when there are constraints on the leading (leftmost) columns. The exact rule is that equality constraints on leading columns, plus any inequality constraints on the first column that does not have an equality constraint, will be used to limit the portion of the index that is scanned. Constraints on columns to the right of these columns are checked in the index, so they save visits to the table proper, but they do not reduce the portion of the index that has to be scanned. For example, given an index on (a, b, c) and a query condition WHERE a = 5 AND b >= 42 AND c < 77, the index would have to be scanned from the first entry with a = 5 and b = 42 up through the last entry with a = 5. Index entries with c >= 77 would be skipped, but they'd still have to be scanned through. This index could in principle be used for queries that have constraints on b and/or c with no constraint on a — but the entire index would have to be scanned, so in most cases the planner would prefer a sequential table scan over using the index.

Thank you for answer. Is it related only to primary key? or is it true for other indexes? — Teimuraz, Dec 25 '15 at 09:55
"The order of columns doesn't matter in creating tables in PostgreSQL" - glad that @mlt posted below, since ordering *does* matter for storage size — kevlarr, May 02 '23 at 14:21

mlt · Answer 2 · 2020-08-09T01:04:32.903

4

It does matter... slightly sometimes. There is something like data structure alignment. If you start using a mixture of types that are less than 4 bytes (smallint, char(1) etc), your table might get bigger if you interleave them. If you do use types that are at least 4 bytes, the order does not matter.

There is a good read https://www.2ndquadrant.com/en/blog/on-rocks-and-sand/

edited Aug 09 '20 at 01:04

answered Aug 09 '20 at 00:48

mlt

1,595
2
21
52

Does column order matter in PostgreSQL tables?

2 Answers2