Earlier quoted context omitted.
ctid gives me ptsd !
ya. i use them when I need to delete duplicates and there is no pk.
Ohh. I always used row_number for that.
Postgres Insider Terminology
21–30 of 36 posts
Re: Postgres Insider Terminology
#22Another interesting term not mentioned is 'qual', which basically has same meaning as 'restrictions'.
Re: Postgres Insider Terminology
#23I’m pretty sure the “target list” is called the “projection” amongst the tweed-wearing academics. And the “restrictions” would be the “selection”
Re: Postgres Insider Terminology
#24Earlier quoted context omitted.
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. Que…
Most columnar stores I'm aware of are hybrid, so all columns of a row are still colocated in the same page.
Re: Postgres Insider Terminology
#25One 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.
Could you expand on why timestamptz is preferable to timestamp? What does it additionally store?
Re: Postgres Insider Terminology
#26One 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.
Could you expand on why timestamptz is preferable to timestamp? What does it additionally store?
`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.
Re: Postgres Insider Terminology
#27One 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.
Could you expand on why timestamptz is preferable to timestamp? What does it additionally store?
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...
Re: Postgres Insider Terminology
#28Earlier quoted context omitted.
Could you expand on why timestamptz is preferable to timestamp? What does it additionally store?
`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.
But normally you have a server between the user and the db, where you also prefer to keeps things as utc, while in memory. Then conversion to local time happens as the very last step in the presentation layer.
So unless you are using the sql result verbatim as presentation layer, is there any big benefit of this? On the contrary it seems like yet another moving part one could mess up and accidentally convert time where it shouldn’t?
Re: Postgres Insider Terminology
#29Earlier quoted context omitted.
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. Que…
Most columnar stores I'm aware of are hybrid, so all columns of a row are still colocated in the same page.
Mine is not. If a table has 4 columns (e.g. name, address, phone, email), then all the names are stored separately from the addresses. Likewise all the phone numbers are stored separately from the emails. The data is de-duped so it is incredibly easy to find out how many of each value is in each column (e.g. there are 1,234,567 rows in the table where name = 'John').
Re: Postgres Insider Terminology
#30When 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.
That is insanely impressive. Could you share more about the characteristics and nature of that workload? How did it perform above typical loads?