Question Details

No question body available.

Tags

python sql time-series database-performance griddb

Answers (1)

November 3, 2025 Score: 0 Rep: 21,530 Quality: Medium Completeness: 80%

I’m using GridDB

Ok, great. We're not paying the ACID tax. It would furnish helpful context if you explained the motivation for choosing that database vendor.

My container currently has around 10 million rows

Cool; 10^7 rows is not a "big" number for databases.

this query now takes around 3–5 seconds per device

Whoa, that is whack! grep can go faster than that. When you do EXPLAIN SELECT ..., I imagine it describes some crazy query plan like "table scan"? Ignoring any indexes you might have created? Nosql is not supposed to mean "no indexes".

There's a very simple approach to this, guaranteed to work. Have cron query recent rows from GridDB, and INSERT them into a postgres database. Or MariaDB, sqlite, whatever, there's lots of good options to choose from.

Issue a CREATE INDEX idxdevicetime ON sensordata (deviceid, createdat);. Then your queries will take the appropriate amount of time, independent of total number of rows that have accumulated in the table.

That compound index should probably be the primary key of the table.

Again, 1e7 rows is a small amount of data. Mirroring it to a more appropriate querying environment is not a big drain on storage resources.

Let's see if the GridDB situation can be salvaged. The docs are not very encouraging, based on

CREATE INDEX ... ON tablename ( columnnametobe_indexed );

The fact that we're stuck with "column", rather than compound "columns", is trouble for your Use Case.

Any relational database would easily be able to create a compound index on (A, B), and on (C, D), and on several other attributes if they correspond to queries of interest.

Let's head over to the CREATE TABLE documentation.

Composite primary key can be set to a table ... by setting the primary key after describing the column definition. The composite primary key must be set to the columns which are continuous from the first column and can be set up to 16 columns.

Well that's encouraging.

We can't create arbitrarily many compound keys, but perhaps that is just enough for your use case. And then you wouldn't need to copy recent rows into an RDBMS.