Postgres Insider Terminology

https://www.crunchydata.com/blog/challenging-postgres-terminology

by todsacerdoti • 4 years ago

116 35 4 years ago

crunchydata.com

leetrout• 4 years ago

That cookie banner takes you to the main page when you try to close it on mobile.

Super annoying.

remram• 4 years ago

Array and relation are definitely not insider terms.

didgetmaster3• 4 years ago

When studying the internals of Postgres years ago, I learned that pages could greatly affect the disk space required to hold a table. If the page size was 8192 bytes (the default) and if the schema defined each row as holding 4097 bytes, then two rows would not fit within a single page. This would cause every row to be within its own page and would waste almost half of the space.

Anyone know if this is still true?

j16sdiz• 4 years ago

Afaik, this is still true in most cases.* this is true for almost every database.

* Except, maybe, toast, hot or compressed..

hashmash2• 4 years ago

this is true for almost every database.

...that uses fixed sized pages.

jeff-davis• 4 years ago

What you are describing is called "internal fragmentation" and it's always a problem at some level in any system.

There are tons of ways to mitigate the problem including variable-length data, out-of-line storage, and compression. Postgres does those things, but I suppose there's always room to improve.

Best to just see how much storage a given table uses, and see if it's a problem.

Helmut100013• 4 years ago

Only slightly related, but the biggest increase in space I saw was after transitioning my 1.8 TB Postgres database to ZFS, which has compression turned on by default. Afterwards, the size needed was 310 GB, with no noticeable loss in speed.

TheNewsIsHere• 4 years ago

That is insanely impressive. Could you share more about the characteristics and nature of that workload? How did it perform above typical loads?

to11mtm• 4 years ago

Yeah it's probably still true. It's also true of other DBs; There is usually a certain amount of overhead per-row on the page, or per-row.

So yeah, you have to either shrink to a size acceptable within padding, or pick a more appropriate page size, or play with some TOAST settings in the case of PG.

ialad• 4 years ago

I was confused by the author's definition of a relation. They said that a single tuple is an "unary relation". They probably meant a single-tuple (1-tuple). Removing the hyphen changes the meaning. https://en.wikipedia.org/wiki/Finitary_relation

layer8• 4 years ago

The author seems to have misunderstood Codd’s definition (which is just the usual definition for mathematical relations). An N-ary relation is an N-column table (in the idealized database model where a table is a mathematical set of rows, i.e. no duplicate rows).

ryandotsmith2• 4 years ago

Missing a fun one: ctid

pramsey• 4 years ago

Yes! Actually, I was thinking "WAL" is constantly thrown around without explanation too.

hot_gril5• 4 years ago

pg_stat_activity

asah• 4 years ago

ctid gives me ptsd !

ryandotsmith2• 4 years ago

ya. i use them when I need to delete duplicates and there is no pk.

hot_gril5• 4 years ago

One term that doesn't seem "insider" but kinda is: `timestamptz`, aka the thing you should always use instead of just `timestamp`. The full name is "timestamp with time zone," but contrary to what most people would assume, it doesn't store a time zone.

eric-hu• 4 years ago

Could you expand on why timestamptz is preferable to timestamp? What does it additionally store?

kurmouk• 4 years ago

`timestamptz` stores the same data I believe. Only that when using `timestamptz` instead of `timestamp`, the server will convert the returned value based on the server time zone configuration. Both datatypes have 8 bytes.

biztos• 4 years ago

Per the docs[0], TIMESTAMP WITH TIME ZONE is preferred because it will automatically convert to/from the stored UTC to whatever your time zone is set to, in the config (by default) or within a query.

I think you also need it for converting input at specified time zones, which is more complicated than one might hope, using AT TIME ZONE.

So the point is about conversion, which is important, but I agree that it’s non-obvious that you need to store the source time zone separately if you ever want to retrieve it.

[0] https://www.postgresql.org/docs/current/datatype-datetime.ht...

burmecia• 4 years ago

Another interesting term not mentioned is 'qual', which basically has same meaning as 'restrictions'.

nraynaud• 4 years ago

I’m pretty sure the “target list” is called the “projection” amongst the tweed-wearing academics. And the “restrictions” would be the “selection”

Postgres Insider Terminology

Related Stories