Can TSqlRecord primary key be changed?

wxinix · 2021-10-09 05:07:28

The default primary key is "ID", can we include more fields as the primary key?

I am asking this because, in order to partition postgresql db,

https://www.postgresql.org/docs/13/ddl- … oning.html , Section 5.11.2.3, says:

- Unique constraints (and hence primary keys) on partitioned tables must include all the partition key columns. This limitation exists because the individual indexes making up the constraint can only directly enforce uniqueness within their own partitions; therefore, the partition structure itself must guarantee that there are not duplicates in different partitions.

In other words, in order to partition, the partition key must be part of the primary key.

I guess the answer is NO. Then this leads to the conclusion that we cannot partition postgresql db with the mORMot generated tables, with this primary key requirement.

ab · 2021-10-09 07:47:00

SQLite3 requires a RowID/ID Int64 primary key for its virtual tables, therefore the TOrm.ID is mandatory.

But you can have several primary indexes.
I mean, you can have unique contraints around the partition key columns, AND still have an Int64 RowID primary key.
The ORM will use RowID, whereas the partition will use its own set of partition keys.

This is not supported out of the box by the ORM. The TOrm and IRestOrm only know about TOrm.ID.
So you have to manually maintain the partition key columns uniqueness and consistency.

mpv · 2021-10-09 11:40:38

Postgres (and most or RDBMS) not allows several primary keys on table. The only way I see is to have primary key what includes partition column, and another non primary unique key for ID column as required by ORM.

@wxinix - what is your goal of using partitions? From my experience it needed very rarely, mostly for historical data (partition by years/month) in case table contains > 100 millions of rows. For data isolation Postgre RLS can be used, and it transparent for ORM

wxinix · 2021-10-09 12:03:43

mpv wrote:

Postgres (and most or RDBMS) not allows several primary keys on table. The only way I see is to have primary key what includes partition column, and another non primary unique key for ID column as required by ORM.
@wxinix - what is your goal of using partitions? From my experience it needed very rarely, mostly for historical data (partition by years/month) in case table contains > 100 millions of rows. For data isolation Postgre RLS can be used, and it transparent for ORM

I tried and it doesn’t work with postgresql

The partition key must be part of the primary key and any unique constraint.

I have an ever-increasing time series database - at a rate of 100 million records per day - so I need to partition it for query performance. I need to keep at least 1 year data (365000 million records)

ab · 2021-10-09 12:21:55

The "primary key" I told is about SQlite3 virtual table and mORMot TOrm, not PostgreSQL.

At PostgreSQL level, the ID column won't be a primary key, just an UNIQUE column storing a BigInt.
Then mORMot would be able to use it for its ORM queries and process, and PostgreSQL would only see it as a secondary index.

wxinix · 2021-10-09 12:22:56

@ ab

I understand mORMot needs to have the 64bit integer ID as the primary key

Does it have to be continuously incremented?

Is there a way to encode the month number (1-12) in it?

ab · 2021-10-09 12:34:34

No, the ID just need to be unique.
You need to pre-compute its value then set the "ForceID" flag to TRUE when adding the record in the insertion batch, together with the other fields, including the partitioned primary keys.

You can put the month in some of its bits.
But my concern is that you should take care of avoiding any collusion, and ahead-of-time computation.
I would put the day number since 2021 in the upper bits, then a 32-bit counter in the lower bits.

You may take a look at our TSynUniqueIdentifier and its associated generator, but I am not sure its TSynUniqueIdentifierBits mapping is safe enough for your.
Its 15-bit increasing counter means only 32768 values per second, which may be good enough only if the values are generated in a fair way around the day.
If I am right, 100 million per days means 1157 per second. So it could be good enough for a continuous generation - not if there are bursts of process with more than 32K/second: then it will trick the time and you may be less efficient/precise.

One benefit of TSynUniqueIdentifier is that it is unsynchronized, and could be used safely in up to 65536 nodes in parallel.
And very fast (you can generate around 30 millions of IDs per second):

  - TSynUniqueIdentifier: 1,300,002 assertions passed  348.71ms
     200000 ComputeNew in 6.46ms i.e. 29.5M/s, aver. 32ns

Its ID are also timestamped at the second resolution, and always increasing.

wxinix · 2021-10-09 12:46:53

Thank you very much for the advice. I'll look into it.

The time series data is generated from sensors deployed at city streets (6~7K streets), at an interval of every 1 minute - so the ID burst is really happening every 1 minute, thus we need 6K new IDs to be generated every 1 minute. Ah - I mis-stated - the daily data increment is not 100 million but 10 million records/day (3650 million/yearly).

