FWD » Core Development » Database

Stefanel, another question: if there will be a need to rebuild the tsvector columns, this should be able to be done without dropping and adding them again, right?

Constantin Asofiei wrote:

Stefanel, another question: if there will be a need to rebuild the tsvector columns, this should be able to be done without dropping and adding them again, right?

Actually, you're right. To force PostgreSQL to recalculate the tsvector__ values for every single row in the table, we can run a dummy update using the DEFAULT keyword:

UPDATE <table> SET tsvector__<field> = DEFAULT;
REINDEX TABLE CONCURRENTLY <table>;
ANALYZE <table>;

Stefanel, please also look into if there is any performance impact by not having the tsvector columns as the last ones in the table.

• stop the FWD server;
• apply the transformations (adding column);
• for each affected table:
  • rename the table (it contain the current data and on which the alter table were performed):

    ALTER TABLE abc RENAME TO abc_old;

  • create the new table with the updated order of columns (from ddl/schema_table_<db>_postgresql.sql):

    CREATE TABLE abc (recid INT8 NOT NULL, .... PRIMARY KEY (recid));

  • move the data to new location:

    INSERT INTO abc (recid, ...) SELECT recid, .... FROM abc_old;

  • (re-)index the new table using the specific statements from ddl/schema_index_<db>_postgresql.sql;
  • now, that the new table is fully functional, we can get rid of the old table:

    DROP TABLE users_old;

  • optionally, do a commit to limit the transaction size;
• if commit was not performed after processing each table, now it's the moment;
• restart FWD server.

This has the advantage that is 99% executed on server (SQL) side, with minimal communication with the client (no dump/restore is necessary), therefore is probably optimal from the PoV of the time FWD server is down.

The disadvantage is that each table needs to be processed individually, with DDLs extracted from target conversion, but these statements can be executed in parallel for multiple tables. Ultimately, a small script can be written to drive the process in full automatic fashion. It depends on the number of affected tables if that is feasible.

Constantin Asofiei wrote:

Stefanel, please also look into if there is any performance impact by not having the tsvector columns as the last ones in the table.

Sequential scans will be slower by about 6-8% per tsvector column crossed when filtering on later columns, while index lookups, CONTAINS, and full-row reads show no difference

Implementation plan¶

The tsvector__<field> columns will be placed right after the column(s) they index, in a new database and in a migrated one, so that an incremental ADD COLUMN leaves the schema identical to a fresh import. Rebuilding them will not move them. The minimum PostgreSQL version stays 12.

What I validated¶

I prototyped the migration in SQL and ran it on a copy of my testdb (27 tables, 11 with FTS columns, already migrated with the current trunk) and on a 1M-row table, built from the same words, on PostgreSQL.

Migration cost, 1M rows with 4 FTS columns:

maintenance_work_mem = 1GB makes the rewrite 19% faster.

Rebuild cost, 1M rows, by share of stale rows:

A 1% sample estimated the stale share at 1.04% (exact: 1.00%) in 0.3s.

Not validated yet: the older PostgreSQL versions down to 12, and the Java code.

Correction to my note above: UPDATE <table> SET tsvector__<field> = DEFAULT does not recompute the column, the old value stays (checked on a real table). PostgreSQL recomputes a stored generated column only when one of its source columns is updated. The rebuild below relies on that.

1. Core DDL¶

• P2JPostgreSQLDialect: split getFtsColumnDDL() into a column definition (tsvector__f tsvector generated always as (...) stored).
• DDLGeneratorWorker.generateTableDDLImpl(): emit it inside CREATE TABLE right after the field's last column (the last _N element of an expanded extent) and remove the trailing alter table ... add column. GIN indexes and normalized extents are unchanged.

2. Migration (fts_migrate.sh / ImportWorker)¶

PostgreSQL cannot insert a column at a given position, so a table which is not in the target layout is rewritten, once, whatever its number of word indexes. This is the procedure Ovidiu described, with checks around it.

The FTS columns and their source columns come from the P2O, as ftsSourceColumns() does today. The target order is the live column order, with each FTS column right after its last source column. A table already in that order, with its GIN indexes, is skipped.

One transaction per table, in this order (the word-table triggers and FKs would otherwise make the table refused):

1. SET LOCAL lock_timeout = '5s', statement_timeout = 0 and maintenance_work_mem (sized by --max_threads), then lock the table.
2. Drop the word tables with the existing getDropWordTableDDL().
3. Refuse the table, unchanged, if it has anything the rewrite does not recreate: views or rules, triggers, policies, row level security, publications, extended statistics, owned sequences, identity columns, inheritance or partitions, a non-default tablespace or replica identity, FKs from tables other than <t>__N.
4. Take a snapshot: column definitions, index and constraint definitions, FKs from <t>__N, owner, grants, comments, storage options, row count.
5. Rename the table to <t>__fts_old and create <t> in the target order. Each column is written as format_type(), the collation name from pg_collation when it differs from the type's, not null and the default from pg_get_expr(). When the catalog has a named NOT NULL constraint for the column, the definition keeps that name (constraint <name> not null); the old table still holds the name at that point, so an unnamed definition would get a different one.
6. INSERT ... SELECT the non-generated columns, drop the FKs from <t>__N and drop the old table (never CASCADE).
7. Recreate the constraints (pg_get_constraintdef()), indexes (pg_get_indexdef()), GIN indexes, <t>__N FKs, owner, grants (aclexplode()) and comments (table, column, index, constraint).
8. Compare with the snapshot; any difference other than the FTS columns means ROLLBACK.

