27 ms·

Unfortunately with Postgres, generated columns are always stored on disk, so you lose some storage space.

by RedShift1 4y ago

bawolff 4y ago
Finally, something for mariadb fans to hold on to in the flame war.
- RedShift1 4y ago
  Haha I guess :-D. Though I have a workaround for complicated generated columns that I don't wish to store: using a function. So my select would look like: SELECT a, b, something_complicated(table) FROM table. Of course you have to remember to do that but hey it's something.
- srcreigh 4y ago
  My reason for liking MariaDB is that MySQL uses a B-Tree for primary storage. In Postgres if you want a table with large keys, you have to store them twice once in the index once in primary storage (hash table). In MySQL there’s no hash table you basically just store the rows in an index. Could be as much as 40% reduction in storage… 400GB instead of 1TB. Postgres does have index compression as of a recent version, but I’m not sure how much that would improve random keys.
  - ttfkam 4y ago
    A 40% reduction would be 600GB, and an index that took up 40% of the total would be excessive/unrealistic as well. You're right of course that Postgres lacks true clustered indexes. The closest thing it has is covering indexes, which don't help at all with saving space; quite the opposite in fact.
    - srcreigh 4y ago
      Thanks. You’re right. And yeah, the 40% ish reduction would only happen if the table has for ex every column in a unique index. Not very common.
- eyelidlessness 4y ago
  I prefer Postgres, and the thing I most envy from Maria is first class bitemporal tables. Granted it took me probably a week curiously returning to the topic to fully grasp their value, and I don’t think I’m alone in that lack of immediate intuition. And granted non-SQL databases like Datomic are much better positioned for use cases where people realize they want the feature. And granted it definitely can’t be more storage efficient to store bitemporal data than not to. But I think it’s a much more compelling feature, from the outside. As far as memory complexity, this got me thinking a userspace implementation (I’ve prototyped one for Postgres using VIEWs for fun and personal education) could reasonably borrow optimization techniques from persistent data structures common in FP languages. But I don’t know how well a query planner would cope with that, and my instinct is that the optimization would be better applied at the storage engine level (again as I see it, advantage: Maria).
  - ttfkam 4y ago
    The key to temporal tables in Postgres is the following: • add a timestamp range column • create a history table that matches the main table • using table inheritance, have the live table inherit from the history table At this point all queries on the main table are "live" and queries on the history include past and present data. • add an insert trigger that initializes the live table's timestamp range to start "now()" • add AFTER UPDATE/DELETE/TRUNCATE triggers to insert the OLD record into the history table with appropriate timestamp ranges Bonus • have event triggers automatically set up the temporal tables on CREATE TABLE
    - eyelidlessness 4y ago
      So for temporal data this is a pretty solid approach. For bitemporal data, it’s more complicated to do in plain SQL (though it can be done). To clarify: I’m using temporal here the same way you are, as an immutable/append-only record of state as it existed at the time. Whereas bitemporal data has that property plus a separate historical record of when data was/should have been/should be or optimistically will be effectively true. The thing which helped me grasp this was correcting historical accounting according to the domain (schema), eg updating meeting records. You of course want to preserve the previous record upon update, but the “current” state of what was decided a week ago might still change.
ttfkam 4y ago
Just make a function with the table name as a parameter. Same effect as what you're looking for except not included in SELECT *. eg. SELECT col1, col2, col3(t) FROM table_name t; This is how Postgraphile solved the problem to great effect. https://www.graphile.org/postgraphile/computed-columns/ https://www.graphile.org/postgraphile/computed-columns/

Unfortunately with Postgres, generated columns are always stored on disk, so you lose some storage space.

bawolff 4y ago

Finally, something for mariadb fans to hold on to in the flame war.

RedShift1 4y ago
Haha I guess :-D. Though I have a workaround for complicated generated columns that I don't wish to store: using a function. So my select would look like: SELECT a, b, something_complicated(table) FROM table. Of course you have to remember to do that but hey it's something.
srcreigh 4y ago
My reason for liking MariaDB is that MySQL uses a B-Tree for primary storage. In Postgres if you want a table with large keys, you have to store them twice once in the index once in primary storage (hash table). In MySQL there’s no hash table you basically just store the rows in an index. Could be as much as 40% reduction in storage… 400GB instead of 1TB. Postgres does have index compression as of a recent version, but I’m not sure how much that would improve random keys.
- ttfkam 4y ago
  A 40% reduction would be 600GB, and an index that took up 40% of the total would be excessive/unrealistic as well. You're right of course that Postgres lacks true clustered indexes. The closest thing it has is covering indexes, which don't help at all with saving space; quite the opposite in fact.
  - srcreigh 4y ago
    Thanks. You’re right. And yeah, the 40% ish reduction would only happen if the table has for ex every column in a unique index. Not very common.
eyelidlessness 4y ago
I prefer Postgres, and the thing I most envy from Maria is first class bitemporal tables. Granted it took me probably a week curiously returning to the topic to fully grasp their value, and I don’t think I’m alone in that lack of immediate intuition. And granted non-SQL databases like Datomic are much better positioned for use cases where people realize they want the feature. And granted it definitely can’t be more storage efficient to store bitemporal data than not to. But I think it’s a much more compelling feature, from the outside. As far as memory complexity, this got me thinking a userspace implementation (I’ve prototyped one for Postgres using VIEWs for fun and personal education) could reasonably borrow optimization techniques from persistent data structures common in FP languages. But I don’t know how well a query planner would cope with that, and my instinct is that the optimization would be better applied at the storage engine level (again as I see it, advantage: Maria).
- ttfkam 4y ago
  The key to temporal tables in Postgres is the following: • add a timestamp range column • create a history table that matches the main table • using table inheritance, have the live table inherit from the history table At this point all queries on the main table are "live" and queries on the history include past and present data. • add an insert trigger that initializes the live table's timestamp range to start "now()" • add AFTER UPDATE/DELETE/TRUNCATE triggers to insert the OLD record into the history table with appropriate timestamp ranges Bonus • have event triggers automatically set up the temporal tables on CREATE TABLE
  - eyelidlessness 4y ago
    So for temporal data this is a pretty solid approach. For bitemporal data, it’s more complicated to do in plain SQL (though it can be done). To clarify: I’m using temporal here the same way you are, as an immutable/append-only record of state as it existed at the time. Whereas bitemporal data has that property plus a separate historical record of when data was/should have been/should be or optimistically will be effectively true. The thing which helped me grasp this was correcting historical accounting according to the domain (schema), eg updating meeting records. You of course want to preserve the previous record upon update, but the “current” state of what was decided a week ago might still change.

ttfkam 4y ago

Just make a function with the table name as a parameter. Same effect as what you're looking for except not included in SELECT *. eg. SELECT col1, col2, col3(t) FROM table_name t; This is how Postgraphile solved the problem to great effect. https://www.graphile.org/postgraphile/computed-columns/ https://www.graphile.org/postgraphile/computed-columns/