Live data from Hacker News

Postgres Insider Terminology

crunchydata.com

11–20 of 36 posts

Re: Postgres Insider Terminology

#11

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?

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.

Re: Postgres Insider Terminology

#12
post #6

Earlier quoted context omitted.

> this is true for almost every database. ...that uses fixed sized pages.

so true for almost every sql database? or not?

I think it is true for every row-oriented database that stores all the values for a single row together within a fixed-sized structure. Columnar stores on the other hand may or may not do this.

I have built a database engine that is a columnar store. All the values for each column are stored together. This requires just about every query to fetch each row value separately, but it has proven to be incredibly fast. Queries on big tables run several times faster than on Postgres and it is also about 5x faster than on SQLite. https://www.youtube.com/watch?v=Va5ZqfwQXWI

Re: Postgres Insider Terminology

#13

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?

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.

Re: Postgres Insider Terminology

#15
post #7

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

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).

Re: Postgres Insider Terminology

#19
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.
Post reply on HN