Advice

I am working on a highload system, which needs to process quite a lot of events, but not an insanely huge amount. The data ingested has to be editable during a short period of time, and then the need for editing diminishes. I want to store that data in a cache while it's "hot", and then save it to PG when the data "cools down". I want to be able to partition my tables based on the dates, and drop partitions as they expire. This way I try to avoid update and delete related vacuum. Thus I need to be able to assign record ids in my cache, but such that they don't conflict in the db.

PG's bigserial type is so big that I can use nanosecond precision timestamps and have the last three digits to use as random "tie brake", and that will last me till 2262. I will be able to partition my main table on its primary key and the related table on their foreign keys, thus keeping my related data in the same partitions.

Is it a good idea?

Improve this question

3 Replies 3

Laurenz Albe
•

Use uuidv7(). That does exactly what you want and is simpler. But if the additional 8 bytes per row are a problem, use your technique.

Improve this answer
FranckPachot
•

Three random digits may not be sufficient to prevent collisions, and detecting duplicates only at insertion time (using a primary key) could be too late. What happens if the system clock drifts? What if you have multiple application servers without perfectly synchronized clocks? Maybe better something like Snowflake ID with less presition on the time but with a node/thread ID and a sequence number per node/thread rather than a small random

Improve this answer
Sergey Prokhorenko
•

PostgreSQL Gains a Built-in UUIDv7 Generation Function for Primary Keys

Improve this answer

Your Reply

By clicking “Post Your Reply”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.