Database

In event-driven systems, services often need to update local state and publish an event for other services. Doing these as separate operations introduces a consistency risk known as the Dual Write Problem. The core requirement is simple: the database write and event publication should represent one logical change. If one succeeds and the other fails, downstream systems may observe incomplete state. The Transactional Outbox Pattern is a practical way to address this in many microservice architectures. ...

I don’t know if you have tried this. But I did try to read function definition from the system tables in Vertica. I used the table called USER_FUNCTIONS which is a table in the V_CATALOG system schema. More details can be found here: https://my.vertica.com/docs/6.1.x/HTML/index.htm#15021.htm But unfortunately when I try and do something like this: vertdeva01:20130604-113434 > select function\_definition from v\_catalog.user\_functions where schema\_name='calc' and function\_name='getCYForDate'; \---------------------------------------------------------------------------------------------------------------------------------- RETURN to\_date(concat(((((date\_part('year', (processDate)::timestamp))::int - CASE WHEN ((date\_part('month', (processDate)::time (1 row) If you notice the function definition is only partly visible. The rest of it gets truncated. I don’t know how to set it right. If you know how, feel free to comment on this post. ...

Download your free copy of DBVisualizer from http://www.dbvis.com/download/ I am going to give you the steps to setup vertica connection to DB Visualizer on a windows 7. Our windows might look different. But I suppose the terms used on the GUI would match that on any operating system. I know this might be a big assumption as I don’t work for DBVisualizer and I haven’t installed it on any other platform. But it is always worth giving this a try if you are starting to use Vertica and would like to see your tables and write queries on a GUI based environment. I am not a big fan of GUIs. But some times it might become handy. Especially if you are a DBA and you manage quite a lot of databases. ...

This is a brilliant power point presentation that helped summarize partitioning and its uses. I am currently working on mapping an object oriented database into a relational one, focusing on query time performance. So partitioning data according to how it is accessed was a key. And this sure helped a lot in clearing the basics, without wasting much time. hope it helps you as well. MySQL partitions tutorial ...

I am currently working on a database design problem. I work in the financial domain and I deal with data related to benchmarks and securities. The problem with such databases are that each security has multiple identifiers in the real world. They could be Uniquely identified by a SEDOL, CUSIP or ISIN or the many other type of identification standards available. This lead me to the question of designing the tables using a surrogate keys but I was still not sure how my team would react as they had already design loads based on real world identifiers and named it security_id. Unfortunately security_id could be a SEDOL, a CUSIP or ISIN or whatever depending on what region or benchmark the security belongs to. That isn’t really a reliable identifier. And hence to avoid annoying the guy in my team who does data loading I redesigned my queries to do some intelligent identifier resolutions. This cost us a lot. Queries that would perform in barely 500ms now take about 1500ms. ...