What’s the best database for HA with a lot of entities?

Configuration
23

Hi, not sure if you read my post above, but I had removed it from my yaml. The issue was that the sensor was not longer working. The issue is that none of these errors was in my log at all, until the last update.

The only place this sensor could be found was in the core log files which I found by doing a local search for SQL in troubleshooting my DB errors…

24

I did read your post above, the core log files is what I’m talking about. So your entity was migrated to the UI, when it was migrated, a warning would appear in your core logs to remove the yaml because it was migrated to the UI. This occurred over a 2 or 3 month grace period. Then, that warning turned into an error simply saying “remove this from yaml”. At that point, it didn’t mention migration because it was already migrated months ago. This apparently is when you noticed it.

25

no it was stuck in my core.config_entries file… no sign of it in my ui as a sensor

no sign of the sensor having any errors in the log files…

and yaml all removed from my sensors.yaml

the only place that I could find any reference to this SQL sensor was in my core.config_entries file… this is where I removed it as it was not showing up anywhere else…

26

If it’s in config entries, it would show up in the integrations section in your UI with the name SQL. Config entries drives that UI.

27

would or should? :slight_smile: anyway, it all seems to be stable. I am now using timescale and it looks solid again… I do think I need to do a complete rebuild again from scratch as I have simply been backup/updating since I started this wonderful journey.

28

It will, not a should. Config entries drives all things loaded in the UI.

29

Can you give some details on how you setup QuestDB?

I like to give it a try, but am getting the error

[homeassistant.components.recorder.core] Error during connection setup: (psycopg2.DatabaseError) unbalanced ]
LINE 3: ...ents' AND pg_catalog.pg_class.relkind = ANY (ARRAY['r', 'p',...

when I had a go at setting it up via the postgresql url option of the recorder configuration.

30

For me the connecting via a postgres connection string is working. I use qss as a Docker container. My compose file looks like this:

version: '3.7'

services:
  questdb:
    image: "questdb/questdb"
    container_name: "questdb"
    restart: "always"
    networks:
      - "bridge"
    ports:
      - "9008:9000"
      - "9009:9009"
      - "8812:8812"
      - "9003:9003"
    volumes:
      - "${DOCKER_DATA}/questdb:/root/.questdb"
    environment:
      - "TZ=Europe/Berlin"
      - "QDB_PG_USER=${USER}" # postgresql user -> Configured in .env file
      - "QDB_PG_PASSWORD=${PASSWORD}" # postgresql password -> Configured in .env file
      - "QDB_TELEMETRY_ENABLED=false" # Disable telemetry
networks:
  bridge:
    driver: "bridge"

And the connection string like this:
postgresql://user:password@SERVER_IP/DB_NAME

I only logged in once with Dbeaver to create the database ‘DB_NAME’ for the connection string.
Otherwise, I have not made any further configurations regarding the connection to the Quest DB.
But as I already wrote, I currently use TimescaleDB because it offers me more analytical functions. I would probably have to take another look at my QSS configuration to see if it still works.

31

Was that using the just the recorder component? Or is the QSS custom component needed as well? I was instead trying to use QuestDB as the database for the recorder component without QSS - I thought QSS was for long term storage.

32

As the recorder component.
The QSS custom component is for long term state storage an it is using a native questdb ingress conntection instead of psycopg.

33

Do you know which versions of home assistant and questdb you tested with?

34

No, but they must have been the latest versions from 9-10 months ago