• After the commit: VACUUM ANALYZE the table.
• Before starting: refuse while other client sessions are connected (pg_stat_activity, backend_type = 'client backend'), unless --force is given. The largest tables go first.
• --dry-run prints the tables, their target order and the refusals without changing anything (useful for Infor, who write their SQL upgrades by hand, #11842). --verify reports the tables which are not in the target layout.
• One code path for every version from 12 on, without version checks: all statements and catalog objects used above exist in 12. The snapshot compares the columns through to_jsonb() of their pg_attribute rows, without attrelid and attnum, and without atthasmissing and attmissingval, which the rewrite clears for a column added later with a default. So the same query works everywhere and also covers the column settings newer versions add.

3. Rebuild (--rebuild)¶

Per table, the stale fraction (rows where tsvector__f IS DISTINCT FROM <expression>) is estimated from a 1% TABLESAMPLE SYSTEM; small tables are scanned fully.

• Nothing stale: skip.
• Up to about 5%: UPDATE <t> SET <source> = <source>, ... WHERE <any FTS column stale>, one statement for all FTS columns of the table. Only the stale rows are written and the table is not rewritten.
• Above that: the rewrite from section 2.

6. Tests¶

• Fresh DDL: plain, case-sensitive, expanded extent and last-field cases;
• Migration from word tables and from the current columns-at-the-end layout: the layout equals a fresh database (information_schema.columns across all tables); a re-run changes nothing.
• Refused, failed and locked tables leave the database unchanged; extent FKs, grants and comments survive.
• Rebuild after a UDF change, both paths: no stale values left, positions kept.

So, is the scan cost mentioned in #11956-9 acceptable?
For a table the migration refuses, Should I skip it and finish with a non-zero exit code, or stopping the whole run?
Should anything in this plan be changed?

Before we move ahead, I want Alex and Eric to review and provide input.

Taking a performance hit on some sequential scans is certainly something we would not want, so it needs to be considered.

On the other hand, the maintenance problems introduced by using an at-the-end approach are real and will turn into production problems when not managed well in the future. Since customers implement their own schema editing, there are many changes for customers to accidentally edit the schema and leave it in an unexpected state that might not be found until much later.

Eric/Alex: Please provide your thoughts by EOD today.

I also want to present both options fairly, why the tsvector columns are currently placed at the end of the table rather than next to their source columns. This is the usual practice, for the reasons outlined below.

1. Storage and scan speed: PostgreSQL aligns each value on a byte boundary which depends on its type. Fixed-width types (integer, timestamp) take less space and perform better when they are grouped together, so variable-length types like tsvector usually go at the end of the table, to keep the padding between columns low. A tsvector column in the middle also slows down the columns after it: to read a column, PostgreSQL has to step over every variable-length value stored before it in the row. I measured it on a 1M-row table: a sequential scan filtering on a column placed after tsvector columns is about 6-8% slower for each tsvector column it has to step over. Index lookups, CONTAINS and full-row reads showed no difference.
2. DDL: PostgreSQL has no ADD COLUMN ... AFTER <column>, unlike MySQL. Putting a column in the middle of an existing table needs a workaround, like rewriting the whole table or renaming columns. Adding it at the end is what ALTER TABLE ... ADD COLUMN does natively.
3. Separation: The tsvector columns are technical data, used only for the index.
   Keeping them at the end leaves the application columns together and the schema easier to read for whoever inspects it.

Migration speed does not decide it. I measured both on a 1M-row table with 4 FTS columns: dropping and re-adding the tsvector columns in a single ALTER TABLE (Currently the code does one ALTER TABLE per column, not optimal) also rewrites the whole table, and it takes the same time as recreating the table with them next to their source, about 28s and 590 MB of WAL for both. The real difference is that the ALTER TABLE leaves the rest of the table as it is (constraints, indexes, foreign keys, grants, comments), while recreating the table means rebuilding all of them.

Eric/Alex: Please provide your thoughts by EOD today.

Personally, I think that tsvector is only one of multiple example of columns that are dynamic in size and are not suitable to be at the beginning of the table.
In general, fixed sized columns are put at the beginning so that offset computing is O(1). If there are variable sized columns, then all columns after that have a dynamic algorithm to gather their offset. This is why it is preferred to have sized columns at the beginning.

I am not sure I understand the gain of sticking to the OE column order instead of hijacking that and using a FWD specific order of columns that maximizes performance. New columns are added to the end because it is cheap and fast, but on the log run, the table structure is the one granting query speed. Just like analyze, but for DDL, the table layout is the one that influences how fast query speed is.

Taking a performance hit on some sequential scans is certainly something we would not want, so it needs to be considered.

Well, it is a shame that indexing affects table structure and more precisely the seq scan. The table structure should be static and not influenced by what the user wants to index. If an index is added later, then it changes the table layout.

Can you reiterate why the to_tsvector('simple', coalesce(series_title, '')) is computed as a generated column in table and not only in the word-index? Other index lookups are technically also affected because:

• if you query on (non-word-index), you gather the rowid
• based on the rowid, you need to resolve the other columns in filters, sorting or select list
• if the columns in question (non-indexed) are after the computed column, they will be gathered slower.

So I don't this it is only about seq scan. An indexed scan (where you require fields after tsvector or use an unindexed column in the filter after the tsvector) may be slower.

This adds a persistent tsvector column that is automatically generated and updated by PostgreSQL whenever the row changes, so we don't need custom triggers. The index is built on this stored column.

I see:

CREATE INDEX textsearch_idx 
ON imdb_movies 
USING GIN (textsearchable_index_col);

but why not:

CREATE INDEX textsearch_idx 
ON imdb_movies 
USING GIN (to_tsvector('simple', coalesce(series_title, '')));

PS: with the latest suggestion, the "computed" expression in question will occur only in the textsearch_idx index and won't affect the layout of the table, thus the performance of other indexes.

I am not sure I understand the gain of sticking to the OE column order instead of hijacking that

Consider that pretty much all customers are planning to leverage direct access to the converted RDBMS. Most already do this.

Also consider, many customers have code (or their own customers who have code) that accesses the existing database structure. This is most often done to enable reporting engines, user-level direct query capabilities and/or customizations/customer-specific extensions. As part of the conversion project, it is common for these external tools to need to be manually converted. The more hijacking we do, the bigger this job. It becomes a land mine waiting to explode, especially if we don't figure it out in advance.

Bottom line: just as everywhere else, we need to be as compatible as possible. We must not make such changes lightly.

Greg Shah wrote:

Eric/Alex: Please provide your thoughts by EOD today.

Eric?

Also consider, many customers have code (or their own customers who have code) that accesses the existing database structure. This is most often done to enable reporting engines, user-level direct query capabilities and/or customizations/customer-specific extensions. As part of the conversion project, it is common for these external tools to need to be manually converted. The more hijacking we do, the bigger this job. It becomes a land mine waiting to explode, especially if we don't figure it out in advance.

That access using syntax like select * from that gathers the columns in the definition order I suppose.

Bottom line: just as everywhere else, we need to be as compatible as possible. We must not make such changes lightly.

Surely not. But I am trying to figure out if the internal representation of the table on disk (i.e. column order) is something to be compatible about or the migration from OE to PostgreSQL can help customers also reorganize the table structure for better performance.

Greg, I am not that confident on this idea for this task, However, I want to push on my suggestion on USING GIN (to_tsvector('simple', coalesce(series_title, '')));. I know we delivered a solution that stores that tsvector as a separate table column, but I want to understand the benefit of that. Stefanel, are we querying the ts_vector from Java? Isn't it used only for indexing purposes?

That access using syntax like select * from that gathers the columns in the definition order I suppose.

I would expect direct SQL access including dependencies on column positions.

Alexandru Lungu wrote:

However, I want to push on my suggestion on USING GIN (to_tsvector('simple', coalesce(series_title, '')));. I know we delivered a solution that stores that tsvector as a separate table column, but I want to understand the benefit of that. Stefanel, are we querying the ts_vector from Java? Isn't it used only for indexing purposes?

I recall there were some downsides of using the expression only in the GIN index, but I'll double check.
This could be a solution but I will do a comparison between both and come back with the results.

Generated column vs. expression index for CONTAINS

• A (current): stored column tsvector__f = udf.words_to_tsvector(f, true), GIN index on the column.
• B: GIN index on udf.words_to_tsvector(f, true) directly, no column. PostgreSQL computes the value again whenever it needs it from the row.

PostgreSQL 18, default settings (work_mem = 4MB), real UDF, rows of 40 words.

Search, 2M rows (search text known to the planner / bind parameter):

• Few matches: B is as fast or faster, its table is 2.4x smaller. At 200k rows B was never slower.
• Many matches on a big table: B is 2-100x slower. Once the matching pages exceed work_mem, every row on them is checked again, and in B that calls the UDF for each row.
• B needs the exact expression in the query, otherwise the index is silently ignored (4.1s instead of 0.5ms).

Conclusion: B is smaller, leaves the table unchanged and is as fast or faster when a search matches up to ~15% of the rows. A stays fast when a search matches most of a large table, where B took up to 44s. From what I understand, work_mem directly sets the point where B slows down a lot: B stays fast while the heap pages holding the matches fit in work_mem.

The expression index for CONTAINS may be worth using, so I can test it further.

Conclusion: B is smaller, leaves the table unchanged and is as fast or faster when a search matches up to ~15% of the rows. A stays fast when a search matches most of a large table, where B took up to 44s. From what I understand, work_mem directly sets the point where B slows down a lot: B stays fast while the heap pages holding the matches fit in work_mem.

This is a general "breakpoint" for any kind of query. If the analysis detects that the number of rows going to match exceeds a certain threshold, it will fallback to the seq scan (or other index) scan. In this case, the expression covered in the index is no longer "cached" in the index, but recomputed. Basically, it becomes a "slow filter".

Prefix ~100% 0.43 / 1.3 s 43.7 / 44.1 s

This is odd.

Eric/Constantin please provide additional feedback.

Alexandru Lungu wrote:

Eric/Constantin please provide additional feedback.

Considering not having the tsvector column is a performance downgrade, is a no-go for me.

• migrating an existing production database from word indexes to FTS (following the idea Ovidiu mentioned in #11956-7)
• adding a new tsvector column to a production database
• dropping a tsvector column from a production database

• index with trunk, where the index has the computed column
• the computed column exists in the table, and the index is on that column

Considering not having the tsvector column is a performance downgrade, is a no-go for me.

Well, it is a performance downgrade for some cases and improvement for others:

Search Rows matched A B
OR of 20 words 15% 1440 ms 216 ms
OR of 100 words 55% 5.5 / 17.5 s 25.2 / 35.6 s

Also:

Columns after it (1M rows, scans over many rows) 7-9% slower unchanged

Is favorable improvement for B.

Alex/Stefanel: is 'B' (index with computed GIN index) dependent on 'work_mem' set at postgresql? If so, we need to consider that this is per JDBC connection, right (here I mean FWD client)?

And how can this scale when we have 100s of clients? How much additional RAM is required for the database server, if we increase this to a value where it can handle not 2M rows, but 200M or more rows?

Although I don't think a client can request in 'normal operation conditions' a SELECT to retrieve more than lets say in the order of 10k rows.

PS: when talking about performance, I'm inclined to consider almost always the 'worst case scenario'; although the discussion after this is how probable the 'worst case scenario' to happen in real production use-cases.

Constantin Asofiei wrote:

PS: when talking about performance, I'm inclined to consider almost always the 'worst case scenario'; although the discussion after this is how probable the 'worst case scenario' to happen in real production use-cases.

The thing with #11956-22 comparison shows that B is worse because the UDF is evaluated more times, where A is cached. So, we are in a scenario where we have to pick:

• the caching overhead for small cases is making things slower overall.
• but the caching is sometimes better as we avoid some time recomputing the words of a phrase.

But this time, the caching is in the DB and persistent.

Stefanel, I wonder if we can make words_to_tsvector faster so that we nullify completely the need of caching it in a column. There are two steps:

• regexp_split_to_array('some text', '(?:[-,.](?!\d)|(?![-,.])(?:${delimiters}))+'); - this is implemented in PG core in C, so I think for decently sized texts, it should be fast.

coalesce(array_to_tsvector(ARRAY(
             SELECT word
             FROM unnest(udf.split_words('some text', true)) AS word
             WHERE octet_length(word) BETWEEN 1 AND 2046
          )), ''::tsvector)

• this second udf may be performance killer because it has to transform the array in a table, back in an array and then in a tsvector. This extra work is just to trim the words that are bigger than 2046 bytes. From my POV, this is outside the "decently sized text". These texts won't exist in the column anyway, because they will be toasted.

So, maybe we can distinguish between long texts and short texts:

• if octet_length(txt) <= 2046, then the where is irrelevant.
• if octet_length(txt) > 2046, then we can risk having very big words. We need to think more on this.

Is OE storing any arbitrary sized texts in the word indexes or is throwing an error? Is this filtering mimicking a OE behavior that drops words that are bigger than 2046?

The "2046" is a postgresql tsvector limit for one lexeme(word). If ever happens the insert/update will just fail with an error.

Stefanel Pezamosca wrote:

The "2046" is a postgresql tsvector limit for one lexeme(word). If ever happens the insert/update will just fail with an error.

Oh god. I understand. Yet, this is something we can preprocess in FWD before insert/update I suppose, so we don't overwhelm the DB to do that. With this, we can emit an error condition specific to FWD so that we don't silently corrupt data, but actually leverage the business logic converted to error handle this.

https://www.postgresql.org/docs/current/textsearch-limitations.html Here are some limitations of tsvector on PosgreSQL.

I may have some good news regarding these PostgreSQL limitations. I did another experiment, that I never did before and it's possible that PosgreSQL limitations are actually higher than what OE ever reaches.

e.g. OE uses only the first 184 bytes of each word (bytes of the database code page, so 184 characters for ISO8859-1/1252), for stored words and for search terms alike. The rest of the word is discarded, without an error or a warning.

If FWD cuts each word and each search term to 184 the same way, a lexeme is at most 552 bytes (184 characters of 3 UTF-8 bytes), or 276 bytes for a UTF-8 source database, even after upper(). The PostgreSQL lexeme limit is 2046 bytes, so the 1..2046 filter in udf.words_to_tsvector and the MAX_LEXEME_BYTES check in toTsqueryLexeme become redundant. The only condition: cut before upper-casing, on both sides.

There are more tests that I'm currently running and may discover other things, but the conclusion here is that we should not worry about PosgreSQL FTS limits at all.

Also there is Index value over maximum key size. (141) error condition that should be handled but is not checked at all for word indexes.

e.g. OE uses only the first 184 bytes of each word (bytes of the database code page, so 184 characters for ISO8859-1/1252), for stored words and for search terms alike. The rest of the word is discarded, without an error or a warning.

I tried this exact thing an managed to insert a 200k character text in a word index BUT I NEVER checked if it was trimmed :)

Alexandru Lungu wrote:

e.g. OE uses only the first 184 bytes of each word (bytes of the database code page, so 184 characters for ISO8859-1/1252), for stored words and for search terms alike. The rest of the word is discarded, without an error or a warning.

I tried this exact thing an managed to insert a 200k character text in a word index BUT I NEVER checked if it was trimmed :)

