Search code examples
databasevertica

Verica - Flex table limit

I am approaching on Vertica and I have some questions about flex tables:

  1. how many rows can manage a flex table? Is it a good idea to manage with a flex table up to half a billion of rows?

  2. can I delete certain rows from big flex table without any performance problem? I need to run an optimize table (if exists like mysql)?

  3. for run sql query and csv export, is better to perform from flex table or from the associated view?

Solution
    1. I still have to find a limit to the rows you can put into a flex table. So the idea is good.
    2. I would personally not use a flex table for DML operations. As soon as you want to keep the table for reporting and further processing, I would make it a standard table: If the view you created is foo_view, run: CREATE TABLE foo_fix AS SELECT * FROM foo_view;. That's the best possible optimisation you can get. Vertica flex tables are a perfect means to discover what is in an unknown data source. But for serious querying, they are orders of magnitude slower than standard Vertica ROS tables.
    3. Selecting from flex table directly (knowing all keys of the flex table) is roughly the same as selecting from the view generated on it.