Last edited by wxinix (2021-10-09 12:54:14)

mpv · 2021-10-09 12:49:31

For such capacity Postgres IMHO is not a best candidate. Even with partition indexes become too huge to fit in memory.
We uses InfluxDB for last few years to store a time-series. With proper downsampling and data retention even on midle-level server it will fit your needs.
Also Influx uses very efficient data compression internally.
We use HTTP interface to put data into Influx (app server group data into portions and bulk insert using HTTP POST), and HTTP to retrieve a data.
Also We use Grafana to visualize a data.

ab · 2021-10-09 12:54:08

So I would create a custom ID as such, from higher to lower bit:
- 24-bit TimeStamp = minute since 1th January 2021
- 17-bit StreetNumber (up to 131,072 streets)
- 16-bit Node Generator ID (you could avoid those bits if you have a single server generating the IDs)
- 4-bit increasing counter (you could avoid those bits since they should be generated every minute)

So in practice, an ID of 41-bit could be enough.
And you could use this ID to quickly query for a given measure from the ORM code, just from time + Street number, using a regular TOrm per TID query. Benefit is that you could enable the per-ID cache.

The index on this ID would be used to quickly query some values using regular > < comparison for a range.
If you need to query per time, i.e. query for a given time range, put the TimeStamp in the upper bits.
If you need to query per street, then put the StreetNumber in the upper bits.

ab · 2021-10-09 13:00:11

For 7K values per minute, PostgreSQL could be enough.

Even SQlite3, perhaps with our sharding abilities, or gathering streets in several DBs (one SQLite3 DB file per 1K street), or one DB file per year.

wxinix · 2021-10-09 13:04:26

ab wrote:

For 7K values per minute, PostgreSQL could be enough.
Even SQlite3, perhaps with our sharding abilities, or gathering streets in several DBs (one SQLite3 DB file per 1K street), or one DB file per year.

Thank you - I am also thinking of exploring mORMot sharding abilities - but putting 1 year's data into a single SQLiteDB file - the file size would be huge I guess? No problem with that?

ab · 2021-10-09 19:40:41

I had SQlite3 databases > 40GB with no problem.

The advantage is that you can copy the file and use it stand-alone e.g. for reporting or data mining.
Of course, for complex queries PostgreSQL may be better. But SQLite3 has pretty good range support, using RTree tables for indexing multiple dimensions.

7K values per minute is not huge. It gathers to 10M entries per day, or 3 679 M per year. You could use a SQLite3 database per week or month, for instance.
It may not be required to have a Time Series DB. But it sometimes, for bigdata processing using Map/Reduce process, it may be needed.
For LiveMon, I wrote a Time Series DB which received 2K values per second and per client, with thousands of clients on the same server, which pushed the data to 500 distributed servers. We use SQLite3 for metadata/indexing, but our own format for the values.

All depends on what you expect to do with the data.
You may put the data into several databases: SQLite3 for MicroService, local or stand-alone process, PostgreSQL for melting complex SQL queries, a time series DB for big data process... Each kind DB has its pro and cons.
My advice would be to make your persistence layer "agnostic", i.e. you should try to be able to switch from one DB to another if needed. Don't start from the DB, but from your data/classes.

mORMot Open Source

#1 2021-10-09 05:07:28

Can TSqlRecord primary key be changed?

#2 2021-10-09 07:47:00

Re: Can TSqlRecord primary key be changed?

#3 2021-10-09 11:40:38

Re: Can TSqlRecord primary key be changed?

#4 2021-10-09 12:03:43

Re: Can TSqlRecord primary key be changed?

#5 2021-10-09 12:21:55

Re: Can TSqlRecord primary key be changed?

#6 2021-10-09 12:22:56

Re: Can TSqlRecord primary key be changed?

#7 2021-10-09 12:34:34

Re: Can TSqlRecord primary key be changed?

#8 2021-10-09 12:46:53

Re: Can TSqlRecord primary key be changed?

#9 2021-10-09 12:49:31

Re: Can TSqlRecord primary key be changed?

#10 2021-10-09 12:54:08

Re: Can TSqlRecord primary key be changed?

#11 2021-10-09 13:00:11

Re: Can TSqlRecord primary key be changed?

#12 2021-10-09 13:04:26

Re: Can TSqlRecord primary key be changed?

#13 2021-10-09 19:40:41

Re: Can TSqlRecord primary key be changed?

Board footer