So, OE stores and returns the complete value, and ordinary comparisons (=) use all of it. CONTAINS works from the word-index keys alone, which hold each word's first 184 bytes. OE never re-checks the matched records against the stored value.

Stefanel, please redo #11956-22 tests, but simplify the udfs as much as possible to compute the tsvector as fast as you can (dropping the validation).

Alexandru Lungu wrote:

Stefanel, please redo #11956-22 tests, but simplify the udfs as much as possible to compute the tsvector as fast as you can (dropping the validation).

• Stored columns (tsvector + GIN index, same table)
• Expression index (GIN index using the tsvector generator expression)
• Side Table (like previous word-tables, but only one per table and will contain tsvector columns + GIN, maintained by simple triggers)

I'll come back with a report over the results. Will also include the UDF simplification.

Below is the report AI drafted over the tests results.

CONTAINS on PostgreSQL: stored column vs expression index vs side table¶

I compared three ways to index the words of a field for CONTAINS, on a table of 2M records with 40 words each (PostgreSQL 18, default settings, simplified UDF):

• Stored column (FWD today): an extra tsvector__<field> column in the table, with a GIN index.
• Expression index: a GIN index on udf.words_to_tsvector(<field>, true); the table does not change.
• Side table: a separate table <table>__tsvector (recid, tsvector__<field>) with a GIN index, kept up to date by triggers and searched with EXISTS; the main table does not change.

