Databases in 2021: A Year in Review

Re:Databases in 2021: A Year in Review by why-el on Wednesday December 29, 2021 @09:08PM (#29732688)

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 [2] https://techcommunity.microsoft.com/t5/azure-database-for-po...

[ Parent | on HN ]

Re:Databases in 2021: A Year in Review by newlisp on Wednesday December 29, 2021 @11:47PM (#29733714)

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

[ Parent | on HN ]

Re:Databases in 2021: A Year in Review by sa46 on Thursday December 30, 2021 @12:12AM (#29733854)

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

[ Parent | on HN ]

Re:Databases in 2021: A Year in Review by manigandham on Thursday December 30, 2021 @01:31AM (#29734231)

I've never seen a business actually use them, large or small. Any auditing requirements are usually fed from other sources, like Kafka event streams, files on S3, or a OLAP data warehouse.

[ Parent | on HN ]

Re:Databases in 2021: A Year in Review by code_biologist on Thursday December 30, 2021 @06:12AM (#29735732)

How do you set up and feed the warehouse? Temporal-ish tables have been an obvious, simple, and mostly foolproof solution for many of our historical analytics and reporting needs.

Bitemporal stuff (enabling edited versions of history) is where things get hairy and I definitely question the utility outside of a dedicated use case.

[ Parent | on HN ]

Re:Databases in 2021: A Year in Review by manigandham on Thursday December 30, 2021 @10:50PM (#29744683)

Most databases have built-in CDC (change data capture) that can be exported. Otherwise the WAL logs can be read with other tooling.

Debezium is a great open-source product for streaming changes from many relational databases: https://debezium.io

[ Parent | on HN ]

Re:Databases in 2021: A Year in Review by code_biologist on Thursday December 30, 2021 @06:05AM (#29735695)

Would love to see wider support for temporal tables, but application level approaches like https://github.com/jazzband/django-simple-history have worked for the business issues I have.

[ Parent | on HN ]

Re:Databases in 2021: A Year in Review by p_l on Thursday December 30, 2021 @03:27AM (#29734838)

Interestingly enough, Postgres used to have time travel in tables before MVCC transactions were added. Apparently it wasn't exactly used feature.

[ Parent | on HN ]

Re:Databases in 2021: A Year in Review by roenxi on Thursday December 30, 2021 @01:24AM (#29734201)

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.

[ Parent | on HN ]

Re:Databases in 2021: A Year in Review by tpetry on Thursday December 30, 2021 @04:44AM (#29735216)

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.

[ Parent | on HN ]

Re:Databases in 2021: A Year in Review by ivank on Thursday December 30, 2021 @04:47AM (#29735231)

I use https://github.com/xocolatl/periods for this to some success.

[ Parent | on HN ]

Re:Databases in 2021: A Year in Review by srcreigh on Thursday December 30, 2021 @02:41AM (#29734594)

What do temporal tables do that good queries don't?

[ Parent | on HN ]

Re:Databases in 2021: A Year in Review by InsaneOstrich on Thursday December 30, 2021 @03:40AM (#29734906)

Auditing of changes. We have to have a second table that stores history for any table that may need to be audited in the future

[ Parent | on HN ]

Re:Databases in 2021: A Year in Review by nightpool on Thursday December 30, 2021 @01:05AM (#29734124)

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?

[ Parent | on HN ]

Re:Databases in 2021: A Year in Review by lenkite on Sunday January 02, 2022 @10:49PM (#29777124)

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

[ Parent | on HN ]

Re:Databases in 2021: A Year in Review by nicoburns on Wednesday December 29, 2021 @09:45PM (#29732936)

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

[ Parent | on HN ]

Re:Databases in 2021: A Year in Review by phonon on Wednesday December 29, 2021 @10:04PM (#29733065)

Looks like it's still being actively worked on.

https://www.cybertec-postgresql.com/en/zheap-undo-logs-disca...

https://github.com/cybertec-postgresql/postgres/tree/zheap_u...

[ Parent | on HN ]

Re:Databases in 2021: A Year in Review by srcreigh on Thursday December 30, 2021 @02:11AM (#29734454)

Clustered indexes?

[ Parent | on HN ]