7 ms·

Postgres's dominance is well deserved, of course. My only concerns with it, both are actively worked on, are bloat management (significant for update heavy work

by why-el 5y ago

Postgres's dominance is well deserved, of course. My only concerns with it, both are actively worked on, are bloat management (significant for update heavy workloads and programmers used to the MySQL model of rollback segments) and the scaling of concurrency (going over 500 connections). Bloat was taken over by Cybertec[1] after stalling for a bit and is funded (yay), while concurrency was also enhanced out of Microsoft [2]. All in all, an excellent future for our beloved Postgres. [1] https://github.com/cybertec-postgresql/zheap https://github.com/cybertec-postgresql/zheap [2] https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/improving-postgres-connection-scalability-snapshots/ba-p/1806462#conclusion-one-bottleneck-down-in-pg-14-others-in-sight https://techcommunity.microsoft.com/t5/azure-database-for-po...

lenkite 5y ago
I wish Postgres was more SQL standards compliant. Stuff like using `nextval()` instead of `NEXT VALUE` in SQL sequences is a pain.
nicoburns 5y ago
Is zheap definitely still an active project? Last commit seems to be Oct 2020
- phonon 5y ago
  Looks like it's still being actively worked on. https://www.cybertec-postgresql.com/en/zheap-undo-logs-discarding-in-postgresql/ https://www.cybertec-postgresql.com/en/zheap-undo-logs-disca... https://github.com/cybertec-postgresql/postgres/tree/zheap_undo_record_set_pg_14.1 https://github.com/cybertec-postgresql/postgres/tree/zheap_u...
redis_mlc 5y ago
newlisp 5y ago
Another concern, no temporal tables, don't businesses demand this feature?
- sa46 5y ago
  In Postgres land, I think most businesses work around temporal tables with audit tables using triggers to dump jsonb or hstore. I wrote up how I used table-inheritance here [1]. I agree with your point. Postgres is starting to stick out compared to alternatives: - MS SQL supports uni-temporal tables using system time. - Snowflake has time travel which acts like temporal tables but with a limited retention window. Seems more like a restore mechanism. - MariaDB has system-versioned tables (doesn't look like it's in MySQL). - Cockroach DB has uni-temporal support with system time but limited to the garbage collection period. The docs indicate you don't want a long garbage collection period since all versions are stored in a single range. - Oracle seems to have the best temporal support with their flashback tech. But it's hard to read between the lines to figure out what it actually does. [1]: https://news.ycombinator.com/item?id=29010446 https://news.ycombinator.com/item?id=29010446
- nightpool 5y ago
  i’ve very rarely found that using a full temporal table is the right choice for online analysis—a dedicated schema serves you better in the long run and helps you design your indexes, etc appropriately. For compliance, PIT backups via WAL shipping should suffice, no?
- roenxi 5y ago
  Although temporal tables are a really good idea; it is possible to get away without them being a first class feature. They aren't hard to mimic if you can give up the guarantee of catching every detail. In an ideal world (ha ha, silly thought) the tables would be designed to be append-only anyway, or the amount of data would be significant. Both of which make temporal tables somewhat moot.
  - tpetry 5y ago
    They are really easy to mimic in PostgreSQL with range types (tstzrange) and an exclusion constraint, so now overlapping values are allowed. I guess they will not add it to the core if a developer can add support to them so easy.
srcreigh 5y ago
Clustered indexes?

by why-el 5y ago

lenkite 5y ago

I wish Postgres was more SQL standards compliant. Stuff like using `nextval()` instead of `NEXT VALUE` in SQL sequences is a pain.

nicoburns 5y ago

Is zheap definitely still an active project? Last commit seems to be Oct 2020

phonon 5y ago
Looks like it's still being actively worked on. https://www.cybertec-postgresql.com/en/zheap-undo-logs-discarding-in-postgresql/ https://www.cybertec-postgresql.com/en/zheap-undo-logs-disca... https://github.com/cybertec-postgresql/postgres/tree/zheap_undo_record_set_pg_14.1 https://github.com/cybertec-postgresql/postgres/tree/zheap_u...

redis_mlc 5y ago

newlisp 5y ago

Another concern, no temporal tables, don't businesses demand this feature?

sa46 5y ago
In Postgres land, I think most businesses work around temporal tables with audit tables using triggers to dump jsonb or hstore. I wrote up how I used table-inheritance here [1]. I agree with your point. Postgres is starting to stick out compared to alternatives: - MS SQL supports uni-temporal tables using system time. - Snowflake has time travel which acts like temporal tables but with a limited retention window. Seems more like a restore mechanism. - MariaDB has system-versioned tables (doesn't look like it's in MySQL). - Cockroach DB has uni-temporal support with system time but limited to the garbage collection period. The docs indicate you don't want a long garbage collection period since all versions are stored in a single range. - Oracle seems to have the best temporal support with their flashback tech. But it's hard to read between the lines to figure out what it actually does. [1]: https://news.ycombinator.com/item?id=29010446 https://news.ycombinator.com/item?id=29010446
nightpool 5y ago
i’ve very rarely found that using a full temporal table is the right choice for online analysis—a dedicated schema serves you better in the long run and helps you design your indexes, etc appropriately. For compliance, PIT backups via WAL shipping should suffice, no?
roenxi 5y ago
Although temporal tables are a really good idea; it is possible to get away without them being a first class feature. They aren't hard to mimic if you can give up the guarantee of catching every detail. In an ideal world (ha ha, silly thought) the tables would be designed to be append-only anyway, or the amount of data would be significant. Both of which make temporal tables somewhat moot.
- tpetry 5y ago
  They are really easy to mimic in PostgreSQL with range types (tstzrange) and an exclusion constraint, so now overlapping values are allowed. I guess they will not add it to the core if a developer can add support to them so easy.

srcreigh 5y ago

Clustered indexes?