Searches (ms)¶

Each cell: search text written in the SQL / passed as a parameter (one value when both are equal). num1 is another indexed field; num1 = 5 matches 2,057 records.

Why the expression index is slow when many records match: when PostgreSQL has to look at a record itself instead of trusting the index, the expression index must run the UDF again, while the stored column just reads the stored words. With the default work_mem = 4MB this happens when the matches are on more than 65,536 table pages: "any of 20 words" is on 66,102 pages and takes about 6 s, but 153 ms with work_mem = 8MB. It also happens when PostgreSQL reads the whole table (prefix found in every record): the UDF then runs 2M times. The #11956-22 test was fast because its matches were on 65,108 pages, just under the limit.

Writes and maintenance¶

Simplified UDF¶

The simplified UDF returns exactly the same words as the current one (checked on 2.2M values). Two changes:

• No sub-select, so PostgreSQL can inline it (copy it into the query instead of calling it). The 2046-byte limit that the sub-select enforced is not needed once words are cut to 184 bytes like OE (#11956-33).
• Text with only blanks between the words is split with string_to_array instead of a regular expression.

Conclusions¶

• Stored column: fastest searches in most cases. Its costs are in the table: an extra column (its position and the table rewrites, #11956-1), the slowest recompute after a UDF change (149 s), and a 2.1x bigger table, so reading the whole table is 1.6x slower. Filtering on a field placed after the tsvector column is 11% slower than on a field before it (20% without parallel query), against 4-6% on a table without that column.
• Expression index: smallest, cheapest to build and recompute, table unchanged. But searches that match many records are very slow (6-9 s for "any of 20 words", 11-12 s for a prefix found in every record), and their speed depends on work_mem and on how the matches are spread over the table.
• Side table: main table unchanged, searches that match many records between 2.5x slower and 2.8x faster than the stored column, and changing another field is 6x faster. But:
  • even a search that finds nothing takes 17 ms, spent starting parallel workers (under 1 ms with parallel query turned off);
  • num1 = 5 OR f CONTAINS ... takes 175-199 ms instead of 1 ms;
  • the search must be written with EXISTS: with recid in (select ...) some OR searches did not finish within 60 s;
  • the delete trigger must be row level: a statement-level one took 260 ms per deleted record after a bulk delete in the same session;
  • 3 triggers per table.
• Simplified UDF: take it in any case: loads and index builds 2-3x faster, expression index searches up to 3.5x faster.

Recommendation: keep the stored column if search speed comes first. Use the side table, with EXISTS, if an unchanged table layout and no table rewrites come first. I don't recommend the expression index.

Recommendation: keep the stored column if search speed comes first. Use the side table, with EXISTS, if an unchanged table layout and no table rewrites come first. I don't recommend the expression index.

Got it, lets keep the computed column. This is a confirmed approach. Using it as a last column or inline was the OG topic. Using as a last column from what I see in this task is faster, while inline is slower. Can you reiterate what are the advantages of using it inline? Is it about the fact that updating the df in the OG environment will technically yield a column that won't be last in PG, so columns would be shifted? I guess this is a legit concern. Lets keep it inline to stabilize the structure and force the customer to reorder columns in the df if they want to have a reordered set of columns in PG.

Alexandru Lungu wrote:

Can you reiterate what are the advantages of using it inline? Is it about the fact that updating the df in the OG environment will technically yield a column that won't be last in PG, so columns would be shifted?

Yes, that is the main reason. PostgreSQL can only add a column at the end (ALTER TABLE ... ADD COLUMN), and that is also what customers' own schema tools do.

• At the end: a field added to the .df later lands after the tsvector__* columns in an upgraded database, but before them in a database created fresh from the same .df. So two databases built from the same .df end up with different column orders. Any SQL that depends on column position (select *, insert without a column list, reporting tools, #11956-16/18) then behaves differently between them, and nobody notices until much later.
• Inline: the layout depends only on the .df. A field added at the end gives the same order in an upgraded and in a fresh database. A word index added or removed later is handled by fts_migrate.sh, which brings the table to the same layout as a fresh one. The rebuild keeps the column where it is.

• Only sequential scans that filter on a column placed after a tsvector column are slower, by about 6-8% for each tsvector column they step over (#11956-9). Index lookups, CONTAINS and full-row reads are not affected.
• The larger cost of the stored column is its size: the table is 2.1x bigger and a full read is 1.6x slower (#11956-37). That cost is the same whether the column is at the end or inline.
• Migration costs the same: adding a stored generated column already rewrites the whole table. On 1M rows with 4 FTS columns, re-adding them at the end took 27.2s and 597 MB of WAL, and the inline rewrite took 27.4s and 589 MB.

I'll generate a spec plan for the implementation.

At this point any performance degradation is something to avoid. If we were to leave the columns at the end, then we would need to put much more effort into helping the customers manage this. Better documentation is one thing to help but this really needs to be done with tooling.

I think there is no full fix while customers are writing their own code to apply incremental schema updates (see #8786). Before then, perhaps we need a schema-checker that compares the structure that would come from the .df with the actual database structure. With something like that, at least the problem could be detected.

So, the stored tsvector__<field> columns stay at the end of the table, so there is no scan penalty. The layout drift that customer-written schema updates can cause (#8786) is handled by tooling. This is what I will implement in 11956a, in this order. Everything targets PostgreSQL 12+; the SQL was prototyped and tested on PostgreSQL 12 and 18.

1. DDL¶

• All FTS columns of a table are added by one ALTER TABLE instead of one per word index, in field order. Two word indexes on the same field give one column and one GIN index.
• On a populated table this means one rewrite instead of one per column. On 500k rows with 2 FTS columns, WAL drops from 1090 MB to 675 MB.

2. fts_migrate.sh¶

• Per table it either leaves the table as it is, drops orphan columns (no rewrite), adds the missing columns (one rewrite), or rebuilds the FTS block at the end with one ALTER (one rewrite). The last case also fixes a field that a customer appended after the FTS columns.
• --rebuild uses the same single ALTER: the values are recomputed and the column order is kept. Today the column is missing for a moment and the order can change.
• It works one transaction per table. It does not wait more than 5s for a lock and never uses CASCADE. A table with a view on an FTS column is refused, with the reason. A new --dry-run prints the statements without running them.

3. Schema checker (schema_check.sh)¶

• It compares the live database with the structure the conversion DDL would create: column order, types, nullability, defaults, generated expressions, indexes, constraints, sequences and the UDF version. To do this, it builds the DDL in a scratch schema inside a transaction that is always rolled back, so check mode changes nothing.
• For each difference it prints a code, a severity, the expected and actual values, and the SQL that fixes it. There is a JSON output for CI and exit codes 0/1/2.
• Repair works by classes: tables, FTS, indexes, sequences and UDFs by default; columns, nullability and type widening only on request. It never drops a table or a data column. There are --dry-run and --sql-out options, the latter for customers who apply SQL through their own process (#11842).
• Prerequisite found while testing: the import creates some indexes on <table> differently from schema_index_<db>_postgresql.sql. I will make the import reduce indexes exactly like the DDL generator, otherwise every imported database would report differences.

4. Import speed¶

• The import connection pool settings have no effect today (wrong c3p0 names), so the pool stays at 15 connections. I will fix that, a connection leak in the batch path, and synchronous_commit=off leaking to other connections.
• One ANALYZE per table instead of two. A table's indexes are built in parallel; on 18, GIN builds also use parallel workers (2 GIN indexes on 500k rows: 14.5s with no workers, 5.7s with 4). Unique indexes and <table>__N foreign keys are created after the load. The batch insert path gets faster.
• The tsvector computation is about 85% of the COPY time on FTS tables. If the largest FTS tables limit the total import time, I will try splitting those tables over several COPY connections.

The simplified udf.words_to_tsvector and the 184-byte word cut (#11956-33, #11956-39) are already done and will be committed first, after a final check on PostgreSQL 12.

Does this sound good ?

Is there any impact on customers that have already rolled this out to production databases?

Greg Shah wrote:

Is there any impact on customers that have already rolled this out to production databases?

I don't think so, it will have the same output.

Sorry, ignore the last message, there are some differences but avoidable.

Impact on databases already migrated to FTS with the current fts_migrate.sh. If nothing is done after the upgrade, they keep working: same column and index names, same CONTAINS SQL, and FWD does not depend on the column order.

• Order of the tsvector__* columns: field order instead of today's word-index order would rewrite tables whose word indexes are not declared in field order (4 of 47 FTS tables in one converted application). Avoided by keeping today's order.
• Fields added by the customer after the migration sit after the FTS columns. The checker reports them; the repair moves the FTS columns back to the end, which shifts those fields for positional SQL. Repair is opt-in, and --dry-run shows it first.
• UDFs at server start: only udfs.sql is reinstalled, so migrated databases keep the old, slower udf.words_to_tsvector (results are correct). Fixed by reinstalling both scripts together.
• fts_migrate.sh changes: new summary line, refuses to start while other clients are connected, refuses a table with a view on an FTS column. Documented in the release notes.
• Indexes from older imports: a few may use a trailing recid instead of include (recid). Both work; the checker reports them as warnings. Optional --repair=index.

Indexes from older imports: a few may use a trailing recid instead of include (recid). Both work; the checker reports them as warnings. Optional --repair=index.

What is this about? A trailing recid vs include (recid) is a functional issue if the index is unique. A (name, recid) index is always unique by design, so (Alex, 1) and (Alex, 2) is valid, even though names were meant to be unique. This doesn't happen for include (recid) that doesn't make the recid participate in the unique check. Please elaborate on this subject.

UDFs at server start: only udfs.sql is reinstalled, so migrated databases keep the old, slower udf.words_to_tsvector (results are correct). Fixed by reinstalling both scripts together.
The simplified udf.words_to_tsvector and the 184-byte word cut (#11956-33, #11956-39) are already done and will be committed first, after a final check on PostgreSQL 12.

Ok.

Does this sound good ?

I am fine with the plan.

Stefanel, INCLUDE(recid) was added to bring the recid column in the index structure, although is not part of the index - because an order by will always have recid as a sort component, regardless of the index. So, if these indexes get changed (include(recid) is replaced with recid in the index or vice-versa), then we need to understand why.

It's about these two bugs that were flagged by AI in how FWD decides the PostgreSQL index form.
A non-unique index that contains all fields of a unique index should be marked unique and created as (cols) include (recid); when it is not marked, it gets (cols, recid). Uniqueness is not affected (it is always enforced by the unique index), but the same .df gives different indexes.

• The import and the DDL create different indexes. DDLGeneratorWorker.preprocessIndices() removes the word indexes before P2JIndex.normalizeUniqueIndexes(), ImportWorker.importTable() does not. A word index on a field of the unique index then stops the scan (see the next bug), so the derived-unique indexes are not marked. Running normalizeUniqueIndexes() on a table with a unique index (a, b), a word index on (b) and four non-unique indexes containing a and b: the DDL path marks all four, the import path none. An index-only import of a converted application shows the same 4 differences against schema_index_<db>_postgresql.sql.
• normalizeUniqueIndexes() depends on the order the indexes are declared in. Its inner loop stops (break) at the first contained non-unique index, before it reaches the unique index that would mark the candidate. With U unique (a, b), X non-unique (a, c) and Y non-unique (a, b, c), Y is marked only in the order X, U, Y; in U, X, Y and U, Y, X it is not. In a large converted application, 42 of 226 such indexes are missed by the DDL (46 by the import). The minimal unique set is still correct: a unique index never takes the break path.

Stefanel Pezamosca wrote:

• The import and the DDL create different indexes. DDLGeneratorWorker.preprocessIndices() removes the word indexes before P2JIndex.normalizeUniqueIndexes(), ImportWorker.importTable() does not. [...]

Without FST, the word indexes were moved to special word table, they were actually not useful in the primary table. The word indexes are not like normal indexes based on record order, but rather like unique indexes, mapping the existence of tokens (words and prefixes) in the fields/columns of the records. With FST, they must remain with the primary table because this is the place where their data is.

[...] With U unique (a, b), X non-unique (a, c) and Y non-unique (a, b, c), Y is marked only in the order X, U, Y; in U, X, Y and U, Y, X it is not. In a large converted application, 42 of 226 such indexes are missed by the DDL (46 by the import). The minimal unique set is still correct: a unique index never takes the break path.

This case is not about word indexes. (a, b) is a prefix-index of (a, b, c). This means the index Y can be used for locating the (a, b) pairs (which normally requires index U). However, since the U is unique, it should not be dropped or else the unique keys are not enforced (Y will offer just the record order, not other constraints). Should U not be unique, it would be dropped during normalisation.

Order of the tsvector__* columns: field order instead of today's word-index order would rewrite tables whose word indexes are not declared in field order (4 of 47 FTS tables in one converted application). Avoided by keeping today's order.

Does it matter in which order they are defined? I guess not, but it would be good to have a stable ordering of them to match the fields. I guess we can make the reordering of there optional.

Fields added by the customer after the migration sit after the FTS columns. The checker reports them; the repair moves the FTS columns back to the end, which shifts those fields for positional SQL. Repair is opt-in, and --dry-run shows it first.

Well, I guess it is not needed to move the FTS back to the end. The PG production DB will be stressed to always make the ts_vector columns at the end. I guess this is an opt-in as you said and purely optional.

UDFs at server start: only udfs.sql is reinstalled

What do you mean by UDFs at server start? Will FWD automatically install the udfs, is this right?

so migrated databases keep the old, slower udf.words_to_tsvector (results are correct). Fixed by reinstalling both scripts together.

I think it is ok to have the udf upgrade opt-in. You can have the faster words_to_tsvector if you reinstall. But is there a dependency upon udfs to word sql scripts? Can you reinstall word udfs, udfs or both and still work?

fts_migrate.sh changes: new summary line, refuses to start while other clients are connected, refuses a table with a view on an FTS column. Documented in the release notes.

OK.

PS: Ovidiu, the algorithm of dropping extra indexes if they are already satisfied by other indexes has a performance impact. A query that uses in order-by, where and select-list only columns found in the index (e.g. just the recid), won't go to the table on disk to gather the other columns. That means:

If an index (a, b, c) and one (a) exists, then FWD keeps only (a) as unique because is is enough, right? It considers that there is only one record matching (a), so having (a, b, c) would be an overkill. However:

• if the query requires b and c in WHERE, ORDER-BY or SELECT, then the query will do a second table look-up based on internal PG rowid to find b and c and use it (either in WHERE / ORDER BY or in the select-list).
  • this can be fixed by adding in include some of the columns that were considered redundant.
• also, mind that (a) can be null, so there can be multiple records with same a. If we need them sorted by b and c, we would have needed the (a, b, c) index to also help on sorting b and c.

Alexandru Lungu wrote:

UDFs at server start: only udfs.sql is reinstalled

What do you mean by UDFs at server start? Will FWD automatically install the udfs, is this right?
I think it is ok to have the udf upgrade opt-in. You can have the faster words_to_tsvector if you reinstall. But is there a dependency upon udfs to word sql scripts? Can you reinstall word udfs, udfs or both and still work?

Yes, but only when certain conditions hold, and today it only installs a script when something is missing and if the server was started with admin credentialss for the DB. Also these really depends only on what changes I keep in 11956a based on testing.

So, I have branch 11956a based on trunk rev. 16812.
I have two committes here:

• rev. 16813:
  Cut long search terms and match a long term by prefix; inlinable words_to_tsvector (index values equal trunk's).

• rev. 16814:
  Added the stored FTS columns of a table with one ALTER, one column per word-indexed field (FtsColumn, Dialect builders, DDLGeneratorWorker); the import creates the FTS indexes from the same canonical list; udf.words_to_tsvector tests the blank-only fast path with ~* for a case-insensitive index (one upper() less, same values).

• FtsRelayout: a new class that plans and applies each table's FTS migration as one ALTER inside one transaction. Its result per table is in place, added, relaid, orphans dropped, refused or failed.
• ImportWorker: the per-field migration (migrateWordIndex) is replaced by FtsRelayout, with new counters and summary line, and a field with two word indexes is rebuilt only once.
• fts_migrate.sh: gains --dry-run and --fts-at-end. A pre-check refuses to run while other sessions are connected, and an advisory lock prevents two runs at once.
• Cleanup: removes getFtsColumnDDL and getDropFtsColumnDDL, and gives getRelayoutFtsColumnsDDL its caller.

• the import reduces indexes like the DDL: a new P2JIndex.reduce is shared by the DDL generator and the import, so the import creates the same indexes the DDL defines, whatever the declaration order.
• the schema checker in check mode: a new schema_check.sh compares a live database with the expected DDL (tables, columns, FTS columns, indexes, sequences, UDF version) and reports the differences as text or JSON. --check-fts-values finds stale FTS values.
• schema checker repair: schema_check.sh --repair fixes the differences per table in one transaction: missing tables, FTS columns through FtsRelayout, indexes, sequences and the UDF upgrade. Moving the FTS block to the end is opt-in (fts-order).
• import quick wins: a correctly sized connection pool, synchronous_commit=off, a single ANALYZE instead of a second serial one, and fewer small round trips.

Alexandru Lungu wrote:

PS: Ovidiu, the algorithm of dropping extra indexes if they are already satisfied by other indexes has a performance impact. A query that uses in order-by, where and select-list only columns found in the index (e.g. just the recid), won't go to the table on disk to gather the other columns. That means:

If an index (a, b, c) and one (a) exists, then FWD keeps only (a) as unique because is is enough, right? It considers that there is only one record matching (a), so having (a, b, c) would be an overkill. However:

• if the query requires b and c in WHERE, ORDER-BY or SELECT, then the query will do a second table look-up based on internal PG rowid to find b and c and use it (either in WHERE / ORDER BY or in the select-list).
  • this can be fixed by adding in include some of the columns that were considered redundant.
• also, mind that (a) can be null, so there can be multiple records with same a. If we need them sorted by b and c, we would have needed the (a, b, c) index to also help on sorting b and c.

If (a) is defined as unique then we should be keeping both (and I think we do). Your arguments are valid.
However, if they are not, then (a) could be dropped. All columns of a occur sorted in (a, b, c) (if there are more records with same a they will be grouped, arranged in an order given by the other two fields). So this is the question: can the plan identify the super-index and later use it for 'jumping' to a? This might be dialect dependent.

To clarify, the discussion on index trimming is not related to FTS. I will think a bit more on it and eventually defer it to another task.
Stefanel, you can continue the plan.

Summary for the changes committed in 11956a rev. 16815

What changed for the user¶

• fts_migrate.sh now changes each table in a single transaction with at most one table rewrite. A failed table is rolled back completely, including its word tables.
• New option --dry-run. It changes nothing and prints, per table, the status, the reasons, the statements and the table size. The sizes of the tables to be rewritten show how much free disk the run needs, for as many tables as --max_threads migrates at once.
• New option --fts-at-end. It also moves the stored FTS columns behind every other column, in field order. Without it, a column added after them, or columns out of field order, are only reported and do not cause a rewrite.
• Before the UDFs are installed, the script checks the database and refuses to run in two cases:
  • Another client is connected, for example a running FWD server. UDFs are never replaced under a live session.
  • Another fts_migrate run is migrating the same database.
• The admin user for that check must be a superuser or a member of pg_read_all_stats. Otherwise PostgreSQL hides the other sessions and the check could not see them, so it stops with an error. (This is related to the above point. If it's wrong, I can delete the session check)
• The one-run-per-database lock is a PostgreSQL advisory lock tied to the run's connection. A killed run cannot leave it behind.
• The final summary now counts tables as: in place, orphans dropped, added, relaid, refused, failed.

What the plan can be, per table¶

Code¶

• FtsRelayout (new): plans the change from catalog data alone, then applies it inside the caller's transaction. It never commits.
• DbSessionCheck (new): holds the session check and the run lock. Its main is what fts_migrate.sh calls as the admin user.
• ImportWorker: runFtsMigration takes the new ftsAtEnd and dryRun arguments. It takes the run lock on a control connection and then migrates the tables in parallel.
• word_reindex.xml: new variables ftsAtEnd and ftsDryRun, passed through to runFtsMigration.
• Dialect, P2JPostgreSQLDialect: the per-field getFtsColumnDDL and getDropFtsColumnDDL are removed.
• FtsColumn, DDLGeneratorWorker: stored columns are ordered by the position of the field in the table, not by the position of its word index. FtsColumn.toTarget is new and gives the planner the expected column, definition and GIN index statement.

I have more tests running over the last 11956a/r16815. When they finish I'll continue with designing the import fixes and the improvements for the schema checker.

Summary for the changes committed in 11956a since rev. 16815 (r16816 to r16818).

Index import bug fixes (r16816)¶

Problem: the import and the DDL generator chose index forms differently, so an imported database did not match schema_index_<db>_postgresql.sql. The import counted word indexes when reducing the btree indexes, and on both paths the result depended on the order the indexes were declared in. Some non-unique indexes that contain a unique index's fields got (cols, recid) instead of (cols) include (recid). Uniqueness was never affected.

Fix: new P2JIndex.reduce, used by the DDL generator and by the import. Word indexes are left out, the unique index choice is the same as before, and the index form no longer depends on the declaration order. In a large converted application, 42 indexes change form in the generated DDL; nothing else changes. Databases built earlier keep the old form, and the checker reports it as INFO only.

• Index-only import of the large application: 3886 indexes, 0 differences from the DDL (r16815: 4).
• Full testdb import: 0 differences (r16815: 1 index differed).
• word_index suite: no new failures.

The same revision lets the import, word_reindex and fts_migrate.sh take database passwords from the FWD_DB_PASSWORD environment variable when none is given on the command line. fts_migrate.sh uses it, so passwords no longer show in its Java processes' command lines, and it now takes its run lock as soon as the migration starts.

Schema checker (r16817, r16818)¶

New schema_check.sh: it compares a live database with what the current conversion DDL would create, and reports the differences. Repair comes next.

• It builds the expected structure by running the DDL in a scratch schema, in a transaction that is always rolled back, then reads both sides with the same catalog queries. Only the statement types that conversion generates are accepted.
• Each difference has a code (missing/extra/changed table, column, FTS column, index, constraint, sequence, UDF, word table) and a severity: ERROR, WARN or INFO. An FTS block out of order and the old index form are INFO only.
• Output is text or JSON. Exit code 0 means nothing to fix, 1 means differences, 2 means a tool error.
• --check-fts-values finds stored FTS values that are out of date, for example after a UDF change.
• On a large schema it splits the work into several transactions, so it cannot exhaust PostgreSQL's lock table.

• Database built from the DDL: 0 differences, and the database is left unchanged.
• One injected change per difference code: each one is reported with the right code and severity.
• Large application: 0 differences, in about 4 s on v18 and 25 to 35 s on v12.
• testdb migrated with trunk: only the expected UDF version warning and two INFO notes. After the branch's fts_migrate.sh: only the two INFO notes.
• A changed UDF is detected by --check-fts-values on both a 1-row table and a large table.