An app has to provide an ability to sort order and filter on fairly large dataset by any column, the current setup has an index on each column in data tables. This obviously is horrible for updates and inserts (updating 50+ indexes) in some instances. Anyone has any cool ideas/experience with this kind of setup.
2 Likes
Uh, hmm, first Iâd tried to keep the data as immutable as possible, second probably remove some of those indexes if a lot of writes are happening, third if it is not per-user based returns then lots of lots of caching, Iâd probably keeping a running updated cache of each heavily used variation⊠PostgreSQL has a nice feature I use for that called a MATERIALIZED VIEW, can save a little work there but not a ton⊠You have to re-materialize it manually, but many things can share it.
3 Likes
For the major portions of tables they are insert heavy not update heavy.
Y would love to even to save on ram, but all queries are order by with limit against huge numbers of rows so index scan untill you hit 300 matches vs full table scan of possible few hundred millions of rows is what drove the original design
No data layer abstraction like 20 services hitting a single DB directly 
For some joined things yep will save time
1 Like
Cool, so immutable it all.
Now what about reading vs writing?
Something I do often is keep a running tally for things like that, keep a side table and insert there too. Now when people query you can limit them to, oh, 300 max or so, and for each index you keep the âtopâ 300 of the sorts. This makes reading the sorted data for the limited top subset almost stupid fast, increases write time slightly due to another table write (and a janitor process to go over on occasion to pare it back down again), but easy to do. You do have to make a new table per each new âindexâ you are doing, basically making custom indexes, but you can get rid of the 50 indexes on the main table then. 
Uh, build something in front that emulates the postgresql then!
Or worst case, lots of views, replace some tables with views, lots of functions, etc⊠You can optimize a postgresql database internally pretty well, but hope you are comfortable with the true horrors of SQL if you do that, but it is possible.
Better yet, burn all the things that access the DB straight, that is retardedâŠ
Materialized views can work for the recent top N list too, but I prefer to update those on the fly so could get rid of the main table index-hoarde.
2 Likes
Thank you cool idea
2 Likes
Basically âtrueâ services need service autonomy i.e. they donât share their data with anyone else (which either means that one single service manages the data and the way it is cached or the data is âduplicatedâ over multiple services but with varying service-specific representations)
Sounds like too much âreportingâ on the OLTP system (like you didnât know that already)
Rather that simply âguessingâ based on the indices it may be worth logging the queries that are hitting the table (I know, I know, performance killer) in order to determine:
- Which queries are actually common
- Relative frequency of each query
- Cost of common queries
That way you should get a better idea which queries you should go after first and optimize those âside tablesâ for these queries, holding just enough data to be relevant/accurate. In effect you would be taking the first steps of refactoring towards segregating the data for these diverse âservicesâ.
2 Likes
This is large commercial product and I am first week on new job so my ability to change it fundamentally is limited [quote=âpeerreynders, post:6, topic:4239â]
Rather that simply âguessingâ based on the indices it may be worth logging the queries that are hitting the table (I know, I know, performance killer) in order to determine:
Which queries are actually common
Relative frequency of each query
Cost of common queries
[/quote]
Thatâs the problem all queries are basically add hoc people are looking for a needle in a haystack
1 Like
Now I donât know what is going on there but it sounds to me like you may have been dropped into a situation where there is ignored/unacknowledged technological debt. There often is the expectation that advances in technology will counter the accumulated technological debt but in general it rarely works out that way. So by âfundamentally limiting your ability to effect changeâ they might as well be asking you to address the issue of a million dollar debt with a single quarter. Just like financial debt, technological debt has to be managed otherwise it turns into technical bankruptcy which can kill the business.
Often âad hoc queriesâ is a euphemistic description of a situation where âeverybody and their brotherâ is issuing manual queries (from Excel spreadsheets, Access reports, etc) against a mission critical OLTP RDBMS - where I come from that is considered a cardinal sin.
The old fashioned solution is to simply duplicate the database, which is synced every night and redirect all the reporting traffic (âad hoc queriesâ) to the duplicate DB where they donât interfere with mission critical operations.
The more modern approach is to go the BI (Business Intelligence) route and to duplicate the business data into a schema optimized for analysis (often using a star schema) where the various BI tools can do their thing, feeding various dashboards and such. Often one beneficial side effect is that historical data only relevant to reporting can be removed from the operations database - which may bring additional performance improvements.
So far it looks like you a being asked for a âquick fixâ where there is none.
2 Likes
This is not really BI although similar in function the sole purpose of this database is to collect data and let people dig through it quickly users are very few and they are very technical most likely better than I am the only purpose of the system is to save them time.
1 Like
